Post reply

Slow Query

  • 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

  • July 11, 2005 at 1:40 pm

    #573104

    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?

  • July 11, 2005 at 2:12 pm

    #573110

    hi,

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

    thanks,

    Sudha

    thanx...,

    SSM

  • July 11, 2005 at 2:14 pm

    #573111

    SET SHOWPLAN_TEXT ON

    GO

    Select * from dbo.SysObjects

    GO

    SET SHOWPLAN_TEXT OFF

  • July 11, 2005 at 2:18 pm

    #573112

    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

  • July 11, 2005 at 2:28 pm

    #573123

     execHour                          SUM(CAST(StressTime AS decimal) / 3600) AS MachineHours  targetName  FROM         dbo.tbl_execution_time_hour 4 1 0 NULL NULL 1 NULL 7581.8608 NULL NULL NULL 0.46791801 NULL 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 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

  • July 11, 2005 at 2:39 pm

    #573129

    IS there an index on the columns dateClosed and deleted?

    What datatype is the deleted column?

  • July 11, 2005 at 2:45 pm

    #573133

    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

  • July 11, 2005 at 2:55 pm

    #573140

    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.

  • July 11, 2005 at 3:08 pm

    #573147

    dateClosed Column can be Null, is that OK?

    thanx...,

    SSM

  • July 11, 2005 at 3:10 pm

    #573148

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

  • July 12, 2005 at 1:36 am

    #573247

    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

     

  • July 12, 2005 at 1:42 am

    #573249

    H!!

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

    vinod (DBA)

    HTC Global Services

     

  • July 12, 2005 at 6:19 am

    #573304

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

  • July 12, 2005 at 7:56 pm

    #573748

    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.

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

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