×

Loading...
Ad by
  • 推荐 OXIO 加拿大高速网络，最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务
Ad by
  • 推荐 OXIO 加拿大高速网络，最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务

@Florida

Interesting SQL issue. Please come in and give me a hand...

hanarmy(汉军)
本文发表在 rolia.net 枫下论坛I've got a table with half a million rows. One of the columns is of type DEC(7,2). Let's call it column A. Now user wants to inquire the table by A - he inputs an amount, then I show the first 300 rows that are greater than that amount, sorting the result set by A.

Because there are half a million rows, performance may be a problem. So I created an index on A. But, interestingly, the system refuses to use that index. It says that it would be more costly to use that index than to use arrival sequence access! So it puts the result set into a temporary file and sort the quarter-million records at run time. The result is a response time of 20 seconds. That's surely not satisfactory.

Then I removed the index and tested again. The reponse time remains the same. And system suggested me to create an index on A. Isn't it funny?

Finally I tried to force the use of the index by making the SORT BY clause the same as the keys of the index. This time the system goes crazy and says it has to create a temporary table for the original table before it can start working on it. Consequently, the reponse time becomes more than 2 minutes!

Now I'm wandering if there is any restriction on using a decimal column as a key. Or maybe there are other ways to approach this issue because only the first 300 rows in the result set are needed.

Guys, give me a hand! I'd appreciate it a lot! btw, I'm using UDB for iSeries.

I may have put this post in the wrong category because my PC can't show Chinese. Dear webmaster please don't delete this post if so. Thanks!更多精彩文章及讨论，请光临枫下论坛 rolia.net
(#678805@0)
Last Updated: 2002-8-7
This post has been archived. It cannot be replied.
Report

Replies, comments and Discussions:

  • 工作学习 / 求学深造 / Interesting SQL issue. Please come in and give me a hand... -hanarmy(汉军); 2002-8-7 {1578} (#678805@0)
    本文发表在 rolia.net 枫下论坛I've got a table with half a million rows. One of the columns is of type DEC(7,2). Let's call it column A. Now user wants to inquire the table by A - he inputs an amount, then I show the first 300 rows that are greater than that amount, sorting the result set by A.

    Because there are half a million rows, performance may be a problem. So I created an index on A. But, interestingly, the system refuses to use that index. It says that it would be more costly to use that index than to use arrival sequence access! So it puts the result set into a temporary file and sort the quarter-million records at run time. The result is a response time of 20 seconds. That's surely not satisfactory.

    Then I removed the index and tested again. The reponse time remains the same. And system suggested me to create an index on A. Isn't it funny?

    Finally I tried to force the use of the index by making the SORT BY clause the same as the keys of the index. This time the system goes crazy and says it has to create a temporary table for the original table before it can start working on it. Consequently, the reponse time becomes more than 2 minutes!

    Now I'm wandering if there is any restriction on using a decimal column as a key. Or maybe there are other ways to approach this issue because only the first 300 rows in the result set are needed.

    Guys, give me a hand! I'd appreciate it a lot! btw, I'm using UDB for iSeries.

    I may have put this post in the wrong category because my PC can't show Chinese. Dear webmaster please don't delete this post if so. Thanks!更多精彩文章及讨论，请光临枫下论坛 rolia.net
    • What's the version of your UDB and what's your client/server? -h4ppy(哈皮); 2002-8-7 (#678813@0)
      • The OS/400 version is V5R1. It's server side programming. -hanarmy(汉军); 2002-8-7 (#678889@0)
    • h4ppy, Mr. Jianghong, Torontoguy, and other high-hands that I havn't got a chance to know yet, please take a look here. Thanks! -hanarmy(汉军); 2002-8-7 (#678918@0)
      • ^ -hanarmy(汉军); 2002-8-7 (#679015@0)
    • Try http://groups.google.ca/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&group=comp.databases.ibm-db2; If you're IBMer, try IBM.SOFTWARE.DB2.UDB -wdding(shy sheep); 2002-8-7 (#679270@0)
      • Thank you, nice girl(?)! -hanarmy(汉军); 2002-8-7 (#679318@0)
    • something need to be considered -uid(㊣Miracle); 2002-8-7 {785} (#679627@0)
      1.not sure the index you create is "unique index" or index
      I think the index would be better.

      2, each time you create index , you 'd better drop the old index

      3. the numer of index colums can not greater than 16.

      4. The sum of the stored lengths of the specified columns must not be greater than 1024

      5. The length of any individual column must not be greater than 255 bytes. No LOB column, DATALINK column, or distinct type column based on a LOB or DATALINK may be used as part of an index, even if the length attribute of the column is small enough to fit within the 255 byte limit (SQLSTATE 42962). A structured type column can only be specified if the EXTEND USING clause is also specified


      6. Type dec should be OK, can you provide anything else info there ?
      • 谢谢！仔细看了你的贴，没有发现问题。现在的情况是程序可以运行，那些信息是我启动调试工具后看到的。详细情况容我再整理一下贴出来，能不能说说你最关心哪方面？ -hanarmy(汉军); 2002-8-7 (#679700@0)
        • 无心干活，提前开溜。 吹毛求疵，不明白"using a decimal column as a key"？what that exactly mean？ -uid(㊣Miracle); 2002-8-7 {191} (#679781@0)
          index 最好建在foreign key上。 但建立在decimal field 上也可以。
          能说说你的Select sentence 吗？ “show the first 300 rows that are greater than that amount” 是不是可以用了什么函数代替sorting?
          • 18:49，在哪？家里？这也算提前开溜？我5：30已经在家看报纸了。：） -hanarmy(汉军); 2002-8-7 {298} (#679969@0)
            说正经，那句话是说用DECIMAL型的字段作键值。

            能不能说说为什么INDEX要建在FOREIGN KEY上，看不出直接的联系啊。

            下面是SQL句子：
            SELECT * FROM AFILE WHERE A > :X ORDER BY A
            本来还有个JOIN的，也忍痛去掉了。

            那前300条记录是指键值最小的300条，好想还是免不了给整个RESULT SET排序，除非能用上INDEX。
            • Although job is boring, but it still need to pretend busying......// Got some idea from my friends -uid(㊣Miracle); 2002-8-8 {577} (#681557@0)
              The solution for your db2 performance problem.
              Suppose you want to impose the constraint col1>100 order by col3. Do the following to improve SQL performance.

              SQL statement:
              Select Col1, Col2, Col3, Col4 from TABLE1 where Col1>100 order by Col3

              To improve performance:
              1. create index INDEX1 on TABLE1 (Col1, Col3) CLUSTER
              2. reorg table TABLE1 index INDEX1 use TEMPSPACE1
              3. runstats on table TABLE1 with distribution and detailed indexes all

              NOTE: you only need to do the above steps once and should improve the performance when running the above SQL statement.
    • 早上发贴的时候不得不用英文，想来大家不爱看。本想就这样交活了，又有点不甘，想弄明白到底咋回事。其实这篇英文还没到不忍卒读的地步，大家看看，讨论讨论，也许都能有所收获。shy sheep推荐的论坛今晚俺也会贴上去试试。 -hanarmy(汉军); 2002-8-7 (#679710@0)
      • if you do a DSPFFD, A is packed or signed? if P try S, if S try P. -mssg(mssg); 2002-8-7 (#679892@0)
        • 明天试试，只为研究。LEGACY的东西动起来很麻烦啊。另外，如果只SELECT一条记录，那个INDEX就会被用上；用CURSOR，就用不上，想不通。 -hanarmy(汉军); 2002-8-7 (#679944@0)
    • 今天在SHY SHEEP推荐的论坛上贴出不出1小时, 就受到了有效的解决方案, 真是高手如云啊. 这里特别谢谢SHY SHEEP! -hanarmy(汉军); 2002-8-8 (#681715@0)
      • share me the result please...... um :-) -uid(㊣Miracle); 2002-8-8 (#681738@0)
        • 在那里共得到3种方案 -hanarmy(汉军); 2002-8-8 {281} (#682004@0)
          1. 给SQL语句加上OPTIMIZE FOR 1 ROWS
          - 解决了问题

          2. 给SQL语句加上FETCH FIRST 300 ROWS ONLY
          - 为了得到前300条记录, 系统还是把整个RESULT SET排了序, 然后把那300条记录放到另一个临时文件里再排序. 够DUMB.

          3. 就是你的朋友的方案
          - 还没试. 不过有时间一定要试试. 你的朋友是不是DBA?
          • 我的DB2不支持CLUSTERED INDEX, 按索引REORGANIZE文件后, 命中率已足够高, 但系统仍然不用索引. 你怎么没声了? 你朋友到底干吗的? 让咱好认识一下差距啊. -hanarmy(汉军); 2002-8-9 (#684009@0)

More Topics

  • 福特政府要求医学院招生无关种族，TMU75％照顾率 真他妈太扯了
  • 意识到TMU 跟 TEMU差不多 , 以后就叫Ryerson 拼多多大学:D
  • 数学家陈景润获批国家杰出青年基金_手机网易网
  • 想申请一下U of T, CS .应该选哪个学院, 如果早申, Nov 2, 什么时候出结果?
  • 这个对一般民众，好消息的吧？ZT: 加州从此禁止大学legacy录取和捐款买录取
    • 枫下论坛主坛 /  工作学习 /  求学深造