[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

CONVERTING EMBEDDED SQL WITH VARIABLE WHERE CLAUSE TO STORED PROCEDURE

Posted on 2011-05-12
5
Medium Priority
?
416 Views
Last Modified: 2012-06-27
I have several SQL Queries that are generated by ColdFusion WEB application and executed in the database. These queries take very long time to run, up to 2 minutes.  

I want to convert them to SPs, however, I cannot figure out a way to handle the varying AND and OR conditions that make up the WHERE clause. That is, there may be different AND/OR conditions from one execution to another.

The code below is an example of the typical query:


select distinct TOP 501 a.mlsnum
from mls_unified_svo_tbl a (nolock)  -- 46 sec, 18 rows
LEFT OUTER JOIN mls_unified_mvo_svo_tbl m (nolock) on a.mlsnum = m.mlsnum
LEFT OUTER JOIN photos_exist b (nolock) on a.mlsnum = b.mlsnum
LEFT OUTER JOIN virtual_tours c (nolock) on a.mlsnum = c.mlsnum
where ((a.status = 20 AND cast(a.statusdate as datetime) >= '03/26/2008')
OR (a.status = 25 AND cast(a.statusdate as datetime) >= '08/26/2008')
OR (a.status = 15 AND cast(a.statusdate as datetime) >= '08/26/2008')
OR (a.status IN (5,30,10))) AND (a.property_type = 0)
AND ( dbo.fn_GetDistance(34.051039, -118.341143, a.latitude, a.longitude) <= 0.25)
0
Comment
Question by:dteshome
  • 2
  • 2
5 Comments
 
LVL 22

Accepted Solution

by:
Nico Bontenbal earned 1200 total points
ID: 35749242
I'm afraid a stored procedure won't help you much. I think the delay is in the
AND ( dbo.fn_GetDistance(34.051039, -118.341143, a.latitude, a.longitude) <= 0.25)
part. How many rows does the query return without the where part. This is the number of times the fn_GetDistance is executed. Try to execute the query without this clause and see if it is faster.
Could also be the
cast(a.statusdate as datetime)
is causing some delay. Make sure this field is a date field (if it isn't already). If it isn't a date field it might also help to have a calculated column in the table that converts the text to a date. You can then query on this calculated column.
Also you need indexes on all the fields you use in your where clause. Try something like this
For the fn_GetDistance part you could first calculate what ranges the lat and lon should be in at least (the square around the circle you are looking for. You'll than add something like:
a.latitude between 33 and 35
and a.longitude between -119 and -117
This way SQL server can use an index and doesn't have to execute the fn_GetDistance so many times.
0
 
LVL 40

Assisted Solution

by:lcohan
lcohan earned 800 total points
ID: 35749247
You should build a dynamic SQL inside the SP based on the different AND/OR conditions and by using input parameters then execute that dynamic code by sp_executesql

http://msdn.microsoft.com/en-us/library/ms188001.aspx
0
 

Author Comment

by:dteshome
ID: 35751749
Thank you for the feedback, Nicobo. I have changed the CAST stmt and removed the LEFT OUTER JOIN, replaced with INNER ... and the function is calculating the distance between a fixed point (passed as param) for all the returned recs... over 600K!  I have found a faster version.

My concern is with thedynamic SQL geing compiled each time ... as opposed to SP.  

Like Icohan said, I have written a stub SP to execute the string, but I thought there was some clever idea .... to convert to SP.

Thank you
D
0
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35752179
It depends on how flexible your criteria are. For example when sometimes you want to filter on the data also, and sometimes you don't you can use something like:
Where (a.statusdate  >=@filterdate or @filterdate is null)

Open in new window

But I can imagine you could lose more performance because of the added complexity than you gain by the query being compiled. You could also use if clauses in your sp of course and determine what query is executed. But this won't give you much flexibility also.
I don't know how long your query runs now. But if it is 6 seconds for example, you could wonder how much of these 6 seconds is spent on compiling the query. And if you want to spent so much time bringing it back to 5.8 for example.
0
 
LVL 40

Expert Comment

by:lcohan
ID: 35755094
sp_executesql does just that - it does NOT recompile all dynamic SQL every time.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question