September 15, 2010 at 7:04 pm
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)
September 15, 2010 at 7:48 pm
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
Change is inevitable... Change for the better is not.
September 15, 2010 at 9:22 pm
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.
September 16, 2010 at 12:16 am
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
September 16, 2010 at 12:48 am
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)
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
September 16, 2010 at 1:28 am
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
September 16, 2010 at 4:52 am
Does this work?
SELECT *
FROM X
WHERE HistoryID = MAX(HistoryID) OVER (PARTITION BY [ObjID])
September 16, 2010 at 5:38 am
why not use a temp table with an index instead of using a subquery
September 16, 2010 at 5:44 am
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
Change is inevitable... Change for the better is not.
September 16, 2010 at 11:46 am
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...
September 16, 2010 at 11:18 pm
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
September 16, 2010 at 11:28 pm
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
September 17, 2010 at 1:25 am
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
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 17, 2010 at 1:29 am
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 White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 17, 2010 at 7:45 am
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