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

asked 20 Apr '11, 13:50

TimMitchell's gravatar image

TimMitchell
16115
accept rate: 0%

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.

(20 Apr '11, 17:41) Mark Culp

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

(21 Apr '11, 10:16) TimMitchell

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

(21 Apr '11, 15:00) TimMitchell

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

permanent link

answered 21 Apr '11, 07:40

Nica%20_SAP's gravatar image

Nica _SAP
866722
accept rate: 3%

edited 21 Apr '11, 07:45

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
(21 Apr '11, 11:38) TimMitchell
1

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

(21 Apr '11, 14:21) Volker Barth
Replies hidden

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

(21 Apr '11, 16:14) TimMitchell

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.

(23 Apr '11, 12:40) Volker Barth

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

(26 Apr '11, 10:34) TimMitchell

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

(26 Apr '11, 11:25) Volker Barth
Comment Text Removed
showing 2 of 6 show all flat view

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

permanent link

answered 21 Apr '11, 12:09

Nica%20_SAP's gravatar image

Nica _SAP
866722
accept rate: 3%

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.

(21 Apr '11, 12:34) TimMitchell
Replies hidden

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

(29 Apr '11, 03:45) Martin

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

permanent link

answered 21 Apr '11, 15:48

Nica%20_SAP's gravatar image

Nica _SAP
866722
accept rate: 3%

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.

(21 Apr '11, 16:07) TimMitchell

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.

permanent link

answered 03 May '11, 11:05

TimMitchell's gravatar image

TimMitchell
16115
accept rate: 0%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×137
×20

question asked: 20 Apr '11, 13:50

question was seen: 1,277 times

last updated: 03 May '11, 11:05