RE: Distributed Query Performance in IDS 10

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