I have a problem with a partition view which is spread across various tables
devided by quarter. When I run the select stmt listed below (at the bottom),
I come up with strange results in terms of execution plan, statistics IO and
profiler data.
In both cases the data is returned the same, but case 2 takes longer than 1
(results below). It seems that this is based on how the query is written -
when it uses an "or 1=2" clause it is much faster.
I have seen other posts where people point out that you should only look at
the statistics IO for this and that the execution plan does not reflect the
actually query execution. But in my case, the statistics IO is different as
well.
Can anyone explain this?
(I am running CHECKPOINT, DBCC DROPCLEANBUFFERS, DBCC FREEPROCCACHE before
each of these)
--CASE 1
SELECT *
FROM [dbo].[PartitionView]
WHERE [DateID] = 7925 OR 1 = 2
Execution plan:
Only access one table from the view
Query Cost (relative to batch): Q1: 2.32%, Q3: 2.32%
Sub tree Cost: 0.0372
statistics IO:
Table 'atable1Qtr2005'. Scan count 1, logical reads 26, physical reads 3,
read-ahead reads 24.
Profiler:
Reads: 68
--CASE 2
SELECT *
FROM [dbo].[PartitionView]
WHERE [DateID] = 7925
Execution plan:
Access all tables from the view
Query Cost (relative to batch): Q2: 12.69% Q4: 41.34%, Q5: 41.34%
Sub tree Cost: Q2: 0.204, Q4: 0.665, Q5: 0.665
statistics IO:
Table 'atable4Qtr2005'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'atable3Qtr2005'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'atable2Qtr2005'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'atable1Qtr2005'. Scan count 1, logical reads 26, physical reads 3,
read-ahead reads 24.
Table 'atable4Qtr2004'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'atable3Qtr2004'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'atable2Qtr2004'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'atable1Qtr2004'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'atable4Qtr2003'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'atable3Qtr2003'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'atable2Qtr2003'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'atable1Qtr2003'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'atable4Qtr2002'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'atable3Qtr2002'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'atable2Qtr2002'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'atable1Qtr2002'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'atable4Qtr2001'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'atable3Qtr2001'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'atable2Qtr2001'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'atable1Qtr2001'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'atable4Qtr2000'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'atable3Qtr2000'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'atable2Qtr2000'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'atable1Qtr2000'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'atable4Qtr1999'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'atable3Qtr1999'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'atable2Qtr1999'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Profiler:
Reads: 432Please post your DDL.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:85D05074-D45F-4AFD-BEA6-2A147AC16725@.microsoft.com...
I have a problem with a partition view which is spread across various tables
devided by quarter. When I run the select stmt listed below (at the bottom),
I come up with strange results in terms of execution plan, statistics IO
and
profiler data.
In both cases the data is returned the same, but case 2 takes longer than 1
(results below). It seems that this is based on how the query is written -
when it uses an "or 1=2" clause it is much faster.
I have seen other posts where people point out that you should only look at
the statistics IO for this and that the execution plan does not reflect the
actually query execution. But in my case, the statistics IO is different as
well.
Can anyone explain this?
(I am running CHECKPOINT, DBCC DROPCLEANBUFFERS, DBCC FREEPROCCACHE before
each of these)
--CASE 1
SELECT *
FROM [dbo].[PartitionView]
WHERE [DateID] = 7925 OR 1 = 2
Execution plan:
Only access one table from the view
Query Cost (relative to batch): Q1: 2.32%, Q3: 2.32%
Sub tree Cost: 0.0372
statistics IO:
Table 'atable1Qtr2005'. Scan count 1, logical reads 26, physical reads 3,
read-ahead reads 24.
Profiler:
Reads: 68
--CASE 2
SELECT *
FROM [dbo].[PartitionView]
WHERE [DateID] = 7925
Execution plan:
Access all tables from the view
Query Cost (relative to batch): Q2: 12.69% Q4: 41.34%, Q5: 41.34%
Sub tree Cost: Q2: 0.204, Q4: 0.665, Q5: 0.665
statistics IO:
Table 'atable4Qtr2005'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'atable3Qtr2005'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'atable2Qtr2005'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'atable1Qtr2005'. Scan count 1, logical reads 26, physical reads 3,
read-ahead reads 24.
Table 'atable4Qtr2004'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'atable3Qtr2004'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'atable2Qtr2004'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'atable1Qtr2004'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'atable4Qtr2003'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'atable3Qtr2003'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'atable2Qtr2003'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'atable1Qtr2003'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'atable4Qtr2002'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'atable3Qtr2002'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'atable2Qtr2002'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'atable1Qtr2002'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'atable4Qtr2001'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'atable3Qtr2001'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'atable2Qtr2001'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'atable1Qtr2001'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'atable4Qtr2000'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'atable3Qtr2000'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'atable2Qtr2000'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'atable1Qtr2000'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'atable4Qtr1999'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'atable3Qtr1999'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'atable2Qtr1999'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Profiler:
Reads: 432|||Here you go Tom. Only problem is that I am using another table to populate my
underlying tables but you should be able to get around this:
--create and populate tables
USE aDatabase
-- =============================================-- Declare and using a READ_ONLY cursor
-- =============================================DECLARE tableName CURSOR
READ_ONLY
FOR
select
CalendarYear,
CalendarQuarter,
min(CalendarID) as minCalendarID,
max(CalendarID) as maxCalendarID
from
aDatabase..dimCalendarTbl
where
CalendarDate between '23 April 1999' and '31 Dec 2005'
group by
CalendarYear,
CalendarQuarter
order by min(CalendarID)
DECLARE
@.CalendarYear int,
@.CalendarQuarter int,
@.minCalendarID int,
@.maxCalendarID int,
@.ViewTables varchar(8000)
select @.ViewTables = ''
OPEN tableName
FETCH NEXT FROM tableName INTO @.CalendarYear, @.CalendarQuarter,
@.minCalendarID, @.maxCalendarID
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
DECLARE @.tableName varchar(100)
DECLARE @.dropStatement varchar(8000)
DECLARE @.createStatement varchar(8000)
DECLARE @.fileGroup varchar(100)
DECLARE @.physicalFile varchar(100)
DECLARE @.logicalFile varchar(100)
DECLARE @.AddFilegroup varchar(8000)
DECLARE @.AddFile varchar(8000)
SELECT @.tableName = 'aTable' + ltrim(rtrim(str(@.CalendarQuarter))) + 'Qtr'
+ ltrim(rtrim(str(@.CalendarYear)))
select @.logicalFile = 'aDatabase_' +
ltrim(rtrim(str(@.CalendarYear))) + '0' + ltrim(rtrim(str(@.CalendarQuarter)))
--select @.physicalFile = 'd:\MSSQL\DATA\f_' + @.tableName +
'.ndf'
SELECT @.ViewTables = @.ViewTables + ' UNION ALL select * from
' + @.logicalFile + '.dbo.' + @.tableName
select @.AddFile = 'DROP DATABASE ' + @.logicalFile + ' '
exec(@.AddFile)
select @.AddFile = 'CREATE DATABASE ' + @.logicalFile + ' '
exec(@.AddFile)
select @.dropStatement = 'USE ' + @.logicalFile + ' if exists
(select * from sysobjects where id = object_id(''' + @.tableName + ''') and
OBJECTPROPERTY(id, ''IsUserTable'') = 1) begin ' +
' drop table ' + @.tableName + ' end'
exec(@.dropStatement)
select @.createStatement = 'USE ' + @.logicalFile + ' CREATE
TABLE ' + @.tableName +
'(' +
' DateID int NOT NULL DEFAULT -1,' +
' KeyField2 int NOT NULL DEFAULT -1,' +
' KeyField3 int NOT NULL DEFAULT -1,' +
' KeyField4 int NOT NULL DEFAULT -1,' +
' KeyField5 int NOT NULL DEFAULT -1,' +
' DataField1 numeric(13,2) NULL,' +
' DataField2 numeric(13,2) NULL,' +
' DataField3 numeric(13,2) NULL,' +
' DataField4 numeric(13,2) NULL,' +
' DataField5 numeric(13,2) NULL' +
')' +
' ALTER TABLE ' + @.tableName + ' WITH CHECK ADD ' +
'CONSTRAINT PK' + @.tableName + ' PRIMARY KEY CLUSTERED '
+
'(' +
' DateID,' +
' KeyField2,' +
' KeyField3,' +
' KeyField4,' +
' KeyField5' +
'),' +
'CONSTRAINT CK' + @.tableName + ' CHECK (DateID between ' +
ltrim(rtrim(str(@.minCalendarID))) + ' AND ' +
ltrim(rtrim(str(@.maxCalendarID))) + ')'
exec( @.createStatement)
END
FETCH NEXT FROM tableName INTO @.CalendarYear, @.CalendarQuarter,
@.minCalendarID, @.maxCalendarID
END
CLOSE tableName
DEALLOCATE tableName
--View
CREATE VIEW PartitionView AS
SELECT [DateID], [KeyField2], [KeyField3], [KeyField4], [KeyField5],
[DataField1], [DataField2], [DataField3], [DataField4], [DataField5]
FROM aDatabase_199902.dbo.aTable2Qtr1999
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_199903.dbo.aTable3Qtr1999
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_199904.dbo.aTable4Qtr1999
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200001.dbo.aTable1Qtr2000
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200002.dbo.aTable2Qtr2000
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200003.dbo.aTable3Qtr2000
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200004.dbo.aTable4Qtr2000
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200101.dbo.aTable1Qtr2001
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200102.dbo.aTable2Qtr2001
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200103.dbo.aTable3Qtr2001
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200104.dbo.aTable4Qtr2001
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200201.dbo.aTable1Qtr2002
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200202.dbo.aTable2Qtr2002
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200203.dbo.aTable3Qtr2002
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200204.dbo.aTable4Qtr2002
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200301.dbo.aTable1Qtr2003
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200302.dbo.aTable2Qtr2003
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200303.dbo.aTable3Qtr2003
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200304.dbo.aTable4Qtr2003
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200401.dbo.aTable1Qtr2004
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200402.dbo.aTable2Qtr2004
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200403.dbo.aTable3Qtr2004
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200404.dbo.aTable4Qtr2004
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200501.dbo.aTable1Qtr2005
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200502.dbo.aTable2Qtr2005
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200503.dbo.aTable3Qtr2005
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200504.dbo.aTable4Qtr2005
GO
"Tom Moreau" wrote:
> Please post your DDL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "DBA72" <DBA72@.discussions.microsoft.com> wrote in message
> news:85D05074-D45F-4AFD-BEA6-2A147AC16725@.microsoft.com...
> I have a problem with a partition view which is spread across various tables
> devided by quarter. When I run the select stmt listed below (at the bottom),
> I come up with strange results in terms of execution plan, statistics IO
> and
> profiler data.
> In both cases the data is returned the same, but case 2 takes longer than 1
> (results below). It seems that this is based on how the query is written -
> when it uses an "or 1=2" clause it is much faster.
> I have seen other posts where people point out that you should only look at
> the statistics IO for this and that the execution plan does not reflect the
> actually query execution. But in my case, the statistics IO is different as
> well.
> Can anyone explain this?
> (I am running CHECKPOINT, DBCC DROPCLEANBUFFERS, DBCC FREEPROCCACHE before
> each of these)
> --CASE 1
> SELECT *
> FROM [dbo].[PartitionView]
> WHERE [DateID] = 7925 OR 1 = 2
> Execution plan:
> Only access one table from the view
> Query Cost (relative to batch): Q1: 2.32%, Q3: 2.32%
> Sub tree Cost: 0.0372
> statistics IO:
> Table 'atable1Qtr2005'. Scan count 1, logical reads 26, physical reads 3,
> read-ahead reads 24.
> Profiler:
> Reads: 68
> --CASE 2
> SELECT *
> FROM [dbo].[PartitionView]
> WHERE [DateID] = 7925
> Execution plan:
> Access all tables from the view
> Query Cost (relative to batch): Q2: 12.69% Q4: 41.34%, Q5: 41.34%
> Sub tree Cost: Q2: 0.204, Q4: 0.665, Q5: 0.665
> statistics IO:
> Table 'atable4Qtr2005'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable3Qtr2005'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable2Qtr2005'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable1Qtr2005'. Scan count 1, logical reads 26, physical reads 3,
> read-ahead reads 24.
> Table 'atable4Qtr2004'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable3Qtr2004'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable2Qtr2004'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable1Qtr2004'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable4Qtr2003'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable3Qtr2003'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable2Qtr2003'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable1Qtr2003'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable4Qtr2002'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable3Qtr2002'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable2Qtr2002'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable1Qtr2002'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable4Qtr2001'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable3Qtr2001'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable2Qtr2001'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable1Qtr2001'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable4Qtr2000'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable3Qtr2000'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable2Qtr2000'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable1Qtr2000'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable4Qtr1999'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable3Qtr1999'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable2Qtr1999'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Profiler:
> Reads: 432
>|||You are right. It does depend on how the query is written, and it has to do
with the optimizer and the parameter sniffing associated with partitioned
views.
Can you post the table DDL (aTable4Qtr2002, and so on), with all indexes and
check constraints?
Omri Bahat
SQL Farms Solutions
www.sqlfarms.com|||You've given us the view but not the underlying tables and indexes. We need
those for a complete picture.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:62BEB6AC-EB2E-4099-8B78-7AE4752EF986@.microsoft.com...
Here you go Tom. Only problem is that I am using another table to populate
my
underlying tables but you should be able to get around this:
--create and populate tables
USE aDatabase
-- =============================================-- Declare and using a READ_ONLY cursor
-- =============================================DECLARE tableName CURSOR
READ_ONLY
FOR
select
CalendarYear,
CalendarQuarter,
min(CalendarID) as minCalendarID,
max(CalendarID) as maxCalendarID
from
aDatabase..dimCalendarTbl
where
CalendarDate between '23 April 1999' and '31 Dec 2005'
group by
CalendarYear,
CalendarQuarter
order by min(CalendarID)
DECLARE
@.CalendarYear int,
@.CalendarQuarter int,
@.minCalendarID int,
@.maxCalendarID int,
@.ViewTables varchar(8000)
select @.ViewTables = ''
OPEN tableName
FETCH NEXT FROM tableName INTO @.CalendarYear, @.CalendarQuarter,
@.minCalendarID, @.maxCalendarID
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
DECLARE @.tableName varchar(100)
DECLARE @.dropStatement varchar(8000)
DECLARE @.createStatement varchar(8000)
DECLARE @.fileGroup varchar(100)
DECLARE @.physicalFile varchar(100)
DECLARE @.logicalFile varchar(100)
DECLARE @.AddFilegroup varchar(8000)
DECLARE @.AddFile varchar(8000)
SELECT @.tableName = 'aTable' + ltrim(rtrim(str(@.CalendarQuarter))) + 'Qtr'
+ ltrim(rtrim(str(@.CalendarYear)))
select @.logicalFile = 'aDatabase_' +
ltrim(rtrim(str(@.CalendarYear))) + '0' + ltrim(rtrim(str(@.CalendarQuarter)))
--select @.physicalFile = 'd:\MSSQL\DATA\f_' + @.tableName +
'.ndf'
SELECT @.ViewTables = @.ViewTables + ' UNION ALL select * from
' + @.logicalFile + '.dbo.' + @.tableName
select @.AddFile = 'DROP DATABASE ' + @.logicalFile + ' '
exec(@.AddFile)
select @.AddFile = 'CREATE DATABASE ' + @.logicalFile + ' '
exec(@.AddFile)
select @.dropStatement = 'USE ' + @.logicalFile + ' if exists
(select * from sysobjects where id = object_id(''' + @.tableName + ''') and
OBJECTPROPERTY(id, ''IsUserTable'') = 1) begin ' +
' drop table ' + @.tableName + ' end'
exec(@.dropStatement)
select @.createStatement = 'USE ' + @.logicalFile + ' CREATE
TABLE ' + @.tableName +
'(' +
' DateID int NOT NULL DEFAULT -1,' +
' KeyField2 int NOT NULL DEFAULT -1,' +
' KeyField3 int NOT NULL DEFAULT -1,' +
' KeyField4 int NOT NULL DEFAULT -1,' +
' KeyField5 int NOT NULL DEFAULT -1,' +
' DataField1 numeric(13,2) NULL,' +
' DataField2 numeric(13,2) NULL,' +
' DataField3 numeric(13,2) NULL,' +
' DataField4 numeric(13,2) NULL,' +
' DataField5 numeric(13,2) NULL' +
')' +
' ALTER TABLE ' + @.tableName + ' WITH CHECK ADD ' +
'CONSTRAINT PK' + @.tableName + ' PRIMARY KEY CLUSTERED '
+
'(' +
' DateID,' +
' KeyField2,' +
' KeyField3,' +
' KeyField4,'
+
' KeyField5' +
'),' +
'CONSTRAINT CK' + @.tableName + ' CHECK (DateID between '
+
ltrim(rtrim(str(@.minCalendarID))) + ' AND ' +
ltrim(rtrim(str(@.maxCalendarID))) + ')'
exec( @.createStatement)
END
FETCH NEXT FROM tableName INTO @.CalendarYear, @.CalendarQuarter,
@.minCalendarID, @.maxCalendarID
END
CLOSE tableName
DEALLOCATE tableName
--View
CREATE VIEW PartitionView AS
SELECT [DateID], [KeyField2], [KeyField3], [KeyField4], [KeyField5],
[DataField1], [DataField2], [DataField3], [DataField4], [DataField5]
FROM aDatabase_199902.dbo.aTable2Qtr1999
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_199903.dbo.aTable3Qtr1999
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_199904.dbo.aTable4Qtr1999
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200001.dbo.aTable1Qtr2000
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200002.dbo.aTable2Qtr2000
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200003.dbo.aTable3Qtr2000
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200004.dbo.aTable4Qtr2000
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200101.dbo.aTable1Qtr2001
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200102.dbo.aTable2Qtr2001
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200103.dbo.aTable3Qtr2001
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200104.dbo.aTable4Qtr2001
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200201.dbo.aTable1Qtr2002
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200202.dbo.aTable2Qtr2002
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200203.dbo.aTable3Qtr2002
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200204.dbo.aTable4Qtr2002
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200301.dbo.aTable1Qtr2003
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200302.dbo.aTable2Qtr2003
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200303.dbo.aTable3Qtr2003
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200304.dbo.aTable4Qtr2003
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200401.dbo.aTable1Qtr2004
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200402.dbo.aTable2Qtr2004
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200403.dbo.aTable3Qtr2004
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200404.dbo.aTable4Qtr2004
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200501.dbo.aTable1Qtr2005
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200502.dbo.aTable2Qtr2005
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200503.dbo.aTable3Qtr2005
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200504.dbo.aTable4Qtr2005
GO
"Tom Moreau" wrote:
> Please post your DDL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "DBA72" <DBA72@.discussions.microsoft.com> wrote in message
> news:85D05074-D45F-4AFD-BEA6-2A147AC16725@.microsoft.com...
> I have a problem with a partition view which is spread across various
> tables
> devided by quarter. When I run the select stmt listed below (at the
> bottom),
> I come up with strange results in terms of execution plan, statistics IO
> and
> profiler data.
> In both cases the data is returned the same, but case 2 takes longer than
> 1
> (results below). It seems that this is based on how the query is written -
> when it uses an "or 1=2" clause it is much faster.
> I have seen other posts where people point out that you should only look
> at
> the statistics IO for this and that the execution plan does not reflect
> the
> actually query execution. But in my case, the statistics IO is different
> as
> well.
> Can anyone explain this?
> (I am running CHECKPOINT, DBCC DROPCLEANBUFFERS, DBCC FREEPROCCACHE before
> each of these)
> --CASE 1
> SELECT *
> FROM [dbo].[PartitionView]
> WHERE [DateID] = 7925 OR 1 = 2
> Execution plan:
> Only access one table from the view
> Query Cost (relative to batch): Q1: 2.32%, Q3: 2.32%
> Sub tree Cost: 0.0372
> statistics IO:
> Table 'atable1Qtr2005'. Scan count 1, logical reads 26, physical reads 3,
> read-ahead reads 24.
> Profiler:
> Reads: 68
> --CASE 2
> SELECT *
> FROM [dbo].[PartitionView]
> WHERE [DateID] = 7925
> Execution plan:
> Access all tables from the view
> Query Cost (relative to batch): Q2: 12.69% Q4: 41.34%, Q5: 41.34%
> Sub tree Cost: Q2: 0.204, Q4: 0.665, Q5: 0.665
> statistics IO:
> Table 'atable4Qtr2005'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable3Qtr2005'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable2Qtr2005'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable1Qtr2005'. Scan count 1, logical reads 26, physical reads 3,
> read-ahead reads 24.
> Table 'atable4Qtr2004'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable3Qtr2004'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable2Qtr2004'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable1Qtr2004'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable4Qtr2003'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable3Qtr2003'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable2Qtr2003'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable1Qtr2003'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable4Qtr2002'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable3Qtr2002'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable2Qtr2002'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable1Qtr2002'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable4Qtr2001'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable3Qtr2001'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable2Qtr2001'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable1Qtr2001'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable4Qtr2000'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable3Qtr2000'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable2Qtr2000'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable1Qtr2000'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable4Qtr1999'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable3Qtr1999'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable2Qtr1999'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Profiler:
> Reads: 432
>|||Tom and Omri,
Thank you for you rreplies. The DDL for the table is in the script. It is
built dynamically. If you do a search for "CREATE TABLE" you should be able
to find it.
Regards
"Tom Moreau" wrote:
> You've given us the view but not the underlying tables and indexes. We need
> those for a complete picture.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "DBA72" <DBA72@.discussions.microsoft.com> wrote in message
> news:62BEB6AC-EB2E-4099-8B78-7AE4752EF986@.microsoft.com...
> Here you go Tom. Only problem is that I am using another table to populate
> my
> underlying tables but you should be able to get around this:
> --create and populate tables
> USE aDatabase
> -- =============================================> -- Declare and using a READ_ONLY cursor
> -- =============================================> DECLARE tableName CURSOR
> READ_ONLY
> FOR
> select
> CalendarYear,
> CalendarQuarter,
> min(CalendarID) as minCalendarID,
> max(CalendarID) as maxCalendarID
> from
> aDatabase..dimCalendarTbl
> where
> CalendarDate between '23 April 1999' and '31 Dec 2005'
> group by
> CalendarYear,
> CalendarQuarter
> order by min(CalendarID)
>
> DECLARE
> @.CalendarYear int,
> @.CalendarQuarter int,
> @.minCalendarID int,
> @.maxCalendarID int,
> @.ViewTables varchar(8000)
> select @.ViewTables = ''
> OPEN tableName
> FETCH NEXT FROM tableName INTO @.CalendarYear, @.CalendarQuarter,
> @.minCalendarID, @.maxCalendarID
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> DECLARE @.tableName varchar(100)
> DECLARE @.dropStatement varchar(8000)
> DECLARE @.createStatement varchar(8000)
> DECLARE @.fileGroup varchar(100)
> DECLARE @.physicalFile varchar(100)
> DECLARE @.logicalFile varchar(100)
> DECLARE @.AddFilegroup varchar(8000)
> DECLARE @.AddFile varchar(8000)
> SELECT @.tableName = 'aTable' + ltrim(rtrim(str(@.CalendarQuarter))) + 'Qtr'
> + ltrim(rtrim(str(@.CalendarYear)))
> select @.logicalFile = 'aDatabase_' +
> ltrim(rtrim(str(@.CalendarYear))) + '0' + ltrim(rtrim(str(@.CalendarQuarter)))
> --select @.physicalFile = 'd:\MSSQL\DATA\f_' + @.tableName +
> '.ndf'
> SELECT @.ViewTables = @.ViewTables + ' UNION ALL select * from
> ' + @.logicalFile + '.dbo.' + @.tableName
> select @.AddFile => 'DROP DATABASE ' + @.logicalFile + ' '
> exec(@.AddFile)
> select @.AddFile => 'CREATE DATABASE ' + @.logicalFile + ' '
> exec(@.AddFile)
>
> select @.dropStatement = 'USE ' + @.logicalFile + ' if exists
> (select * from sysobjects where id = object_id(''' + @.tableName + ''') and
> OBJECTPROPERTY(id, ''IsUserTable'') = 1) begin ' +
> ' drop table ' + @.tableName + ' end'
> exec(@.dropStatement)
> select @.createStatement = 'USE ' + @.logicalFile + ' CREATE
> TABLE ' + @.tableName +
> '(' +
> ' DateID int NOT NULL DEFAULT -1,' +
> ' KeyField2 int NOT NULL DEFAULT -1,' +
> ' KeyField3 int NOT NULL DEFAULT -1,' +
> ' KeyField4 int NOT NULL DEFAULT -1,' +
> ' KeyField5 int NOT NULL DEFAULT -1,' +
> ' DataField1 numeric(13,2) NULL,' +
> ' DataField2 numeric(13,2) NULL,' +
> ' DataField3 numeric(13,2) NULL,' +
> ' DataField4 numeric(13,2) NULL,' +
> ' DataField5 numeric(13,2) NULL' +
> ')' +
> ' ALTER TABLE ' + @.tableName + ' WITH CHECK ADD ' +
> 'CONSTRAINT PK' + @.tableName + ' PRIMARY KEY CLUSTERED '
> +
> '(' +
> ' DateID,' +
> ' KeyField2,' +
> ' KeyField3,' +
> ' KeyField4,'
> +
> ' KeyField5' +
> '),' +
> 'CONSTRAINT CK' + @.tableName + ' CHECK (DateID between '
> +
> ltrim(rtrim(str(@.minCalendarID))) + ' AND ' +
> ltrim(rtrim(str(@.maxCalendarID))) + ')'
> exec( @.createStatement)
> END
> FETCH NEXT FROM tableName INTO @.CalendarYear, @.CalendarQuarter,
> @.minCalendarID, @.maxCalendarID
> END
> CLOSE tableName
> DEALLOCATE tableName
> --View
> CREATE VIEW PartitionView AS
> SELECT [DateID], [KeyField2], [KeyField3], [KeyField4], [KeyField5],
> [DataField1], [DataField2], [DataField3], [DataField4], [DataField5]
> FROM aDatabase_199902.dbo.aTable2Qtr1999
> UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
> [KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
> [DataField5]
> FROM aDatabase_199903.dbo.aTable3Qtr1999
> UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
> [KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
> [DataField5]
> FROM aDatabase_199904.dbo.aTable4Qtr1999
> UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
> [KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
> [DataField5]
> FROM aDatabase_200001.dbo.aTable1Qtr2000
> UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
> [KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
> [DataField5]
> FROM aDatabase_200002.dbo.aTable2Qtr2000
> UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
> [KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
> [DataField5]
> FROM aDatabase_200003.dbo.aTable3Qtr2000
> UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
> [KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
> [DataField5]
> FROM aDatabase_200004.dbo.aTable4Qtr2000
> UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
> [KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
> [DataField5]
> FROM aDatabase_200101.dbo.aTable1Qtr2001
> UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
> [KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
> [DataField5]
> FROM aDatabase_200102.dbo.aTable2Qtr2001
> UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
> [KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
> [DataField5]
> FROM aDatabase_200103.dbo.aTable3Qtr2001
> UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
> [KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
> [DataField5]
> FROM aDatabase_200104.dbo.aTable4Qtr2001
> UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
> [KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
> [DataField5]
> FROM aDatabase_200201.dbo.aTable1Qtr2002
> UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
> [KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
> [DataField5]
> FROM aDatabase_200202.dbo.aTable2Qtr2002
> UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
> [KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
> [DataField5]
> FROM aDatabase_200203.dbo.aTable3Qtr2002
> UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
> [KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
> [DataField5]
> FROM aDatabase_200204.dbo.aTable4Qtr2002
> UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
> [KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
> [DataField5]
> FROM aDatabase_200301.dbo.aTable1Qtr2003
> UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
> [KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
> [DataField5]
> FROM aDatabase_200302.dbo.aTable2Qtr2003
> UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
> [KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
> [DataField5]
> FROM aDatabase_200303.dbo.aTable3Qtr2003
> UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
> [KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
> [DataField5]
> FROM aDatabase_200304.dbo.aTable4Qtr2003
> UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
> [KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
> [DataField5]
> FROM aDatabase_200401.dbo.aTable1Qtr2004
> UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
> [KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
> [DataField5]
> FROM aDatabase_200402.dbo.aTable2Qtr2004
> UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
> [KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
> [DataField5]
> FROM aDatabase_200403.dbo.aTable3Qtr2004
> UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
> [KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
> [DataField5]
> FROM aDatabase_200404.dbo.aTable4Qtr2004
> UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
> [KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
> [DataField5]
> FROM aDatabase_200501.dbo.aTable1Qtr2005
> UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
> [KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
> [DataField5]
> FROM aDatabase_200502.dbo.aTable2Qtr2005
> UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
> [KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
> [DataField5]
> FROM aDatabase_200503.dbo.aTable3Qtr2005
> UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
> [KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
> [DataField5]
> FROM aDatabase_200504.dbo.aTable4Qtr2005
> GO
>
> "Tom Moreau" wrote:
> > Please post your DDL.
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinpub.com
> > ..
> > "DBA72" <DBA72@.discussions.microsoft.com> wrote in message
> > news:85D05074-D45F-4AFD-BEA6-2A147AC16725@.microsoft.com...
> > I have a problem with a partition view which is spread across various
> > tables
> > devided by quarter. When I run the select stmt listed below (at the
> > bottom),
> > I come up with strange results in terms of execution plan, statistics IO
> > and
> > profiler data.
> >
> > In both cases the data is returned the same, but case 2 takes longer than
> > 1
> > (results below). It seems that this is based on how the query is written -
> > when it uses an "or 1=2" clause it is much faster.
> >
> > I have seen other posts where people point out that you should only look
> > at
> > the statistics IO for this and that the execution plan does not reflect
> > the
> > actually query execution. But in my case, the statistics IO is different
> > as
> > well.
> >
> > Can anyone explain this?
> >
> > (I am running CHECKPOINT, DBCC DROPCLEANBUFFERS, DBCC FREEPROCCACHE before
> > each of these)
> >
> > --CASE 1
> > SELECT *
> > FROM [dbo].[PartitionView]
> > WHERE [DateID] = 7925 OR 1 = 2
> >
> > Execution plan:
> > Only access one table from the view
> > Query Cost (relative to batch): Q1: 2.32%, Q3: 2.32%
> > Sub tree Cost: 0.0372
> >
> > statistics IO:
> > Table 'atable1Qtr2005'. Scan count 1, logical reads 26, physical reads 3,
> > read-ahead reads 24.
> >
> > Profiler:
> > Reads: 68|||Actually, without that other table (aDatabase..dimCalendarTbl ), I can't run
your script.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:62BEB6AC-EB2E-4099-8B78-7AE4752EF986@.microsoft.com...
Here you go Tom. Only problem is that I am using another table to populate
my
underlying tables but you should be able to get around this:
--create and populate tables
USE aDatabase
-- =============================================-- Declare and using a READ_ONLY cursor
-- =============================================DECLARE tableName CURSOR
READ_ONLY
FOR
select
CalendarYear,
CalendarQuarter,
min(CalendarID) as minCalendarID,
max(CalendarID) as maxCalendarID
from
aDatabase..dimCalendarTbl
where
CalendarDate between '23 April 1999' and '31 Dec 2005'
group by
CalendarYear,
CalendarQuarter
order by min(CalendarID)
DECLARE
@.CalendarYear int,
@.CalendarQuarter int,
@.minCalendarID int,
@.maxCalendarID int,
@.ViewTables varchar(8000)
select @.ViewTables = ''
OPEN tableName
FETCH NEXT FROM tableName INTO @.CalendarYear, @.CalendarQuarter,
@.minCalendarID, @.maxCalendarID
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
DECLARE @.tableName varchar(100)
DECLARE @.dropStatement varchar(8000)
DECLARE @.createStatement varchar(8000)
DECLARE @.fileGroup varchar(100)
DECLARE @.physicalFile varchar(100)
DECLARE @.logicalFile varchar(100)
DECLARE @.AddFilegroup varchar(8000)
DECLARE @.AddFile varchar(8000)
SELECT @.tableName = 'aTable' + ltrim(rtrim(str(@.CalendarQuarter))) + 'Qtr'
+ ltrim(rtrim(str(@.CalendarYear)))
select @.logicalFile = 'aDatabase_' +
ltrim(rtrim(str(@.CalendarYear))) + '0' + ltrim(rtrim(str(@.CalendarQuarter)))
--select @.physicalFile = 'd:\MSSQL\DATA\f_' + @.tableName +
'.ndf'
SELECT @.ViewTables = @.ViewTables + ' UNION ALL select * from
' + @.logicalFile + '.dbo.' + @.tableName
select @.AddFile = 'DROP DATABASE ' + @.logicalFile + ' '
exec(@.AddFile)
select @.AddFile = 'CREATE DATABASE ' + @.logicalFile + ' '
exec(@.AddFile)
select @.dropStatement = 'USE ' + @.logicalFile + ' if exists
(select * from sysobjects where id = object_id(''' + @.tableName + ''') and
OBJECTPROPERTY(id, ''IsUserTable'') = 1) begin ' +
' drop table ' + @.tableName + ' end'
exec(@.dropStatement)
select @.createStatement = 'USE ' + @.logicalFile + ' CREATE
TABLE ' + @.tableName +
'(' +
' DateID int NOT NULL DEFAULT -1,' +
' KeyField2 int NOT NULL DEFAULT -1,' +
' KeyField3 int NOT NULL DEFAULT -1,' +
' KeyField4 int NOT NULL DEFAULT -1,' +
' KeyField5 int NOT NULL DEFAULT -1,' +
' DataField1 numeric(13,2) NULL,' +
' DataField2 numeric(13,2) NULL,' +
' DataField3 numeric(13,2) NULL,' +
' DataField4 numeric(13,2) NULL,' +
' DataField5 numeric(13,2) NULL' +
')' +
' ALTER TABLE ' + @.tableName + ' WITH CHECK ADD ' +
'CONSTRAINT PK' + @.tableName + ' PRIMARY KEY CLUSTERED '
+
'(' +
' DateID,' +
' KeyField2,' +
' KeyField3,' +
' KeyField4,'
+
' KeyField5' +
'),' +
'CONSTRAINT CK' + @.tableName + ' CHECK (DateID between '
+
ltrim(rtrim(str(@.minCalendarID))) + ' AND ' +
ltrim(rtrim(str(@.maxCalendarID))) + ')'
exec( @.createStatement)
END
FETCH NEXT FROM tableName INTO @.CalendarYear, @.CalendarQuarter,
@.minCalendarID, @.maxCalendarID
END
CLOSE tableName
DEALLOCATE tableName
--View
CREATE VIEW PartitionView AS
SELECT [DateID], [KeyField2], [KeyField3], [KeyField4], [KeyField5],
[DataField1], [DataField2], [DataField3], [DataField4], [DataField5]
FROM aDatabase_199902.dbo.aTable2Qtr1999
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_199903.dbo.aTable3Qtr1999
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_199904.dbo.aTable4Qtr1999
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200001.dbo.aTable1Qtr2000
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200002.dbo.aTable2Qtr2000
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200003.dbo.aTable3Qtr2000
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200004.dbo.aTable4Qtr2000
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200101.dbo.aTable1Qtr2001
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200102.dbo.aTable2Qtr2001
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200103.dbo.aTable3Qtr2001
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200104.dbo.aTable4Qtr2001
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200201.dbo.aTable1Qtr2002
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200202.dbo.aTable2Qtr2002
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200203.dbo.aTable3Qtr2002
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200204.dbo.aTable4Qtr2002
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200301.dbo.aTable1Qtr2003
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200302.dbo.aTable2Qtr2003
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200303.dbo.aTable3Qtr2003
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200304.dbo.aTable4Qtr2003
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200401.dbo.aTable1Qtr2004
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200402.dbo.aTable2Qtr2004
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200403.dbo.aTable3Qtr2004
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200404.dbo.aTable4Qtr2004
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200501.dbo.aTable1Qtr2005
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200502.dbo.aTable2Qtr2005
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200503.dbo.aTable3Qtr2005
UNION ALL SELECT [DateID], [KeyField2], [KeyField3], [KeyField4],
[KeyField5], [DataField1], [DataField2], [DataField3], [DataField4],
[DataField5]
FROM aDatabase_200504.dbo.aTable4Qtr2005
GO
"Tom Moreau" wrote:
> Please post your DDL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "DBA72" <DBA72@.discussions.microsoft.com> wrote in message
> news:85D05074-D45F-4AFD-BEA6-2A147AC16725@.microsoft.com...
> I have a problem with a partition view which is spread across various
> tables
> devided by quarter. When I run the select stmt listed below (at the
> bottom),
> I come up with strange results in terms of execution plan, statistics IO
> and
> profiler data.
> In both cases the data is returned the same, but case 2 takes longer than
> 1
> (results below). It seems that this is based on how the query is written -
> when it uses an "or 1=2" clause it is much faster.
> I have seen other posts where people point out that you should only look
> at
> the statistics IO for this and that the execution plan does not reflect
> the
> actually query execution. But in my case, the statistics IO is different
> as
> well.
> Can anyone explain this?
> (I am running CHECKPOINT, DBCC DROPCLEANBUFFERS, DBCC FREEPROCCACHE before
> each of these)
> --CASE 1
> SELECT *
> FROM [dbo].[PartitionView]
> WHERE [DateID] = 7925 OR 1 = 2
> Execution plan:
> Only access one table from the view
> Query Cost (relative to batch): Q1: 2.32%, Q3: 2.32%
> Sub tree Cost: 0.0372
> statistics IO:
> Table 'atable1Qtr2005'. Scan count 1, logical reads 26, physical reads 3,
> read-ahead reads 24.
> Profiler:
> Reads: 68
> --CASE 2
> SELECT *
> FROM [dbo].[PartitionView]
> WHERE [DateID] = 7925
> Execution plan:
> Access all tables from the view
> Query Cost (relative to batch): Q2: 12.69% Q4: 41.34%, Q5: 41.34%
> Sub tree Cost: Q2: 0.204, Q4: 0.665, Q5: 0.665
> statistics IO:
> Table 'atable4Qtr2005'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable3Qtr2005'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable2Qtr2005'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable1Qtr2005'. Scan count 1, logical reads 26, physical reads 3,
> read-ahead reads 24.
> Table 'atable4Qtr2004'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable3Qtr2004'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable2Qtr2004'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable1Qtr2004'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable4Qtr2003'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable3Qtr2003'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable2Qtr2003'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable1Qtr2003'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable4Qtr2002'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable3Qtr2002'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable2Qtr2002'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable1Qtr2002'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable4Qtr2001'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable3Qtr2001'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable2Qtr2001'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable1Qtr2001'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable4Qtr2000'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable3Qtr2000'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable2Qtr2000'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable1Qtr2000'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable4Qtr1999'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable3Qtr1999'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Table 'atable2Qtr1999'. Scan count 0, logical reads 0, physical reads 0,
> read-ahead reads 0.
> Profiler:
> Reads: 432
>
