存储过程，查看工作缓慢 [英] Stored Procedure,View Working Slow
问题描述
大家好，
我正在网站上工作。对于管理员，我需要生成包含9-10个表格数据的报告。
我创建了存储过程，但工作速度很慢，需要5分钟到在sql server 2005中获取数据，所以在我的网站上，我得到超时异常。
正如我所说，我从9-10个表中获取数据，所以我使用了内连接。内连接会减慢存储过程吗？
为此，我尝试使用视图。我按照我的要求创建了5到6个视图，并在一个存储过程中使用了这些视图。但仍然非常慢。
请帮我解决上述情况..
以下是我的存储过程
Hi all,
I am working on website . For administrator, i need to generate report which contains data from 9-10 tables.
I have created stored procedure but it is working very slow, it takes 5 mins to get data in sql server 2005 so in my website, i get timeout exception.
As i said, i fetch data from 9-10 tables so i used inner join for that. is inner join slows down stored procedure?
For this, i tried to work with view. I craeated 5 to 6 views as per my requirement and used those views in one stored procedure. but still its very slow.
Please help me with above scenario..
Below is my stored procedure
CREATE Procedure getAdminReport -- '91,93,826,823,83,86,712,85,82,88',12,12,2012,2012
(
@eid varchar(500),
@from_month int,
@to_month int,
@from_year int,
@to_year int,
@desg int= null
)
as
CREATE TABLE #2
(
LIDList Int
)
-- Optional index on the temp table
CREATE INDEX idx1 ON #2 (LIDList)
INSERT INTO #2
SELECT Convert(Int, NullIf(SubString(',' + @eid + ',' , ID ,
CharIndex(',' , ',' + @eid + ',' , ID) - ID) , '')) AS LIDList
FROM tblToolsStringParserCounter
WHERE ID <= Len(',' + @eid + ',') AND SubString(',' +
@eid + ',' , ID - 1, 1) = ','
AND CharIndex(',' , ',' + @eid + ',' , ID) - ID > 0
select EC.eid,(ED.first_name+' '+ED.last_name) as FullName,st.state_name,region.state_name as region,city.state_name as city,ED.designation,ED.employee_id,
sum(Case when DB.cd_type=0 Then 1 Else 0 End) as cnt_clinic,
sum(Case when DB.cd_type=1 Then 1 Else 0 End) as cnt_camp,
sum(Case when DB.activity_type=0 Then 1 Else 0 End) as cnt_spir,
sum(Case when DB.activity_type=1 Then 1 Else 0 End) as cnt_breat,
sum(Case when DB.activity_type=2 Then 1 Else 0 End) as cnt_vitalo,
sum(Case when DB.timing=0 Then 1 Else 0 End) as cnt_morning,
sum(Case when DB.timing=1 Then 1 Else 0 End) as cnt_eve,
sum(Case when DB.timing=2 Then 1 Else 0 End) as cnt_full
from doc_business_data DB
inner join educator_call_dtls EC on DB.call_id=EC.call_id
inner join doctor_master DM on DM.doc_id=DB.doc_id
inner join educator_master ED on ED.eid=EC.eid
inner join educator_state_master city on city.state_city_id=ED.city_id
inner join educator_state_master region on region.state_city_id=city.parent_id
inner join educator_state_master st on st.state_city_id = region.parent_id
where EC.eid in (select LIDList FROM #2) AND ED.designation <> 'BM' AND ED.designation <> 'SM' AND ED.designation <> 'SuperAdmin' and
(((year(EC.call_date) > @from_year) OR
推荐答案
不要使用视图。视图很慢......很慢......
您可能想要尝试实际执行计划，这样您就可以看到查询的哪个部分花费的时间最长，然后您可能需要向表中添加另一个索引，或者将主表切换到查询中的其他表。
关于你的SQL我觉得你可以加快速度的方法之一就是把你的WHERE放在内部联接中。
Dont use views. Views are slow....very slow....
You may perhaps want to try the Actual Execution Plan, so you can see what part of the query is taking the longest, and then you may have to add another index to the table, or switch the main table to a different table in the query.
With regards to your SQL I think one of the ways you could speed it up is placing your WHERE in the Inner Join.
CREATE Procedure getAdminReport -- '91,93,826,823,83,86,712,85,82,88',12,12,2012,2012
(
@eid varchar(500),
@from_month int,
@to_month int,
@from_year int,
@to_year int,
@desg int= null
)
as
CREATE TABLE #2
(
LIDList Int
)
-- Optional index on the temp table
CREATE INDEX idx1 ON #2 (LIDList)
INSERT INTO #2
SELECT Convert(Int, NullIf(SubString(',' + @eid + ',' , ID ,
CharIndex(',' , ',' + @eid + ',' , ID) - ID) , '')) AS LIDList
FROM tblToolsStringParserCounter
WHERE ID <= Len(',' + @eid + ',') AND SubString(',' +
@eid + ',' , ID - 1, 1) = ','
AND CharIndex(',' , ',' + @eid + ',' , ID) - ID > 0
select EC.eid,(ED.first_name+' '+ED.last_name) as FullName,st.state_name,region.state_name as region,city.state_name as city,ED.designation,ED.employee_id,
sum(Case when DB.cd_type=0 Then 1 Else 0 End) as cnt_clinic,
sum(Case when DB.cd_type=1 Then 1 Else 0 End) as cnt_camp,
sum(Case when DB.activity_type=0 Then 1 Else 0 End) as cnt_spir,
sum(Case when DB.activity_type=1 Then 1 Else 0 End) as cnt_breat,
sum(Case when DB.activity_type=2 Then 1 Else 0 End) as cnt_vitalo,
sum(Case when DB.timing=0 Then 1 Else 0 End) as cnt_morning,
sum(Case when DB.timing=1 Then 1 Else 0 End) as cnt_eve,
sum(Case when DB.timing=2 Then 1 Else 0 End) as cnt_full
from doc_business_data DB
inner join educator_call_dtls EC on DB.call_id=EC.call_id
inner join doctor_master DM on DM.doc_id=DB.doc_id
inner join educator_master ED on ED.eid=EC.eid
-- ##########################################################################################
-- CHANGES BELOW
AND ED.designation <> 'BM'
AND ED.designation <> 'SM'
AND ED.designation <> 'SuperAdmin'
-- ##########################################################################################
inner join educator_state_master city on city.state_city_id=ED.city_id
inner join educator_state_master region on region.state_city_id=city.parent_id
inner join educator_state_master st on st.state_city_id = region.parent_id
where EC.eid in (select LIDList FROM #2) AND ED.designation <> 'BM' AND ED.designation <> 'SM' AND ED.designation <> 'SuperAdmin' and
(((year(EC.call_date) > @from_year) OR
应该做的是缩小连接所需的记录数量。如果你能用更多的连接来做到这一点，这将有很大的帮助！ />
如果你想象你有1,000,000行，但只有350,000行是你感兴趣的行，只加入350,000会更快！
祝你好运！
What that should do is narrow down the number of records needed in the join. If you can do that with more of the joins this will help a lot!
If you imagine you have 1,000,000 rows, but only 350,000 of them are the ones you are interested in, only joining on the 350,000 will be faster!
Good Luck!
这篇关于存储过程，查看工作缓慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
