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