×

Loading...
Ad by
  • 最优利率和cashback可以申请特批，好信用好收入offer更好。请点链接扫码加微信咨询，Scotiabank -- Nick Zhang 6478812600。
Ad by
  • 最优利率和cashback可以申请特批，好信用好收入offer更好。请点链接扫码加微信咨询，Scotiabank -- Nick Zhang 6478812600。

@Ontario

I guess you want to tune the 1 second query because you are looping this sp call...

whatwhenwherewhy(一生何求)
better idea is to re-design the db model to avoid cursor or looping...

botoom line to create index is selectivity. if your year and iid column has many duplicated record (means bad selectivity), then you will not benefit too much from creating index on these column.

create index on column that is highly selective. you can find selectivity by running

select year, iid, count(*) from table_name group by year,iid
or
select unique year, iid from table_name

look at other column's selectivity and see if you can benefit from creating index on them.
(#2033270@0)
Last Updated: 2004-12-17
This post has been archived. It cannot be replied.
Report

Replies, comments and Discussions:

  • 工作学习 / IT技术讨论 / sql server2000里面一个store procedure, 用时超过一秒钟。table1 里面记录有1,000,000条。index如何加呢？谢谢。 -lilyba(sunshine 顶风作案); 2004-12-17 {246} (#2032593@0)
    @year smallint,
    @iid uniqueidentifier
    AS

    begin

    if exists (select 1 from dbo.table1
    where year = @year - 1
    and iid = @iid
    and column1 = '1'
    and column2 = 'a'
    and column3 = 'CURR'
    )
    return 0
    else
    return -1
    end
    • see inside -whatwhenwherewhy(一生何求); 2004-12-17 {243} (#2032847@0)
      1. create an index on colume iid, because it's unique identifier.
      2. in order to use this index, you need to modify the proc from

      "
      where year = @year - 1
      and iid = @iid
      "

      to

      "
      where iid = @iid
      and year = @year - 1
      "
      • Thanks. but iid is not a unique identifier since different year has same iid; in same year, same iid might have 'curr' and 'hist' status. I build index on year and iid, still cannot improve performance. -lilyba(sunshine 顶风作案); 2004-12-17 (#2033083@0)
        • Make sure the @year and @iid data types defined in the stored proc are the same as they are in the table. -nexttime(11); 2004-12-17 (#2033097@0)
          • Yes, I checked, the data types are exactly same. -lilyba(sunshine 顶风作案); 2004-12-17 (#2033153@0)
            • I guess you want to tune the 1 second query because you are looping this sp call... -whatwhenwherewhy(一生何求); 2004-12-17 {562} (#2033270@0)
              better idea is to re-design the db model to avoid cursor or looping...

              botoom line to create index is selectivity. if your year and iid column has many duplicated record (means bad selectivity), then you will not benefit too much from creating index on these column.

              create index on column that is highly selective. you can find selectivity by running

              select year, iid, count(*) from table_name group by year,iid
              or
              select unique year, iid from table_name

              look at other column's selectivity and see if you can benefit from creating index on them.
              • iid is highly selective. since this sp affect performance a lot, if i can only change this sp, do not need to affect any coding, that would be better. Later I am going to change the code. This sp is not good. -lilyba(sunshine 顶风作案); 2004-12-17 (#2033681@0)
    • 世界变化快。一秒钟的程序也需要优化一下了。。。 -aka(棒棒); 2004-12-17 (#2033660@0)
      • I guess if this sp is called millions of time in a nested sp or loop, it will be a bottleneck for an overall transaction. -whatwhenwherewhy(一生何求); 2004-12-17 (#2033720@0)

More Topics

  • 肉脸真有搞 IT 的？Linux 把俄国中国伊朗踢出去了好像肉脸 IT 没啥感觉似的
  • 请问web developer和software developer技术上有什么区别？
  • Elon Musk 带着川普总统奔向火星了。
  • 扔硬币的实验研究
  • 哪位Excel大神帮忙解答一下，一个spreadsheet一表格connection到另外一个spreadsheet，原来的data是数字格式，但是connection以后变成text格式，这是为什么呢？这两天才出现的问题，之前好多年没有过这样的问题。谢谢先
    • 枫下论坛主坛 /  工作学习 /  IT技术讨论