How to expand this variable for the query to work
-
GSquared: Thanks for helping me out.
I think some people are missing the point. Assuming is indexed, cast( as anything) will mean that 's index will never be used thus resulting in a table/index scan.
It's just like converting a datetime to a string. dateTimeKey's index is useless for selectivity if it's converted like so:
select ... from
T where convert(char(8),T.dateTimeKey,12) = "080107"
So searching with '%'+ cast( as varchar) + '%' is bad. It works fine for small tables because even bad SQL runs acceptably on small tables.
-
You just have to keep the tests honest. I wasn't counting icons and going "Oooh, it's faster." In the simple tests, both processes had a table scan. The one that did not also have to perform a join against data missing statistics was faster.
But those were simple tests. I'm setting up something a bit more comprehensive and including a table of numbers in the test.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Grant Fritchey (1/7/2008)
You just have to keep the tests honest. I wasn't counting icons and going "Oooh, it's faster." In the simple tests, both processes had a table scan. The one that did not also have to perform a join against data missing statistics was faster.But those were simple tests. I'm setting up something a bit more comprehensive and including a table of numbers in the test.
For the IN( UDF ) alternative, the table being queried (hosts/udf) did not have a table scan if the identifying column (hostid/seq) was indexed. The table the UDF created had a table scan but it only has a handful of rows.
-
There's several techniques and timings described here
http://www.sommarskog.se/arrays-in-sql-2005.html
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
I did some tests. For good or for ill, I found that the XML & Table of Numbers worked better than anything else when dealing with large tables and complex queries. The function just didn't work that well. Yes, the LIKE clause always lead to table scans, so while it was sometimes faster than the function, depending on how well the function was working with the clustered index in the tables for the queries I was testing with, overall, it just didn't cut the mustard once I went to really complex queries.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Grant Fritchey (1/7/2008)
I did some tests. For good or for ill, I found that the XML & Table of Numbers worked better than anything else when dealing with large tables and complex queries. The function just didn't work that well. Yes, the LIKE clause always lead to table scans, so while it was sometimes faster than the function, depending on how well the function was working with the clustered index in the tables for the queries I was testing with, overall, it just didn't cut the mustard once I went to really complex queries.without details on your dataset or seeing the queries or the associated timings, your mustard statement is really unsupported.
-
I just tested:
set statistics time on
select *
from dbo.names
inner join common.dbo.stringparser('140564,142245,139778,139779,149508,141315,141618,141175,140808,
141127,141128,141131,140822,141135,141140,140817,141163,141148,141609,140833,141152,140804,141115,
141113,141709,141706,141707,141277,141725,141728,141724,141286,141281,141720,141722,141673,141682,
141681,141679,141230,141677,141224,141675,141265,141261,141247,141693,141249,141630,141184,141187,
141636,141189,141200,141201,141191,141650,141649,141193,141802,143006,143754,141762,141340,148796,
148797,151588,139750,140565,148860,151568,139095,148856,139019,138996,139009,139313,138894,138893,
138980,138979,138886,148838,151600,148842,151643,148847,148848,148844,140150,140143,140145,140158,
139703,139704,139601,139724,139449,140167,140165,139718,140164,139720,140117,140334,139667,140341,
139895,139892,140135,139684,140124,139620,140097,140095,140096,139642,150912,140093,140100,140071,
140542,140850,140541,140548,140559,140557,140552,140553,140858,140554,140844,140874,139603,140881,
139608,139607,140880,139286,140501,140502,140049,140513,140246,140066,140255,139791,139796,139795,
140236,139792,139797,140232,140391,139941,140408,140405,139953,139952,140395,139957,139956,139955,
139950,140397,139948,140423,139971,140382,140375,140387,139972,139934,140384,139936,139920,139921,
140364,140425,140426,139303,140421,139633,140418,140010,140438,139982,140437,140467,139987,140448,
140439,140440,140442,140495,140032,140445,140446,139984,139990,140034,139988,139994,140452,140453,
139991,140000,140003,140487,140024,140005,140494,140475,140014,140305,151151,140357,138957,140329,
140350,140183,139739,140176,140182,140259,139727,139769,139768,140214,139765,139764,139733,140194,
140187,140185,139732,139816,140276,139836,140286,151632,151616,151617,151625,148835,140585,140584,
151633,141087,140761,140763,140752,140757,141084,141369,141368,141050,140718,141064,140736,140731,
141062,141061,141057,140654,140670,140681,140888,140580,140581,141007,141030,140699,140694,140695,
151613,143369,140638,140976,140645,140647,140983,140597,140907,140910,140905,140982,140649,140941,
140936,140621,140937,140618,140960,140634,140958,140959,140950,140948,140631,140952,140955,140626,
140964,140966,141356,140602,140916,140604,140603,140918,140926,140927,140606,140605,140925,140921,
140609,140933,140931,140615,151608,140588,151609,140593,151610,139096,139439,139438,139029,139064,
139073,140073,139413,139058,139387,139373,139378,139056,139371,139369,139370,139368,139341,139339,
139484,139116,139458,139104,141806,139196,139155,139156,139574,139578,139512,139518,139493,139494,
139497,149917,139157,139552,139249,139219,139217,139535,143368,152326,149999,149998,149994,149991,
151500,151516,148735,151509,151501,150546,152383,145780,142106,143362,150059,142907,150952,142101,
143134,143751,145674,150020,152350,141125,143338,145072,142884,150026,142892,142891,147786,147784,
151345,142271,143345,140181,140179,139737,140178,139736,139738,140180,151515,150083,142953,142952,
142949,151403,151409,151398,148639,148281,142941,142930,148653,148687,148675,148673,148671,151448,
148669,151449,148668,151439,148664,148656,148657,148655,148659,148660,151438,148661,148662,151435,
148693,151469,142937,143408,142916,152397,150069,150953,148595,142920,151370,148587,148588,151391,
151388,142980,152415,148745,141160,151685,150052,139974,146267,143473,142432,144485,144486,144487,
149005,149006,142108,142061,150186,152421,147705,147703,147701,151222,147700,151443,151433,151446,
151445,143456,151539,149430,148756,141608,152440,143463,143462,142908,142995,142994,143458,148725,
140826,146389,146385,148777,141499,146373,141915,146372,149559,141917,146357,149544,149542,149545,
149538,149539,146361,149547,149522,142820,143502,149645,149512,146318,151196,151199,151200,149578,
146467,149637,146468,149636,149639,146412,146410,149573,146411,152573,146329,149617,146452,149622,
146450,146453,140080,140079,151507,151508,146457,146434,150786,146415,149586,149585,146428,149570,
149596,146418,146417,151040,147474,151046,149660,151051,147478,147451,151330,151335,151334,151333,
147766,147434,147439,147442,147447,147763,147446,151015,151014,147445,151013,147494,141122,151027,
151028,151033,151023,146605,150188,147083,147084,147075,147078,147077,147081,147079,147088,146592,
150277,150273,146588,150159,150172,146601,150174,146595,141919,146586,150156,146581,146579,150149,
146568,146577,150342,147159,150142,147167,150329,146632,147019,147021,150196,146622,150201,146623,
147124,147112,147109,150286,147123,147129,150297,150299,147135,147132,150307,150309,150312,147041,
150237,147060,150257,147061,150258,147057,147055,150261,147026,150218,150216,150212,150211,150209,
150210,147024,150190,150224,147029,147036,147035,147427,151002,147241,150422,150409,147235,147238,
150376,150368,147195,147197,150390,150391,147201,147210,150383,150394,150393,150392,147229,150352,
150351,150350,147176,147182,147185,147475,147281,150447,150452,150456,147219,150439,150399,147225,
147410,147413,150433,150997,147419,147421,147256,147408,149693,149683,146496,149680,146497,146492,
146559,149710,146524,149713,146525,149711,149702,149690,149689,149715,150348,150130,146550,149738,
146546,147322,147595,147368,150945,150950,150948,150946,147371,147376,147308,150957,150954,150484,
150492,150486,147315,150488,150490,147302,150473,147528,147350,150921,147349,149406,147773,150938,
150507,147330,147329,147518,147520,147511,151084,151086,147337,147335,150513,150518,139782,147583,
151138,151136,147391,150976,150978,151105,147555,147553,147549,151108,151111,147552,147563,151114,
151125,147569,147568,151123,151098,151100,151099,147542,147546,145326,151261,151262,151271,151244,
147643,151185,147642,151186,147644,147660,151215,151213,147668,151237,147693,147678,147679,147687,
147690,151238,147675,151226,139388,149433,151202,147656,151193,140370,151277,151075,147722,151076,
151776,151778,151777,147734,151287,147740,151078,151079,151303,151301,147500,151077,139360,151279,
140535,139036,147490,151058,149912,151133,151064,147491,141494,147603,141812,141813,141362,143370,
143543,141492,143358,142905,143353,143340,143341,142883,142882,142885,143350,143348,142950,143418,
143416,142958,142957,143417,142954,142928,142929,142942,143381,143384,142917,143374,142919,143440,
143014,143451,148666,151434,148667,143471,143474,140317,143477,143461,142993,143466,141916,141923,
141466,141476,141925,141927,141938,141490,148932,141370,141374,141371,141887,141879,141891,141897,
141440,141888,141442,141869,141409,141414,141418,141419,141417,141858,141857,141421,141423,141865,
141875,141407,141825,141837,141390,141391,141834,141831,141835,141842,141843,141394,141845,141829,
141387,141821,141378,141377,141822,141906,141444,141447,141904,141450,141909,141908,141539,141994,
142028,141957,141956,142024,141978,141999,141980,141555,141531,141982,143485,141512,141510,141941,
141947,141486,141485,142582,141818,142573,142595,142510,142509,141816,142550,142549,142651,142227,
142242,142567,142220,142536,150202,142524,142724,142379,143161,142726,142513,142362,142701,142709,
142708,142294,142671,142681,142328,142331,142679,142674,142346,142689,142343', ',')
on names.nameid = stringparser.parsed
(Yes, that's 1,000 numbers in a parsed string).
Results:
SQL Server parse and compile time:
CPU time = 15 ms, elapsed time = 16 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
(1000 row(s) affected)
SQL Server Execution Times:
CPU time = 32 ms, elapsed time = 114 ms.
That's on a Pentium-D 3Ghz, 1 Gig RAM (shared w/ video card), SATA HDD
Tried (same hardware):
set statistics time on
select *
from dbo.names
where ',...,'
like '%,' + cast(nameid as varchar(10)) + ',%'
(With the same string where I have "...")
Results:
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 5 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
(1000 row(s) affected)
SQL Server Execution Times:
CPU time = 40766 ms, elapsed time = 40786 ms.
Summary: Large select on a simple table (dbo.Names: 3 fields, NameID (int), Name (varchar(100)),
HashIDX (Computed Int hash of name)), time difference between function using Numbers table and Like
statement is 114 ms vs 40,786 ms, or just over 1/10th of a second vs just over 40 seconds.
In other words, approximately 400 times faster.
So, the "Like" statement could be used for very simple queries against small tables, but I'd never allow
it in any production environment I administer.
(Sorry for the HUGE string in the post, just wanted to make this very, very clear.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
I'm certainly willing to accept that the number table and stringparser function work better than the LIKE code. Generally the tests I have done have been against fairly simple functions to split the string that are either written to a temp table or using a table function. I've never really sat down and tried to create anything much faster because for my purposes its always worked fine. And lets face it .. its simple and I'm lazy :).
Hope you don't mind but I'm going to have to copy your stringparser function into my code library.
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
-
GSquared: I really like stuff like this, so I hope you're not annoyed with another reply.
But I don't think the numbers table is giving you enough bang for the buck. I created this numbers table:
CREATE TABLE [dbo].[numbers]([number] [int] NOT NULL,
CONSTRAINT [PK_NUM] PRIMARY KEY CLUSTERED
(
[number] ASC
)) ON [PRIMARY]
and added 250,000 entries. then i ran 3 passes for various queries. the 1st query uses the LIKE method (which of course performs awful) with a table of 125K rows. the 2nd uses StringParser(), the 3rd uses IN( ListToValues() ), the 4th joins to ListToValues(), and the 5th uses an IN( ) list of the literal values. with each pass i added a few more entries to the candidate list.
declare @list varchar(800)set @list = '... 37 arbitrary nums in no particular order...'
select count(*), ' pass 1, like' from QHD where ('.'+ @list + ',') like '%,'+ cast(seq as varchar(8))+ ',%'
select count(*), ' pass 1, StringParser' from QHD join dbo.stringparser(@list,',') on seq = parsed
select count(*), ' pass 1, in( ListToValues )' from QHD where seq in (select val from dbo.fListToValues( @list ))
select count(*), ' pass 1, join ListToValues' from QHD join dbo.fListToValues( @list ) on seq = val
select count(*), ' pass 1, in( literals )' from QHD where seq in (...literal values of @list ...)
set @list = @list + '... 38 more nums...'
... repeat queries ...
set @list = @list + '... 33 more nums ...'
... repeat queries again ...
here's the results:
SQL Server parse and compile time:
CPU time = 78 ms, elapsed time = 88 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
----------- -------------
37 pass 1, like
SQL Server Execution Times:
CPU time = 6063 ms, elapsed time = 6058 ms.
----------- ---------------------
37 pass 1, StringParser
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 2 ms.
----------- ---------------------------
37 pass 1, in( ListToValues )
SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 6 ms.
----------- --------------------------
37 pass 1, join ListToValues
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 6 ms.
----------- -----------------------
37 pass 1, in( literals )
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
----------- -------------
75 pass 2, like
SQL Server Execution Times:
CPU time = 11766 ms, elapsed time = 11773 ms.
----------- ---------------------
75 pass 2, StringParser
SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 4 ms.
----------- ---------------------------
75 pass 2, in( ListToValues )
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 11 ms.
----------- --------------------------
75 pass 2, join ListToValues
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 11 ms.
----------- -----------------------
75 pass 2, in( literals )
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
----------- -------------
108 pass 3, like
SQL Server Execution Times:
CPU time = 17094 ms, elapsed time = 17109 ms.
----------- ---------------------
108 pass 3, StringParser
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 5 ms.
----------- ---------------------------
108 pass 3, in( ListToValues )
SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 15 ms.
----------- --------------------------
108 pass 3, join ListToValues
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 15 ms.
----------- -----------------------
108 pass 3, in( literals )
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Note that the literal IN() list is hands down the fastest and dispells the far too frequent belief that IN lists are always bad or slow. StringParser() with a numbers table performs next best, but fListToValues() is not far behind and the difference between them is (imo) negligible. Also note that there's no difference between in "(select val from dbo.ListToValues())" and "join dbo.ListToValues() on ...". For simplicity when working with a set of scalar values, I prefer IN (select scalar from table) rather than joining to the pseudo-table.
It seems the performance difference between StringParser() and ListToValues() stems from their techniques: a (uber slick) select vs a (mundane) while loop. So, I made an bigger list of 703 values and got these results:
1> declare @list varchar(4000)
set @list = '1357,...,'+
...
select count(*), ' pass 4, StringParser' from QHD join dbo.stringparser(@list,',') on seq = parsed
select count(*), ' pass 4, in( ListToValues )' from QHD where seq in (select val from dbo.fListToValues( @list ))
24> go
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
----------- ---------------------
703 pass 4, StringParser
(1 rows affected)
SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 30 ms.
----------- ---------------------------
703 pass 4, in( ListToValues )
(1 rows affected)
SQL Server Execution Times:
CPU time = 125 ms, elapsed time = 128 ms.
Since StringParser() and fListToValues() are only executed once per query, a 100ms difference is acceptable to me considering any heavy lifting will occur after they resolve their scalar sets. StringParser() requires a numbers table to be maintained wherever it is used while fListToValues() can be created anywhere since it has no dependencies. StringParser() also requires all values to be preloaded while fListToValues() does not. And fListToValues() can be modified to support strings and non-integers (such as a key storing dewey decimal values). Just something to keep in mind. 😉
ALTER function [dbo].[fListToValues]( @listText as varchar(max) )
returns @values table( [val] int null)
as
begin
declare @list varchar(max), @pos int
set @list = @listText+ ','
while (@list <> '')
begin
set @pos = charindex(',', @list)
insert @values
values ( cast( left( @list, @pos -1 ) as int) )
set @list = right( @list, len(@list) - @pos)
end
return
end
-
Kenneth: Since I stole the function from http://www.simple-talk.com, with a couple of minor modifications, please, feel free to re-steal it from me. 🙂
Antonio: Yes, a straight up "in ()" list will perform quite well. The disadvantage is it requires dynamic SQL to take the list as an input parameter for a proc/function.
Also, as mentioned before, the string parser function, as written, can deal with any sort of string. "'Joe|Bob|Sue', '|'" works just fine as the input parameters for it. It can even deal with escaped single-quotes: "'O''Riley\Smithers', '\'" works just fine. There's even a relatively easy modification (requires a replace char(10) with '' in the select statement) that will allow it to take a list like:
'Bob
Sue
Dave','
'
(Setting the return character as the delimiter.) (This last trick is quite useful in ad-hoc queries where I've copied a column of values from an e-mail or some such format, and want to query against each of them.)
Is it the perfect function that solves everything in SQL? Certainly not! But it is pretty darn useful in the right circumstances.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
Antonio:
On maintaining a Numbers table wherever the code is going to be used, yes, it's work. But such tables have a lot of uses, so I do it. And it's not much work.
"Create table dbo.Numbers (Number int identity primary key) go set nocount on insert dbo.numbers default values go 100000", just isn't that difficult. 🙂
My practice is to create a "Common" database on the server, put a numbers table in it, add in a few functions I like to use a lot (the string parser, a name-case function, a HEX converter and a few others), and a couple of other common-use tables, like a list of US states, and a list of the holidays, etc., observed by the company. Then I don't have to have a list of states in each and every database on the server, and can reference the table from any database in the server. The Dates table can be used for workflow applications that need to know not to set a deadline on a holiday, etc. (Lots of other uses for Dates tables.)
When I'm administering multiple servers, like my current job, I have one copy of Common on each server, and use log shipping to keep them all in synch with the copy on my main server. (Most of the time, it's unusual for the Common database to have any changes more than once or twice a year, unless I discover some really cool new SQL trick I didn't know before and just have to add it in.)
I find this easier than adding a Numbers table to the model database, since I only have to keep one copy of the table per server, not one per database, which means less disk usage, etc.
For a lot of other uses of a Numbers table (there are quite a few), check out the helper table article on http://www.simple-talk.com. Robyn and Phil put a lot of thought into it and it's quite good.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
GSquared: totally agree on your points save one -- i don't think a numbers table is ever necessary. but i'll read your references since i'm sure i'll learn something! great to exchange thoughts with another trickster. 😀
-
antonio.collins (1/7/2008)
i don't think a numbers table is ever necessary.What do you use SQL Server 2000, instead?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (1/7/2008)
antonio.collins (1/7/2008)
i don't think a numbers table is ever necessary.What do you use SQL Server 2000, instead?
ss2k5. as shown in my earlier examples, the numbers table isn't noticeably faster and it has the drawback of having to predefinine all possible values.
-
You see the need to split a parameter with more than a million characters?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply