Partial Success

Log Explorer from Lumigent absolutely rocks. I was able to view the three
major delete transactions, generate sql scripts for them, and run them on
the db. I've now got files back in sharepoint.
The sharepoint strucure does not look like the users are saying it should,
so I'm either executing the scripts in the wrong order or they're not
shooting me straight. But I can deal with that. ;-)
Thanks a million for all of your help.
"Jack" <anonymous@.microsoft.com> wrote in message
news:%23N8QDhV7GHA.4604@.TK2MSFTNGP03.phx.gbl...
>I goofed and did not have a maintenance plan running. Someone deleted a
>folder in Sharepoint with thousands of documents.
> Since no backup has been run, the transaction log has not been truncated.
> Is there any way to roll back all transactions since Monday, or am I
> hosed?
>
I'm happy for you, Jack. :-)
I'm also a bit surprised that the transaction still existed in the log. Either you were *very* lucky
that the log records are still around. Or, a db backup was actually taken of the database at some
point in time. In any case, I have a feeling that you will look over your backup strategy now... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jack" <anonymous@.microsoft.com> wrote in message news:OKGZU8Y7GHA.3836@.TK2MSFTNGP02.phx.gbl...
> Log Explorer from Lumigent absolutely rocks. I was able to view the three major delete
> transactions, generate sql scripts for them, and run them on the db. I've now got files back in
> sharepoint.
> The sharepoint strucure does not look like the users are saying it should, so I'm either executing
> the scripts in the wrong order or they're not shooting me straight. But I can deal with that.
> ;-)
> Thanks a million for all of your help.
>
> "Jack" <anonymous@.microsoft.com> wrote in message news:%23N8QDhV7GHA.4604@.TK2MSFTNGP03.phx.gbl...
>

Partial Success

Log Explorer from Lumigent absolutely rocks. I was able to view the three
major delete transactions, generate sql scripts for them, and run them on
the db. I've now got files back in sharepoint.
The sharepoint strucure does not look like the users are saying it should,
so I'm either executing the scripts in the wrong order or they're not
shooting me straight. But I can deal with that. ;-)
Thanks a million for all of your help.
"Jack" <anonymous@.microsoft.com> wrote in message
news:%23N8QDhV7GHA.4604@.TK2MSFTNGP03.phx.gbl...
>I goofed and did not have a maintenance plan running. Someone deleted a
>folder in Sharepoint with thousands of documents.
> Since no backup has been run, the transaction log has not been truncated.
> Is there any way to roll back all transactions since Monday, or am I
> hosed?
>I'm happy for you, Jack. :-)
I'm also a bit surprised that the transaction still existed in the log. Eith
er you were *very* lucky
that the log records are still around. Or, a db backup was actually taken of
the database at some
point in time. In any case, I have a feeling that you will look over your ba
ckup strategy now... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jack" <anonymous@.microsoft.com> wrote in message news:OKGZU8Y7GHA.3836@.TK2MSFTNGP02.phx.gbl
..
> Log Explorer from Lumigent absolutely rocks. I was able to view the three
major delete
> transactions, generate sql scripts for them, and run them on the db. I've
now got files back in
> sharepoint.
> The sharepoint strucure does not look like the users are saying it should,
so I'm either executing
> the scripts in the wrong order or they're not shooting me straight. But I
can deal with that.
> ;-)
> Thanks a million for all of your help.
>
> "Jack" <anonymous@.microsoft.com> wrote in message news:%23N8QDhV7GHA.4604@.
TK2MSFTNGP03.phx.gbl...
>

Partial and Full Replication

Hi,
Is it possible to setup full replication of a database to run nightly
as well as partial replication of only a couple of tables on an hourly
basis?
Thanks,
Jason
Hello Jason,
There might be conflits because there are two replicaitons for the same
tables. You may consider set up a replicaiton of some tables to run nightly
and the rest tables to run on a hourly basis.
Thanks & Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: jason.hill@.pearsoned.com.au
| Newsgroups: microsoft.public.sqlserver.replication
| Subject: Partial and Full Replication
| Date: 9 Aug 2005 20:25:54 -0700
| Organization: http://groups.google.com
| Lines: 10
| Message-ID: <1123644354.337678.325910@.g44g2000cwa.googlegroups .com>
| NNTP-Posting-Host: 203.143.94.10
| Mime-Version: 1.0
| Content-Type: text/plain; charset="iso-8859-1"
| X-Trace: posting.google.com 1123644360 22422 127.0.0.1 (10 Aug 2005
03:26:00 GMT)
| X-Complaints-To: groups-abuse@.google.com
| NNTP-Posting-Date: Wed, 10 Aug 2005 03:26:00 +0000 (UTC)
| User-Agent: G2/0.2
| Complaints-To: groups-abuse@.google.com
| Injection-Info: g44g2000cwa.googlegroups.com; posting-host=203.143.94.10;
| posting-account=vUxf9g0AAAC_Tbj8TfB8dQv9VE03ynWf
| Path:
TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfee d00.sul.t-online.de!t-onli
ne.de!news.glorb.com!postnews.google.com!g44g2000c wa.googlegroups.com!not-fo
r-mail
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.replication:15717
| X-Tomcat-NG: microsoft.public.sqlserver.replication
|
| Hi,
|
| Is it possible to setup full replication of a database to run nightly
| as well as partial replication of only a couple of tables on an hourly
| basis?
|
| Thanks,
|
| Jason
|
|
|||Jason,
you'll need 2 publications for this - one containing the 2 tables and the
other publication containing the rest of the tables. This is often done to
separate lookup tables from transactional tables. However, if there are
PK-FK relationships across the publications, there can obviously be issues
arising.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks for the prompt replies. Sounds like 2 publications is the way
to go, splitting the tables out separately, which will work perfectly.
Cheers,
Jason

Parsing web URL's in SQL

Hello everyone,
I was wondering if anyone could help me out. Basically I have several
sharepoint sites for applications that I run. Each applications
sharepoint site needs to display information about the application
that is located in a SQL database. So for example:
www.qwerty.com/application1/home.html
www.qwerty.com/application2/home.html
www.qwerty.com/application3/home.html
I need to find a way that SQL, Sharepoint, some code, would take the
web address, parse, and truncate it to only read "Application1."
Then I could write a SQL statement to get the data from Application1's
database to display on the sharepoint site. However I have several
application pages, and I would like to be able to have some type of
code that would automatically do it for all the applications, instead
of manually coding/quiering for the data for each application
page.....it would just take too long.
if anyone has any ideas, feel free to share. Thank you in advance, for
all your time and help it is greatly appreciated!
--A4orce84
If the part you want is always the next to last section, then this
would appear to provide what is needed. A bit convoluted though!
CREATE TABLE Demo (url varchar(100) not null)
INSERT Demo values ('www.qwerty.com/application1/home.html')
INSERT Demo values ('www.qwerty.com/application2/home.html')
INSERT Demo values ('www.qwerty.com/application3/home.html')
INSERT Demo values ('www.qwerty.com/whatever/banana/987.htm')
SELECT REVERSE(
SUBSTRING(REVERSE(url),
charindex('/', REVERSE(url)) +1,
charindex('/', REVERSE(url), charindex('/',
REVERSE(url)) + 1) -
charindex('/', REVERSE(url)) - 1))
FROM Demo
application1
application2
application3
banana
Roy Harvey
Beacon Falls, CT
On Fri, 08 Jun 2007 09:01:28 -0700, Asif_Ahmad@.dell.com wrote:

>Hello everyone,
>I was wondering if anyone could help me out. Basically I have several
>sharepoint sites for applications that I run. Each applications
>sharepoint site needs to display information about the application
>that is located in a SQL database. So for example:
>www.qwerty.com/application1/home.html
>www.qwerty.com/application2/home.html
>www.qwerty.com/application3/home.html
>I need to find a way that SQL, Sharepoint, some code, would take the
>web address, parse, and truncate it to only read "Application1."
>Then I could write a SQL statement to get the data from Application1's
>database to display on the sharepoint site. However I have several
>application pages, and I would like to be able to have some type of
>code that would automatically do it for all the applications, instead
>of manually coding/quiering for the data for each application
>page.....it would just take too long.
>if anyone has any ideas, feel free to share. Thank you in advance, for
>all your time and help it is greatly appreciated!
>--A4orce84
|||Ron,
Is this if you only know the number of applications you have? I think
there are several hundred I am using particularly. It also appears
from your code that you have to enter the physical address into the
code by your:
INSERT Demo values ('www.qwerty.com/application1/home.html')
INSERT Demo values ('www.qwerty.com/application2/home.html')
INSERT Demo values ('www.qwerty.com/application3/home.html')
INSERT Demo values ('www.qwerty.com/whatever/banana/987.htm')
I wanted a way that it would do it automatically, that way if servers
change and things get moved around, or they add more applications it
would be easy to keep maintaining the SQL. Or I may have completely
mis-interpreted your code, either way thank you for your help. Any
additional information you could provide would be helpful!
|||I assumed that the URLs you needed to parse would already be in a SQL
Server database table. (Why else would you be trying to parse a URL
in SQL Server?) So my CREATE TABLE and INSERT statements were simply
there to create some test data for demonstration purposes. The number
of URLs in your table is irrelevant to the parsing process.
What I intended to demonstrate was a way to pick out the next to last
section of the URL, which was what I thought you were aiming for. I
believe the code provided does that.
Good luck!
Roy Harvey
Beacon Falls, CT
On Sun, 10 Jun 2007 15:04:14 -0700, Asif_Ahmad@.dell.com wrote:

>Ron,
>Is this if you only know the number of applications you have? I think
>there are several hundred I am using particularly. It also appears
>from your code that you have to enter the physical address into the
>code by your:
>
>INSERT Demo values ('www.qwerty.com/application1/home.html')
>INSERT Demo values ('www.qwerty.com/application2/home.html')
>INSERT Demo values ('www.qwerty.com/application3/home.html')
>INSERT Demo values ('www.qwerty.com/whatever/banana/987.htm')
>
>I wanted a way that it would do it automatically, that way if servers
>change and things get moved around, or they add more applications it
>would be easy to keep maintaining the SQL. Or I may have completely
>mis-interpreted your code, either way thank you for your help. Any
>additional information you could provide would be helpful!

Parsing web URL's in SQL

Hello everyone,
I was wondering if anyone could help me out. Basically I have several
sharepoint sites for applications that I run. Each applications
sharepoint site needs to display information about the application
that is located in a SQL database. So for example:
www.qwerty.com/application1/home.html
www.qwerty.com/application2/home.html
www.qwerty.com/application3/home.html
I need to find a way that SQL, Sharepoint, some code, would take the
web address, parse, and truncate it to only read "Application1."
Then I could write a SQL statement to get the data from Application1's
database to display on the sharepoint site. However I have several
application pages, and I would like to be able to have some type of
code that would automatically do it for all the applications, instead
of manually coding/quiering for the data for each application
page.....it would just take too long.
if anyone has any ideas, feel free to share. Thank you in advance, for
all your time and help it is greatly appreciated!
--A4orce84If the part you want is always the next to last section, then this
would appear to provide what is needed. A bit convoluted though!
CREATE TABLE Demo (url varchar(100) not null)
INSERT Demo values ('www.qwerty.com/application1/home.html')
INSERT Demo values ('www.qwerty.com/application2/home.html')
INSERT Demo values ('www.qwerty.com/application3/home.html')
INSERT Demo values ('www.qwerty.com/whatever/banana/987.htm')
SELECT REVERSE(
SUBSTRING(REVERSE(url),
charindex('/', REVERSE(url)) +1,
charindex('/', REVERSE(url), charindex('/',
REVERSE(url)) + 1) -
charindex('/', REVERSE(url)) - 1))
FROM Demo
--
application1
application2
application3
banana
Roy Harvey
Beacon Falls, CT
On Fri, 08 Jun 2007 09:01:28 -0700, Asif_Ahmad@.dell.com wrote:
>Hello everyone,
>I was wondering if anyone could help me out. Basically I have several
>sharepoint sites for applications that I run. Each applications
>sharepoint site needs to display information about the application
>that is located in a SQL database. So for example:
>www.qwerty.com/application1/home.html
>www.qwerty.com/application2/home.html
>www.qwerty.com/application3/home.html
>I need to find a way that SQL, Sharepoint, some code, would take the
>web address, parse, and truncate it to only read "Application1."
>Then I could write a SQL statement to get the data from Application1's
>database to display on the sharepoint site. However I have several
>application pages, and I would like to be able to have some type of
>code that would automatically do it for all the applications, instead
>of manually coding/quiering for the data for each application
>page.....it would just take too long.
>if anyone has any ideas, feel free to share. Thank you in advance, for
>all your time and help it is greatly appreciated!
>--A4orce84|||Ron,
Is this if you only know the number of applications you have? I think
there are several hundred I am using particularly. It also appears
from your code that you have to enter the physical address into the
code by your:
INSERT Demo values ('www.qwerty.com/application1/home.html')
INSERT Demo values ('www.qwerty.com/application2/home.html')
INSERT Demo values ('www.qwerty.com/application3/home.html')
INSERT Demo values ('www.qwerty.com/whatever/banana/987.htm')
I wanted a way that it would do it automatically, that way if servers
change and things get moved around, or they add more applications it
would be easy to keep maintaining the SQL. Or I may have completely
mis-interpreted your code, either way thank you for your help. Any
additional information you could provide would be helpful!|||I assumed that the URLs you needed to parse would already be in a SQL
Server database table. (Why else would you be trying to parse a URL
in SQL Server?) So my CREATE TABLE and INSERT statements were simply
there to create some test data for demonstration purposes. The number
of URLs in your table is irrelevant to the parsing process.
What I intended to demonstrate was a way to pick out the next to last
section of the URL, which was what I thought you were aiming for. I
believe the code provided does that.
Good luck!
Roy Harvey
Beacon Falls, CT
On Sun, 10 Jun 2007 15:04:14 -0700, Asif_Ahmad@.dell.com wrote:
>Ron,
>Is this if you only know the number of applications you have? I think
>there are several hundred I am using particularly. It also appears
>from your code that you have to enter the physical address into the
>code by your:
>
>INSERT Demo values ('www.qwerty.com/application1/home.html')
>INSERT Demo values ('www.qwerty.com/application2/home.html')
>INSERT Demo values ('www.qwerty.com/application3/home.html')
>INSERT Demo values ('www.qwerty.com/whatever/banana/987.htm')
>
>I wanted a way that it would do it automatically, that way if servers
>change and things get moved around, or they add more applications it
>would be easy to keep maintaining the SQL. Or I may have completely
>mis-interpreted your code, either way thank you for your help. Any
>additional information you could provide would be helpful!

Parsing web URL's in SQL

Hello everyone,
I was wondering if anyone could help me out. Basically I have several
sharepoint sites for applications that I run. Each applications
sharepoint site needs to display information about the application
that is located in a SQL database. So for example:
www.qwerty.com/application1/home.html
www.qwerty.com/application2/home.html
www.qwerty.com/application3/home.html
I need to find a way that SQL, Sharepoint, some code, would take the
web address, parse, and truncate it to only read "Application1."
Then I could write a SQL statement to get the data from Application1's
database to display on the sharepoint site. However I have several
application pages, and I would like to be able to have some type of
code that would automatically do it for all the applications, instead
of manually coding/quiering for the data for each application
page.....it would just take too long.
if anyone has any ideas, feel free to share. Thank you in advance, for
all your time and help it is greatly appreciated!
--A4orce84If the part you want is always the next to last section, then this
would appear to provide what is needed. A bit convoluted though!
CREATE TABLE Demo (url varchar(100) not null)
INSERT Demo values ('www.qwerty.com/application1/home.html')
INSERT Demo values ('www.qwerty.com/application2/home.html')
INSERT Demo values ('www.qwerty.com/application3/home.html')
INSERT Demo values ('www.qwerty.com/whatever/banana/987.htm')
SELECT REVERSE(
SUBSTRING(REVERSE(url),
charindex('/', REVERSE(url)) +1,
charindex('/', REVERSE(url), charindex('/',
REVERSE(url)) + 1) -
charindex('/', REVERSE(url)) - 1))
FROM Demo
application1
application2
application3
banana
Roy Harvey
Beacon Falls, CT
On Fri, 08 Jun 2007 09:01:28 -0700, Asif_Ahmad@.dell.com wrote:

>Hello everyone,
>I was wondering if anyone could help me out. Basically I have several
>sharepoint sites for applications that I run. Each applications
>sharepoint site needs to display information about the application
>that is located in a SQL database. So for example:
>www.qwerty.com/application1/home.html
>www.qwerty.com/application2/home.html
>www.qwerty.com/application3/home.html
>I need to find a way that SQL, Sharepoint, some code, would take the
>web address, parse, and truncate it to only read "Application1."
>Then I could write a SQL statement to get the data from Application1's
>database to display on the sharepoint site. However I have several
>application pages, and I would like to be able to have some type of
>code that would automatically do it for all the applications, instead
>of manually coding/quiering for the data for each application
>page.....it would just take too long.
>if anyone has any ideas, feel free to share. Thank you in advance, for
>all your time and help it is greatly appreciated!
>--A4orce84|||Ron,
Is this if you only know the number of applications you have? I think
there are several hundred I am using particularly. It also appears
from your code that you have to enter the physical address into the
code by your:
INSERT Demo values ('www.qwerty.com/application1/home.html')
INSERT Demo values ('www.qwerty.com/application2/home.html')
INSERT Demo values ('www.qwerty.com/application3/home.html')
INSERT Demo values ('www.qwerty.com/whatever/banana/987.htm')
I wanted a way that it would do it automatically, that way if servers
change and things get moved around, or they add more applications it
would be easy to keep maintaining the SQL. Or I may have completely
mis-interpreted your code, either way thank you for your help. Any
additional information you could provide would be helpful!|||I assumed that the URLs you needed to parse would already be in a SQL
Server database table. (Why else would you be trying to parse a URL
in SQL Server?) So my CREATE TABLE and INSERT statements were simply
there to create some test data for demonstration purposes. The number
of URLs in your table is irrelevant to the parsing process.
What I intended to demonstrate was a way to pick out the next to last
section of the URL, which was what I thought you were aiming for. I
believe the code provided does that.
Good luck!
Roy Harvey
Beacon Falls, CT
On Sun, 10 Jun 2007 15:04:14 -0700, Asif_Ahmad@.dell.com wrote:

>Ron,
>Is this if you only know the number of applications you have? I think
>there are several hundred I am using particularly. It also appears
>from your code that you have to enter the physical address into the
>code by your:
>
>INSERT Demo values ('www.qwerty.com/application1/home.html')
>INSERT Demo values ('www.qwerty.com/application2/home.html')
>INSERT Demo values ('www.qwerty.com/application3/home.html')
>INSERT Demo values ('www.qwerty.com/whatever/banana/987.htm')
>
>I wanted a way that it would do it automatically, that way if servers
>change and things get moved around, or they add more applications it
>would be easy to keep maintaining the SQL. Or I may have completely
>mis-interpreted your code, either way thank you for your help. Any
>additional information you could provide would be helpful!

Parsing T-SQL is not validating table schemas

I am curious why my stored procedures are parsing properly when you do not reference a table with its schema. The stored procs then fail when you run them.

It seems that the parser does not validate that a tables schema is missing.

This is an example stored procedure against the Person.Address table in the Adventureworks database. It will execute fine if I change the FROM clause to Person.Address.

CREATE PROCEDURE [dbo].[Address_Load]
@.AddressID [int]
AS
BEGIN
SET NOCOUNT ON;

DECLARE @.intError int

BEGIN TRY
SELECT A.[AddressID]
, A.[AddressLine1]
, A.[AddressLine2]
, A.[City]
, A.[StateProvinceID]
, A.[PostalCode]
FROM [Address] A
WHERE A.[AddressID] = @.AddressID

IF @.@.ROWCOUNT = 0
BEGIN
RAISERROR('Record not found', 16, 1) -- Record not found.
END

-- Return success
RETURN 0
END TRY
BEGIN CATCH
SET @.intError = ERROR_NUMBER();

-- Log error here

RETURN @.intError;
END CATCH
END

The stored proc parses fine and gets saved to the database but when executing it I get the following

Msg 208, Level 16, State 1, Procedure Address_Load, Line 10

Invalid object name 'Address'.

Is there any way to change this so the parsing will generate an error and not allow this into the database?

Thanks,

Cory

The behavior is due to deferred name resolution/compilation of TSQL modules in SQL Server. You can look it up in BOL for more details. See link below for starters:

http://msdn2.microsoft.com/en-us/library/ms190686.aspx

|||

And when you decide you don't like the behavior, please go here and vote!

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124490

It is good about 1% of the time to be like this. However, the problem is the 2% of the time when you are coding and mistype a table name and it still compiles, only to find out later when you are (hopefully) testing :)

|||Is there a way to turn off Deferred Name Resolution?|||

Nope. That is what this feedback that I mentioned:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124490

It really ought to be a settting you can ask for, not soemthing that is on all of the time.

Parent Package reports failure on errors, but no errors in log

I have a parent package that calls child packages inside a For Each container. When I debug/run the parent package (from VS), I get the following error message: Warning: The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

It appears to be failing while executing the child package. However, the logs (via the "progress" tab) for both the parent package and the child package show no errors other than the one listed above (and that shows in the parent package log). The child package appears to validate completely without error (all components are green and no error messages in the log). I turned on SSIS logging to a text file and see nothing in there either.

If I bump up the MaximumErrorCount in the parent package and in the Execute Package Task that calls the child package to 4 (to go one above the error count indicated in the message above), the whole thing executes sucessfully. I don't want to leave the Max Error Count set like this. Is there something I am missing? For example are there errors that do not get logged by default? I get some warnings, do a certain number of warnings equal an error?

Thanks,

Lee

I never figured this one out. It looks like in a parent/child package model this happens where you will get an "error" even though the child package succeeded. I've checked and there are no data errors (e.g. truncation, ...) or package logic errors. It has happened on two other package sets I've created where there is a parent package calling child packages. The work around has been to bump up the MaximumErrorCounts in the execute package task, the for each container that contains the child packages, and the parent package. Not a good solution but it works.|||

I still continue to see this behavior. I have text file logging setup to catch every event in both the child and parent packages. I have no warnings or errors and the child package runs completely and does what it is supposed to do. But in the parent package, the child "Execute Package Task" fails with a "The Execution method succeeded, but the number of errors raised..." which is the only error message I get in the logs. Again, every event type is set to log.

I can work around this problem by bumping up the MaximumErrorCount on the "Execute Package Task" but what errors is this catching?

Is this a bug? Are there any MVPs on here?

|||I encountered the same error and could not solve it, I had to use the maxerrorcount work around as well

Parent Package reports failure on errors, but no errors in log

I have a parent package that calls child packages inside a For Each container. When I debug/run the parent package (from VS), I get the following error message: Warning: The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

It appears to be failing while executing the child package. However, the logs (via the "progress" tab) for both the parent package and the child package show no errors other than the one listed above (and that shows in the parent package log). The child package appears to validate completely without error (all components are green and no error messages in the log). I turned on SSIS logging to a text file and see nothing in there either.

If I bump up the MaximumErrorCount in the parent package and in the Execute Package Task that calls the child package to 4 (to go one above the error count indicated in the message above), the whole thing executes sucessfully. I don't want to leave the Max Error Count set like this. Is there something I am missing? For example are there errors that do not get logged by default? I get some warnings, do a certain number of warnings equal an error?

Thanks,

Lee

I never figured this one out. It looks like in a parent/child package model this happens where you will get an "error" even though the child package succeeded. I've checked and there are no data errors (e.g. truncation, ...) or package logic errors. It has happened on two other package sets I've created where there is a parent package calling child packages. The work around has been to bump up the MaximumErrorCounts in the execute package task, the for each container that contains the child packages, and the parent package. Not a good solution but it works.|||

I still continue to see this behavior. I have text file logging setup to catch every event in both the child and parent packages. I have no warnings or errors and the child package runs completely and does what it is supposed to do. But in the parent package, the child "Execute Package Task" fails with a "The Execution method succeeded, but the number of errors raised..." which is the only error message I get in the logs. Again, every event type is set to log.

I can work around this problem by bumping up the MaximumErrorCount on the "Execute Package Task" but what errors is this catching?

Is this a bug? Are there any MVPs on here?

Parent Package reports failure on errors, but no errors in log

I have a parent package that calls child packages inside a For Each container. When I debug/run the parent package (from VS), I get the following error message: Warning: The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

It appears to be failing while executing the child package. However, the logs (via the "progress" tab) for both the parent package and the child package show no errors other than the one listed above (and that shows in the parent package log). The child package appears to validate completely without error (all components are green and no error messages in the log). I turned on SSIS logging to a text file and see nothing in there either.

If I bump up the MaximumErrorCount in the parent package and in the Execute Package Task that calls the child package to 4 (to go one above the error count indicated in the message above), the whole thing executes sucessfully. I don't want to leave the Max Error Count set like this. Is there something I am missing? For example are there errors that do not get logged by default? I get some warnings, do a certain number of warnings equal an error?

Thanks,

Lee

I never figured this one out. It looks like in a parent/child package model this happens where you will get an "error" even though the child package succeeded. I've checked and there are no data errors (e.g. truncation, ...) or package logic errors. It has happened on two other package sets I've created where there is a parent package calling child packages. The work around has been to bump up the MaximumErrorCounts in the execute package task, the for each container that contains the child packages, and the parent package. Not a good solution but it works.|||

I still continue to see this behavior. I have text file logging setup to catch every event in both the child and parent packages. I have no warnings or errors and the child package runs completely and does what it is supposed to do. But in the parent package, the child "Execute Package Task" fails with a "The Execution method succeeded, but the number of errors raised..." which is the only error message I get in the logs. Again, every event type is set to log.

I can work around this problem by bumping up the MaximumErrorCount on the "Execute Package Task" but what errors is this catching?

Is this a bug? Are there any MVPs on here?

Parent package call to Child package

I run into some issues and really need some expert help here.

Here is the problem. I have two packages (parent.dtsx and child.dtsx). Both package have its own configuration file (parent.dtsConfig and child.dtsConfig). The file Child.dtsConfig contains a variable (i.e. "X") that is to be used by Child.dtsx.

Inside parent.dtsx. there is a package-task that calls into Child.dtsx. It worked perfectly well if I run parent.dtsx using Dtexec or from inside SSIS's IDE.

Now I want to programmably call "parent.dtsx" from my C# code. I loaded package using "app.LoadPackage"... Inside C# code, I want to reconfigure Child-package's variable ("X"). I then loaded in "Child.dtsx". However when I run "parent.dtsx" and child.dtsx still loads the original value for "X". The reconfigured value for "X" is not updated.

Please help on how to get around this issue.

Thanks.

Are you changing the configuration file or the value in the package itself? If you change the value in the package, it will be overwritten by the value from the config file at runtime.|||

Your comments are exactly CORRECT...

I load the package and change the values (for both parent and chiled) inside the C# code. Yes, when I kick off parent package from C#, the child package load variables' value from child's configuration file.... This is NOT what I want.. Do you know any way to avoid this ? How do I persist the udpated values ?

|||Change the config file information with your C# code. Or use a SQL Server based configuration and update the table before each child package execution.|||

Phil Brammer wrote:

Change the config file information with your C# code. Or use a SQL Server based configuration and update the table before each child package execution.

Or don't use configurations. If you are running the packages from your code, and you are setting the values each time you load and execute the packages, what purpose are the configurations serving?

|||

Thank you... Your comments are valid suggestions.

However,

first, I have a master package (parent) and many child packages. Each individual child package has its own configuration file (childXXX.dtsConfig) which is an XML file. We did not use SQL as configuration. We want to reuse this entire package suite for two different business domains. In one scenario, we run packages using DTEXEC (or scheduled job) and it worked well. In another scenario, we want to RE-USE the entire package suite from C# code. I want to re-configure each packages inside C# code. And lauch the master package and HOPEFULLY execute all child packages with updated variable values (rather than reading from configuration files because configuration file contains old-values which I do not want to load). Those configuration files

So i need to keep all XML configuration files.

Secondly even if I do not use configuration file, I still cannot persist child's updated variable values. I have set up a simple pair of package (parent and child) to verify it. Inside parent package, i have a task to execute the child package... And i reconfigure child package and start to run parent package inside C#. I found that Child package's configuration updates are lost.

I have zipped all of my testing files and wish to send you all files via email if you are interest. I would definitely appreciate your help as I am stuck now.

Please help.

|||Right, but the configuration files override any changes you make with C# as John stated. How do you propose to get around that?|||

Steve Wang 2006 wrote:

Thank you... Your comments are valid suggestions.

However,

first, I have a master package (parent) and many child packages. Each individual child package has its own configuration file (childXXX.dtsConfig) which is an XML file. We did not use SQL as configuration. We want to reuse this entire package suite for two different business domains. In one scenario, we run packages using DTEXEC (or scheduled job) and it worked well. In another scenario, we want to RE-USE the entire package suite from C# code. I want to re-configure each packages inside C# code. And lauch the master package and HOPEFULLY execute all child packages with updated variable values (rather than reading from configuration files because configuration file contains old-values which I do not want to load). Those configuration files

So i need to keep all XML configuration files.

Secondly even if I do not use configuration file, I still cannot persist child's updated variable values. I have set up a simple pair of package (parent and child) to verify it. Inside parent package, i have a task to execute the child package... And i reconfigure child package and start to run parent package inside C#. I found that Child package's configuration updates are lost.

I have zipped all of my testing files and wish to send you all files via email if you are interest. I would definitely appreciate your help as I am stuck now.

Please help.

In your simple test, are you re-saving the child package after changing the variable settings? When you execute the parent from code, it will load the child package from the location pointed to by the package connection manager. It will not run the one you have loaded in your program.

I don't think you can do what you are trying to do. As Phil mentioned, the configurations will always take precedence over values saved in the package. If you want to run the same packages with different configurations, try using two different sets of configuration files, or have the parent package pass all values to the child packages through a parent package configuration. That way, you set the parent values when you execute it, and all the child packages pick up their values from it.

|||

Thanks Phil and John for prompt response...

In my simple test, I re-saved the re-configured parent and child packages to new XML files (using Application.SaveToXML(..) ) and reloaded them back with the hope of child package not loading configuration from configuration files. It did not work.... i.e. Child package still loaded variable values from child.dtsConfig file.

I have also tried to overwrite the configuration file using SSIS's API (i.e. Package.ExportConfigurationFile (....). This function seems to have some bugs that does NOTHING always.. Nothing is written out to file. Here is a posing that complains this API: http://sqljunkies.com/Forums/Search/default.aspx?SearchFor=1&SearchText=neetash

What I thought is: If i could overwrite the configuration file with updated variable setting (from C#), then the issue is solved. However the API "Package.ExportConfigurationFile(...)" is useless.

|||

Even if you were able to use the ExportConfigurationFile function - wouldn't that impact the ability to run the packages directly through DTEXEC? You'd effectively be altering both ways of running the packages.

If that is acceptable, I'd still suggest just loading the config file into an XML DOM object, change what you need to change, and save it back out.

If you need to keep the two methods of running the packages seperate, you'd still need to go with Parent Package configurations.

Parent Child Integration using Configuration File

Hi All,

I though i'd been going about setting up my SSIS package to run via a SQL job in the correct way. It would appear however that is not the case.

I have 4 SSIS packages, one of which is the parent package which calls the other three in sequence. I want to run this from a SQL job so that it calls the parent package and it deals with the others. There are connection managers in the package which use a SQL account to access the relevant databases. In addition i have encrypted the package.

I have set up a configuration file which holds the password package and the BillingSystem connection manager password. This file is re used by each package.

After deployment i have set up the SQL agent job to run the parent package. The job returns an error. Looking at the SSIS logging it appears that it completes the SQL task in the parent package and then fails when trying to launch each of the sub packages with an error stating that the SQL account used in the connection manager login failed. It looks as though the sub packages are not getting the password value from the configuration file and this is causing the failure. I though that each package would automatically pick up the config file as it has been setup to do. I have specified the configuration file in the SQL job but this appears to make no difference. Running the integrations seperately i have to add the configuration file in manually to the Run Package dialog box before they will work. This is not saved for the next run.

Can anyone help me get around this problem as i think i have the wrong idea as to how it is supposed to work.

Cheers,

Grant

Are your packages stored in the filesystem or in MSDB?|||

MSDB; Does that make a difference in this case?

Grant

|||Is the SQL server connection for each of the Execute Package task part of a configuration file?|||

Initially the config file contained the connection manager password. It now has the connection string but doesn't seem to be used for the packages called from the initial package. I had to manually alter the i config xml to include the password in the connection string.

Grant

Partitioned View not operating as Documented

Hi All,
We have run into a huge problem at a large data-heavy multi-user
installation. In order to increase response times we split up a number of
tables into "Company" specific divisions to reduce the volume of records in
each Division and then placed Check constaints on each table by DIVID. We
Unioned the tables into partitioned views.
However, contrary to documentation and recommendations from this newsgroup,
even though we include DIVID in all our queries, Enterprise Manager and QA
indicate that SQL Server is opening ALL tables in the view during a Query or
an Update. I am thinking we must have done something wrong, or misunderstood
what partitoned views are supposed to do. We thought that the Check
constraints would allow SQL Server to key in immediately on the requested
divisional table. Here are some code examples:
A Table:
CREATE TABLE [PLTBDIV_ACT_DET_D00]
(
[DIVID] [CHAR] (3) NOT NULL CHECK (DIVID='D00')
,[ACCTID] [CHAR] (10) NOT NULL
,[ACT_TYPE] [CHAR] (1) NOT NULL
,[TRANID] [CHAR] (10) NOT NULL
,[TRANDATE] [SMALLDATETIME]
,[TRANCODE] [CHAR] (5)
,[TRANMODE] [CHAR] (5)
,[TRANREF] [CHAR] (25)
,[CSHREF] [CHAR] (25)
,[REVERSED] [CHAR] (1)
,[CHECKNUM] [CHAR] (25)
,[CHECKACCTID] [CHAR] (10)
,[INV_NUM] [CHAR] (12)
,[REBILLID] [CHAR] (3)
,[INV_TYPE] [CHAR] (1)
,[TRANAMOUNT] [NUMERIC] (19,4)
,[APPLIED] [NUMERIC] (19,4)
,[AVAILABLE] [NUMERIC] (19,4)
,[COMMENT] [CHAR] (90)
,[CREATION] [SMALLDATETIME]
,[BATCHNUM] [CHAR] (12) NULL
,[CLOSING] [CHAR] (1)
,[USERID] [CHAR] (15)
)
Note the Check constraint.
The Primary Key:
ALTER TABLE [dbo].[PLTBDIV_ACT_DET_D00] WITH NOCHECK ADD CONSTRAINT
[PLPKDIV_ACT_DET_D00]
PRIMARY KEY CLUSTERED
(
[DIVID]
,[TRANID]
) --WITH FILLFACTOR = 10
The View is a simple Union of all tables.
Some TSQL tests run indicate that the Constraint is trusted.
EXEC SP_HELPCONSTRAINT PLTBDIV_ACT_DET_D00
SELECT
OBJECTPROPERTY(OBJECT_ID('CK__PLTBDIV_A_
_DIVID__7DF19EE6'),'CnstIsNotTrusted
')
The 2nd query returns 0.
Can anyone see what might be the problem? Or are partitoned views not meant
to improve performance?
Thanks to all in advance."John Kotuby" <johnk@.powerlist.com> wrote in message
news:OW40DgpcGHA.3908@.TK2MSFTNGP02.phx.gbl...
> Hi All,
> We have run into a huge problem at a large data-heavy multi-user
> installation. In order to increase response times we split up a number of
> tables into "Company" specific divisions to reduce the volume of records
> in each Division and then placed Check constaints on each table by DIVID.
> We Unioned the tables into partitioned views.
> However, contrary to documentation and recommendations from this
> newsgroup, even though we include DIVID in all our queries, Enterprise
> Manager and QA indicate that SQL Server is opening ALL tables in the view
> during a Query or an Update. I am thinking we must have done something
> wrong, or misunderstood what partitoned views are supposed to do. We
> thought that the Check constraints would allow SQL Server to key in
> immediately on the requested divisional table. Here are some code
> examples:
> A Table:
> CREATE TABLE [PLTBDIV_ACT_DET_D00]
> (
> [DIVID] [CHAR] (3) NOT NULL CHECK (DIVID='D00')
> ,[ACCTID] [CHAR] (10) NOT NULL
> ,[ACT_TYPE] [CHAR] (1) NOT NULL
> ,[TRANID] [CHAR] (10) NOT NULL
> ,[TRANDATE] [SMALLDATETIME]
> ,[TRANCODE] [CHAR] (5)
> ,[TRANMODE] [CHAR] (5)
> ,[TRANREF] [CHAR] (25)
> ,[CSHREF] [CHAR] (25)
> ,[REVERSED] [CHAR] (1)
> ,[CHECKNUM] [CHAR] (25)
> ,[CHECKACCTID] [CHAR] (10)
> ,[INV_NUM] [CHAR] (12)
> ,[REBILLID] [CHAR] (3)
> ,[INV_TYPE] [CHAR] (1)
> ,[TRANAMOUNT] [NUMERIC] (19,4)
> ,[APPLIED] [NUMERIC] (19,4)
> ,[AVAILABLE] [NUMERIC] (19,4)
> ,[COMMENT] [CHAR] (90)
> ,[CREATION] [SMALLDATETIME]
> ,[BATCHNUM] [CHAR] (12) NULL
> ,[CLOSING] [CHAR] (1)
> ,[USERID] [CHAR] (15)
> )
> Note the Check constraint.
> The Primary Key:
> ALTER TABLE [dbo].[PLTBDIV_ACT_DET_D00] WITH NOCHECK ADD CONSTRAINT
> [PLPKDIV_ACT_DET_D00]
> PRIMARY KEY CLUSTERED
> (
> [DIVID]
> ,[TRANID]
> ) --WITH FILLFACTOR = 10
> The View is a simple Union of all tables.
> Some TSQL tests run indicate that the Constraint is trusted.
> EXEC SP_HELPCONSTRAINT PLTBDIV_ACT_DET_D00
> SELECT
> OBJECTPROPERTY(OBJECT_ID('CK__PLTBDIV_A_
_DIVID__7DF19EE6'),'CnstIsNotTrust
ed')
> The 2nd query returns 0.
> Can anyone see what might be the problem? Or are partitoned views not
> meant to improve performance?
>
Too little information.
Post the UNION view, along with a query againt the view, and the results for
running the query with SET STATISTICS_IO ON.
David|||John,
I don't know if it's what you're seeing, but a common confusion here
is that the *estimated* query plan shows all tables accessed with
equal cost, but the *actual* plan only accesses the relevant table.
In the query plan details, you will often see the dependence of
each table's access hinging on STARTUP_EXPR, which is
evaluated at run time before any of the tables are accessed.
If you run the query with SET STATISTICS IO ON in cases
like this, you will see all tables listed, but the number of reads
for the unneeded tables will be zero.
What exactly is indicating to you that "SQL Server is opening ALL
tables in the view" ?
Steve Kass
Drew University
John Kotuby wrote:

>Hi All,
>We have run into a huge problem at a large data-heavy multi-user
>installation. In order to increase response times we split up a number of
>tables into "Company" specific divisions to reduce the volume of records in
>each Division and then placed Check constaints on each table by DIVID. We
>Unioned the tables into partitioned views.
>However, contrary to documentation and recommendations from this newsgroup,
>even though we include DIVID in all our queries, Enterprise Manager and QA
>indicate that SQL Server is opening ALL tables in the view during a Query o
r
>an Update. I am thinking we must have done something wrong, or misunderstoo
d
>what partitoned views are supposed to do. We thought that the Check
>constraints would allow SQL Server to key in immediately on the requested
>divisional table. Here are some code examples:
>A Table:
>CREATE TABLE [PLTBDIV_ACT_DET_D00]
>(
> [DIVID] [CHAR] (3) NOT NULL CHECK (DIVID='D00')
>,[ACCTID] [CHAR] (10) NOT NULL
>,[ACT_TYPE] [CHAR] (1) NOT NULL
>,[TRANID] [CHAR] (10) NOT NULL
>,[TRANDATE] [SMALLDATETIME]
>,[TRANCODE] [CHAR] (5)
>,[TRANMODE] [CHAR] (5)
>,[TRANREF] [CHAR] (25)
>,[CSHREF] [CHAR] (25)
>,[REVERSED] [CHAR] (1)
>,[CHECKNUM] [CHAR] (25)
>,[CHECKACCTID] [CHAR] (10)
>,[INV_NUM] [CHAR] (12)
>,[REBILLID] [CHAR] (3)
>,[INV_TYPE] [CHAR] (1)
>,[TRANAMOUNT] [NUMERIC] (19,4)
>,[APPLIED] [NUMERIC] (19,4)
>,[AVAILABLE] [NUMERIC] (19,4)
>,[COMMENT] [CHAR] (90)
>,[CREATION] [SMALLDATETIME]
>,[BATCHNUM] [CHAR] (12) NULL
>,[CLOSING] [CHAR] (1)
>,[USERID] [CHAR] (15)
> )
>Note the Check constraint.
>The Primary Key:
>ALTER TABLE [dbo].[PLTBDIV_ACT_DET_D00] WITH NOCHECK ADD CONSTRAINT
>[PLPKDIV_ACT_DET_D00]
>PRIMARY KEY CLUSTERED
>(
> [DIVID]
>,[TRANID]
> ) --WITH FILLFACTOR = 10
>The View is a simple Union of all tables.
>Some TSQL tests run indicate that the Constraint is trusted.
>EXEC SP_HELPCONSTRAINT PLTBDIV_ACT_DET_D00
>SELECT
> OBJECTPROPERTY(OBJECT_ID('CK__PLTBDIV_A_
_DIVID__7DF19EE6'),'CnstIsNotTruste
d')
>The 2nd query returns 0.
>Can anyone see what might be the problem? Or are partitoned views not meant
>to improve performance?
>Thanks to all in advance.
>
>
>|||Got caught up in work....
Here is a simple query that seems to be hitting only one table.
SET STATISTICS IO ON
SELECT DIVID, TRANID, INV_NUM FROM PLVWDIV_ACT_DET
WHERE DIVID = 'D02' AND ACT_TYPE = 'P' AND TRANDATE > '01/01/2006'
(1509 row(s) affected)
Table 'PLTBDIV_ACT_DET_D02'. Scan count 1, logical reads 5456, physical
reads 0,
read-ahead reads 4.
There are approximately 320,000 records in the PLTBDIV_ACT_DET_D02 table.
We join about 30 tables in the view.
We were watching Current Activity in EM at the site and refreshing every few
seconds and saw all tables in multiple partitioned views open at the same
time. In some cases every table had locks on them. We were getting timeouts
from the VB client.
When we changed our programming techniques to use Paramaterized queries that
hit the tables directly most of that contention just disappeared.
CREATE VIEW [PLVWDIV_ACT_DET]
-- DESC: UNIONED VIEW INTO ACCOUNTS DETAIL
AS
SELECT * FROM [PLTBCOM_ACT_DET] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D00] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D01] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D02] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D03] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D04] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D05] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D06] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D07] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D08] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D09] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D10] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D11] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D12] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D13] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D14] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D15] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D16] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D17] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D18] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D19] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D20] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D21] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D22] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D23] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D24] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D25] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D26] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D27] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D28] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D29] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D30] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D98] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D99] WITH (NOLOCK)
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23jq5zRqcGHA.5048@.TK2MSFTNGP04.phx.gbl...
> John,
> I don't know if it's what you're seeing, but a common confusion here
> is that the *estimated* query plan shows all tables accessed with
> equal cost, but the *actual* plan only accesses the relevant table.
> In the query plan details, you will often see the dependence of
> each table's access hinging on STARTUP_EXPR, which is
> evaluated at run time before any of the tables are accessed.
> If you run the query with SET STATISTICS IO ON in cases
> like this, you will see all tables listed, but the number of reads
> for the unneeded tables will be zero.
> What exactly is indicating to you that "SQL Server is opening ALL
> tables in the view" ?
> Steve Kass
> Drew University
> John Kotuby wrote:
>|||Got caught up in work....
Here is a simple query that seems to be hitting only one table.
SET STATISTICS IO ON
SELECT DIVID, TRANID, INV_NUM FROM PLVWDIV_ACT_DET
WHERE DIVID = 'D02' AND ACT_TYPE = 'P' AND TRANDATE > '01/01/2006'
(1509 row(s) affected)
Table 'PLTBDIV_ACT_DET_D02'. Scan count 1, logical reads 5456, physical
reads 0,
read-ahead reads 4.
There are approximately 320,000 records in the PLTBDIV_ACT_DET_D02 table.
We join about 30 tables in the view.
We were watching Current Activity in EM at the site and refreshing every few
seconds and saw all tables in multiple partitioned views open at the same
time. In some cases every table had locks on them. We were getting timeouts
from the VB client.
When we changed our programming techniques to use Paramaterized queries that
hit the tables directly most of that contention just disappeared.
CREATE VIEW [PLVWDIV_ACT_DET]
-- DESC: UNIONED VIEW INTO ACCOUNTS DETAIL
AS
SELECT * FROM [PLTBCOM_ACT_DET] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D00] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D01] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D02] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D03] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D04] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D05] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D06] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D07] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D08] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D09] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D10] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D11] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D12] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D13] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D14] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D15] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D16] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D17] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D18] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D19] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D20] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D21] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D22] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D23] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D24] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D25] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D26] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D27] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D28] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D29] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D30] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D98] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D99] WITH (NOLOCK)
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:eVErOIqcGHA.2404@.TK2MSFTNGP03.phx.gbl...
> "John Kotuby" <johnk@.powerlist.com> wrote in message
> news:OW40DgpcGHA.3908@.TK2MSFTNGP02.phx.gbl...
> Too little information.
> Post the UNION view, along with a query againt the view, and the results
> for running the query with SET STATISTICS_IO ON.
> David
>

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
>