Hi all,
I have a SPROC that returns 773,923 rows and takes 13mins to execute in SQL
Query Analyzer.
I think I have increased all available timeout and session settings, but the
report returns page cannot be displayed.
The same happens when I try to create a snapshot.
Any ideas?
Thank you in advance.
Cheers
ShaneThis is a very bad idea. RS renders everything in RAM. Depending on the
format it can take varying amounts of time. If you try to render to PDF or
Excel (can Excel even take that many rows, I'm not sure what the limit is
now, it was 64K in Excel 2000) it will most likely never finish. What are
you trying to do, a human will never look at that many rows. If you are
trying to do data extraction then there are better technologies to use. HTML
is fast and might be possible but not sure what it buys you since this is
probably 100,000 rows of data. I suggest reading up on drill through, that
is a very good technique for navigating large amounts of data. If you
explain what you are trying to do there might be some other way to
accomplish it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Shane" <s.dixon@.hia.asn.auNOSPAM> wrote in message
news:86D96692-5AD5-4BC0-953D-99A53D623AE3@.microsoft.com...
> Hi all,
> I have a SPROC that returns 773,923 rows and takes 13mins to execute in
> SQL
> Query Analyzer.
> I think I have increased all available timeout and session settings, but
> the
> report returns page cannot be displayed.
> The same happens when I try to create a snapshot.
> Any ideas?
> Thank you in advance.
> Cheers
> Shane|||Thanks for taking the time to read my post Bruce.
The report is an age trial balance for our entire organisation. So each line
(all 773,923 rows) are invoice lines.
It allows our snr management to see what debt are owed to our company.
I use a drill down so when the report is initially loaded you see each of my
company's regions (10 of them) and the sum of the money owed by age 30 Days,
60-90 Days, 120+ Days...
If you click on the + it expands to reveal the different general leger
accounts and the sum of the money owed by age 30 Days, 60-90 Days, 120+
Days...
If you click on the + again, it will display the person who owes the money
and the sum of the money owed by age 30 Days, 60-90 Days, 120+ Days...
If you click on the + again, it will display each of the invoice details..
The report is being rendered in HTML and I also want the execution snapshot
to be rendered in HTML as well.
Cheers,
Shane
"Bruce L-C [MVP]" wrote:
> This is a very bad idea. RS renders everything in RAM. Depending on the
> format it can take varying amounts of time. If you try to render to PDF or
> Excel (can Excel even take that many rows, I'm not sure what the limit is
> now, it was 64K in Excel 2000) it will most likely never finish. What are
> you trying to do, a human will never look at that many rows. If you are
> trying to do data extraction then there are better technologies to use. HTML
> is fast and might be possible but not sure what it buys you since this is
> probably 100,000 rows of data. I suggest reading up on drill through, that
> is a very good technique for navigating large amounts of data. If you
> explain what you are trying to do there might be some other way to
> accomplish it.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Shane" <s.dixon@.hia.asn.auNOSPAM> wrote in message
> news:86D96692-5AD5-4BC0-953D-99A53D623AE3@.microsoft.com...
> > Hi all,
> >
> > I have a SPROC that returns 773,923 rows and takes 13mins to execute in
> > SQL
> > Query Analyzer.
> >
> > I think I have increased all available timeout and session settings, but
> > the
> > report returns page cannot be displayed.
> >
> > The same happens when I try to create a snapshot.
> >
> > Any ideas?
> >
> > Thank you in advance.
> >
> > Cheers
> >
> > Shane
>
>|||OK, that is what I thought. Drill downs are great. Very intuitive.
Unfortunately they require all the data to be present. So although it is
great for the user it is not so a great a solution for speed. What I suggest
is to change this to limit the amount of data. Perhaps make it a combination
of drilldown and drillthrough. For instance, how much data is decreased if
you do a drillthrough for the involice details. Or perhaps the Person and
the invoice details. Make that it's own report with the appropriate
parameters and then drill through to it. My guess is that the sp would speed
up tremendously and the amount of data being dealt with by RS would drop to
a manageable level. My users (a very wide range from production operators to
management) have found drillthough to be very intuitive.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Shane" <s.dixon@.hia.asn.auNOSPAM> wrote in message
news:3A3D9583-DF23-43B9-806F-0B4DD3A68F12@.microsoft.com...
> Thanks for taking the time to read my post Bruce.
> The report is an age trial balance for our entire organisation. So each
> line
> (all 773,923 rows) are invoice lines.
> It allows our snr management to see what debt are owed to our company.
> I use a drill down so when the report is initially loaded you see each of
> my
> company's regions (10 of them) and the sum of the money owed by age 30
> Days,
> 60-90 Days, 120+ Days...
> If you click on the + it expands to reveal the different general leger
> accounts and the sum of the money owed by age 30 Days, 60-90 Days, 120+
> Days...
> If you click on the + again, it will display the person who owes the money
> and the sum of the money owed by age 30 Days, 60-90 Days, 120+ Days...
> If you click on the + again, it will display each of the invoice details..
> The report is being rendered in HTML and I also want the execution
> snapshot
> to be rendered in HTML as well.
> Cheers,
> Shane
> "Bruce L-C [MVP]" wrote:
>> This is a very bad idea. RS renders everything in RAM. Depending on the
>> format it can take varying amounts of time. If you try to render to PDF
>> or
>> Excel (can Excel even take that many rows, I'm not sure what the limit is
>> now, it was 64K in Excel 2000) it will most likely never finish. What are
>> you trying to do, a human will never look at that many rows. If you are
>> trying to do data extraction then there are better technologies to use.
>> HTML
>> is fast and might be possible but not sure what it buys you since this is
>> probably 100,000 rows of data. I suggest reading up on drill through,
>> that
>> is a very good technique for navigating large amounts of data. If you
>> explain what you are trying to do there might be some other way to
>> accomplish it.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Shane" <s.dixon@.hia.asn.auNOSPAM> wrote in message
>> news:86D96692-5AD5-4BC0-953D-99A53D623AE3@.microsoft.com...
>> > Hi all,
>> >
>> > I have a SPROC that returns 773,923 rows and takes 13mins to execute in
>> > SQL
>> > Query Analyzer.
>> >
>> > I think I have increased all available timeout and session settings,
>> > but
>> > the
>> > report returns page cannot be displayed.
>> >
>> > The same happens when I try to create a snapshot.
>> >
>> > Any ideas?
>> >
>> > Thank you in advance.
>> >
>> > Cheers
>> >
>> > Shane
>>|||Thanks again Bruce.
I think the drillthrough idea sounds like a good one. Similar to this Sample
report:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSAMPLES/htm/rss_samplereports_v1_4z95.asp
I'll give it a go and let you know how I go! :)
Cheers,
Shane
"Bruce L-C [MVP]" wrote:
> OK, that is what I thought. Drill downs are great. Very intuitive.
> Unfortunately they require all the data to be present. So although it is
> great for the user it is not so a great a solution for speed. What I suggest
> is to change this to limit the amount of data. Perhaps make it a combination
> of drilldown and drillthrough. For instance, how much data is decreased if
> you do a drillthrough for the involice details. Or perhaps the Person and
> the invoice details. Make that it's own report with the appropriate
> parameters and then drill through to it. My guess is that the sp would speed
> up tremendously and the amount of data being dealt with by RS would drop to
> a manageable level. My users (a very wide range from production operators to
> management) have found drillthough to be very intuitive.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Shane" <s.dixon@.hia.asn.auNOSPAM> wrote in message
> news:3A3D9583-DF23-43B9-806F-0B4DD3A68F12@.microsoft.com...
> > Thanks for taking the time to read my post Bruce.
> >
> > The report is an age trial balance for our entire organisation. So each
> > line
> > (all 773,923 rows) are invoice lines.
> >
> > It allows our snr management to see what debt are owed to our company.
> >
> > I use a drill down so when the report is initially loaded you see each of
> > my
> > company's regions (10 of them) and the sum of the money owed by age 30
> > Days,
> > 60-90 Days, 120+ Days...
> >
> > If you click on the + it expands to reveal the different general leger
> > accounts and the sum of the money owed by age 30 Days, 60-90 Days, 120+
> > Days...
> >
> > If you click on the + again, it will display the person who owes the money
> > and the sum of the money owed by age 30 Days, 60-90 Days, 120+ Days...
> >
> > If you click on the + again, it will display each of the invoice details..
> >
> > The report is being rendered in HTML and I also want the execution
> > snapshot
> > to be rendered in HTML as well.
> >
> > Cheers,
> >
> > Shane
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> This is a very bad idea. RS renders everything in RAM. Depending on the
> >> format it can take varying amounts of time. If you try to render to PDF
> >> or
> >> Excel (can Excel even take that many rows, I'm not sure what the limit is
> >> now, it was 64K in Excel 2000) it will most likely never finish. What are
> >> you trying to do, a human will never look at that many rows. If you are
> >> trying to do data extraction then there are better technologies to use.
> >> HTML
> >> is fast and might be possible but not sure what it buys you since this is
> >> probably 100,000 rows of data. I suggest reading up on drill through,
> >> that
> >> is a very good technique for navigating large amounts of data. If you
> >> explain what you are trying to do there might be some other way to
> >> accomplish it.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Shane" <s.dixon@.hia.asn.auNOSPAM> wrote in message
> >> news:86D96692-5AD5-4BC0-953D-99A53D623AE3@.microsoft.com...
> >> > Hi all,
> >> >
> >> > I have a SPROC that returns 773,923 rows and takes 13mins to execute in
> >> > SQL
> >> > Query Analyzer.
> >> >
> >> > I think I have increased all available timeout and session settings,
> >> > but
> >> > the
> >> > report returns page cannot be displayed.
> >> >
> >> > The same happens when I try to create a snapshot.
> >> >
> >> > Any ideas?
> >> >
> >> > Thank you in advance.
> >> >
> >> > Cheers
> >> >
> >> > Shane
> >>
> >>
> >>
>
>
Showing posts with label analyzer. Show all posts
Showing posts with label analyzer. Show all posts
Wednesday, March 21, 2012
Tuesday, March 20, 2012
Report taking a long time to render
I have a somewhat complex query that takes about 2 seconds to run in
Query Analyzer. Yet it takes a full 30 seconds to generate the
report. If I remove the document map then it takes ~10 seconds. It
seems that there is just tremendous overhead or some other problem
when generating reports.
Does anyone have any suggestions how I can speed this up? We need up
to the minute data so I can't really use a snapshot.
Thanks!
Matt PennerOn Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
> I have a somewhat complex query that takes about 2 seconds to run in
> Query Analyzer. Yet it takes a full 30 seconds to generate the
> report. If I remove the document map then it takes ~10 seconds. It
> seems that there is just tremendous overhead or some other problem
> when generating reports.
> Does anyone have any suggestions how I can speed this up? We need up
> to the minute data so I can't really use a snapshot.
> Thanks!
> Matt Penner
In general, a deployed report has to deal w/IIS and the SSRS web
service, which adds overhead. Aside form this fact, if you haven't
already, you will want to save the query for the report as a stored
procedure, this will improve performance and response time: due to the
stored procedure having a predefined query plan. Also, you will want
to run the stored procedure/query against the Database Engine Tuning
Advisor to determine if any table indexes will further improve your
performance based on this query. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||errr... How do you use a stored procedure in SSRS? It doesn't appear to be an
option.
--
Tom Garth
"EMartinez" wrote:
> On Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
> > I have a somewhat complex query that takes about 2 seconds to run in
> > Query Analyzer. Yet it takes a full 30 seconds to generate the
> > report. If I remove the document map then it takes ~10 seconds. It
> > seems that there is just tremendous overhead or some other problem
> > when generating reports.
> >
> > Does anyone have any suggestions how I can speed this up? We need up
> > to the minute data so I can't really use a snapshot.
> >
> > Thanks!
> > Matt Penner
>
> In general, a deployed report has to deal w/IIS and the SSRS web
> service, which adds overhead. Aside form this fact, if you haven't
> already, you will want to save the query for the report as a stored
> procedure, this will improve performance and response time: due to the
> stored procedure having a predefined query plan. Also, you will want
> to run the stored procedure/query against the Database Engine Tuning
> Advisor to determine if any table indexes will further improve your
> performance based on this query. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||RS has full and good stored procedure report. The problem is you probably
are just using the wizard. The wizard does not have good stored procedure
report. For some SP if you put in the query and do not use the query builder
to do this.
exec mystoredprocedure @.param1= 'Blah', @.param2 = 'Whatever'
However, depending on how the stored is written it still might have
troubles.
What I sometimes do is create a simple query that returns the fields I want
and use that with the wizard. Then open up the design, go to the dataset
tab. Click on the ..., change the command type to store procedure then in
the next box put in the name of the stored procedure (just the name, do not
put in exec or put in the parameter values). RS recognizes the parameters
and create Report Parameters for each parameter of the SP if it is SQL
Server. Note that you can use SP from other systems but the mechanism is
different (I write lots of SP for Sybase).
If anyone tells you that SP do not work then this is just flat out wrong. In
some cases there are design issues but every single issue can be worked out.
Here is a list of how the SP should be written (most of which is concerning
temp tables)
Temp tables should work for you. I use them a whole lot. Do the following:
1. Click on the refresh fields button (to the right of the ...) if you do
not get field list returned for your stored procedure.
2. Do not use set nocount on
3. Do not explicitly drop the temp tables
4. Have your last statement be a select
If none of these work then add Set FMTONLY Off (the below is from Simon
Sabin a SQL Server MVP). The only time I have had to do this is when one SP
is calling another SP and the first SP has created a temp table that the
second SP puts data into.Here is his explanation: "The issue with RS is that
the rowset of the SP is defined by calling the SP with SET FMTONLY ON
because Temp tables don't get created if you select from the temp table the
metadata from the rowset can't be returned. This can be worked around by
turning FMTONLY OFF in the SP."
I use SP extensively from both SQL Server and Sybase.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Tom Garth" <TomGarth@.discussions.microsoft.com> wrote in message
news:2F03EA77-9F0F-42A3-8E16-AAD9929E0195@.microsoft.com...
> errr... How do you use a stored procedure in SSRS? It doesn't appear to be
> an
> option.
> --
> Tom Garth
>
> "EMartinez" wrote:
>> On Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
>> > I have a somewhat complex query that takes about 2 seconds to run in
>> > Query Analyzer. Yet it takes a full 30 seconds to generate the
>> > report. If I remove the document map then it takes ~10 seconds. It
>> > seems that there is just tremendous overhead or some other problem
>> > when generating reports.
>> >
>> > Does anyone have any suggestions how I can speed this up? We need up
>> > to the minute data so I can't really use a snapshot.
>> >
>> > Thanks!
>> > Matt Penner
>>
>> In general, a deployed report has to deal w/IIS and the SSRS web
>> service, which adds overhead. Aside form this fact, if you haven't
>> already, you will want to save the query for the report as a stored
>> procedure, this will improve performance and response time: due to the
>> stored procedure having a predefined query plan. Also, you will want
>> to run the stored procedure/query against the Database Engine Tuning
>> Advisor to determine if any table indexes will further improve your
>> performance based on this query. Hope this helps.
>> Regards,
>> Enrique Martinez
>> Sr. Software Consultant
>>|||Bruce,
This is terrific stuff! No one ever told me that SPs wouldn't work. I
surmised it as you said, from using the wizard. I considered it to be a major
drawback to using SSRS, but will have to do some rethinking on that now.
I'm glad you mentioned not using SET NOCOUNT ON. I almost always have used
it in reporting. Especially when using temp tables for a Crystal Report.
Thank you for taking the time to reply to my post.
Tom Garth
Developer / Integration Specialist
R. L. Nelson and Associates, Inc.
1400 Technology Drive
Harrisonburg, VA 22802
Main Office: (888) 313-0647
www.rlninc.com
Tom Garth
"Bruce L-C [MVP]" wrote:
> RS has full and good stored procedure report. The problem is you probably
> are just using the wizard. The wizard does not have good stored procedure
> report. For some SP if you put in the query and do not use the query builder
> to do this.
> exec mystoredprocedure @.param1= 'Blah', @.param2 = 'Whatever'
> However, depending on how the stored is written it still might have
> troubles.
> What I sometimes do is create a simple query that returns the fields I want
> and use that with the wizard. Then open up the design, go to the dataset
> tab. Click on the ..., change the command type to store procedure then in
> the next box put in the name of the stored procedure (just the name, do not
> put in exec or put in the parameter values). RS recognizes the parameters
> and create Report Parameters for each parameter of the SP if it is SQL
> Server. Note that you can use SP from other systems but the mechanism is
> different (I write lots of SP for Sybase).
> If anyone tells you that SP do not work then this is just flat out wrong. In
> some cases there are design issues but every single issue can be worked out.
> Here is a list of how the SP should be written (most of which is concerning
> temp tables)
> Temp tables should work for you. I use them a whole lot. Do the following:
> 1. Click on the refresh fields button (to the right of the ...) if you do
> not get field list returned for your stored procedure.
> 2. Do not use set nocount on
> 3. Do not explicitly drop the temp tables
> 4. Have your last statement be a select
> If none of these work then add Set FMTONLY Off (the below is from Simon
> Sabin a SQL Server MVP). The only time I have had to do this is when one SP
> is calling another SP and the first SP has created a temp table that the
> second SP puts data into.Here is his explanation: "The issue with RS is that
> the rowset of the SP is defined by calling the SP with SET FMTONLY ON
> because Temp tables don't get created if you select from the temp table the
> metadata from the rowset can't be returned. This can be worked around by
> turning FMTONLY OFF in the SP."
> I use SP extensively from both SQL Server and Sybase.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Tom Garth" <TomGarth@.discussions.microsoft.com> wrote in message
> news:2F03EA77-9F0F-42A3-8E16-AAD9929E0195@.microsoft.com...
> > errr... How do you use a stored procedure in SSRS? It doesn't appear to be
> > an
> > option.
> > --
> > Tom Garth
> >
> >
> > "EMartinez" wrote:
> >
> >> On Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
> >> > I have a somewhat complex query that takes about 2 seconds to run in
> >> > Query Analyzer. Yet it takes a full 30 seconds to generate the
> >> > report. If I remove the document map then it takes ~10 seconds. It
> >> > seems that there is just tremendous overhead or some other problem
> >> > when generating reports.
> >> >
> >> > Does anyone have any suggestions how I can speed this up? We need up
> >> > to the minute data so I can't really use a snapshot.
> >> >
> >> > Thanks!
> >> > Matt Penner
> >>
> >>
> >> In general, a deployed report has to deal w/IIS and the SSRS web
> >> service, which adds overhead. Aside form this fact, if you haven't
> >> already, you will want to save the query for the report as a stored
> >> procedure, this will improve performance and response time: due to the
> >> stored procedure having a predefined query plan. Also, you will want
> >> to run the stored procedure/query against the Database Engine Tuning
> >> Advisor to determine if any table indexes will further improve your
> >> performance based on this query. Hope this helps.
> >>
> >> Regards,
> >>
> >> Enrique Martinez
> >> Sr. Software Consultant
> >>
> >>
>
>|||No problem. Glad to be of help.
Most of the issues with SP tends to be because of how the SP is written. For
instance, a lot of people drop their temp tables at the end of the SP. This
messes up RS. Instead you just let SQL Server clean up the temp tables when
it goes out of scope.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Tom Garth" <TomGarth@.discussions.microsoft.com> wrote in message
news:DC7C9319-3451-49F6-91F9-2A31EE9A7A9B@.microsoft.com...
> Bruce,
> This is terrific stuff! No one ever told me that SPs wouldn't work. I
> surmised it as you said, from using the wizard. I considered it to be a
> major
> drawback to using SSRS, but will have to do some rethinking on that now.
> I'm glad you mentioned not using SET NOCOUNT ON. I almost always have used
> it in reporting. Especially when using temp tables for a Crystal Report.
> Thank you for taking the time to reply to my post.
> Tom Garth
> Developer / Integration Specialist
> R. L. Nelson and Associates, Inc.
> 1400 Technology Drive
> Harrisonburg, VA 22802
> Main Office: (888) 313-0647
> www.rlninc.com
>
> --
> Tom Garth
>
> "Bruce L-C [MVP]" wrote:
>> RS has full and good stored procedure report. The problem is you probably
>> are just using the wizard. The wizard does not have good stored procedure
>> report. For some SP if you put in the query and do not use the query
>> builder
>> to do this.
>> exec mystoredprocedure @.param1= 'Blah', @.param2 = 'Whatever'
>> However, depending on how the stored is written it still might have
>> troubles.
>> What I sometimes do is create a simple query that returns the fields I
>> want
>> and use that with the wizard. Then open up the design, go to the dataset
>> tab. Click on the ..., change the command type to store procedure then in
>> the next box put in the name of the stored procedure (just the name, do
>> not
>> put in exec or put in the parameter values). RS recognizes the parameters
>> and create Report Parameters for each parameter of the SP if it is SQL
>> Server. Note that you can use SP from other systems but the mechanism is
>> different (I write lots of SP for Sybase).
>> If anyone tells you that SP do not work then this is just flat out wrong.
>> In
>> some cases there are design issues but every single issue can be worked
>> out.
>> Here is a list of how the SP should be written (most of which is
>> concerning
>> temp tables)
>> Temp tables should work for you. I use them a whole lot. Do the
>> following:
>> 1. Click on the refresh fields button (to the right of the ...) if you do
>> not get field list returned for your stored procedure.
>> 2. Do not use set nocount on
>> 3. Do not explicitly drop the temp tables
>> 4. Have your last statement be a select
>> If none of these work then add Set FMTONLY Off (the below is from Simon
>> Sabin a SQL Server MVP). The only time I have had to do this is when one
>> SP
>> is calling another SP and the first SP has created a temp table that the
>> second SP puts data into.Here is his explanation: "The issue with RS is
>> that
>> the rowset of the SP is defined by calling the SP with SET FMTONLY ON
>> because Temp tables don't get created if you select from the temp table
>> the
>> metadata from the rowset can't be returned. This can be worked around by
>> turning FMTONLY OFF in the SP."
>> I use SP extensively from both SQL Server and Sybase.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Tom Garth" <TomGarth@.discussions.microsoft.com> wrote in message
>> news:2F03EA77-9F0F-42A3-8E16-AAD9929E0195@.microsoft.com...
>> > errr... How do you use a stored procedure in SSRS? It doesn't appear to
>> > be
>> > an
>> > option.
>> > --
>> > Tom Garth
>> >
>> >
>> > "EMartinez" wrote:
>> >
>> >> On Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
>> >> > I have a somewhat complex query that takes about 2 seconds to run in
>> >> > Query Analyzer. Yet it takes a full 30 seconds to generate the
>> >> > report. If I remove the document map then it takes ~10 seconds. It
>> >> > seems that there is just tremendous overhead or some other problem
>> >> > when generating reports.
>> >> >
>> >> > Does anyone have any suggestions how I can speed this up? We need
>> >> > up
>> >> > to the minute data so I can't really use a snapshot.
>> >> >
>> >> > Thanks!
>> >> > Matt Penner
>> >>
>> >>
>> >> In general, a deployed report has to deal w/IIS and the SSRS web
>> >> service, which adds overhead. Aside form this fact, if you haven't
>> >> already, you will want to save the query for the report as a stored
>> >> procedure, this will improve performance and response time: due to the
>> >> stored procedure having a predefined query plan. Also, you will want
>> >> to run the stored procedure/query against the Database Engine Tuning
>> >> Advisor to determine if any table indexes will further improve your
>> >> performance based on this query. Hope this helps.
>> >>
>> >> Regards,
>> >>
>> >> Enrique Martinez
>> >> Sr. Software Consultant
>> >>
>> >>
>>|||Well, as I said, the raw query returns results in <2 seconds. This
purely in development right now and we'll most likely move this to
stored procedures in the future but that doesn't seem to be the issue.
The waiting time seems to be on the rendering side.
If SSRS is fast and the overhead of IIS is causing the issue (seems
unlikely) are there any good sites to look at to help improve this?
All I've been able to find are the MS recommended enterprise rollout
with several servers. We don't have these kind of resources for SSRS,
and currently there are only 4 reports and 4 users. I am disappointed
that it takes this long to render simple reports when we're not even
in production capacity.
Thanks,
Matt
On Sep 24, 6:22 pm, EMartinez <emartinez...@.gmail.com> wrote:
> On Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
> > I have a somewhat complex query that takes about 2 seconds to run in
> > Query Analyzer. Yet it takes a full 30 seconds to generate the
> > report. If I remove the document map then it takes ~10 seconds. It
> > seems that there is just tremendous overhead or some other problem
> > when generating reports.
> > Does anyone have any suggestions how I can speed this up? We need up
> > to the minute data so I can't really use a snapshot.
> > Thanks!
> > Matt Penner
> In general, a deployed report has to deal w/IIS and the SSRS web
> service, which adds overhead. Aside form this fact, if you haven't
> already, you will want to save the query for the report as a stored
> procedure, this will improve performance and response time: due to the
> stored procedure having a predefined query plan. Also, you will want
> to run the stored procedure/query against the Database Engine Tuning
> Advisor to determine if any table indexes will further improve your
> performance based on this query. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant|||RS currently renders in RAM. The amount of data is what matters. My guess is
that the 2 seconds is enough to bring over the data and the rest is
rendering. The document map must require more compute time.
A couple of questions, what are you rendering to? HTML? PDF? that makes a
difference as well. Also, you didn't mention what version you are on. How
much RAM is on your box?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Matt Penner" <mattpenner2007@.gmail.com> wrote in message
news:1190843034.956283.72260@.57g2000hsv.googlegroups.com...
> Well, as I said, the raw query returns results in <2 seconds. This
> purely in development right now and we'll most likely move this to
> stored procedures in the future but that doesn't seem to be the issue.
> The waiting time seems to be on the rendering side.
> If SSRS is fast and the overhead of IIS is causing the issue (seems
> unlikely) are there any good sites to look at to help improve this?
> All I've been able to find are the MS recommended enterprise rollout
> with several servers. We don't have these kind of resources for SSRS,
> and currently there are only 4 reports and 4 users. I am disappointed
> that it takes this long to render simple reports when we're not even
> in production capacity.
> Thanks,
> Matt
> On Sep 24, 6:22 pm, EMartinez <emartinez...@.gmail.com> wrote:
>> On Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
>> > I have a somewhat complex query that takes about 2 seconds to run in
>> > Query Analyzer. Yet it takes a full 30 seconds to generate the
>> > report. If I remove the document map then it takes ~10 seconds. It
>> > seems that there is just tremendous overhead or some other problem
>> > when generating reports.
>> > Does anyone have any suggestions how I can speed this up? We need up
>> > to the minute data so I can't really use a snapshot.
>> > Thanks!
>> > Matt Penner
>> In general, a deployed report has to deal w/IIS and the SSRS web
>> service, which adds overhead. Aside form this fact, if you haven't
>> already, you will want to save the query for the report as a stored
>> procedure, this will improve performance and response time: due to the
>> stored procedure having a predefined query plan. Also, you will want
>> to run the stored procedure/query against the Database Engine Tuning
>> Advisor to determine if any table indexes will further improve your
>> performance based on this query. Hope this helps.
>> Regards,
>> Enrique Martinez
>> Sr. Software Consultant
>|||This is using SQL Server 2005 SP2 and SSRS. I'm rendering straight to
the MS Report Viewer. Viewing the report during testing on my dev
machine as well as from the server after deployment seem to give the
same response times. This would make sense if the rendering is purely
on the client side. I have 1.5GB of ram on a P4 3GHz WinXP machine,
but I usually have several things open like Visual Studio, SQL
Management Studio, etc.
If the Report Viewer is entirely client side then there's not much I
can do about it. I didn't realize this is how it works. We have
several different computer models throughout our network so optimizing
the client side would to be difficult at best.
Thanks, I'll just live with it. :)
If there are any other comments out there that would be great but I'll
just assume this is the way it is until MS develops a better viewer.
On Sep 26, 2:54 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> RS currently renders in RAM. The amount of data is what matters. My guess is
> that the 2 seconds is enough to bring over the data and the rest is
> rendering. The document map must require more compute time.
> A couple of questions, what are you rendering to? HTML? PDF? that makes a
> difference as well. Also, you didn't mention what version you are on. How
> much RAM is on your box?
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Matt Penner" <mattpenner2...@.gmail.com> wrote in message
> news:1190843034.956283.72260@.57g2000hsv.googlegroups.com...
> > Well, as I said, the raw query returns results in <2 seconds. This
> > purely in development right now and we'll most likely move this to
> > stored procedures in the future but that doesn't seem to be the issue.
> > The waiting time seems to be on the rendering side.
> > If SSRS is fast and the overhead of IIS is causing the issue (seems
> > unlikely) are there any good sites to look at to help improve this?
> > All I've been able to find are the MS recommended enterprise rollout
> > with several servers. We don't have these kind of resources for SSRS,
> > and currently there are only 4 reports and 4 users. I am disappointed
> > that it takes this long to render simple reports when we're not even
> > in production capacity.
> > Thanks,
> > Matt
> > On Sep 24, 6:22 pm, EMartinez <emartinez...@.gmail.com> wrote:
> >> On Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
> >> > I have a somewhat complex query that takes about 2 seconds to run in
> >> > Query Analyzer. Yet it takes a full 30 seconds to generate the
> >> > report. If I remove the document map then it takes ~10 seconds. It
> >> > seems that there is just tremendous overhead or some other problem
> >> > when generating reports.
> >> > Does anyone have any suggestions how I can speed this up? We need up
> >> > to the minute data so I can't really use a snapshot.
> >> > Thanks!
> >> > Matt Penner
> >> In general, a deployed report has to deal w/IIS and the SSRS web
> >> service, which adds overhead. Aside form this fact, if you haven't
> >> already, you will want to save the query for the report as a stored
> >> procedure, this will improve performance and response time: due to the
> >> stored procedure having a predefined query plan. Also, you will want
> >> to run the stored procedure/query against the Database Engine Tuning
> >> Advisor to determine if any table indexes will further improve your
> >> performance based on this query. Hope this helps.
> >> Regards,
> >> Enrique Martinez
> >> Sr. Software Consultant|||The rendering is at the Server. It is NOT at the client.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Matt Penner" <mattpenner2007@.gmail.com> wrote in message
news:1190992965.413678.301900@.g4g2000hsf.googlegroups.com...
> This is using SQL Server 2005 SP2 and SSRS. I'm rendering straight to
> the MS Report Viewer. Viewing the report during testing on my dev
> machine as well as from the server after deployment seem to give the
> same response times. This would make sense if the rendering is purely
> on the client side. I have 1.5GB of ram on a P4 3GHz WinXP machine,
> but I usually have several things open like Visual Studio, SQL
> Management Studio, etc.
> If the Report Viewer is entirely client side then there's not much I
> can do about it. I didn't realize this is how it works. We have
> several different computer models throughout our network so optimizing
> the client side would to be difficult at best.
> Thanks, I'll just live with it. :)
> If there are any other comments out there that would be great but I'll
> just assume this is the way it is until MS develops a better viewer.
> On Sep 26, 2:54 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
> wrote:
>> RS currently renders in RAM. The amount of data is what matters. My guess
>> is
>> that the 2 seconds is enough to bring over the data and the rest is
>> rendering. The document map must require more compute time.
>> A couple of questions, what are you rendering to? HTML? PDF? that makes a
>> difference as well. Also, you didn't mention what version you are on. How
>> much RAM is on your box?
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Matt Penner" <mattpenner2...@.gmail.com> wrote in message
>> news:1190843034.956283.72260@.57g2000hsv.googlegroups.com...
>> > Well, as I said, the raw query returns results in <2 seconds. This
>> > purely in development right now and we'll most likely move this to
>> > stored procedures in the future but that doesn't seem to be the issue.
>> > The waiting time seems to be on the rendering side.
>> > If SSRS is fast and the overhead of IIS is causing the issue (seems
>> > unlikely) are there any good sites to look at to help improve this?
>> > All I've been able to find are the MS recommended enterprise rollout
>> > with several servers. We don't have these kind of resources for SSRS,
>> > and currently there are only 4 reports and 4 users. I am disappointed
>> > that it takes this long to render simple reports when we're not even
>> > in production capacity.
>> > Thanks,
>> > Matt
>> > On Sep 24, 6:22 pm, EMartinez <emartinez...@.gmail.com> wrote:
>> >> On Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
>> >> > I have a somewhat complex query that takes about 2 seconds to run in
>> >> > Query Analyzer. Yet it takes a full 30 seconds to generate the
>> >> > report. If I remove the document map then it takes ~10 seconds. It
>> >> > seems that there is just tremendous overhead or some other problem
>> >> > when generating reports.
>> >> > Does anyone have any suggestions how I can speed this up? We need
>> >> > up
>> >> > to the minute data so I can't really use a snapshot.
>> >> > Thanks!
>> >> > Matt Penner
>> >> In general, a deployed report has to deal w/IIS and the SSRS web
>> >> service, which adds overhead. Aside form this fact, if you haven't
>> >> already, you will want to save the query for the report as a stored
>> >> procedure, this will improve performance and response time: due to the
>> >> stored procedure having a predefined query plan. Also, you will want
>> >> to run the stored procedure/query against the Database Engine Tuning
>> >> Advisor to determine if any table indexes will further improve your
>> >> performance based on this query. Hope this helps.
>> >> Regards,
>> >> Enrique Martinez
>> >> Sr. Software Consultant
>|||Ahh, that did seem a little strange but I haven't had the time to look
into it.
Anyway, the server is a dual Xeon 3Ghz Dual Core with 4GB of Ram,
although it is a pretty heavy lifter for one of our SQL databases.
SSRS was more of a test and afterthought on this server. It is quite
possible that SQL 2005 is hogging all the resources. What's the best
way to view the resources being used by SSRS and alter this? Should
SQL server and SSRS play nicely together or do I really need to
manually allocate memory to each?
Thanks,
Matt
On Sep 28, 9:14 am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> The rendering is at the Server. It is NOT at the client.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Matt Penner" <mattpenner2...@.gmail.com> wrote in message
> news:1190992965.413678.301900@.g4g2000hsf.googlegroups.com...
> > This is using SQL Server 2005 SP2 and SSRS. I'm rendering straight to
> > the MS Report Viewer. Viewing the report during testing on my dev
> > machine as well as from the server after deployment seem to give the
> > same response times. This would make sense if the rendering is purely
> > on the client side. I have 1.5GB of ram on a P4 3GHz WinXP machine,
> > but I usually have several things open like Visual Studio, SQL
> > Management Studio, etc.
> > If the Report Viewer is entirely client side then there's not much I
> > can do about it. I didn't realize this is how it works. We have
> > several different computer models throughout our network so optimizing
> > the client side would to be difficult at best.
> > Thanks, I'll just live with it. :)
> > If there are any other comments out there that would be great but I'll
> > just assume this is the way it is until MS develops a better viewer.
> > On Sep 26, 2:54 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
> > wrote:
> >> RS currently renders in RAM. The amount of data is what matters. My guess
> >> is
> >> that the 2 seconds is enough to bring over the data and the rest is
> >> rendering. The document map must require more compute time.
> >> A couple of questions, what are you rendering to? HTML? PDF? that makes a
> >> difference as well. Also, you didn't mention what version you are on. How
> >> much RAM is on your box?
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >> "Matt Penner" <mattpenner2...@.gmail.com> wrote in message
> >>news:1190843034.956283.72260@.57g2000hsv.googlegroups.com...
> >> > Well, as I said, the raw query returns results in <2 seconds. This
> >> > purely in development right now and we'll most likely move this to
> >> > stored procedures in the future but that doesn't seem to be the issue.
> >> > The waiting time seems to be on the rendering side.
> >> > If SSRS is fast and the overhead of IIS is causing the issue (seems
> >> > unlikely) are there any good sites to look at to help improve this?
> >> > All I've been able to find are the MS recommended enterprise rollout
> >> > with several servers. We don't have these kind of resources for SSRS,
> >> > and currently there are only 4 reports and 4 users. I am disappointed
> >> > that it takes this long to render simple reports when we're not even
> >> > in production capacity.
> >> > Thanks,
> >> > Matt
> >> > On Sep 24, 6:22 pm, EMartinez <emartinez...@.gmail.com> wrote:
> >> >> On Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
> >> >> > I have a somewhat complex query that takes about 2 seconds to run in
> >> >> > Query Analyzer. Yet it takes a full 30 seconds to generate the
> >> >> > report. If I remove the document map then it takes ~10 seconds. It
> >> >> > seems that there is just tremendous overhead or some other problem
> >> >> > when generating reports.
> >> >> > Does anyone have any suggestions how I can speed this up? We need
> >> >> > up
> >> >> > to the minute data so I can't really use a snapshot.
> >> >> > Thanks!
> >> >> > Matt Penner
> >> >> In general, a deployed report has to deal w/IIS and the SSRS web
> >> >> service, which adds overhead. Aside form this fact, if you haven't
> >> >> already, you will want to save the query for the report as a stored
> >> >> procedure, this will improve performance and response time: due to the
> >> >> stored procedure having a predefined query plan. Also, you will want
> >> >> to run the stored procedure/query against the Database Engine Tuning
> >> >> Advisor to determine if any table indexes will further improve your
> >> >> performance based on this query. Hope this helps.
> >> >> Regards,
> >> >> Enrique Martinez
> >> >> Sr. Software Consultant|||Try to simplify the report layout as much as possible - I typically
render to PDF, and find the following tips helpful -
= Use tables in place of lists (tables with grouping vs. nested lists)
= Avoid using cell borders - used alternate row shading instead to
differentiate rows
= Set the 'Can Grow' and 'Can Shrink' properties of report objects to
False wherever possible
= minimize the number of objects (merge empty cells in a table, for
instance)
= minimize the number of fonts
I believe the view is rendering in HTML, so think in terms of setting
up a report that will produce the smallest, simplest HTML document
that will correctly present your data.|||Hi Parker,
Thanks. These are great tips. I never thought of rendering straight
to PDF. Since the MS Report viewer doesn't seem to offer much that
pdf can't do, other than toggling sections, this may be a better
option. It makes sense that simplifying the end result may reduce the
render time.
I'll experiment with all this.
Matt
On Sep 29, 6:15 am, Parker <psm...@.iquest.net> wrote:
> Try to simplify the report layout as much as possible - I typically
> render to PDF, and find the following tips helpful -
> = Use tables in place of lists (tables with grouping vs. nested lists)
> = Avoid using cell borders - used alternate row shading instead to
> differentiate rows
> = Set the 'Can Grow' and 'Can Shrink' properties of report objects to
> False wherever possible
> = minimize the number of objects (merge empty cells in a table, for
> instance)
> = minimize the number of fonts
> I believe the view is rendering in HTML, so think in terms of setting
> up a report that will produce the smallest, simplest HTML document
> that will correctly present your data.
Query Analyzer. Yet it takes a full 30 seconds to generate the
report. If I remove the document map then it takes ~10 seconds. It
seems that there is just tremendous overhead or some other problem
when generating reports.
Does anyone have any suggestions how I can speed this up? We need up
to the minute data so I can't really use a snapshot.
Thanks!
Matt PennerOn Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
> I have a somewhat complex query that takes about 2 seconds to run in
> Query Analyzer. Yet it takes a full 30 seconds to generate the
> report. If I remove the document map then it takes ~10 seconds. It
> seems that there is just tremendous overhead or some other problem
> when generating reports.
> Does anyone have any suggestions how I can speed this up? We need up
> to the minute data so I can't really use a snapshot.
> Thanks!
> Matt Penner
In general, a deployed report has to deal w/IIS and the SSRS web
service, which adds overhead. Aside form this fact, if you haven't
already, you will want to save the query for the report as a stored
procedure, this will improve performance and response time: due to the
stored procedure having a predefined query plan. Also, you will want
to run the stored procedure/query against the Database Engine Tuning
Advisor to determine if any table indexes will further improve your
performance based on this query. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||errr... How do you use a stored procedure in SSRS? It doesn't appear to be an
option.
--
Tom Garth
"EMartinez" wrote:
> On Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
> > I have a somewhat complex query that takes about 2 seconds to run in
> > Query Analyzer. Yet it takes a full 30 seconds to generate the
> > report. If I remove the document map then it takes ~10 seconds. It
> > seems that there is just tremendous overhead or some other problem
> > when generating reports.
> >
> > Does anyone have any suggestions how I can speed this up? We need up
> > to the minute data so I can't really use a snapshot.
> >
> > Thanks!
> > Matt Penner
>
> In general, a deployed report has to deal w/IIS and the SSRS web
> service, which adds overhead. Aside form this fact, if you haven't
> already, you will want to save the query for the report as a stored
> procedure, this will improve performance and response time: due to the
> stored procedure having a predefined query plan. Also, you will want
> to run the stored procedure/query against the Database Engine Tuning
> Advisor to determine if any table indexes will further improve your
> performance based on this query. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||RS has full and good stored procedure report. The problem is you probably
are just using the wizard. The wizard does not have good stored procedure
report. For some SP if you put in the query and do not use the query builder
to do this.
exec mystoredprocedure @.param1= 'Blah', @.param2 = 'Whatever'
However, depending on how the stored is written it still might have
troubles.
What I sometimes do is create a simple query that returns the fields I want
and use that with the wizard. Then open up the design, go to the dataset
tab. Click on the ..., change the command type to store procedure then in
the next box put in the name of the stored procedure (just the name, do not
put in exec or put in the parameter values). RS recognizes the parameters
and create Report Parameters for each parameter of the SP if it is SQL
Server. Note that you can use SP from other systems but the mechanism is
different (I write lots of SP for Sybase).
If anyone tells you that SP do not work then this is just flat out wrong. In
some cases there are design issues but every single issue can be worked out.
Here is a list of how the SP should be written (most of which is concerning
temp tables)
Temp tables should work for you. I use them a whole lot. Do the following:
1. Click on the refresh fields button (to the right of the ...) if you do
not get field list returned for your stored procedure.
2. Do not use set nocount on
3. Do not explicitly drop the temp tables
4. Have your last statement be a select
If none of these work then add Set FMTONLY Off (the below is from Simon
Sabin a SQL Server MVP). The only time I have had to do this is when one SP
is calling another SP and the first SP has created a temp table that the
second SP puts data into.Here is his explanation: "The issue with RS is that
the rowset of the SP is defined by calling the SP with SET FMTONLY ON
because Temp tables don't get created if you select from the temp table the
metadata from the rowset can't be returned. This can be worked around by
turning FMTONLY OFF in the SP."
I use SP extensively from both SQL Server and Sybase.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Tom Garth" <TomGarth@.discussions.microsoft.com> wrote in message
news:2F03EA77-9F0F-42A3-8E16-AAD9929E0195@.microsoft.com...
> errr... How do you use a stored procedure in SSRS? It doesn't appear to be
> an
> option.
> --
> Tom Garth
>
> "EMartinez" wrote:
>> On Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
>> > I have a somewhat complex query that takes about 2 seconds to run in
>> > Query Analyzer. Yet it takes a full 30 seconds to generate the
>> > report. If I remove the document map then it takes ~10 seconds. It
>> > seems that there is just tremendous overhead or some other problem
>> > when generating reports.
>> >
>> > Does anyone have any suggestions how I can speed this up? We need up
>> > to the minute data so I can't really use a snapshot.
>> >
>> > Thanks!
>> > Matt Penner
>>
>> In general, a deployed report has to deal w/IIS and the SSRS web
>> service, which adds overhead. Aside form this fact, if you haven't
>> already, you will want to save the query for the report as a stored
>> procedure, this will improve performance and response time: due to the
>> stored procedure having a predefined query plan. Also, you will want
>> to run the stored procedure/query against the Database Engine Tuning
>> Advisor to determine if any table indexes will further improve your
>> performance based on this query. Hope this helps.
>> Regards,
>> Enrique Martinez
>> Sr. Software Consultant
>>|||Bruce,
This is terrific stuff! No one ever told me that SPs wouldn't work. I
surmised it as you said, from using the wizard. I considered it to be a major
drawback to using SSRS, but will have to do some rethinking on that now.
I'm glad you mentioned not using SET NOCOUNT ON. I almost always have used
it in reporting. Especially when using temp tables for a Crystal Report.
Thank you for taking the time to reply to my post.
Tom Garth
Developer / Integration Specialist
R. L. Nelson and Associates, Inc.
1400 Technology Drive
Harrisonburg, VA 22802
Main Office: (888) 313-0647
www.rlninc.com
Tom Garth
"Bruce L-C [MVP]" wrote:
> RS has full and good stored procedure report. The problem is you probably
> are just using the wizard. The wizard does not have good stored procedure
> report. For some SP if you put in the query and do not use the query builder
> to do this.
> exec mystoredprocedure @.param1= 'Blah', @.param2 = 'Whatever'
> However, depending on how the stored is written it still might have
> troubles.
> What I sometimes do is create a simple query that returns the fields I want
> and use that with the wizard. Then open up the design, go to the dataset
> tab. Click on the ..., change the command type to store procedure then in
> the next box put in the name of the stored procedure (just the name, do not
> put in exec or put in the parameter values). RS recognizes the parameters
> and create Report Parameters for each parameter of the SP if it is SQL
> Server. Note that you can use SP from other systems but the mechanism is
> different (I write lots of SP for Sybase).
> If anyone tells you that SP do not work then this is just flat out wrong. In
> some cases there are design issues but every single issue can be worked out.
> Here is a list of how the SP should be written (most of which is concerning
> temp tables)
> Temp tables should work for you. I use them a whole lot. Do the following:
> 1. Click on the refresh fields button (to the right of the ...) if you do
> not get field list returned for your stored procedure.
> 2. Do not use set nocount on
> 3. Do not explicitly drop the temp tables
> 4. Have your last statement be a select
> If none of these work then add Set FMTONLY Off (the below is from Simon
> Sabin a SQL Server MVP). The only time I have had to do this is when one SP
> is calling another SP and the first SP has created a temp table that the
> second SP puts data into.Here is his explanation: "The issue with RS is that
> the rowset of the SP is defined by calling the SP with SET FMTONLY ON
> because Temp tables don't get created if you select from the temp table the
> metadata from the rowset can't be returned. This can be worked around by
> turning FMTONLY OFF in the SP."
> I use SP extensively from both SQL Server and Sybase.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Tom Garth" <TomGarth@.discussions.microsoft.com> wrote in message
> news:2F03EA77-9F0F-42A3-8E16-AAD9929E0195@.microsoft.com...
> > errr... How do you use a stored procedure in SSRS? It doesn't appear to be
> > an
> > option.
> > --
> > Tom Garth
> >
> >
> > "EMartinez" wrote:
> >
> >> On Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
> >> > I have a somewhat complex query that takes about 2 seconds to run in
> >> > Query Analyzer. Yet it takes a full 30 seconds to generate the
> >> > report. If I remove the document map then it takes ~10 seconds. It
> >> > seems that there is just tremendous overhead or some other problem
> >> > when generating reports.
> >> >
> >> > Does anyone have any suggestions how I can speed this up? We need up
> >> > to the minute data so I can't really use a snapshot.
> >> >
> >> > Thanks!
> >> > Matt Penner
> >>
> >>
> >> In general, a deployed report has to deal w/IIS and the SSRS web
> >> service, which adds overhead. Aside form this fact, if you haven't
> >> already, you will want to save the query for the report as a stored
> >> procedure, this will improve performance and response time: due to the
> >> stored procedure having a predefined query plan. Also, you will want
> >> to run the stored procedure/query against the Database Engine Tuning
> >> Advisor to determine if any table indexes will further improve your
> >> performance based on this query. Hope this helps.
> >>
> >> Regards,
> >>
> >> Enrique Martinez
> >> Sr. Software Consultant
> >>
> >>
>
>|||No problem. Glad to be of help.
Most of the issues with SP tends to be because of how the SP is written. For
instance, a lot of people drop their temp tables at the end of the SP. This
messes up RS. Instead you just let SQL Server clean up the temp tables when
it goes out of scope.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Tom Garth" <TomGarth@.discussions.microsoft.com> wrote in message
news:DC7C9319-3451-49F6-91F9-2A31EE9A7A9B@.microsoft.com...
> Bruce,
> This is terrific stuff! No one ever told me that SPs wouldn't work. I
> surmised it as you said, from using the wizard. I considered it to be a
> major
> drawback to using SSRS, but will have to do some rethinking on that now.
> I'm glad you mentioned not using SET NOCOUNT ON. I almost always have used
> it in reporting. Especially when using temp tables for a Crystal Report.
> Thank you for taking the time to reply to my post.
> Tom Garth
> Developer / Integration Specialist
> R. L. Nelson and Associates, Inc.
> 1400 Technology Drive
> Harrisonburg, VA 22802
> Main Office: (888) 313-0647
> www.rlninc.com
>
> --
> Tom Garth
>
> "Bruce L-C [MVP]" wrote:
>> RS has full and good stored procedure report. The problem is you probably
>> are just using the wizard. The wizard does not have good stored procedure
>> report. For some SP if you put in the query and do not use the query
>> builder
>> to do this.
>> exec mystoredprocedure @.param1= 'Blah', @.param2 = 'Whatever'
>> However, depending on how the stored is written it still might have
>> troubles.
>> What I sometimes do is create a simple query that returns the fields I
>> want
>> and use that with the wizard. Then open up the design, go to the dataset
>> tab. Click on the ..., change the command type to store procedure then in
>> the next box put in the name of the stored procedure (just the name, do
>> not
>> put in exec or put in the parameter values). RS recognizes the parameters
>> and create Report Parameters for each parameter of the SP if it is SQL
>> Server. Note that you can use SP from other systems but the mechanism is
>> different (I write lots of SP for Sybase).
>> If anyone tells you that SP do not work then this is just flat out wrong.
>> In
>> some cases there are design issues but every single issue can be worked
>> out.
>> Here is a list of how the SP should be written (most of which is
>> concerning
>> temp tables)
>> Temp tables should work for you. I use them a whole lot. Do the
>> following:
>> 1. Click on the refresh fields button (to the right of the ...) if you do
>> not get field list returned for your stored procedure.
>> 2. Do not use set nocount on
>> 3. Do not explicitly drop the temp tables
>> 4. Have your last statement be a select
>> If none of these work then add Set FMTONLY Off (the below is from Simon
>> Sabin a SQL Server MVP). The only time I have had to do this is when one
>> SP
>> is calling another SP and the first SP has created a temp table that the
>> second SP puts data into.Here is his explanation: "The issue with RS is
>> that
>> the rowset of the SP is defined by calling the SP with SET FMTONLY ON
>> because Temp tables don't get created if you select from the temp table
>> the
>> metadata from the rowset can't be returned. This can be worked around by
>> turning FMTONLY OFF in the SP."
>> I use SP extensively from both SQL Server and Sybase.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Tom Garth" <TomGarth@.discussions.microsoft.com> wrote in message
>> news:2F03EA77-9F0F-42A3-8E16-AAD9929E0195@.microsoft.com...
>> > errr... How do you use a stored procedure in SSRS? It doesn't appear to
>> > be
>> > an
>> > option.
>> > --
>> > Tom Garth
>> >
>> >
>> > "EMartinez" wrote:
>> >
>> >> On Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
>> >> > I have a somewhat complex query that takes about 2 seconds to run in
>> >> > Query Analyzer. Yet it takes a full 30 seconds to generate the
>> >> > report. If I remove the document map then it takes ~10 seconds. It
>> >> > seems that there is just tremendous overhead or some other problem
>> >> > when generating reports.
>> >> >
>> >> > Does anyone have any suggestions how I can speed this up? We need
>> >> > up
>> >> > to the minute data so I can't really use a snapshot.
>> >> >
>> >> > Thanks!
>> >> > Matt Penner
>> >>
>> >>
>> >> In general, a deployed report has to deal w/IIS and the SSRS web
>> >> service, which adds overhead. Aside form this fact, if you haven't
>> >> already, you will want to save the query for the report as a stored
>> >> procedure, this will improve performance and response time: due to the
>> >> stored procedure having a predefined query plan. Also, you will want
>> >> to run the stored procedure/query against the Database Engine Tuning
>> >> Advisor to determine if any table indexes will further improve your
>> >> performance based on this query. Hope this helps.
>> >>
>> >> Regards,
>> >>
>> >> Enrique Martinez
>> >> Sr. Software Consultant
>> >>
>> >>
>>|||Well, as I said, the raw query returns results in <2 seconds. This
purely in development right now and we'll most likely move this to
stored procedures in the future but that doesn't seem to be the issue.
The waiting time seems to be on the rendering side.
If SSRS is fast and the overhead of IIS is causing the issue (seems
unlikely) are there any good sites to look at to help improve this?
All I've been able to find are the MS recommended enterprise rollout
with several servers. We don't have these kind of resources for SSRS,
and currently there are only 4 reports and 4 users. I am disappointed
that it takes this long to render simple reports when we're not even
in production capacity.
Thanks,
Matt
On Sep 24, 6:22 pm, EMartinez <emartinez...@.gmail.com> wrote:
> On Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
> > I have a somewhat complex query that takes about 2 seconds to run in
> > Query Analyzer. Yet it takes a full 30 seconds to generate the
> > report. If I remove the document map then it takes ~10 seconds. It
> > seems that there is just tremendous overhead or some other problem
> > when generating reports.
> > Does anyone have any suggestions how I can speed this up? We need up
> > to the minute data so I can't really use a snapshot.
> > Thanks!
> > Matt Penner
> In general, a deployed report has to deal w/IIS and the SSRS web
> service, which adds overhead. Aside form this fact, if you haven't
> already, you will want to save the query for the report as a stored
> procedure, this will improve performance and response time: due to the
> stored procedure having a predefined query plan. Also, you will want
> to run the stored procedure/query against the Database Engine Tuning
> Advisor to determine if any table indexes will further improve your
> performance based on this query. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant|||RS currently renders in RAM. The amount of data is what matters. My guess is
that the 2 seconds is enough to bring over the data and the rest is
rendering. The document map must require more compute time.
A couple of questions, what are you rendering to? HTML? PDF? that makes a
difference as well. Also, you didn't mention what version you are on. How
much RAM is on your box?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Matt Penner" <mattpenner2007@.gmail.com> wrote in message
news:1190843034.956283.72260@.57g2000hsv.googlegroups.com...
> Well, as I said, the raw query returns results in <2 seconds. This
> purely in development right now and we'll most likely move this to
> stored procedures in the future but that doesn't seem to be the issue.
> The waiting time seems to be on the rendering side.
> If SSRS is fast and the overhead of IIS is causing the issue (seems
> unlikely) are there any good sites to look at to help improve this?
> All I've been able to find are the MS recommended enterprise rollout
> with several servers. We don't have these kind of resources for SSRS,
> and currently there are only 4 reports and 4 users. I am disappointed
> that it takes this long to render simple reports when we're not even
> in production capacity.
> Thanks,
> Matt
> On Sep 24, 6:22 pm, EMartinez <emartinez...@.gmail.com> wrote:
>> On Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
>> > I have a somewhat complex query that takes about 2 seconds to run in
>> > Query Analyzer. Yet it takes a full 30 seconds to generate the
>> > report. If I remove the document map then it takes ~10 seconds. It
>> > seems that there is just tremendous overhead or some other problem
>> > when generating reports.
>> > Does anyone have any suggestions how I can speed this up? We need up
>> > to the minute data so I can't really use a snapshot.
>> > Thanks!
>> > Matt Penner
>> In general, a deployed report has to deal w/IIS and the SSRS web
>> service, which adds overhead. Aside form this fact, if you haven't
>> already, you will want to save the query for the report as a stored
>> procedure, this will improve performance and response time: due to the
>> stored procedure having a predefined query plan. Also, you will want
>> to run the stored procedure/query against the Database Engine Tuning
>> Advisor to determine if any table indexes will further improve your
>> performance based on this query. Hope this helps.
>> Regards,
>> Enrique Martinez
>> Sr. Software Consultant
>|||This is using SQL Server 2005 SP2 and SSRS. I'm rendering straight to
the MS Report Viewer. Viewing the report during testing on my dev
machine as well as from the server after deployment seem to give the
same response times. This would make sense if the rendering is purely
on the client side. I have 1.5GB of ram on a P4 3GHz WinXP machine,
but I usually have several things open like Visual Studio, SQL
Management Studio, etc.
If the Report Viewer is entirely client side then there's not much I
can do about it. I didn't realize this is how it works. We have
several different computer models throughout our network so optimizing
the client side would to be difficult at best.
Thanks, I'll just live with it. :)
If there are any other comments out there that would be great but I'll
just assume this is the way it is until MS develops a better viewer.
On Sep 26, 2:54 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> RS currently renders in RAM. The amount of data is what matters. My guess is
> that the 2 seconds is enough to bring over the data and the rest is
> rendering. The document map must require more compute time.
> A couple of questions, what are you rendering to? HTML? PDF? that makes a
> difference as well. Also, you didn't mention what version you are on. How
> much RAM is on your box?
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Matt Penner" <mattpenner2...@.gmail.com> wrote in message
> news:1190843034.956283.72260@.57g2000hsv.googlegroups.com...
> > Well, as I said, the raw query returns results in <2 seconds. This
> > purely in development right now and we'll most likely move this to
> > stored procedures in the future but that doesn't seem to be the issue.
> > The waiting time seems to be on the rendering side.
> > If SSRS is fast and the overhead of IIS is causing the issue (seems
> > unlikely) are there any good sites to look at to help improve this?
> > All I've been able to find are the MS recommended enterprise rollout
> > with several servers. We don't have these kind of resources for SSRS,
> > and currently there are only 4 reports and 4 users. I am disappointed
> > that it takes this long to render simple reports when we're not even
> > in production capacity.
> > Thanks,
> > Matt
> > On Sep 24, 6:22 pm, EMartinez <emartinez...@.gmail.com> wrote:
> >> On Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
> >> > I have a somewhat complex query that takes about 2 seconds to run in
> >> > Query Analyzer. Yet it takes a full 30 seconds to generate the
> >> > report. If I remove the document map then it takes ~10 seconds. It
> >> > seems that there is just tremendous overhead or some other problem
> >> > when generating reports.
> >> > Does anyone have any suggestions how I can speed this up? We need up
> >> > to the minute data so I can't really use a snapshot.
> >> > Thanks!
> >> > Matt Penner
> >> In general, a deployed report has to deal w/IIS and the SSRS web
> >> service, which adds overhead. Aside form this fact, if you haven't
> >> already, you will want to save the query for the report as a stored
> >> procedure, this will improve performance and response time: due to the
> >> stored procedure having a predefined query plan. Also, you will want
> >> to run the stored procedure/query against the Database Engine Tuning
> >> Advisor to determine if any table indexes will further improve your
> >> performance based on this query. Hope this helps.
> >> Regards,
> >> Enrique Martinez
> >> Sr. Software Consultant|||The rendering is at the Server. It is NOT at the client.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Matt Penner" <mattpenner2007@.gmail.com> wrote in message
news:1190992965.413678.301900@.g4g2000hsf.googlegroups.com...
> This is using SQL Server 2005 SP2 and SSRS. I'm rendering straight to
> the MS Report Viewer. Viewing the report during testing on my dev
> machine as well as from the server after deployment seem to give the
> same response times. This would make sense if the rendering is purely
> on the client side. I have 1.5GB of ram on a P4 3GHz WinXP machine,
> but I usually have several things open like Visual Studio, SQL
> Management Studio, etc.
> If the Report Viewer is entirely client side then there's not much I
> can do about it. I didn't realize this is how it works. We have
> several different computer models throughout our network so optimizing
> the client side would to be difficult at best.
> Thanks, I'll just live with it. :)
> If there are any other comments out there that would be great but I'll
> just assume this is the way it is until MS develops a better viewer.
> On Sep 26, 2:54 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
> wrote:
>> RS currently renders in RAM. The amount of data is what matters. My guess
>> is
>> that the 2 seconds is enough to bring over the data and the rest is
>> rendering. The document map must require more compute time.
>> A couple of questions, what are you rendering to? HTML? PDF? that makes a
>> difference as well. Also, you didn't mention what version you are on. How
>> much RAM is on your box?
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Matt Penner" <mattpenner2...@.gmail.com> wrote in message
>> news:1190843034.956283.72260@.57g2000hsv.googlegroups.com...
>> > Well, as I said, the raw query returns results in <2 seconds. This
>> > purely in development right now and we'll most likely move this to
>> > stored procedures in the future but that doesn't seem to be the issue.
>> > The waiting time seems to be on the rendering side.
>> > If SSRS is fast and the overhead of IIS is causing the issue (seems
>> > unlikely) are there any good sites to look at to help improve this?
>> > All I've been able to find are the MS recommended enterprise rollout
>> > with several servers. We don't have these kind of resources for SSRS,
>> > and currently there are only 4 reports and 4 users. I am disappointed
>> > that it takes this long to render simple reports when we're not even
>> > in production capacity.
>> > Thanks,
>> > Matt
>> > On Sep 24, 6:22 pm, EMartinez <emartinez...@.gmail.com> wrote:
>> >> On Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
>> >> > I have a somewhat complex query that takes about 2 seconds to run in
>> >> > Query Analyzer. Yet it takes a full 30 seconds to generate the
>> >> > report. If I remove the document map then it takes ~10 seconds. It
>> >> > seems that there is just tremendous overhead or some other problem
>> >> > when generating reports.
>> >> > Does anyone have any suggestions how I can speed this up? We need
>> >> > up
>> >> > to the minute data so I can't really use a snapshot.
>> >> > Thanks!
>> >> > Matt Penner
>> >> In general, a deployed report has to deal w/IIS and the SSRS web
>> >> service, which adds overhead. Aside form this fact, if you haven't
>> >> already, you will want to save the query for the report as a stored
>> >> procedure, this will improve performance and response time: due to the
>> >> stored procedure having a predefined query plan. Also, you will want
>> >> to run the stored procedure/query against the Database Engine Tuning
>> >> Advisor to determine if any table indexes will further improve your
>> >> performance based on this query. Hope this helps.
>> >> Regards,
>> >> Enrique Martinez
>> >> Sr. Software Consultant
>|||Ahh, that did seem a little strange but I haven't had the time to look
into it.
Anyway, the server is a dual Xeon 3Ghz Dual Core with 4GB of Ram,
although it is a pretty heavy lifter for one of our SQL databases.
SSRS was more of a test and afterthought on this server. It is quite
possible that SQL 2005 is hogging all the resources. What's the best
way to view the resources being used by SSRS and alter this? Should
SQL server and SSRS play nicely together or do I really need to
manually allocate memory to each?
Thanks,
Matt
On Sep 28, 9:14 am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> The rendering is at the Server. It is NOT at the client.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Matt Penner" <mattpenner2...@.gmail.com> wrote in message
> news:1190992965.413678.301900@.g4g2000hsf.googlegroups.com...
> > This is using SQL Server 2005 SP2 and SSRS. I'm rendering straight to
> > the MS Report Viewer. Viewing the report during testing on my dev
> > machine as well as from the server after deployment seem to give the
> > same response times. This would make sense if the rendering is purely
> > on the client side. I have 1.5GB of ram on a P4 3GHz WinXP machine,
> > but I usually have several things open like Visual Studio, SQL
> > Management Studio, etc.
> > If the Report Viewer is entirely client side then there's not much I
> > can do about it. I didn't realize this is how it works. We have
> > several different computer models throughout our network so optimizing
> > the client side would to be difficult at best.
> > Thanks, I'll just live with it. :)
> > If there are any other comments out there that would be great but I'll
> > just assume this is the way it is until MS develops a better viewer.
> > On Sep 26, 2:54 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
> > wrote:
> >> RS currently renders in RAM. The amount of data is what matters. My guess
> >> is
> >> that the 2 seconds is enough to bring over the data and the rest is
> >> rendering. The document map must require more compute time.
> >> A couple of questions, what are you rendering to? HTML? PDF? that makes a
> >> difference as well. Also, you didn't mention what version you are on. How
> >> much RAM is on your box?
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >> "Matt Penner" <mattpenner2...@.gmail.com> wrote in message
> >>news:1190843034.956283.72260@.57g2000hsv.googlegroups.com...
> >> > Well, as I said, the raw query returns results in <2 seconds. This
> >> > purely in development right now and we'll most likely move this to
> >> > stored procedures in the future but that doesn't seem to be the issue.
> >> > The waiting time seems to be on the rendering side.
> >> > If SSRS is fast and the overhead of IIS is causing the issue (seems
> >> > unlikely) are there any good sites to look at to help improve this?
> >> > All I've been able to find are the MS recommended enterprise rollout
> >> > with several servers. We don't have these kind of resources for SSRS,
> >> > and currently there are only 4 reports and 4 users. I am disappointed
> >> > that it takes this long to render simple reports when we're not even
> >> > in production capacity.
> >> > Thanks,
> >> > Matt
> >> > On Sep 24, 6:22 pm, EMartinez <emartinez...@.gmail.com> wrote:
> >> >> On Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
> >> >> > I have a somewhat complex query that takes about 2 seconds to run in
> >> >> > Query Analyzer. Yet it takes a full 30 seconds to generate the
> >> >> > report. If I remove the document map then it takes ~10 seconds. It
> >> >> > seems that there is just tremendous overhead or some other problem
> >> >> > when generating reports.
> >> >> > Does anyone have any suggestions how I can speed this up? We need
> >> >> > up
> >> >> > to the minute data so I can't really use a snapshot.
> >> >> > Thanks!
> >> >> > Matt Penner
> >> >> In general, a deployed report has to deal w/IIS and the SSRS web
> >> >> service, which adds overhead. Aside form this fact, if you haven't
> >> >> already, you will want to save the query for the report as a stored
> >> >> procedure, this will improve performance and response time: due to the
> >> >> stored procedure having a predefined query plan. Also, you will want
> >> >> to run the stored procedure/query against the Database Engine Tuning
> >> >> Advisor to determine if any table indexes will further improve your
> >> >> performance based on this query. Hope this helps.
> >> >> Regards,
> >> >> Enrique Martinez
> >> >> Sr. Software Consultant|||Try to simplify the report layout as much as possible - I typically
render to PDF, and find the following tips helpful -
= Use tables in place of lists (tables with grouping vs. nested lists)
= Avoid using cell borders - used alternate row shading instead to
differentiate rows
= Set the 'Can Grow' and 'Can Shrink' properties of report objects to
False wherever possible
= minimize the number of objects (merge empty cells in a table, for
instance)
= minimize the number of fonts
I believe the view is rendering in HTML, so think in terms of setting
up a report that will produce the smallest, simplest HTML document
that will correctly present your data.|||Hi Parker,
Thanks. These are great tips. I never thought of rendering straight
to PDF. Since the MS Report viewer doesn't seem to offer much that
pdf can't do, other than toggling sections, this may be a better
option. It makes sense that simplifying the end result may reduce the
render time.
I'll experiment with all this.
Matt
On Sep 29, 6:15 am, Parker <psm...@.iquest.net> wrote:
> Try to simplify the report layout as much as possible - I typically
> render to PDF, and find the following tips helpful -
> = Use tables in place of lists (tables with grouping vs. nested lists)
> = Avoid using cell borders - used alternate row shading instead to
> differentiate rows
> = Set the 'Can Grow' and 'Can Shrink' properties of report objects to
> False wherever possible
> = minimize the number of objects (merge empty cells in a table, for
> instance)
> = minimize the number of fonts
> I believe the view is rendering in HTML, so think in terms of setting
> up a report that will produce the smallest, simplest HTML document
> that will correctly present your data.
Monday, February 20, 2012
Report Render in VS2005 slower than stored proc
I have searched many forums and found some cases of people also reporting slower rendering in RS than in Management Studio / Query Analyzer. However, none of the other solution suggestions seem to make a difference for me.
I'm a VS/VB developer and have got multiple reports built -- all using stored procedures on the backend -- that all take many times longer to run than if executed via Management Studio (SSMS). My simplest proc takes a couple of parameters (no defaults included) and does a simple select against one table with a few joins. Nothing complicated. It runs in 8 secs for 6867 rows via SSMS. Through RS (running locally through Visual Studio 2005 at this point) it takes around 25-28 secs. Yet, when I'm in the report on the DATA tab (not the PREVIEW tab) the run takes the expected 8 secs ?!?!
All reports are behaving this way.
I am not using cursors.
I have no default values on parameters.
I have added the "WITH RECOMPILE" to the proc statement.
I have "SET NOCOUNT ON" as the first line of the proc.
I hate to say this, but I even connected the proc to Crystal Reports to see how it behaved. It ran in the expected 8 secs.
I've seen some mention by someone that perhaps this is a known issue of RS that it reads the proc twice. Any truth to this?
Also a couple posts have traced and demonstrated that the report is generating significantly more data "reads" via RS than through SSMS.
We're a shop that is considering a switch from Crystal to RS, but we do everything through stored procedures. I need to clear up this issue before I can go forward recommending a switch. I'm including a copy of a typical proc below for review... What am I missing? What's the deal here with RS?
IF OBJECT_ID('dbo.rpt_InactiveAccounts') IS NOT NULL
DROP PROCEDURE dbo.rpt_InactiveAccounts
GO
CREATE PROCEDURE dbo.rpt_InactiveAccounts
(@.pRunDate datetime
,@.pSalesperson varchar(5000)
,@.pIncludeOpen char(1)
)
WITH RECOMPILE
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- CREATE/SETUP TEMP TABLE FOR USE IN PARSING MULTI-VALUED STRING INPUTS
DECLARE @.NumberPivot TABLE (NumberID INT PRIMARY KEY)
DECLARE @.intLoopCounter INT
SELECT @.intLoopCounter =0
WHILE @.intLoopCounter <=4999 BEGIN
INSERT INTO @.NumberPivot
VALUES (@.intLoopCounter)
SELECT @.intLoopCounter = @.intLoopCounter +1
END
-- CREATE TEMP TABLES TO HOLD PARSED VALUES FROM MULTI-VALUE STRING INPUT PARAMETERS
DECLARE @.SalespersonTable TABLE
(tmpSalesperson varchar(30))
-- PARSE OUT @.pSALESPERSON PARAMETER AND STORE VALUES IN TEMP TABLE
INSERT INTO @.SalespersonTable
SELECT SUBSTRING(',' + @.pSalesperson + ',', NumberID + 1,
CHARINDEX(',', ',' + @.pSalesperson + ',', NumberID + 1) - NumberID -1)
FROM @.NumberPivot
WHERE NumberID <= LEN(',' + @.pSalesperson + ',') - 1
AND SUBSTRING(',' + @.pSalesperson + ',', NumberID, 1) = ','
SELECT DISTINCT
CASE
WHEN s.Name IS NULL THEN '<< OPEN >>'
ELSE s.Name
END As SalespersonName
,c.ClassId
,c.CustId
,c.Name
,c.Addr1
,c.Addr2
,c.Addr3
,c.City
,State
,CASE
WHEN Len(c.Zip) = 9 And CharIndex(' ', c.Zip, 0) = 0 THEN Left(c.Zip, 5) + '-' + Right(c.Zip, 4)
ELSE c.Zip
END As Zip
,ac1.descr As Terms
,ac2.descr As Status
FROM
ACTCustomer c (NOLOCK)
LEFT OUTER JOIN CustSales cs ON c.CustId = cs.CustId
LEFT OUTER JOIN ACTSalesperson s ON cs.SlsId = s.SalesId
INNER JOIN @.SalespersonTable st ON s.Name = st.tmpSalesperson OR (s.Name IS NULL AND @.pIncludeOpen = 'Y')
INNER JOIN ACTCode ac1 ON ac1.Code = c.Terms And ac1.FieldId = 'CustTerms'
INNER JOIN ACTCode ac2 ON ac2.Code = c.Status And ac2.FieldId = 'Status'
WHERE
c.LastInvcDate <= @.pRunDate
And c.ClassId <> 'TR'
ORDER BY
SalespersonName, Name, CustId
Hi Dave,
I don't think that this will solve you performance issue but I have a recommendation for you, since you are passing some comma separated list to your reports as parameter, maybe you should create a user-defined function that will convert you list into a table. This would allow you to reuse this same function in all of you reports.
Sample Here:
Here is an example of a function that would convert comma separated list into a table:
http://blogs.vandamme.com/development/2007/06/parse_comma_sep.html
Then you could modify your SQL statement in the stored procedure tu use this function, something like this should do:
SELECT <Fields>
FROM <Table> a
JOIN <Function> (@.CommaSeparatedList) b ON b.uid = a.id
For the performance issue, did you try to profile and compare your SSMS execution and the Report execution?
HTH,
Subscribe to:
Posts (Atom)