May 11, 2002 at 12:00 am
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rmarda/howdynamicsqlcanbestatic2.asp
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
May 13, 2002 at 11:37 am
Interesting approach.
But all three examples cause a clustered index scan. I wonder if the author thought about that. I would not use such queries on a large table.
May 13, 2002 at 11:52 am
The first example has been tested on larger searches, up to about 5500 rows and does well. The execution plan for that stored procedure shows a clustered index seek for the table that is used with the code in example one of my article.
I wouldn't use any code that takes too long to deliver results. If it takes too long, I find another way. There is more than one way to do everything described in this article.
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
May 14, 2002 at 4:49 am
This produces extremely bad SQL which will have a dreadful performance.
Calling a table with 5500 rows a large search is a laugh, my customer table has 3.6 million rows in it....
A far better way would be to build a query @string variable suitable to meet the exact query and exec(@string) instead.
May 14, 2002 at 8:40 am
I have tested the technique described in example 1 of my article on a table that has 1.8 million rows.
When getting half to all of the rows I found that the time it takes to return those rows is the same.
When getting few rows from the table is when a difference in time is noticed and the query not using my technique got the rows faster.
The reason for this is that using my technique SQL Server pulls all the rows before reducing the result set based on the WHERE clause and using a query with a WHERE clause that has no variables and no CASE functions only pulls the rows it needs.
I'll do some research on this and plan to publish an article or two that describe performance issues with using dynamic SQL and static SQL using the CASE function.
Any suggestions for what would make such an article useful are welcome.
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
May 14, 2002 at 8:48 am
Interesting reading. I still think the biggest downside I see in trying to make dynamic sql static is the loss of readability (to me anyway).
Andy
April 8, 2006 at 8:52 am
Hi All,
I read this query examples. i am not good in database. i want to use the same idea in jsp page. i have a one search page which includes the area, location, price, beds, type and ownership from one table property. so i want to use this query for selecting the data according to selections, some time user can select all area or specific area and same for others, so how i can use same concept in jsp. i am using jsp and mysql server. 90 percent concept of query i understood but how i will use in my application i cant. please any body can give me the idea.
April 22, 2008 at 7:27 am
I have read your article , but it seems to have limited use.
It might work for equalto '=' or the 'like' clause in the where condition.
But the same wouldn't work with "In" clause.
this really takes away the practicality of the solution.As this solution has too many riders on it.
Chetan B.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply