MS SQL Server Connectivity - SP running slower from access/crystal reports than query analyzer

Author

SP running slower from access/crystal reports than query analyzer

Kalle Dahlberg

2006-11-01, 7:12 pm

Hi!

I have a strange problem.

I have a few procedures which run fast in query analyzer, but extremely slow
from Access (via ODBC) and for example Crystal Reports. The difference is
enormous, the procedure takes about 1 second to run from QA; it takes about
200 seconds from Access. With the same parameters of course. Also, when I use
SQL profiler and look at CPU time and reads, the execute from MS Access lists
just above 111 000 000 reads, while the same execute from QA is only 99 000
reads.

The proc itself is quite simple, but uses a table valued UDF to do some
calculations.

I got the tip from someone to do "SET NO_BROWSETABLE ON" in the proc, but
that didn't help.

I really need some help with this.

Best regards,
Kalle Dahlberg

Kalle Dahlberg

2006-11-01, 7:12 pm

Forgot some info, adding it here.

Version of SQL Server is 8.00.818 (SQL 2000 standard edition)

Server is a 4-way win 2000 standard with 2GB RAM.

Server has 2 named instances running.

When I restore the database to another server with almost equal
specifications, the procs run fine from Crystal Reports / Access.

best regards,
Kalle

"Kalle Dahlberg" wrote:

> Hi!
>
> I have a strange problem.
>
> I have a few procedures which run fast in query analyzer, but extremely slow
> from Access (via ODBC) and for example Crystal Reports. The difference is
> enormous, the procedure takes about 1 second to run from QA; it takes about
> 200 seconds from Access. With the same parameters of course. Also, when I use
> SQL profiler and look at CPU time and reads, the execute from MS Access lists
> just above 111 000 000 reads, while the same execute from QA is only 99 000
> reads.
>
> The proc itself is quite simple, but uses a table valued UDF to do some
> calculations.
>
> I got the tip from someone to do "SET NO_BROWSETABLE ON" in the proc, but
> that didn't help.
>
> I really need some help with this.
>
> Best regards,
> Kalle Dahlberg

Sylvain Lafontaine

2006-11-09, 7:14 pm

Bad query plan: the first thing to do would be to recompile the SP by
clearing the procedure cache or to add the WITH RECOMPILE option to the SP
if the parameters transmitted are very different from call to call:

dbcc freeproccache
go

The second thing to do would be to update the statistics (or reindexes all
tables, as this will also update the statistics). For example:

DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX (@TableName, ' ') With NO_INFOMSGS
-- or: DBCC DBREINDEX (@TableName, ' ', 90) With NO_INFOMSGS

FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor
DEALLOCATE TableCursor
GO

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

"Kalle Dahlberg" <kalle@newsgroups.nospam> wrote in message
news:DE9798C9-B4C0-4868-A7EB- BD117AEAD446@microso
ft.com...[color=darkred]
> Forgot some info, adding it here.
>
> Version of SQL Server is 8.00.818 (SQL 2000 standard edition)
>
> Server is a 4-way win 2000 standard with 2GB RAM.
>
> Server has 2 named instances running.
>
> When I restore the database to another server with almost equal
> specifications, the procs run fine from Crystal Reports / Access.
>
> best regards,
> Kalle
>
> "Kalle Dahlberg" wrote:
>

Sponsored Links