Re: Embedded sql without index faster than with index

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.