SQL查询花费太多时间 [英] SQL query is taking too much time

查看：75
本文介绍了SQL查询花费太多时间的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在使用下面的查询从主表table1中获取3年数据.性能非常慢.任何人都可以通过我的下面的查询并提供替代方法来提高性能.

请提供有价值的建议.

如果您不能在下面的查询中理解此内容.随便问.谢谢

-四分之一的老鼠

I am fetching 3 yrs data from my main table table1 using below query.The performance is very slow.Can anyone go through my below query and provide me alternate way so that performance can be increased.

pls. provide ur valueable suggessions.

If you are not able to understand this below query pls. feel free to ask. Thanks

--Quaterly rat

Declare @Dataset1 table (

 [AID] varchar(10),
[AName] varchar(30),

[Qtr1 2007] varchar(2),
[Qtr2 2007] varchar(2),
[Qtr3 2007] varchar(2),
[Qtr4 2007] varchar(2),
[Qtr1 2008] varchar(2),
[Qtr2 2008] varchar(2),
[Qtr3 2008] varchar(2),
[Qtr4 2008] varchar(2),
[Qtr1 2009] varchar(2),
[Qtr2 2009] varchar(2),
[Qtr3 2009] varchar(2),
[Qtr4 2009] varchar(2)

  )

insert into @Dataset1
SELECT s_id ''AID'', aname ''name'',
CONVERT(CHAR(1), AVG(CASE quarter WHEN 20071 THEN p_rat END)) +
CONVERT(CHAR(1), AVG(CASE quarter WHEN 20071 THEN c_rat END)) ''Qtr1 2007'',
CONVERT(CHAR(1), AVG(CASE quarter WHEN 20072 THEN p_rat END)) +
CONVERT(CHAR(1), AVG(CASE quarter WHEN 20072 THEN c_rat END)) ''Qtr2 2007'',
CONVERT(CHAR(1), AVG(CASE quarter WHEN 20073 THEN p_rat END)) +
CONVERT(CHAR(1), AVG(CASE quarter WHEN 20073 THEN c_rat END)) ''Qtr3 2007'',
CONVERT(CHAR(1), AVG(CASE quarter WHEN 20074 THEN p_rat END)) +
CONVERT(CHAR(1), AVG(CASE quarter WHEN 20074 THEN c_rat END)) ''Qtr4 2007'',
CONVERT(CHAR(1), AVG(CASE quarter WHEN 20081 THEN p_rat END)) +
CONVERT(CHAR(1), AVG(CASE quarter WHEN 20081 THEN c_rat END)) ''Qtr1 2008'',
CONVERT(CHAR(1), AVG(CASE quarter WHEN 20082 THEN p_rat END)) +
CONVERT(CHAR(1), AVG(CASE quarter WHEN 20082 THEN c_rat END)) ''Qtr2 2008'',
CONVERT(CHAR(1), AVG(CASE quarter WHEN 20083 THEN p_rat END)) +
CONVERT(CHAR(1), AVG(CASE quarter WHEN 20083 THEN c_rat END)) ''Qtr3 2008'',
CONVERT(CHAR(1), AVG(CASE quarter WHEN 20084 THEN p_rat END)) +
CONVERT(CHAR(1), AVG(CASE quarter WHEN 20084 THEN c_rat END)) ''Qtr4 2008'',
CONVERT(CHAR(1), AVG(CASE quarter WHEN 20091 THEN p_rat END)) +
CONVERT(CHAR(1), AVG(CASE quarter WHEN 20091 THEN c_rat END)) ''Qtr1 2009'',
CONVERT(CHAR(1), AVG(CASE quarter WHEN 20092 THEN p_rat END)) +
CONVERT(CHAR(1), AVG(CASE quarter WHEN 20092 THEN c_rat END)) ''Qtr2 2009'',
CONVERT(CHAR(1), AVG(CASE quarter WHEN 20093 THEN p_rat END)) +
CONVERT(CHAR(1), AVG(CASE quarter WHEN 20093 THEN c_rat END)) ''Qtr3 2009'',
CONVERT(CHAR(1), AVG(CASE quarter WHEN 20094 THEN p_rat END)) +
CONVERT(CHAR(1), AVG(CASE quarter WHEN 20094 THEN c_rat END)) ''Qtr4 2009''
from table1 t1,
table2 SC,        
table3 HC,           
table2 SP,        
table3 HP
WHERE
t1.quarter in(20071,20072,20073,20074,20081,20082,20083, 20084, 20091, 20092, 20093,2004)
AND t1.completed = 1             
 AND (t1.delete_flag IS NULL OR t1.delete_flag = '''')                 
 AND (t1.skip_flag IS NULL OR t1.skip_flag = '''')
AND HP.from_qtr <= t1.quarter          
AND HC.from_qtr <= t1.quarter          
AND ISNULL(HP.to_qtr,t1.quarter) >= t1.quarter          
AND HP.hdr_id = SP.hdr_id         
AND SP.type = ''performance''       
AND ISNULL(HC.to_qtr,t1.quarter) >= t1.quarter          
AND HC.hdr_id = SC.hdr_id         
AND SC.type = ''competency''        
AND t1.p_rat >= SP.start_range        
AND t1.p_rat < SP.end_range           
AND t1.c_rat >= SC.start_range
group by t1.s_id, t1.aname

--select * from @Dataset1
----Year SC
Declare @Dataset2 table (
 s_id varchar(10),
 P2007 numeric(5,2),
 C2007 numeric(5,2),
 P2008 numeric(5,2),
 C2008 numeric(5,2),
 P2009 numeric(5,2),
 C2009 numeric(5,2)
)

insert into @Dataset2
select
distinct
t1.s_id,
Case WHEN left(t1.Quarter,4) = 2007 THEN round((sum(avg_ps)*1.0) / (count(avg_ps)*1.0),2,1) end as P2007,
Case WHEN left(bsc.Quarter,4) = 2007 THEN round((sum(avg_cs)*1.0) / (count(avg_cs)*1.0),2,1) end as C2007,
Case WHEN left(bsc.Quarter,4) = 2008 THEN round((sum(avg_ps)*1.0) / (count(avg_ps)*1.0),2,1) end as P2008,
Case WHEN left(bsc.Quarter,4) = 2008 THEN round((sum(avg_cs)*1.0) / (count(avg_cs)*1.0),2,1) end as C2008,
Case WHEN left(bsc.Quarter,4) = 2009 THEN round((sum(avg_ps)*1.0) / (count(avg_ps)*1.0),2,1) end as P2009,
Case WHEN left(bsc.Quarter,4) = 2009 THEN round((sum(avg_cs)*1.0) / (count(avg_cs)*1.0),2,1) end as C2009
from table t1,
table2 SC,        
table3 HC,           
table2 SP,        
table3 HP
WHERE
t1.quarter in(20071,20072,20073,20074,20081,20082,20083, 20084, 20091, 20092, 20093,20094)
AND t1.completed = 1             
 AND (t1.delete_flag IS NULL OR t1.delete_flag = '''')                 
 AND (t1.skip_flag IS NULL OR t1.skip_flag = '''')
group by
      t1.s_id, left(t1.Quarter,4)

--select * from @Dataset2
----Year Rat
Declare @Dataset3 table (
 s_id varchar(10),
 PR2007 numeric(2,0),
 CR2007 numeric(2,0),
 PR2008 numeric(2,0),
 CR2008 numeric(2,0),
 PR2009 numeric(2,0),
 CR2009 numeric(2,0)
)
insert into @Dataset3
select distinct
t1.s_id,
case when ((avg(t1.P2007)>= SC.start_range) and (avg(t1.P2007)< SC.end_range)) then SC.rating end as PR2007,
case when ((avg(t1.C2007)>= SC.start_range) and (avg(bsc.C2007)< SC.end_range)) then SC.rating end as CR2007,
case when ((avg(t1.P2008)>= SC.start_range) and (avg(t1.P2008)< SC.end_range)) then SC.rating end as PR008,
case when ((avg(t1.C2008)>= SC.start_range) and (avg(t1.C2008)< SC.end_range)) then SC.rating end as CR2008,
case when ((avg(t1.P2009)>= SC.start_range) and (avg(t1.P2009)< SC.end_range)) then SC.rating end as PR2009,
case when ((avg(t1.C2009)>= SC.start_range) and (avg(t1.C2009)< SC.end_range)) then SC.rating end as CR2009
from @Dataset2 t1,
table2 SC
group by
 t1.s_id,
 SC.start_range,
 SC.end_range,
 SC.rating 

--select * from @Dataset3
Declare @GridDataSet table    (
 [AID] varchar(10),
 [AName] varchar(30),
 [Qtr1 2007] varchar(2),
 [Qtr2 2007] varchar(2),
 [Qtr3 2007] varchar(2),
 [Qtr4 2007] varchar(2),
 [Qtr1 2008] varchar(2),
 [Qtr2 2008] varchar(2),
 [Qtr3 2008] varchar(2),
 [Qtr4 2008] varchar(2),
 [Qtr1 2009] varchar(2),
 [Qtr2 2009] varchar(2),
 [Qtr3 2009] varchar(2),
 [Qtr4 2009] varchar(2),
 P2007 numeric(3,2),
 C2007 numeric(3,2),
 P2008 numeric(3,2),
 C2008 numeric(3,2),
 P2009 numeric(3,2),
 C2009 numeric(3,2),
 PR2007 numeric(2,0),
 CR2007 numeric(2,0),
 PR2008 numeric(2,0),
 CR2008 numeric(2,0),
 PR2009 numeric(2,0),
 CR2009 numeric(2,0)
)

insert into @GridDataSet
select
      a.*,
      b.P2007,
      b.C2007,
      b.P2008,
      b.C2008,
      b.P2009,
      b.C2009,
      c.PR2007,
      c.CR2007,
      c.PR2008,
      c.CR2008,
      c.PR2009,
      c.CR2009
from @Dataset1 a
inner join @Dataset2 b on a.[AID] = b.s_id
inner join @Dataset3 c on a.[AID] = c.s_id
select
      [AID],
      [AName],
	avg(convert(int,[Qtr1 2007])) as [Qtr1 2007],
        avg(convert(int,[Qtr2 2007])) as [Qtr2 2007],
      avg(convert(int,[Qtr3 2007])) as [Qtr3 2007],
      avg(convert(int,[Qtr4 2007])) as [Qtr4 2007],
        avg(convert(int,[Qtr1 2008])) as [Qtr1 2008],
        avg(convert(int,[Qtr2 2008])) as [Qtr2 2008],
      avg(convert(int,[Qtr3 2008])) as [Qtr3 2008],
      avg(convert(int,[Qtr4 2008])) as [Qtr4 2008],
      avg(convert(int,[Qtr1 2009])) as [Qtr1 2009],
      avg(convert(int,[Qtr2 2009])) as [Qtr2 2009],
      avg(convert(int,[Qtr3 2009])) as [Qtr3 2009],
      avg(convert(int,[Qtr4 2009])) as [Qtr9 2009],
      avg(P2008) as P2007,
      avg(C2008) as C2007,
      avg(P2008) as P2008,
      avg(C2008) as C2008,
      avg(P2009) as P2009,
      avg(C2009) as C2009,
      avg(convert(int, PR2007)) as PR2007,
        avg(convert(int, CR2007)) as CR2007,
      avg(convert(int, PR2008)) as PR2008,
        avg(convert(int, CR2008)) as CR2008,
      avg(convert(int, PR2009))as PR2009 ,
        avg(convert(int, CR2009)) as CR2009
from @GridDataSet
group by [AID], [AName]
order by [AID]

推荐答案

您应该通过SQL事件探查器运行此命令以查看其作用.在相关列上有索引吗?它在做表扫描吗?老实说，我怀疑您问题的至少一部分在于您使用计算来控制查询，从而在其中执行查询，例如，所有类似下面的行:
You should run this through a SQL Profiler to see what it''s up to. Do you have any indexes on the relevant columns? Is it doing table scans? To be honest, I suspect that at least part of your problem lies in you performing queries in there using calculations to control the queries, e.g all those lines like this:
case when ((avg(t1.P2007)>= SC.start_range) and (avg(t1.P2007)< SC.end_range))


Quaterly rat，

您没有提供有关查询表上索引的任何信息.

Quaterly rat,

You didn''t provide any information about the indexes on the tables of the query. 

from table1 t1,
table2 SC,        
table3 HC,           
table2 SP,        
table3 HP



我猜从表1到表3在它们的键上没有任何索引.同样奇怪的是，您使用不同的别名再次连接了table2和table3.

尝试仅添加这3个索引，看看结果如何.

HP.hdr_id作为非群集"索引
SP.hdr_id作为非群集"索引
t1.quarter作为聚集"索引

您可能还需要索引正在创建的数据集...

如果使用的是SQL Server，则将查询放在窗口中，然后按CTRL + L以获取查询的执行计划.在没有任何索引类型的情况下将两个表联接在一起的任何地方，都可以建立索引并提高性能.

很多时候，反复试验将帮助您找到最佳答案，但是从表上的索引开始！

霍根



I''m guessing that table1 through table3 don''t have any indexes on their keys. It is also strange that you are joining table2 and table3 a second time with a different alias.

Try just adding these 3 indexes and see how it turns out for you.

HP.hdr_id as a "Non Clustered" index
SP.hdr_id as a "Non Clustered" index
t1.quarter as a "Clustered" index

You''ll probably need to index your data sets that you are creating also...

If you are using SQL server, put your query in the window and press CTRL + L to get the execution plan of the query. Anywhere two tables are joined without some type of index is a place you could index and improve performance.

Many times trial and error will help you find the best answer, but start with your indexes on the tables!

Hogan


这篇关于SQL查询花费太多时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发语言最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆