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

Slowness on Sql Server Query for unknown reason

Hi,
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?


SELECT
      a.account_id,
      a.account_status_date,
c.customer_number,
c.first_name +''+ c.last_name as Customer_name,
      p.product,
pg.product_group,
sr.status_reason_name,
mac.value,
        -- 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,
gis.confirm_geo_code_x,
gis.confirm_geo_code_y,
--tab.salesdate,
--tab.SalesOrder,
--tab.Sales_Rep,
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
      
FROM ACCOUNT a
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
LEFT JOIN ORDERED_ITEM_TRACKING_VARIABLES_MAC mac  ON mac.ItemID = oi.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
      FROM         ACCOUNT AS a LEFT OUTER JOIN
                      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))
OR                    
(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!!
Thanks,
elaine.



0
Putoch
Asked:
Putoch
  • 2
  • 2
2 Solutions
 
UnifiedISCommented:
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
0
 
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?
0
 
UnifiedISCommented:
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.  
0
 
PutochAuthor Commented:
hi,
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.
Putoch.
0
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