JtheRipper
IS-IT--Management
Hi there,
Apologies if this looks a bit long-winded, but I believe it is an interesting issue if someone can help!
I was wondering if someone can please explain to me how SQL Server works out the following execution plan. The query does not execute slow, as a matter of fact it is very fast... I would just like to know how it gets to the plan:
View:
Table example:
Select:
Explain plan:
From the explain plan it looks like the optimizer "knows" in what range the pslpokerhandid is without really going through the partitionkey. I am asking this because in Oracle we have a similar setup and the query runs for more than 3 minutes (5 seconds in SQL Server) with the same amount of data, data structures etc.
In Oracle you can clearly see how the optimizer needs to get the range first (from the where clause) and then do a full scan of the index on the pslpokerhandid field to get the min and max values.
Oracle plan:
Many thanks,
Jaco
Apologies if this looks a bit long-winded, but I believe it is an interesting issue if someone can help!
I was wondering if someone can please explain to me how SQL Server works out the following execution plan. The query does not execute slow, as a matter of fact it is very fast... I would just like to know how it gets to the plan:
View:
Code:
CREATE VIEW vw_PSLPokerHand
WITH ENCRYPTION AS
SELECT PartitionKey, CasinoID, PSLPokerHandID, PSLPokerTableID, DateStarted, DateCompleted, IsConsolidated, CHSynchroStatus, PSLPokerMultiTournamentID, PSLPokerSingleTournamentID, TotalRake From dbo.tb_PSLPokerHand_112005
UNION ALL
SELECT PartitionKey, CasinoID, PSLPokerHandID, PSLPokerTableID, DateStarted, DateCompleted, IsConsolidated, CHSynchroStatus, PSLPokerMultiTournamentID, PSLPokerSingleTournamentID, TotalRake From dbo.tb_PSLPokerHand_122005
UNION ALL
SELECT PartitionKey, CasinoID, PSLPokerHandID, PSLPokerTableID, DateStarted, DateCompleted, IsConsolidated, CHSynchroStatus, PSLPokerMultiTournamentID, PSLPokerSingleTournamentID, TotalRake From dbo.tb_PSLPokerHand_012006
UNION ALL
SELECT PartitionKey, CasinoID, PSLPokerHandID, PSLPokerTableID, DateStarted, DateCompleted, IsConsolidated, CHSynchroStatus, PSLPokerMultiTournamentID, PSLPokerSingleTournamentID, TotalRake From dbo.tb_PSLPokerHand_022006
GOTable example:
Code:
--CREATE TABLE [dbo].[tb_PSLPokerHand_082005] (
-- [PartitionKey] [int] NOT NULL ,
-- [CasinoID] [int] NOT NULL ,
-- [PSLPokerHandID] [bigint] NOT NULL ,
-- [PSLPokerTableID] [int] NOT NULL ,
-- [DateStarted] [datetime] NOT NULL ,
-- [DateCompleted] [datetime] NOT NULL ,
-- [IsConsolidated] [tinyint] NOT NULL ,
-- [CHSynchroStatus] [tinyint] NULL ,
-- [PSLPokerMultiTournamentID] [int] NULL ,
-- [PSLPokerSingleTournamentID] [int] NULL ,
-- [TotalRake] [money] NULL ,
-- CONSTRAINT [pk_PSLPokerHand_082005] PRIMARY KEY CLUSTERED
-- (
-- [PartitionKey],
-- [PSLPokerHandID]
-- ) ON [PRIMARY] ,
-- CONSTRAINT [ck_PSLPokerHand_082005_PartitionKey] CHECK ([PartitionKey] >= 2005213 and [PartitionKey] <= 2005243)
--) ON [PRIMARY]
--GO
--
-- CREATE INDEX [ix_PSLPokerHand_082005_DateCompleted] ON [dbo].[tb_PSLPokerHand_082005]([DateCompleted]) ON [PRIMARY]
--GO
--
-- CREATE INDEX [ix_PSLPokerHand_082005_PSLPokerHandID] ON [dbo].[tb_PSLPokerHand_082005]([PSLPokerHandID]) ON [PRIMARY]
--GO
--
-- CREATE INDEX [ix_PSLPokerHand_01_PSLPokerTableID] ON [dbo].[tb_PSLPokerHand_082005]([PSLPokerTableID]) ON [PRIMARY]
--GO
--
--/****** The index created by the following statement is for internal use only. ******/
--/****** It is not a real index but exists as statistics only. ******/
--if (@@microsoftversion > 0x07000000 )
--EXEC ('CREATE STATISTICS [Statistic_IsConsolidated] ON [dbo].[tb_PSLPokerHand_082005] ([IsConsolidated]) ')
--GO
--
--/****** The index created by the following statement is for internal use only. ******/
--/****** It is not a real index but exists as statistics only. ******/
--if (@@microsoftversion > 0x07000000 )
--EXEC ('CREATE STATISTICS [Statistic_PSLPokerHandID] ON [dbo].[tb_PSLPokerHand_082005] ([PSLPokerHandID]) ')
--GO
--
--/****** The index created by the following statement is for internal use only. ******/
--/****** It is not a real index but exists as statistics only. ******/
--if (@@microsoftversion > 0x07000000 )
--EXEC ('CREATE STATISTICS [Statistic_PSLPokerTableID] ON [dbo].[tb_PSLPokerHand_082005] ([PSLPokerTableID]) ')
--GOSelect:
Code:
select min(pslpokerhandid), max(pslpokerhandid)
from vw_pslpokerhand
where partitionkey >= 2005277
and partitionkey < 2005359Explain plan:
Code:
StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
------------------------------ ----------- ----------- ----------- ------------------------------ ------------------------------ -------- ------------- ------------------------ ------------------------ ------------------------ ----------- ------------------------ ---------- -------- ------------------------------ -------- ------------------------
SET STATISTICS PROFILE ON 8 1 0 NULL NULL 1 NULL NULL NULL NULL NULL NULL NULL NULL SETSTATON 0 NULL
(1 row(s) affected)
StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------- ----------- ----------- ------------------------------ ------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------ ------------------------ ------------------------ ----------- ------------------------ ------------------------------------------------------------------------------------- -------- ------------------------------ -------- ------------------------
select min(pslpokerhandid), max(pslpokerhandid)
from vw_pslpokerhand
where partitionkey >= 2005277
and partitionkey < 2005359 9 1 0 NULL NULL 1 NULL 1.0 NULL NULL NULL 2.5656156E-2 NULL NULL SELECT 0 NULL
|--Stream Aggregate(DEFINE:([Expr1041]=MIN([partialagg1043]), [Expr1042]=MAX([partialagg1044]))) 9 2 1 Stream Aggregate Aggregate NULL [Expr1041]=MIN([partialagg1043]), [Expr1042]=MAX([partialagg1044]) 1.0 0.0 8.0000001E-7 23 2.5656156E-2 [Expr1041], [Expr1042] NULL PLAN_ROW 0 1.0
|--Concatenation 9 3 2 Concatenation Concatenation NULL [partialagg1043] = ([tb_PSLPokerHand_112005].[PSLPokerHandID], [tb_PSLPokerHand_122005].[PSLPokerHandID]), [partialagg1044] = ([tb_PSLPokerHand_112005].[PSLPokerHandID], [tb_PSLPokerHand_122005].[PSLPokerHandID]) 8.0 0.0 8.0000001E-7 23 2.5655357E-2 [partialagg1043], [partialagg1044] NULL PLAN_ROW 0 1.0
|--Nested Loops(Inner Join) 9 4 3 Nested Loops Inner Join NULL NULL 4.0 0.0 4.1799999E-6 23 0.01282078 [tb_PSLPokerHand_112005].[PSLPokerHandID], [tb_PSLPokerHand_112005].[PSLPokerHandID] NULL PLAN_ROW 0 1.0
| |--Compute Scalar(DEFINE:([tb_PSLPokerHand_112005].[PSLPokerHandID]=[tb_PSLPokerHand_112005].[PSLPokerHandID])) 9 5 4 Compute Scalar Compute Scalar DEFINE:([tb_PSLPokerHand_112005].[PSLPokerHandID]=[tb_PSLPokerHand_112005].[PSLPokerHandID]) [tb_PSLPokerHand_112005].[PSLPokerHandID]=[tb_PSLPokerHand_112005].[PSLPokerHandID] 1.0 0.0 0.0000001 15 6.4082998E-3 [tb_PSLPokerHand_112005].[PSLPokerHandID] NULL PLAN_ROW 0 1.0
| | |--Top(1) 9 6 5 Top Top NULL NULL 1.0 0.0 0.0000001 41 6.4082001E-3 [tb_PSLPokerHand_112005].[PSLPokerHandID] NULL PLAN_ROW 0 1.0
| | |--Index Scan(OBJECT:([CasinoArch].[dbo].[tb_PSLPokerHand_112005].[ix_PSLPokerHand_112005_PSLPokerHandID]), ORDERED FORWARD) 9 7 6 Index Scan Index Scan OBJECT:([CasinoArch].[dbo].[tb_PSLPokerHand_112005].[ix_PSLPokerHand_112005_PSLPokerHandID]), ORDERED FORWARD [tb_PSLPokerHand_112005].[PSLPokerHandID] 1.0 0.00320405 0.00320405 41 0.0064081 [tb_PSLPokerHand_112005].[PSLPokerHandID] NULL PLAN_ROW 0 1.0
| |--Compute Scalar(DEFINE:([tb_PSLPokerHand_112005].[PSLPokerHandID]=[tb_PSLPokerHand_112005].[PSLPokerHandID])) 9 11 4 Compute Scalar Compute Scalar DEFINE:([tb_PSLPokerHand_112005].[PSLPokerHandID]=[tb_PSLPokerHand_112005].[PSLPokerHandID]) [tb_PSLPokerHand_112005].[PSLPokerHandID]=[tb_PSLPokerHand_112005].[PSLPokerHandID] 1.0 0.0 0.0000001 15 6.4082998E-3 [tb_PSLPokerHand_112005].[PSLPokerHandID] NULL PLAN_ROW 0 1.0
| |--Top(1) 9 12 11 Top Top NULL NULL 1.0 0.0 0.0000001 41 6.4082001E-3 [tb_PSLPokerHand_112005].[PSLPokerHandID] NULL PLAN_ROW 0 1.0
| |--Index Scan(OBJECT:([CasinoArch].[dbo].[tb_PSLPokerHand_112005].[ix_PSLPokerHand_112005_PSLPokerHandID]), ORDERED BACKWARD) 9 13 12 Index Scan Index Scan OBJECT:([CasinoArch].[dbo].[tb_PSLPokerHand_112005].[ix_PSLPokerHand_112005_PSLPokerHandID]), ORDERED BACKWARD [tb_PSLPokerHand_112005].[PSLPokerHandID] 1.0 0.00320405 0.00320405 41 0.0064081 [tb_PSLPokerHand_112005].[PSLPokerHandID] NULL PLAN_ROW 0 1.0
|--Nested Loops(Inner Join) 9 17 3 Nested Loops Inner Join NULL NULL 4.0 0.0 4.1799999E-6 23 1.2833776E-2 [tb_PSLPokerHand_122005].[PSLPokerHandID], [tb_PSLPokerHand_122005].[PSLPokerHandID] NULL PLAN_ROW 0 1.0
|--Compute Scalar(DEFINE:([tb_PSLPokerHand_122005].[PSLPokerHandID]=[tb_PSLPokerHand_122005].[PSLPokerHandID])) 9 18 17 Compute Scalar Compute Scalar DEFINE:([tb_PSLPokerHand_122005].[PSLPokerHandID]=[tb_PSLPokerHand_122005].[PSLPokerHandID]) [tb_PSLPokerHand_122005].[PSLPokerHandID]=[tb_PSLPokerHand_122005].[PSLPokerHandID] 1.0 0.0 0.0000001 15 6.4147981E-3 [tb_PSLPokerHand_122005].[PSLPokerHandID] NULL PLAN_ROW 0 1.0
| |--Top(1) 9 19 18 Top Top NULL NULL 1.0 0.0 0.0000001 41 6.414698E-3 [tb_PSLPokerHand_122005].[PSLPokerHandID] NULL PLAN_ROW 0 1.0
| |--Index Scan(OBJECT:([CasinoArch].[dbo].[tb_PSLPokerHand_122005].[ix_PSLPokerHand_122005_PSLPokerHandID]), WHERE:([tb_PSLPokerHand_122005].[PartitionKey]<2005359) ORDERED FORWARD) 9 20 19 Index Scan Index Scan OBJECT:([CasinoArch].[dbo].[tb_PSLPokerHand_122005].[ix_PSLPokerHand_122005_PSLPokerHandID]), WHERE:([tb_PSLPokerHand_122005].[PartitionKey]<2005359) ORDERED FORWARD [tb_PSLPokerHand_122005].[PSLPokerHandID], [tb_PSLPokerHand_122005].[PartitionKey] 1.0 3.2066961E-3 3.2066961E-3 41 6.4133923E-3 [tb_PSLPokerHand_122005].[PSLPokerHandID], [tb_PSLPokerHand_122005].[PartitionKey] NULL PLAN_ROW 0 1.0
|--Compute Scalar(DEFINE:([tb_PSLPokerHand_122005].[PSLPokerHandID]=[tb_PSLPokerHand_122005].[PSLPokerHandID])) 9 24 17 Compute Scalar Compute Scalar DEFINE:([tb_PSLPokerHand_122005].[PSLPokerHandID]=[tb_PSLPokerHand_122005].[PSLPokerHandID]) [tb_PSLPokerHand_122005].[PSLPokerHandID]=[tb_PSLPokerHand_122005].[PSLPokerHandID] 1.0 0.0 0.0000001 15 6.4147981E-3 [tb_PSLPokerHand_122005].[PSLPokerHandID] NULL PLAN_ROW 0 1.0
|--Top(1) 9 25 24 Top Top NULL NULL 1.0 0.0 0.0000001 41 6.414698E-3 [tb_PSLPokerHand_122005].[PSLPokerHandID] NULL PLAN_ROW 0 1.0
|--Index Scan(OBJECT:([CasinoArch].[dbo].[tb_PSLPokerHand_122005].[ix_PSLPokerHand_122005_PSLPokerHandID]), WHERE:([tb_PSLPokerHand_122005].[PartitionKey]<2005359) ORDERED BACKWARD) 9 26 25 Index Scan Index Scan OBJECT:([CasinoArch].[dbo].[tb_PSLPokerHand_122005].[ix_PSLPokerHand_122005_PSLPokerHandID]), WHERE:([tb_PSLPokerHand_122005].[PartitionKey]<2005359) ORDERED BACKWARD [tb_PSLPokerHand_122005].[PSLPokerHandID], [tb_PSLPokerHand_122005].[PartitionKey] 1.0 3.2066961E-3 3.2066961E-3 41 6.4133923E-3 [tb_PSLPokerHand_122005].[PSLPokerHandID], [tb_PSLPokerHand_122005].[PartitionKey] NULL PLAN_ROW 0 1.0
(17 row(s) affected)
StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
----------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ -------- ------------- ------------------------ ------------------------ ------------------------ ----------- ------------------------ ---------- -------- ------------------------------ -------- ------------------------
SET STATISTICS PROFILE OFF 10 1 0 NULL NULL 1 NULL NULL NULL NULL NULL NULL NULL NULL SETSTATON 0 NULL
(1 row(s) affected)From the explain plan it looks like the optimizer "knows" in what range the pslpokerhandid is without really going through the partitionkey. I am asking this because in Oracle we have a similar setup and the query runs for more than 3 minutes (5 seconds in SQL Server) with the same amount of data, data structures etc.
In Oracle you can clearly see how the optimizer needs to get the range first (from the where clause) and then do a full scan of the index on the pslpokerhandid field to get the min and max values.
Oracle plan:
Code:
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 24147 (1)| 00:04:50 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | INDEX FAST FULL SCAN| PK_PSLPOKERHAND_NONPART | 35M| 434M| 24147 (1)| 00:04:50 |
-------------------------------------------------------------------------------------------------Many thanks,
Jaco
![[banghead]](/data/assets/smilies/banghead.gif "[banghead] [banghead]")
