Slowness on Sql Server Query for unknown reason

Posted on 2007-10-09
Last Modified: 2010-03-19
I running a t-sql query on sql server 2005.The problem is with slowness when i put in certain dates in the dates criteria.
The thing is if i run the report for the month of September the query runs in 8 seconds
if i run the query for one week in will just hang.

when i check the  Estimated Query Plan there is a certain table who is at 50% Estimated operator cost and if i check this then when i run it for the month (comes back in 8 sec) the Estimated operator cost comes back at 28%.

It strange as the dates are being pulled from a different table(althogh they are linked)
Here is my code but i don't think there is anything wrong with it considering it runs for certin dates.
I have index on my tables also which i have checked cause i thought that might be the reason for slowness?

c.first_name +''+ c.last_name as Customer_name,
        -- Select the information from the radius_cpetracking table by specific value
      ( --       cpe.btsid,
            SELECT TOP 1 btsID
            FROM radius_cpetracking cp
            WHERE time_inserted <= a.account_status_date
                 -- AND substring(eid,7,14) = mac.value
                   AND  eid = '0x0000'+mac.value
            ORDER BY time_inserted DESC
      ) AS btsid,      
      ( -- cpe.time_inserted
            SELECT TOP 1 time_inserted
            FROM radius_cpetracking cp
            WHERE time_inserted <= a.account_status_date
            --  AND substring(eid,7,14) = mac.value
                  AND  eid = '0x0000'+mac.value
            ORDER BY time_inserted DESC
      ) AS time_inserted,
CASE WHEN act.email_address IS NULL
                      THEN c.email_address ELSE act.email_address END AS email,
                      CASE WHEN c.bulk_email_opt_out = 1 THEN 'Yes' ELSE 'No' END AS Bulk_email_opt_out
LEFT JOIN ACCOUNT_CONTACT AS act on a.account_id = act.account_id
INNER JOIN PRODUCT p on p.product_id = a.product_id
INNER JOIN CUSTOMER c on c.customer_id = a.customer_id
INNER JOIN PRODUCT_GROUP pg on pg.product_group_id = p.product_group_id
LEFT JOIN STATUS_REASONS sr on sr.status_reason_id = a.status_reason_id
-- code to return ModemID associated with service just before it was cancelled
LEFT JOIN (  SELECT   MAX(b.OrderedItemHIstoryID) AS max_hist_id,  b.AccountID
                        FROM ORDERED_ITEM_HISTORY b
                        -- Where clause picks items from Day before Yesterday
                        WHERE b.DateAdded < CONVERT(DateTime, FLOOR( CONVERT( Float, GETDATE()))) -1
                        GROUP BY b.AccountID ) eq_hist  ON eq_hist.AccountID = a.account_id

LEFT JOIN ORDERED_ITEM_HISTORY eq_hist2 ON eq_hist2.OrderedItemHistoryID = eq_hist.max_hist_id
LEFT JOIN ORDERED_ITEM oi ON oi.OrderedItemID = eq_hist2.OrderedItemID
--Find the GIS Co-Ordinates
left JOIN (select gis1.account_id,confirm_geo_code_x,confirm_geo_code_y, gis_date
                  FROM(select account_id,max(gis_date) AS max_date
                  FROM GIS_INFO where confirm_geo_code_x<>'0'
                  AND confirm_geo_code_x<>'0.00' GROUP BY account_id) AS x
left JOIN GIS_INFO AS gis1 ON x.account_id=gis1.account_id AND x.max_date=gis1.gis_date)AS gis ON gis.account_id = a.account_id
--Find Sales Order Dates remmeber there are two differnet dates for Walled REg and Tele Sales
LEFT JOIN (SELECT distinct ah.date_entered as salesdate, a.date_entered as SalesOrder,um.first_name + ' ' + um.last_name AS Sales_Rep, a.account_id as acc
                      USER_MASTER AS um ON um.user_id = a.sales_rep_id LEFT OUTER JOIN
                          (SELECT     account_id, date_entered
                            FROM          ACCOUNT_HISTORY
                            WHERE      (history_text LIKE '<b>Service Status</b> changed from <b>Incomplete Sale</b> to%')) AS ah ON
                      ah.account_id = a.account_id
WHERE   ((a.sales_rep_id NOT IN (10004, 10014))
(a.sales_rep_id IN (10004, 10014))))tab on tab.acc = a.account_id

--Then I link radius_cpetracking substring(eid,7,14) to mac.value (table above)to get the BTSid.
-- NOTE:  This is changed from linking to single field extraction in the SELECT portion of the query Only bring back cancelled accounts
WHERE a.account_status_id = 3
and a.account_status_date between '2007-09-01' and '2007-09-30'
AND (CASE WHEN act.email_address IS NULL THEN c.email_address ELSE act.email_address END IS NOT NULL)
AND (LEN(CASE WHEN act.email_address IS NULL THEN c.email_address ELSE act.email_address END) > 1)
and (act.email_address not like '%dump%' or c.email_address not like '%dump%')
--AND  (CASE WHEN c.bulk_email_opt_out = 1 THEN 'Yes' ELSE 'No' END IN (@OptOut))
Order by p.product_group_id

Please help!!

Question by:Putoch
    LVL 18

    Assisted Solution

    Have you tried using a subquery instead of ACCOUNT and move your ACCOUNT specific criteria from the where clause to the subquery?
    Instead of FROM ACCOUNT a
    FROM (SELECT columns FROM ACCOUNT WHERE account_status_id = 3
    and account_status_date between '2007-09-01' and '2007-09-30') AS a

    Author Comment

    I see where you are coming from with this as i thought it had somthing to do with the where clause but this does not help either?
    The strange thing is why would the script run perfectly when i put in a whole month
    but when i put in a week in teh date parameters it hangs?
    LVL 18

    Accepted Solution

    I'm not sure why narrowing the window makes it hang.  Do you have some sort of index that is based on the month?
    Can you bring your data into a temporary table without the date restrictions and then delete the records from the temp table that are outside the date range?  I've used this technique when date-based criteria on large tables causes slowdowns.  

    Author Comment

    No i have no index on months.
    Ok when i used variables instead of writing in the dates in this query it works perfectly this is strange.
    So by entering at the top of the query
    Declare @fromdate datetime,@todate datetime
    Set @fromdate = '2007-10-01'
    Set @todate = '2007-10-08'

    and replace the ''and a.account_status_date between '2007-09-01' and '2007-09-30'''
    with '' and a.account_status_date between @fromdate and @todate ''

    thanks for your help and advice.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
    This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
    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…

    733 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

    20 Experts available now in Live!

    Get 1:1 Help Now