Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How Dynamic SQL Can Be Static SQL Part 2 Expand / Collapse
Author
Message
Posted Saturday, May 11, 2002 12:00 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 9:05 AM
Points: 976, Visits: 59
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rmarda/howdynamicsqlcanbestatic2.asp



Robert W. Marda
SQL Programmer
Ipreo
Post #4181
Posted Monday, May 13, 2002 11:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, June 14, 2014 8:10 AM
Points: 3, Visits: 4
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.




Post #33589
Posted Monday, May 13, 2002 11:52 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 9:05 AM
Points: 976, Visits: 59
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
SQL Programmer
Ipreo
Post #33590
Posted Tuesday, May 14, 2002 4:49 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 5, 2004 2:54 AM
Points: 220, Visits: 1
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.




Post #33591
Posted Tuesday, May 14, 2002 8:40 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 9:05 AM
Points: 976, Visits: 59
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
SQL Programmer
Ipreo
Post #33592
Posted Tuesday, May 14, 2002 8:48 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Tuesday, October 14, 2014 7:15 AM
Points: 6,785, Visits: 1,901
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


Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #33593
Posted Saturday, April 8, 2006 8:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, April 8, 2006 11:35 AM
Points: 1, Visits: 1

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.

Post #272060
Posted Tuesday, April 22, 2008 7:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 30, 2008 8:49 AM
Points: 1, Visits: 20
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.
Post #488611
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse