Re: Anybody got any speed suggestions?

In article <6h1vr4$41s$1@nnrp1.dejanews.com>, pe@pre.datel-group.co.uk writes >Hi all, I currently have a situation whereby a program is taking far to long >to run, after investigation it is because of the following statement: > >select max(sequence_number) from opaudm where audits_key matches >"sop_order_header*" > Find out what it is at the moment say <x> and do select max(sequence_number) from opaudm where audits_key matches "sop_order_header*" and sequence_number > <x-1> If you have Online 7 fragment the table as well and have audits_key matches "sop_order_header*" and sequence_number < x in one dbspace and remainder in another dbspace! This should allow fragment elimination and hence may things faster. In think expression based fragmentation and range searches allow fragment elimination, you may have to experiment though! >There is an index on audits_key and sequence_number but the table itself has >400,000 records and there are around 50,000 which have sop_order_header in >the audits_key. > >Has anybody got any suggestion of getting the last sequence number rather >than using the select max. > >For your info the set explain command shows: > >QUERY: >------ >select max(sequence_number) from opaudm where > audits_key matches sop_order_header*" > >Estimated cost 145 >Estimated # of Rows Returned: 1 > >1) roger.opaudm: INDEX PATH > > (1) Index Keys: audits_key sequence_number > Lower Index Filter: roger.opaudm.audits_key MATCHES > 'sop_order_header*' > > >Please note that for various reasons I can't store the last sequence number >elsewhere because there are other programs outside of my control which write >to this table. > >Please email, thanks > >Paul > >-----== Posted via Deja News, The Leader in Internet Discussion ==----- >http://www.dejanews.com/ Now offering spam-free web-based newsreading -- David Williams Maintainer of the Informix FAQ Primary site (Beta Version) http://www.smooth1.demon.co.uk Official site http://www.iiug.org/techinfo/faq/faq_top.html I see you standin', Standin' on your own, It's such a lonely place for you, For you to be If you need a shoulder, Or if you need a friend, I'll be here standing, Until the bitter end... So don't chastise me Or think I, I mean you harm... All I ever wanted Was for you To know that I care