sql7 stored procs very slow when using cursors with order by clause

sql7 stored procs very slow when using cursors with order by clause

Post by Neil Pik » Sun, 18 Jul 1999 04:00:00



Vince - what does a showplan show?

Quote:> We have an c/s app which uses sql7 as backend(upgraded from sql6.5). We
> use stored procs with cursors .. and 'order by' clause for results. The
> performance for these stored procs has been unbearably slow. What used
> to take miniutes with 6.5 now takes hours (when 200,000-row tables are
> involved). The performance is acceptable when 'order by' is not used.

> We have installed sp1 but there is no apprarent improvement.

> Has anyone else encountered the same problem?
> Are we talking about waiting for sp2 or sp3 to fix this?

 Report it to MS PSS - if they don't receive a fault call they can't fix it.

 Neil Pike MVP/MCSE.  Protech Computing Ltd
 (Please post ALL replies to the newsgroup only unless indicated otherwise)
 For SQL FAQ entries see
 http://go.compuserve.com/sqlserver (library 1) - latest stuff is always here
 www.ntfaq.com/sql.html
 http://www.swynk.com/faq/sql/sqlserverfaq.asp

 
 
 

sql7 stored procs very slow when using cursors with order by clause

Post by Vince Le » Mon, 19 Jul 1999 04:00:00


Hi,

We have an c/s app which uses sql7 as backend(upgraded from sql6.5). We
use stored procs with cursors .. and 'order by' clause for results. The
performance for these stored procs has been unbearably slow. What used
to take miniutes with 6.5 now takes hours (when 200,000-row tables are
involved). The performance is acceptable when 'order by' is not used.

We have installed sp1 but there is no apprarent improvement.

Has anyone else encountered the same problem?

Are we talking about waiting for sp2 or sp3 to fix this?

Regards,
acl

 
 
 

sql7 stored procs very slow when using cursors with order by clause

Post by Hal Berenso » Tue, 20 Jul 1999 04:00:00


An interesting situation you may want to check:

The way cursors work (have always worked) is that you ask for a cursor of a
specific type.  If the server can't give you that type of cursor, it gives
you a less functional cursor ("implicit conversion").  You can test which
kind of cursor you received after the fact, but I suspect most people don't
do so.  Not only that, many people seem to ask for cursors with more
functionality then they really need.  So lets say in 6.5 you asked for
(perhaps without realizing it) a dynamic cursor, couldn't get one for some
reason, and got a static cursor instead.  Lets say that you never checked to
see what kind of cursor it was, but you also never used any functionality
that the static cursor couldn't provide.  So your app worked correctly.  Now
7.0 comes along and has removed the restriction that caused the implicit
conversion to occur in 6.5, so you are getting the dynamic cursor you
actually requested.  Well, what if the cursor you asked for is actually
slower than the cursor you were unintentionally getting in 6.5?

This is not a far-fetched scenario.  Here is a statement from the 7.0 Books
Online about a 6.5 limitation that is removed in 7.0:

"If a dynamic cursor is requested and the Transact-SQL statement contains an
ORDER BY that does not match an index or subquery, the cursor is converted
to a keyset-driven or static cursor. If all the tables have a unique index,
but no index that covers the ORDER BY, the cursor is converted to a
keyset-driven cursor. If at least one table has no index that covers the
ORDER BY and at least one has no unique index (not necessarily the same
table), the cursor is converted to static. "

So in 6.5 it could be that you were asking for a dynamic cursor and getting
a keyset or static.  In 7.0 you get the dynamic cursor you asked for, but
walking whichever index is selected turns out to be slower than the keyset
or static cursor population.  You could test this by modifying your code to
explicitly request keyset or static (and try both, since you don't know
which you were getting in 6.5).

Hal
--
All opinions expressed here are mine and mine alone


Quote:> Hi,

> We have an c/s app which uses sql7 as backend(upgraded from sql6.5). We
> use stored procs with cursors .. and 'order by' clause for results. The
> performance for these stored procs has been unbearably slow. What used
> to take miniutes with 6.5 now takes hours (when 200,000-row tables are
> involved). The performance is acceptable when 'order by' is not used.

> We have installed sp1 but there is no apprarent improvement.

> Has anyone else encountered the same problem?

> Are we talking about waiting for sp2 or sp3 to fix this?

> Regards,
> acl

 
 
 

1. Sybase/VB - 40088 No cursor open or cursor closed w/ stored procs

Using Sybase System 11 Sybase ODBC driver 3.1 (Open Client 11.1.1) with RDO
2.0 and VB5.0...(and a wide variety of NT and 95 configurations)

I receive No cursor open or cursor closed when using stored procs with
greater than a single select statement.

Any ideas? I had similar problems on MS SQL Server, but they were resolved
when I set the rdoEngine cursor diver to rdUseOdbc. Needless to say this
doesn't work with Sybase.

I've seen a few posts that claimed success with "SET NOCOUNT ON" in
procedures, but this did not seem to work.


2. Reportsmith & Misc.

3. SQL7 BUG : SELECT DISTINCT with ORDER BY CLAUSE

4. Error in delete table on linked server

5. Cursor becoming read-only with order by clause

6. problem connecting a remote database

7. ASP, Stored Procs, Recordsets & Parameters under SQL7

8. Image data type..

9. Performance problems after SQL7 Upgrade with Stored Procs

10. ASP, Stored Procs, Recordsets & Parameters under SQL7

11. full-text search in SQL7 w/stored procs

12. sysdepends and finding out the correct run order for stored procs