asked on

CONVERTING EMBEDDED SQL WITH VARIABLE WHERE CLAUSE TO STORED PROCEDURE

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)

ASKER CERTIFIED SOLUTION

Nico Bontenbal

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

lcohan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dteshome

ASKER

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

Nico Bontenbal

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.

lcohan

sp_executesql does just that - it does NOT recompile all dynamic SQL every time.