How Dynamic SQL Can Be Static SQL Part 2

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Robert Marda » How Dynamic SQL Can Be Static SQL Part 2

How Dynamic SQL Can Be Static SQL Part 2

Rate Topic

Display Mode

Topic Options

Author

Message

Robert W Marda

Posted Saturday, May 11, 2002 12:00 AM

SSC Eights!

Group: General Forum Members
Last Login: Friday, February 15, 2013 10:33 AM
Points: 976, Visits: 48

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

mingxiao

Posted Monday, May 13, 2002 11:37 AM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, January 22, 2005 9:30 AM
Points: 3, Visits: 1

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

Robert W Marda

Posted Monday, May 13, 2002 11:52 AM

SSC Eights!

Group: General Forum Members
Last Login: Friday, February 15, 2013 10:33 AM
Points: 976, Visits: 48

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

andbir

Posted Tuesday, May 14, 2002 4:49 AM

SSC Veteran

Group: General Forum Members
Last Login: Monday, April 05, 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

Robert W Marda

Posted Tuesday, May 14, 2002 8:40 AM

SSC Eights!

Group: General Forum Members
Last Login: Friday, February 15, 2013 10:33 AM
Points: 976, Visits: 48

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

Andy Warren

Posted Tuesday, May 14, 2002 8:48 AM

SSCertifiable

Group: Moderators
Last Login: Thursday, May 09, 2013 12:38 PM
Points: 6,462, Visits: 1,384

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
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #33593

Mirza Hasnat Ghani

Posted Saturday, April 08, 2006 8:52 AM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, April 08, 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

chetan-396370

Posted Tuesday, April 22, 2008 7:27 AM

Forum 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 »

Permissions