cancel
Showing results for 
Search instead for 
Did you mean: 

INListScan in query plan

Former Member
3,681

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.

Multiple Plan

Single Plan

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

MarkCulp
Participant
0 Kudos

FWIW: You could have attached the two plans to your posting directly on this site using the "paper clip" (aka Attach File) tool shown above the text box when you were entering your question.

Former Member
0 Kudos

Yeah, I missed that when I was writing the post, then saw that afterwards.

Former Member
0 Kudos

I tried but it said I need to have 100 reputation points to do that.

Accepted Solutions (0)

Answers (4)

Answers (4)

Former Member

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).

Former Member
0 Kudos

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
VolkerBarth
Contributor

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...

Former Member
0 Kudos

I can try that. We're currently using 11.0.0.1264. It might take a while to get that done.

VolkerBarth
Contributor
0 Kudos

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.

Former Member
0 Kudos

We've since decided to upgrade to ASA 12, so I'll test it there and see if the problem still exists.

VolkerBarth
Contributor
0 Kudos

Well, even for v12 I would recommend to use 12.0.1 and a recent EBF, particularly when you are about to do new development/adation:)

Former Member
0 Kudos

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.

Former Member
0 Kudos

Can you share with us the v11 database you used for the first tests above (i.e., "FETCH first row: 1.413 seconds")?

Former Member
0 Kudos

Don't think so. I'm not familiar with the rules about that but I know that it has confidential info. Plus, it's over 3GB.

Former Member
0 Kudos

What are the runtimes using ASA 8.0 for these two queries?

Former Member
0 Kudos

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.

MCMartin
Participant
0 Kudos

very similar means, that the runtime for v11 for the same db contents is the same as for v8?

In this case you might try to recreate statistics on the production db