Slowness in running Reports in Visual Studio 2005 but not in the editor
Hi there,
I am using SQL SERVER 2005. I create all my scripts in t-sql in MS management Studio and create the reports using Visual Studio.
I have a strage problem with some of my scripts.
They run perfectly and quickly in the editor (management studio)
however when i run them in the Report Writer (visual Studio) they take outrageously long to run.
For example one script ran in 21 seconds in the editor but has been runnign for nearly 20 mins in the Report writer.
Has anyone any ideas what could be happening?
Thanks,
ELaine.
I am using SQL SERVER 2005. I create all my scripts in t-sql in MS management Studio and create the reports using Visual Studio.
I have a strage problem with some of my scripts.
They run perfectly and quickly in the editor (management studio)
however when i run them in the Report Writer (visual Studio) they take outrageously long to run.
For example one script ran in 21 seconds in the editor but has been runnign for nearly 20 mins in the Report writer.
Has anyone any ideas what could be happening?
Thanks,
ELaine.
ASKER
Really not that many records.i am returning 1725 rows.
there are two sub queries but i've often built bigger and its ran quicker. I will post the script and maybe there is something obvious i am missing. i ran it this time and it returned in 37 seconds
Declare @fromdate datetime,@todate datetime
SET @fromdate = '2007-09-01'
set @todate = '2007-10-01'
SELECT
oih.ordereditemid,
c.customer_number,
oih.AccountID,
ast.account_status,
a.billing_start_date,
mac.Value AS Modem_ID,
p.product,
oih.DateAdded AS Date_initiated,
acc_his.date_entered as Service_HISTORY_DATE,
SUBSTRING(oih2.EventDesc_v
CASE WHEN acc_his.history_text like'%Equipment Swapout via Courier Requested%' then um.first_name +'-'+ um.last_name else 'Null' END,
is2.status as "Current Swapout Status"
FROM
ORDERED_ITEM_HISTORY AS oih INNER JOIN
ITEM_STATUS AS st ON st.StatusID = oih.StatusID INNER JOIN
ACCOUNT AS a ON a.account_id = oih.AccountID INNER JOIN
PRODUCT as p on p.product_id = a.product_id INNER JOIN
CUSTOMER AS c ON c.customer_id = a.customer_id INNER JOIN
--TROUBLETICKET AS tt on tt.recordid = a.account_id INNER JOIN
ACCOUNT_STATUS AS ast ON ast.account_status_id = a.account_status_id LEFT OUTER JOIN
ORDERED_ITEM AS oi ON oi.OrderedItemID = oih.OrderedItemID LEFT OUTER JOIN
ORDERED_ITEM_TRACKING_VARI
ORDERED_ITEM_HISTORY AS oih2 ON oih2.OrderedItemHistoryID = oih.OrderedItemHistoryID + 1 LEFT OUTER JOIN
(SELECT * FROM ACCOUNT_HISTORY where history_text like '%Equipment Swapout via Courier Requested%')acc_his on acc_his.account_id = a.account_id LEFT OUTER JOIN
USER_MASTER um on um.user_id = acc_his.entered_by
--Finding the status of the swapout now
Left join (select eq.ordereditemid,eq.accoun
Left join equipment_integration eq on eq.account_id = a.account_id
left join ORDERED_ITEM oi on oi.OrderedItemID = eq.OrderedItemID)subq on subq.account_id = a.account_id
Inner join ITEM_STATUS is2 on is2.statusid = subq.status
WHERE (oih.StatusID = 13)
AND (oih.EventDescription LIKE '%Equipment Swapout initiated%')
-- AND (SUBSTRING(oih2.EventDesc_
AND (oih.DateAdded BETWEEN @FROMDATE AND @TODATE)
and acc_his.date_entered = oih.dateadded
and p.product_group_id in (7,26,19)
I am also running another report which is a bit more compleicated then above but not much. There are some more sub -queries in it thats all really. it used to run ok and i added in a table to return an extra field. I know there is nothing wrong with this table as its used in many other queries. Now this report is taking 23 seconds to run in query editor and times out after ages in the report viewer.
Declare @fromdate datetime,@todate datetime
set @fromdate = '2007-10-01'
set @todate = '2007-10-15'
--LAST BTSID using Nested /correlated subqueries
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 --(select * FROM ACCOUNT WHERE account_status_id = 3 and account_status_date between @fromdate and @todate)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 dbo.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, a1.date_entered as SalesOrder,um.first_name + ' ' + um.last_name AS Sales_Rep, a1.account_id as acc
FROM ACCOUNT AS a1 LEFT OUTER JOIN
USER_MASTER AS um ON um.user_id = a1.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 = a1.account_id
--
WHERE ((a1.sales_rep_id NOT IN (10004, 10014))
OR
(a1.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 pg.product_group_id = 7
AND a.account_status_date between @fromdate and @todate
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?
Rgds Putoch
there are two sub queries but i've often built bigger and its ran quicker. I will post the script and maybe there is something obvious i am missing. i ran it this time and it returned in 37 seconds
Declare @fromdate datetime,@todate datetime
SET @fromdate = '2007-09-01'
set @todate = '2007-10-01'
SELECT
oih.ordereditemid,
c.customer_number,
oih.AccountID,
ast.account_status,
a.billing_start_date,
mac.Value AS Modem_ID,
p.product,
oih.DateAdded AS Date_initiated,
acc_his.date_entered as Service_HISTORY_DATE,
SUBSTRING(oih2.EventDesc_v
CASE WHEN acc_his.history_text like'%Equipment Swapout via Courier Requested%' then um.first_name +'-'+ um.last_name else 'Null' END,
is2.status as "Current Swapout Status"
FROM
ORDERED_ITEM_HISTORY AS oih INNER JOIN
ITEM_STATUS AS st ON st.StatusID = oih.StatusID INNER JOIN
ACCOUNT AS a ON a.account_id = oih.AccountID INNER JOIN
PRODUCT as p on p.product_id = a.product_id INNER JOIN
CUSTOMER AS c ON c.customer_id = a.customer_id INNER JOIN
--TROUBLETICKET AS tt on tt.recordid = a.account_id INNER JOIN
ACCOUNT_STATUS AS ast ON ast.account_status_id = a.account_status_id LEFT OUTER JOIN
ORDERED_ITEM AS oi ON oi.OrderedItemID = oih.OrderedItemID LEFT OUTER JOIN
ORDERED_ITEM_TRACKING_VARI
ORDERED_ITEM_HISTORY AS oih2 ON oih2.OrderedItemHistoryID = oih.OrderedItemHistoryID + 1 LEFT OUTER JOIN
(SELECT * FROM ACCOUNT_HISTORY where history_text like '%Equipment Swapout via Courier Requested%')acc_his on acc_his.account_id = a.account_id LEFT OUTER JOIN
USER_MASTER um on um.user_id = acc_his.entered_by
--Finding the status of the swapout now
Left join (select eq.ordereditemid,eq.accoun
Left join equipment_integration eq on eq.account_id = a.account_id
left join ORDERED_ITEM oi on oi.OrderedItemID = eq.OrderedItemID)subq on subq.account_id = a.account_id
Inner join ITEM_STATUS is2 on is2.statusid = subq.status
WHERE (oih.StatusID = 13)
AND (oih.EventDescription LIKE '%Equipment Swapout initiated%')
-- AND (SUBSTRING(oih2.EventDesc_
AND (oih.DateAdded BETWEEN @FROMDATE AND @TODATE)
and acc_his.date_entered = oih.dateadded
and p.product_group_id in (7,26,19)
I am also running another report which is a bit more compleicated then above but not much. There are some more sub -queries in it thats all really. it used to run ok and i added in a table to return an extra field. I know there is nothing wrong with this table as its used in many other queries. Now this report is taking 23 seconds to run in query editor and times out after ages in the report viewer.
Declare @fromdate datetime,@todate datetime
set @fromdate = '2007-10-01'
set @todate = '2007-10-15'
--LAST BTSID using Nested /correlated subqueries
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 --(select * FROM ACCOUNT WHERE account_status_id = 3 and account_status_date between @fromdate and @todate)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 dbo.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, a1.date_entered as SalesOrder,um.first_name + ' ' + um.last_name AS Sales_Rep, a1.account_id as acc
FROM ACCOUNT AS a1 LEFT OUTER JOIN
USER_MASTER AS um ON um.user_id = a1.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 = a1.account_id
--
WHERE ((a1.sales_rep_id NOT IN (10004, 10014))
OR
(a1.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 pg.product_group_id = 7
AND a.account_status_date between @fromdate and @todate
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?
Rgds Putoch
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok, i thried to create a procedure for the second example i should you [
/****** Object: StoredProcedure [dbo].[can_cust] Script Date: 10/15/2007 16:06:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[can_cust]
@fromdate datetime,
@todate datetime
As
--LAST BTSID using Nested /correlated subqueries
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 --(select * FROM ACCOUNT WHERE account_status_id = 3 and account_status_date between @fromdate and @todate)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 dbo.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, a1.date_entered as SalesOrder,um.first_name + ' ' + um.last_name AS Sales_Rep, a1.account_id as acc
FROM ACCOUNT AS a1 LEFT OUTER JOIN
USER_MASTER AS um ON um.user_id = a1.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 = a1.account_id
--
WHERE ((a1.sales_rep_id NOT IN (10004, 10014))
OR
(a1.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 pg.product_group_id = 7
AND a.account_status_date between @fromdate and @todate
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
RETURN]
When i create the procedure in visual studio this is hanging also? am i not mayne creating the sp correctly?
/****** Object: StoredProcedure [dbo].[can_cust] Script Date: 10/15/2007 16:06:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[can_cust]
@fromdate datetime,
@todate datetime
As
--LAST BTSID using Nested /correlated subqueries
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 --(select * FROM ACCOUNT WHERE account_status_id = 3 and account_status_date between @fromdate and @todate)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 dbo.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, a1.date_entered as SalesOrder,um.first_name + ' ' + um.last_name AS Sales_Rep, a1.account_id as acc
FROM ACCOUNT AS a1 LEFT OUTER JOIN
USER_MASTER AS um ON um.user_id = a1.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 = a1.account_id
--
WHERE ((a1.sales_rep_id NOT IN (10004, 10014))
OR
(a1.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 pg.product_group_id = 7
AND a.account_status_date between @fromdate and @todate
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
RETURN]
When i create the procedure in visual studio this is hanging also? am i not mayne creating the sp correctly?
ASKER
I've checked my space on the server and i have over a gig and a half (surely this is enough without effecting run time of queries on it?)
I've tried using queries (perhaps i haven't created them corectly but i think i have?) and still
the SSRS (visual studio report writer seems to just keep running for ever where as if i run the queries in an script editor it runs with in maybe 20 seconds to 30 seconds now?)
Please can someone help or advise , maybe you had the same problem and fixed it?
There is another report now having the same problem (thats 3 all of a sudden)
Maybe this is a spacec issue (but how come it runs on management studio (text editor) and not visual studio(reporter writer)
Please help?
Thanks,
Putoch
I've tried using queries (perhaps i haven't created them corectly but i think i have?) and still
the SSRS (visual studio report writer seems to just keep running for ever where as if i run the queries in an script editor it runs with in maybe 20 seconds to 30 seconds now?)
Please can someone help or advise , maybe you had the same problem and fixed it?
There is another report now having the same problem (thats 3 all of a sudden)
Maybe this is a spacec issue (but how come it runs on management studio (text editor) and not visual studio(reporter writer)
Please help?
Thanks,
Putoch
Is this script a stored procedure or do you have it embedded in a dataset within reporting services?