Putoch
asked on
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.OrderedItemHistor yID = eq_hist.max_hist_id
LEFT JOIN ORDERED_ITEM oi ON oi.OrderedItemID = eq_hist2.OrderedItemID
LEFT JOIN ORDERED_ITEM_TRACKING_VARI ABLES_MAC mac ON mac.ItemID = oi.OrderedItemID
--Find the GIS Co-Ordinates
left JOIN (select gis1.account_id,confirm_ge o_code_x,c onfirm_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)A S 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.
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
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.OrderedItemHistor
LEFT JOIN ORDERED_ITEM oi ON oi.OrderedItemID = eq_hist2.OrderedItemID
LEFT JOIN ORDERED_ITEM_TRACKING_VARI
--Find the GIS Co-Ordinates
left JOIN (select gis1.account_id,confirm_ge
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'
left JOIN GIS_INFO AS gis1 ON x.account_id=gis1.account_
--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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
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?