Re: Anybody got any speed suggestions?

pe@pre.datel-group.co.uk wrote: > > 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*" Try: select max(sequence_number) from opaudm where audits_key[1,16] = 'sop_order_header'; > > 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. You could use a trigger to maintain the last sequence number. > > Please email, thanks > > Paul > > -----== Posted via Deja News, The Leader in Internet Discussion ==----- > http://www.dejanews.com/ Now offering spam-free web-based newsreading -- Peter Lancashire Information Systems Specialist, Bayer plc Eastern Way, Bury St Edmunds, Suffolk, IP32 7AH, UK Tel: +44-1635-562258, Fax: +44-1635-562281 Mail: Peter.Lancashire.PL1@bayer.co.uk --- My Internet plumbing does not allow me to mail and post news together. Sorry. All opinions are my own and not those of Bayer plc. --- Join Infuse, the UK Informix User Group at http://www.infuse.co.uk/