All Forums Database
aqeel.naser 2 posts Joined 03/11
28 Jan 2012
High Performance Select Statements


I have a very large table which has a primary index (x, y). I am trying to perform a search on multiple areas; e.g. area 1 is located between x=1, y=1 and x=100, y=100. My aim is to retrieve the first available row for each searching area as fast as possible.

What I have done so far is using a multi-statement SELECT request for each searching area; each query is against a singe x and y value which performs a single AMP step without fully scanning all rows. I didn't want to use a SELECT statement which has something like "where x between 1 and 100 and y between 1 and 100;" as this will perform a scan on all rows.

1. How do I know how many SQL commands I can send per multi-statement request?

2. Is there a faster way to perform this task?


Thanks a lot.

dnoeth 4628 posts Joined 11/04
28 Jan 2012

For your existing DDL:
Instead of MultiStatement with single values better use "where x in (1,2,3,4,5) and y in (1,2,3,4,5)".

The optimizer will create all possible combinations and use PI-access, but you have to test what's the maximum number of values/combinations before it switches to a Full Table Scan. E.g. if you can use 1..10 it's down to 100 queries vs. 10000 for single value.


Another (probably faster way) would be partitioning the table, e.g.:
partition by range_n (x between 1 and 100000 each 1000) , range_n(y between 1 and 100000 each 1000))



aqeel.naser 2 posts Joined 03/11
28 Jan 2012

Thanks Dieter.

I did some initial measures .. it seems that your hint is giving me a boost in performance.


Thanks again :)

You must sign in to leave a comment.