Re: RE: Distributed Query Performance in IDS 10

Maybe an optimizer hint would be usefull? I don't know, I'm just guessing. J. -----Original Message----- From: DL Redden <redden96@yahoo.com> To: David Buchholz <David.Buchholz@vitalps.com>, Informix List <informix-list@iiug.org> Date: Fri, 26 Aug 2005 13:34:08 -0700 (PDT) Subject: RE: Distributed Query Performance in IDS 10 --- David Buchholz <David.Buchholz@VITALPS.COM> wrote: Dave, Thanks for the bug number. I've opened a case with IBM as well so we'll see if they match it to the byg number you provided. Our remote server is IDS 7.31. DL > DL, > > We reported this issue to IBM and they have opened > bug #173922 > for this issue. We only noticed this if the remote > server is XPS. What > is the remote server in your environment? > > -Dave > David Buchholz > Vital Processing Services > david.buchholz@vitalps.com > > -----Original Message----- > From: owner-informix-list@iiug.org > [mailto:owner-informix-list@iiug.org] > On Behalf Of DL Redden > Sent: Friday, August 26, 2005 10:06 AM > To: Informix List > Subject: Distributed Query Performance in IDS 10 > > I have a query that runs fast (1 second) in our IDS > 9.30 environment but runs long (I quit waiting after > 11 minutes) in our IDS 10 environment. The cause is > a > remote query. > > The explain from a 9.3 instance: > > .... > 2) psoft.ps_bi_line: REMOTE PATH > > Remote SQL Request: > select x0.original_invoice ,x0.original_line_seq > ,x0.invoice ,x0 > .line_seq_num from finprod:"psoft".ps_bi_line x0 > where ((? = x0. > invoice ) AND (? = x0.line_seq_num ) )> .... > > The explain from a 10 instance: > > .... > 8) psoft.ps_bi_line: REMOTE PATH > > Remote SQL Request: > select x0.original_invoice ,x0.original_line_seq > ,x0.invoice ,x0 > .line_seq_num from finprod:"psoft".ps_bi_line x0> > > .... > > Two things I think are important: > > 1) The remote query is moved down from number 2 in > IDS > 9.3 to number 8 in IDS 10. Probably not crucial but > I > noticed it anyway. > > 2) The filter that gets pushed down to the remote > instance is missing from the IDS 10 explain, this > means that the entire ps_bi_line table will be > retrieved (~12 million rows). I'm pretty sure that's > bad. > > Any ideas on how to undo this bad optimization? > > Thanks, > > DL > sending to informix-list sending to informix-list Jean Sagi jeansagi@myrealbox.com jeansagi@gmail.com sending to informix-list