| Groups | Blog | Home |
sql server reporting services : Incredibly poor performance running SP from RS
I'm having huge problems with the run-time of a stored procedure from RS Designer (and from the server when the report is uploaded). I have a SP with about 6 parameters. When executed from QA the SP takes less than a second to return a result-set of about 1500 rows. When running the SP from RS (Designer > Data tab) using query parameters the excution time is around 3 mins !!! Looking at the TimeDataRetrieval field in ExecutionLog table in the ReportServer DB, the SP is the problem (consuming about 99% of the time involved in the report generation). I've tried adding indexes, I'm not using report filters.... Are there any known problems with RS in executing SPs with multiple params or JOINs ...?? Help! :) Thanks in advance,
This is very odd. Usually when I see a performance issue it is because of
lots of data being returned and the rendering time. 1500 rows is not excessive. When you use Data Tab no rendering is going on so it is definitely not that. I use lots of stored procedures. Both against SQL Server and against Sybase. I have never seen this. Here's a few questions that might help us determine what is happening. What are you doing for credentials? Is the stored procedure using linked databases? Is the stored procedure using dynamic sql? Same timing issue when you view the report? The data tab is also getting schema information not just the data. -- Bruce Loehle-Conger MVP SQL Server Reporting Services [quoted text, click to view] "Casey" <Casey@discussions.microsoft.com> wrote in message news:B17C5B95-E50D-415C-A286-6141B4079428@microsoft.com... > Hi, > > I'm having huge problems with the run-time of a stored procedure from RS > Designer (and from the server when the report is uploaded). > > I have a SP with about 6 parameters. When executed from QA the SP takes > less > than a second to return a result-set of about 1500 rows. When running the > SP > from RS (Designer > Data tab) using query parameters the excution time is > around 3 mins !!! > > Looking at the TimeDataRetrieval field in ExecutionLog table in the > ReportServer DB, the SP is the problem (consuming about 99% of the time > involved in the report generation). I've tried adding indexes, I'm not > using > report filters.... > > Are there any known problems with RS in executing SPs with multiple params > or JOINs ...?? > > Help! :) > > Thanks in advance, > > Casey.
Hi Bruce, thanks for the reply!
To answer your questions... [quoted text, click to view] > I use lots of stored procedures. Both against SQL Server and against Sybase. > I have never seen this. I use SPs for for most of my RS reporting, this is only problem SP I have encountered. [quoted text, click to view] > Here's a few questions that might help us determine what is happening. > > What are you doing for credentials? Straight "Windows NT Integrated Security". [quoted text, click to view] > Is the stored procedure using linked databases? No. RS and SQL sitting on the same server (3.2GHz Hyperthreading Xeon with 3GB RAM) and I'm sure load ain't the problem. [quoted text, click to view] > Is the stored procedure using dynamic sql? No. I'll copy the entire SP at the bottom of this message. [quoted text, click to view] > Same timing issue when you view the report? The data tab is also getting > schema information not just the data. Yes same problem when viewing/rendering the report (through the Designer preview and when uploaded to the Rpt Manager) As promised above, heres the code: SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO /****** Object: Stored Procedure dbo.SalesOrderEntry Script Date: 10/08/2005 2:38:39 p.m. ******/ ALTER PROCEDURE SalesOrderEntry -- These variables are passed to SQL by the Reporting Services report. The default setting is '%' which is the -- wildcard for select all. @BRANCH VARCHAR(3) = '%', @CUSTNAME VARCHAR(50) = '%', @DEALERNAME VARCHAR(50) = '%', @STOCKCODE VARCHAR(50) = '%', @MONTH VARCHAR(3) = '%', @YEAR VARCHAR(6) = '%', @STOCK_CATEGORY VARCHAR(3) = '%' AS -- The default value that Reporting Services passes to SQL is the string value 'ALL' This group of IF statements -- changes the value of parameter to the wildcard value '%' IF @BRANCH LIKE '%ALL%' BEGIN SET @BRANCH = '%' END IF @CUSTNAME LIKE '%ALL%' BEGIN SET @CUSTNAME = '%' END IF @DEALERNAME LIKE '%ALL%' BEGIN SET @DEALERNAME = '%' END IF @STOCKCODE LIKE '%ALL%' BEGIN SET @STOCKCODE = '%' END IF @MONTH LIKE '%ALL%' BEGIN SET @MONTH = '%' END IF @YEAR LIKE '%ALL%' BEGIN SET @YEAR = '%' END IF @STOCK_CATEGORY LIKE '%ALL%' BEGIN SET @STOCK_CATEGORY = '%' END SELECT TOP 100 PERCENT dbo.OEMASTER.DEL_DATE AS [Despatch Date], dbo.OEMASTER.INV_NO AS [Invoice Number], dbo.OEPSLINE.NUMBER AS [Packing Slip Number], dbo.OEMASTER.BRANCH, dbo.ARMASTER.NAME AS [Dealer Name], dbo.OEMASTER.CUST_ORDER AS [Dealer Order Num], dbo.OEMASTER.DEL_NAME AS [Customer Name], dbo.OEMASTER.DEL_ADDR1, dbo.OEMASTER.DEL_ADDR2, dbo.OEPSLINE.STOCK_CODE, dbo.INMASTER.CATEGORY, dbo.INSLTRAN.SERIAL_LOT_NO, CASE WHEN dbo.INSLTRAN.SERIAL_LOT_NO IS NULL THEN dbo.OEPSLINE.QTY_SUPP_SEL ELSE 1 END AS [QTY SOLD], CASE WHEN dbo.INSLTRAN.SERIAL_LOT_NO IS NULL THEN dbo.OEPSLINE.QTY_SUPP_SEL * dbo.OEPSLINE.PRICE ELSE 1 * dbo.OEPSLINE.PRICE END AS [List Price], CAST(dbo.OEPSLINE.DISCRATE AS VARCHAR) + ' %' AS Discount, CASE WHEN dbo.INSLTRAN.SERIAL_LOT_NO IS NULL THEN (dbo.OEPSLINE.QTY_SUPP_SEL * dbo.OEPSLINE.PRICE) * (1 - dbo.OEPSLINE.DISCRATE / 100) ELSE (1 * dbo.OEPSLINE.PRICE) * (1 - dbo.OEPSLINE.DISCRATE / 100) END AS [Net Price] -- INSLTRAN.REF <-- OEPSLINE.STOCK_CODE(ALL) -- INSLTRAN.STOCKCODE <-- OEPSLINE.NUMBER(ALL) FROM dbo.INMASTER RIGHT OUTER JOIN dbo.OEPSLINE ON dbo.INMASTER.CODE = dbo.OEPSLINE.STOCK_CODE LEFT OUTER JOIN dbo.INSLTRAN ON dbo.OEPSLINE.STOCK_CODE = dbo.INSLTRAN.STOCKCODE AND CAST(dbo.OEPSLINE.NUMBER AS VARCHAR(50)) = dbo.INSLTRAN.REF RIGHT OUTER JOIN dbo.OEMASTER ON dbo.OEPSLINE.ORDER_NO = dbo.OEMASTER.NUMBER LEFT OUTER JOIN dbo.OEPSHDR ON dbo.OEMASTER.NUMBER = dbo.OEPSHDR.ORDER_NO LEFT OUTER JOIN dbo.ARMASTER ON dbo.OEMASTER.CUST_NO = dbo.ARMASTER.NUMBER WHERE -- Bakery or Catering only (dbo.OEMASTER.BRANCH LIKE N'NB' OR dbo.OEMASTER.BRANCH = N'NC') -- Records in INSLTRAN that have no Serial Lot No are not included (therefore the test for NULL values) AND (dbo.INSLTRAN.[TRAN] LIKE N'OEORD' OR dbo.INSLTRAN.[TRAN] IS NULL) -- Only Invoiced values AND (dbo.OEMASTER.STATUS = N'I') -- removes erroneously entered values AND NOT ((dbo.INSLTRAN.[TRAN] LIKE N'OEORD') AND (dbo.OEPSLINE.QTY_SUPP_SEL = 0 )) -- Force parameters AND (OEMASTER.BRANCH LIKE @BRANCH) AND OEMASTER.DEL_NAME LIKE @CUSTNAME AND ARMASTER.[NAME] LIKE @DEALERNAME AND OEPSLINE.STOCK_CODE LIKE @STOCKCODE AND MONTH(dbo.OEMASTER.DEL_DATE) LIKE @MONTH AND (CAST(YEAR(DEL_DATE) AS VARCHAR)) LIKE @YEAR AND dbo.INMASTER.CATEGORY LIKE @STOCK_CATEGORY GROUP BY dbo.ARMASTER.[NAME], dbo.OEMASTER.DEL_DATE, dbo.OEMASTER.CUST_ORDER, dbo.OEMASTER.NUMBER, dbo.OEMASTER.INV_NO, dbo.OEPSLINE.STOCK_CODE, dbo.OEMASTER.DEL_NAME, dbo.OEMASTER.DEL_ADDR1, dbo.OEMASTER.DEL_ADDR2, dbo.OEMASTER.STATUS, dbo.OEMASTER.BRANCH, dbo.OEPSLINE.NUMBER, dbo.INSLTRAN.SERIAL_LOT_NO, dbo.OEPSLINE.QTY_SUPP_SEL, dbo.OEPSLINE.PRICE, dbo.OEPSLINE.DISCRATE, dbo.INSLTRAN.[TRAN], dbo.INMASTER.CATEGORY ORDER BY dbo.OEMASTER.BRANCH, dbo.OEPSLINE.NUMBER GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
Nothing jumps out at me. Here is something that might help though. I had an
explanation that when executing SP from RS that for whatever reason it caused an issue with the query plan. I don't see why but that is what I heard. That being the case it might be worth trying out my suggested change to your code. OK, here is how I handle the whole issue with All. Get rid of the code checking for %All% and do this instead. Have the default be All @BRANCH VARCHAR(3) = 'All' Just pass in the word All for your parameter value and change the code to look like this. AND (OEMASTER.BRANCH = @BRANCH or @BRANCH = 'All') This has the benefit of simplifying your code and it uses = instead of like. This is one of my favorite tips for dealing with All. Perhaps it will make a difference since it seems what is happening is that the appropriate indexes are not being used. -- Bruce Loehle-Conger MVP SQL Server Reporting Services [quoted text, click to view] "Casey" <Casey@discussions.microsoft.com> wrote in message news:2A691314-8EE1-46C4-A928-003DC770B77E@microsoft.com... > Hi Bruce, thanks for the reply! > > To answer your questions... > >> I use lots of stored procedures. Both against SQL Server and against >> Sybase. >> I have never seen this. > > I use SPs for for most of my RS reporting, this is only problem SP I have > encountered. > >> Here's a few questions that might help us determine what is happening. >> >> What are you doing for credentials? > > Straight "Windows NT Integrated Security". > >> Is the stored procedure using linked databases? > > No. RS and SQL sitting on the same server (3.2GHz Hyperthreading Xeon with > 3GB RAM) and I'm sure load ain't the problem. > >> Is the stored procedure using dynamic sql? > > No. I'll copy the entire SP at the bottom of this message. > >> Same timing issue when you view the report? The data tab is also getting >> schema information not just the data. > > Yes same problem when viewing/rendering the report (through the Designer > preview and when uploaded to the Rpt Manager) > > > As promised above, heres the code: > > SET QUOTED_IDENTIFIER ON > GO > SET ANSI_NULLS ON > GO > > /****** Object: Stored Procedure dbo.SalesOrderEntry Script Date: > 10/08/2005 2:38:39 p.m. ******/ > > > ALTER PROCEDURE SalesOrderEntry > > -- These variables are passed to SQL by the Reporting Services report. The > default setting is '%' which is the > -- wildcard for select all. > @BRANCH VARCHAR(3) = '%', > @CUSTNAME VARCHAR(50) = '%', > @DEALERNAME VARCHAR(50) = '%', > @STOCKCODE VARCHAR(50) = '%', > @MONTH VARCHAR(3) = '%', > @YEAR VARCHAR(6) = '%', > @STOCK_CATEGORY VARCHAR(3) = '%' > > AS > > -- The default value that Reporting Services passes to SQL is the string > value 'ALL' This group of IF statements > -- changes the value of parameter to the wildcard value '%' > IF @BRANCH LIKE '%ALL%' > BEGIN > SET @BRANCH = '%' > END > > IF @CUSTNAME LIKE '%ALL%' > BEGIN > SET @CUSTNAME = '%' > END > > IF @DEALERNAME LIKE '%ALL%' > BEGIN > SET @DEALERNAME = '%' > END > > IF @STOCKCODE LIKE '%ALL%' > BEGIN > SET @STOCKCODE = '%' > END > > IF @MONTH LIKE '%ALL%' > BEGIN > SET @MONTH = '%' > END > > IF @YEAR LIKE '%ALL%' > BEGIN > SET @YEAR = '%' > END > > IF @STOCK_CATEGORY LIKE '%ALL%' > BEGIN > SET @STOCK_CATEGORY = '%' > END > > SELECT TOP 100 PERCENT > dbo.OEMASTER.DEL_DATE AS [Despatch Date], > dbo.OEMASTER.INV_NO AS [Invoice Number], > dbo.OEPSLINE.NUMBER AS [Packing Slip Number], > dbo.OEMASTER.BRANCH, > dbo.ARMASTER.NAME AS [Dealer Name], > dbo.OEMASTER.CUST_ORDER AS [Dealer Order Num], > dbo.OEMASTER.DEL_NAME AS [Customer Name], > dbo.OEMASTER.DEL_ADDR1, > dbo.OEMASTER.DEL_ADDR2, > dbo.OEPSLINE.STOCK_CODE, > dbo.INMASTER.CATEGORY, > dbo.INSLTRAN.SERIAL_LOT_NO, > CASE WHEN dbo.INSLTRAN.SERIAL_LOT_NO IS NULL THEN > dbo.OEPSLINE.QTY_SUPP_SEL > ELSE 1 END AS [QTY SOLD], > > CASE WHEN dbo.INSLTRAN.SERIAL_LOT_NO IS NULL THEN > dbo.OEPSLINE.QTY_SUPP_SEL > * dbo.OEPSLINE.PRICE ELSE 1 * dbo.OEPSLINE.PRICE END AS [List Price], > > CAST(dbo.OEPSLINE.DISCRATE AS VARCHAR) + ' %' AS Discount, > > CASE WHEN dbo.INSLTRAN.SERIAL_LOT_NO IS NULL THEN > (dbo.OEPSLINE.QTY_SUPP_SEL > * dbo.OEPSLINE.PRICE) * (1 - dbo.OEPSLINE.DISCRATE / 100) ELSE (1 * > dbo.OEPSLINE.PRICE) * (1 - dbo.OEPSLINE.DISCRATE / 100) END AS [Net Price] > > -- INSLTRAN.REF <-- OEPSLINE.STOCK_CODE(ALL) > -- INSLTRAN.STOCKCODE <-- OEPSLINE.NUMBER(ALL) > > FROM dbo.INMASTER RIGHT OUTER JOIN > dbo.OEPSLINE ON dbo.INMASTER.CODE = > dbo.OEPSLINE.STOCK_CODE LEFT OUTER JOIN > dbo.INSLTRAN ON dbo.OEPSLINE.STOCK_CODE = > dbo.INSLTRAN.STOCKCODE AND CAST(dbo.OEPSLINE.NUMBER AS VARCHAR(50)) > = dbo.INSLTRAN.REF RIGHT OUTER JOIN > dbo.OEMASTER ON dbo.OEPSLINE.ORDER_NO = > dbo.OEMASTER.NUMBER LEFT OUTER JOIN > dbo.OEPSHDR ON dbo.OEMASTER.NUMBER = > dbo.OEPSHDR.ORDER_NO LEFT OUTER JOIN > dbo.ARMASTER ON dbo.OEMASTER.CUST_NO = > dbo.ARMASTER.NUMBER > > WHERE > -- Bakery or Catering only > (dbo.OEMASTER.BRANCH LIKE N'NB' OR dbo.OEMASTER.BRANCH = N'NC') > -- Records in INSLTRAN that have no Serial Lot No are not included > (therefore the test for NULL values) > AND (dbo.INSLTRAN.[TRAN] LIKE N'OEORD' OR dbo.INSLTRAN.[TRAN] IS NULL) > -- Only Invoiced values > AND (dbo.OEMASTER.STATUS = N'I') > -- removes erroneously entered values > AND NOT ((dbo.INSLTRAN.[TRAN] LIKE N'OEORD') AND > (dbo.OEPSLINE.QTY_SUPP_SEL > = 0 )) > > -- Force parameters > AND (OEMASTER.BRANCH LIKE @BRANCH) > AND OEMASTER.DEL_NAME LIKE @CUSTNAME > AND ARMASTER.[NAME] LIKE @DEALERNAME > AND OEPSLINE.STOCK_CODE LIKE @STOCKCODE > AND MONTH(dbo.OEMASTER.DEL_DATE) LIKE @MONTH > AND (CAST(YEAR(DEL_DATE) AS VARCHAR)) LIKE @YEAR > AND dbo.INMASTER.CATEGORY LIKE @STOCK_CATEGORY > > GROUP BY dbo.ARMASTER.[NAME], dbo.OEMASTER.DEL_DATE, > dbo.OEMASTER.CUST_ORDER, dbo.OEMASTER.NUMBER, dbo.OEMASTER.INV_NO, > dbo.OEPSLINE.STOCK_CODE, dbo.OEMASTER.DEL_NAME, > dbo.OEMASTER.DEL_ADDR1, dbo.OEMASTER.DEL_ADDR2, dbo.OEMASTER.STATUS, > dbo.OEMASTER.BRANCH, dbo.OEPSLINE.NUMBER, > dbo.INSLTRAN.SERIAL_LOT_NO, dbo.OEPSLINE.QTY_SUPP_SEL, dbo.OEPSLINE.PRICE, > dbo.OEPSLINE.DISCRATE, dbo.INSLTRAN.[TRAN], > dbo.INMASTER.CATEGORY > > ORDER BY dbo.OEMASTER.BRANCH, dbo.OEPSLINE.NUMBER > > GO > > SET QUOTED_IDENTIFIER OFF > GO > SET ANSI_NULLS ON > GO > >
Sorry, no solution but I just want to say I've seen this too... The exact
same stored procedure when called from RS runs an order of magnitude slower than direct from query analyzer and the execution log determines it to be the query time. This is only for some sprocs though not all and rewriting the sproc to produce a new query plan didn't help either. Regards, James Snape [quoted text, click to view] "Casey" <Casey@discussions.microsoft.com> wrote in message news:B17C5B95-E50D-415C-A286-6141B4079428@microsoft.com... > Hi, > > I'm having huge problems with the run-time of a stored procedure from RS > Designer (and from the server when the report is uploaded). > > I have a SP with about 6 parameters. When executed from QA the SP takes > less > than a second to return a result-set of about 1500 rows. When running the > SP > from RS (Designer > Data tab) using query parameters the excution time is > around 3 mins !!! > > Looking at the TimeDataRetrieval field in ExecutionLog table in the > ReportServer DB, the SP is the problem (consuming about 99% of the time > involved in the report generation). I've tried adding indexes, I'm not > using > report filters.... > > Are there any known problems with RS in executing SPs with multiple params > or JOINs ...?? > > Help! :) > > Thanks in advance, > > Casey.
[quoted text, click to view]
> Get rid of the code checking for %All% and do this instead. > Have the default be All > @BRANCH VARCHAR(3) = 'All' > > Just pass in the word All for your parameter value and change the code to > look like this. > AND (OEMASTER.BRANCH = @BRANCH or @BRANCH = 'All') > This is neat. I've been doing it like this: --default is NULL AND OEMASTER.BRANCH = COALESCE(@BRANCH, OEMASTER.BRANCH) I get the feeling that your way is more optimal. Any comments before I experiment? Tim
Nope, no further comments. I am definitely interested in whether changing
this part of the SP makes a difference. -- Bruce Loehle-Conger MVP SQL Server Reporting Services [quoted text, click to view] "timseal" <timseal@gmail.com> wrote in message news:1124806729.792746.187260@g47g2000cwa.googlegroups.com... >> Get rid of the code checking for %All% and do this instead. >> Have the default be All >> @BRANCH VARCHAR(3) = 'All' >> >> Just pass in the word All for your parameter value and change the code to >> look like this. >> AND (OEMASTER.BRANCH = @BRANCH or @BRANCH = 'All') >> > > This is neat. I've been doing it like this: > --default is NULL > AND OEMASTER.BRANCH = COALESCE(@BRANCH, OEMASTER.BRANCH) > > I get the feeling that your way is more optimal. Any comments before I > experiment? > > Tim >
Bruce gave good advice, I think - I would add that it would be better
still to replace lines like: AND (OEMASTER.BRANCH = @BRANCH or @BRANCH = 'All') with AND (@BRANCH = 'All' or OEMASTER.BRANCH = @BRANCH) This is so the database comparison is only made if needed - if the first comparison is true, the second comparison is not evaluated - and the first comparison is evaluated a LOT faster since it does not have to make a round trip to the database. Also, adding 'WITH RECOMPILE' to the start of the stored procedure will force it to recompile every time it is run - this can help if the parameter values you are using change greatly from run to run, in that it keeps the SP from using a cached execution plan that may no longer be appropriate. The potential drawback is that objects in the SP will be (very) briefly locked during the recompile - but we have not found this to be a problem in practice.
I am experiencing this problem also. My query runs fast when the sp is
executed through query analyzer. Through RS however the query runs slow. Adding "with recompile" did fix for the problem - for the moment. Can anyone who knows a little more about RS hypothesize why it would take so much longer in RS vs Query Analyzer? -- JS [quoted text, click to view] "Casey" wrote:
> Hi, > > I'm having huge problems with the run-time of a stored procedure from RS > Designer (and from the server when the report is uploaded). > > I have a SP with about 6 parameters. When executed from QA the SP takes less > than a second to return a result-set of about 1500 rows. When running the SP > from RS (Designer > Data tab) using query parameters the excution time is > around 3 mins !!! > > Looking at the TimeDataRetrieval field in ExecutionLog table in the > ReportServer DB, the SP is the problem (consuming about 99% of the time > involved in the report generation). I've tried adding indexes, I'm not using > report filters.... > > Are there any known problems with RS in executing SPs with multiple params > or JOINs ...?? > > Help! :) > > Thanks in advance, >
hi casey
don't know if your SP is still and issue? as a 'rule of thumb' I always (with SRSS and SPs) do the following: 1. SET NOCOUNT NO 2. add 'WITH (NOLOCK)' after each (TABLE/VIEW/etc.) referenced in the SQL 3. if the SQL is consistent in it's composition, create a VIEW I run HUGE SPs which dynamically create PIVOT/CROSSTAB SQL 'on the fly' on much less hardware muscle and get GREAT performance (what they had running in Access would take 15-20 minutes - I can get report results in under 3 seconds). Industry is big 3 automotive, so I know this is about a tough as it gets for size and amount of crunching. Rob [quoted text, click to view] "Casey" wrote:
> Hi Bruce, thanks for the reply! > > To answer your questions... > > > I use lots of stored procedures. Both against SQL Server and against Sybase. > > I have never seen this. > > I use SPs for for most of my RS reporting, this is only problem SP I have > encountered. > > > Here's a few questions that might help us determine what is happening. > > > > What are you doing for credentials? > > Straight "Windows NT Integrated Security". > > > Is the stored procedure using linked databases? > > No. RS and SQL sitting on the same server (3.2GHz Hyperthreading Xeon with > 3GB RAM) and I'm sure load ain't the problem. > > > Is the stored procedure using dynamic sql? > > No. I'll copy the entire SP at the bottom of this message. > > > Same timing issue when you view the report? The data tab is also getting > > schema information not just the data. > > Yes same problem when viewing/rendering the report (through the Designer > preview and when uploaded to the Rpt Manager) > > > As promised above, heres the code: > > SET QUOTED_IDENTIFIER ON > GO > SET ANSI_NULLS ON > GO > > /****** Object: Stored Procedure dbo.SalesOrderEntry Script Date: > 10/08/2005 2:38:39 p.m. ******/ > > > ALTER PROCEDURE SalesOrderEntry > > -- These variables are passed to SQL by the Reporting Services report. The > default setting is '%' which is the > -- wildcard for select all. > @BRANCH VARCHAR(3) = '%', > @CUSTNAME VARCHAR(50) = '%', > @DEALERNAME VARCHAR(50) = '%', > @STOCKCODE VARCHAR(50) = '%', > @MONTH VARCHAR(3) = '%', > @YEAR VARCHAR(6) = '%', > @STOCK_CATEGORY VARCHAR(3) = '%' > > AS > > -- The default value that Reporting Services passes to SQL is the string > value 'ALL' This group of IF statements > -- changes the value of parameter to the wildcard value '%' > IF @BRANCH LIKE '%ALL%' > BEGIN > SET @BRANCH = '%' > END > > IF @CUSTNAME LIKE '%ALL%' > BEGIN > SET @CUSTNAME = '%' > END > > IF @DEALERNAME LIKE '%ALL%' > BEGIN > SET @DEALERNAME = '%' > END > > IF @STOCKCODE LIKE '%ALL%' > BEGIN > SET @STOCKCODE = '%' > END > > IF @MONTH LIKE '%ALL%' > BEGIN > SET @MONTH = '%' > END > > IF @YEAR LIKE '%ALL%' > BEGIN > SET @YEAR = '%' > END > > IF @STOCK_CATEGORY LIKE '%ALL%' > BEGIN > SET @STOCK_CATEGORY = '%' > END > > SELECT TOP 100 PERCENT > dbo.OEMASTER.DEL_DATE AS [Despatch Date], > dbo.OEMASTER.INV_NO AS [Invoice Number], > dbo.OEPSLINE.NUMBER AS [Packing Slip Number], > dbo.OEMASTER.BRANCH, > dbo.ARMASTER.NAME AS [Dealer Name], > dbo.OEMASTER.CUST_ORDER AS [Dealer Order Num], > dbo.OEMASTER.DEL_NAME AS [Customer Name], > dbo.OEMASTER.DEL_ADDR1, > dbo.OEMASTER.DEL_ADDR2, > dbo.OEPSLINE.STOCK_CODE, > dbo.INMASTER.CATEGORY, > dbo.INSLTRAN.SERIAL_LOT_NO, > CASE WHEN dbo.INSLTRAN.SERIAL_LOT_NO IS NULL THEN dbo.OEPSLINE.QTY_SUPP_SEL > ELSE 1 END AS [QTY SOLD], > > CASE WHEN dbo.INSLTRAN.SERIAL_LOT_NO IS NULL THEN dbo.OEPSLINE.QTY_SUPP_SEL > * dbo.OEPSLINE.PRICE ELSE 1 * dbo.OEPSLINE.PRICE END AS [List Price], > > CAST(dbo.OEPSLINE.DISCRATE AS VARCHAR) + ' %' AS Discount, > > CASE WHEN dbo.INSLTRAN.SERIAL_LOT_NO IS NULL THEN (dbo.OEPSLINE.QTY_SUPP_SEL > * dbo.OEPSLINE.PRICE) * (1 - dbo.OEPSLINE.DISCRATE / 100) ELSE (1 * > dbo.OEPSLINE.PRICE) * (1 - dbo.OEPSLINE.DISCRATE / 100) END AS [Net Price] > > -- INSLTRAN.REF <-- OEPSLINE.STOCK_CODE(ALL) > -- INSLTRAN.STOCKCODE <-- OEPSLINE.NUMBER(ALL) > > FROM dbo.INMASTER RIGHT OUTER JOIN > dbo.OEPSLINE ON dbo.INMASTER.CODE = > dbo.OEPSLINE.STOCK_CODE LEFT OUTER JOIN > dbo.INSLTRAN ON dbo.OEPSLINE.STOCK_CODE = > dbo.INSLTRAN.STOCKCODE AND CAST(dbo.OEPSLINE.NUMBER AS VARCHAR(50)) > = dbo.INSLTRAN.REF RIGHT OUTER JOIN > dbo.OEMASTER ON dbo.OEPSLINE.ORDER_NO = > dbo.OEMASTER.NUMBER LEFT OUTER JOIN > dbo.OEPSHDR ON dbo.OEMASTER.NUMBER = > dbo.OEPSHDR.ORDER_NO LEFT OUTER JOIN > dbo.ARMASTER ON dbo.OEMASTER.CUST_NO = > dbo.ARMASTER.NUMBER > > WHERE > -- Bakery or Catering only > (dbo.OEMASTER.BRANCH LIKE N'NB' OR dbo.OEMASTER.BRANCH = N'NC') > -- Records in INSLTRAN that have no Serial Lot No are not included > (therefore the test for NULL values) > AND (dbo.INSLTRAN.[TRAN] LIKE N'OEORD' OR dbo.INSLTRAN.[TRAN] IS NULL) > -- Only Invoiced values > AND (dbo.OEMASTER.STATUS = N'I') > -- removes erroneously entered values > AND NOT ((dbo.INSLTRAN.[TRAN] LIKE N'OEORD') AND (dbo.OEPSLINE.QTY_SUPP_SEL > = 0 )) > > -- Force parameters > AND (OEMASTER.BRANCH LIKE @BRANCH) > AND OEMASTER.DEL_NAME LIKE @CUSTNAME > AND ARMASTER.[NAME] LIKE @DEALERNAME > AND OEPSLINE.STOCK_CODE LIKE @STOCKCODE > AND MONTH(dbo.OEMASTER.DEL_DATE) LIKE @MONTH > AND (CAST(YEAR(DEL_DATE) AS VARCHAR)) LIKE @YEAR > AND dbo.INMASTER.CATEGORY LIKE @STOCK_CATEGORY > > GROUP BY dbo.ARMASTER.[NAME], dbo.OEMASTER.DEL_DATE, > dbo.OEMASTER.CUST_ORDER, dbo.OEMASTER.NUMBER, dbo.OEMASTER.INV_NO, > dbo.OEPSLINE.STOCK_CODE, dbo.OEMASTER.DEL_NAME, > dbo.OEMASTER.DEL_ADDR1, dbo.OEMASTER.DEL_ADDR2, dbo.OEMASTER.STATUS, > dbo.OEMASTER.BRANCH, dbo.OEPSLINE.NUMBER, > dbo.INSLTRAN.SERIAL_LOT_NO, dbo.OEPSLINE.QTY_SUPP_SEL, dbo.OEPSLINE.PRICE, > dbo.OEPSLINE.DISCRATE, dbo.INSLTRAN.[TRAN], > dbo.INMASTER.CATEGORY > > ORDER BY dbo.OEMASTER.BRANCH, dbo.OEPSLINE.NUMBER > > GO > > SET QUOTED_IDENTIFIER OFF > GO > SET ANSI_NULLS ON > GO >
Just an FYI. I think the issue was resolved by using a WITH RECOMPILE in the
sp definition. Good list of suggestions. -- Bruce Loehle-Conger MVP SQL Server Reporting Services [quoted text, click to view] "tutor" <tutor@discussions.microsoft.com> wrote in message news:F423E025-1767-4290-80D0-F831BE59BCBE@microsoft.com... > hi casey > > don't know if your SP is still and issue? > > as a 'rule of thumb' I always (with SRSS and SPs) do the following: > > 1. SET NOCOUNT NO > 2. add 'WITH (NOLOCK)' after each (TABLE/VIEW/etc.) referenced in the SQL > 3. if the SQL is consistent in it's composition, create a VIEW > > I run HUGE SPs which dynamically create PIVOT/CROSSTAB SQL 'on the fly' on > much less hardware muscle and get GREAT performance (what they had running > in > Access would take 15-20 minutes - I can get report results in under 3 > seconds). > > Industry is big 3 automotive, so I know this is about a tough as it gets > for > size and amount of crunching. > > Rob > > > > "Casey" wrote: > >> Hi Bruce, thanks for the reply! >> >> To answer your questions... >> >> > I use lots of stored procedures. Both against SQL Server and against >> > Sybase. >> > I have never seen this. >> >> I use SPs for for most of my RS reporting, this is only problem SP I have >> encountered. >> >> > Here's a few questions that might help us determine what is happening. >> > >> > What are you doing for credentials? >> >> Straight "Windows NT Integrated Security". >> >> > Is the stored procedure using linked databases? >> >> No. RS and SQL sitting on the same server (3.2GHz Hyperthreading Xeon >> with >> 3GB RAM) and I'm sure load ain't the problem. >> >> > Is the stored procedure using dynamic sql? >> >> No. I'll copy the entire SP at the bottom of this message. >> >> > Same timing issue when you view the report? The data tab is also >> > getting >> > schema information not just the data. >> >> Yes same problem when viewing/rendering the report (through the Designer >> preview and when uploaded to the Rpt Manager) >> >> >> As promised above, heres the code: >> >> SET QUOTED_IDENTIFIER ON >> GO >> SET ANSI_NULLS ON >> GO >> >> /****** Object: Stored Procedure dbo.SalesOrderEntry Script Date: >> 10/08/2005 2:38:39 p.m. ******/ >> >> >> ALTER PROCEDURE SalesOrderEntry >> >> -- These variables are passed to SQL by the Reporting Services report. >> The >> default setting is '%' which is the >> -- wildcard for select all. >> @BRANCH VARCHAR(3) = '%', >> @CUSTNAME VARCHAR(50) = '%', >> @DEALERNAME VARCHAR(50) = '%', >> @STOCKCODE VARCHAR(50) = '%', >> @MONTH VARCHAR(3) = '%', >> @YEAR VARCHAR(6) = '%', >> @STOCK_CATEGORY VARCHAR(3) = '%' >> >> AS >> >> -- The default value that Reporting Services passes to SQL is the string >> value 'ALL' This group of IF statements >> -- changes the value of parameter to the wildcard value '%' >> IF @BRANCH LIKE '%ALL%' >> BEGIN >> SET @BRANCH = '%' >> END >> >> IF @CUSTNAME LIKE '%ALL%' >> BEGIN >> SET @CUSTNAME = '%' >> END >> >> IF @DEALERNAME LIKE '%ALL%' >> BEGIN >> SET @DEALERNAME = '%' >> END >> >> IF @STOCKCODE LIKE '%ALL%' >> BEGIN >> SET @STOCKCODE = '%' >> END >> >> IF @MONTH LIKE '%ALL%' >> BEGIN >> SET @MONTH = '%' >> END >> >> IF @YEAR LIKE '%ALL%' >> BEGIN >> SET @YEAR = '%' >> END >> >> IF @STOCK_CATEGORY LIKE '%ALL%' >> BEGIN >> SET @STOCK_CATEGORY = '%' >> END >> >> SELECT TOP 100 PERCENT >> dbo.OEMASTER.DEL_DATE AS [Despatch Date], >> dbo.OEMASTER.INV_NO AS [Invoice Number], >> dbo.OEPSLINE.NUMBER AS [Packing Slip Number], >> dbo.OEMASTER.BRANCH, >> dbo.ARMASTER.NAME AS [Dealer Name], >> dbo.OEMASTER.CUST_ORDER AS [Dealer Order Num], >> dbo.OEMASTER.DEL_NAME AS [Customer Name], >> dbo.OEMASTER.DEL_ADDR1, >> dbo.OEMASTER.DEL_ADDR2, >> dbo.OEPSLINE.STOCK_CODE, >> dbo.INMASTER.CATEGORY, >> dbo.INSLTRAN.SERIAL_LOT_NO, >> CASE WHEN dbo.INSLTRAN.SERIAL_LOT_NO IS NULL THEN >> dbo.OEPSLINE.QTY_SUPP_SEL >> ELSE 1 END AS [QTY SOLD], >> >> CASE WHEN dbo.INSLTRAN.SERIAL_LOT_NO IS NULL THEN >> dbo.OEPSLINE.QTY_SUPP_SEL >> * dbo.OEPSLINE.PRICE ELSE 1 * dbo.OEPSLINE.PRICE END AS [List Price], >> >> CAST(dbo.OEPSLINE.DISCRATE AS VARCHAR) + ' %' AS Discount, >> >> CASE WHEN dbo.INSLTRAN.SERIAL_LOT_NO IS NULL THEN >> (dbo.OEPSLINE.QTY_SUPP_SEL >> * dbo.OEPSLINE.PRICE) * (1 - dbo.OEPSLINE.DISCRATE / 100) ELSE (1 * >> dbo.OEPSLINE.PRICE) * (1 - dbo.OEPSLINE.DISCRATE / 100) END AS [Net >> Price] >> >> -- INSLTRAN.REF <-- OEPSLINE.STOCK_CODE(ALL) >> -- INSLTRAN.STOCKCODE <-- OEPSLINE.NUMBER(ALL) >> >> FROM dbo.INMASTER RIGHT OUTER JOIN >> dbo.OEPSLINE ON dbo.INMASTER.CODE = >> dbo.OEPSLINE.STOCK_CODE LEFT OUTER JOIN >> dbo.INSLTRAN ON dbo.OEPSLINE.STOCK_CODE = >> dbo.INSLTRAN.STOCKCODE AND CAST(dbo.OEPSLINE.NUMBER AS VARCHAR(50)) >> = dbo.INSLTRAN.REF RIGHT OUTER JOIN >> dbo.OEMASTER ON dbo.OEPSLINE.ORDER_NO = >> dbo.OEMASTER.NUMBER LEFT OUTER JOIN >> dbo.OEPSHDR ON dbo.OEMASTER.NUMBER = >> dbo.OEPSHDR.ORDER_NO LEFT OUTER JOIN >> dbo.ARMASTER ON dbo.OEMASTER.CUST_NO = >> dbo.ARMASTER.NUMBER >> >> WHERE >> -- Bakery or Catering only >> (dbo.OEMASTER.BRANCH LIKE N'NB' OR dbo.OEMASTER.BRANCH = N'NC') >> -- Records in INSLTRAN that have no Serial Lot No are not included >> (therefore the test for NULL values) >> AND (dbo.INSLTRAN.[TRAN] LIKE N'OEORD' OR dbo.INSLTRAN.[TRAN] IS NULL) >> -- Only Invoiced values >> AND (dbo.OEMASTER.STATUS = N'I') >> -- removes erroneously entered values >> AND NOT ((dbo.INSLTRAN.[TRAN] LIKE N'OEORD') AND >> (dbo.OEPSLINE.QTY_SUPP_SEL >> = 0 )) >> >> -- Force parameters >> AND (OEMASTER.BRANCH LIKE @BRANCH) >> AND OEMASTER.DEL_NAME LIKE @CUSTNAME >> AND ARMASTER.[NAME] LIKE @DEALERNAME >> AND OEPSLINE.STOCK_CODE LIKE @STOCKCODE >> AND MONTH(dbo.OEMASTER.DEL_DATE) LIKE @MONTH >> AND (CAST(YEAR(DEL_DATE) AS VARCHAR)) LIKE @YEAR >> AND dbo.INMASTER.CATEGORY LIKE @STOCK_CATEGORY >> >> GROUP BY dbo.ARMASTER.[NAME], dbo.OEMASTER.DEL_DATE, >> dbo.OEMASTER.CUST_ORDER, dbo.OEMASTER.NUMBER, dbo.OEMASTER.INV_NO, >> dbo.OEPSLINE.STOCK_CODE, dbo.OEMASTER.DEL_NAME, >> dbo.OEMASTER.DEL_ADDR1, dbo.OEMASTER.DEL_ADDR2, dbo.OEMASTER.STATUS, >> dbo.OEMASTER.BRANCH, dbo.OEPSLINE.NUMBER, >> dbo.INSLTRAN.SERIAL_LOT_NO, dbo.OEPSLINE.QTY_SUPP_SEL, >> dbo.OEPSLINE.PRICE, >> dbo.OEPSLINE.DISCRATE, dbo.INSLTRAN.[TRAN], >> dbo.INMASTER.CATEGORY >> >> ORDER BY dbo.OEMASTER.BRANCH, dbo.OEPSLINE.NUMBER >> >> GO >> >> SET QUOTED_IDENTIFIER OFF >> GO >> SET ANSI_NULLS ON >> GO >> >>
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |