High Performance Select Statements

Database

High Performance Select Statements

Hi,

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.

2 REPLIES
N/A

Re: High Performance Select Statements

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

Dieter

Re: High Performance Select Statements

Thanks Dieter.

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

Thanks again :)