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

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.
0
Putoch
Asked:
Putoch
  • 3
  • 2
1 Solution
 
SQL_SERVER_DBACommented:
how many records are you trying to display??? 21 seconds is a long time, by the way, especially for a report.
Is this script a stored procedure or do you have it embedded in a dataset within reporting services?
0
 
PutochAuthor Commented:
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_varchar, 25, LEN(oih2.EventDesc_varchar)) AS Reason,
 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_VARIABLES_MAC AS mac ON mac.ItemID = oi.OrderedItemID LEFT OUTER JOIN
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.account_id,oi.status from account a
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_varchar, 25, LEN(oih2.EventDesc_varchar)) IN (@REASON))
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) 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 dbo.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, 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
0
 
SQL_SERVER_DBACommented:
Turn these  bad boys into stored procedures and call them from reporting services.
0
 
PutochAuthor Commented:
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) 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 dbo.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, 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?

0
 
PutochAuthor Commented:
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
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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