SQL Tuning Assistance Needed

I have a query that is running approx. 6 minutes that I need to run in under one minute. I would like to see if one of you tuning gurus could give me a hand. IDX11.50.FC5 AIX 5.3 query = select max(a.audit_dt) from trailer_audit a, trailer t where a.trailer_prefix = t.trailer_prefix and a.trailer_number = t.trailer_number and a.audit_code = 'TRSTATCHG' group by a.trailer_number; tables = trailer 16,000 rows relevant indexes - multi column index on trailer_prefix and trailer_number trailer_audit 24,000,000 rows relevant indexes - multi column index on trailer_prefix, trailer_number, & audit_code I will post tjhe sqexplain below however I am taking a seq scan path on the big table and joining on the smaller table.This "may" be as easy as turning those 2 around but i am getting nowhere fast. TIA, Dan sqexplain output QUERY: (OPTIMIZATION TIMESTAMP: 06-24-2011 11:31:10) ------ select max(a.audit_dt) from trailer_audit a, trailer t where a.trailer_prefix = t.trailer_prefix and a.trailer_number = t.trailer_number and a.audit_code = 'TRSTATCHG' group by a.trailer_number Estimated Cost: 45844532 Estimated # of Rows Returned: 52 Temporary Files Required For: Group By 1) informix.a: SEQUENTIAL SCAN Filters: informix.a.audit_code = 'TRSTATCHG' 2) informix.t: INDEX PATH (1) Index Name: informix. 228_1212 Index Keys: trailer_prefix trailer_number (Key-Only) DYNAMIC HASH JOIN Dynamic Hash Filters: (informix.a.trailer_number = informix.t.trailer_number AND informix.a.trailer_prefix = informix.t.trailer_prefix ) Query statistics: ----------------- Table map : ---------------------------- Internal name Table name ---------------------------- t1 a t2 t type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t1 573774 23197300 576281 00:03.55 1383360 type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t2 15980 15980 15980 00:00.09 565 type rows_prod est_rows rows_bld rows_prb novrflo time est_cost ------------------------------------------------------------------------------ hjoin 573774 15938630 15980 573774 0 00:05.52 45844532 type rows_prod est_rows rows_cons time ------------------------------------------------- group 0 52 573774 00:00.00

does a.audit_code have an index o it? j. On Jun 24, 2011, at 12:44 PM, DAN MUELLER wrote: > I have a query that is running approx. 6 minutes that I need to run in = under=20 > one minute. I would like to see if one of you tuning gurus could give = me a=20 > hand.=20 >=20 > IDX11.50.FC5=20 > AIX 5.3=20 >=20 > query =3D select max(a.audit_dt)=20 >=20 > from trailer_audit a, trailer t=20 >=20 > where a.trailer_prefix =3D t.trailer_prefix=20 >=20 > and a.trailer_number =3D t.trailer_number=20 >=20 > and a.audit_code =3D 'TRSTATCHG'=20 >=20 > group by a.trailer_number;=20 >=20 > tables =3D trailer 16,000 rows=20 >=20 > relevant indexes - multi column index on trailer_prefix and = trailer_number=20 >=20 > trailer_audit 24,000,000 rows=20 >=20 > relevant indexes - multi column index on trailer_prefix, = trailer_number, &=20 > audit_code=20 >=20 > I will post tjhe sqexplain below however I am taking a seq scan path = on the=20 > big table and joining on the smaller table.This "may" be as easy as = turning=20 > those 2 around but i am getting nowhere fast.=20 >=20 > TIA,=20 > Dan=20 >=20 > sqexplain output=20 >=20 > QUERY: (OPTIMIZATION TIMESTAMP: 06-24-2011 11:31:10)=20 > ------=20 > select max(a.audit_dt)=20 > from=20 >=20 > trailer_audit a, trailer t=20 > where=20 >=20 > a.trailer_prefix =3D t.trailer_prefix=20 > and a.trailer_number =3D t.trailer_number=20 > and a.audit_code =3D 'TRSTATCHG'=20 > group by a.trailer_number=20 >=20 > Estimated Cost: 45844532=20 > Estimated # of Rows Returned: 52=20 > Temporary Files Required For: Group By=20 >=20 > 1) informix.a: SEQUENTIAL SCAN=20 >=20 > Filters: informix.a.audit_code =3D 'TRSTATCHG'=20 >=20 > 2) informix.t: INDEX PATH=20 >=20 > (1) Index Name: informix. 228_1212=20 >=20 > Index Keys: trailer_prefix trailer_number (Key-Only)=20 >=20 > DYNAMIC HASH JOIN=20 >=20 > Dynamic Hash Filters: (informix.a.trailer_number =3D = informix.t.trailer_number=20 > AND informix.a.trailer_prefix =3D informix.t.trailer_prefix )=20 >=20 > Query statistics:=20 > -----------------=20 >=20 > Table map :=20 > ----------------------------=20 > Internal name Table name=20 > ----------------------------=20 > t1 a=20 > t2 t=20 >=20 > type table rows_prod est_rows rows_scan time est_cost=20 > -------------------------------------------------------------------=20 > scan t1 573774 23197300 576281 00:03.55 1383360=20 >=20 > type table rows_prod est_rows rows_scan time est_cost=20 > -------------------------------------------------------------------=20 > scan t2 15980 15980 15980 00:00.09 565=20 >=20 > type rows_prod est_rows rows_bld rows_prb novrflo time est_cost=20 > = --------------------------------------------------------------------------= ----=20 > hjoin 573774 15938630 15980 573774 0 00:05.52 45844532=20 >=20 > type rows_prod est_rows rows_cons time=20 > -------------------------------------------------=20 > group 0 52 573774 00:00.00=20 >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20= >=20

short answer - yes long answer - it is a multi column index containing prefix, number and code in that order.

The DB can't use it unless it is the first column in the index. j. On Jun 24, 2011, at 1:02 PM, DAN MUELLER wrote: > short answer - yes=20 > long answer - it is a multi column index containing prefix, number and = code in=20 > that order.=20 >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20= >=20

I guess I am confused then. The join columns are indexed on both tables with the trailer_audit also containing the audit_code column. Since we can only use one index, what good would it do me to have a separate index on the audit_code column? Incidently, the audit code I am looking for is on at least 95% of the rows in the table.

Create a new index over the table 'trailer_audit' with only the field 'audit_code' to avoid the sequential scan. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of DAN MUELLER Sent: viernes, 24 de junio de 2011 11:45 a.m. To: ids@iiug.org Subject: SQL Tuning Assistance Needed [24128] I have a query that is running approx. 6 minutes that I need to run in under one minute. I would like to see if one of you tuning gurus could give me a hand. IDX11.50.FC5 AIX 5.3 query = select max(a.audit_dt) from trailer_audit a, trailer t where a.trailer_prefix = t.trailer_prefix and a.trailer_number = t.trailer_number and a.audit_code = 'TRSTATCHG' group by a.trailer_number; tables = trailer 16,000 rows relevant indexes - multi column index on trailer_prefix and trailer_number trailer_audit 24,000,000 rows relevant indexes - multi column index on trailer_prefix, trailer_number, & audit_code I will post tjhe sqexplain below however I am taking a seq scan path on the big table and joining on the smaller table.This "may" be as easy as turning those 2 around but i am getting nowhere fast. TIA, Dan sqexplain output QUERY: (OPTIMIZATION TIMESTAMP: 06-24-2011 11:31:10) ------ select max(a.audit_dt) from trailer_audit a, trailer t where a.trailer_prefix = t.trailer_prefix and a.trailer_number = t.trailer_number and a.audit_code = 'TRSTATCHG' group by a.trailer_number Estimated Cost: 45844532 Estimated # of Rows Returned: 52 Temporary Files Required For: Group By 1) informix.a: SEQUENTIAL SCAN Filters: informix.a.audit_code = 'TRSTATCHG' 2) informix.t: INDEX PATH (1) Index Name: informix. 228_1212 Index Keys: trailer_prefix trailer_number (Key-Only) DYNAMIC HASH JOIN Dynamic Hash Filters: (informix.a.trailer_number = informix.t.trailer_number AND informix.a.trailer_prefix = informix.t.trailer_prefix ) Query statistics: ----------------- Table map : ---------------------------- Internal name Table name ---------------------------- t1 a t2 t type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t1 573774 23197300 576281 00:03.55 1383360 type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t2 15980 15980 15980 00:00.09 565 type rows_prod est_rows rows_bld rows_prb novrflo time est_cost ------------------------------------------------------------------------------ hjoin 573774 15938630 15980 573774 0 00:05.52 45844532 type rows_prod est_rows rows_cons time ------------------------------------------------- group 0 52 573774 00:00.00 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Out of the 24 million rows, only 573,774 met the filter criteria. = That's still substantial and not a great index, but might reduce your = reads for that table by 4x to 6x ish. That might cut 2 min off the = query. The composite index isn't really working. The second table is getting = all 16K rows read and then probing with it. What bothers me is that it is reading he larger of the two tables first = - when it should read the "smaller" And the join time is not that good, it's adding 2 minutes to the query - = yet it says no overflow. Things to check. Ensure statistics are recent on both tables. Add an index to the audit_code Add a filter on the trailer table (can be a dummy) just to hint the = optimizer into reading it first. What is PDQPRIORITY set to - or is that an option. How much DS_Memory do you have j. On Jun 24, 2011, at 1:27 PM, DAN MUELLER wrote: > I guess I am confused then. The join columns are indexed on both = tables with=20 > the trailer_audit also containing the audit_code column. Since we can = only use=20 > one index, what good would it do me to have a separate index on the = audit_code=20 > column? Incidently, the audit code I am looking for is on at least 95% = of the=20 > rows in the table.=20 >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20= >=20

Yes, the join column is indexed on both...but that doesn't make the optimizer decide to join the big table first. You want to filter 24 million rows to as few as possible as fast as possible. You state you have a composite of the 3 join columns on the smaller table, but you don't put what I call "terminal filters" on the smaller table. In my experience, the optimizer isn't going to be inclined to back join to the bigger table in this scenario. As Jack points out, 500k-ish rows match your audit-code. That's a significantly small enough chunk of 24 million that if you had started with an audit_code index on trailer_audit, your query would be much faster (probably 2 times at least)...since the optimizer would see that index and choose it as a very fast way to reduce records in that table. Not to mention you very well might have PDQ with a lot of processors making the engine believe a SEQ will go faster with multiple read threads. The composite on the trailer table is useless in this join unless you can get the optimizer to choose to read from trailer_audit first.

Update statistics? Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Jun 24, 2011 at 12:44 PM, DAN MUELLER <dan.mueller@trnswrks.com>wrote: > I have a query that is running approx. 6 minutes that I need to run in > under > one minute. I would like to see if one of you tuning gurus could give me a > hand. > > IDX11.50.FC5 > AIX 5.3 > > query = select max(a.audit_dt) > > from trailer_audit a, trailer t > > where a.trailer_prefix = t.trailer_prefix > > and a.trailer_number = t.trailer_number > > and a.audit_code = 'TRSTATCHG' > > group by a.trailer_number; > > tables = trailer 16,000 rows > > relevant indexes - multi column index on trailer_prefix and trailer_number > > trailer_audit 24,000,000 rows > > relevant indexes - multi column index on trailer_prefix, trailer_number, & > audit_code > > I will post tjhe sqexplain below however I am taking a seq scan path on the > big table and joining on the smaller table.This "may" be as easy as turning > those 2 around but i am getting nowhere fast. > > TIA, > Dan > > sqexplain output > > QUERY: (OPTIMIZATION TIMESTAMP: 06-24-2011 11:31:10) > ------ > select max(a.audit_dt) > from > > trailer_audit a, trailer t > where > > a.trailer_prefix = t.trailer_prefix > and a.trailer_number = t.trailer_number > and a.audit_code = 'TRSTATCHG' > group by a.trailer_number > > Estimated Cost: 45844532 > Estimated # of Rows Returned: 52 > Temporary Files Required For: Group By > > 1) informix.a: SEQUENTIAL SCAN > > Filters: informix.a.audit_code = 'TRSTATCHG' > > 2) informix.t: INDEX PATH > > (1) Index Name: informix. 228_1212 > > Index Keys: trailer_prefix trailer_number (Key-Only) > > DYNAMIC HASH JOIN > > Dynamic Hash Filters: (informix.a.trailer_number = > informix.t.trailer_number > AND informix.a.trailer_prefix = informix.t.trailer_prefix ) > > Query statistics: > ----------------- > > Table map : > ---------------------------- > Internal name Table name > ---------------------------- > t1 a > t2 t > > type table rows_prod est_rows rows_scan time est_cost > ------------------------------------------------------------------- > scan t1 573774 23197300 576281 00:03.55 1383360 > > type table rows_prod est_rows rows_scan time est_cost > ------------------------------------------------------------------- > scan t2 15980 15980 15980 00:00.09 565 > > type rows_prod est_rows rows_bld rows_prb novrflo time est_cost > > ------------------------------------------------------------------------------ > hjoin 573774 15938630 15980 573774 0 00:05.52 45844532 > > type rows_prod est_rows rows_cons time > ------------------------------------------------- > group 0 52 573774 00:00.00 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --bcaec547c99516897604a67a26e6

WHAT? No. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Jun 24, 2011 at 1:54 PM, Flores, Julio Gerardo < j-gerardo.flores.olvera@hp.com> wrote: > Create a new index over the table 'trailer_audit' with only the field > 'audit_code' to avoid the sequential scan. > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of DAN > MUELLER > Sent: viernes, 24 de junio de 2011 11:45 a.m. > To: ids@iiug.org > Subject: SQL Tuning Assistance Needed [24128] > > I have a query that is running approx. 6 minutes that I need to run in > under > one minute. I would like to see if one of you tuning gurus could give me a > hand. > > IDX11.50.FC5 > AIX 5.3 > > query = select max(a.audit_dt) > > from trailer_audit a, trailer t > > where a.trailer_prefix = t.trailer_prefix > > and a.trailer_number = t.trailer_number > > and a.audit_code = 'TRSTATCHG' > > group by a.trailer_number; > > tables = trailer 16,000 rows > > relevant indexes - multi column index on trailer_prefix and trailer_number > > trailer_audit 24,000,000 rows > > relevant indexes - multi column index on trailer_prefix, trailer_number, & > audit_code > > I will post tjhe sqexplain below however I am taking a seq scan path on the > big table and joining on the smaller table.This "may" be as easy as turning > those 2 around but i am getting nowhere fast. > > TIA, > Dan > > sqexplain output > > QUERY: (OPTIMIZATION TIMESTAMP: 06-24-2011 11:31:10) > ------ > select max(a.audit_dt) > from > > trailer_audit a, trailer t > where > > a.trailer_prefix = t.trailer_prefix > and a.trailer_number = t.trailer_number > and a.audit_code = 'TRSTATCHG' > group by a.trailer_number > > Estimated Cost: 45844532 > Estimated # of Rows Returned: 52 > Temporary Files Required For: Group By > > 1) informix.a: SEQUENTIAL SCAN > > Filters: informix.a.audit_code = 'TRSTATCHG' > > 2) informix.t: INDEX PATH > > (1) Index Name: informix. 228_1212 > > Index Keys: trailer_prefix trailer_number (Key-Only) > > DYNAMIC HASH JOIN > > Dynamic Hash Filters: (informix.a.trailer_number = > informix.t.trailer_number > AND informix.a.trailer_prefix = informix.t.trailer_prefix ) > > Query statistics: > ----------------- > > Table map : > ---------------------------- > Internal name Table name > ---------------------------- > t1 a > t2 t > > type table rows_prod est_rows rows_scan time est_cost > ------------------------------------------------------------------- > scan t1 573774 23197300 576281 00:03.55 1383360 > > type table rows_prod est_rows rows_scan time est_cost > ------------------------------------------------------------------- > scan t2 15980 15980 15980 00:00.09 565 > > type rows_prod est_rows rows_bld rows_prb novrflo time est_cost > > ------------------------------------------------------------------------------ > hjoin 573774 15938630 15980 573774 0 00:05.52 45844532 > > type rows_prod est_rows rows_cons time > ------------------------------------------------- > group 0 52 573774 00:00.00 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --bcaec54866789ca65904a67a2ed5

I will tell you that a new performance improvements in 11.70.xC3 is the ability to push down aggregates (specifically count/max/min). When doing sequential scans this pushdown allows the aggregate to be processed inline with the scan. This has a very drastic improvement on the performance of the query. What is DS_NONPDQ_QUERY_MEM set to, you are building a large hash table (16K of rows) an if this overflows it will slow you down. John F. Miller III STSM, Embedability Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 06/24/2011 09:44:55 AM: > From: > > "DAN MUELLER" <dan.mueller@trnswrks.com> > > To: > > ids@iiug.org > > Date: > > 06/24/2011 09:52 AM > > Subject: > > SQL Tuning Assistance Needed [24128] > > Sent by: > > ids-bounces@iiug.org > > I have a query that is running approx. 6 minutes that I need to run in under > one minute. I would like to see if one of you tuning gurus could give me a > hand. > > IDX11.50.FC5 > AIX 5.3 > > query = select max(a.audit_dt) > > from trailer_audit a, trailer t > > where a.trailer_prefix = t.trailer_prefix > > and a.trailer_number = t.trailer_number > > and a.audit_code = 'TRSTATCHG' > > group by a.trailer_number; > > tables = trailer 16,000 rows > > relevant indexes - multi column index on trailer_prefix and trailer_number > > trailer_audit 24,000,000 rows > > relevant indexes - multi column index on trailer_prefix, trailer_number, & > audit_code > > I will post tjhe sqexplain below however I am taking a seq scan path on the > big table and joining on the smaller table.This "may" be as easy as turning > those 2 around but i am getting nowhere fast. > > TIA, > Dan > > sqexplain output > > QUERY: (OPTIMIZATION TIMESTAMP: 06-24-2011 11:31:10) > ------ > select max(a.audit_dt) > from > > trailer_audit a, trailer t > where > > a.trailer_prefix = t.trailer_prefix > and a.trailer_number = t.trailer_number > and a.audit_code = 'TRSTATCHG' > group by a.trailer_number > > Estimated Cost: 45844532 > Estimated # of Rows Returned: 52 > Temporary Files Required For: Group By > > 1) informix.a: SEQUENTIAL SCAN > > Filters: informix.a.audit_code = 'TRSTATCHG' > > 2) informix.t: INDEX PATH > > (1) Index Name: informix. 228_1212 > > Index Keys: trailer_prefix trailer_number (Key-Only) > > DYNAMIC HASH JOIN > > Dynamic Hash Filters: (informix.a.trailer_number = informix.t.trailer_number > AND informix.a.trailer_prefix = informix.t.trailer_prefix ) > > Query statistics: > ----------------- > > Table map : > ---------------------------- > Internal name Table name > ---------------------------- > t1 a > t2 t > > type table rows_prod est_rows rows_scan time est_cost > ------------------------------------------------------------------- > scan t1 573774 23197300 576281 00:03.55 1383360 > > type table rows_prod est_rows rows_scan time est_cost > ------------------------------------------------------------------- > scan t2 15980 15980 15980 00:00.09 565 > > type rows_prod est_rows rows_bld rows_prb novrflo time est_cost > ------------------------------------------------------------------------------ > hjoin 573774 15938630 15980 573774 0 00:05.52 45844532 > > type rows_prod est_rows rows_cons time > ------------------------------------------------- > group 0 52 573774 00:00.00 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

John, When will 11.7.FC3 be GAed Bruce Simms Data Base Services TALX, Provider of Equifax Workforce Solutions 2330 Ball Drive St. Louis, MO 63146 Phone (314) 214-7703 FAX (314) 983-3238 bsimms@talx.com -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of John Miller iii Sent: Friday, June 24, 2011 4:18 PM To: ids@iiug.org Subject: Re: SQL Tuning Assistance Needed [24140] I will tell you that a new performance improvements in 11.70.xC3 is the ability to push down aggregates (specifically count/max/min). When doing sequential scans this pushdown allows the aggregate to be processed inline with the scan. This has a very drastic improvement on the performance of the query. What is DS_NONPDQ_QUERY_MEM set to, you are building a large hash table (16K of rows) an if this overflows it will slow you down. John F. Miller III STSM, Embedability Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 06/24/2011 09:44:55 AM: > From: > > "DAN MUELLER" <dan.mueller@trnswrks.com> > > To: > > ids@iiug.org > > Date: > > 06/24/2011 09:52 AM > > Subject: > > SQL Tuning Assistance Needed [24128] > > Sent by: > > ids-bounces@iiug.org > > I have a query that is running approx. 6 minutes that I need to run in under > one minute. I would like to see if one of you tuning gurus could give me a > hand. > > IDX11.50.FC5 > AIX 5.3 > > query = select max(a.audit_dt) > > from trailer_audit a, trailer t > > where a.trailer_prefix = t.trailer_prefix > > and a.trailer_number = t.trailer_number > > and a.audit_code = 'TRSTATCHG' > > group by a.trailer_number; > > tables = trailer 16,000 rows > > relevant indexes - multi column index on trailer_prefix and trailer_number > > trailer_audit 24,000,000 rows > > relevant indexes - multi column index on trailer_prefix, trailer_number, & > audit_code > > I will post tjhe sqexplain below however I am taking a seq scan path on the > big table and joining on the smaller table.This "may" be as easy as turning > those 2 around but i am getting nowhere fast. > > TIA, > Dan > > sqexplain output > > QUERY: (OPTIMIZATION TIMESTAMP: 06-24-2011 11:31:10) > ------ > select max(a.audit_dt) > from > > trailer_audit a, trailer t > where > > a.trailer_prefix = t.trailer_prefix > and a.trailer_number = t.trailer_number > and a.audit_code = 'TRSTATCHG' > group by a.trailer_number > > Estimated Cost: 45844532 > Estimated # of Rows Returned: 52 > Temporary Files Required For: Group By > > 1) informix.a: SEQUENTIAL SCAN > > Filters: informix.a.audit_code = 'TRSTATCHG' > > 2) informix.t: INDEX PATH > > (1) Index Name: informix. 228_1212 > > Index Keys: trailer_prefix trailer_number (Key-Only) > > DYNAMIC HASH JOIN > > Dynamic Hash Filters: (informix.a.trailer_number = informix.t.trailer_number > AND informix.a.trailer_prefix = informix.t.trailer_prefix ) > > Query statistics: > ----------------- > > Table map : > ---------------------------- > Internal name Table name > ---------------------------- > t1 a > t2 t > > type table rows_prod est_rows rows_scan time est_cost > ------------------------------------------------------------------- > scan t1 573774 23197300 576281 00:03.55 1383360 > > type table rows_prod est_rows rows_scan time est_cost > ------------------------------------------------------------------- > scan t2 15980 15980 15980 00:00.09 565 > > type rows_prod est_rows rows_bld rows_prb novrflo time est_cost > ------------------------------------------------------------------------------ > hjoin 573774 15938630 15980 573774 0 00:05.52 45844532 > > type rows_prod est_rows rows_cons time > ------------------------------------------------- > group 0 52 573774 00:00.00 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

In about 2 weeks. John F. Miller III STSM, Embedability Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 06/24/2011 02:23:43 PM: > From: > > "Bruce Simms" <Bruce.Simms@talx.com> > > To: > > ids@iiug.org > > Date: > > 06/24/2011 02:24 PM > > Subject: > > RE: SQL Tuning Assistance Needed [24141] > > Sent by: > > ids-bounces@iiug.org > > John, > > When will 11.7.FC3 be GAed > > Bruce Simms > Data Base Services > TALX, Provider of Equifax Workforce Solutions > 2330 Ball Drive > St. Louis, MO 63146 > > Phone (314) 214-7703 > FAX (314) 983-3238 > bsimms@talx.com > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of John > Miller iii > Sent: Friday, June 24, 2011 4:18 PM > To: ids@iiug.org > Subject: Re: SQL Tuning Assistance Needed [24140] > > I will tell you that a new performance improvements in 11.70.xC3 is > the ability to push down aggregates (specifically count/max/min). > When doing sequential scans this pushdown allows the aggregate > to be processed inline with the scan. This has a very drastic > improvement on the performance of the query. > > What is DS_NONPDQ_QUERY_MEM set to, you are building > > a large hash table (16K of rows) an if this overflows > > it will slow you down. > > John F. Miller III > STSM, Embedability Architect > miller3@us.ibm.com > 503-578-5645 > IBM Informix Dynamic Server (IDS) > > ids-bounces@iiug.org wrote on 06/24/2011 09:44:55 AM: > > > From: > > > > "DAN MUELLER" <dan.mueller@trnswrks.com> > > > > To: > > > > ids@iiug.org > > > > Date: > > > > 06/24/2011 09:52 AM > > > > Subject: > > > > SQL Tuning Assistance Needed [24128] > > > > Sent by: > > > > ids-bounces@iiug.org > > > > I have a query that is running approx. 6 minutes that I need to run in > under > > one minute. I would like to see if one of you tuning gurus could give me > a > > hand. > > > > IDX11.50.FC5 > > AIX 5.3 > > > > query = select max(a.audit_dt) > > > > from trailer_audit a, trailer t > > > > where a.trailer_prefix = t.trailer_prefix > > > > and a.trailer_number = t.trailer_number > > > > and a.audit_code = 'TRSTATCHG' > > > > group by a.trailer_number; > > > > tables = trailer 16,000 rows > > > > relevant indexes - multi column index on trailer_prefix and > trailer_number > > > > trailer_audit 24,000,000 rows > > > > relevant indexes - multi column index on trailer_prefix, trailer_number, > & > > audit_code > > > > I will post tjhe sqexplain below however I am taking a seq scan path on > the > > big table and joining on the smaller table.This "may" be as easy as > turning > > those 2 around but i am getting nowhere fast. > > > > TIA, > > Dan > > > > sqexplain output > > > > QUERY: (OPTIMIZATION TIMESTAMP: 06-24-2011 11:31:10) > > ------ > > select max(a.audit_dt) > > from > > > > trailer_audit a, trailer t > > where > > > > a.trailer_prefix = t.trailer_prefix > > and a.trailer_number = t.trailer_number > > and a.audit_code = 'TRSTATCHG' > > group by a.trailer_number > > > > Estimated Cost: 45844532 > > Estimated # of Rows Returned: 52 > > Temporary Files Required For: Group By > > > > 1) informix.a: SEQUENTIAL SCAN > > > > Filters: informix.a.audit_code = 'TRSTATCHG' > > > > 2) informix.t: INDEX PATH > > > > (1) Index Name: informix. 228_1212 > > > > Index Keys: trailer_prefix trailer_number (Key-Only) > > > > DYNAMIC HASH JOIN > > > > Dynamic Hash Filters: (informix.a.trailer_number = > informix.t.trailer_number > > AND informix.a.trailer_prefix = informix.t.trailer_prefix ) > > > > Query statistics: > > ----------------- > > > > Table map : > > ---------------------------- > > Internal name Table name > > ---------------------------- > > t1 a > > t2 t > > > > type table rows_prod est_rows rows_scan time est_cost > > ------------------------------------------------------------------- > > scan t1 573774 23197300 576281 00:03.55 1383360 > > > > type table rows_prod est_rows rows_scan time est_cost > > ------------------------------------------------------------------- > > scan t2 15980 15980 15980 00:00.09 565 > > > > type rows_prod est_rows rows_bld rows_prb novrflo time est_cost > > > ------------------------------------------------------------------------------ > > > hjoin 573774 15938630 15980 573774 0 00:05.52 45844532 > > > > type rows_prod est_rows rows_cons time > > ------------------------------------------------- > > group 0 52 573774 00:00.00 > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Try to create the index on table trailer_audit (trailer_number, trailer_prefix, audit_code, audit_dt) Gary

DAN MUELLER Wrote: -------------------------------------------------------------------------------- I have a query that is running approx. 6 minutes that I need to run in under one minute. I would like to see if one of you tuning gurus could give me a hand. IDX11.50.FC5 AIX 5.3 query = select max(a.audit_dt) from trailer_audit a, trailer t where a.trailer_prefix = t.trailer_prefix and a.trailer_number = t.trailer_number and a.audit_code = 'TRSTATCHG' group by a.trailer_number; tables = trailer 16,000 rows relevant indexes - multi column index on trailer_prefix and trailer_number trailer_audit 24,000,000 rows relevant indexes - multi column index on trailer_prefix, trailer_number, & audit_code I will post tjhe sqexplain below however I am taking a seq scan path on the big table and joining on the smaller table.This "may" be as easy as turning those 2 around but i am getting nowhere fast. TIA, Dan -------------------------------------------------------------------------------- Response: New Index on trailer_audit(audit_code, audit_dt) This depends a bit on the data relationship between the tables. Do the most recent records in trailer_audit have a corresponding record in the trailer table? If they do, you could see sub-second results with this index. If they don't, this would read through the most recent trailer_audit records until it finds a match in the trailer table. In either case, this is the best path to reading the minimum amount of those 24 million records. HTH, Dave

Bruce: I should mention I am doing a "chat with the labs" this Thursday (June 30) about the new features in 11.70.xC2 and 11.70.xC3. To register for the free talk go to the following URL. https://events.webdialogs.com/register.php?id=5963992ea1&l=en-US John F. Miller III STSM, Embedability Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) John Miller iii/Menlo Park/IBM wrote on 06/24/2011 03:49:23 PM: > [image removed] > > RE: SQL Tuning Assistance Needed [24141] > > John Miller iii > > to: > > ids > > 06/24/2011 03:49 PM > > Cc: > > ids, ids-bounces > > In about 2 weeks. > > John F. Miller III > STSM, Embedability Architect > miller3@us.ibm.com > 503-578-5645 > IBM Informix Dynamic Server (IDS) > > ids-bounces@iiug.org wrote on 06/24/2011 02:23:43 PM: > > > From: > > > > "Bruce Simms" <Bruce.Simms@talx.com> > > > > To: > > > > ids@iiug.org > > > > Date: > > > > 06/24/2011 02:24 PM > > > > Subject: > > > > RE: SQL Tuning Assistance Needed [24141] > > > > Sent by: > > > > ids-bounces@iiug.org > > > > John, > > > > When will 11.7.FC3 be GAed > > > > Bruce Simms > > Data Base Services > > TALX, Provider of Equifax Workforce Solutions > > 2330 Ball Drive > > St. Louis, MO 63146 > > > > Phone (314) 214-7703 > > FAX (314) 983-3238 > > bsimms@talx.com > > > > -----Original Message----- > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of John > > Miller iii > > Sent: Friday, June 24, 2011 4:18 PM > > To: ids@iiug.org > > Subject: Re: SQL Tuning Assistance Needed [24140] > > > > I will tell you that a new performance improvements in 11.70.xC3 is > > the ability to push down aggregates (specifically count/max/min). > > When doing sequential scans this pushdown allows the aggregate > > to be processed inline with the scan. This has a very drastic > > improvement on the performance of the query. > > > > What is DS_NONPDQ_QUERY_MEM set to, you are building > > > > a large hash table (16K of rows) an if this overflows > > > > it will slow you down. > > > > John F. Miller III > > STSM, Embedability Architect > > miller3@us.ibm.com > > 503-578-5645 > > IBM Informix Dynamic Server (IDS) > > > > ids-bounces@iiug.org wrote on 06/24/2011 09:44:55 AM: > > > > > From: > > > > > > "DAN MUELLER" <dan.mueller@trnswrks.com> > > > > > > To: > > > > > > ids@iiug.org > > > > > > Date: > > > > > > 06/24/2011 09:52 AM > > > > > > Subject: > > > > > > SQL Tuning Assistance Needed [24128] > > > > > > Sent by: > > > > > > ids-bounces@iiug.org > > > > > > I have a query that is running approx. 6 minutes that I need to run in > > under > > > one minute. I would like to see if one of you tuning gurus could give me > > a > > > hand. > > > > > > IDX11.50.FC5 > > > AIX 5.3 > > > > > > query = select max(a.audit_dt) > > > > > > from trailer_audit a, trailer t > > > > > > where a.trailer_prefix = t.trailer_prefix > > > > > > and a.trailer_number = t.trailer_number > > > > > > and a.audit_code = 'TRSTATCHG' > > > > > > group by a.trailer_number; > > > > > > tables = trailer 16,000 rows > > > > > > relevant indexes - multi column index on trailer_prefix and > > trailer_number > > > > > > trailer_audit 24,000,000 rows > > > > > > relevant indexes - multi column index on trailer_prefix, trailer_number, > > & > > > audit_code > > > > > > I will post tjhe sqexplain below however I am taking a seq scan path on > > the > > > big table and joining on the smaller table.This "may" be as easy as > > turning > > > those 2 around but i am getting nowhere fast. > > > > > > TIA, > > > Dan > > > > > > sqexplain output > > > > > > QUERY: (OPTIMIZATION TIMESTAMP: 06-24-2011 11:31:10) > > > ------ > > > select max(a.audit_dt) > > > from > > > > > > trailer_audit a, trailer t > > > where > > > > > > a.trailer_prefix = t.trailer_prefix > > > and a.trailer_number = t.trailer_number > > > and a.audit_code = 'TRSTATCHG' > > > group by a.trailer_number > > > > > > Estimated Cost: 45844532 > > > Estimated # of Rows Returned: 52 > > > Temporary Files Required For: Group By > > > > > > 1) informix.a: SEQUENTIAL SCAN > > > > > > Filters: informix.a.audit_code = 'TRSTATCHG' > > > > > > 2) informix.t: INDEX PATH > > > > > > (1) Index Name: informix. 228_1212 > > > > > > Index Keys: trailer_prefix trailer_number (Key-Only) > > > > > > DYNAMIC HASH JOIN > > > > > > Dynamic Hash Filters: (informix.a.trailer_number = > > informix.t.trailer_number > > > AND informix.a.trailer_prefix = informix.t.trailer_prefix ) > > > > > > Query statistics: > > > ----------------- > > > > > > Table map : > > > ---------------------------- > > > Internal name Table name > > > ---------------------------- > > > t1 a > > > t2 t > > > > > > type table rows_prod est_rows rows_scan time est_cost > > > ------------------------------------------------------------------- > > > scan t1 573774 23197300 576281 00:03.55 1383360 > > > > > > type table rows_prod est_rows rows_scan time est_cost > > > ------------------------------------------------------------------- > > > scan t2 15980 15980 15980 00:00.09 565 > > > > > > type rows_prod est_rows rows_bld rows_prb novrflo time est_cost > > > > > > ------------------------------------------------------------------------------ > > > > > hjoin 573774 15938630 15980 573774 0 00:05.52 45844532 > > > > > > type rows_prod est_rows rows_cons time > > > ------------------------------------------------- > > > group 0 52 573774 00:00.00 > > > > > > > > > > > > > > > > ******************************************************************************* > > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > >