Compiled string.Format

Ever looked at Performance wizard and seen a significant potion being taken by string.Format? one day I have and decided to try and find a faster string.Format, it took a couple of hours and I came up with a way to cache the static and dynamic portions of the string which speed up things by a bit, but not enough to permanently integrate it into the project, maintenance time is not worth it. 


But if you're program relies heavily on string.format and the difference you have with 1 million executions is worth the 100-500 ms you'll save on it, have fun.


For example, a formatted string with 5 parameters times 1 million executions is ~2350 ms with my method and ~2800 ms with string.Format.


You can find the project here:
https://github.com/drorgl/ForBlog/tree/master/FormatBenchmarks


and the benchmarks here:
http://uhurumkate.blogspot.com/p/stringformat-benchmarks.html

The categories in the graph are a number of parameters the formatting needs to parse.

Read More

Tags: benchmarks, C#, string.format

Dror Gluska March 21, 2011  No comment

Network information and tracing

We all have these times where we offer help or asked to do things outside our job description, some more reasonable then others, this tool was written to help those times and shorten the time we did something else so we can get back to the more interesting stuff.

Here are some of the features:

Fully multithreaded
Tabbed browser-like environment
Whois client, you can add your servers easily!
Traceroute and see estimated distances between nodes.
Watch your routes on the globe!
ASN/Peers information
Get Abuse emails for your hostnames
Check if your IPs/Hostnames are in spam blacklists
a DNS tracing tool.

GeoLite City needs to be updated for the maps to show correct locations.

https://sourceforge.net/projects/netinfotrace/


I felt creative that week, so I even did a website for this project! Be gentle, I'm a developer, not a designer :-)

http://netinfotrace.sourceforge.net/

Read More

Tags: C#, dns, trace route, whois

Dror Gluska March 19, 2011  No comment

Using FTP to Sync

I've needed a script to sync a remote folder, its a folder that is used to get updates dropped into it but I wanted to download only non existing files, I've found a script on dostips, which I've used for a while until I've stumbled on their note "Since all files are passed into the FTP`s MGET command there might be a limit to the number of files that can be processed at once."

So I've changed the script, it batches downloads, you can specify how many files to download in a batch, but 10 is the most stable (it depends on the filename length).

@Echo Off
REM Taken from http://www.dostips.com/DtTipsFtpBatchScript.php
REM 2011-02-15 - Dror Gluska - Added limit of files to download in a batch

if [%1]==[] goto usage

set servername=%~1
set username=%~2
set password=%~3
set rdir=%~4
set ldir=%~5
set filematch=%~6
set maxfilesperbatch=%~7


REM -- Extract Ftp Script to create List of Files
Set "FtpCommand=ls"
Call:extractFileSection "[Ftp Script 1]" "-">"%temp%\%~n0.ftp"
rem notepad "%temp%\%~n0.ftp"

REM -- Execute Ftp Script, collect File Names and execute batch download
setlocal ENABLEDELAYEDEXPANSION

set /a nocount=0
set /a totalfiles=0

Set FileList=
For /F "tokens=* delims= " %%A In ('"Ftp -v -i -s:"%temp%\%~n0.ftp"|Findstr %filematch%"') Do (
    call set filename=%%~A
    
    if Not Exist "%ldir%\!filename!" (
        echo [!filename!] added to batch
    
        set /a nocount+=1
        set /a totalfiles+=1
        call Set FileList=!FileList! ""!filename!""
        
        if !nocount! EQU !maxfilesperbatch! (
            
            if !nocount! gtr 0 (
                echo Downloading !totalfiles! files...
                Call:downloadFiles "!FileList!"
                call set /a nocount=0
                call Set FileList=
            )
        )
    )
)

if !nocount! gtr 0 (
    echo Downloading !totalfiles! files...
    Call:downloadFiles "%FileList%"
)

endlocal

exit /B 0

GOTO:EOF

:downloadFiles filenames
SETLOCAL Disabledelayedexpansion
Set "FtpCommand=mget "
call set "FtpCommand=%FtpCommand% %~1"

call set FtpCommand=%FtpCommand:""="% 

rem echo %nocount% files to download

Call:extractFileSection "[Ftp Script 1]" "-">"%temp%\%~n0.ftp"
   rem notepad "%temp%\%~n0.ftp"

REM -- Execute Ftp Script, download files

ftp -i -s:"%temp%\%~n0.ftp" > nul
Del "%temp%\%~n0.ftp"

exit /b

:usage

echo %0 ^<server^> ^<username^> ^<password^> ^<remotepath^> ^<localpath^> ^<maximum files^>
exit /B 1

goto:EOF


:extractFileSection StartMark EndMark FileName -- extract a section of file that is defined by a start and end mark
::                  -- [IN]     StartMark - start mark, use '...:S' mark to allow variable substitution
::                  -- [IN,OPT] EndMark   - optional end mark, default is first empty line
::                  -- [IN,OPT] FileName  - optional source file, default is THIS file
:$created 20080219 :$changed 20100205 :$categories ReadFile
:$source http://www.dostips.com
SETLOCAL Disabledelayedexpansion
set "bmk=%~1"
set "emk=%~2"
set "src=%~3"
set "bExtr="
set "bSubs="
if "%src%"=="" set src=%~f0&        rem if no source file then assume THIS file
for /f "tokens=1,* delims=]" %%A in ('find /n /v "" "%src%"') do (
    if /i "%%B"=="%emk%" set "bExtr="&set "bSubs="
    if defined bExtr if defined bSubs (call echo.%%B) ELSE (echo.%%B)
    if /i "%%B"=="%bmk%"   set "bExtr=Y"
    if /i "%%B"=="%bmk%:S" set "bExtr=Y"&set "bSubs=Y"
)
EXIT /b


[Ftp Script 1]:S
!Title Connecting...
open %servername%
%username%
%password%

!Title Preparing...
cd %rdir%
lcd %ldir%
binary
hash

!Title Processing... %FtpCommand%
%FtpCommand%

!Title Disconnecting...
disconnect
bye

Example:

ftpsync 10.0.0.1 "anonymous" "email@email.com" "/" ".\Temp" ".txt" 10

You have to specify all the parameters.

Unfortunately you have to specify some kind of regex file match, otherwise it will attempt to download the ftp status messages too, could be a problem if there are more status messages than number of files in a batch.

Read More

Tags:

Dror Gluska March 03, 2011  No comment

Checking SQL Load by Top Queries

So you're checking you SQL server, you see the CPU is very high for long periods of time or your users complain the database is slow, where can you start looking?

Well, we can check the load and what is causing it.

The basic query is:

SELECT TOP 500
total_worker_time/execution_count AS [Avg CPU Time],
(SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS query_text,
creation_time,
last_execution_time,
execution_count,
total_worker_time,
last_worker_time,
min_worker_time,
max_worker_time,
total_physical_reads,
last_physical_reads,
min_physical_reads,
max_physical_reads,
total_logical_writes,
last_logical_writes,
min_logical_writes,
max_logical_writes,
total_logical_reads,
last_logical_reads,
min_logical_reads,
max_logical_reads,
total_elapsed_time,
last_elapsed_time,
min_elapsed_time,
max_elapsed_time

FROM sys.dm_exec_query_stats
ORDER BY sys.dm_exec_query_stats.last_elapsed_time DESC

We can modify the order by or where clauses so it will give the results for our needs.

I would recommend adding 

where execution_count > 100

So it will weed out the single long-running queries from the result set or you can check for execution_count = 1 if you suspect a programmer abuses dynamic SQL.

Then we can modify the order by 

ORDER BY total_worker_time/execution_count desc

so it will give us the mostly used/long running queries. 

You can find out more in the documentation.

Read More

Tags:

Dror Gluska March 03, 2011  No comment

Modifying SQL Stored Procedures/Functions/Views with SQL

I thought I might explain this article a bit more, backup/restore is not enough if your installation is not so simple, accessing tables from a different database can make life a bit more difficult.

I'm going to write about three major features of the other script:
1. List Programmable objects
2. Retrieving Indexes
3. Modify all programmable objects

1. List Programmable objects

Almost all databases contain one way or another of programmable objects, stored procedures, stored functions and views, some DBAs write the full object name [dbname].[schema].[object], some by shortcut [dbname]..[object], sometimes there's a need to access one database from the other, going over even 10 of these objects can be a headache and a complete waste of time, so first, lets dump them to a temp table.

-- =============================================
-- Author:      Dror Gluska
-- Create date: 2010-05-30
-- Description: Gets all Views/StoredProcedures and references outside the current database
-- =============================================
create PROCEDURE tuspGetAllExecutables
AS
BEGIN
    SET NOCOUNT ON;

    declare @retval table (name nvarchar(max), text nvarchar(max), refs int);
    
    declare @tmpval nvarchar(max);
    
    declare @tmpname nvarchar(max);
    
    declare @ref table(
                        ReferencingDBName nvarchar(255),
                        ReferencingEntity nvarchar(255),
                        ReferencedDBName nvarchar(255),
                        ReferencedSchema nvarchar(255),
                        ReferencedEntity nvarchar(255)
                    );
    declare @refdata table (DBName nvarchar(255), Entity nvarchar(255), NoOfReferences int);
                    
    insert into @ref
    select DB_NAME() AS ReferencingDBName, OBJECT_NAME(referencing_id) as ReferencingEntity, referenced_database_name, referenced_schema_name, referenced_entity_name
    FROM sys.sql_expression_dependencies


    
    
    insert into @refdata
    select ReferencingDBNAme,
           ReferencingEntity,
           SUM(NoOfReferences) as NoOfReferences
    from
    (
        select *
        ,    (
                select COUNT(*) 
                from @ref r2
                where r2.ReferencedEntity = [@ref].ReferencingEntity
                and r2.ReferencedDBName = [@ref].ReferencingDBName
            ) 
            as NoOfReferences

        from @ref
    ) as refs
    group by ReferencingDBNAme,         ReferencingEntity
    order by NoOfReferences 


    
    declare xpcursor CURSOR for
    SELECT name
    FROM syscomments B, sysobjects A
    WHERE A.[id]=B.[id]
    and xtype in ('TF','IF','P','V')
    group by name
    order by name
    
    open xpcursor
    fetch next from xpcursor into @tmpname
    
    while @@FETCH_STATUS = 0
    begin
        set @tmpval = '';
        
        select @tmpval = @tmpval + text
        FROM syscomments B, sysobjects A
        WHERE A.[id]=B.[id]
        and A.name = @tmpname
        order by colid
        
        insert into @retval
        select @tmpname, @tmpval, (select top 1 NoOfReferences from @refdata where [@refdata].Entity = @tmpname)
        
        fetch next from xpcursor into @tmpname
    end
    
    close xpcursor
    deallocate xpcursor
    
    select * from @retval
    order by refs 
    
END
GO

The output of this stored procedure looks something like this:

2. Retrieving Indexes

But that's not enough for schemabound views, since they support indexes, we need to save these indexes too since all indexes drop when schemabound views are altered.

-- =============================================
-- Author:        thorv-918308
-- Create date: 2009-06-05
-- Description:    Script all indexes as CREATE INDEX statements
-- Copied from http://www.sqlservercentral.com/Forums/Topic401795-566-1.aspx#bm879833
-- =============================================
CREATE PROCEDURE tuspGetIndexOnTable
    @indexOnTblName nvarchar(255)
AS
BEGIN
    SET NOCOUNT ON;

    --1. get all indexes from current db, place in temp table
    select
            tablename = object_name(i.id),
            tableid = i.id,
            indexid = i.indid,
            indexname = i.name,
            i.status,
            isunique = indexproperty (i.id,i.name,'isunique'),
            isclustered = indexproperty (i.id,i.name,'isclustered'),
            indexfillfactor = indexproperty (i.id,i.name,'indexfillfactor')
    into #tmp_indexes
    from sysindexes i
    where i.indid > 0 and i.indid < 255                                             --not certain about this
    and (i.status & 64) = 0                                                                 --existing indexes


    --add additional columns to store include and key column lists
    alter table #tmp_indexes add keycolumns varchar(4000), includes varchar(4000)
    --go
    --################################################################################################




    --2. loop through tables, put include and index columns into variables
    declare @isql_key varchar(4000), @isql_incl varchar(4000), @tableid int, @indexid int

    declare index_cursor cursor for
    select tableid, indexid from #tmp_indexes  

    open index_cursor
    fetch next from index_cursor into @tableid, @indexid

    while @@fetch_status <> -1
    begin

            select @isql_key = '', @isql_incl = ''

            select --i.name, sc.colid, sc.name, ic.index_id, ic.object_id, *
                    --key column
                    @isql_key = case ic.is_included_column 
                            when 0 then 
                                    case ic.is_descending_key 
                                            when 1 then @isql_key + coalesce(sc.name,'') + ' DESC, '
                                            else            @isql_key + coalesce(sc.name,'') + ' ASC, '
                                    end
                            else @isql_key end,
                            
                    --include column
                    @isql_incl = case ic.is_included_column 
                            when 1 then 
                                    case ic.is_descending_key 
                                            when 1 then @isql_incl + coalesce(sc.name,'') + ', ' 
                                            else @isql_incl + coalesce(sc.name,'') + ', ' 
                                    end
                            else @isql_incl end
            from sysindexes i
            INNER JOIN sys.index_columns AS ic ON (ic.column_id > 0 and (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0)) AND (ic.index_id=CAST(i.indid AS int) AND ic.object_id=i.id)
            INNER JOIN sys.columns AS sc ON sc.object_id = ic.object_id and sc.column_id = ic.column_id
            
            where i.indid > 0 and i.indid < 255
            and (i.status & 64) = 0
            and i.id = @tableid and i.indid = @indexid
            order by i.name, case ic.is_included_column when 1 then ic.index_column_id else ic.key_ordinal end

            
            if len(@isql_key) > 1   set @isql_key   = left(@isql_key,  len(@isql_key) -1)
            if len(@isql_incl) > 1  set @isql_incl  = left(@isql_incl, len(@isql_incl) -1)

            update #tmp_indexes 
            set keycolumns = @isql_key,
                    includes = @isql_incl
            where tableid = @tableid and indexid = @indexid

            fetch next from index_cursor into @tableid,@indexid
            end

    close index_cursor
    deallocate index_cursor

    --remove invalid indexes,ie ones without key columns
    delete from #tmp_indexes where keycolumns = ''
    --################################################################################################

    --select * from #tmp_indexes


    --3. output the index creation scripts
    set nocount on

    --separator
    --select '---------------------------------------------------------------------'

    --create index scripts (for backup)
    SELECT  
            'CREATE ' 
            + CASE WHEN ISUNIQUE    = 1 THEN 'UNIQUE ' ELSE '' END 
            + CASE WHEN ISCLUSTERED = 1 THEN 'CLUSTERED ' ELSE '' END 
            + 'INDEX [' + INDEXNAME + ']' 
            +' ON [' + TABLENAME + '] '
            + '(' + keycolumns + ')' 
            + CASE 
                    WHEN INDEXFILLFACTOR = 0 AND ISCLUSTERED = 1 AND INCLUDES = '' THEN '' 
                    WHEN INDEXFILLFACTOR = 0 AND ISCLUSTERED = 0 AND INCLUDES = '' THEN ' WITH (ONLINE = ON)' 
                    WHEN INDEXFILLFACTOR <> 0 AND ISCLUSTERED = 0 AND INCLUDES = '' THEN ' WITH (ONLINE = ON, FILLFACTOR = ' + CONVERT(VARCHAR(10),INDEXFILLFACTOR) + ')'
                    WHEN INDEXFILLFACTOR = 0 AND ISCLUSTERED = 0 AND INCLUDES <> '' THEN ' INCLUDE (' + INCLUDES + ') WITH (ONLINE = ON)'
                    ELSE ' INCLUDE(' + INCLUDES + ') WITH (FILLFACTOR = ' + CONVERT(VARCHAR(10),INDEXFILLFACTOR) + ', ONLINE = ON)'  
            END collate database_default as 'DDLSQL'
    FROM #tmp_indexes
    where left(tablename,3) not in ('sys', 'dt_')   --exclude system tables
    and tablename = @indexOnTblName
    order by tablename, indexid, indexname


    set nocount off

    drop table #tmp_indexes

END
GO

3. Modify all programmable objects

Now that we have a list of all the programmable objects and the indexes the schemabound objects have we can go over each object, modify it and save it.

declare @codetext nvarchar(max);
declare @newcodetext nvarchar(max);
declare @idxcode nvarchar(max)
declare @tablename nvarchar(255);
declare @indextable table (DDLSQL nvarchar(max));

declare @executables table(name nvarchar(max), text nvarchar(max),refcount int);
insert into @executables
exec tuspGetAllExecutables

declare spcursor CURSOR for
select text,name
from @executables
order by refcount

open spcursor
fetch next from spcursor into @codetext, @tablename

while @@FETCH_STATUS = 0
begin
    set @newcodetext = @codetext;
    
    set @newcodetext = REPLACE( @newcodetext,'test.','test_development.')
    
    if (@newcodetext != @codetext)
    begin
        set @newcodetext = REPLACE( @newcodetext,'CREATE FUNCTION','ALTER FUNCTION')
        set @newcodetext = REPLACE( @newcodetext,'CREATE PROCEDURE','ALTER PROCEDURE')
        set @newcodetext = REPLACE( @newcodetext,'CREATE VIEW','ALTER VIEW')
        
        insert into @indextable
        exec tuspGetIndexOnTable @tablename
                
        print @tablename
        print @newcodetext
        EXECUTE sp_executesql @newcodetext
        
        --recreate lost index for schemabinded views
        if (select COUNT(*) from @indextable) > 0
        begin
            
            declare vidxcursor cursor for select DDLSQL from @indextable
            open vidxcursor
            fetch next from vidxcursor into @idxcode
            
            while @@FETCH_STATUS = 0
            begin
                print @idxcode
                EXECUTE sp_executesql @idxcode
                fetch next from vidxcursor into @idxcode
            end
            close vidxcursor
            deallocate vidxcursor
            
        end
    end
    fetch next from spcursor into @codetext, @tablename
end

close spcursor
deallocate spcursor

So what happens here?
a. we use tuspGetAllExecutables to get all programmable objects.
b. we 'replace' all occurences of 'test.' to 'test_development.', its not perfect (or even correct for your case), but it worked for my needs since the database name I have is unique.
c. modify 'create' to 'alter' for functions, procedures and views.
d. get a list of indexes for that programmable object, only schemabound views return anything.
e. alter the programmable object.
f. recreate all the indexes.

Read More

Tags:

Dror Gluska February 02, 2011  No comment