This topic has been archived. It cannot be replied.
-
工作学习 / IT技术讨论 / sql server2000里面一个store procedure, 用时超过一秒钟。table1 里面记录有1,000,000条。index如何加呢？谢谢。@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
-lilyba(sunshine 顶风作案);
2004-12-17
{246}
(#2032593@0)
-
see inside1. 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
"
-whatwhenwherewhy(一生何求);
2004-12-17
{243}
(#2032847@0)
-
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)
-
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)