on 2011 Apr 20 2:50 PM
We are currently on ASA v8 looking to upgrade to v11. I'm seeing some problems in v11 that don't appear in v8 with a particular view that summarizes information for a client. It makes use of a few other views and, while nothing is overly complicated, the explain plan shows 44 index scans. It runs fine, in a second or two, even for multiple clients in v8. The where clause uses an IN search condition, whether it be for one or multiple client IDs. "Multiple" is almost always less than 10.
However, this view runs quite slowly in v11 when using multiple IDs. A single ID still returns in less than one second. Use 4 IDs and we're looking at 15 seconds. The difference in the two plans is that for a single ID, the optimizer uses "client_id = 1". For multiple IDs, the optimizer shows that it will use an INListScan, then lists the 4 IDs. I don't know exactly how an INListScan works and I haven't been able to find any explanation. If I change the statement to 4 separate selects all unioned together, the data again returns in less than 1 second.
One other clue that I can't explain: Using ISQL, if I run the statement with "client_id in (1,2,3,4)" the data comes back in 15 seconds, but on the message tab, execution time is listed as .797 seconds. The status bar shows "Fetching rows…" the whole time.
I saved the plans in .saplan files, but they are too big to include in a post. I've uploaded them to Windows SkyDrive and they can be accessed using the following links.
In the multiple plan, I couldn't find any reference to an INListScan. I found that term by looking at the plan using QweryBuilder. But, I assume the problem is buried somewhere in this plan too.
Any help would be greatly appreciated.
Tim
The issues with your plans are related to the fact that you have set 'Optimization Goal' to 'First Row'. One cannot compare total runtimes of queries optimized with the 'First Row' optimization goal. Please set this option to 'All Rows'.
Also, for performance testings DBISQL is not a suitable tool. Use, for example, 'fetchtst.exe' (which usually can be found in /test/Bin32/fetchtst.exe in your SQL Anywhere installation directory).
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
OK, I tried using fetchtst, with all rows and got these results. The all-rows with 4 ids has a much longer fetch first row time, but it's still nothing like 15 seconds through ISQL. I don't really know where to go after this either.
optimization_goal = 'all-rows' IN (1,2,3,4)
Statement 1 (line 1): SELECT phclientid, firstname, lastname, lastn...
PREPARE: 0.001 seconds
DESCRIBE: 0.048 seconds
OPEN: 0.446 seconds
FETCH first row: 1.413 seconds, 0 I/Os, 0 per second
FETCH remaining rows: 0.000 seconds
CLOSE: 0.004 seconds
DROP: 0.000 seconds
Total: 1.910 seconds, 0 I/Os, 0 per second
Fetch rate: 4 rows in 1.413 seconds, 2.831 per second
optimization_goal = 'all-rows' IN (1)
Statement 1 (line 1): SELECT phclientid, firstname, lastname, lastn...
PREPARE: 0.001 seconds
DESCRIBE: 0.048 seconds
OPEN: 0.169 seconds
FETCH first row: 0.006 seconds, 0 I/Os, 0 per second
FETCH remaining rows: 0.000 seconds
CLOSE: 0.000 seconds
DROP: 0.000 seconds
Total: 0.224 seconds, 0 I/Os, 0 per second
Fetch rate: 1 rows in 0.006 seconds, 163.324 per second
What builds of v8 and v11 are you using? I would recommend to use the latest major version (8.0.3. and 11.0.1) and the according latest EBFs to make sure any improvements/bugfixes are contained. (Note that I'm not aware of particular fixes for such IN list optimization.) - Just as a general recommendation...
Tim, 11.0.0.1264 is the 11 GA version. Lots of folks (including me) would recommend to use a current EBF for 11.0.0 or upgrade to 11.0.1 (and a current EBF for that). As to the usage of EBFs, you may confine this question.
ASA 12 does not show this problem, so for the time being, I will let this problem go, even though I do not know the exact cause.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Can you share with us the v11 database you used for the first tests above (i.e., "FETCH first row: 1.413 seconds")?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
What are the runtimes using ASA 8.0 for these two queries?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
In v8 Single row
PREPARE: 0.000 seconds
DESCRIBE: 0.019 seconds
OPEN: 0.041 seconds
FETCH first row: 0.005 seconds, 0 I/Os, 0 per second
FETCH remaining row: 0.000 seconds
CLOSE: 0.000 seconds
DROP: 0.000 seconds
Total: 0.066 seconds, 0 I/Os, 0 per second
Client total: 0.066 seconds
Fetch rate: 1 rows in 0.005 seconds, 193.787 per second
Multiple row
PREPARE: 0.000 seconds
DESCRIBE: 0.020 seconds
OPEN: 0.045 seconds
FETCH first row: 0.008 seconds, 0 I/Os, 0 per second
FETCH remaining row: 0.002 seconds
CLOSE: 0.000 seconds
DROP: 0.000 seconds
Total: 0.075 seconds, 0 I/Os, 0 per second
Client total: 0.076 seconds
Fetch rate: 4 rows in 0.010 seconds, 418.236 per second
These times were taken from a v8 database with similar data (but not the same I have since discovered). If I use the v8 fetchtst.exe against the same v11 database I get very similar results. I do not have access to the v8 version of the v11 database. The v11 database was taken from a production site that I do not have access to.
User | Count |
---|---|
68 | |
10 | |
10 | |
7 | |
6 | |
6 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.