I have the following tables in a mailng list management program:
CREATE TABLE [dbo].[tabMailGroupSubscribers] (
[MailSubscriberID] [uniqueidentifier] NOT NULL ,
[MailGroupID] [uniqueidentifier] NOT NULL ,
[UpdatedBy] [int] NOT NULL ,
[DateUpdated] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tabMailGroups] (
[MailGroupID] uniqueidentifier ROWGUIDCOL NOT NULL ,
[GroupName] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[MemberOf] [uniqueidentifier] NULL ,
[GroupEmailAddress] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Description] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Conditions] [ntext] COLLATE Latin1_General_CI_AS NULL ,
[DirectLink] [bit] NOT NULL ,
[UpdatedBy] [int] NOT NULL ,
[DateUpdated] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[tabMailSubscribers] (
[MailSubscriberID] uniqueidentifier ROWGUIDCOL NOT NULL ,
[HotelID] [int] NOT NULL ,
[Name] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Surname] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Designation] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[EmailAddress] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[UpdatedBy] [int] NOT NULL ,
[DateUpdated] [datetime] NOT NULL
) ON [PRIMARY]
GO
Problem:
How can I dispaly a list containing all the mailgroups in tabMailgroups, and
have the ability to see if a subscriber from tabMailSubscribers has
subscribed to a specific group, and wich not?
Something like this...
CREAT PROCEDURE dbo. proc_tabMailGroupSubscribers_MailSubscri
berID
@.MailSubscriberID varchar(50)
AS
Begin
SELECT tabMailGroups.MailGroupID, tabMailGroups.GroupName,
tabMailGroupSubscribers.MailSubscriberID, tabMailGroups.GroupEmailAddress,
tabMailGroups.Description, tabMailGroups.Conditions, (CASE WHEN
tabMailGroupSubscribers.MailSubscriberID IS NULL THEN 0 ELSE 1 END)
AS Subscribed
FROM tabMailGroups LEFT OUTER JOIN
tabMailGroupSubscribers ON tabMailGroups.MailGroupID =
tabMailGroupSubscribers.MailGroupID
WHERE (tabMailGroupSubscribers.MailSubscriberID = @.MailSubscriberID)
end
Return
Only problem is this is not working.
Thanks for any helpUse [tabMailGroupSubscribers].[MailGroupID] in the CASE expression.
...
(
CASE
WHEN [tabMailGroupSubscribers].[MailGroupID] IS NULL THEN 0
ELSE 1
END
) AS Subscribed
...
AMB
"HB" wrote:
> I have the following tables in a mailng list management program:
> CREATE TABLE [dbo].[tabMailGroupSubscribers] (
> [MailSubscriberID] [uniqueidentifier] NOT NULL ,
> [MailGroupID] [uniqueidentifier] NOT NULL ,
> [UpdatedBy] [int] NOT NULL ,
> [DateUpdated] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[tabMailGroups] (
> [MailGroupID] uniqueidentifier ROWGUIDCOL NOT NULL ,
> [GroupName] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> [MemberOf] [uniqueidentifier] NULL ,
> [GroupEmailAddress] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> [Description] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> [Conditions] [ntext] COLLATE Latin1_General_CI_AS NULL ,
> [DirectLink] [bit] NOT NULL ,
> [UpdatedBy] [int] NOT NULL ,
> [DateUpdated] [datetime] NOT NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[tabMailSubscribers] (
> [MailSubscriberID] uniqueidentifier ROWGUIDCOL NOT NULL ,
> [HotelID] [int] NOT NULL ,
> [Name] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> [Surname] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> [Designation] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> [EmailAddress] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> [UpdatedBy] [int] NOT NULL ,
> [DateUpdated] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
> Problem:
> How can I dispaly a list containing all the mailgroups in tabMailgroups, a
nd
> have the ability to see if a subscriber from tabMailSubscribers has
> subscribed to a specific group, and wich not?
> Something like this...
> CREAT PROCEDURE dbo. proc_tabMailGroupSubscribers_MailSubscri
berID
> @.MailSubscriberID varchar(50)
> AS
> Begin
> SELECT tabMailGroups.MailGroupID, tabMailGroups.GroupName,
> tabMailGroupSubscribers.MailSubscriberID, tabMailGroups.GroupEmailAddress,
> tabMailGroups.Description, tabMailGroups.Conditions, (CASE WHEN
> tabMailGroupSubscribers.MailSubscriberID IS NULL THEN 0 ELSE 1 END)
> AS Subscribed
> FROM tabMailGroups LEFT OUTER JOIN
> tabMailGroupSubscribers ON tabMailGroups.MailGroupID =
> tabMailGroupSubscribers.MailGroupID
> WHERE (tabMailGroupSubscribers.MailSubscriberID = @.MailSubscriberID)
> end
> Return
> Only problem is this is not working.
>
> Thanks for any help
>
>|||Hi AMB
This does not seem to do the trick.
It is still not returning the correct results. There is records outstanding
from the results.
Regards
Hennie
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:F1B1C8B2-62E6-4AF7-821A-F6DE740C0D10@.microsoft.com...
> Use [tabMailGroupSubscribers].[MailGroupID] in the CASE expression.
> ...
> (
> CASE
> WHEN [tabMailGroupSubscribers].[MailGroupID] IS NULL THEN 0
> ELSE 1
> END
> ) AS Subscribed
> ...
>
> AMB
> "HB" wrote:
>
Showing posts with label dbo. Show all posts
Showing posts with label dbo. Show all posts
Monday, March 26, 2012
Query problem
I have the following tables in a mailng list management program:
CREATE TABLE [dbo].[tabMailGroupSubscribers] (
[MailSubscriberID] [uniqueidentifier] NOT NULL ,
[MailGroupID] [uniqueidentifier] NOT NULL ,
[UpdatedBy] [int] NOT NULL ,
[DateUpdated] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tabMailGroups] (
[MailGroupID] uniqueidentifier ROWGUIDCOL NOT NULL ,
[GroupName] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[MemberOf] [uniqueidentifier] NULL ,
[GroupEmailAddress] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Description] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Conditions] [ntext] COLLATE Latin1_General_CI_AS NULL ,
[DirectLink] [bit] NOT NULL ,
[UpdatedBy] [int] NOT NULL ,
[DateUpdated] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[tabMailSubscribers] (
[MailSubscriberID] uniqueidentifier ROWGUIDCOL NOT NULL ,
[HotelID] [int] NOT NULL ,
[Name] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Surname] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Designation] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[EmailAddress] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[UpdatedBy] [int] NOT NULL ,
[DateUpdated] [datetime] NOT NULL
) ON [PRIMARY]
GO
Problem:
How can I dispaly a list containing all the mailgroups in tabMailgroups, and
have the ability to see if a subscriber from tabMailSubscribers has
subscribed to a specific group, and wich not?
Something like this...
CREAT PROCEDURE dbo. proc_tabMailGroupSubscribers_MailSubscri
berID
@.MailSubscriberID varchar(50)
AS
Begin
SELECT tabMailGroups.MailGroupID, tabMailGroups.GroupName,
tabMailGroupSubscribers.MailSubscriberID, tabMailGroups.GroupEmailAddress,
tabMailGroups.Description, tabMailGroups.Conditions, (CASE WHEN
tabMailGroupSubscribers.MailSubscriberID IS NULL THEN 0 ELSE 1 END)
AS Subscribed
FROM tabMailGroups LEFT OUTER JOIN
tabMailGroupSubscribers ON tabMailGroups.MailGroupID =
tabMailGroupSubscribers.MailGroupID
WHERE (tabMailGroupSubscribers.MailSubscriberID = @.MailSubscriberID)
end
Return
Only problem is this is not working.
Should I rateher change my table design to do this sort query, I'm not sure.
Thanks for any helpThe problem is that your where clause is making your LEFT OUTER JOIN
behave as an INNER JOIN. Try something like this:
CREATE PROCEDURE dbo. proc_tabMailGroupSubscribers_MailSubscri
berID
@.MailSubscriberID varchar(50)
AS
Begin
SELECT tabMailGroups.MailGroupID, tabMailGroups.GroupName,
tabMailGroupSubscribers.MailSubscriberID,
tabMailGroups.GroupEmailAddress,
tabMailGroups.Description, tabMailGroups.Conditions, (CASE WHEN
tabMailGroupSubscribers.MailSubscriberID IS NULL THEN 0 ELSE 1 END)
AS Subscribed
FROM tabMailGroups LEFT OUTER JOIN
tabMailGroupSubscribers ON tabMailGroups.MailGroupID =
tabMailGroupSubscribers.MailGroupID
AND tabMailGroupSubscribers.MailSubscriberID = @.MailSubscriberID
end
Return
Regards,
William D. Bartholomew
http://blog.bartholomew.id.au
*** Sent via Developersdex http://www.examnotes.net ***|||Hi William, thanks, it is working great.
Hennie
"William Bartholomew" <william(at)bartholomew[dot]id{dot}au> wrote in
message news:#RoA3QzOFHA.1564@.TK2MSFTNGP14.phx.gbl...
> The problem is that your where clause is making your LEFT OUTER JOIN
> behave as an INNER JOIN. Try something like this:
> CREATE PROCEDURE dbo. proc_tabMailGroupSubscribers_MailSubscri
berID
> @.MailSubscriberID varchar(50)
> AS
> Begin
> SELECT tabMailGroups.MailGroupID, tabMailGroups.GroupName,
> tabMailGroupSubscribers.MailSubscriberID,
> tabMailGroups.GroupEmailAddress,
> tabMailGroups.Description, tabMailGroups.Conditions, (CASE WHEN
> tabMailGroupSubscribers.MailSubscriberID IS NULL THEN 0 ELSE 1 END)
> AS Subscribed
> FROM tabMailGroups LEFT OUTER JOIN
> tabMailGroupSubscribers ON tabMailGroups.MailGroupID =
> tabMailGroupSubscribers.MailGroupID
> AND tabMailGroupSubscribers.MailSubscriberID = @.MailSubscriberID
> end
> Return
>
> Regards,
> William D. Bartholomew
> http://blog.bartholomew.id.au
> *** Sent via Developersdex http://www.examnotes.net ***
CREATE TABLE [dbo].[tabMailGroupSubscribers] (
[MailSubscriberID] [uniqueidentifier] NOT NULL ,
[MailGroupID] [uniqueidentifier] NOT NULL ,
[UpdatedBy] [int] NOT NULL ,
[DateUpdated] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tabMailGroups] (
[MailGroupID] uniqueidentifier ROWGUIDCOL NOT NULL ,
[GroupName] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[MemberOf] [uniqueidentifier] NULL ,
[GroupEmailAddress] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Description] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Conditions] [ntext] COLLATE Latin1_General_CI_AS NULL ,
[DirectLink] [bit] NOT NULL ,
[UpdatedBy] [int] NOT NULL ,
[DateUpdated] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[tabMailSubscribers] (
[MailSubscriberID] uniqueidentifier ROWGUIDCOL NOT NULL ,
[HotelID] [int] NOT NULL ,
[Name] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Surname] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Designation] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[EmailAddress] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[UpdatedBy] [int] NOT NULL ,
[DateUpdated] [datetime] NOT NULL
) ON [PRIMARY]
GO
Problem:
How can I dispaly a list containing all the mailgroups in tabMailgroups, and
have the ability to see if a subscriber from tabMailSubscribers has
subscribed to a specific group, and wich not?
Something like this...
CREAT PROCEDURE dbo. proc_tabMailGroupSubscribers_MailSubscri
berID
@.MailSubscriberID varchar(50)
AS
Begin
SELECT tabMailGroups.MailGroupID, tabMailGroups.GroupName,
tabMailGroupSubscribers.MailSubscriberID, tabMailGroups.GroupEmailAddress,
tabMailGroups.Description, tabMailGroups.Conditions, (CASE WHEN
tabMailGroupSubscribers.MailSubscriberID IS NULL THEN 0 ELSE 1 END)
AS Subscribed
FROM tabMailGroups LEFT OUTER JOIN
tabMailGroupSubscribers ON tabMailGroups.MailGroupID =
tabMailGroupSubscribers.MailGroupID
WHERE (tabMailGroupSubscribers.MailSubscriberID = @.MailSubscriberID)
end
Return
Only problem is this is not working.
Should I rateher change my table design to do this sort query, I'm not sure.
Thanks for any helpThe problem is that your where clause is making your LEFT OUTER JOIN
behave as an INNER JOIN. Try something like this:
CREATE PROCEDURE dbo. proc_tabMailGroupSubscribers_MailSubscri
berID
@.MailSubscriberID varchar(50)
AS
Begin
SELECT tabMailGroups.MailGroupID, tabMailGroups.GroupName,
tabMailGroupSubscribers.MailSubscriberID,
tabMailGroups.GroupEmailAddress,
tabMailGroups.Description, tabMailGroups.Conditions, (CASE WHEN
tabMailGroupSubscribers.MailSubscriberID IS NULL THEN 0 ELSE 1 END)
AS Subscribed
FROM tabMailGroups LEFT OUTER JOIN
tabMailGroupSubscribers ON tabMailGroups.MailGroupID =
tabMailGroupSubscribers.MailGroupID
AND tabMailGroupSubscribers.MailSubscriberID = @.MailSubscriberID
end
Return
Regards,
William D. Bartholomew
http://blog.bartholomew.id.au
*** Sent via Developersdex http://www.examnotes.net ***|||Hi William, thanks, it is working great.
Hennie
"William Bartholomew" <william(at)bartholomew[dot]id{dot}au> wrote in
message news:#RoA3QzOFHA.1564@.TK2MSFTNGP14.phx.gbl...
> The problem is that your where clause is making your LEFT OUTER JOIN
> behave as an INNER JOIN. Try something like this:
> CREATE PROCEDURE dbo. proc_tabMailGroupSubscribers_MailSubscri
berID
> @.MailSubscriberID varchar(50)
> AS
> Begin
> SELECT tabMailGroups.MailGroupID, tabMailGroups.GroupName,
> tabMailGroupSubscribers.MailSubscriberID,
> tabMailGroups.GroupEmailAddress,
> tabMailGroups.Description, tabMailGroups.Conditions, (CASE WHEN
> tabMailGroupSubscribers.MailSubscriberID IS NULL THEN 0 ELSE 1 END)
> AS Subscribed
> FROM tabMailGroups LEFT OUTER JOIN
> tabMailGroupSubscribers ON tabMailGroups.MailGroupID =
> tabMailGroupSubscribers.MailGroupID
> AND tabMailGroupSubscribers.MailSubscriberID = @.MailSubscriberID
> end
> Return
>
> Regards,
> William D. Bartholomew
> http://blog.bartholomew.id.au
> *** Sent via Developersdex http://www.examnotes.net ***
Friday, March 23, 2012
Query plans
Hi all,
I read somewhere that you should write the fully qualified name for a table(db.dbo.table) in order to have a better chance to match the statement to a queryplan/execution plan..
Ive tested it to some of the tables in your db with different results, in some cases the query without the "complete path" was up to 3 times heavier to execute(relatively to the batch) than the same query with qualified path(i ran the two at the same time with show execution plan).
Sometimes there was no difference at all 50%-50% cost. And i wasent able to find any logic reason for the sometimes quicker execution with qualified path, sometimes a didnt even had any statistics created for the column in the where clause when the question was faster than the other one.
type of statements executed:
Select Col1,Col2 From db.dbo.table1
Select Col1,Col2 From table1
Does anyone know exactly how this works?
Any help would be mostly appreciated
JonasThe times that they were equal - were both queries on the fast side or slow side ?|||"The times that they were equal - were both queries on the fast side or slow side ?"
I really dont know..They where both executed with the same cost. My guess is that both queries where matched to an existing execution plan and should therefor been on the fast side.
Q2: Lets say that we have a stored proc that is compiled and sqlserver has generated a execution plan for it, right. Does the created execution plan uses other already created plans for the statements in the proc or does it creates a completely new one just for the statments in the proc?|||Take a look at Recompilng a Stored Procedure in bol. If you need more information or a better explanation, let me know.
I read somewhere that you should write the fully qualified name for a table(db.dbo.table) in order to have a better chance to match the statement to a queryplan/execution plan..
Ive tested it to some of the tables in your db with different results, in some cases the query without the "complete path" was up to 3 times heavier to execute(relatively to the batch) than the same query with qualified path(i ran the two at the same time with show execution plan).
Sometimes there was no difference at all 50%-50% cost. And i wasent able to find any logic reason for the sometimes quicker execution with qualified path, sometimes a didnt even had any statistics created for the column in the where clause when the question was faster than the other one.
type of statements executed:
Select Col1,Col2 From db.dbo.table1
Select Col1,Col2 From table1
Does anyone know exactly how this works?
Any help would be mostly appreciated
JonasThe times that they were equal - were both queries on the fast side or slow side ?|||"The times that they were equal - were both queries on the fast side or slow side ?"
I really dont know..They where both executed with the same cost. My guess is that both queries where matched to an existing execution plan and should therefor been on the fast side.
Q2: Lets say that we have a stored proc that is compiled and sqlserver has generated a execution plan for it, right. Does the created execution plan uses other already created plans for the statements in the proc or does it creates a completely new one just for the statments in the proc?|||Take a look at Recompilng a Stored Procedure in bol. If you need more information or a better explanation, let me know.
Wednesday, March 21, 2012
query performance question
Hello. I have query performance question.
I need to optimize procedure
I need to optimize procedure
CREATE PROCEDURE dbo.SECUQUSRCOMPACCES
@.P1 VARCHAR(50),
@.P2 INTEGER
AS
DECLARE @.IORGANIZATIONID INTEGER
EXECUTE dbo.ORGNQGETORGID @.PORGUNIQUEID = @.IORGANIZATIONID OUTPUT
SELECT TSECCOMP.ID,
CASE TSECPROFILEGRP.ACCESSTYPE
WHEN -1 THEN
CASE TSECCLASS.DEFAULTACCESS
WHEN -1 THEN
CASE TSECGROUPCOMP.DEFAULTACCESS
WHEN -1 THEN
TSECCOMP.DEFAULTACCESS
ELSE
TSECGROUPCOMP.DEFAULTACCESS
END
ELSE
TSECCLASS.DEFAULTACCESS
END
ELSE TSECPROFILEGRP.ACCESSTYPE
END AS EXPR1
FROM TSECCOMP
INNER JOIN ((TSECPROFILE
INNER JOIN (TSECCLASS
INNER JOIN TSECPROFILEGRP
ON TSECCLASS.UNIQUEID = TSECPROFILEGRP.SECURITYGROUPID)
ON TSECPROFILE.UNIQUEID = TSECPROFILEGRP.PROFILEID) INNER JOIN
TSECGROUPCOMP ON TSECCLASS.UNIQUEID = TSECGROUPCOMP.SECURITYGROUPID)
ON TSECCOMP.UNIQUEID = TSECGROUPCOMP.SECCOMPID
WHERE
(
CASE TSECPROFILEGRP.ACCESSTYPE
WHEN -1 THEN
CASE TSECCLASS.DEFAULTACCESS
WHEN -1 THEN
CASE TSECGROUPCOMP.DEFAULTACCESS
WHEN -1 THEN
TSECCOMP.DEFAULTACCESS
ELSE
TSECGROUPCOMP.DEFAULTACCESS
END
ELSE
TSECCLASS.DEFAULTACCESS
END
ELSE TSECPROFILEGRP.ACCESSTYPE
END > 0 ) AND (TSECPROFILE.KEYVALUE=@.P1) AND ( TSECCOMP.TYPE =@.P2)
AND TSECCOMP.ORGANIZATIONID = @.IORGANIZATIONID
GO
Thank you In advance.Make sure you have indexed the join keys. You can try running the Index
Tuning Wizard for advice.
Gert-Jan
inna wrote:
> Hello. I have query performance question.
> I need to optimize procedure
> CREATE PROCEDURE dbo.SECUQUSRCOMPACCES
> @.P1 VARCHAR(50),
> @.P2 INTEGER
> AS
> DECLARE @.IORGANIZATIONID INTEGER
> EXECUTE dbo.ORGNQGETORGID @.PORGUNIQUEID = @.IORGANIZATIONID OUTPUT
> SELECT TSECCOMP.ID,
> CASE TSECPROFILEGRP.ACCESSTYPE
> WHEN -1 THEN
> CASE TSECCLASS.DEFAULTACCESS
> WHEN -1 THEN
> CASE TSECGROUPCOMP.DEFAULTACCESS
> WHEN -1 THEN
> TSECCOMP.DEFAULTACCESS
> ELSE
> TSECGROUPCOMP.DEFAULTACCESS
> END
> ELSE
> TSECCLASS.DEFAULTACCESS
> END
> ELSE TSECPROFILEGRP.ACCESSTYPE
> END AS EXPR1
> FROM TSECCOMP
> INNER JOIN ((TSECPROFILE
> INNER JOIN (TSECCLASS
> INNER JOIN TSECPROFILEGRP
> ON TSECCLASS.UNIQUEID = TSECPROFILEGRP.SECURITYGROUPID)
> ON TSECPROFILE.UNIQUEID = TSECPROFILEGRP.PROFILEID) INNER JOIN
> TSECGROUPCOMP ON TSECCLASS.UNIQUEID = TSECGROUPCOMP.SECURITYGROUPID)
> ON TSECCOMP.UNIQUEID = TSECGROUPCOMP.SECCOMPID
> WHERE
> (
> CASE TSECPROFILEGRP.ACCESSTYPE
> WHEN -1 THEN
> CASE TSECCLASS.DEFAULTACCESS
> WHEN -1 THEN
> CASE TSECGROUPCOMP.DEFAULTACCESS
> WHEN -1 THEN
> TSECCOMP.DEFAULTACCESS
> ELSE
> TSECGROUPCOMP.DEFAULTACCESS
> END
> ELSE
> TSECCLASS.DEFAULTACCESS
> END
> ELSE TSECPROFILEGRP.ACCESSTYPE
> END > 0 ) AND (TSECPROFILE.KEYVALUE=@.P1) AND ( TSECCOMP.TYPE =@.P2)
> AND TSECCOMP.ORGANIZATIONID = @.IORGANIZATIONID
> GO
> Thank you In advance.|||Hi
Check out the query execution plan
http://www.sql-server-performance.c...an_analysis.asp
http://www.sql-server-performance.com/transact_sql.asp
John
"inna" <mednyk@.hotmail.com> wrote in message
news:347a408b.0309131204.19c074e8@.posting.google.c om...
> Hello. I have query performance question.
> I need to optimize procedure
> CREATE PROCEDURE dbo.SECUQUSRCOMPACCES
> @.P1 VARCHAR(50),
> @.P2 INTEGER
> AS
> DECLARE @.IORGANIZATIONID INTEGER
> EXECUTE dbo.ORGNQGETORGID @.PORGUNIQUEID = @.IORGANIZATIONID OUTPUT
> SELECT TSECCOMP.ID,
> CASE TSECPROFILEGRP.ACCESSTYPE
> WHEN -1 THEN
> CASE TSECCLASS.DEFAULTACCESS
> WHEN -1 THEN
> CASE TSECGROUPCOMP.DEFAULTACCESS
> WHEN -1 THEN
> TSECCOMP.DEFAULTACCESS
> ELSE
> TSECGROUPCOMP.DEFAULTACCESS
> END
> ELSE
> TSECCLASS.DEFAULTACCESS
> END
> ELSE TSECPROFILEGRP.ACCESSTYPE
> END AS EXPR1
> FROM TSECCOMP
> INNER JOIN ((TSECPROFILE
> INNER JOIN (TSECCLASS
> INNER JOIN TSECPROFILEGRP
> ON TSECCLASS.UNIQUEID = TSECPROFILEGRP.SECURITYGROUPID)
> ON TSECPROFILE.UNIQUEID = TSECPROFILEGRP.PROFILEID) INNER JOIN
> TSECGROUPCOMP ON TSECCLASS.UNIQUEID = TSECGROUPCOMP.SECURITYGROUPID)
> ON TSECCOMP.UNIQUEID = TSECGROUPCOMP.SECCOMPID
> WHERE
> (
> CASE TSECPROFILEGRP.ACCESSTYPE
> WHEN -1 THEN
> CASE TSECCLASS.DEFAULTACCESS
> WHEN -1 THEN
> CASE TSECGROUPCOMP.DEFAULTACCESS
> WHEN -1 THEN
> TSECCOMP.DEFAULTACCESS
> ELSE
> TSECGROUPCOMP.DEFAULTACCESS
> END
> ELSE
> TSECCLASS.DEFAULTACCESS
> END
> ELSE TSECPROFILEGRP.ACCESSTYPE
> END > 0 ) AND (TSECPROFILE.KEYVALUE=@.P1) AND ( TSECCOMP.TYPE =@.P2)
> AND TSECCOMP.ORGANIZATIONID = @.IORGANIZATIONID
> GO
> Thank you In advance.
Tuesday, March 20, 2012
Query performance
Can anyone tell me if there‘s a difference how the queries below are treated
by the optimizer
Select A.cmpcode, B.cmpcode
From dbo.oas_docline B
Join dbo.oas_dochead A
On A.cmpcode = B.cmpcode
Where A.cmpcode = 'Microsoft' -- this is the diff
And B.cmpcode = 'Microsoft'
Select A.cmpcode, B.cmpcode
From dbo.oas_docline B
Join dbo.oas_dochead A
On A.cmpcode = B.cmpcode
And A.cmpcode = 'Microsoft'
And B.cmpcode = 'Microsoft'
SUnny
Hi Sanjay
both the queries has the same performance
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
"Sanjay" wrote:
> Can anyone tell me if there‘s a difference how the queries below are treated
> by the optimizer
> Select A.cmpcode, B.cmpcode
> From dbo.oas_docline B
> Join dbo.oas_dochead A
> On A.cmpcode = B.cmpcode
> Where A.cmpcode = 'Microsoft' -- this is the diff
> And B.cmpcode = 'Microsoft'
>
> Select A.cmpcode, B.cmpcode
> From dbo.oas_docline B
> Join dbo.oas_dochead A
> On A.cmpcode = B.cmpcode
> And A.cmpcode = 'Microsoft'
> And B.cmpcode = 'Microsoft'
>
> --
> SUnny
|||Same same. It doesn't matter if you specify a filter in the FROM or WHERE clause for an inner joins.
It will produce the same result and the optimizer know that, so the optimizer has the same
flexibility to product query plans...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:6B3328C5-B4EC-4A7F-8CDD-3D69BF745067@.microsoft.com...
> Can anyone tell me if there's a difference how the queries below are treated
> by the optimizer
> Select A.cmpcode, B.cmpcode
> From dbo.oas_docline B
> Join dbo.oas_dochead A
> On A.cmpcode = B.cmpcode
> Where A.cmpcode = 'Microsoft' -- this is the diff
> And B.cmpcode = 'Microsoft'
>
> Select A.cmpcode, B.cmpcode
> From dbo.oas_docline B
> Join dbo.oas_dochead A
> On A.cmpcode = B.cmpcode
> And A.cmpcode = 'Microsoft'
> And B.cmpcode = 'Microsoft'
>
> --
> SUnny
|||Sanjay wrote on Thu, 26 May 2005 03:25:17 -0700:
> Can anyone tell me if theres a difference how the queries below are
> treated by the optimizer
> Select A.cmpcode, B.cmpcode
> From dbo.oas_docline B
> Join dbo.oas_dochead A
> On A.cmpcode = B.cmpcode
> Where A.cmpcode = 'Microsoft' -- this is the diff
> And B.cmpcode = 'Microsoft'
> Select A.cmpcode, B.cmpcode
> From dbo.oas_docline B
> Join dbo.oas_dochead A
> On A.cmpcode = B.cmpcode
> And A.cmpcode = 'Microsoft'
> And B.cmpcode = 'Microsoft'
>
If you ever want to compare the query plans, put both into QA with a go
after each statement, click the Show Execution Plan on the Query menu, and
run them both. Then look at the Execution Plan tab to see how they compare.
There are probably better ways to check, but I'm still learning the
intricacies of SQL Server DBA.
Dan
by the optimizer
Select A.cmpcode, B.cmpcode
From dbo.oas_docline B
Join dbo.oas_dochead A
On A.cmpcode = B.cmpcode
Where A.cmpcode = 'Microsoft' -- this is the diff
And B.cmpcode = 'Microsoft'
Select A.cmpcode, B.cmpcode
From dbo.oas_docline B
Join dbo.oas_dochead A
On A.cmpcode = B.cmpcode
And A.cmpcode = 'Microsoft'
And B.cmpcode = 'Microsoft'
SUnny
Hi Sanjay
both the queries has the same performance
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
"Sanjay" wrote:
> Can anyone tell me if there‘s a difference how the queries below are treated
> by the optimizer
> Select A.cmpcode, B.cmpcode
> From dbo.oas_docline B
> Join dbo.oas_dochead A
> On A.cmpcode = B.cmpcode
> Where A.cmpcode = 'Microsoft' -- this is the diff
> And B.cmpcode = 'Microsoft'
>
> Select A.cmpcode, B.cmpcode
> From dbo.oas_docline B
> Join dbo.oas_dochead A
> On A.cmpcode = B.cmpcode
> And A.cmpcode = 'Microsoft'
> And B.cmpcode = 'Microsoft'
>
> --
> SUnny
|||Same same. It doesn't matter if you specify a filter in the FROM or WHERE clause for an inner joins.
It will produce the same result and the optimizer know that, so the optimizer has the same
flexibility to product query plans...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:6B3328C5-B4EC-4A7F-8CDD-3D69BF745067@.microsoft.com...
> Can anyone tell me if there's a difference how the queries below are treated
> by the optimizer
> Select A.cmpcode, B.cmpcode
> From dbo.oas_docline B
> Join dbo.oas_dochead A
> On A.cmpcode = B.cmpcode
> Where A.cmpcode = 'Microsoft' -- this is the diff
> And B.cmpcode = 'Microsoft'
>
> Select A.cmpcode, B.cmpcode
> From dbo.oas_docline B
> Join dbo.oas_dochead A
> On A.cmpcode = B.cmpcode
> And A.cmpcode = 'Microsoft'
> And B.cmpcode = 'Microsoft'
>
> --
> SUnny
|||Sanjay wrote on Thu, 26 May 2005 03:25:17 -0700:
> Can anyone tell me if theres a difference how the queries below are
> treated by the optimizer
> Select A.cmpcode, B.cmpcode
> From dbo.oas_docline B
> Join dbo.oas_dochead A
> On A.cmpcode = B.cmpcode
> Where A.cmpcode = 'Microsoft' -- this is the diff
> And B.cmpcode = 'Microsoft'
> Select A.cmpcode, B.cmpcode
> From dbo.oas_docline B
> Join dbo.oas_dochead A
> On A.cmpcode = B.cmpcode
> And A.cmpcode = 'Microsoft'
> And B.cmpcode = 'Microsoft'
>
If you ever want to compare the query plans, put both into QA with a go
after each statement, click the Show Execution Plan on the Query menu, and
run them both. Then look at the Execution Plan tab to see how they compare.
There are probably better ways to check, but I'm still learning the
intricacies of SQL Server DBA.
Dan
Query performance
Can anyone tell me if there‘s a difference how the queries below are treat
ed
by the optimizer
Select A.cmpcode, B.cmpcode
From dbo.oas_docline B
Join dbo.oas_dochead A
On A.cmpcode = B.cmpcode
Where A.cmpcode = 'Microsoft' -- this is the diff
And B.cmpcode = 'Microsoft'
Select A.cmpcode, B.cmpcode
From dbo.oas_docline B
Join dbo.oas_dochead A
On A.cmpcode = B.cmpcode
And A.cmpcode = 'Microsoft'
And B.cmpcode = 'Microsoft'
SUnnyHi Sanjay
both the queries has the same performance
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Sanjay" wrote:
> Can anyone tell me if there‘s a difference how the queries below are tre
ated
> by the optimizer
> Select A.cmpcode, B.cmpcode
> From dbo.oas_docline B
> Join dbo.oas_dochead A
> On A.cmpcode = B.cmpcode
> Where A.cmpcode = 'Microsoft' -- this is the diff
> And B.cmpcode = 'Microsoft'
>
> Select A.cmpcode, B.cmpcode
> From dbo.oas_docline B
> Join dbo.oas_dochead A
> On A.cmpcode = B.cmpcode
> And A.cmpcode = 'Microsoft'
> And B.cmpcode = 'Microsoft'
>
> --
> SUnny|||Same same. It doesn't matter if you specify a filter in the FROM or WHERE cl
ause for an inner joins.
It will produce the same result and the optimizer know that, so the optimize
r has the same
flexibility to product query plans...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:6B3328C5-B4EC-4A7F-8CDD-3D69BF745067@.microsoft.com...
> Can anyone tell me if there's a difference how the queries below are treat
ed
> by the optimizer
> Select A.cmpcode, B.cmpcode
> From dbo.oas_docline B
> Join dbo.oas_dochead A
> On A.cmpcode = B.cmpcode
> Where A.cmpcode = 'Microsoft' -- this is the diff
> And B.cmpcode = 'Microsoft'
>
> Select A.cmpcode, B.cmpcode
> From dbo.oas_docline B
> Join dbo.oas_dochead A
> On A.cmpcode = B.cmpcode
> And A.cmpcode = 'Microsoft'
> And B.cmpcode = 'Microsoft'
>
> --
> SUnny|||Sanjay wrote on Thu, 26 May 2005 03:25:17 -0700:
> Can anyone tell me if theres a difference how the queries below are
> treated by the optimizer
> Select A.cmpcode, B.cmpcode
> From dbo.oas_docline B
> Join dbo.oas_dochead A
> On A.cmpcode = B.cmpcode
> Where A.cmpcode = 'Microsoft' -- this is the diff
> And B.cmpcode = 'Microsoft'
> Select A.cmpcode, B.cmpcode
> From dbo.oas_docline B
> Join dbo.oas_dochead A
> On A.cmpcode = B.cmpcode
> And A.cmpcode = 'Microsoft'
> And B.cmpcode = 'Microsoft'
>
If you ever want to compare the query plans, put both into QA with a go
after each statement, click the Show Execution Plan on the Query menu, and
run them both. Then look at the Execution Plan tab to see how they compare.
There are probably better ways to check, but I'm still learning the
intricacies of SQL Server DBA.
Dan
ed
by the optimizer
Select A.cmpcode, B.cmpcode
From dbo.oas_docline B
Join dbo.oas_dochead A
On A.cmpcode = B.cmpcode
Where A.cmpcode = 'Microsoft' -- this is the diff
And B.cmpcode = 'Microsoft'
Select A.cmpcode, B.cmpcode
From dbo.oas_docline B
Join dbo.oas_dochead A
On A.cmpcode = B.cmpcode
And A.cmpcode = 'Microsoft'
And B.cmpcode = 'Microsoft'
SUnnyHi Sanjay
both the queries has the same performance
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Sanjay" wrote:
> Can anyone tell me if there‘s a difference how the queries below are tre
ated
> by the optimizer
> Select A.cmpcode, B.cmpcode
> From dbo.oas_docline B
> Join dbo.oas_dochead A
> On A.cmpcode = B.cmpcode
> Where A.cmpcode = 'Microsoft' -- this is the diff
> And B.cmpcode = 'Microsoft'
>
> Select A.cmpcode, B.cmpcode
> From dbo.oas_docline B
> Join dbo.oas_dochead A
> On A.cmpcode = B.cmpcode
> And A.cmpcode = 'Microsoft'
> And B.cmpcode = 'Microsoft'
>
> --
> SUnny|||Same same. It doesn't matter if you specify a filter in the FROM or WHERE cl
ause for an inner joins.
It will produce the same result and the optimizer know that, so the optimize
r has the same
flexibility to product query plans...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:6B3328C5-B4EC-4A7F-8CDD-3D69BF745067@.microsoft.com...
> Can anyone tell me if there's a difference how the queries below are treat
ed
> by the optimizer
> Select A.cmpcode, B.cmpcode
> From dbo.oas_docline B
> Join dbo.oas_dochead A
> On A.cmpcode = B.cmpcode
> Where A.cmpcode = 'Microsoft' -- this is the diff
> And B.cmpcode = 'Microsoft'
>
> Select A.cmpcode, B.cmpcode
> From dbo.oas_docline B
> Join dbo.oas_dochead A
> On A.cmpcode = B.cmpcode
> And A.cmpcode = 'Microsoft'
> And B.cmpcode = 'Microsoft'
>
> --
> SUnny|||Sanjay wrote on Thu, 26 May 2005 03:25:17 -0700:
> Can anyone tell me if theres a difference how the queries below are
> treated by the optimizer
> Select A.cmpcode, B.cmpcode
> From dbo.oas_docline B
> Join dbo.oas_dochead A
> On A.cmpcode = B.cmpcode
> Where A.cmpcode = 'Microsoft' -- this is the diff
> And B.cmpcode = 'Microsoft'
> Select A.cmpcode, B.cmpcode
> From dbo.oas_docline B
> Join dbo.oas_dochead A
> On A.cmpcode = B.cmpcode
> And A.cmpcode = 'Microsoft'
> And B.cmpcode = 'Microsoft'
>
If you ever want to compare the query plans, put both into QA with a go
after each statement, click the Show Execution Plan on the Query menu, and
run them both. Then look at the Execution Plan tab to see how they compare.
There are probably better ways to check, but I'm still learning the
intricacies of SQL Server DBA.
Dan
Monday, March 12, 2012
Query Optimizer Question
SELECT dbo.ProjectTran.ProjectRowID, ProjectTran.RowID,
COALESCE(dbo.ProjectTran.ProjectWBSRowID,0),
PA.BillingTypeRowID,
'REVENUE',
dbo.ProjectTran.PriorInvoiceAmount-(SELECT
COALESCE(SUM(dbo.ProjectInvoiceDetail.InvoiceAmoun t),0)
FROM dbo.ProjectInvoiceDetail
INNER JOIN dbo.ProjectInvoice on
dbo.ProjectInvoiceDetail.ProjectInvoiceRowID = dbo.ProjectInvoice.RowID
Where dbo.ProjectInvoice.LedgerDocRowID IS NOT NULL
AND dbo.ProjectInvoice.StatusRowID = 1165
AND dbo.ProjectInvoiceDetail.InvoiceAmount <> 0
AND dbo.ProjectInvoiceDetail.ProjectTranRowID =
ProjectTran.RowID) AS Revenue,
dbo.ProjectTran.PriorInvoiceUnits-(SELECT
COALESCE(SUM(dbo.ProjectInvoiceDetail.InvoiceUnits ),0)
FROM dbo.ProjectInvoiceDetail
INNER JOIN dbo.ProjectInvoice on
dbo.ProjectInvoiceDetail.ProjectInvoiceRowID = dbo.ProjectInvoice.RowID
Where dbo.ProjectInvoice.LedgerDocRowID IS NOT NULL
AND dbo.ProjectInvoice.StatusRowID = 1165
AND dbo.ProjectInvoiceDetail.InvoiceAmount <> 0
AND dbo.ProjectInvoiceDetail.ProjectTranRowID =
ProjectTran.RowID) AS BilledUnits,
dbo.ProjectTran.PriorInvoiceAmount -(SELECT
COALESCE(SUM(dbo.ProjectInvoiceDetail.InvoiceAmoun t),0)
FROM dbo.ProjectInvoiceDetail
INNER JOIN dbo.ProjectInvoice on
dbo.ProjectInvoiceDetail.ProjectInvoiceRowID = dbo.ProjectInvoice.RowID
Where dbo.ProjectInvoice.LedgerDocRowID IS NOT NULL
AND dbo.ProjectInvoice.StatusRowID = 1165
AND dbo.ProjectInvoiceDetail.InvoiceAmount <> 0
AND dbo.ProjectInvoiceDetail.ProjectTranRowID =
ProjectTran.RowID) AS BilledAmount
FROM dbo.ProjectTran
LEFT OUTER JOIN @.ProjectAccount PA ON
dbo.ProjectTran.ProjectAccountRowID = PA.ProjectAccountRowID
-- LEFT OUTER JOIN @.ProjectAccount PA2 ON
dbo.ProjectTran.LastInvoiceProjectAccountRowID = PA2.ProjectAccountRowID
INNER JOIN dbo.ProjectDoc ON dbo.ProjectTran.ProjectDocRowID =
dbo.ProjectDoc.RowID
Where dbo.ProjectTran.ProjectRowID in (Select RowID From #ProjectTemp)
AND dbo.ProjectTran.ProjectDocRowID IS NOT NULL
AND dbo.ProjectDoc.StatusRowID = 1003
AND ProjectTran.RowID NOT IN (Select TransactionRowID From
@.ProjectDataDetail WHERE AccountTypeDescription = 'REVENUE')
Amos Soma wrote:
> SELECT dbo.ProjectTran.ProjectRowID, ProjectTran.RowID,
> <SNIP>
That's a very nice query. What's the question :-)
David Gugick - SQL Server MVP
Quest Software
|||I'm sorry - I didn't mean to post this in this condition. Another post is
coming.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:u6ggfDzMGHA.2828@.TK2MSFTNGP12.phx.gbl...
> Amos Soma wrote:
> That's a very nice query. What's the question :-)
>
> --
> David Gugick - SQL Server MVP
> Quest Software
>
COALESCE(dbo.ProjectTran.ProjectWBSRowID,0),
PA.BillingTypeRowID,
'REVENUE',
dbo.ProjectTran.PriorInvoiceAmount-(SELECT
COALESCE(SUM(dbo.ProjectInvoiceDetail.InvoiceAmoun t),0)
FROM dbo.ProjectInvoiceDetail
INNER JOIN dbo.ProjectInvoice on
dbo.ProjectInvoiceDetail.ProjectInvoiceRowID = dbo.ProjectInvoice.RowID
Where dbo.ProjectInvoice.LedgerDocRowID IS NOT NULL
AND dbo.ProjectInvoice.StatusRowID = 1165
AND dbo.ProjectInvoiceDetail.InvoiceAmount <> 0
AND dbo.ProjectInvoiceDetail.ProjectTranRowID =
ProjectTran.RowID) AS Revenue,
dbo.ProjectTran.PriorInvoiceUnits-(SELECT
COALESCE(SUM(dbo.ProjectInvoiceDetail.InvoiceUnits ),0)
FROM dbo.ProjectInvoiceDetail
INNER JOIN dbo.ProjectInvoice on
dbo.ProjectInvoiceDetail.ProjectInvoiceRowID = dbo.ProjectInvoice.RowID
Where dbo.ProjectInvoice.LedgerDocRowID IS NOT NULL
AND dbo.ProjectInvoice.StatusRowID = 1165
AND dbo.ProjectInvoiceDetail.InvoiceAmount <> 0
AND dbo.ProjectInvoiceDetail.ProjectTranRowID =
ProjectTran.RowID) AS BilledUnits,
dbo.ProjectTran.PriorInvoiceAmount -(SELECT
COALESCE(SUM(dbo.ProjectInvoiceDetail.InvoiceAmoun t),0)
FROM dbo.ProjectInvoiceDetail
INNER JOIN dbo.ProjectInvoice on
dbo.ProjectInvoiceDetail.ProjectInvoiceRowID = dbo.ProjectInvoice.RowID
Where dbo.ProjectInvoice.LedgerDocRowID IS NOT NULL
AND dbo.ProjectInvoice.StatusRowID = 1165
AND dbo.ProjectInvoiceDetail.InvoiceAmount <> 0
AND dbo.ProjectInvoiceDetail.ProjectTranRowID =
ProjectTran.RowID) AS BilledAmount
FROM dbo.ProjectTran
LEFT OUTER JOIN @.ProjectAccount PA ON
dbo.ProjectTran.ProjectAccountRowID = PA.ProjectAccountRowID
-- LEFT OUTER JOIN @.ProjectAccount PA2 ON
dbo.ProjectTran.LastInvoiceProjectAccountRowID = PA2.ProjectAccountRowID
INNER JOIN dbo.ProjectDoc ON dbo.ProjectTran.ProjectDocRowID =
dbo.ProjectDoc.RowID
Where dbo.ProjectTran.ProjectRowID in (Select RowID From #ProjectTemp)
AND dbo.ProjectTran.ProjectDocRowID IS NOT NULL
AND dbo.ProjectDoc.StatusRowID = 1003
AND ProjectTran.RowID NOT IN (Select TransactionRowID From
@.ProjectDataDetail WHERE AccountTypeDescription = 'REVENUE')
Amos Soma wrote:
> SELECT dbo.ProjectTran.ProjectRowID, ProjectTran.RowID,
> <SNIP>
That's a very nice query. What's the question :-)
David Gugick - SQL Server MVP
Quest Software
|||I'm sorry - I didn't mean to post this in this condition. Another post is
coming.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:u6ggfDzMGHA.2828@.TK2MSFTNGP12.phx.gbl...
> Amos Soma wrote:
> That's a very nice query. What's the question :-)
>
> --
> David Gugick - SQL Server MVP
> Quest Software
>
Query Optimizer Question
SELECT dbo.ProjectTran.ProjectRowID, ProjectTran.RowID,
COALESCE(dbo.ProjectTran.ProjectWBSRowID,0),
PA.BillingTypeRowID,
'REVENUE',
dbo.ProjectTran.PriorInvoiceAmount-(SELECT
COALESCE(SUM(dbo.ProjectInvoiceDetail.InvoiceAmount),0)
FROM dbo.ProjectInvoiceDetail
INNER JOIN dbo.ProjectInvoice on
dbo.ProjectInvoiceDetail.ProjectInvoiceRowID = dbo.ProjectInvoice.RowID
Where dbo.ProjectInvoice.LedgerDocRowID IS NOT NULL
AND dbo.ProjectInvoice.StatusRowID = 1165
AND dbo.ProjectInvoiceDetail.InvoiceAmount <> 0
AND dbo.ProjectInvoiceDetail.ProjectTranRowID =
ProjectTran.RowID) AS Revenue,
dbo.ProjectTran.PriorInvoiceUnits-(SELECT
COALESCE(SUM(dbo.ProjectInvoiceDetail.InvoiceUnits),0)
FROM dbo.ProjectInvoiceDetail
INNER JOIN dbo.ProjectInvoice on
dbo.ProjectInvoiceDetail.ProjectInvoiceRowID = dbo.ProjectInvoice.RowID
Where dbo.ProjectInvoice.LedgerDocRowID IS NOT NULL
AND dbo.ProjectInvoice.StatusRowID = 1165
AND dbo.ProjectInvoiceDetail.InvoiceAmount <> 0
AND dbo.ProjectInvoiceDetail.ProjectTranRowID =
ProjectTran.RowID) AS BilledUnits,
dbo.ProjectTran.PriorInvoiceAmount -(SELECT
COALESCE(SUM(dbo.ProjectInvoiceDetail.InvoiceAmount),0)
FROM dbo.ProjectInvoiceDetail
INNER JOIN dbo.ProjectInvoice on
dbo.ProjectInvoiceDetail.ProjectInvoiceRowID = dbo.ProjectInvoice.RowID
Where dbo.ProjectInvoice.LedgerDocRowID IS NOT NULL
AND dbo.ProjectInvoice.StatusRowID = 1165
AND dbo.ProjectInvoiceDetail.InvoiceAmount <> 0
AND dbo.ProjectInvoiceDetail.ProjectTranRowID =
ProjectTran.RowID) AS BilledAmount
FROM dbo.ProjectTran
LEFT OUTER JOIN @.ProjectAccount PA ON
dbo.ProjectTran.ProjectAccountRowID = PA.ProjectAccountRowID
-- LEFT OUTER JOIN @.ProjectAccount PA2 ON
dbo.ProjectTran.LastInvoiceProjectAccountRowID = PA2.ProjectAccountRowID
INNER JOIN dbo.ProjectDoc ON dbo.ProjectTran.ProjectDocRowID =
dbo.ProjectDoc.RowID
Where dbo.ProjectTran.ProjectRowID in (Select RowID From #ProjectTemp)
AND dbo.ProjectTran.ProjectDocRowID IS NOT NULL
AND dbo.ProjectDoc.StatusRowID = 1003
AND ProjectTran.RowID NOT IN (Select TransactionRowID From
@.ProjectDataDetail WHERE AccountTypeDescription = 'REVENUE')Amos Soma wrote:
> SELECT dbo.ProjectTran.ProjectRowID, ProjectTran.RowID,
> <SNIP>
That's a very nice query. What's the question :-)
David Gugick - SQL Server MVP
Quest Software|||I'm sorry - I didn't mean to post this in this condition. Another post is
coming.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:u6ggfDzMGHA.2828@.TK2MSFTNGP12.phx.gbl...
> Amos Soma wrote:
> That's a very nice query. What's the question :-)
>
> --
> David Gugick - SQL Server MVP
> Quest Software
>
COALESCE(dbo.ProjectTran.ProjectWBSRowID,0),
PA.BillingTypeRowID,
'REVENUE',
dbo.ProjectTran.PriorInvoiceAmount-(SELECT
COALESCE(SUM(dbo.ProjectInvoiceDetail.InvoiceAmount),0)
FROM dbo.ProjectInvoiceDetail
INNER JOIN dbo.ProjectInvoice on
dbo.ProjectInvoiceDetail.ProjectInvoiceRowID = dbo.ProjectInvoice.RowID
Where dbo.ProjectInvoice.LedgerDocRowID IS NOT NULL
AND dbo.ProjectInvoice.StatusRowID = 1165
AND dbo.ProjectInvoiceDetail.InvoiceAmount <> 0
AND dbo.ProjectInvoiceDetail.ProjectTranRowID =
ProjectTran.RowID) AS Revenue,
dbo.ProjectTran.PriorInvoiceUnits-(SELECT
COALESCE(SUM(dbo.ProjectInvoiceDetail.InvoiceUnits),0)
FROM dbo.ProjectInvoiceDetail
INNER JOIN dbo.ProjectInvoice on
dbo.ProjectInvoiceDetail.ProjectInvoiceRowID = dbo.ProjectInvoice.RowID
Where dbo.ProjectInvoice.LedgerDocRowID IS NOT NULL
AND dbo.ProjectInvoice.StatusRowID = 1165
AND dbo.ProjectInvoiceDetail.InvoiceAmount <> 0
AND dbo.ProjectInvoiceDetail.ProjectTranRowID =
ProjectTran.RowID) AS BilledUnits,
dbo.ProjectTran.PriorInvoiceAmount -(SELECT
COALESCE(SUM(dbo.ProjectInvoiceDetail.InvoiceAmount),0)
FROM dbo.ProjectInvoiceDetail
INNER JOIN dbo.ProjectInvoice on
dbo.ProjectInvoiceDetail.ProjectInvoiceRowID = dbo.ProjectInvoice.RowID
Where dbo.ProjectInvoice.LedgerDocRowID IS NOT NULL
AND dbo.ProjectInvoice.StatusRowID = 1165
AND dbo.ProjectInvoiceDetail.InvoiceAmount <> 0
AND dbo.ProjectInvoiceDetail.ProjectTranRowID =
ProjectTran.RowID) AS BilledAmount
FROM dbo.ProjectTran
LEFT OUTER JOIN @.ProjectAccount PA ON
dbo.ProjectTran.ProjectAccountRowID = PA.ProjectAccountRowID
-- LEFT OUTER JOIN @.ProjectAccount PA2 ON
dbo.ProjectTran.LastInvoiceProjectAccountRowID = PA2.ProjectAccountRowID
INNER JOIN dbo.ProjectDoc ON dbo.ProjectTran.ProjectDocRowID =
dbo.ProjectDoc.RowID
Where dbo.ProjectTran.ProjectRowID in (Select RowID From #ProjectTemp)
AND dbo.ProjectTran.ProjectDocRowID IS NOT NULL
AND dbo.ProjectDoc.StatusRowID = 1003
AND ProjectTran.RowID NOT IN (Select TransactionRowID From
@.ProjectDataDetail WHERE AccountTypeDescription = 'REVENUE')Amos Soma wrote:
> SELECT dbo.ProjectTran.ProjectRowID, ProjectTran.RowID,
> <SNIP>
That's a very nice query. What's the question :-)
David Gugick - SQL Server MVP
Quest Software|||I'm sorry - I didn't mean to post this in this condition. Another post is
coming.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:u6ggfDzMGHA.2828@.TK2MSFTNGP12.phx.gbl...
> Amos Soma wrote:
> That's a very nice query. What's the question :-)
>
> --
> David Gugick - SQL Server MVP
> Quest Software
>
Query Optimizer Question
SELECT dbo.ProjectTran.ProjectRowID, ProjectTran.RowID,
COALESCE(dbo.ProjectTran.ProjectWBSRowID,0),
PA.BillingTypeRowID,
'REVENUE',
dbo.ProjectTran.PriorInvoiceAmount-(SELECT
COALESCE(SUM(dbo.ProjectInvoiceDetail.InvoiceAmount),0)
FROM dbo.ProjectInvoiceDetail
INNER JOIN dbo.ProjectInvoice on
dbo.ProjectInvoiceDetail.ProjectInvoiceRowID = dbo.ProjectInvoice.RowID
Where dbo.ProjectInvoice.LedgerDocRowID IS NOT NULL
AND dbo.ProjectInvoice.StatusRowID = 1165
AND dbo.ProjectInvoiceDetail.InvoiceAmount <> 0
AND dbo.ProjectInvoiceDetail.ProjectTranRowID = ProjectTran.RowID) AS Revenue,
dbo.ProjectTran.PriorInvoiceUnits-(SELECT
COALESCE(SUM(dbo.ProjectInvoiceDetail.InvoiceUnits),0)
FROM dbo.ProjectInvoiceDetail
INNER JOIN dbo.ProjectInvoice on
dbo.ProjectInvoiceDetail.ProjectInvoiceRowID = dbo.ProjectInvoice.RowID
Where dbo.ProjectInvoice.LedgerDocRowID IS NOT NULL
AND dbo.ProjectInvoice.StatusRowID = 1165
AND dbo.ProjectInvoiceDetail.InvoiceAmount <> 0
AND dbo.ProjectInvoiceDetail.ProjectTranRowID = ProjectTran.RowID) AS BilledUnits,
dbo.ProjectTran.PriorInvoiceAmount -(SELECT
COALESCE(SUM(dbo.ProjectInvoiceDetail.InvoiceAmount),0)
FROM dbo.ProjectInvoiceDetail
INNER JOIN dbo.ProjectInvoice on
dbo.ProjectInvoiceDetail.ProjectInvoiceRowID = dbo.ProjectInvoice.RowID
Where dbo.ProjectInvoice.LedgerDocRowID IS NOT NULL
AND dbo.ProjectInvoice.StatusRowID = 1165
AND dbo.ProjectInvoiceDetail.InvoiceAmount <> 0
AND dbo.ProjectInvoiceDetail.ProjectTranRowID = ProjectTran.RowID) AS BilledAmount
FROM dbo.ProjectTran
LEFT OUTER JOIN @.ProjectAccount PA ON
dbo.ProjectTran.ProjectAccountRowID = PA.ProjectAccountRowID
-- LEFT OUTER JOIN @.ProjectAccount PA2 ON
dbo.ProjectTran.LastInvoiceProjectAccountRowID = PA2.ProjectAccountRowID
INNER JOIN dbo.ProjectDoc ON dbo.ProjectTran.ProjectDocRowID = dbo.ProjectDoc.RowID
Where dbo.ProjectTran.ProjectRowID in (Select RowID From #ProjectTemp)
AND dbo.ProjectTran.ProjectDocRowID IS NOT NULL
AND dbo.ProjectDoc.StatusRowID = 1003
AND ProjectTran.RowID NOT IN (Select TransactionRowID From
@.ProjectDataDetail WHERE AccountTypeDescription = 'REVENUE')Amos Soma wrote:
> SELECT dbo.ProjectTran.ProjectRowID, ProjectTran.RowID,
> <SNIP>
That's a very nice query. What's the question :-)
David Gugick - SQL Server MVP
Quest Software|||I'm sorry - I didn't mean to post this in this condition. Another post is
coming.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:u6ggfDzMGHA.2828@.TK2MSFTNGP12.phx.gbl...
> Amos Soma wrote:
>> SELECT dbo.ProjectTran.ProjectRowID, ProjectTran.RowID,
>> <SNIP>
> That's a very nice query. What's the question :-)
>
> --
> David Gugick - SQL Server MVP
> Quest Software
>
COALESCE(dbo.ProjectTran.ProjectWBSRowID,0),
PA.BillingTypeRowID,
'REVENUE',
dbo.ProjectTran.PriorInvoiceAmount-(SELECT
COALESCE(SUM(dbo.ProjectInvoiceDetail.InvoiceAmount),0)
FROM dbo.ProjectInvoiceDetail
INNER JOIN dbo.ProjectInvoice on
dbo.ProjectInvoiceDetail.ProjectInvoiceRowID = dbo.ProjectInvoice.RowID
Where dbo.ProjectInvoice.LedgerDocRowID IS NOT NULL
AND dbo.ProjectInvoice.StatusRowID = 1165
AND dbo.ProjectInvoiceDetail.InvoiceAmount <> 0
AND dbo.ProjectInvoiceDetail.ProjectTranRowID = ProjectTran.RowID) AS Revenue,
dbo.ProjectTran.PriorInvoiceUnits-(SELECT
COALESCE(SUM(dbo.ProjectInvoiceDetail.InvoiceUnits),0)
FROM dbo.ProjectInvoiceDetail
INNER JOIN dbo.ProjectInvoice on
dbo.ProjectInvoiceDetail.ProjectInvoiceRowID = dbo.ProjectInvoice.RowID
Where dbo.ProjectInvoice.LedgerDocRowID IS NOT NULL
AND dbo.ProjectInvoice.StatusRowID = 1165
AND dbo.ProjectInvoiceDetail.InvoiceAmount <> 0
AND dbo.ProjectInvoiceDetail.ProjectTranRowID = ProjectTran.RowID) AS BilledUnits,
dbo.ProjectTran.PriorInvoiceAmount -(SELECT
COALESCE(SUM(dbo.ProjectInvoiceDetail.InvoiceAmount),0)
FROM dbo.ProjectInvoiceDetail
INNER JOIN dbo.ProjectInvoice on
dbo.ProjectInvoiceDetail.ProjectInvoiceRowID = dbo.ProjectInvoice.RowID
Where dbo.ProjectInvoice.LedgerDocRowID IS NOT NULL
AND dbo.ProjectInvoice.StatusRowID = 1165
AND dbo.ProjectInvoiceDetail.InvoiceAmount <> 0
AND dbo.ProjectInvoiceDetail.ProjectTranRowID = ProjectTran.RowID) AS BilledAmount
FROM dbo.ProjectTran
LEFT OUTER JOIN @.ProjectAccount PA ON
dbo.ProjectTran.ProjectAccountRowID = PA.ProjectAccountRowID
-- LEFT OUTER JOIN @.ProjectAccount PA2 ON
dbo.ProjectTran.LastInvoiceProjectAccountRowID = PA2.ProjectAccountRowID
INNER JOIN dbo.ProjectDoc ON dbo.ProjectTran.ProjectDocRowID = dbo.ProjectDoc.RowID
Where dbo.ProjectTran.ProjectRowID in (Select RowID From #ProjectTemp)
AND dbo.ProjectTran.ProjectDocRowID IS NOT NULL
AND dbo.ProjectDoc.StatusRowID = 1003
AND ProjectTran.RowID NOT IN (Select TransactionRowID From
@.ProjectDataDetail WHERE AccountTypeDescription = 'REVENUE')Amos Soma wrote:
> SELECT dbo.ProjectTran.ProjectRowID, ProjectTran.RowID,
> <SNIP>
That's a very nice query. What's the question :-)
David Gugick - SQL Server MVP
Quest Software|||I'm sorry - I didn't mean to post this in this condition. Another post is
coming.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:u6ggfDzMGHA.2828@.TK2MSFTNGP12.phx.gbl...
> Amos Soma wrote:
>> SELECT dbo.ProjectTran.ProjectRowID, ProjectTran.RowID,
>> <SNIP>
> That's a very nice query. What's the question :-)
>
> --
> David Gugick - SQL Server MVP
> Quest Software
>
Friday, March 9, 2012
Query Optimization
IS there any way to rewrite this Query in optimized way?
SELECT dbo.Table1.EmpId E from dbo.Table1
where EmpId in(
SELECT dbo.Table1.EmpId
FROM (SELECT DISTINCT PersonID, MAX(dtmStatusDate) AS dtmStatusDate
FROM dbo.Table1
GROUP BY PersonID) derived_table INNER JOIN
dbo.Table1 ON derived_table.PersonID = dbo.Table1.PersonID AND
derived_table.dtmStatusDate = dbo.Table1.dtmStatusDate))
Thanks...jDon't know abiut being faster but I think this is what oyu are trying to do. (get the empid's with max(dtmStatusDate) from each person.
SELECT t1.EmpId
from dbo.Table1 t1
where t1.dtmStatusDate =
(select max(dtmStatusDate) from dbo.Table1 t2 where t1.PersonID = t2.PersonID)
also try
SELECT t1.EmpId
from dbo.Table1 t1
where not exists ( select * from dbo.Table1 t2 where t1.PersonID = t2.PersonID and t1.dtmStatusDate < t2.dtmStatusDate)
SELECT dbo.Table1.EmpId E from dbo.Table1
where EmpId in(
SELECT dbo.Table1.EmpId
FROM (SELECT DISTINCT PersonID, MAX(dtmStatusDate) AS dtmStatusDate
FROM dbo.Table1
GROUP BY PersonID) derived_table INNER JOIN
dbo.Table1 ON derived_table.PersonID = dbo.Table1.PersonID AND
derived_table.dtmStatusDate = dbo.Table1.dtmStatusDate))
Thanks...jDon't know abiut being faster but I think this is what oyu are trying to do. (get the empid's with max(dtmStatusDate) from each person.
SELECT t1.EmpId
from dbo.Table1 t1
where t1.dtmStatusDate =
(select max(dtmStatusDate) from dbo.Table1 t2 where t1.PersonID = t2.PersonID)
also try
SELECT t1.EmpId
from dbo.Table1 t1
where not exists ( select * from dbo.Table1 t2 where t1.PersonID = t2.PersonID and t1.dtmStatusDate < t2.dtmStatusDate)
Query optimization
I have a view comprised of the following select statement.
SELECT
x.Key
FROM dbo.Table_X AS x
INNER JOIN
dbo.Table_Z AS z ON z.FKID = x.PVUID AND z.Key = x.Key
INNER JOIN
dbo.Table_Z AS abc ON abc.FKID = x.PVUID AND abc.Key = x.Key
AND abc.MyBit = 1
I have the following indexes for Table_Z:
Clustered: (Key, PFUID)
Nonclustered: (FKID, Key)
I have the following indexes for Table_X:
Clustered: (Key, PVUID)
Unique Nonclustered: (PVUID, Key)
Note: It does appear I have overlapping/duplicate indexes for Table_X. Unless
that factors in to the following, please disregard. Let's move on.
Table_Z has 98 rows.
Table_X has 84 rows.
All the fields used in the above select statement are data type int, except
field MyBit, which is data type bit.
In running the select statement (of which the view is comprised) I obtain the
following statistics:
Table 'Table_Z'. Scan count 76, logical reads 80, physical reads 0, read-
ahead reads 0.
Table 'Table_X'. Scan count 75, logical reads 75, physical reads 0, read-
ahead reads 0.
CPU time = 0 ms
Table_Z gets is the outer input using a Clustered Index Scan, Actual rows = 75, Estimated rows = 74.25
Table_X is the inner input using an Index Seek, Actual rows = 75, Estimated = 1
The two combine into a Nested Loop with 75 rows returned.
Followed by another Index Seek on Table_Z, Actual rows = 98, Estimated rows = 1.31
This results in another Nested Loop, with 98 rows returned.
I was hoping to optimize the select statement so I added a where clause:
SELECT
x.Key
FROM dbo.Table_X AS x
INNER JOIN
dbo.Table_Z AS z ON z.FKID = x.PVUID AND z.Key = x.Key
INNER JOIN
dbo.Table_Z AS abc ON abc.FKID = x.PVUID AND abc.Key = x.Key
AND abc.MyBit = 1
WHERE x.Key > 0
This resulted with the following:
Table 'Table_Z'. Scan count 159, logical reads 291, physical reads 3, read-
ahead reads 4.
Table 'Table_X'. Scan count 1, logical reads 4, physical reads 1, read-ahead
reads 3.
CPU time = 0 ms
Table_X gets is the outer input using a Clustered Index Seek, Actual rows = 84, Estimated rows = 83.93
Table_Z is the inner input using an Clustered Index Seek, Actual rows = 75,
Estimated = 1
The two combine into a Nested Loop with 75 rows returned.
Followed by another Index Seek on Table_Z, Actual rows = 98, Estimated rows = 1.31
This results in another Nested Loop, with 98 rows returned.
The where clause greatly reduced the scans on logical reads on Table_X, but
greatly increased the scans and logical reads on Table_Z. I tried adding an
additional filter of "AND z.Key > 0" but this had no affect. The statistics
and Explain Plan remained the same.
Is their any way to simultaneously reduce the scans and reads on Table_Z to
coincide with the reduction on Table_X while still returning the 98 rows?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1What's the difference between the two inner joins on table z? It's not
apparent to me why you'd join table_z twice. Can you strip the second join?
-T
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:6065e661ba0a5@.uwe...
>I have a view comprised of the following select statement.
> SELECT
> x.Key
> FROM dbo.Table_X AS x
> INNER JOIN
> dbo.Table_Z AS z ON z.FKID = x.PVUID AND z.Key = x.Key
> INNER JOIN
> dbo.Table_Z AS abc ON abc.FKID = x.PVUID AND abc.Key = x.Key
> AND abc.MyBit = 1
> I have the following indexes for Table_Z:
> Clustered: (Key, PFUID)
> Nonclustered: (FKID, Key)
>
> I have the following indexes for Table_X:
> Clustered: (Key, PVUID)
> Unique Nonclustered: (PVUID, Key)
> Note: It does appear I have overlapping/duplicate indexes for Table_X.
> Unless
> that factors in to the following, please disregard. Let's move on.
> Table_Z has 98 rows.
> Table_X has 84 rows.
> All the fields used in the above select statement are data type int,
> except
> field MyBit, which is data type bit.
> In running the select statement (of which the view is comprised) I obtain
> the
> following statistics:
> Table 'Table_Z'. Scan count 76, logical reads 80, physical reads 0, read-
> ahead reads 0.
> Table 'Table_X'. Scan count 75, logical reads 75, physical reads 0, read-
> ahead reads 0.
> CPU time = 0 ms
> Table_Z gets is the outer input using a Clustered Index Scan, Actual rows
> => 75, Estimated rows = 74.25
> Table_X is the inner input using an Index Seek, Actual rows = 75,
> Estimated => 1
> The two combine into a Nested Loop with 75 rows returned.
> Followed by another Index Seek on Table_Z, Actual rows = 98, Estimated
> rows => 1.31
> This results in another Nested Loop, with 98 rows returned.
>
> I was hoping to optimize the select statement so I added a where clause:
> SELECT
> x.Key
> FROM dbo.Table_X AS x
> INNER JOIN
> dbo.Table_Z AS z ON z.FKID = x.PVUID AND z.Key = x.Key
> INNER JOIN
> dbo.Table_Z AS abc ON abc.FKID = x.PVUID AND abc.Key = x.Key
> AND abc.MyBit = 1
> WHERE x.Key > 0
> This resulted with the following:
> Table 'Table_Z'. Scan count 159, logical reads 291, physical reads 3,
> read-
> ahead reads 4.
> Table 'Table_X'. Scan count 1, logical reads 4, physical reads 1,
> read-ahead
> reads 3.
> CPU time = 0 ms
> Table_X gets is the outer input using a Clustered Index Seek, Actual rows
> => 84, Estimated rows = 83.93
> Table_Z is the inner input using an Clustered Index Seek, Actual rows => 75,
> Estimated = 1
> The two combine into a Nested Loop with 75 rows returned.
> Followed by another Index Seek on Table_Z, Actual rows = 98, Estimated
> rows => 1.31
> This results in another Nested Loop, with 98 rows returned.
> The where clause greatly reduced the scans on logical reads on Table_X,
> but
> greatly increased the scans and logical reads on Table_Z. I tried adding
> an
> additional filter of "AND z.Key > 0" but this had no affect. The
> statistics
> and Explain Plan remained the same.
> Is their any way to simultaneously reduce the scans and reads on Table_Z
> to
> coincide with the reduction on Table_X while still returning the 98 rows?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1
SELECT
x.Key
FROM dbo.Table_X AS x
INNER JOIN
dbo.Table_Z AS z ON z.FKID = x.PVUID AND z.Key = x.Key
INNER JOIN
dbo.Table_Z AS abc ON abc.FKID = x.PVUID AND abc.Key = x.Key
AND abc.MyBit = 1
I have the following indexes for Table_Z:
Clustered: (Key, PFUID)
Nonclustered: (FKID, Key)
I have the following indexes for Table_X:
Clustered: (Key, PVUID)
Unique Nonclustered: (PVUID, Key)
Note: It does appear I have overlapping/duplicate indexes for Table_X. Unless
that factors in to the following, please disregard. Let's move on.
Table_Z has 98 rows.
Table_X has 84 rows.
All the fields used in the above select statement are data type int, except
field MyBit, which is data type bit.
In running the select statement (of which the view is comprised) I obtain the
following statistics:
Table 'Table_Z'. Scan count 76, logical reads 80, physical reads 0, read-
ahead reads 0.
Table 'Table_X'. Scan count 75, logical reads 75, physical reads 0, read-
ahead reads 0.
CPU time = 0 ms
Table_Z gets is the outer input using a Clustered Index Scan, Actual rows = 75, Estimated rows = 74.25
Table_X is the inner input using an Index Seek, Actual rows = 75, Estimated = 1
The two combine into a Nested Loop with 75 rows returned.
Followed by another Index Seek on Table_Z, Actual rows = 98, Estimated rows = 1.31
This results in another Nested Loop, with 98 rows returned.
I was hoping to optimize the select statement so I added a where clause:
SELECT
x.Key
FROM dbo.Table_X AS x
INNER JOIN
dbo.Table_Z AS z ON z.FKID = x.PVUID AND z.Key = x.Key
INNER JOIN
dbo.Table_Z AS abc ON abc.FKID = x.PVUID AND abc.Key = x.Key
AND abc.MyBit = 1
WHERE x.Key > 0
This resulted with the following:
Table 'Table_Z'. Scan count 159, logical reads 291, physical reads 3, read-
ahead reads 4.
Table 'Table_X'. Scan count 1, logical reads 4, physical reads 1, read-ahead
reads 3.
CPU time = 0 ms
Table_X gets is the outer input using a Clustered Index Seek, Actual rows = 84, Estimated rows = 83.93
Table_Z is the inner input using an Clustered Index Seek, Actual rows = 75,
Estimated = 1
The two combine into a Nested Loop with 75 rows returned.
Followed by another Index Seek on Table_Z, Actual rows = 98, Estimated rows = 1.31
This results in another Nested Loop, with 98 rows returned.
The where clause greatly reduced the scans on logical reads on Table_X, but
greatly increased the scans and logical reads on Table_Z. I tried adding an
additional filter of "AND z.Key > 0" but this had no affect. The statistics
and Explain Plan remained the same.
Is their any way to simultaneously reduce the scans and reads on Table_Z to
coincide with the reduction on Table_X while still returning the 98 rows?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1What's the difference between the two inner joins on table z? It's not
apparent to me why you'd join table_z twice. Can you strip the second join?
-T
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:6065e661ba0a5@.uwe...
>I have a view comprised of the following select statement.
> SELECT
> x.Key
> FROM dbo.Table_X AS x
> INNER JOIN
> dbo.Table_Z AS z ON z.FKID = x.PVUID AND z.Key = x.Key
> INNER JOIN
> dbo.Table_Z AS abc ON abc.FKID = x.PVUID AND abc.Key = x.Key
> AND abc.MyBit = 1
> I have the following indexes for Table_Z:
> Clustered: (Key, PFUID)
> Nonclustered: (FKID, Key)
>
> I have the following indexes for Table_X:
> Clustered: (Key, PVUID)
> Unique Nonclustered: (PVUID, Key)
> Note: It does appear I have overlapping/duplicate indexes for Table_X.
> Unless
> that factors in to the following, please disregard. Let's move on.
> Table_Z has 98 rows.
> Table_X has 84 rows.
> All the fields used in the above select statement are data type int,
> except
> field MyBit, which is data type bit.
> In running the select statement (of which the view is comprised) I obtain
> the
> following statistics:
> Table 'Table_Z'. Scan count 76, logical reads 80, physical reads 0, read-
> ahead reads 0.
> Table 'Table_X'. Scan count 75, logical reads 75, physical reads 0, read-
> ahead reads 0.
> CPU time = 0 ms
> Table_Z gets is the outer input using a Clustered Index Scan, Actual rows
> => 75, Estimated rows = 74.25
> Table_X is the inner input using an Index Seek, Actual rows = 75,
> Estimated => 1
> The two combine into a Nested Loop with 75 rows returned.
> Followed by another Index Seek on Table_Z, Actual rows = 98, Estimated
> rows => 1.31
> This results in another Nested Loop, with 98 rows returned.
>
> I was hoping to optimize the select statement so I added a where clause:
> SELECT
> x.Key
> FROM dbo.Table_X AS x
> INNER JOIN
> dbo.Table_Z AS z ON z.FKID = x.PVUID AND z.Key = x.Key
> INNER JOIN
> dbo.Table_Z AS abc ON abc.FKID = x.PVUID AND abc.Key = x.Key
> AND abc.MyBit = 1
> WHERE x.Key > 0
> This resulted with the following:
> Table 'Table_Z'. Scan count 159, logical reads 291, physical reads 3,
> read-
> ahead reads 4.
> Table 'Table_X'. Scan count 1, logical reads 4, physical reads 1,
> read-ahead
> reads 3.
> CPU time = 0 ms
> Table_X gets is the outer input using a Clustered Index Seek, Actual rows
> => 84, Estimated rows = 83.93
> Table_Z is the inner input using an Clustered Index Seek, Actual rows => 75,
> Estimated = 1
> The two combine into a Nested Loop with 75 rows returned.
> Followed by another Index Seek on Table_Z, Actual rows = 98, Estimated
> rows => 1.31
> This results in another Nested Loop, with 98 rows returned.
> The where clause greatly reduced the scans on logical reads on Table_X,
> but
> greatly increased the scans and logical reads on Table_Z. I tried adding
> an
> additional filter of "AND z.Key > 0" but this had no affect. The
> statistics
> and Explain Plan remained the same.
> Is their any way to simultaneously reduce the scans and reads on Table_Z
> to
> coincide with the reduction on Table_X while still returning the 98 rows?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1
Wednesday, March 7, 2012
Query on XML column: fn:lower-case not working?
Hi everybody, I'm looking to issue a query to an XML column like this:
SELECT *
FROM dbo.ContactRecords
WHERE XmlContent.exist(
'declare namespace
my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2004-03-09T14-09
-40";
// my:myFields[my:AddressInfo[fn:contains(f
n:lower-case(my:Company[1]),"lego"
)]]')
= 1
(i skipped some other conditions, but they are working)
--> SQL 2005 tells me: There is no function
'{http://www.w3.org/2004/07/xpath-functions}:lower-case()'
however, there is one...
How can I compare string values in SQL 2005 case-insensitive?
I appreciate your help, have a good day,
MichaelHello michael.hofer@.getronics.com,
Well, you've stumbled into the chasm caused by Microsoft trying to get a
product released and the W3C XQuery WG trying to get things perfect the firs
t
time. There's a number of functions that just aren't implemented in MS's
XQuery heap but are in the spec. We might get them in service packs, or we
might get them in the next version of SQL Server. String-Upper and String-L
ower
are a couple of the common ones that we'd like to have but don't.
Interestingly enough, I see that contains() has a optional collation pattern
.
I try to find a collation that ignored case to test with but didn't have
much immediate luck. If you might want to go down that path. Or not. No prom
ises.
I'd like to offer a suggestion other than .value(path,'varchar(n)') like
'%pattern%' (e.g., do the comparsion in T-SQL as shown below) since this
is fairly ugly for the kind of stuff you're doing. But it does work.
declare @.x xml
set @.x = '<book>Ender''s Game</book><book>Xenocide</book><book>Children of
the Mind</book><book>The Hive Queen</book><book>The Hegemon</book>'
select p.x.value('.','varchar(100)')
from @.x.nodes('/book') as p(x)
where p.x.value('.','varchar(100)') like '%the%'
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
SELECT *
FROM dbo.ContactRecords
WHERE XmlContent.exist(
'declare namespace
my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2004-03-09T14-09
-40";
// my:myFields[my:AddressInfo[fn:contains(f
n:lower-case(my:Company[1]),"lego"
)]]')
= 1
(i skipped some other conditions, but they are working)
--> SQL 2005 tells me: There is no function
'{http://www.w3.org/2004/07/xpath-functions}:lower-case()'
however, there is one...
How can I compare string values in SQL 2005 case-insensitive?
I appreciate your help, have a good day,
MichaelHello michael.hofer@.getronics.com,
Well, you've stumbled into the chasm caused by Microsoft trying to get a
product released and the W3C XQuery WG trying to get things perfect the firs
t
time. There's a number of functions that just aren't implemented in MS's
XQuery heap but are in the spec. We might get them in service packs, or we
might get them in the next version of SQL Server. String-Upper and String-L
ower
are a couple of the common ones that we'd like to have but don't.
Interestingly enough, I see that contains() has a optional collation pattern
.
I try to find a collation that ignored case to test with but didn't have
much immediate luck. If you might want to go down that path. Or not. No prom
ises.
I'd like to offer a suggestion other than .value(path,'varchar(n)') like
'%pattern%' (e.g., do the comparsion in T-SQL as shown below) since this
is fairly ugly for the kind of stuff you're doing. But it does work.
declare @.x xml
set @.x = '<book>Ender''s Game</book><book>Xenocide</book><book>Children of
the Mind</book><book>The Hive Queen</book><book>The Hegemon</book>'
select p.x.value('.','varchar(100)')
from @.x.nodes('/book') as p(x)
where p.x.value('.','varchar(100)') like '%the%'
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
Monday, February 20, 2012
Query multiple database tables
Sql2005? -NEW to SQL. Have a database which creates tables basically named the same thing except the date. i.e. dbo.table05012006, dbo.table05022006. I need to query a table if the date is = yesterday. I am searching for a way to do this everyday dynamically. Is this even possible?
If you are doing this in SSIS then look up property expressions in Books Online. You can use this to dynamically build the SELECT statement, generating the table name based on a date, or today's date - 1.
Having tables in this way seems a bit strange, certainly at such a low level of day. You may want to investigate partitioned views, or partitioned tables. These concepts present a uniform view a to something like a SELECT statement, but you can still organise the data into tables or partitions respectively.
Subscribe to:
Posts (Atom)