dynamic sql vs. clustered index scan
-
Hi,
I have a senario where data has to be retrieved based on the list. Have come up with two approaches
-- dynamic approach
declare @v_stmt varchar(1000)
declare @v_input varchar(1000)
set @v_stmt = 'SELECT LookupId, LookupKey, LookupDescription
FROM Lookups
WHERE Lookups in ' + '('''+replace (@v_input,',',''',''')+''')'
exec (@v_stmt)
-- static approach
SELECT LookupId, LookupKey, LookupDescription
FROM Lookups
WHERE CHARINDEX(LookupKey, 'AwardStatus,PersonType' ) > 0
Best practise usually mentions on avoiding the dynamic approach
In the second case when I see the execution plan I come across an Clustered Index Scan on Lookups table. I've also read that scan's are not so good for performance.
I was wondering over the approach I should opt for. Look forward to you valuable feedbacks.
Thanks!
Regards,
Sachin Dedhia -
First of all, from looking at your code I guessing that what you have is a "mega-lookup table", containing lookup values for several other tables. If so, then that is your main problem. Best practice would be to remove that table and replace it with one lookup table for every type of lookup value (such as PersonType, AwardStatus etc) stored in it. Don Peterson has a great article called Lookup Table Madness that describes this.
Regarding searching for data in lists, check out SQL Server MVP Erland Sommarskog's articles Arrays and Lists in SQL Server and Dynamic Search Conditions
-
Or you could keep your static SQL but rather than make use of your charindex statement, which cannot use any indices, you can use a UDF that returns a table.
For example, this UDF will take a comma separated list of integers as a string and returns a table (I think I got this from this site maybe two years ago - apologies for not referencing author properly!)
CREATE FUNCTION intlist_to_tbl (@list varchar(8000))
RETURNS @tbl TABLE (val int) AS
BEGIN
DECLARE @ix int,
@pos int,
@STR varchar(8000),
@num int
SET @pos = 1
SET @ix = 1
WHILE @ix > 0
BEGIN
SET @ix = charindex(',', @list, @pos)
IF @ix > 0
SET @STR = substring(@list, @pos, @ix - @pos)
ELSE
SET @STR = substring(@list, @pos, len(@list))
SET @STR = ltrim(rtrim(@str))
IF @STR LIKE '%[0-9]%' AND
(@str NOT LIKE '%[^0-9]%' OR
@STR LIKE '[-+]%' AND
substring(@str, 2, len(@str)) NOT LIKE '[-+]%[^0-9]%')
BEGIN
SET @num = convert(int, @STR)
INSERT @tbl (val) VALUES(@num)
END
SET @pos = @ix + 1
END
RETURN
END
You can then use it for something like
select C.customerName, C.phone, C.address, etc
from Customers C
inner join dbo.intlist_to_tbl('1,2,3,4,5,6') CIDs
on C.customerID = CIDs.customerID
This should let you keep your static SQL but at least have a dynamic list like your "in" statement.
-
Chris, you are right in you assumption, what I have is a master lookup with approx 175 keys and approx 3000 records. The Id, which is an identity field, is being referenced by other tables.
janyates, thanks for the code, but my question still remains. Or lets forget my query for a moment. If an option is given to choose between dynamic sql and clustered index scan which would it be and why.
Chris, I would like you comments on removing the master lookup table and replacing with a table for each key (considering there are as I mentioned approx 175 keys and approx. 225 tables)
Thanks,
Sachin
Regards,
Sachin Dedhia -
> Or lets forget my query for a moment. If an option is given to choose between dynamic sql and clustered index scan which would it be and why.
Comparing dynamic sql to a clustered index scan is like comparing the speed of a car to the time it takes to paint a wall. No meaning at all, that is.
But seriously, a clustered index scan is a physical operator that tells you how SQL Server decided to fetch the results for your query. Even though you might be able to speed up the query by changing indexing etc, there is nothing inherently wrong with a clustered index scan. Even though it might be incorrect to say that it is inherently wrong to use dynamic sql, it is definitely something to think twice about. Is it really necessary?
> Chris, I would like you comments on removing the master lookup table and replacing with a table for each key (considering there are as I mentioned approx 175 keys and approx. 225 tables)
My comments are simply that you should create the lookup tables, irregardless of how many they are. As Don shows in the article it is not correct to have a single master lookup table.
To make sure there is no confusion, what I mean is that if you have a table lookup (keytype, key, description), with values such as (type1, key1, foo), (type1, key2, bar), (type1, key3, foobar), (type2, key1, foo), (type2, key2, bar) and (type2, key3, foobar) then that table should be replaced with two tables. One for the keys of type1, and the other for those of type2.
-
Another reason to move the master lookup table into seperate lookup tables is that the master does not scale well. You are performing a clustered index scan. A clustered index is the data. You are looking through every record in your table each time your run a query that produces that type of scan.
As the number of records increases the time it takes to perform that query will get longer and longer.
You can test your queries in query analyzer by turning on Show Client Statistics from the query menu. It will provide information on the query performance.
Richard L. Dawson
Microsoft Sql Server DBA/Data ArchitectI can like a person. People are arrogant, ignorant, idiotic, irritating and mostly just plain annoying.
-
Chris, Tinker thanks for valuable inputs, I would have done the same you mentioned, but I'm a sql developer
Btw, I'll also add that there is a lot of overhead involved in maitaining the master lookup as compared to the tables.
Thanks,
Sachin
Regards,
Sachin Dedhia -
Not to mention that you always have to know the keytype when you write the queries... try changing the key type once the project is one year old. Can you say all-nighter
. -
True. But for now
Regards,
Sachin Dedhia -
Yup... wouldn't wanna be in the shoes of that guy when that happens.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply