Re: Stored Procedure slow when called from Crystal Reports
- From: "Scott" <scott@xxxxxxxxxxx>
- Date: Tue, 5 Apr 2005 23:45:02 -0700
I was doing more testing with the stored procedure from with MS Access and
realised that it is called slightly differently from Access. Access executes
another TSQL statement immediately before calling the SP. Here it is:
SET NO_BROWSETABLE OFF
Apparently it is an undocumented feature in SQL Server 2000, but when I add
that statement to my stored procedure that is executed by the report, the
report runs about 3 times faster - almost as fast as the SP on its own.
Magic!!! But why does it work? I assume the statement has some side effect
on the optimiser, but I am really just guessing. Does anyone know for sure?
I have only found this performance issue in complex SPs. This one has five
or six tables, mostly with OUTER JOINs, a WHERE BETWEEN clause on a datetime
column and a GROUP BY that SUMs about 100,000 rows down to about 100. If you
are still interested Brian, I can package up an install script for my DB, and
you should be able to reproduce the problem with any simple report on that SP.
Scott.
"Brian Bischof" wrote:
> Hey Scott, do you have something you could send me? I would like to debug it
> and see if there is anything strange going on behind the scenes. If you can
> get me a zip file with something that I could run on my computer then send
> it to the email address listed on my website.
>
>
> Brian Bischof
> www.CrystalReportsBook.com
>
>
> "Scott" <scott@xxxxxxxxxxx> wrote in message
> news:6FB2591C-047F-40EB-84D3-8A6D3B242820@xxxxxxxxxxxxxxxx
> > I know what you mean. Yes, it is slow the first time, but that's before
> the
> > stored procedure is executed. I know this because my app opens the report
> > and looks at the parameters, filling in AppName etc where it can
> > automatically and prompting the using for input. The first time it takes
> a
> > long time to get to the prompting stage, and I have also assumed it's
> loading
> > all the CR libraries etc.
> >
> > However, the stored procedure takes a long time to run each time.
> > I'll go looking for fixes as you suggested.
> >
> > Scott.
> >
> >
> > "dave" wrote:
> >
> > > Is it slow only the first time it's called from the application, or is
> > > it always equally slow? The first report called is always the slowest -
> > > seems like the print engine is loading into memory - but subsequent
> > > calls to the same/other reports are faster.
> > >
> > > You might want to check the Business Objects site for service
> > > packs/hotfixes for your version of CR for .NET:
> > >
> http://support.businessobjects.com/downloads/updates/service_packs/default.asp#Crystal%20Reports%20Service%20Packs
> > > http://support.businessobjects.com/fix/hot/mhf/crystalreports.asp
> > >
>
>
>
.
- References:
- Stored Procedure slow when called from Crystal Reports
- From: Scott
- Re: Stored Procedure slow when called from Crystal Reports
- From: dave
- Re: Stored Procedure slow when called from Crystal Reports
- From: Scott
- Re: Stored Procedure slow when called from Crystal Reports
- From: dave
- Re: Stored Procedure slow when called from Crystal Reports
- From: Scott
- Re: Stored Procedure slow when called from Crystal Reports
- From: Brian Bischof
- Stored Procedure slow when called from Crystal Reports
- Prev by Date: Re: Installing Crystal Report 10 Run-time on a windows 98 PC
- Next by Date: Re: Crystal 8.5 and informix database
- Previous by thread: Re: Stored Procedure slow when called from Crystal Reports
- Next by thread: Re: Stored Procedure slow when called from Crystal Reports
- Index(es):
Relevant Pages
|
