Posted on 2011-05-12
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)
Question by:dteshome
    LVL 22

    Accepted Solution

    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.
    LVL 39

    Assisted Solution

    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

    Author Comment

    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
    LVL 22

    Expert Comment

    by: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.
    LVL 39

    Expert Comment

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

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
    Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
    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…

    731 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now