Re: Query using cursors using 100% CPU

On Tuesday 12 February 2008 16:03:31 Glyn Astill wrote:

> Thanks Mark,
>
> I've turned this on and I never see a DECLARE CURSOR so I presume I
> was wrong and it is not using cursors.
>
> I do see a DEALLOCATE though, although no PREPARE. Before this it
> does lots of statements that are limited to 100 records, and all the
> statements are named and preceded by execute, so I presume it is
> using prepared statements getting little chunks of data and relying
> on the first execute to prepare them?

Yup, it's using prepared queries. Unfortunately prepared queries are not
always a good thing, because if your data is non-uniformly distributed then
the planner has guess what will be the best plan without knowing what
parameters you are passing in at run-time. Hence you may get a plan that is
optimal for one set of values, but not for others.

> Both tests were run over the network.

Okay. From what you mention above, it's likely that what you're seeing is a
bad query plan choice anyway.

ATB,

Mark.

--
Mark Cave-Ayland
Sirius Corporation - The Open Source Experts
http://www.siriusit.co.uk
T: +44 870 608 0063