Slow Query

Question

Post reply

Slow Query

SSM-140546

Old Hand

Points: 365
More actions
July 11, 2005 at 1:37 pm

#109933

Hi,
My Query is very slow.How can I improve the speed.this is the query for a view and many sp's use this view.
SELECT     TOP 100 PERCENT cycleName, framework, toolName, taskName, DATEPART(ww, execDate) AS WW, execDate, execHour,
                      SUM(CAST(StressTime AS decimal) / 3600) AS MachineHours, targetName
FROM         dbo.tbl_execution_time_hourly
WHERE     (cycleName IN
                          (SELECT     [tbl_Cycle].[cycleName]
                            FROM          [tbl_Cycle]
                            WHERE      [tbl_Cycle].[deleted] = 0 AND [tbl_Cycle].[dateClosed] IS NULL))
GROUP BY cycleName, framework, toolName, targetName, taskName, DATEPART(ww, execDate), execDate, execHour
ORDER BY cycleName, framework, toolName, targetName, taskName, DATEPART(ww, execDate), execDate, execHour
thanks,
Sudha
thanx...,
SSM

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

change this a derived table an use it in a inner join :

(SELECT [tbl_Cycle].[cycleName]

FROM [tbl_Cycle]

WHERE [tbl_Cycle].[deleted] = 0 AND [tbl_Cycle].[dateClosed] IS NULL)

Could you post the execution plan so that we may spot missing indexes?

SSM-140546 Old Hand Points: 365 More actions · Answer 2

hi,

I am not able to save or copy execution plan.how should I do it?

thanks,

Sudha

thanx...,

SSM

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

SET SHOWPLAN_TEXT ON

GO

Select * from dbo.SysObjects

GO

SET SHOWPLAN_TEXT OFF

SSM-140546 Old Hand Points: 365 More actions · Answer 4

Hi,

|--Compute Scalar(DEFINE[Expr1004]=Convert(Convert([ET].[StressTime])/3600)))

|--Sort(ORDER BY[ET].[cycleName] ASC, [ET].[framework] ASC, [ET].[toolName] ASC, [ET].[targetName] ASC, [ET].[taskName] ASC, [Expr1003] ASC, [ET].[execDate] ASC, [ET].[execHour] ASC))

|--Compute Scalar(DEFINE[Expr1003]=datepart(week, Convert([ET].[execDate]))))

|--Nested Loops(Inner Join, OUTER REFERENCES[tbl_cycle].[cycleName]))

|--Clustered Index Scan(OBJECT[Pandu].[dbo].[tbl_cycle].[PK__tbl_cycle__59063A47]), WHERE[tbl_cycle].[dateClosed]=NULL AND Convert([tbl_cycle].[deleted])=0))

|--Clustered Index Seek(OBJECT[Pandu].[dbo].[tbl_execution_time_hourly].[PK_tbl_execution_time_hourly] AS [ET]), SEEK[ET].[cycleName]=[tbl_cycle].[cycleName]) ORDERED FORWARD)

thanks,

Sudha

thanx...,

SSM

SSM-140546 Old Hand Points: 365 More actions · Answer 5

execHour

SUM(CAST(StressTime AS decimal) / 3600) AS MachineHours

targetName FROM dbo.tbl_execution_time_hour

4

1

0

NULL

1

NULL

7581.8608

NULL

0.46791801

NULL

SELECT

0

NULL

[Expr1004]=Convert(Convert([ET].[StressTime])/3600)

7581.861

0

7.58E-04

113

0.46791801

[ET].[cycleName]

[ET].[framework]

[ET].[toolName]

[ET].[targetName]

[ET].[taskName]

[ET].[execDate]

[ET].[execHour]

[Expr1003]

[Expr1004]

NULL

PLAN_ROW

0

1

[ET].[execHour] ASC))

4

3

2

Sort

ORDER BY

[ET].[cycleName] ASC

[ET].[framework] ASC

[ET].[toolName] ASC

[ET].[targetName] ASC

[ET].[taskName] ASC

[Expr1003] ASC

[ET].[execDate] ASC

[ET].[execHour] ASC)

NULL

7581.8608

1.13E-02

0.30175713

104

0.46715984

[ET].[StressTime]

[ET].[cycleName]

[ET].[framework]

[ET].[toolName]

[ET].[targetName]

[ET].[taskName]

[ET].[execDate]

[ET].[execHour]

[Expr1003]

NULL

PLAN_ROW

0

1

DEFINE

[Expr1003]=datepart(week

Convert([ET].[execDate])))

[Expr1003]=datepart(week

Convert([ET].[execDate]))

7581.8608

0

7.58E-04

104

0.15414143

[ET].[StressTime]

[ET].[cycleName]

[ET].[framework]

[ET].[toolName]

[ET].[targetName]

[ET].[taskName]

[ET].[execDate]

[ET].[execHour]

[Expr1003]

NULL

PLAN_ROW

0

1

OUTER REFERENCES

[tbl_cycle].[cycleName])

NULL

7581.861

0

3.44E-02

658

0.15338324

[ET].[StressTime]

[ET].[cycleName]

[ET].[framework]

[ET].[toolName]

[ET].[targetName]

[ET].[taskName]

[ET].[execDate]

[ET].[execHour]

NULL

PLAN_ROW

0

1

OBJECT

[Pandu].[dbo].[tbl_cycle].[PK__tbl_cycle__59063A47])

WHERE

[tbl_cycle].[dateClosed]=NULL AND Convert([tbl_cycle].[deleted])=0)

[tbl_cycle].[cycleName]

[tbl_cycle].[dateClosed]

[tbl_cycle].[deleted]

2.764977

3.98E-02

0.0003172

321

4.01E-02

[tbl_cycle].[cycleName]

[tbl_cycle].[dateClosed]

[tbl_cycle].[deleted]

NULL

PLAN_ROW

0

1

OBJECT

[Pandu].[dbo].[tbl_execution_time_hourly].[PK_tbl_execution_time_hourly] AS [ET])

SEEK

[ET].[cycleName]=[tbl_cycle].[cycleName]) ORDERED FORWARD

[ET].[StressTime]

[ET].[cycleName]

[ET].[framework]

[ET].[toolName]

[ET].[targetName]

[ET].[taskName]

[ET].[execDate]

[ET].[execHour]

2742.1062

6.69E-02

3.10E-03

345

0.07866665

[ET].[StressTime]

[ET].[cycleName]

[ET].[framework]

[ET].[toolName]

[ET].[targetName]

[ET].[taskName]

[ET].[execDate]

[ET].[execHour]

NULL

PLAN_ROW

0

3

thanx...,

SSM

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

IS there an index on the columns dateClosed and deleted?

What datatype is the deleted column?

SSM-140546 Old Hand Points: 365 More actions · Answer 7

hi ,

no, there is no index on the dateClosed & deleted,deleted is bit.

primary key is cycleName in tbl_cycle

on tbl_execution_time hourly primary key is

[cycleName],

[framework],

[toolName],

[taskName],

[targetName],

[execDate],

[execHour]

Thanks a lot,

sudha

thanx...,

SSM

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

Well at this point I think the only simple change that could help you would be to add an index on the dateClosed column. The deleted flag will always force a scan. You could always change that column to a tinyint and do a covering index on those 2 columns to see if it helps.

SSM-140546 Old Hand Points: 365 More actions · Answer 9

dateClosed Column can be Null, is that OK?

thanx...,

SSM

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

Yes... it makes sens that this value will be unknown for a part of the lifetime of the record.

Leo Leong SSCommitted Points: 1615 More actions · Answer 11

what if we change from IN to EXISTS?

SELECT cycleName, framework, toolName, taskName, DATEPART(ww, execDate) AS WW, execDate, execHour,

SUM(CAST(StressTime AS decimal) / 3600) AS MachineHours, targetName

FROM dbo.tbl_execution_time_hourly ETH

WHERE EXISTS

(SELECT 1

FROM [tbl_Cycle] C

WHERE [tbl_Cycle].[deleted] = 0 AND [tbl_Cycle].[dateClosed] IS NULL AND ETH.cycleName = C.cycleName )

GROUP BY cycleName, framework, toolName, targetName, taskName, DATEPART(ww, execDate), execDate, execHour

ORDER BY cycleName, framework, toolName, targetName, taskName, DATEPART(ww, execDate), execDate, execHour

Just a suggestion. You can try.

Leo

vinod-228002 SSChasing Mays Points: 608 More actions · Answer 12

H!!

Create composite non clustered index on the columns in which you try to execute the select statement ..

vinod (DBA)

HTC Global Services

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

I already said that, but he'll have to change the bot column to a tinyint to be able to index it.

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 14

Bit columns can be indexed. They're not very selective, but they can be useful in compound covering indexes.

It might be true that the Convert([deleted]) = 0 expression in the execution plan makes it less efficient than the straight [deleted] = 0 condition you would get for a tinyint field, but it will be an index seek operation instead of a scan.

An index on tbl_Cycle (dateClosed, deleted, cycleName) should speed up this query, possibly in addition to the other suggested rewrites.

Slow Query

Cookies on SQLServerCentral