I have a stock movements table in v10.0.1.4239 that has 2 million rows, when using sql to select where the product field (which is in an index) equals a certain product it can take 15-20 seconds to return ~ 4000 rows for the 1st time, the 2nd time the sql is ran it only takes 4 seconds then the 3rd time it's instant, however if I then do the same select for a different product it's back up to 15-20 seconds for the 1st time. Once the database has been up and running for a few days and most of the popular products have been selected a few times everything is fast.
I've trawled through a few posts on here and the sql documentation and found somewhere that if the results for sql are spread out in the table then multiple pages will be loaded (and possibly dumped) and this can slow down the results, so to test it out I added the product field into the primary key along with an integer identity field, and ran a reorganise table command, now the sql always returns almost instantly, however I think I'm right in saying as more records are added to the table for the same product they will become spread out across more pages and the sql will eventually slow down again so I don't think this is the real solution.
If this is normal behaviour and I'm not doing something stupidly wrong (always a possibility), are there any changes I can make to the startup options for the server, or a way to pre-load whatever information is causing the sql to run slow for the first couple of times on each product ?
Our current startup options -ti 600 -tl 0 -x tcpip(port=####)
Honestly you wait until you've exhausted all other options before posting on here, then almost as soon as you have you realise you've missed something obvious.
dbisqlc returns the results of the sql in a couple of seconds, I guess it's because only the few results that are needed for the visible grid come back first then the rest are loaded when needed ? I've checked the third party component I'm using for accessing the database (NativeDB) and it has a wantrowcounts option which is true by default, I'm assuming this must force all the rows to be returned so it can count them after the cursor is opened but before we get the first result back. With this set to false it estimates the rowcount (not sure how, or if it's accurate) but it is just as fast as dbisqlc.
Luckily for me I don't normally need all the rows, just the first few (but there is no way to work out exactly how many until I have them) so it should all be a lot faster now.
At least that's my theory and it seems to be working (for now), thanks everyone.
answered 11 Nov '11, 09:05
4000 rows it's 2% of all raws in your table. 15-20sec looks too slow for index seek, so probably server use table scan. It will be better if you show table definition and query plan for first query execution and second query execution. Also, choosing index seek strategy for 2% of all raws is for me is near critical statistics value border.
answered 11 Nov '11, 05:51
I think everything works as expected: after startup, server cache is empty, so server needs to read stuff from the HDD. Eventually, everything gets loaded into RAM, hence much faster response.
answered 11 Nov '11, 06:44