poor select performance

[AC Slater]

I have 1 table (out of many) that has very poor performance when performing
a select into on. The select statement is called multiple times. We've
found each call to take almost 1 second... we were expecting less than .1
seconds.

The select stmt looks like:

select key,data into key_buff, data_buff from tablename where key > curr_key
AND key < max_key ORDER by key FETCH FIRST 1 ROW ONLY;

Any idea to troubleshoot performance ?

Thanks

[Mark A]

Re: poor select performance

"AC Slater" <notme@yahoo.co m> wrote in message
news:UKNub.8437 $nE6.2008370@ne ws4.srv.hcvlny. cv.net...[color=blue]
> I have 1 table (out of many) that has very poor performance when[/color]
performing[color=blue]
> a select into on. The select statement is called multiple times. We've
> found each call to take almost 1 second... we were expecting less than .1
> seconds.
>
> The select stmt looks like:
>
> select key,data into key_buff, data_buff from tablename where key >[/color]
curr_key[color=blue]
> AND key < max_key ORDER by key FETCH FIRST 1 ROW ONLY;
>
> Any idea to troubleshoot performance ?
>
> Thanks
>[/color]
Can you please identify the host variables vs. the DB2 columns?

[AC Slater]

Re: poor select performance


"Mark A" <ma@switchboard .net> wrote in message
news:K_Nub.157$ 5h1.21383@news. uswest.net...[color=blue]
> "AC Slater" <notme@yahoo.co m> wrote in message
> news:UKNub.8437 $nE6.2008370@ne ws4.srv.hcvlny. cv.net...[color=green]
> > I have 1 table (out of many) that has very poor performance when[/color]
> performing[color=green]
> > a select into on. The select statement is called multiple times. We've
> > found each call to take almost 1 second... we were expecting less than[/color][/color]
..1[color=blue][color=green]
> > seconds.
> >
> > The select stmt looks like:
> >
> > select key,data into key_buff, data_buff from tablename where key >[/color]
> curr_key[color=green]
> > AND key < max_key ORDER by key FETCH FIRST 1 ROW ONLY;
> >
> > Any idea to troubleshoot performance ?
> >
> > Thanks
> >[/color]
> Can you please identify the host variables vs. the DB2 columns?
>
>[/color]

key and data are the only table columns. The rest are stored procedure
params.

/Frank

[Mark A]

Re: poor select performance

> > > I have 1 table (out of many) that has very poor performance when[color=blue][color=green]
> > performing[color=darkred]
> > > a select into on. The select statement is called multiple times.[/color][/color][/color]
We've[color=blue][color=green][color=darkred]
> > > found each call to take almost 1 second... we were expecting less than[/color][/color]
> .1[color=green][color=darkred]
> > > seconds.
> > >
> > > The select stmt looks like:
> > >
> > > select key,data into key_buff, data_buff from tablename where key >[/color]
> > curr_key[color=darkred]
> > > AND key < max_key ORDER by key FETCH FIRST 1 ROW ONLY;
> > >
> > > Any idea to troubleshoot performance ?
> > >
> > > Thanks
> > >[/color]
> > Can you please identify the host variables vs. the DB2 columns?
> >
> >[/color]
>
> key and data are the only table columns. The rest are stored procedure
> params.
>
> /Frank
>[/color]
1. Is there an index on key?
2. Have you executed the runstats command on the table and indexes? You must
rebind after runstats is performed.
2. How many rows are in the answer set without the fetch first row only?
3. I would use "where key between curr_key and key_max" although it might
not make any difference depending on which query optimization level is used.

[AC Slater]

Re: poor select performance


"Mark A" <ma@switchboard .net> wrote in message
news:3NOub.161$ 5h1.24741@news. uswest.net...[color=blue][color=green][color=darkred]
> > > > I have 1 table (out of many) that has very poor performance when
> > > performing
> > > > a select into on. The select statement is called multiple times.[/color][/color]
> We've[color=green][color=darkred]
> > > > found each call to take almost 1 second... we were expecting less[/color][/color][/color]
than[color=blue][color=green]
> > .1[color=darkred]
> > > > seconds.
> > > >
> > > > The select stmt looks like:
> > > >
> > > > select key,data into key_buff, data_buff from tablename where key >
> > > curr_key
> > > > AND key < max_key ORDER by key FETCH FIRST 1 ROW ONLY;
> > > >
> > > > Any idea to troubleshoot performance ?
> > > >
> > > > Thanks
> > > >
> > > Can you please identify the host variables vs. the DB2 columns?
> > >
> > >[/color]
> >
> > key and data are the only table columns. The rest are stored procedure
> > params.
> >
> > /Frank
> >[/color]
> 1. Is there an index on key?
> 2. Have you executed the runstats command on the table and indexes? You[/color]
must[color=blue]
> rebind after runstats is performed.
> 2. How many rows are in the answer set without the fetch first row only?
> 3. I would use "where key between curr_key and key_max" although it might
> not make any difference depending on which query optimization level is[/color]
used.[color=blue]
>
>[/color]

Yes there is an index on key.
There will be about 40,000 rows returned...

The same SQL statement is run on a different table (same structure, just
about half the rows). THe same loop (retrieving 20 rows), takes 1.5 seconds
compared to 30 for the 'bad table'. They have the same structure just a
different number of rows (40K vs 15K).

/F

[AC Slater]

Re: poor select performance

[color=blue][color=green]
> >[/color]
> 1. Is there an index on key?
> 2. Have you executed the runstats command on the table and indexes? You[/color]
must[color=blue]
> rebind after runstats is performed.
> 2. How many rows are in the answer set without the fetch first row only?
> 3. I would use "where key between curr_key and key_max" although it might
> not make any difference depending on which query optimization level is[/color]
used.[color=blue]
>
>[/color]

Interesting enough I found the db2expln for this sql statement and the SAME
sql statement on another system where things perform better ,, Now that I
found this info not sure what to do with it:

--Path on bad system--
Section Code Page = 819

Estimated Cost = 104.929337
Estimated Cardinality = 1.000000

Table Constructor
| 1-Row(s)
Residual Predicate(s)
| #Predicates = 1
Nested Loop Join
| Piped Inner
| Access Table Name = PRODPCIA.RIAUDT ID = 16,4
| | #Columns = 0
| | Index Scan: Name = PRODPCIA.AUD01_ RIAUDT_PK ID = 1
| | | Regular Index (Clustered)
| | | Index Columns:
| | | | 1: LFKEY (Ascending)
| | | #Key Columns = 1
| | | | Start Key: Exclusive Value
| | | | | 1: ?
| | | | Stop Key: Inclusive Value
| | | | | 1: ?
| | | Index-Only Access
| | | Index Prefetch: None
| | | Insert Into Sorted Temp Table ID = t1
| | | | #Columns = 1
| | | | #Sort Key Columns = 1
| | | | | Key 1: (Ascending)
| | | | Sortheap Allocation Parameters:
| | | | | #Rows = 76
| | | | | Row Width = 12
| | | | Piped
| | | | Duplicate Elimination
| | Isolation Level: Uncommitted Read
| | Lock Intents
| | | Table: Intent None
| | | Row : None
| Sorted Temp Table Completion ID = t1
| List Prefetch Preparation
| | Access Table Name = PRODPCIA.RIAUDT ID = 16,4
| | | #Columns = 2
| | | Relation Scan
| | | | Prefetch: 10 Pages
| | | Lock Intents
| | | | Table: Intent Share
| | | | Row : Next Key Share
| | | Sargable Predicate(s)
| | | | #Predicates = 2
Insert Into Sorted Temp Table ID = t2
| #Columns = 2
| #Sort Key Columns = 1
| | Key 1: LFKEY (Ascending)
| Sortheap Allocation Parameters:
| | #Rows = 1
| | Row Width = 140
| | Sort Limited To Estimated Row Count
| Piped
Access Temp Table ID = t2
| #Columns = 2
| Single Record
| Relation Scan
| | Prefetch: Eligible
Return Data to Application
| #Columns = 2

End of section


--- On good system --

SQL Statement:

SELECT LFKEY, CDATA INTO :H00004 :H00005 , :H00010 :H00011
FROM RIAUDT
WHERE LFKEY > :H00004 :H00005 AND LFKEY <= :H00012 :H00013
ORDER BY lfkey
FETCH FIRST 1 ROW ONLY


Section Code Page = 819

Estimated Cost = 0.161109
Estimated Cardinality = 0.000000

Table Constructor
| 1-Row(s)
Residual Predicate(s)
| #Predicates = 1
Nested Loop Join
| Access Table Name = DB2C81.RIAUDT ID = 3,25
| | #Columns = 2
| | Index Scan: Name = DB2C81.RIAUDT_P K ID = 1
| | | Regular Index (Not Clustered)
| | | Index Columns:
| | | | 1: LFKEY (Ascending)
| | | #Key Columns = 1
| | | | Start Key: Exclusive Value
| | | | | 1: ?
| | | | Stop Key: Inclusive Value
| | | | | 1: ?
| | | Data Prefetch: None
| | | Index Prefetch: None
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
Return Data to Application
| #Columns = 2

End of section

[Mark A]

Re: poor select performance

"AC Slater" <notme@yahoo.co m> wrote in message
news:ehQub.1262 2$nE6.2509751@n ews4.srv.hcvlny .cv.net...[color=blue]
>
> Interesting enough I found the db2expln for this sql statement and the[/color]
SAME[color=blue]
> sql statement on another system where things perform better ,, Now that I
> found this info not sure what to do with it:
>[/color]
The first one is creating a temporary table. Looks like it is using UR
isolation level.

Try using CS isolation level (or anything but UR) and add this clause to the
SQL:

optimize for 1 row

This is in addition to the "select 1 row only" clause.

[AC Slater]

Re: poor select performance


"Mark A" <ma@switchboard .net> wrote in message
news:_uQub.168$ 5h1.31801@news. uswest.net...[color=blue]
> "AC Slater" <notme@yahoo.co m> wrote in message
> news:ehQub.1262 2$nE6.2509751@n ews4.srv.hcvlny .cv.net...[color=green]
> >
> > Interesting enough I found the db2expln for this sql statement and the[/color]
> SAME[color=green]
> > sql statement on another system where things perform better ,, Now that[/color][/color]
I[color=blue][color=green]
> > found this info not sure what to do with it:
> >[/color]
> The first one is creating a temporary table. Looks like it is using UR
> isolation level.
>
> Try using CS isolation level (or anything but UR) and add this clause to[/color]
the[color=blue]
> SQL:
>
> optimize for 1 row
>
> This is in addition to the "select 1 row only" clause.
>
>[/color]

How do I change the isolation level? I really dont have access to change
the stored procedure as it is performing ok on most DBs. I was/am hoping to
figure out what can be done to the system DB itslef(maybe the isolation
level) or table organization/stats to fix the bad performance.

/F

[Mark A]

Re: poor select performance

> How do I change the isolation level? I really dont have access to change[color=blue]
> the stored procedure as it is performing ok on most DBs. I was/am hoping[/color]
to[color=blue]
> figure out what can be done to the system DB itslef(maybe the isolation
> level) or table organization/stats to fix the bad performance.
>
> /F
>[/color]
I am not an expert is stored procedures, but I assume that isolation level
is a bind parameter for the stored procedure. But the problem is that it is
creating a temporary result table and sorting the results and then returning
the first row. If there is an index on the only predicate column (which is
the same column as the order by), I am not sure why it is doing the
temporary table.

Are you sure you gave the actual SQL statement that is being used? Did you
reorg the table, perform runstats, and then rebind?

[AC Slater]

Re: poor select performance


"Mark A" <ma@switchboard .net> wrote in message
news:ZhRub.178$ 5h1.35422@news. uswest.net...[color=blue][color=green]
> > How do I change the isolation level? I really dont have access to[/color][/color]
change[color=blue][color=green]
> > the stored procedure as it is performing ok on most DBs. I was/am[/color][/color]
hoping[color=blue]
> to[color=green]
> > figure out what can be done to the system DB itslef(maybe the isolation
> > level) or table organization/stats to fix the bad performance.
> >
> > /F
> >[/color]
> I am not an expert is stored procedures, but I assume that isolation[/color]
level[color=blue]
> is a bind parameter for the stored procedure. But the problem is that it[/color]
is[color=blue]
> creating a temporary result table and sorting the results and then[/color]
returning[color=blue]
> the first row. If there is an index on the only predicate column (which is
> the same column as the order by), I am not sure why it is doing the
> temporary table.
>
> Are you sure you gave the actual SQL statement that is being used? Did you
> reorg the table, perform runstats, and then rebind?
>
>[/color]

As Returned by the db2expln the SQL is:

SELECT LFKEY, CDATA Into :H0004 :H0005, :H00010 :H00011
FROM RITABLE
WHERE LFKEY > :H0004 :H0005 AND LFKEY <= :H00012 :H00013
ORDER BY LFKEY
FETCH FIRST 1 ONLY

That portion of the db2explan is the same on both (the good and bad system).
The access plan below it is what differs.

/Frank

[Mark A]

Re: poor select performance

"AC Slater" <notme@yahoo.co m> wrote in message
news:onRub.1475 4$nE6.2740060@n ews4.srv.hcvlny .cv.net...[color=blue]
>
> As Returned by the db2expln the SQL is:
>
> SELECT LFKEY, CDATA Into :H0004 :H0005, :H00010 :H00011
> FROM RITABLE
> WHERE LFKEY > :H0004 :H0005 AND LFKEY <= :H00012 :H00013
> ORDER BY LFKEY
> FETCH FIRST 1 ONLY
>
> That portion of the db2explan is the same on both (the good and bad[/color]
system).[color=blue]
> The access plan below it is what differs.
>
> /Frank
>[/color]
I noticed that the index was clustered in the first one and not clustered in
the second one. If the table and clustered index is dis-organized, this
could be a problem, but not sure. Do reorg, runstats, and rebind.