SQL query is taking too much time

Question

4.00/5 (1 vote)

See more:

HI, 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]

Posted 9-Apr-10 10:57am

Care Career

Updated 9-Apr-10 11:20am

Pete O'Hanlon

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Pete O'Hanlon · Answer 1 · 2010-04-09T11:25:00

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:

SQL

case when ((avg(t1.P2007)>= SC.start_range) and (avg(t1.P2007)< SC.end_range))

snorkie · Answer 2 · 2010-04-09T11:33:00

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

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 query is taking too much time

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0