Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Slowness on Sql Server Query for unknown reason

Posted on 2007-10-09
Medium Priority
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
  • 2
  • 2
LVL 18

Assisted Solution

UnifiedIS earned 375 total points
ID: 20041644
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

ID: 20041848
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

UnifiedIS earned 375 total points
ID: 20043162
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

ID: 20047004
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Exchange database can often fail to mount thereby halting the work of all users connected to it. Finding out why database isn’t mounting is crucial and getting the server back online. Stellar Phoenix Mailbox Exchange Recovery is a champion product t…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

571 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