Re: Embedded sql without index faster than with index

[Vern Hamberg <vhamberg@xxxxxxxxxxx>]

David

If you are on a fairly recent release, there is a plan cache for SQL, so 
that even dynamic SQL might not need to reoptimize things. This means 
that as a statement is run enough, its access plan will be stored. 
Hence, better performance. The first time something is run, things can 
be a bit slow. Without an index, it is likely a temporary had to be 
built, and that can be slow.

All this is a guess but is definitely a possibility. The only way to 
know for sure is to run a monitor over the whole process - something not 
practical most of the time.

Anyhow, you got 2 good things - your first declared cursor, and you saw 
the benefit of having a suitable index - the latter is a must must have 
for good SQL performance.

HTH
Vern

On 3/8/2011 2:52 AM, David FOXWELL wrote:
> Thanks all,
>
> I'm now running 2 programs side by side in 2 sessiions and comparing results :
>
> The original dynamic version :
> wSql =
>      'SELECT * FROM MYFILE +
>       WHERE +
>           DATE   BETWEEN ''' + wDate1    + ''' AND +
>                          ''' + wDate2    + ''' AND +
>           CLIENT BETWEEN '   + wClientN1 +   ' AND +
>                          '   + wClientN2 +   ' AND +
>           AMOUNT BETWEEN '   + wAmount1  +   ' AND +
>                          '   + wAmount2  +   ';
>
>
> and the new static version :
> 	DECLARE C1 CURSOR FOR
>    		SELECT * FROM MYFILE
>    		WHERE
>        	date   BETWEEN : wDate1    AND : wDate2    AND
>        	client BETWEEN : wClientN1 AND : wClientN2 AND
>       		amount BETWEEN : wAmount1  AND : wAmount2  ;
>
> At least I did my first static cursor so thanks for that.
>
> But..... when I enter only a client number and no amount, eg client between 123 and 123 and amount between 0 and 9999999999.99 the results are fast..as I would expect because the client is in the index.
>
> Here's the thing : at first I thought that the dynamic version was running faster than the static one. I have continued testing and I cannot say which is the fastest. I am entering data in both screens then rapidly hitting enter on one screen and switching to the other. I have no explanation why it was running so slowly yesterday. Or why the same request executed in inav ran so much faster.
>
> Anyhow, I shall probably now create an index on the amount. If I'd done that in the first place I'd probably never have noticed anything.