simple SELECT query takes ten seconds to return 5000 rows - can I optimize?

  • I have a table and index:

    CREATE TABLE X (

    HistoryID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,

    ObjID INT NOT NULL,

    Date1 DATETIME2,

    Date2 DATETIME2,

    Info1 CHAR(400),

    Info2 CHAR(400),

    Int1 INT,

    Int2 INT,

    Int3 INT

    )

    CREATE INDEX X_IX1 ON X (ObjID)

    The table has about 4 million rows and is 854MB in size.

    The ObjID value repeats and there are about 5000 unique ObjIDs in the table.

    When I run this query:

    DBCC DROPCLEANBUFFERS

    SELECT

    X.*

    FROM X

    INNER JOIN (

    SELECT

    ObjID,

    MAX(HistoryID) AS MaxID

    FROM X

    GROUP BY ObjID

    ) X2

    ON X.HistoryID = X2.MaxID

    It takes about 10 seconds to return 5000 records which seems like forever considering it's a local database on my laptop (Intel Core Duo - 4GB RAM).

    My questions:

    1) Is 10 seconds a reasonable amount of time for this query to run?

    2) Is there anything I can do to speed it up?

    I've included the query plan as well:

    |--Nested Loops(Inner Join, OUTER REFERENCES ([Expr1006]))

    |--Stream Aggregate(GROUP BY ([LoadTest].[dbo].[X].[OBJ_ID]) DEFINE ([Expr1006]=MAX

    ([LoadTest].[dbo].[X].[ID])))

    | |--Index Scan(OBJECT ([LoadTest].[dbo].[X].[X_IX1]), ORDERED FORWARD)

    |--Clustered Index Seek(OBJECT ([LoadTest].[dbo].[X].[PK__X__3214EC273F466844]),

    SEEK ([LoadTest].[dbo].[X].[ID]=[Expr1006]) ORDERED FORWARD)

  • I know this might seem stupid to even try but it made the code run twice as fast on my machine...

    SELECT

    X.*

    FROM X

    INNER JOIN (

    SELECT

    --ObjID, --<<<<COMMENT THIS LINE OUT!!!

    MAX(HistoryID) AS MaxID

    FROM X

    GROUP BY ObjID

    ) X2

    ON X.HistoryID = X2.MaxID

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks for looking at this. I tried removing the extra column but didn't see any substantial difference in the elapsed time of the query.

    Although I did notice that upon removing the column, SET STATISTICS IO shows 31933 logical reads as opposed to 32095.

  • SELECT X.*

    FROM X

    WHERE HistoryId In (

    SELECT MAX(HistoryID)

    FROM X

    GROUP BY ObjID)

    I don't know but may be it works. Did you try this... or even this can be tried...

    SELECT X.* FROM

    (SELECT MAX(HistoryID) AS MaxID

    FROM X GROUP BY ObjID) AS X1

    INNER JOIN X ON X.HistoryID = X1.MaxID

  • 8kb (9/15/2010)


    CREATE INDEX X_IX1 ON X (ObjID)

    Note, non-covering, non-clustered index, so we're going here, to the PrimaryKey:

    |--Index Scan(OBJECT ([LoadTest].[dbo].[X].[X_IX1]), ORDERED FORWARD)

    |--Clustered Index Seek(OBJECT ([LoadTest].[dbo].[X].[PK__X__3214EC273F466844]),

    Add HistoryID to the index to use the index directly instead of the second lookup on the clustered index.

    Next this:

    The table has about 4 million rows and is 854MB in size.

    (and from later...)

    It takes about 10 seconds to return 5000 records which seems like forever considering it's a local database on my laptop (Intel Core Duo - 4GB RAM).

    Now, in theory, you could load the entire DB to RAM, I know, but that's going to depend on how much room his local server is allowed overall. Also, if the db is local to the laptop, it's sitting on a single spindle IDE drive. You simply will NEVER get high speed performance from this. I'm kinda surprised it went that fast, actually. I'd have assumed it was holding a lot of data in cache still if it wasn't for the DROPCLEANBUFFERS you've ran.

    1) Is 10 seconds a reasonable amount of time for this query to run?

    2) Is there anything I can do to speed it up?

    1) In the local environment, in my opinion, for 4 mill rows that're wide... yeah, that's reasonable.

    2) Get it on the real server. 🙂 Also, try altering your index to be a covering index, should recover some time that way. Just make it ( ObjID, HistoryID)


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Can you post the full execution plan as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Does this work?

    SELECT *

    FROM X

    WHERE HistoryID = MAX(HistoryID) OVER (PARTITION BY [ObjID])

  • why not use a temp table with an index instead of using a subquery

  • Craig Farrell (9/16/2010)


    8kb (9/15/2010)


    CREATE INDEX X_IX1 ON X (ObjID)

    Note, non-covering, non-clustered index, so we're going here, to the PrimaryKey:

    |--Index Scan(OBJECT ([LoadTest].[dbo].[X].[X_IX1]), ORDERED FORWARD)

    |--Clustered Index Seek(OBJECT ([LoadTest].[dbo].[X].[PK__X__3214EC273F466844]),

    Add HistoryID to the index to use the index directly instead of the second lookup on the clustered index.

    That's probably the best advice so far and would also be what I'd try next.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks everyone for your help. To create a baseline, I've attached a script to load sample data (takes about 2 minutes if you're interested). I also attached the sqlplan for the original query.

    The new table is larger than the original, so now the query takes about 30 seconds instead of 10.

    I tried everyone's suggestions but noticed no substantial improvements:

    Original (8kb): 30493 ms

    Remove ObjID from subquery (J. Moden): 29384 ms

    IN clause (sanmatrix): 29970 ms

    Covering index (C. Farrell): 28879 ms

    Temp table (thetodaisis): 29504 ms

    I haven't yet run it on the server. The only other thing I can think of is partitioning the table...

  • On my laptop, I'm getting subsecond response time using data generated by your script and no additional indexing. But I'm testing by writing to a temp table. When I let it display all the rows, it runs in 10-11 seconds. Thanks to Mr. Moden for teaching me not to confuse the time to produce a result set with the time it takes the screen to scroll.

    declare @timer datetime = getdate()

    ;with cte as ( select ObjID, max(HistoryID) as MaxID

    from dbo.X

    group by OBJID

    )

    select X.*

    into #temp

    from dbo.X

    join cte c on c.objID = x.objID

    and c.maxID = x.HistoryID

    select cast(@@ROWCOUNT as varchar) as rows, DATEDIFF(MS,@timer,GETDATE()) as milliseconds

    drop table #temp

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • In the example below, a CROSS APPLY technique manages to run a hair faster with an execution plan that goes directly from an index scan into a clustered index seek, instead of index scan / NC-index seek / keylookup.

    declare @timer datetime = getdate()

    ;with cte as ( select ObjID, max(HistoryID) as MaxID

    from dbo.X

    group by OBJID

    )

    select X.*

    into #temp1

    from dbo.X

    join cte c on c.objID = x.objID

    and c.maxID = x.HistoryID

    select cast(@@ROWCOUNT as varchar) as rows, DATEDIFF(MS,@timer,GETDATE()) as milliseconds

    drop table #temp1

    set @timer = GETDATE()

    ;with cte as ( select ObjID, max(HistoryID) as MaxID

    from dbo.X

    group by OBJID

    )

    select ca.*

    into #temp2

    from cte c

    cross apply (select * from dbo.x where objID = x.objID

    and c.maxID = x.HistoryID) ca

    select cast(@@ROWCOUNT as varchar) as rows, DATEDIFF(MS,@timer,GETDATE()) as milliseconds

    drop table #temp2

    Logical reads dropped from 36091 (JOIN) to 20770 (Cross Apply).

    I tried Jeff's suggestion of dropping the ObjID from the JOIN and seemed to save another 10 or 12 milliseconds out of 800+. Without the ObjID column, the JOIN ran faster than the CROSS APPLY without ObjID. The execution plans appeared the same as before. In both cases there were 20770 logical reads.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • The most efficient execution avoids any joins:

    That's a single ordered scan of an index. One way to achieve that plan is to change the clustered index:

    CREATE TABLE X

    (

    HistoryID INT NOT NULL IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,

    ObjID INT NOT NULL,

    Date1 DATETIME2,

    Date2 DATETIME2,

    Info1 CHAR(75),

    Info2 CHAR(200),

    Int1 INT,

    Int2 INT,

    Int3 INT

    )

    GO

    CREATE UNIQUE CLUSTERED INDEX cuq ON X (ObjID, HistoryID)

    GO

    Unfortunately, cost estimation overstates the cost of the plan above. I get the optimal plan by using a query hint:

    SELECT X1.*

    FROM dbo.X X1

    WHERE X1.HistoryID =

    (

    SELECT MAX(X2.HistoryID)

    FROM dbo.X X2

    WHERE X2.ObjID = X1.ObjID

    )

    OPTION (FAST 100);

    The more robust alternative is to force the plan shape:

    SELECT X1.*

    FROM dbo.X X1

    WHERE X1.HistoryID =

    (

    SELECT MAX(X2.HistoryID)

    FROM dbo.X X2

    WHERE X2.ObjID = X1.ObjID

    )

    OPTION (

    USE PLAN

    N'<?xml version="1.0" encoding="utf-16"?>

    <ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.1" Build="10.0.2789.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">

    <BatchSequence>

    <Batch>

    <Statements>

    <StmtSimple StatementCompId="1" StatementEstRows="3950.43" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="1.23612" StatementText="SELECT X1.* FROM dbo.X X1 WHERE X1.HistoryID = ( SELECT MAX(X2.HistoryID) FROM dbo.X X2 WHERE X2.ObjID = X1.ObjID ) " StatementType="SELECT" QueryHash="0x6EA71E2F95338845" QueryPlanHash="0x93D3C5FB53F91114">

    <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />

    <QueryPlan CachedPlanSize="16" CompileTime="6" CompileCPU="6" CompileMemory="248">

    <RelOp AvgRowSize="319" EstimateCPU="0.000879009" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="3950.43" LogicalOp="Top" NodeId="1" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="1.23612">

    <OutputList>

    <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[X]" Alias="[X1]" Column="HistoryID" />

    <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[X]" Alias="[X1]" Column="ObjID" />

    <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[X]" Alias="[X1]" Column="Date1" />

    <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[X]" Alias="[X1]" Column="Date2" />

    <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[X]" Alias="[X1]" Column="Info1" />

    <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[X]" Alias="[X1]" Column="Info2" />

    <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[X]" Alias="[X1]" Column="Int1" />

    <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[X]" Alias="[X1]" Column="Int2" />

    <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[X]" Alias="[X1]" Column="Int3" />

    </OutputList>

    <Top RowCount="false" Rows="1" IsPercent="false" WithTies="true">

    <TieColumns>

    <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[X]" Alias="[X1]" Column="ObjID" />

    <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[X]" Alias="[X1]" Column="HistoryID" />

    </TieColumns>

    <RelOp AvgRowSize="319" EstimateCPU="0.00351603" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="40000" LogicalOp="Segment" NodeId="2" Parallel="false" PhysicalOp="Segment" EstimatedTotalSubtreeCost="1.23524">

    <OutputList>

    <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[X]" Alias="[X1]" Column="HistoryID" />

    <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[X]" Alias="[X1]" Column="ObjID" />

    <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[X]" Alias="[X1]" Column="Date1" />

    <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[X]" Alias="[X1]" Column="Date2" />

    <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[X]" Alias="[X1]" Column="Info1" />

    <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[X]" Alias="[X1]" Column="Info2" />

    <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[X]" Alias="[X1]" Column="Int1" />

    <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[X]" Alias="[X1]" Column="Int2" />

    <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[X]" Alias="[X1]" Column="Int3" />

    <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[X]" Alias="[X1]" Column="ObjID" />

    <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[X]" Alias="[X1]" Column="HistoryID" />

    <ColumnReference Column="Segment1007" />

    </OutputList>

    <Segment>

    <GroupBy>

    <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[X]" Alias="[X1]" Column="ObjID" />

    </GroupBy>

    <SegmentColumn>

    <ColumnReference Column="Segment1007" />

    </SegmentColumn>

    <RelOp AvgRowSize="319" EstimateCPU="0.044157" EstimateIO="1.18757" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="40000" LogicalOp="Clustered Index Scan" NodeId="3" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="1.23173" TableCardinality="40000">

    <OutputList>

    <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[X]" Alias="[X1]" Column="HistoryID" />

    <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[X]" Alias="[X1]" Column="ObjID" />

    <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[X]" Alias="[X1]" Column="Date1" />

    <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[X]" Alias="[X1]" Column="Date2" />

    <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[X]" Alias="[X1]" Column="Info1" />

    <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[X]" Alias="[X1]" Column="Info2" />

    <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[X]" Alias="[X1]" Column="Int1" />

    <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[X]" Alias="[X1]" Column="Int2" />

    <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[X]" Alias="[X1]" Column="Int3" />

    </OutputList>

    <IndexScan Ordered="true" ScanDirection="BACKWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">

    <DefinedValues>

    <DefinedValue>

    <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[X]" Alias="[X1]" Column="HistoryID" />

    </DefinedValue>

    <DefinedValue>

    <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[X]" Alias="[X1]" Column="ObjID" />

    </DefinedValue>

    <DefinedValue>

    <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[X]" Alias="[X1]" Column="Date1" />

    </DefinedValue>

    <DefinedValue>

    <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[X]" Alias="[X1]" Column="Date2" />

    </DefinedValue>

    <DefinedValue>

    <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[X]" Alias="[X1]" Column="Info1" />

    </DefinedValue>

    <DefinedValue>

    <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[X]" Alias="[X1]" Column="Info2" />

    </DefinedValue>

    <DefinedValue>

    <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[X]" Alias="[X1]" Column="Int1" />

    </DefinedValue>

    <DefinedValue>

    <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[X]" Alias="[X1]" Column="Int2" />

    </DefinedValue>

    <DefinedValue>

    <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[X]" Alias="[X1]" Column="Int3" />

    </DefinedValue>

    </DefinedValues>

    <Object Database="[Sandpit]" Schema="[dbo]" Table="[X]" Index="[cuq]" Alias="[X1]" TableReferenceId="-1" IndexKind="Clustered" />

    </IndexScan>

    </RelOp>

    </Segment>

    </RelOp>

    </Top>

    </RelOp>

    </QueryPlan>

    </StmtSimple>

    </Statements>

    </Batch>

    </BatchSequence>

    </ShowPlanXML>')

    Because the plan uses an ordered scan of the index, fragmentation must be low for great performance. The logical reads are quite high with this method, but the ordered scan results in sequential I/O which can make maximum use of read-ahead.

    Paul

  • For further details about that query plan, see:

    http://sqlblog.com/blogs/paul_white/archive/2010/07/28/the-segment-top-query-optimisation.aspx

    That entry also covers other possibilities including solutions using ranking functions, for example:

    SELECT Ranked.HistoryID,

    Ranked.ObjID,

    Ranked.Date1,

    Ranked.Date2,

    Ranked.Info1,

    Ranked.Info2,

    Ranked.Int1,

    Ranked.Int2,

    Ranked.Int3

    FROM (

    SELECT *,

    rn = ROW_NUMBER() OVER (PARTITION BY X.ObjID ORDER BY X.HistoryID DESC)

    FROM dbo.X

    ) Ranked

    WHERE Ranked.rn = 1;

  • Paul: I wasn't aware this was a Speed Phreak challenge. 😀

    8kb: If you're willing to change the clustered index structure to the one proposed by Paul, you might consider this, without resorting to forcing a plan.

    ;with cte as( select distinct objid

    from dbo.X

    )

    select ca.*

    --into #temp

    from cte

    cross apply (select top 1 *

    from dbo.X

    where objid = cte.objid

    order by HistoryID desc) ca

    When I compared this to the forced plan I saw the following

    ================

    Forced Plan

    ================

    (5000 row(s) affected)

    Table 'X'. Scan count 1, logical reads 160339, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (1 row(s) affected)

    SQL Server Execution Times:

    CPU time = 1747 ms, elapsed time = 4796 ms.

    ================

    Cross Apply

    ================

    (5000 row(s) affected)

    Table 'X'. Scan count 5001, logical reads 21412, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (1 row(s) affected)

    SQL Server Execution Times:

    CPU time = 499 ms, elapsed time = 4802 ms.

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 1 ms.

    As you can see, the number of logical reads and the CPU time are significantly lower, although the elapsed time is just a few milliseconds higher. I would have liked to test it against Paul's plan without displaying the results but when I tried to store the results in a temporary table, I got the following message.

    Msg 8698, Level 16, State 0, Line 15

    Query processor could not produce query plan because USE PLAN hint contains plan that could not be verified to be legal for query. Remove or replace USE PLAN hint. For best likelihood of successful plan forcing, verify that the plan provided in the USE PLAN hint is one generated automatically by SQL Server for the same query.

    One of the reasons I shun forced execution plans is they are inflexible in many respects. Perhaps Paul would be good enough to modify his plan and post up results comparing both techniques populating temp tables, or table variables.

    If the performance of this particular query is critical enough to warrant changing the clustered index and using a forced plan, you might consider this alternative:

    Create a separate table 'Y' with an identical column to table X, but with a unique clustered index over [ObjID] only as the primary key. Write a trigger to keep it updated it with all values from the row with the max historyID for each objectID. Then you can simply query that table to get a simple clustered index scan that contains only the minimum possible number of rows. Fragmentation should even be lower than in table X because objIDs are presumably assigned sequentially like historyIDs. Hard to get much faster than that.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

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

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