×

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

@Calgary

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

  • 老程序员第一次用copilot的一点点体会
  • 现在社区游泳池招救生员巡逻，过一会还要不停的点人头，这工作是否很快就被AI代替了，装一个摄像头后面是AI处理系统，这样只招一个等着救人就行了，人头沉下水一定时间立马强光照射，还可以分析水下人的状况来判断是否溺水等
  • 新冠疫苗引发心肌炎? 最新研究这样看
  • 这道几何题有解吗?
  • SAM 2 Demo
    • 枫下论坛主坛 /  工作学习 /  IT技术讨论