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_hour410NULLNULL1NULL7581.8608NULLNULLNULL0.46791801NULLNULLSELECT0NULL             
    [Expr1004]=Convert(Convert([ET].[StressTime])/3600)7581.86107.58E-041130.46791801[ET].[cycleName] [ET].[framework] [ET].[toolName] [ET].[targetName] [ET].[taskName] [ET].[execDate] [ET].[execHour] [Expr1003] [Expr1004]NULLPLAN_ROW01              
     [ET].[execHour] ASC))432SortSortORDER BY[ET].[cycleName] ASC [ET].[framework] ASC [ET].[toolName] ASC [ET].[targetName] ASC [ET].[taskName] ASC [Expr1003] ASC [ET].[execDate] ASC [ET].[execHour] ASC)NULL7581.86081.13E-020.301757131040.46715984[ET].[StressTime] [ET].[cycleName] [ET].[framework] [ET].[toolName] [ET].[targetName] [ET].[taskName] [ET].[execDate] [ET].[execHour] [Expr1003]NULLPLAN_ROW01
    DEFINE[Expr1003]=datepart(week Convert([ET].[execDate])))[Expr1003]=datepart(week Convert([ET].[execDate]))7581.860807.58E-041040.15414143[ET].[StressTime] [ET].[cycleName] [ET].[framework] [ET].[toolName] [ET].[targetName] [ET].[taskName] [ET].[execDate] [ET].[execHour] [Expr1003]NULLPLAN_ROW01           
    OUTER REFERENCES[tbl_cycle].[cycleName])NULL7581.86103.44E-026580.15338324[ET].[StressTime] [ET].[cycleName] [ET].[framework] [ET].[toolName] [ET].[targetName] [ET].[taskName] [ET].[execDate] [ET].[execHour]NULLPLAN_ROW01              
    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.7649773.98E-020.00031723214.01E-02[tbl_cycle].[cycleName] [tbl_cycle].[dateClosed] [tbl_cycle].[deleted]NULLPLAN_ROW01                
    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.10626.69E-023.10E-033450.07866665[ET].[StressTime] [ET].[cycleName] [ET].[framework] [ET].[toolName] [ET].[targetName] [ET].[taskName] [ET].[execDate] [ET].[execHour]NULLPLAN_ROW03      
    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