SQL Optimizing- Indexes and Order By clause

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Avichal Narayan - 10 Jul 2008 01:22 GMT

Hi

I have a simple SQL which iam trying to optimize.

select debt_code, debt_surname, debt_firstnames
from debtor
where debt_surname matches "Smith"
order by debt_surname, debt_firstnames

I have a composite index on debt_surname, debt_firstnames on debtor table.
I also have a unique index on debt_code in debtor table.

Questions:

If i dont put the order by clause, will i still get the same results as with
order by clause since index is created.
If not whats the best way to optimize this sql. The debtor table contains
some 18million records. If i put a surname that returns maybe 20-30 rows is
much faster. But if i use the above surname-"Smith" which contains some 3
million records it gets really slow.

I am using this in I4GL and only going to select the first 300 sorted
records into the array. So i need it to be sorted.

Any assistance will be appreciated.

TIA

Avichal

Reply to this Message

Fernando Nunes - 10 Jul 2008 02:26 GMT

> Hi
>
[quoted text clipped - 28 lines]
>
> Avichal

If you don't use an order by clause, you can't expect any specific order.
This is a fact, even that in 99.999999% of the situations you may know that it
will give you the records in a certain order.

You should (assuming you're using a version that supports it) use:

SELECT FIRST 300 <...>
FROM debtor
...

Then check that it is using the index and look for a "temporary file needed..."
I would expect it to don't need the temporary file, but I had a recent
situation where this would only happen if I included a hint specifying
"FIRST_ROWS"...

Try it, and if you still have doubts show us the query plans and indicate your
version.

It it works as expected, the FIRST 300 should limit the result set, and the
query should be fairly quick whichever the surname you specify...
If it states it need a temporary file, than this will be a good topic for
discussion here ... :)

Note that i4GL will probably not accept the "FIRST 300" clause...
So you'll have to include it in "SQL ... END SQL" block (v7.31+) or prepare the
statement in previous versions.

Regards.
Regards.

Reply to this Message

bozon - 11 Jul 2008 14:47 GMT

On Jul 9, 8:22 pm, "Avichal Narayan" <avichal.nara...@gmail.com>
wrote:

> Hi
>
[quoted text clipped - 25 lines]
>
> Avichal

Yes, use the first clause.

If you are paging through a list of debtors 300 at a time then the
skip clause will also come in handy.

select first 300
debt_code,
debt_surnmae,
debt_firstnames
from
debtor
where
debt_surname matches "Smith"
order by
debt_surname, debt_firstnames, debt_code -- needed for uniqueness of
order if both names match.
;

select first 300
debt_code,
debt_surnmae,
debt_firstnames
from
debtor
where
debt_surname matches "Smith"
order by
debt_surname, debt_firstnames, debt_code -- PK
;

Reply to this Message