Query Tuning -- Again

Posted in 2004

Topics: Performance & Tuning, Storage & Space Management, SQL Development & Query Writing

Anthony Presley — 28 Jul 2004 09:38:53 -0700 — source: Usenet: comp.databases.informix

Hi all, I'm back at it -- and still haven't found a GREAT reference on tuning queries. IE, where / how do you put your Indexes? I've used Art's utilities (and Obnoxio's) to some great extent -- but so far as I have seen, they don't do a wonderful job [any?] of telling me "You need an index HERE". To that end, I've been using p6Spy, which works like a charm. I know I need to tune THIS query -- but am not 100% sure where to start. Can someone please provide some pointers? The SQExplain is: QUERY: ------ select unique (dassigned) as dassigned FROM leadsheet where dassigned <= TODAY AND inv_id NOT IN (Select inv_id FROM inv_contracts WHERE contract_id != 1) ORDER BY dassigned DESC Estimated Cost: 92071 Estimated # of Rows Returned: 1 Temporary Files Required For: Order By 1) root.invention: SEQUENTIAL SCAN Filters: (root.invention.id != ALL <subquery> AND root.invention.id != ALL <subquery> ) 2) root.companydef: SEQUENTIAL SCAN DYNAMIC HASH JOIN Dynamic Hash Filters: root.invention.company_id = root.companydef.id 3) root.campaigndef: INDEX PATH (1) Index Keys: id (Key-Only) DYNAMIC HASH JOIN Dynamic Hash Filters: root.invention.campaign_id = root.campaigndef.id 4) root.inv_flags: INDEX PATH Filters: (root.inv_flags.dateassigned <= TODAY AND root.inv_flags.flag_id = 1 ) (1) Index Keys: inv_id Lower Index Filter: root.inv_flags.inv_id = root.invention.id NESTED LOOP JOIN 5) root.user: INDEX PATH (1) Index Keys: id (Key-Only) Lower Index Filter: root.user.id = root.inv_flags.con_id NESTED LOOP JOIN 6) root.user: INDEX PATH (1) Index Keys: id (Key-Only) Lower Index Filter: root.user.id = root.invention.user_id NESTED LOOP JOIN Subquery: --------- Estimated Cost: 11905 Estimated # of Rows Returned: 39711 1) root.inv_contracts: INDEX PATH Filters: root.inv_contracts.contract_id != 1 (1) Index Keys: inv_id (desc) con_id (desc) contract_id (Key-Only) Subquery: --------- Estimated Cost: 13762 Estimated # of Rows Returned: 46308 1) root.x6: INDEX PATH (1) Index Keys: milestone_id Lower Index Filter: root.x6.milestone_id = 1

↪ replying to Anthony Presley

Art S. Kagel — Wed, 28 Jul 2004 16:32:14 -0400 — source: Usenet: comp.databases.informix

On Wed, 28 Jul 2004 12:38:53 -0400, Anthony Presley wrote: The SET EXPLAIN output indicates that leadsheet is a VIEW. Provide the view definition and that will help greatly. One thing I can think of from the top is to try the query using the underlying tables instead of the views as the view may be forced to use indexes that are preventing dassigned (or its underlying column <dateassigned?> from being looked up in some index if one exists. Views are not good for performance. If you must encapsulate the results that the view gives you try a stored procedure. Art S. Kagel > Hi all, > > I'm back at it -- and still haven't found a GREAT reference on tuning > queries. IE, where / how do you put your Indexes? > > I've used Art's utilities (and Obnoxio's) to some great extent -- but so far > as I have seen, they don't do a wonderful job [any?] of telling me "You need > an index HERE". > > To that end, I've been using p6Spy, which works like a charm. I know I need > to tune THIS query -- but am not 100% sure where to start. Can someone > please provide some pointers? > > The SQExplain is: > > QUERY: > ------ > select unique (dassigned) as dassigned FROM > leadsheet where dassigned <= TODAY AND > inv_id NOT IN (Select inv_id FROM > inv_contracts WHERE contract_id != 1) > ORDER BY dassigned DESC> > Estimated Cost: 92071 > Estimated # of Rows Returned: 1 > Temporary Files Required For: Order By > > 1) root.invention: SEQUENTIAL SCAN > > Filters: (root.invention.id != ALL <subquery> AND > root.invention.id != ALL <subquery> ) > > 2) root.companydef: SEQUENTIAL SCAN > > > DYNAMIC HASH JOIN > Dynamic Hash Filters: root.invention.company_id = > root.companydef.id > > 3) root.campaigndef: INDEX PATH > > (1) Index Keys: id (Key-Only) > > > DYNAMIC HASH JOIN > Dynamic Hash Filters: root.invention.campaign_id = > root.campaigndef.id > > 4) root.inv_flags: INDEX PATH > > Filters: (root.inv_flags.dateassigned <= TODAY AND > root.inv_flags.flag_id = 1 ) > > (1) Index Keys: inv_id > Lower Index Filter: root.inv_flags.inv_id = root.invention.id > NESTED LOOP JOIN > > 5) root.user: INDEX PATH > > (1) Index Keys: id (Key-Only) > Lower Index Filter: root.user.id = root.inv_flags.con_id > NESTED LOOP JOIN > > 6) root.user: INDEX PATH > > (1) Index Keys: id (Key-Only) > Lower Index Filter: root.user.id = root.invention.user_id > NESTED LOOP JOIN > > Subquery: > --------- > Estimated Cost: 11905 > Estimated # of Rows Returned: 39711 > > 1) root.inv_contracts: INDEX PATH > > Filters: root.inv_contracts.contract_id != 1 > > (1) Index Keys: inv_id (desc) con_id (desc) contract_id > (Key-Only) > > > Subquery: > --------- > Estimated Cost: 13762 > Estimated # of Rows Returned: 46308 > > 1) root.x6: INDEX PATH > > (1) Index Keys: milestone_id > Lower Index Filter: root.x6.milestone_id = 1

↪ replying to Anthony Presley

David Snyder — Wed, 28 Jul 2004 18:16:11 -0400 — source: Usenet: comp.databases.informix

Here's an excellent presentation on SQL Performance Tuning... http://www.iiug.org/waiug/present/Forum2004/sessions/Walker_SQL_Performance.ppt DAS -- David Snyder @ Snide Computer Services - Folcroft, PA Email: dave@snide.com Web: http://www.snide.com Anthony Presley wrote: > Hi all, > > I'm back at it -- and still haven't found a GREAT reference on tuning > queries. IE, where / how do you put your Indexes? > > I've used Art's utilities (and Obnoxio's) to some great extent -- but > so far as I have seen, they don't do a wonderful job [any?] of telling > me "You need an index HERE". > > To that end, I've been using p6Spy, which works like a charm. I know > I need to tune THIS query -- but am not 100% sure where to start. Can > someone please provide some pointers? > > The SQExplain is: > > QUERY: > ------ > select unique (dassigned) as dassigned FROM > leadsheet where dassigned <= TODAY AND > inv_id NOT IN (Select inv_id FROM > inv_contracts WHERE contract_id != 1) > ORDER BY dassigned DESC> > Estimated Cost: 92071 > Estimated # of Rows Returned: 1 > Temporary Files Required For: Order By > > 1) root.invention: SEQUENTIAL SCAN > > Filters: (root.invention.id != ALL <subquery> AND > root.invention.id != ALL <subquery> ) > > 2) root.companydef: SEQUENTIAL SCAN > > > DYNAMIC HASH JOIN > Dynamic Hash Filters: root.invention.company_id = > root.companydef.id > > 3) root.campaigndef: INDEX PATH > > (1) Index Keys: id (Key-Only) > > > DYNAMIC HASH JOIN > Dynamic Hash Filters: root.invention.campaign_id = > root.campaigndef.id > > 4) root.inv_flags: INDEX PATH > > Filters: (root.inv_flags.dateassigned <= TODAY AND > root.inv_flags.flag_id = 1 ) > > (1) Index Keys: inv_id > Lower Index Filter: root.inv_flags.inv_id = root.invention.id > NESTED LOOP JOIN > > 5) root.user: INDEX PATH > > (1) Index Keys: id (Key-Only) > Lower Index Filter: root.user.id = root.inv_flags.con_id > NESTED LOOP JOIN > > 6) root.user: INDEX PATH > > (1) Index Keys: id (Key-Only) > Lower Index Filter: root.user.id = root.invention.user_id > NESTED LOOP JOIN > > Subquery: > --------- > Estimated Cost: 11905 > Estimated # of Rows Returned: 39711 > > 1) root.inv_contracts: INDEX PATH > > Filters: root.inv_contracts.contract_id != 1 > > (1) Index Keys: inv_id (desc) con_id (desc) contract_id > (Key-Only) > > > Subquery: > --------- > Estimated Cost: 13762 > Estimated # of Rows Returned: 46308 > > 1) root.x6: INDEX PATH > > (1) Index Keys: milestone_id > Lower Index Filter: root.x6.milestone_id = 1

↪ replying to Anthony Presley

Anthony Presley — 28 Jul 2004 15:57:42 -0700 — source: Usenet: comp.databases.informix

The SQL for the view is: create view leadsheet (lastname,firstname, company,dassigned,invname, source,con_lastname,con_firstname, connum,inv_id,user_id,invnum,title)as select x0.lastname ,x0.firstname ,x1.abbreviation , x3.dateassigned ,x2.name ,x4.source ,x5.lastname , x5.firstname ,x5.unumber ,x2.id ,x0.id ,x2.inv_number , x0.title from "root".user x0 ,"root".companydef x1 ,"root".invention x2 , "root".inv_flags x3 ,"root".campaigndef x4 ,"root".user x5 where (((((((x1.id = x2.company_id ) AND (x0.id = x2.user_id ) ) AND (x2.id = x3.inv_id ) ) AND (x3.flag_id = 1 ) ) AND (x4.id = x2.campaign_id ) ) AND (x5.id = x3.con_id ) ) AND (x2.id != ALL (select x6.inv_id from "root".inv_milestones x6 where (x6.milestone_id = 1 ) ) ) )

↪ replying to Anthony Presley

Art S. Kagel — Thu, 29 Jul 2004 09:53:04 -0400 — source: Usenet: comp.databases.informix

On Wed, 28 Jul 2004 18:57:42 -0400, Anthony Presley wrote: > The SQL for the view is: > > create view leadsheet > (lastname,firstname, > company,dassigned,invname, > source,con_lastname,con_firstname, > connum,inv_id,user_id,invnum,title)> as > select x0.lastname ,x0.firstname ,x1.abbreviation , > x3.dateassigned ,x2.name ,x4.source ,x5.lastname , x5.firstname > ,x5.unumber ,x2.id ,x0.id ,x2.inv_number , x0.title > from "root".user x0 ,"root".companydef x1 ,"root".invention x2 , > "root".inv_flags x3 ,"root".campaigndef x4 ,"root".user x5 > where > (((((((x1.id = x2.company_id ) AND (x0.id = x2.user_id ) ) AND > (x2.id = x3.inv_id ) ) AND (x3.flag_id = 1 ) ) AND (x4.id = > x2.campaign_id ) ) AND (x5.id = x3.con_id ) ) AND (x2.id != ALL (select > x6.inv_id from "root".inv_milestones x6 where (x6.milestone_id = 1 ) > ) > ) ) OK, the SET EXPLAIN output shows a sequential scan on the invention table due to the subquery filter: x2.id != ALL ( select x6.inv_id from "root".inv_milestones x6 where (x6.milestone_id = 1 ) If you change this to a join, ie add inv_milestones to the outer query as an ANSI style OUTER join and make the conditions: x6.milestone_id = 1 AND x2.id = x6.inv_id in the ON clause and the following filter: x6.inv_id IS NULL in the WHERE clause, then the engine will be able to use indexes on x2.id and x6.inv_id. So: SELECT x0.lastname ,x0.firstname ,x1.abbreviation , x3.dateassigned ,x2.name ,x4.source ,x5.lastname , x5.firstname ,x5.unumber ,x2.id ,x0.id ,x2.inv_number , x0.title FROM "root".user x0 INNER JOIN "root".companydef x1 INNER JOIN "root".invention x2 ON x1.id = x2.company_id AND x0.id = x2.user_id INNER JOIN "root".inv_flags x3 ON x2.id = x3.inv_id AND x3.flag_id = 1 INNER JOIN "root".campaigndef x4 ON x4.id = x2.campaign_id INNER JOIN "root".user x5 ON x5.id = x3.con_id LEFT OUTER JOIN "root".inv_milestones ON x6.milestone_id = 1 AND x2.id = x6.inv_id WHERE x6.inv_id IS NULL Art S. Kagel

Related threads

Different instances with markedly different times