IDS2000

Hi, I'm having a problem with the query optimiser in IDS.2000. After upgrading from 7.3 to IDS.2000, a lot of our 4gl applications seemed to be running slower. After running with set explain on, a lot of the queries were using nested loop joins. I've tried all sorts of update statistics and optimiser directives, but cannot seem to avoid them. Even the simplest joins on unique indexes run in dbaccess seem to use them. Has anyone else had this problem, or does anyone know how to get round it? Thanks for any help, Scott.

Scott wrote: > Hi, > I'm having a problem with the query optimiser in IDS.2000. > After upgrading from 7.3 to IDS.2000, a lot of our 4gl applications seemed > to be running slower. > After running with set explain on, a lot of the queries were using nested > loop joins. > I've tried all sorts of update statistics and optimiser directives, but > cannot seem to avoid them. > Even the simplest joins on unique indexes run in dbaccess seem to use them. > Has anyone else had this problem, or does anyone know how to get round it? > > Thanks for any help, > Scott. What type of join were they using previously? What is OPTCOMPIND set to? Do you have an approximation on the speed difference, instantaneous to 2 seconds ... Hope I can help, Will

What is OPTCOMPIND set to in the ONCONFIG file? Art S. Kagel Scott wrote: > > Hi, > I'm having a problem with the query optimiser in IDS.2000. > After upgrading from 7.3 to IDS.2000, a lot of our 4gl applications seemed > to be running slower. > After running with set explain on, a lot of the queries were using nested > loop joins. > I've tried all sorts of update statistics and optimiser directives, but > cannot seem to avoid them. > Even the simplest joins on unique indexes run in dbaccess seem to use them. > Has anyone else had this problem, or does anyone know how to get round it? > > Thanks for any help, > Scott.

OPTCOMPIND is set to 0 I can't remember what sort of joins were being made before (sorry!), but I'm fairly sure they weren't nested loop joins, and it is definetly slower than before. Queries that used to take 40 - 50 seconds now take 2 or 3 minutes. Thanks for any help! Scott. "William Rice" <william.rice@sabre.com> wrote in message news:39B79812.AA9622B0@sabre.com... > Scott wrote: > > > Hi, > > I'm having a problem with the query optimiser in IDS.2000. > > After upgrading from 7.3 to IDS.2000, a lot of our 4gl applications seemed > > to be running slower. > > After running with set explain on, a lot of the queries were using nested > > loop joins. > > I've tried all sorts of update statistics and optimiser directives, but > > cannot seem to avoid them. > > Even the simplest joins on unique indexes run in dbaccess seem to use them. > > Has anyone else had this problem, or does anyone know how to get round it? > > > > Thanks for any help, > > Scott. > > What type of join were they using previously? > What is OPTCOMPIND set to? > > Do you have an approximation on the speed difference, instantaneous to 2 > seconds ... > > Hope I can help, > Will >

Scott O'Rourke wrote: > OPTCOMPIND is set to 0> > I can't remember what sort of joins were being made before (sorry!), but I'm > fairly sure they weren't nested loop joins, and it is definetly slower than > before. > > Queries that used to take 40 - 50 seconds now take 2 or 3 minutes. > > Thanks for any help! > Scott. > > "William Rice" <william.rice@sabre.com> wrote in message > news:39B79812.AA9622B0@sabre.com... > > Scott wrote: > > > > > Hi, > > > I'm having a problem with the query optimiser in IDS.2000. > > > After upgrading from 7.3 to IDS.2000, a lot of our 4gl applications > seemed > > > to be running slower. > > > After running with set explain on, a lot of the queries were using > nested > > > loop joins. > > > I've tried all sorts of update statistics and optimiser directives, but > > > cannot seem to avoid them. > > > Even the simplest joins on unique indexes run in dbaccess seem to use > them. > > > Has anyone else had this problem, or does anyone know how to get round > it? > > > > > > Thanks for any help, > > > Scott. > > > > What type of join were they using previously? > > What is OPTCOMPIND set to? > > > > Do you have an approximation on the speed difference, instantaneous to 2 > > seconds ... > > > > Hope I can help, > > Will > > OPTCOMPIND of 2 might be helpful for the queries you are looking at, but might have a detrimental effect on other queries. I believe that with 0 that the system tries to use indexes whenever possible. 2 chooses the query plan with the least estimated cost. Hope this helps, Will

Just going through testing of a 7.24.UC8 -> 9.20.UC4 upgrade myself. Found one query so far that I just had to re-write. I don't know how the old engine even managed to perform that one with decent response time! What have you set OPT_GOAL to? -1 will optimize queries for all rows (pretty much the behavior of the older engines), while 0 will optimize to get the first row back quicker, but perhaps slow down the overall query. HTH, Irwin In article <8p8tuo$ff5$1@lure.pipex.net>, "Scott O'Rourke" <scott@sccs-ltd.co.uk> wrote: > OPTCOMPIND is set to 0> > I can't remember what sort of joins were being made before (sorry!), but I'm > fairly sure they weren't nested loop joins, and it is definetly slower than > before. > > Queries that used to take 40 - 50 seconds now take 2 or 3 minutes. > > Thanks for any help! > Scott. > > "William Rice" <william.rice@sabre.com> wrote in message > news:39B79812.AA9622B0@sabre.com... > > Scott wrote: > > > > > Hi, > > > I'm having a problem with the query optimiser in IDS.2000. > > > After upgrading from 7.3 to IDS.2000, a lot of our 4gl applications > seemed > > > to be running slower. > > > After running with set explain on, a lot of the queries were using > nested > > > loop joins. > > > I've tried all sorts of update statistics and optimiser directives, but > > > cannot seem to avoid them. > > > Even the simplest joins on unique indexes run in dbaccess seem to use > them. > > > Has anyone else had this problem, or does anyone know how to get round > it? > > > > > > Thanks for any help, > > > Scott. > > > > What type of join were they using previously? > > What is OPTCOMPIND set to? > > > > Do you have an approximation on the speed difference, instantaneous to 2 > > seconds ... > > > > Hope I can help, > > Will > > > > -- Irwin Goldstein Objective Software Systems, Inc. http://www.objectsoft.com Sent via Deja.com http://www.deja.com/ Before you buy.