|
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. It also looks like a situation to think about the fitting setting of the "optimization_goal" option - possibly not for the whole database, but for particular connections and/or the OPTION clause of particular SQL statements... CAVEAT: This comment was automatically created by OPTION(FORCE OPTIMIZATION).
(11 Nov '11, 09:15)
Volker Barth
Replies hidden
One of the many options I had tried before posting on here, except now it might actually be useful :-)
(11 Nov '11, 09:20)
Daz Liquid
|
|
see also Using cache warming, I don't remeber if it was switched on by default in version 10.x. Anyway in 11 and up it is. From the v10 help I think it's turned on by default :(
(11 Nov '11, 07:41)
Daz Liquid
Replies hidden
1
However, AFAIK the "warm cache" will only contain the pages that have been loaded on the last database start, and if these weren't the ones containing the desired rows, that won't help much... I would try to add some "pre-load relevant pages" queries, as discussed in the comments on this forum answer...
(11 Nov '11, 08:41)
Volker Barth
|
|
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. umm 2%, my maths is bad, but are you sure about that ?
(11 Nov '11, 06:03)
Daz Liquid
Replies hidden
Comment Text Removed
sorry, 0.2% :( but, 15-20sec it's time looks normal for table scan even if DB file store on standalone HDD.
(11 Nov '11, 06:54)
AlexeyK77
|
|
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. |
