SQL Optimizing- Indexes and Order By clause

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

Avichal Narayan wrote: > 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 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.