James Dingle-651585 (5/24/2010)
Hi Jon,Quick remarks:
- There is a clustered index scan on the ID. Obviously, this way it won't work. The snippet of code I've wrote was assuming that ProductID is the clustered unique index (usually the PK) of the table.
ProductID is the clustered PK, see the script to populate the table.
- You have to add the indexes on the columns on which the filtering is done. In the execution plan above, obviously there is no index on "Name" and so on. So it is not possible to figure out how and why it works.
There is an index on Name, see the table population script.
- Don't trust too much percentages to define a query to be good or bad. Trust CPU time and # of reads. I never knew what those percentages were about exactly and by experience found that they help to focus attention on bottlenecks. But what really matters is bottom line CPU and IO.
I agree, you can't trust these percentages too much.
- My point was not really about performance, but more about what dynamic queries based on concatenated SQL gives in a real environment, within the lifetime of a project. I used it once, and with the experience of it and others techniques, I went away from dynamic SQL.
I think there are other possible ways to allow the need for maintenance of dynamic SQL to be nearly as easy to detect as it is on static procedures, maybe by listing all the tables and columns used in the dynamic sql in a query at the end of the procedure that would never be excecuted so the procedure would no longer compile if any of the columns were changed, I started just by adding a non-executing select of the table name so it would be detected by sp_depends if you wanted to check which stored procedures would need changing if a table were changed.
My point was to show that it is possible to rely on scalar variable resolution to make some part of the query to be executed or not, and thus triggering dynamic execution without putting some code within quotes.
I'm not sure that you have shown that also, to me, it seems a more complicated method than using dynamic sql.