January 2005 - Posts

Here is some info I have posted elsewhere but thought I should repost here,

Dynamic SQL (very good article)

http://www.sommarskog.se/dyn-search.html

Stored procedures or not?

 http://msdn.microsoft.com/netframework/default.aspx?pull=/library/en-us/dndotnet/html/storedprocsnetdev2.asp

More to follow.

-
Posted by simonsabin
So you've found a bug and you need a screen dump, we've all used the print screen to capture the screen of the window. Then used word to print the picture. How often have you wanted to just capture a bit of the screen, and what a pain that you need to use paint or word to print the image you've got. Well in OneNote you can capture the bit of the screen that you want. Use the windows key + S or right click on One Note in the sys tray and click "Create screen clipping", your screen goes foggy and you then select the area you want by dragging a rectangle. Viola you now have the image you want in onenote, from where you can print, annotat, drag to email.-
Posted by simonsabin
http://www.microsoft.com/downloads/details.aspx?FamilyID=0a6174a4-c009-4768-8284-698c32ec84e3&DisplayLang=en-
Posted by simonsabin

http://www.luschny.de/math/factorial/SrcCsharp/index.html

http://sqljunkies.com/WebLog/amachanic/articles/PatternSplitString.aspx

-
Posted by simonsabin

Well you get the performance but not quite the capacity for SQL Server databases

http://ohlssonvox.8k.com/fdd_raid.htm

Very amusing.

S

-
Posted by simonsabin

For some time now I wanted a basic example that showed the impact of using functions (UDFs), I was looking at SQLServerCentral and saw a new piece on an LPAD function

http://www.sswug.org/see/Lpad_function_in_T-SQL-20878

So I've simplified it slightly and put it in a loop so see the difference between calling the code directly or using the function, I have also added a CLR version.

The results are as follows

It is interesting that the TSQL udf is 6-7 times slower than using the TSQL system functions directly. Thats quite a hit for having more mangeable code. What surprised me was that calling the CLR function was quicker than the TSQL UDF but still quite a hit.

A number of things to point out,

  • This is a very simple function, for more complicated functions the results will be different. (MS report CLR functions can be quicker than using TSQL when complex)
  • The times are for 100000 iterations and so for 1 call the speed is still fast
  • However if you are running a high performance system the last point is irrelevant, you will be able to process 5 times more calls using the system functions directly than you can using a UDF.
  • A set based query involving a UDF may result in that UDF being called for each row in the query, and thus severly hinder performance

So in summary always be careful about using UDFs, assess there usage before you implement them. As a means of tidying up code they are good but you can't have your cake and eat it, there may be a performance hit for using them .

Simon

---Code---
drop function TSQLlpad
go
create function TSQLlpad
(@str varchar(255
))
returns
varchar(500)

as
begin
declare @v
as varchar(500)

set @v
= replicate ('0',6-len(@str)) + @Str

return @v

end
go

declare @i
int
declare @str
varchar(500)

declare @s
datetime
declare @v
varchar(500)

set @str
= '1234'

set @i
= 0

set @s
= getdate()

while @i
< 100000

begin
set @v = dbo.TSQLlpad(@str
)
set @i = @i + 1

end

select
'result using udf',datediff (ms,@s,getdate()),@i
go

declare @i
int
declare @str
varchar(500)

declare @s
datetime
declare @v
varchar(500)

set @str
= '1234'

set @i
= 0

set @s
= getdate()

while @i
< 100000

begin
set @v = dbo.CLRlpad(6,@str
)
set @i = @i + 1

end

select
'result using CLR Function', datediff (ms,@s,getdate()),@i
go

declare @i
int
declare @str
varchar(500)

declare @s
datetime
declare @v
varchar(500)

set @str
= '1234'

set @i
= 0

set @s
= getdate()

while @i
< 100000

begin
set @v = replicate ('0',6-len(@str))
+ @Str
set @i = @i + 1

end

select
'Result using TSQL Functions',datediff (ms,@s,getdate()),@i
go

 public sealed class simon
 {

  public static SqlString LPad(int len, SqlString var)
  {
   string value;
   value = (string)var;

   return (SqlString)value.PadLeft((int)len,'0');

  }
 }

-
Posted by simonsabin | 3 comment(s)

Whats auto recovery, seemed to crash work bench when it kicked in

-
Posted by simonsabin

I am trying to put together some stats on difference between different ways of doing something in SQL 2005, TSQL, SQLCLR etc and find some interesting results.

If the host machine (the one running Virtual Server) is doing something then the guest machine (i.e. the virtual server) doesn't get all its CPU cycles this therefore throws out any results. Occasionally I have had results that seem to defy time.

The middle column is the result of datediff (ms,@s,getdate()) where @s has been set to getdate() prior.

So explain how it can be negative, espcially after looping 100000 times (see future post)

-
Posted by simonsabin

Great overview of TSQL enhancements in SQL 2005

http://msdn.microsoft.com/sql/default.aspx?pull=/library/en-us/dnsql90/html/sql_05TSQLEnhance.asp

Some great examples as always from Itzik

-
Posted by simonsabin

You can enrole for SP4 beta at http://support.microsoft.com/kb/290211/EN-US/

230+ is a lot of fixes

-
Posted by simonsabin