Re: help for sql language

'''''' wrote: > > Hi all, please help my concern to using SQL language. > My database stored 2~4 million record in one table a day > and all redords are stored in time basis which has start and end time fields > in day table, of course it has other information in the same record. > > In above, I need to unload information to file from database and > I'm using SQL sentence as belows. > > ....... > isql xxx@yyy_tcp << EOF > unload to $outdir/zzz.txt > select * from $table > where id = "70" and etime >= "$stm;> EOF > > As I mentioned, all information stored in time sequende which is 00:00:00 ~ > 23:00:00 order and I unload files ever 30 minutes to makes 48 file a day. > However, I have some trouble in the afternoon because isql search > information from the start postion in table which takes time to collect > recent information. > So, I'd like ask anyone to collect information in reverse order from the > table in sql language. > your information will reduce my pain at this stage. > > Sincerely If I understood your question correctly, your unload is slow in the afternoon, when table gets long. The way to get data unloaded in reverse time order is simply to add 'order by etime desc' to your query. However, this will not solve your problem. Do you have index on 'etime'? Is it ascending or descending? (Actually, the indey should serve the purpose whether it is asc or desc; it only should match the 'direction' of yout query, if it is sorted). Take a look in optimizer's query plan. Perhaps optimizer 'thinks' that index on 'id' is more restrictive, while in fact it is not, so that is does index search on less restrictive clause, and sequential scan on more restrictive one. This often happens in version 5, with its simplistic statistics, or version 7 if you don't run update startistics often enough, run it 'low', or 'mid' with to few bins. If this is the case, the trick is to add a composite index on both columns. HTH Dragi "Bonzi" Raos +--------------------------------------------------------+ | 4-MATE Information Engineering Inc. | +--------------------------+-----------------------------+ | 4-MATE | Tel: +385 (1) 242-116 | | Kosa bb | +385 (1) 242-126 | | 10000 Zagreb | Fax: +385 (1) 242-121 | | Hrvatska (Croatia) | e-mail: bonzi@4mate.hr | | | URL: http://www.4mate.hr | +--------------------------+-----------------------------+