选择性能差 [英] poor select performance

问题描述

我有一张桌子（其中很多）在执行

选择时表现非常糟糕。 select语句被多次调用。我们已经发现每次通话需要花费近1秒才能获得
...我们预计会少于.1

秒。


select stmt看起来像：


选择密钥，数据输入key_buff，data_buff来自tablename，其中key> gt; curr_key

AND key< max_key ORDER by key FETCH FIRST 1行;


是否有任何想法来解决性能问题？


谢谢

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

推荐答案

" AC Slater" <无*** @ yahoo.com>在消息中写道

新闻：英国******************** @ news4.srv.hcvlny.cv.ne t ...

"AC Slater" <no***@yahoo.com> wrote in message
news:UK********************@news4.srv.hcvlny.cv.ne t...

当
执行select into时，我有1个表（在众多表中）表现非常糟糕。 select语句被多次调用。我们已经发现每个电话花了将近1秒......我们期待的时间少于.1
秒。

选择stmt看起来像：

选择键，将数据输入key_buff，将data_buff从tablename中键入>
curr_key AND key< max_key ORDER by key FETCH FIRST 1行唯一;

任何想要解决性能的想法？

谢谢

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

你能否确认一下主变量与DB2列相比？

Can you please identify the host variables vs. the DB2 columns?

标记A < [e-mail]>在消息中写道

新闻：K _ ***************** @ news.uswest.net ...

"Mark A" <[e-mail]> wrote in message
news:K_*****************@news.uswest.net...

" AC Slater <无*** @ yahoo.com>在消息中写道
新闻：英国******************** @ news4.srv.hcvlny.cv.ne t ...

"AC Slater" <no***@yahoo.com> wrote in message
news:UK********************@news4.srv.hcvlny.cv.ne t...

当

执行

选择进入时，我有1个表（在众多表中）表现非常差。 select语句被多次调用。我们已经发现每个电话花了将近1秒......我们预计会花费不到
.1秒。

选择stmt看起来像：

选择键，数据到key_buff，data_buff来自tablename，其中键>

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

curr_key

AND key< max_key ORDER by key FETCH FIRST 1行唯一;

任何想要解决性能的想法？

谢谢

AND key < max_key ORDER by key FETCH FIRST 1 ROW ONLY;

Any idea to troubleshoot performance ?

Thanks

你能否确定主变量与？ DB2列？

Can you please identify the host variables vs. the DB2 columns?

键和数据是唯一的表列。其余的是存储过程

params。


/ Frank

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

/Frank

> > >当

> > > I have 1 table (out of many) that has very poor performance when

执行

选择进入时，我有1个表（在众多中）表现非常差。 select语句被多次调用。
我们发现每次通话都需要1秒钟......我们预计不到0.1秒。

选择stmt看起来像：

select key，data into key_buff，data_buff from tablename where key>

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

curr_key

AND key< max_key ORDER by key FETCH FIRST 1行唯一;

任何想要解决性能的想法？

谢谢

AND key < max_key ORDER by key FETCH FIRST 1 ROW ONLY;

Any idea to troubleshoot performance ?

Thanks

你能否确定主变量与？ DB2列？

Can you please identify the host variables vs. the DB2 columns?

键和数据是唯一的表列。其余的是存储过程
params。

/ Frank

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

/Frank

1.密钥上有索引吗？

2.您是否在表和索引上执行了runstats命令？执行runstats后，你必须

重新绑定。

2.答案集中有多少行没有第一行获取？

3我会使用curr_key和key_max之间的关键字。虽然根据使用的查询优化级别，它可能不会产生任何差异。

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.

这篇关于选择性能差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！