Parameterized Expression: below

Showing posts with label below. Show all posts

Friday, March 30, 2012

Parsing XML

I am importing an xml document into a table in SQL Server (SS) 2005.
It imports into a single column (XML datatype). After that I use the
script below to parse it out into different rows in another table.
select xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
Ex_Station_Nu)[1]',
xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
Ex_Station_Do_IP)[1]',
xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
Ex_Station_Sy_Type)[1]',
from xmlimport
In the xml document there are more than one record. How do I loop
through the xml document? Or in other words change the [1] to [2]..
.
[3]...[4]...
I know it is easy, but for some reason I can't figure it out.
Anyone know?
Thanks!
chfranYou probably want to use the nodes() method. Can you post some sample XML
data?
Here's an idea if your data follows the format given:
CREATE TABLE #xmlimport (xmldata xml);
INSERT INTO #xmlimport (xmldata)
VALUES (N'<Lab_Exceptions>
<Lab_Exception_Data>
<A_Data>
<Ex_Station_Nu>100</Ex_Station_Nu>
<Ex_Station_Do_IP>192.168.10.1</Ex_Station_Do_IP>
<Ex_Station_Sy_Type>Type A</Ex_Station_Sy_Type>
</A_Data>
<A_Data>
<Ex_Station_Nu>200</Ex_Station_Nu>
<Ex_Station_Do_IP>192.168.10.2</Ex_Station_Do_IP>
<Ex_Station_Sy_Type>Type B</Ex_Station_Sy_Type>
</A_Data>
</Lab_Exception_Data>
</Lab_Exceptions>');
SELECT c.value('Ex_Station_Nu[1]', 'int') AS Ex_Station_Nu,
c.value('Ex_Station_Do_IP[1]', 'varchar(100)') AS Ex_Station_Do_IP,
c.value('Ex_Station_Sy_Type[1]', 'varchar(100)') AS Ex_Station_Sy_Type
FROM #xmlimport x
CROSS APPLY x.xmldata.nodes('/Lab_Exceptions/Lab_Exception_Data/A_Data') AS
T(c);
DROP TABLE #xmlimport;
"chfran" <chfran@.gmail.com> wrote in message
news:9a50a933-c824-4d71-8864-8be76493a22e@.s19g2000prg.googlegroups.com...
>I am importing an xml document into a table in SQL Server (SS) 2005.
> It imports into a single column (XML datatype). After that I use the
> script below to parse it out into different rows in another table.
> select xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
> Ex_Station_Nu)[1]',
> xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
> Ex_Station_Do_IP)[1]',
> xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
> Ex_Station_Sy_Type)[1]',
> from xmlimport
> In the xml document there are more than one record. How do I loop
> through the xml document? Or in other words change the [1] to [2]
..
> [3]...[4]...
> I know it is easy, but for some reason I can't figure it out.
> Anyone know?
> Thanks!
> chfran

Parsing XML

I am importing an xml document into a table in SQL Server (SS) 2005.
It imports into a single column (XML datatype). After that I use the
script below to parse it out into different rows in another table.
select xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
Ex_Station_Nu)[1]',
xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
Ex_Station_Do_IP)[1]',
xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
Ex_Station_Sy_Type)[1]',
from xmlimport
In the xml document there are more than one record. How do I loop
through the xml document? Or in other words change the [1] to [2]...
[3]...[4]...
I know it is easy, but for some reason I can't figure it out.
Anyone know?
Thanks!
chfran
You probably want to use the nodes() method. Can you post some sample XML
data?
Here's an idea if your data follows the format given:
CREATE TABLE #xmlimport (xmldata xml);
INSERT INTO #xmlimport (xmldata)
VALUES (N'<Lab_Exceptions>
<Lab_Exception_Data>
<A_Data>
<Ex_Station_Nu>100</Ex_Station_Nu>
<Ex_Station_Do_IP>192.168.10.1</Ex_Station_Do_IP>
<Ex_Station_Sy_Type>Type A</Ex_Station_Sy_Type>
</A_Data>
<A_Data>
<Ex_Station_Nu>200</Ex_Station_Nu>
<Ex_Station_Do_IP>192.168.10.2</Ex_Station_Do_IP>
<Ex_Station_Sy_Type>Type B</Ex_Station_Sy_Type>
</A_Data>
</Lab_Exception_Data>
</Lab_Exceptions>');
SELECT c.value('Ex_Station_Nu[1]', 'int') AS Ex_Station_Nu,
c.value('Ex_Station_Do_IP[1]', 'varchar(100)') AS Ex_Station_Do_IP,
c.value('Ex_Station_Sy_Type[1]', 'varchar(100)') AS Ex_Station_Sy_Type
FROM #xmlimport x
CROSS APPLY x.xmldata.nodes('/Lab_Exceptions/Lab_Exception_Data/A_Data') AS
T(c);
DROP TABLE #xmlimport;
"chfran" <chfran@.gmail.com> wrote in message
news:9a50a933-c824-4d71-8864-8be76493a22e@.s19g2000prg.googlegroups.com...
>I am importing an xml document into a table in SQL Server (SS) 2005.
> It imports into a single column (XML datatype). After that I use the
> script below to parse it out into different rows in another table.
> select xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
> Ex_Station_Nu)[1]',
> xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
> Ex_Station_Do_IP)[1]',
> xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
> Ex_Station_Sy_Type)[1]',
> from xmlimport
> In the xml document there are more than one record. How do I loop
> through the xml document? Or in other words change the [1] to [2]...
> [3]...[4]...
> I know it is easy, but for some reason I can't figure it out.
> Anyone know?
> Thanks!
> chfran

Wednesday, March 28, 2012

Pareser take wrong index when using @name runtime variable

Hi,
Today one of our developers came to me with a question which totaly
flabbergasted me. He had 2 little examples (see below) which lead to totally
different execution plans and I couldn't tell him why. Maybe anyone in this
newsgroups know the problem and can tell me what's going on.
The first SELECT runs less than one second because it uses an Index for the
index seek. The second SELECT however stunned me completetly because it does
an index scan on the Primary Key resulting in endless waiting.
The question is: Why does the Optimizer take the wrong index for the second
SELECT?
Paul Sinnema
DECLARE @.SVERARJOURNAL NUMERIC(21,0),
@.SFIRMA NUMERIC(21,0)
-- TIME : 0 SEC
SELECT SBUCHZEILE, XBUCHTEXT
FROM BUCHZEILE
WHERE SVERARJOURNAL = 15884
AND SFIRMA = 0
AND CEINZSTATUS = '40'
SET @.SFIRMA = 0
SET @.SVERARJOURNAL = 15884
-- RUNS INDEFENITLY
SELECT SBUCHZEILE,XBUCHTEXT
FROM BUCHZEILE
WHERE SVERARJOURNAL = @.SVERARJOURNAL
AND SFIRMA = @.SFIRMA
AND CEINZSTATUS = '40'Its called "parameter sniffing" ,google it and you will find the more info
Run those command between the queries
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
"PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
news:AD80F6D0-9E5F-4D85-9086-C43B1B57300C@.microsoft.com...
> Hi,
> Today one of our developers came to me with a question which totaly
> flabbergasted me. He had 2 little examples (see below) which lead to
> totally
> different execution plans and I couldn't tell him why. Maybe anyone in
> this
> newsgroups know the problem and can tell me what's going on.
> The first SELECT runs less than one second because it uses an Index for
> the
> index seek. The second SELECT however stunned me completetly because it
> does
> an index scan on the Primary Key resulting in endless waiting.
> The question is: Why does the Optimizer take the wrong index for the
> second
> SELECT?
> Paul Sinnema
> DECLARE @.SVERARJOURNAL NUMERIC(21,0),
> @.SFIRMA NUMERIC(21,0)
> -- TIME : 0 SEC
> SELECT SBUCHZEILE, XBUCHTEXT
> FROM BUCHZEILE
> WHERE SVERARJOURNAL = 15884
> AND SFIRMA = 0
> AND CEINZSTATUS = '40'
> SET @.SFIRMA = 0
> SET @.SVERARJOURNAL = 15884
> -- RUNS INDEFENITLY
> SELECT SBUCHZEILE,XBUCHTEXT
> FROM BUCHZEILE
> WHERE SVERARJOURNAL = @.SVERARJOURNAL
> AND SFIRMA = @.SFIRMA
> AND CEINZSTATUS = '40'
>|||There are a couple of possibilities. First, make sure the actual column
data types match the variable declarations. If these are different data
types, the expressions will not be sargable if the column value needs to be
converted to the variable declaration (numeric).
Also, when the optimizer generates the plan for the first query, the actual
values are known so the best plan can be generated. However, the values of
@.SVERARJOURNAL and @.SFIRMA are unknown in second query so the optimizer uses
distribution statistics of existing data to guess what actual values might
be provided. This might yield a different plan.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
news:AD80F6D0-9E5F-4D85-9086-C43B1B57300C@.microsoft.com...
> Hi,
> Today one of our developers came to me with a question which totaly
> flabbergasted me. He had 2 little examples (see below) which lead to
> totally
> different execution plans and I couldn't tell him why. Maybe anyone in
> this
> newsgroups know the problem and can tell me what's going on.
> The first SELECT runs less than one second because it uses an Index for
> the
> index seek. The second SELECT however stunned me completetly because it
> does
> an index scan on the Primary Key resulting in endless waiting.
> The question is: Why does the Optimizer take the wrong index for the
> second
> SELECT?
> Paul Sinnema
> DECLARE @.SVERARJOURNAL NUMERIC(21,0),
> @.SFIRMA NUMERIC(21,0)
> -- TIME : 0 SEC
> SELECT SBUCHZEILE, XBUCHTEXT
> FROM BUCHZEILE
> WHERE SVERARJOURNAL = 15884
> AND SFIRMA = 0
> AND CEINZSTATUS = '40'
> SET @.SFIRMA = 0
> SET @.SVERARJOURNAL = 15884
> -- RUNS INDEFENITLY
> SELECT SBUCHZEILE,XBUCHTEXT
> FROM BUCHZEILE
> WHERE SVERARJOURNAL = @.SVERARJOURNAL
> AND SFIRMA = @.SFIRMA
> AND CEINZSTATUS = '40'
>|||Thanks Uri and Dan,
You're probably right. I've also tried the following:
DECLARE @.COMMAND AS VARCHAR(200)
SET @.COMMAND = 'SELECT SBUCHZEILE, XBUCHTEXT' +
' FROM BUCHZEILE ' +
' WHERE SVERARJOURNAL = ' + CAST ( @.SVERARJOURNAL AS VARCHAR) +
' AND SFIRMA = ' + CAST(@.SFIRMA AS VARCHAR) +
' AND CEINZSTATUS = ''40'''
EXEC ( @.COMMAND )
It's a dirty trick but it results in the right index being taken. It
probably proves your suggestion right, that the parser is better at
determining which path to take with constants that with variables.
We could also use the INDEX HINT but that's not very nice. The indexes in
our Project are generated by a tool called Uniface (from Compuware). It
generates the indexes with a sequence number (i.e. BuchzeileI1, BuchzeileI2,
etc.). When one is removed from Uniface all indexes, behind the one removed,
become different names which could lead to dissaster for our INDEX HINTS.
Paul.
"Dan Guzman" wrote:
> There are a couple of possibilities. First, make sure the actual column
> data types match the variable declarations. If these are different data
> types, the expressions will not be sargable if the column value needs to be
> converted to the variable declaration (numeric).
> Also, when the optimizer generates the plan for the first query, the actual
> values are known so the best plan can be generated. However, the values of
> @.SVERARJOURNAL and @.SFIRMA are unknown in second query so the optimizer uses
> distribution statistics of existing data to guess what actual values might
> be provided. This might yield a different plan.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
> news:AD80F6D0-9E5F-4D85-9086-C43B1B57300C@.microsoft.com...
> > Hi,
> >
> > Today one of our developers came to me with a question which totaly
> > flabbergasted me. He had 2 little examples (see below) which lead to
> > totally
> > different execution plans and I couldn't tell him why. Maybe anyone in
> > this
> > newsgroups know the problem and can tell me what's going on.
> >
> > The first SELECT runs less than one second because it uses an Index for
> > the
> > index seek. The second SELECT however stunned me completetly because it
> > does
> > an index scan on the Primary Key resulting in endless waiting.
> >
> > The question is: Why does the Optimizer take the wrong index for the
> > second
> > SELECT?
> >
> > Paul Sinnema
> >
> > DECLARE @.SVERARJOURNAL NUMERIC(21,0),
> > @.SFIRMA NUMERIC(21,0)
> >
> > -- TIME : 0 SEC
> > SELECT SBUCHZEILE, XBUCHTEXT
> > FROM BUCHZEILE
> > WHERE SVERARJOURNAL = 15884
> > AND SFIRMA = 0
> > AND CEINZSTATUS = '40'
> >
> > SET @.SFIRMA = 0
> > SET @.SVERARJOURNAL = 15884
> >
> > -- RUNS INDEFENITLY
> > SELECT SBUCHZEILE,XBUCHTEXT
> > FROM BUCHZEILE
> > WHERE SVERARJOURNAL = @.SVERARJOURNAL
> > AND SFIRMA = @.SFIRMA
> > AND CEINZSTATUS = '40'
> >
>|||Nop, doesn't change a thing. See reply to Guzman!
"Uri Dimant" wrote:
> Its called "parameter sniffing" ,google it and you will find the more info
> Run those command between the queries
> DBCC FREEPROCCACHE
> DBCC DROPCLEANBUFFERS
>
> "PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
> news:AD80F6D0-9E5F-4D85-9086-C43B1B57300C@.microsoft.com...
> > Hi,
> >
> > Today one of our developers came to me with a question which totaly
> > flabbergasted me. He had 2 little examples (see below) which lead to
> > totally
> > different execution plans and I couldn't tell him why. Maybe anyone in
> > this
> > newsgroups know the problem and can tell me what's going on.
> >
> > The first SELECT runs less than one second because it uses an Index for
> > the
> > index seek. The second SELECT however stunned me completetly because it
> > does
> > an index scan on the Primary Key resulting in endless waiting.
> >
> > The question is: Why does the Optimizer take the wrong index for the
> > second
> > SELECT?
> >
> > Paul Sinnema
> >
> > DECLARE @.SVERARJOURNAL NUMERIC(21,0),
> > @.SFIRMA NUMERIC(21,0)
> >
> > -- TIME : 0 SEC
> > SELECT SBUCHZEILE, XBUCHTEXT
> > FROM BUCHZEILE
> > WHERE SVERARJOURNAL = 15884
> > AND SFIRMA = 0
> > AND CEINZSTATUS = '40'
> >
> > SET @.SFIRMA = 0
> > SET @.SVERARJOURNAL = 15884
> >
> > -- RUNS INDEFENITLY
> > SELECT SBUCHZEILE,XBUCHTEXT
> > FROM BUCHZEILE
> > WHERE SVERARJOURNAL = @.SVERARJOURNAL
> > AND SFIRMA = @.SFIRMA
> > AND CEINZSTATUS = '40'
> >
>
>|||Dan,
Is there a way to trick the Optimizer. We've already tried the following:
- Added an index for SVERARJOURNAL. No result.
- Tried changing the order of the WHERE Clause. No result.
- Use the INDEX HINT. Good result, for us bad solution.
Paul.
"Dan Guzman" wrote:
> There are a couple of possibilities. First, make sure the actual column
> data types match the variable declarations. If these are different data
> types, the expressions will not be sargable if the column value needs to be
> converted to the variable declaration (numeric).
> Also, when the optimizer generates the plan for the first query, the actual
> values are known so the best plan can be generated. However, the values of
> @.SVERARJOURNAL and @.SFIRMA are unknown in second query so the optimizer uses
> distribution statistics of existing data to guess what actual values might
> be provided. This might yield a different plan.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
> news:AD80F6D0-9E5F-4D85-9086-C43B1B57300C@.microsoft.com...
> > Hi,
> >
> > Today one of our developers came to me with a question which totaly
> > flabbergasted me. He had 2 little examples (see below) which lead to
> > totally
> > different execution plans and I couldn't tell him why. Maybe anyone in
> > this
> > newsgroups know the problem and can tell me what's going on.
> >
> > The first SELECT runs less than one second because it uses an Index for
> > the
> > index seek. The second SELECT however stunned me completetly because it
> > does
> > an index scan on the Primary Key resulting in endless waiting.
> >
> > The question is: Why does the Optimizer take the wrong index for the
> > second
> > SELECT?
> >
> > Paul Sinnema
> >
> > DECLARE @.SVERARJOURNAL NUMERIC(21,0),
> > @.SFIRMA NUMERIC(21,0)
> >
> > -- TIME : 0 SEC
> > SELECT SBUCHZEILE, XBUCHTEXT
> > FROM BUCHZEILE
> > WHERE SVERARJOURNAL = 15884
> > AND SFIRMA = 0
> > AND CEINZSTATUS = '40'
> >
> > SET @.SFIRMA = 0
> > SET @.SVERARJOURNAL = 15884
> >
> > -- RUNS INDEFENITLY
> > SELECT SBUCHZEILE,XBUCHTEXT
> > FROM BUCHZEILE
> > WHERE SVERARJOURNAL = @.SVERARJOURNAL
> > AND SFIRMA = @.SFIRMA
> > AND CEINZSTATUS = '40'
> >
>|||If the query is in a stored proc, try setting the stored procedure
parameters default values to the ones used for the 'good' plan. The
optimizer will use those values for plan generation instead guessing based
on distribution stats. If that doesn't work, post your table DDL,
including indexes.
CREATE PROC dbo_select_BUCHZEILE
@.SVERARJOURNAL NUMERIC(21,0) = 0.0,
@.SFIRMA NUMERIC(21,0) = 15884.0
AS
SELECT SBUCHZEILE,XBUCHTEXT
FROM BUCHZEILE
WHERE SVERARJOURNAL = @.SVERARJOURNAL
AND SFIRMA = @.SFIRMA
AND CEINZSTATUS = '40'
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
news:DD30F8F8-5DC7-43D5-84C3-1E2CFEEA60E0@.microsoft.com...
> Dan,
> Is there a way to trick the Optimizer. We've already tried the following:
> - Added an index for SVERARJOURNAL. No result.
> - Tried changing the order of the WHERE Clause. No result.
> - Use the INDEX HINT. Good result, for us bad solution.
> Paul.
>
> "Dan Guzman" wrote:
>> There are a couple of possibilities. First, make sure the actual column
>> data types match the variable declarations. If these are different data
>> types, the expressions will not be sargable if the column value needs to
>> be
>> converted to the variable declaration (numeric).
>> Also, when the optimizer generates the plan for the first query, the
>> actual
>> values are known so the best plan can be generated. However, the values
>> of
>> @.SVERARJOURNAL and @.SFIRMA are unknown in second query so the optimizer
>> uses
>> distribution statistics of existing data to guess what actual values
>> might
>> be provided. This might yield a different plan.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
>> news:AD80F6D0-9E5F-4D85-9086-C43B1B57300C@.microsoft.com...
>> > Hi,
>> >
>> > Today one of our developers came to me with a question which totaly
>> > flabbergasted me. He had 2 little examples (see below) which lead to
>> > totally
>> > different execution plans and I couldn't tell him why. Maybe anyone in
>> > this
>> > newsgroups know the problem and can tell me what's going on.
>> >
>> > The first SELECT runs less than one second because it uses an Index for
>> > the
>> > index seek. The second SELECT however stunned me completetly because it
>> > does
>> > an index scan on the Primary Key resulting in endless waiting.
>> >
>> > The question is: Why does the Optimizer take the wrong index for the
>> > second
>> > SELECT?
>> >
>> > Paul Sinnema
>> >
>> > DECLARE @.SVERARJOURNAL NUMERIC(21,0),
>> > @.SFIRMA NUMERIC(21,0)
>> >
>> > -- TIME : 0 SEC
>> > SELECT SBUCHZEILE, XBUCHTEXT
>> > FROM BUCHZEILE
>> > WHERE SVERARJOURNAL = 15884
>> > AND SFIRMA = 0
>> > AND CEINZSTATUS = '40'
>> >
>> > SET @.SFIRMA = 0
>> > SET @.SVERARJOURNAL = 15884
>> >
>> > -- RUNS INDEFENITLY
>> > SELECT SBUCHZEILE,XBUCHTEXT
>> > FROM BUCHZEILE
>> > WHERE SVERARJOURNAL = @.SVERARJOURNAL
>> > AND SFIRMA = @.SFIRMA
>> > AND CEINZSTATUS = '40'
>> >|||I addition to Dan's great explanation , you can declare a local variable to
perevent parameter sniffing
CREATE PROC dbo_select_BUCHZEILE
@.SVERARJOURNAL NUMERIC(21,0) ,
@.SFIRMA NUMERIC(21,0)
AS
DECLARE @.Local_SVERARJOURNAL NUMERIC(21,0)
DECLARE @.Local_SFIRMA NUMERIC(21,0)
SET @.Local_SVERARJOURNAL =@.SVERARJOURNAL
SET @.Local_SFIRMA =@.SFIRMA
SELECT SBUCHZEILE,XBUCHTEXT
FROM BUCHZEILE
WHERE SVERARJOURNAL = @.Local_SVERARJOURNAL
AND SFIRMA = @.Local_SFIRMA
AND CEINZSTATUS = '40'
> GO
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:D7B098AA-8C4D-482C-8C5C-393A2BF401A5@.microsoft.com...
> If the query is in a stored proc, try setting the stored procedure
> parameters default values to the ones used for the 'good' plan. The
> optimizer will use those values for plan generation instead guessing based
> on distribution stats. If that doesn't work, post your table DDL,
> including indexes.
> CREATE PROC dbo_select_BUCHZEILE
> @.SVERARJOURNAL NUMERIC(21,0) = 0.0,
> @.SFIRMA NUMERIC(21,0) = 15884.0
> AS
> SELECT SBUCHZEILE,XBUCHTEXT
> FROM BUCHZEILE
> WHERE SVERARJOURNAL = @.SVERARJOURNAL
> AND SFIRMA = @.SFIRMA
> AND CEINZSTATUS = '40'
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
> news:DD30F8F8-5DC7-43D5-84C3-1E2CFEEA60E0@.microsoft.com...
>> Dan,
>> Is there a way to trick the Optimizer. We've already tried the following:
>> - Added an index for SVERARJOURNAL. No result.
>> - Tried changing the order of the WHERE Clause. No result.
>> - Use the INDEX HINT. Good result, for us bad solution.
>> Paul.
>>
>> "Dan Guzman" wrote:
>> There are a couple of possibilities. First, make sure the actual column
>> data types match the variable declarations. If these are different data
>> types, the expressions will not be sargable if the column value needs to
>> be
>> converted to the variable declaration (numeric).
>> Also, when the optimizer generates the plan for the first query, the
>> actual
>> values are known so the best plan can be generated. However, the values
>> of
>> @.SVERARJOURNAL and @.SFIRMA are unknown in second query so the optimizer
>> uses
>> distribution statistics of existing data to guess what actual values
>> might
>> be provided. This might yield a different plan.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
>> news:AD80F6D0-9E5F-4D85-9086-C43B1B57300C@.microsoft.com...
>> > Hi,
>> >
>> > Today one of our developers came to me with a question which totaly
>> > flabbergasted me. He had 2 little examples (see below) which lead to
>> > totally
>> > different execution plans and I couldn't tell him why. Maybe anyone in
>> > this
>> > newsgroups know the problem and can tell me what's going on.
>> >
>> > The first SELECT runs less than one second because it uses an Index
>> > for
>> > the
>> > index seek. The second SELECT however stunned me completetly because
>> > it
>> > does
>> > an index scan on the Primary Key resulting in endless waiting.
>> >
>> > The question is: Why does the Optimizer take the wrong index for the
>> > second
>> > SELECT?
>> >
>> > Paul Sinnema
>> >
>> > DECLARE @.SVERARJOURNAL NUMERIC(21,0),
>> > @.SFIRMA NUMERIC(21,0)
>> >
>> > -- TIME : 0 SEC
>> > SELECT SBUCHZEILE, XBUCHTEXT
>> > FROM BUCHZEILE
>> > WHERE SVERARJOURNAL = 15884
>> > AND SFIRMA = 0
>> > AND CEINZSTATUS = '40'
>> >
>> > SET @.SFIRMA = 0
>> > SET @.SVERARJOURNAL = 15884
>> >
>> > -- RUNS INDEFENITLY
>> > SELECT SBUCHZEILE,XBUCHTEXT
>> > FROM BUCHZEILE
>> > WHERE SVERARJOURNAL = @.SVERARJOURNAL
>> > AND SFIRMA = @.SFIRMA
>> > AND CEINZSTATUS = '40'
>> >
>|||Good point, Uri. Paul's initial script used local variables instead of proc
parameters so I didn't go there. To clarify the issue for Paul, SQL Server
'sniffs' parameter values as follows:
1) When stored procedure parameters are used directly in the WHERE clause,
SQL Server uses the default parameters to generate the execution plan. If
you don't specify default values, SQL Server uses NULL which is often the
unusual case and can result in a poor plan for non-trivial queries.
Specifying typical values as parameter defaults instead of NULL will
generate a plan that is good for data of similar cardinality.
2) When local variables are used, SQL Server estimates what values may be
based on table index and column statistics. This is often better than #1
but may perform poorly when unusual values are specified.
3) With constants, the actual values are known so the best execution plan
is generated.
SQL server 2005 introduces features such as statement-level recompilation
that help address this issue.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23cNvmdjAHHA.1224@.TK2MSFTNGP04.phx.gbl...
>I addition to Dan's great explanation , you can declare a local variable to
>perevent parameter sniffing
> CREATE PROC dbo_select_BUCHZEILE
> @.SVERARJOURNAL NUMERIC(21,0) ,
> @.SFIRMA NUMERIC(21,0)
> AS
> DECLARE @.Local_SVERARJOURNAL NUMERIC(21,0)
> DECLARE @.Local_SFIRMA NUMERIC(21,0)
> SET @.Local_SVERARJOURNAL =@.SVERARJOURNAL
> SET @.Local_SFIRMA =@.SFIRMA
> SELECT SBUCHZEILE,XBUCHTEXT
> FROM BUCHZEILE
> WHERE SVERARJOURNAL = @.Local_SVERARJOURNAL
> AND SFIRMA = @.Local_SFIRMA
> AND CEINZSTATUS = '40'
>> GO
>
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:D7B098AA-8C4D-482C-8C5C-393A2BF401A5@.microsoft.com...
>> If the query is in a stored proc, try setting the stored procedure
>> parameters default values to the ones used for the 'good' plan. The
>> optimizer will use those values for plan generation instead guessing
>> based on distribution stats. If that doesn't work, post your table DDL,
>> including indexes.
>> CREATE PROC dbo_select_BUCHZEILE
>> @.SVERARJOURNAL NUMERIC(21,0) = 0.0,
>> @.SFIRMA NUMERIC(21,0) = 15884.0
>> AS
>> SELECT SBUCHZEILE,XBUCHTEXT
>> FROM BUCHZEILE
>> WHERE SVERARJOURNAL = @.SVERARJOURNAL
>> AND SFIRMA = @.SFIRMA
>> AND CEINZSTATUS = '40'
>> GO
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
>> news:DD30F8F8-5DC7-43D5-84C3-1E2CFEEA60E0@.microsoft.com...
>> Dan,
>> Is there a way to trick the Optimizer. We've already tried the
>> following:
>> - Added an index for SVERARJOURNAL. No result.
>> - Tried changing the order of the WHERE Clause. No result.
>> - Use the INDEX HINT. Good result, for us bad solution.
>> Paul.
>>
>> "Dan Guzman" wrote:
>> There are a couple of possibilities. First, make sure the actual
>> column
>> data types match the variable declarations. If these are different
>> data
>> types, the expressions will not be sargable if the column value needs
>> to be
>> converted to the variable declaration (numeric).
>> Also, when the optimizer generates the plan for the first query, the
>> actual
>> values are known so the best plan can be generated. However, the
>> values of
>> @.SVERARJOURNAL and @.SFIRMA are unknown in second query so the optimizer
>> uses
>> distribution statistics of existing data to guess what actual values
>> might
>> be provided. This might yield a different plan.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
>> news:AD80F6D0-9E5F-4D85-9086-C43B1B57300C@.microsoft.com...
>> > Hi,
>> >
>> > Today one of our developers came to me with a question which totaly
>> > flabbergasted me. He had 2 little examples (see below) which lead to
>> > totally
>> > different execution plans and I couldn't tell him why. Maybe anyone
>> > in
>> > this
>> > newsgroups know the problem and can tell me what's going on.
>> >
>> > The first SELECT runs less than one second because it uses an Index
>> > for
>> > the
>> > index seek. The second SELECT however stunned me completetly because
>> > it
>> > does
>> > an index scan on the Primary Key resulting in endless waiting.
>> >
>> > The question is: Why does the Optimizer take the wrong index for the
>> > second
>> > SELECT?
>> >
>> > Paul Sinnema
>> >
>> > DECLARE @.SVERARJOURNAL NUMERIC(21,0),
>> > @.SFIRMA NUMERIC(21,0)
>> >
>> > -- TIME : 0 SEC
>> > SELECT SBUCHZEILE, XBUCHTEXT
>> > FROM BUCHZEILE
>> > WHERE SVERARJOURNAL = 15884
>> > AND SFIRMA = 0
>> > AND CEINZSTATUS = '40'
>> >
>> > SET @.SFIRMA = 0
>> > SET @.SVERARJOURNAL = 15884
>> >
>> > -- RUNS INDEFENITLY
>> > SELECT SBUCHZEILE,XBUCHTEXT
>> > FROM BUCHZEILE
>> > WHERE SVERARJOURNAL = @.SVERARJOURNAL
>> > AND SFIRMA = @.SFIRMA
>> > AND CEINZSTATUS = '40'
>> >
>>
>|||Guzman, Uri
Thanks guys for the help. The first example of Guzman takes the right index.
The second one of Uri doesn't (i.e. we have the old problem back again). What
I didn't know is that the presented queries came from a stored procedure the
programmer had written. He added a default value for the @.SVERARJOURNAL
parameter and voila, it works as desired.
Thanks again. We've learned something today.
Paul.
"Dan Guzman" wrote:
> Good point, Uri. Paul's initial script used local variables instead of proc
> parameters so I didn't go there. To clarify the issue for Paul, SQL Server
> 'sniffs' parameter values as follows:
> 1) When stored procedure parameters are used directly in the WHERE clause,
> SQL Server uses the default parameters to generate the execution plan. If
> you don't specify default values, SQL Server uses NULL which is often the
> unusual case and can result in a poor plan for non-trivial queries.
> Specifying typical values as parameter defaults instead of NULL will
> generate a plan that is good for data of similar cardinality.
> 2) When local variables are used, SQL Server estimates what values may be
> based on table index and column statistics. This is often better than #1
> but may perform poorly when unusual values are specified.
> 3) With constants, the actual values are known so the best execution plan
> is generated.
> SQL server 2005 introduces features such as statement-level recompilation
> that help address this issue.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23cNvmdjAHHA.1224@.TK2MSFTNGP04.phx.gbl...
> >I addition to Dan's great explanation , you can declare a local variable to
> >perevent parameter sniffing
> >
> > CREATE PROC dbo_select_BUCHZEILE
> > @.SVERARJOURNAL NUMERIC(21,0) ,
> > @.SFIRMA NUMERIC(21,0)
> > AS
> >
> > DECLARE @.Local_SVERARJOURNAL NUMERIC(21,0)
> > DECLARE @.Local_SFIRMA NUMERIC(21,0)
> >
> > SET @.Local_SVERARJOURNAL =@.SVERARJOURNAL
> > SET @.Local_SFIRMA =@.SFIRMA
> >
> > SELECT SBUCHZEILE,XBUCHTEXT
> > FROM BUCHZEILE
> > WHERE SVERARJOURNAL = @.Local_SVERARJOURNAL
> > AND SFIRMA = @.Local_SFIRMA
> > AND CEINZSTATUS = '40'
> >> GO
> >
> >
> >
> >
> > "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> > news:D7B098AA-8C4D-482C-8C5C-393A2BF401A5@.microsoft.com...
> >> If the query is in a stored proc, try setting the stored procedure
> >> parameters default values to the ones used for the 'good' plan. The
> >> optimizer will use those values for plan generation instead guessing
> >> based on distribution stats. If that doesn't work, post your table DDL,
> >> including indexes.
> >>
> >> CREATE PROC dbo_select_BUCHZEILE
> >> @.SVERARJOURNAL NUMERIC(21,0) = 0.0,
> >> @.SFIRMA NUMERIC(21,0) = 15884.0
> >> AS
> >> SELECT SBUCHZEILE,XBUCHTEXT
> >> FROM BUCHZEILE
> >> WHERE SVERARJOURNAL = @.SVERARJOURNAL
> >> AND SFIRMA = @.SFIRMA
> >> AND CEINZSTATUS = '40'
> >> GO
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >>
> >> "PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
> >> news:DD30F8F8-5DC7-43D5-84C3-1E2CFEEA60E0@.microsoft.com...
> >> Dan,
> >>
> >> Is there a way to trick the Optimizer. We've already tried the
> >> following:
> >> - Added an index for SVERARJOURNAL. No result.
> >> - Tried changing the order of the WHERE Clause. No result.
> >> - Use the INDEX HINT. Good result, for us bad solution.
> >>
> >> Paul.
> >>
> >>
> >> "Dan Guzman" wrote:
> >>
> >> There are a couple of possibilities. First, make sure the actual
> >> column
> >> data types match the variable declarations. If these are different
> >> data
> >> types, the expressions will not be sargable if the column value needs
> >> to be
> >> converted to the variable declaration (numeric).
> >>
> >> Also, when the optimizer generates the plan for the first query, the
> >> actual
> >> values are known so the best plan can be generated. However, the
> >> values of
> >> @.SVERARJOURNAL and @.SFIRMA are unknown in second query so the optimizer
> >> uses
> >> distribution statistics of existing data to guess what actual values
> >> might
> >> be provided. This might yield a different plan.
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >>
> >> "PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
> >> news:AD80F6D0-9E5F-4D85-9086-C43B1B57300C@.microsoft.com...
> >> > Hi,
> >> >
> >> > Today one of our developers came to me with a question which totaly
> >> > flabbergasted me. He had 2 little examples (see below) which lead to
> >> > totally
> >> > different execution plans and I couldn't tell him why. Maybe anyone
> >> > in
> >> > this
> >> > newsgroups know the problem and can tell me what's going on.
> >> >
> >> > The first SELECT runs less than one second because it uses an Index
> >> > for
> >> > the
> >> > index seek. The second SELECT however stunned me completetly because
> >> > it
> >> > does
> >> > an index scan on the Primary Key resulting in endless waiting.
> >> >
> >> > The question is: Why does the Optimizer take the wrong index for the
> >> > second
> >> > SELECT?
> >> >
> >> > Paul Sinnema
> >> >
> >> > DECLARE @.SVERARJOURNAL NUMERIC(21,0),
> >> > @.SFIRMA NUMERIC(21,0)
> >> >
> >> > -- TIME : 0 SEC
> >> > SELECT SBUCHZEILE, XBUCHTEXT
> >> > FROM BUCHZEILE
> >> > WHERE SVERARJOURNAL = 15884
> >> > AND SFIRMA = 0
> >> > AND CEINZSTATUS = '40'
> >> >
> >> > SET @.SFIRMA = 0
> >> > SET @.SVERARJOURNAL = 15884
> >> >
> >> > -- RUNS INDEFENITLY
> >> > SELECT SBUCHZEILE,XBUCHTEXT
> >> > FROM BUCHZEILE
> >> > WHERE SVERARJOURNAL = @.SVERARJOURNAL
> >> > AND SFIRMA = @.SFIRMA
> >> > AND CEINZSTATUS = '40'
> >> >
> >>
> >>
> >
> >
>|||I'm glad we could help you out. Below is a link to the post by Bart Duncan
of Microsoft that describes the behavior in more detail.
http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/a6ae4020b1e3621/0ff9e6e72122e1fb?lnk=st&rnum=1#0ff9e6e72122e1fb
Hope this helps.
Dan Guzman
SQL Server MVP
"PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
news:6D5B834D-3D4A-41E4-87B7-BD43F7DD32FF@.microsoft.com...
> Guzman, Uri
> Thanks guys for the help. The first example of Guzman takes the right
> index.
> The second one of Uri doesn't (i.e. we have the old problem back again).
> What
> I didn't know is that the presented queries came from a stored procedure
> the
> programmer had written. He added a default value for the @.SVERARJOURNAL
> parameter and voila, it works as desired.
> Thanks again. We've learned something today.
> Paul.
> "Dan Guzman" wrote:
>> Good point, Uri. Paul's initial script used local variables instead of
>> proc
>> parameters so I didn't go there. To clarify the issue for Paul, SQL
>> Server
>> 'sniffs' parameter values as follows:
>> 1) When stored procedure parameters are used directly in the WHERE
>> clause,
>> SQL Server uses the default parameters to generate the execution plan.
>> If
>> you don't specify default values, SQL Server uses NULL which is often the
>> unusual case and can result in a poor plan for non-trivial queries.
>> Specifying typical values as parameter defaults instead of NULL will
>> generate a plan that is good for data of similar cardinality.
>> 2) When local variables are used, SQL Server estimates what values may
>> be
>> based on table index and column statistics. This is often better than #1
>> but may perform poorly when unusual values are specified.
>> 3) With constants, the actual values are known so the best execution
>> plan
>> is generated.
>> SQL server 2005 introduces features such as statement-level recompilation
>> that help address this issue.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23cNvmdjAHHA.1224@.TK2MSFTNGP04.phx.gbl...
>> >I addition to Dan's great explanation , you can declare a local variable
>> >to
>> >perevent parameter sniffing
>> >
>> > CREATE PROC dbo_select_BUCHZEILE
>> > @.SVERARJOURNAL NUMERIC(21,0) ,
>> > @.SFIRMA NUMERIC(21,0)
>> > AS
>> >
>> > DECLARE @.Local_SVERARJOURNAL NUMERIC(21,0)
>> > DECLARE @.Local_SFIRMA NUMERIC(21,0)
>> >
>> > SET @.Local_SVERARJOURNAL =@.SVERARJOURNAL
>> > SET @.Local_SFIRMA =@.SFIRMA
>> >
>> > SELECT SBUCHZEILE,XBUCHTEXT
>> > FROM BUCHZEILE
>> > WHERE SVERARJOURNAL = @.Local_SVERARJOURNAL
>> > AND SFIRMA = @.Local_SFIRMA
>> > AND CEINZSTATUS = '40'
>> >> GO
>> >
>> >
>> >
>> >
>> > "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> > news:D7B098AA-8C4D-482C-8C5C-393A2BF401A5@.microsoft.com...
>> >> If the query is in a stored proc, try setting the stored procedure
>> >> parameters default values to the ones used for the 'good' plan. The
>> >> optimizer will use those values for plan generation instead guessing
>> >> based on distribution stats. If that doesn't work, post your table
>> >> DDL,
>> >> including indexes.
>> >>
>> >> CREATE PROC dbo_select_BUCHZEILE
>> >> @.SVERARJOURNAL NUMERIC(21,0) = 0.0,
>> >> @.SFIRMA NUMERIC(21,0) = 15884.0
>> >> AS
>> >> SELECT SBUCHZEILE,XBUCHTEXT
>> >> FROM BUCHZEILE
>> >> WHERE SVERARJOURNAL = @.SVERARJOURNAL
>> >> AND SFIRMA = @.SFIRMA
>> >> AND CEINZSTATUS = '40'
>> >> GO
>> >>
>> >> --
>> >> Hope this helps.
>> >>
>> >> Dan Guzman
>> >> SQL Server MVP
>> >>
>> >> "PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
>> >> news:DD30F8F8-5DC7-43D5-84C3-1E2CFEEA60E0@.microsoft.com...
>> >> Dan,
>> >>
>> >> Is there a way to trick the Optimizer. We've already tried the
>> >> following:
>> >> - Added an index for SVERARJOURNAL. No result.
>> >> - Tried changing the order of the WHERE Clause. No result.
>> >> - Use the INDEX HINT. Good result, for us bad solution.
>> >>
>> >> Paul.
>> >>
>> >>
>> >> "Dan Guzman" wrote:
>> >>
>> >> There are a couple of possibilities. First, make sure the actual
>> >> column
>> >> data types match the variable declarations. If these are different
>> >> data
>> >> types, the expressions will not be sargable if the column value
>> >> needs
>> >> to be
>> >> converted to the variable declaration (numeric).
>> >>
>> >> Also, when the optimizer generates the plan for the first query, the
>> >> actual
>> >> values are known so the best plan can be generated. However, the
>> >> values of
>> >> @.SVERARJOURNAL and @.SFIRMA are unknown in second query so the
>> >> optimizer
>> >> uses
>> >> distribution statistics of existing data to guess what actual values
>> >> might
>> >> be provided. This might yield a different plan.
>> >>
>> >> --
>> >> Hope this helps.
>> >>
>> >> Dan Guzman
>> >> SQL Server MVP
>> >>
>> >> "PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:AD80F6D0-9E5F-4D85-9086-C43B1B57300C@.microsoft.com...
>> >> > Hi,
>> >> >
>> >> > Today one of our developers came to me with a question which
>> >> > totaly
>> >> > flabbergasted me. He had 2 little examples (see below) which lead
>> >> > to
>> >> > totally
>> >> > different execution plans and I couldn't tell him why. Maybe
>> >> > anyone
>> >> > in
>> >> > this
>> >> > newsgroups know the problem and can tell me what's going on.
>> >> >
>> >> > The first SELECT runs less than one second because it uses an
>> >> > Index
>> >> > for
>> >> > the
>> >> > index seek. The second SELECT however stunned me completetly
>> >> > because
>> >> > it
>> >> > does
>> >> > an index scan on the Primary Key resulting in endless waiting.
>> >> >
>> >> > The question is: Why does the Optimizer take the wrong index for
>> >> > the
>> >> > second
>> >> > SELECT?
>> >> >
>> >> > Paul Sinnema
>> >> >
>> >> > DECLARE @.SVERARJOURNAL NUMERIC(21,0),
>> >> > @.SFIRMA NUMERIC(21,0)
>> >> >
>> >> > -- TIME : 0 SEC
>> >> > SELECT SBUCHZEILE, XBUCHTEXT
>> >> > FROM BUCHZEILE
>> >> > WHERE SVERARJOURNAL = 15884
>> >> > AND SFIRMA = 0
>> >> > AND CEINZSTATUS = '40'
>> >> >
>> >> > SET @.SFIRMA = 0
>> >> > SET @.SVERARJOURNAL = 15884
>> >> >
>> >> > -- RUNS INDEFENITLY
>> >> > SELECT SBUCHZEILE,XBUCHTEXT
>> >> > FROM BUCHZEILE
>> >> > WHERE SVERARJOURNAL = @.SVERARJOURNAL
>> >> > AND SFIRMA = @.SFIRMA
>> >> > AND CEINZSTATUS = '40'
>> >> >
>> >>
>> >>
>> >
>> >

Wednesday, March 21, 2012

Partitioned View performance

What is wrong with my partitioned view?
I have split a table into partitioned view by month (see below).
Check the following query information
--this is the original table
select count(*) from activitydetailbackup
where [datetime] between '1/2/2006' and '1/26/2006'
--takes 35 seconds
--this is run against the view
select count(*) from activitydetailbackup_view
where [datetime] between '1/2/2006' and '1/26/2006'
--takes over 6 minutes
SET STATISTICS IO ON
select count(*) from activitydetailbackup_view
where [datetime] between '1/2/2006' and '1/26/2006'
SET STATISTICS IO OFF
--shows the following
Table 'ActivityDetailBackup200603'. Scan count 4, logical reads 76860,
physical reads 0, read-ahead reads 76865.
Table 'ActivityDetailBackup200602'. Scan count 4, logical reads 2366,
physical reads 0, read-ahead reads 2365.
Table 'ActivityDetailBackup200601'. Scan count 4, logical reads 73249,
physical reads 0, read-ahead reads 73250.
Table 'ActivityDetailBackup200512'. Scan count 4, logical reads 42978,
physical reads 67, read-ahead reads 42930.
Table 'ActivityDetailBackup200511'. Scan count 4, logical reads 44662,
physical reads 67, read-ahead reads 44631.
Table 'ActivityDetailBackup200510'. Scan count 4, logical reads 41996,
physical reads 0, read-ahead reads 41996.
Table 'ActivityDetailBackup200509'. Scan count 4, logical reads 36542,
physical reads 0, read-ahead reads 36546.
Table 'ActivityDetailBackup200508'. Scan count 4, logical reads 41171,
physical reads 0, read-ahead reads 41175.
Table 'ActivityDetailBackup200507'. Scan count 4, logical reads 38037,
physical reads 66, read-ahead reads 38269.
Table 'ActivityDetailBackup200506'. Scan count 4, logical reads 38804,
physical reads 65, read-ahead reads 39051.
Table 'ActivityDetailBackup200505'. Scan count 4, logical reads 40052,
physical reads 70, read-ahead reads 40332.
Table 'ActivityDetailBackup200504'. Scan count 4, logical reads 37436,
physical reads 70, read-ahead reads 37693.
Table 'ActivityDetailBackup200503'. Scan count 4, logical reads 38750,
physical reads 66, read-ahead reads 38890.
Table 'ActivityDetailBackup200502'. Scan count 4, logical reads 32304,
physical reads 73, read-ahead reads 32451.
Table 'ActivityDetailBackup200412'. Scan count 4, logical reads 33051,
physical reads 72, read-ahead reads 33131.
Table 'ActivityDetailBackup200411'. Scan count 4, logical reads 36257,
physical reads 69, read-ahead reads 36429.
Table 'ActivityDetailBackup200410'. Scan count 4, logical reads 24012,
physical reads 69, read-ahead reads 24149.
Table 'ActivityDetailBackup200409'. Scan count 4, logical reads 32, physical
reads 1, read-ahead reads 31.
Each table is created as follows:
CREATE TABLE [dbo].[ActivityDetailBackupYYYYMM](
[ActivityID] [uniqueidentifier] NOT NULL,
[DateTime] [datetime] NOT NULL,
[PageName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Querystring] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FormVariables] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SessionVariables] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ServerVariables] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CustomValue] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [ActivityIDDateYYYYMM] PRIMARY KEY CLUSTERED
(
[ActivityID] ASC,
[DateTime] ASC
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
ALTER TABLE [dbo].[ActivityDetailBackup200410] WITH NOCHECK ADD CHECK
(([DateTime] >= convert(datetime,'10/1/2004') and [DateTime] <
convert(datetime,'11/1/2004')))
View created as follows:
CREATE VIEW [dbo].[ActivityDetailBackup_view]
AS
SELECT * FROM ActivityDetailBackup200409
UNION ALL
SELECT * FROM ActivityDetailBackup200410
UNION ALL
SELECT * FROM ActivityDetailBackup200411
UNION ALL
SELECT * FROM ActivityDetailBackup200412
UNION ALL
SELECT * FROM ActivityDetailBackup200502
UNION ALL
SELECT * FROM ActivityDetailBackup200503
UNION ALL
SELECT * FROM ActivityDetailBackup200504
UNION ALL
SELECT * FROM ActivityDetailBackup200505
UNION ALL
SELECT * FROM ActivityDetailBackup200506
UNION ALL
SELECT * FROM ActivityDetailBackup200507
UNION ALL
SELECT * FROM ActivityDetailBackup200508
UNION ALL
SELECT * FROM ActivityDetailBackup200509
UNION ALL
SELECT * FROM ActivityDetailBackup200510
UNION ALL
SELECT * FROM ActivityDetailBackup200511
UNION ALL
SELECT * FROM ActivityDetailBackup200512
UNION ALL
SELECT * FROM ActivityDetailBackup200601
UNION ALL
SELECT * FROM ActivityDetailBackup200602
UNION ALL
SELECT * FROM ActivityDetailBackup200603
UNION ALL
SELECT * FROM ActivityDetailBackup200604
Haroldsthe reason might be because of adding the constraint with no check..
so it has to go and check the existing data in all the tables to find out if
rows exists.|||My understanding of the NOCHECK in the ALTER TABLE statement just means to
set the constraint without validating the data currently on the table.
--
Harolds
"Omnibuzz" wrote:

> the reason might be because of adding the constraint with no check..
> so it has to go and check the existing data in all the tables to find out
if
> rows exists.|||your understanding is right. So when you select from the view, it cannot rel
y
on the contraint you have created and has to go and check all the tables.
This is what I believe. If you can try this. create the constraints (without
the no check option and run it
"Harolds" wrote:
> My understanding of the NOCHECK in the ALTER TABLE statement just means to
> set the constraint without validating the data currently on the table.
> --
> Harolds
>
> "Omnibuzz" wrote:
>|||This did the trick.
Thanks for the help,
--
Harolds
"Omnibuzz" wrote:

> the reason might be because of adding the constraint with no check..
> so it has to go and check the existing data in all the tables to find out
if
> rows exists.

Partitioned View Inconsistencies

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
>

Friday, March 30, 2012

Parsing XML

Parsing XML

Wednesday, March 28, 2012

Pareser take wrong index when using @name runtime variable

Pareser take wrong index when using @name runtime variable

Pareser take wrong index when using @name runtime variable

Wednesday, March 21, 2012

Partitioned View performance

Partitioned View Inconsistencies

Parameterized Expression

Blog Archive

About Me