I need to parse out some alphabetic characters from a Varchar2 field to leave only the number values. The field values look like this $40-35 or say $40$35.
Is there a sql function or statement to do this. Any help is greatly appreciated.
andavianselect to_number(substr(substr('$40-35' ,2),1,2)), to_number(substr(substr('$40-35' ,2),4,2)) from dual
is your select statement to parse out your 2 dollar amounts.
Originally posted by andavian
I need to parse out some alphabetic characters from a Varchar2 field to leave only the number values. The field values look like this $40-35 or say $40$35.
Is there a sql function or statement to do this. Any help is greatly appreciated.
andavian|||This will work only if the position of $ sign is fixed and the length of the string is fixed. Is it So?|||Yes, this is because you have given a fixed length variable and did not mention about how dynamic your VARCHAR would be. This assumes ur variable will be either ''$40-35" or "$40-35" which you have mentioned in your Question.
Originally posted by Rushi
This will work only if the position of $ sign is fixed and the length of the string is fixed. Is it So?
Showing posts with label number. Show all posts
Showing posts with label number. Show all posts
Wednesday, March 28, 2012
Monday, March 26, 2012
Parent Package Variables and SQL Package Store
Hello All,
I have a Master Package which calls a group of other packages out on the SSIS Package Store using parent / child and a number of variables. These seem to work when passing various audit information (audit key, record counts, etc) but when I try to pass a variable for the connection string and assign it via an expression to the connection manager, I get the Master Package writing to the correct database and the children package writing to their "default" database which is supplied as the default value in the variables which should be populated by the parent task.
i.e., I end up with my master package audit information in database a and child package audit and transactions in database b, even though the packages associated with these transactions are supposed to be reading the connection string from a parent variable. Any clues or suggestions?
Edit: It is still passing along the correct parent audit information and record counts, it's just not connecting to the correct database by the variables holding the connect string
Thanks for your time.
After your issue the other day, it occurred to me that I do something differently, yet achieve the same thing.I pass in pieces of the connection strings to separate variables. (ServerName, DatabaseName, etc...)
Then, on the connection manager objects, I use their expressions to build its connection string based on those variables. So I'm not passing in a connection string -- I'm just passing in pieces. Then the connection manager's expression for "ConnectionString" takes it from there. Does that make sense?|||
yeah, that makes sense... I've been thinking about doing that for about a day now myself, although I was just wanting to get this to work first before going through with it. I think I will go ahead and change things over to build up the string dynamically, that way I won't have to retype everything in the set values tab every time I want to tinker with which database or server it is going to.
Right now I am building a test package that is passing the connection string and it works fine, however the test package is passing it to a pacakge on the file server (not in the sql package store). There shouldn't be any difference in how a package accepts parent variables between a file server executed package and a sql store executed package should there?
|||Well, I just spent a few hours converting those packages over to store the data source, initial catalog, name and password as seperatre variables and build the string up in the expression for the connection string. When I ran the package it was still pointing to the "default" values defined in the child package, even though I had changed the parent to point to a different data base within the source...
Any ideas?
|||
EWisdahl wrote:
Well, I just spent a few hours converting those packages over to store the data source, initial catalog, name and password as seperatre variables and build the string up in the expression for the connection string. When I ran the package it was still pointing to the "default" values defined in the child package, even though I had changed the parent to point to a different data base within the source...
Any ideas?
Are you certain that the parent package variable configuration is set up properly? You may want to try adding a Script Task to your control flow, and within it using a MessageBox to pop up the value being configured just to validate.
Personally, I solve this problem with XML file configurations. I have one dtsConfig file that drives the ConnectionString property of the connection, and have connection managers with the same name in my master and child packages, and this works great for me.
|||Also try setting DelayValidation to true on your connection manager that you are configuring.|||I am fairly certain that I have them set up correctly in that I am also passing along other information such as audit id, filename, recordcount, etc and that is all populating correctly. It seems like it has predetermined what the connection managers connection string will be before the package receives the parent variable configurations...
|||
Also try setting DelayValidation to true on your connection manager that you are configuring.
Didn't seem to help any...
Phil Brammer wrote:
Also try setting DelayValidation to true on your connection manager that you are configuring.
Didn't seem to help any...
Then make sure your parent package configurations are working correctly. Make sure you're mapping to the .Value parameter of your child variable. Also, CaSE matters, I believe. And I always just type in the name of the variable. No "User::" or "System::" prefix.|||
Double checked and triple checked. As I said, I am able to pass off certain values (auditkey, etc) no problem. As a matter of fact, I am able to pass the same connection string (or portions thereof to piece together into the connection string) if I am passing it to a package via the file system, it only seems to come up when passing parent package configuration variables from a parent to a child stored on the SSIS package store.
On a positive note, the set values through sql agent job maintenance works fine for the master package now ... If only it would pass these to the child packages (or if the child packages would pick it up from the master's "default") 
After reading another thread I think I may have described something a bit inaccurately. I am storing these packages on the SSIS Server under the msdb subfolder (i.e. connect to integration server, click subnode stored packages, msdb, packagefolder -> import) as opposed to the package store (same as above but click on the file system within the stored packages node). Would that make any difference?
|||Question: Are you using any other configurations in your child packages, besides the Parent Package Variables? If so, can you describe those?
|||Nope, just the parent package variables.
|||Well, i am currently facing something like this...
And i am starting to think that Parent Variables are somehow desync with the child package execution...
In my situation i have Execute SQL Task at the beggining of the package registering the inicial log record... and its beeing inserted with the default values on my child package instead of using the configured parent package values!
Later on i have that kind of connectionString mechanism and its working right... i think its a matter of delay... probably if you try putting a script task before using the connection, you will get your parent variables in time.
Had you tried it?
Just a though...
Regards
Parent Package Variables and SQL Package Store
Hello All,
I have a Master Package which calls a group of other packages out on the SSIS Package Store using parent / child and a number of variables. These seem to work when passing various audit information (audit key, record counts, etc) but when I try to pass a variable for the connection string and assign it via an expression to the connection manager, I get the Master Package writing to the correct database and the children package writing to their "default" database which is supplied as the default value in the variables which should be populated by the parent task.
i.e., I end up with my master package audit information in database a and child package audit and transactions in database b, even though the packages associated with these transactions are supposed to be reading the connection string from a parent variable. Any clues or suggestions?
Edit: It is still passing along the correct parent audit information and record counts, it's just not connecting to the correct database by the variables holding the connect string
Thanks for your time.
After your issue the other day, it occurred to me that I do something differently, yet achieve the same thing.I pass in pieces of the connection strings to separate variables. (ServerName, DatabaseName, etc...)
Then, on the connection manager objects, I use their expressions to build its connection string based on those variables. So I'm not passing in a connection string -- I'm just passing in pieces. Then the connection manager's expression for "ConnectionString" takes it from there. Does that make sense?|||
yeah, that makes sense... I've been thinking about doing that for about a day now myself, although I was just wanting to get this to work first before going through with it. I think I will go ahead and change things over to build up the string dynamically, that way I won't have to retype everything in the set values tab every time I want to tinker with which database or server it is going to.
Right now I am building a test package that is passing the connection string and it works fine, however the test package is passing it to a pacakge on the file server (not in the sql package store). There shouldn't be any difference in how a package accepts parent variables between a file server executed package and a sql store executed package should there?
|||Well, I just spent a few hours converting those packages over to store the data source, initial catalog, name and password as seperatre variables and build the string up in the expression for the connection string. When I ran the package it was still pointing to the "default" values defined in the child package, even though I had changed the parent to point to a different data base within the source...
Any ideas?
|||
Well, I just spent a few hours converting those packages over to store the data source, initial catalog, name and password as seperatre variables and build the string up in the expression for the connection string. When I ran the package it was still pointing to the "default" values defined in the child package, even though I had changed the parent to point to a different data base within the source...
Any ideas?
Are you certain that the parent package variable configuration is set up properly? You may want to try adding a Script Task to your control flow, and within it using a MessageBox to pop up the value being configured just to validate.
Personally, I solve this problem with XML file configurations. I have one dtsConfig file that drives the ConnectionString property of the connection, and have connection managers with the same name in my master and child packages, and this works great for me.
|||Also try setting DelayValidation to true on your connection manager that you are configuring.|||I am fairly certain that I have them set up correctly in that I am also passing along other information such as audit id, filename, recordcount, etc and that is all populating correctly. It seems like it has predetermined what the connection managers connection string will be before the package receives the parent variable configurations...
|||
Also try setting DelayValidation to true on your connection manager that you are configuring.
Didn't seem to help any...
Phil Brammer wrote:
Also try setting DelayValidation to true on your connection manager that you are configuring.
Didn't seem to help any...
Then make sure your parent package configurations are working correctly. Make sure you're mapping to the .Value parameter of your child variable. Also, CaSE matters, I believe. And I always just type in the name of the variable. No "User::" or "System::" prefix.|||
Double checked and triple checked. As I said, I am able to pass off certain values (auditkey, etc) no problem. As a matter of fact, I am able to pass the same connection string (or portions thereof to piece together into the connection string) if I am passing it to a package via the file system, it only seems to come up when passing parent package configuration variables from a parent to a child stored on the SSIS package store.
On a positive note, the set values through sql agent job maintenance works fine for the master package now ... If only it would pass these to the child packages (or if the child packages would pick it up from the master's "default")
After reading another thread I think I may have described something a bit inaccurately. I am storing these packages on the SSIS Server under the msdb subfolder (i.e. connect to integration server, click subnode stored packages, msdb, packagefolder -> import) as opposed to the package store (same as above but click on the file system within the stored packages node). Would that make any difference?
|||Question: Are you using any other configurations in your child packages, besides the Parent Package Variables? If so, can you describe those?
|||Nope, just the parent package variables.
|||Well, i am currently facing something like this...
And i am starting to think that Parent Variables are somehow desync with the child package execution...
In my situation i have Execute SQL Task at the beggining of the package registering the inicial log record... and its beeing inserted with the default values on my child package instead of using the configured parent package values!
Later on i have that kind of connectionString mechanism and its working right... i think its a matter of delay... probably if you try putting a script task before using the connection, you will get your parent variables in time.
Had you tried it?
Just a though...
Regards
Parent Package Variables and SQL Package Store
Hello All,
I have a Master Package which calls a group of other packages out on the SSIS Package Store using parent / child and a number of variables. These seem to work when passing various audit information (audit key, record counts, etc) but when I try to pass a variable for the connection string and assign it via an expression to the connection manager, I get the Master Package writing to the correct database and the children package writing to their "default" database which is supplied as the default value in the variables which should be populated by the parent task.
i.e., I end up with my master package audit information in database a and child package audit and transactions in database b, even though the packages associated with these transactions are supposed to be reading the connection string from a parent variable. Any clues or suggestions?
Edit: It is still passing along the correct parent audit information and record counts, it's just not connecting to the correct database by the variables holding the connect string
Thanks for your time.
After your issue the other day, it occurred to me that I do something differently, yet achieve the same thing.I pass in pieces of the connection strings to separate variables. (ServerName, DatabaseName, etc...)
Then, on the connection manager objects, I use their expressions to build its connection string based on those variables. So I'm not passing in a connection string -- I'm just passing in pieces. Then the connection manager's expression for "ConnectionString" takes it from there. Does that make sense?|||
yeah, that makes sense... I've been thinking about doing that for about a day now myself, although I was just wanting to get this to work first before going through with it. I think I will go ahead and change things over to build up the string dynamically, that way I won't have to retype everything in the set values tab every time I want to tinker with which database or server it is going to.
Right now I am building a test package that is passing the connection string and it works fine, however the test package is passing it to a pacakge on the file server (not in the sql package store). There shouldn't be any difference in how a package accepts parent variables between a file server executed package and a sql store executed package should there?
|||Well, I just spent a few hours converting those packages over to store the data source, initial catalog, name and password as seperatre variables and build the string up in the expression for the connection string. When I ran the package it was still pointing to the "default" values defined in the child package, even though I had changed the parent to point to a different data base within the source...
Any ideas?
|||
Well, I just spent a few hours converting those packages over to store the data source, initial catalog, name and password as seperatre variables and build the string up in the expression for the connection string. When I ran the package it was still pointing to the "default" values defined in the child package, even though I had changed the parent to point to a different data base within the source...
Any ideas?
Are you certain that the parent package variable configuration is set up properly? You may want to try adding a Script Task to your control flow, and within it using a MessageBox to pop up the value being configured just to validate.
Personally, I solve this problem with XML file configurations. I have one dtsConfig file that drives the ConnectionString property of the connection, and have connection managers with the same name in my master and child packages, and this works great for me.
|||Also try setting DelayValidation to true on your connection manager that you are configuring.|||I am fairly certain that I have them set up correctly in that I am also passing along other information such as audit id, filename, recordcount, etc and that is all populating correctly. It seems like it has predetermined what the connection managers connection string will be before the package receives the parent variable configurations...
|||
Also try setting DelayValidation to true on your connection manager that you are configuring.
Didn't seem to help any...
Phil Brammer wrote:
Also try setting DelayValidation to true on your connection manager that you are configuring.
Didn't seem to help any...
Then make sure your parent package configurations are working correctly. Make sure you're mapping to the .Value parameter of your child variable. Also, CaSE matters, I believe. And I always just type in the name of the variable. No "User::" or "System::" prefix.|||
Double checked and triple checked. As I said, I am able to pass off certain values (auditkey, etc) no problem. As a matter of fact, I am able to pass the same connection string (or portions thereof to piece together into the connection string) if I am passing it to a package via the file system, it only seems to come up when passing parent package configuration variables from a parent to a child stored on the SSIS package store.
On a positive note, the set values through sql agent job maintenance works fine for the master package now ... If only it would pass these to the child packages (or if the child packages would pick it up from the master's "default")
After reading another thread I think I may have described something a bit inaccurately. I am storing these packages on the SSIS Server under the msdb subfolder (i.e. connect to integration server, click subnode stored packages, msdb, packagefolder -> import) as opposed to the package store (same as above but click on the file system within the stored packages node). Would that make any difference?
|||Question: Are you using any other configurations in your child packages, besides the Parent Package Variables? If so, can you describe those?
|||Nope, just the parent package variables.
|||Well, i am currently facing something like this...
And i am starting to think that Parent Variables are somehow desync with the child package execution...
In my situation i have Execute SQL Task at the beggining of the package registering the inicial log record... and its beeing inserted with the default values on my child package instead of using the configured parent package values!
Later on i have that kind of connectionString mechanism and its working right... i think its a matter of delay... probably if you try putting a script task before using the connection, you will get your parent variables in time.
Had you tried it?
Just a though...
Regards
Parent Package is losing a child package
I have a parent package which calls a number of child packages. Occasionally, I have one child package which starts up and appears to finish but the parent package never gets notification that the child has finished. If I view this in Management Studio's in Integration Services, I can view the packages that are running and only the parent package is running. Because it never gets control back, the next child package will never get started and the parent package will continue to run and never finish.
Any idea what could be happening?
Jarand wrote:
I have a parent package which calls a number of child packages. Occasionally, I have one child package which starts up and appears to finish but the parent package never gets notification that the child has finished. If I view this in Management Studio's in Integration Services, I can view the packages that are running and only the parent package is running. Because it never gets control back, the next child package will never get started and the parent package will continue to run and never finish.
Any idea what could be happening?
You say the child package "appears" to finish. i wonder if that is the case. Take a look at the child package's log file and see if the package container raises an OnPostExecute event (it should be the last row in the file).
-Jamie
Wednesday, March 21, 2012
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
>
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
>
Friday, March 9, 2012
parameters in Reporting Services
When you are using a number of parameters in a report, how do you set it so that if a parameter is selected the query runs against that parameter, but if a parameter isn't selected, the query runs against all values for that parameter?
you should should give a default value to your parm(0 if it's int),then add to your sql script the condition...|||Yes, if you are using dropdownlist you can add one more item which will redirect you to span all records. You can modify your parameter listing query like below
SELECT LanguageId, Code FROM Languages UNION SELECT 0, 'ALL'
If you are using textboxes then if you trim the value and get nothing then you can code your datasource code not to filter on that column.
Eralper
http://www.kodyaz.com
Monday, February 20, 2012
parameterized query that counts the number of null values in an int32 column
Using c# in the compact framework, is there a way to do a parameterized query for counting the number of records where a specified column is null. I tried all of these, but none of them work:
cmd.Add(new SqlCeParameter("@.IntColumn", SqlInt32.Null));
cmd.CommandText = "select count(*) from TableName where IntColumn is not @.IntColumn";
cmd.Add(new SqlCeParameter("@.IntColumn", DBNull.Value));
cmd.CommandText = "select count(*) from TableName where IntColumn is not @.IntColumn";
cmd.Add(new SqlCeParameter("@.IntColumn", SqlInt32.Null));
cmd.CommandText = "select count(*) from TableName where not IntColumn = @.IntColumn";
cmd.Add(new SqlCeParameter("@.IntColumn", DBNull.Value));
cmd.CommandText = "select count(*) from TableName where not IntColumn = @.IntColumn";
cmd.Parameters.Add(new SqlCeParameter("@.IntColumn", SqlDbType.Int32));
cmd.Parameters["@.IntColumn"].Value = SqlInt32.Null;
cmdGetNumRead.CommandText = "select count(*) from TableName where IntColumn is not @.IntColumn";
cmd.Parameters.Add(new SqlCeParameter("@.IntColumn", SqlDbType.Int32));
cmd.Parameters["@.IntColumn"].Value = SqlInt32.Null;
cmdGetNumRead.CommandText = "select count(*) from TableName where not IntColumn = @.IntColumn";
cmd.Parameters.Add(new SqlCeParameter("@.IntColumn", SqlDbType.Int32));
cmd.Parameters["@.IntColumn"].Value = DBNull.Value;
cmdGetNumRead.CommandText = "select count(*) from TableName where IntColumn is not @.IntColumn";
cmd.Parameters.Add(new SqlCeParameter("@.IntColumn", SqlDbType.Int32));
cmd.Parameters["@.IntColumn"].Value = DBNull.Value;
cmdGetNumRead.CommandText = "select count(*) from TableName where not IntColumn = @.IntColumn";
cmd.Parameters.Add(new SqlCeParameter("@.IntColumn", SqlDbType.Variant));
cmd.Parameters["@.IntColumn"].Value = SqlInt32.Null;
cmd.CommandText = "select count(*) from Meter where IntColumn is not @.IntColumn";
cmd.Parameters.Add(new SqlCeParameter("@.IntColumn", SqlDbType.Variant));
cmd.Parameters["@.IntColumn"].Value = SqlInt32.Null;
cmd.CommandText = "select count(*) from Meter where not IntColumn = @.IntColumn";
md.Parameters.Add(new SqlCeParameter("@.IntColumn", SqlDbType.Variant));
cmd.Parameters["@.IntColumn"].Value = DBNull.Value;
cmd.CommandText = "select count(*) from Meter where IntColumn is not @.IntColumn";
cmd.Parameters.Add(new SqlCeParameter("@.IntColumn", SqlDbType.Variant));
cmd.Parameters["@.IntColumn"].Value = DBNull.Value;
cmd.CommandText = "select count(*) from Meter where not IntColumn = @.IntColumn";
Whenever I use a "is not" in the query I get an exception, otherwise it returns a count of 0 which is not accurate. Any help will be appreciated.
try using this syntax:
WHERE [columnName] != DBNull.Value or WHERE [columnName] <> DBNull.Value
Darren
|||I could not get that to work. Could you provide a more detailed examle of what the c# code would actually look like?|||
I think you've found yourself a bug in the ADO provider for SQL Mobile. I tried this code on the Northwind database and it reports 0 rows (should be 22):
SqlCeConnection cn = new SqlCeConnection(@."Data Source = \program files\deviceapplication7\Northwind.sdf");
try
{
cn.Open();
SqlCeCommand cmd = new SqlCeCommand("SELECT count(*) FROM CUSTOMERS WHERE FAX = @.fax", cn);
SqlCeParameter p1 = new SqlCeParameter("@.fax", DBNull.Value);
cmd.Parameters.Add(p1);
object o = cmd.ExecuteScalar();
if ( o != null && o != DBNull.Value)
{
MessageBox.Show("There were " + ((int)o).ToString() + " null fax numbers");
}
else
MessageBox.Show("Result of query was null");
This only occurs with a parameterized query however. If you use the normal syntax (IS NULL) in your query, you'll get the right results.
Darren
|||
Thanks for your response Daren. I started a new project and ran a program a program with the very similar code as what you tried and I got the same result. I was then wondering if the limitation/bug might be related to using SqlCeCommand.ExecuteScalar with parameterized queries in general rather than specifically looking for null or not null. So I tried this program and it returned a count of 1 which is correct:
SqlCeConnection conn = new SqlCeConnection(@."Data Source= \Program Files\BugTesting\Northwind.sdf;");
conn.Open();
SqlCeCommand cmd = conn.CreateCommand();
cmd.Parameters.Add(new SqlCeParameter("@.fax", SqlDbType.NVarChar, 24));
cmd.CommandText = "select COUNT(*) from Customers where Fax = @.fax";
cmd.Prepare();
cmd.Parameters["@.fax"].Value = "030-0076545";
object o = cmd.ExecuteScalar();
if (o != null && o != DBNull.Value)
{
MessageBox.Show("There were " + ((int)o).ToString() + " null fax numbers");
}
else
{
MessageBox.Show("Result of query was null");
}
conn.Close();
conn.Dispose();
This shows that you can do parameterized queries with count and execute scalar, but I still have not found a way to count the number of null values. I don't really know how parameterized queries work under the hood, but I would guess that the code that Darren posted would translate into something like this:
SELECT count(*) FROM CUSTOMERS WHERE FAX = null
If this is true then that would explain why it returns a count of 0. Which is why in my original post I was trying things more along the lines of: cmd.CommandText = "select count(*) from Customers where Fax is @.fax"
but that generates an SqlCeException. Is this a limitation, a bug, or is there still maybe another way to do this? Anyway the only reason why I was trying to do this as a parameterized query is because in my code runs it multiple times in a row, and I thought it might run faster this way. Even if I could get this to work somehow do think that it would improve the execution speed of the query? Also where is the best place to report this as a potential bug?
Subscribe to:
Posts (Atom)