February 14, 2008 at 9:48 am
Is there any tutorial or example or an article regarding the intelligent/ stupid uses of temporary tables vs table variables as well as dynamic SQL?
February 14, 2008 at 9:11 pm
Now I don't know .. is replacing of temporary tables with table variables worth it or not?
Absolutely NOT...
And, I agree with Matt... your co-worker doesn't know what he/she is talking about... the articles I provided are from the horse's mouth... Microsoft. And, if you're like me, you probably don't even trust that... before you let some SQL Clone scare you into making large pardigm changes, write some test code on known data... LOTS of known data... then, you can challenge the resident ID-Ten-T that thinks (s)he knows it all (because they read a couple of articles) with real live performance tests.
As Sergiy would say, "A Developer must not guess... A Developer must KNOW." Your co-worker would probably fall out of his chair if you dropped test code and performance/run statisics on his lap, looked the bugger square in the eye with a delightful and commanding smile on your face and say "Your articles were wrong... wanna see why?"
So far as Dynamic SQL goes, same thing... do some performance tests. The power of Dynamic SQL is too great to ignore as a tool... especially in SS 2k where the functionality of 2k5's programmable TOP command does not exist. I've written GUI paging routines all in Dynamic SQL that will absolutely blow the doors off of any method you could do in the GUI itself. The key is, if the Dynamic SQL faces the public interface, you have to be very careful to prevent SQL Injection Attacks... very careful, indeed.
Dunno if you want it or not, but here's some code I use (sometimes modify to fit the situation) to generate my usual "Million Row Stess Test Table"... Just change the TOP command to vary the size from maybe a 10,000 row proof of principle all the way up to a nice large 16 million row table for the "ultimate" in stress testing code for performance and scalability. Feel free to modify for whatever kind of data you need to make... takes about a 2-1/2 minutes for a million row run the first time and about 36 seconds on subsequent runs...
--drop table jbmtest
--===== Create and populate a 1,000,000 row test table.
-- Column "RowNum" has a range of 1 to 1,000,000 unique numbers
-- Column "SomeInt" has a range of 1 to 50,000 non-unique numbers
-- Column "SomeLetters2" has a range of "AA" to "ZZ" non-unique 2 character strings
-- Column "SomeMoney has a range of 0.0000 to 99.9999 non-unique numbers
-- Column "SomeDate" has a range of >=01/01/2000 and <01/01/2010 non-unique date/times
-- Column "SomeCSV" contains 'Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10'
-- for all rows.
-- Column "SomeHex12" contains 12 random hex characters (ie, 0-9,A-F)
-- Jeff Moden
SELECT TOP 1000000
RowNum = IDENTITY(INT,1,1),
SomeInt = ABS(CHECKSUM(NEWID()))%50000+1,
SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID()))%26+65)
+ CHAR(ABS(CHECKSUM(NEWID()))%26+65),
SomeCSV = CAST('Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10' AS VARCHAR(80)),
SomeMoney = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY),
SomeDate = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME),
SomeHex12 = RIGHT(NEWID(),12)
INTO dbo.JBMTest
FROM Master.sys.ALL_Columns t1,
Master.sys.ALL_Columns t2 --Lack of join criteria makes this a CROSS-JOIN
--===== A table is not properly formed unless a Primary Key has been assigned
ALTER TABLE dbo.JBMTest
ADD CONSTRAINT PK_JBMTest_RowNum PRIMARY KEY CLUSTERED (RowNum)
If you take a look at some of the articles I wrote, they contain test tables based on the same principle... makes a pretty good test bed for testing different type of indexes, as well.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply