Odd performance dropoff inside a cursor

Odd performance dropoff inside a cursor

Post by Richard McConnel » Fri, 22 Sep 2000 04:00:00



Hi all,

I am experiencing a bit of an odd problem which I hope someone has seen
before - does anyone know of an issue that would cause performance slowdowns
of SQL statements that are running inside of a cursor?  I am running a
reasonably intensive data conversion process on a Compaq 8500 (8 x 550Mhz
Xeon, 1.7GB RAM, SQL 7.0 EE SP2, on a Win2K Advanced platform) and am
getting fantastic performance from any basic SELECT, UPDATE, DROP or INSERT
statements - but for whatever reason, whenever a script launches a cursor,
starts a fetch and starts performing SELECT / INSERT activity within the
cursor query performance drops right off.  I restored the database to an
smaller-scale platform (Compaq 5500 running SQL 7.0 SP2 Std, on Win2K Std)
and ran the identical query and found that performance was substantially
slower on all basic read/write activity *except* the cursor work - where it
was three times faster than on the large box.  I double-checked the
execution plans on each server and verified that both were identical; the
only thing that is obvious is that the 8500 has a substandard RAID
controller with no write-back cache (however, a review of the Disk Queue
Lengths do not indicate a disk bottleneck problem).

If anyone has any bright ideas on what might be causing this, I'd appreciate
the feedback....!

TIA,
RM

 
 
 

Odd performance dropoff inside a cursor

Post by Keith Kratochvi » Fri, 22 Sep 2000 04:00:00


Strange.
Are you running with updated stastics?
Are the indexes the same?
"substandard raid" this could do it.  Are the disks/controller(s) on the
other machine better/faster?

Keith


Quote:> Hi all,

> I am experiencing a bit of an odd problem which I hope someone has seen
> before - does anyone know of an issue that would cause performance
slowdowns
> of SQL statements that are running inside of a cursor?  I am running a
> reasonably intensive data conversion process on a Compaq 8500 (8 x 550Mhz
> Xeon, 1.7GB RAM, SQL 7.0 EE SP2, on a Win2K Advanced platform) and am
> getting fantastic performance from any basic SELECT, UPDATE, DROP or
INSERT
> statements - but for whatever reason, whenever a script launches a cursor,
> starts a fetch and starts performing SELECT / INSERT activity within the
> cursor query performance drops right off.  I restored the database to an
> smaller-scale platform (Compaq 5500 running SQL 7.0 SP2 Std, on Win2K Std)
> and ran the identical query and found that performance was substantially
> slower on all basic read/write activity *except* the cursor work - where
it
> was three times faster than on the large box.  I double-checked the
> execution plans on each server and verified that both were identical; the
> only thing that is obvious is that the 8500 has a substandard RAID
> controller with no write-back cache (however, a review of the Disk Queue
> Lengths do not indicate a disk bottleneck problem).

> If anyone has any bright ideas on what might be causing this, I'd
appreciate
> the feedback....!

> TIA,
> RM


 
 
 

Odd performance dropoff inside a cursor

Post by JRSte » Fri, 22 Sep 2000 04:00:00


On Thu, 21 Sep 2000 20:59:53 GMT, "Richard McConnell"


> does anyone know of an issue that would cause performance slowdowns
>of SQL statements that are running inside of a cursor?  

Default cursor type is dynamic.

Change them to fast_forward or forward_only and thank me later.

Joshua Stern


 
 
 

Odd performance dropoff inside a cursor

Post by Richard McConnel » Sat, 23 Sep 2000 10:53:55


Both servers are configured to auto-update stats.  Indexes are identical.
The disks on the other server are actually slower (4 x 7K drives) but the
controller is superior.  The only thing that puzzles me is why straight
inserts scream on the new server in comparison to the older box but inserts
within a cursor do not - if the disk subsystem was the bottleneck, shouldn't
this be consistent irregardless of a cursor being used?



> Strange.
> Are you running with updated stastics?
> Are the indexes the same?
> "substandard raid" this could do it.  Are the disks/controller(s) on the
> other machine better/faster?

> Keith



> > Hi all,

> > I am experiencing a bit of an odd problem which I hope someone has seen
> > before - does anyone know of an issue that would cause performance
> slowdowns
> > of SQL statements that are running inside of a cursor?  I am running a
> > reasonably intensive data conversion process on a Compaq 8500 (8 x
550Mhz
> > Xeon, 1.7GB RAM, SQL 7.0 EE SP2, on a Win2K Advanced platform) and am
> > getting fantastic performance from any basic SELECT, UPDATE, DROP or
> INSERT
> > statements - but for whatever reason, whenever a script launches a
cursor,
> > starts a fetch and starts performing SELECT / INSERT activity within the
> > cursor query performance drops right off.  I restored the database to an
> > smaller-scale platform (Compaq 5500 running SQL 7.0 SP2 Std, on Win2K
Std)
> > and ran the identical query and found that performance was substantially
> > slower on all basic read/write activity *except* the cursor work - where
> it
> > was three times faster than on the large box.  I double-checked the
> > execution plans on each server and verified that both were identical;
the
> > only thing that is obvious is that the 8500 has a substandard RAID
> > controller with no write-back cache (however, a review of the Disk Queue
> > Lengths do not indicate a disk bottleneck problem).

> > If anyone has any bright ideas on what might be causing this, I'd
> appreciate
> > the feedback....!

> > TIA,
> > RM

 
 
 

Odd performance dropoff inside a cursor

Post by Richard McConnel » Sat, 23 Sep 2000 10:59:33


I'll give it a try, thanks - this doesn't really explain why cursor
performance was quicker on the older platform when both servers were set to
use the default cursor type, but if it speeds things up I don't really care
:)

Unless for some bizarre reason the default cursor type is different between
SQL 7.0 STD and EE?


> On Thu, 21 Sep 2000 20:59:53 GMT, "Richard McConnell"

> > does anyone know of an issue that would cause performance slowdowns
> >of SQL statements that are running inside of a cursor?

> Default cursor type is dynamic.

> Change them to fast_forward or forward_only and thank me later.

> Joshua Stern



 
 
 

Odd performance dropoff inside a cursor

Post by JRSte » Mon, 25 Sep 2000 09:50:39


On Fri, 22 Sep 2000 01:59:33 GMT, "Richard McConnell"


>Unless for some bizarre reason the default cursor type is different between
>SQL 7.0 STD and EE?

Two processors on the EE box, versus one on the STD box?

It would of course be odd, just as you say.  However, you might have
turned up a bug, bordering on a feature, of the
parallelizer/optimizer.  Using fast_forward may leave the optimizer
less room for "creativity", or just sort of kick it back onto the
fairway.  I've never really gotten that deep into SQLServer
metaphysics, I just try different syntax until it works at the speed
desired, with correct results.

Joshua Stern