This website requires Javascript to function properly. Please go to the setting of your web browser and enable Javascript for this website.

Ad by

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

Ad by

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

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)

@Florida

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

Replies, comments and Discussions:

More Topics