• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 249
  • Last Modified:

Slowness on Sql Server Query for unknown reason

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!!

  • 2
  • 2
2 Solutions
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
PutochAuthor Commented:
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?
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.  
PutochAuthor Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now