I get the following message when I'm inserting a row into a table. The
statement is very simple, but there is a large amount of data going into one
column.
Internal Query Processor Error: The query processor ran out of stack space
during query optimization
All the KB articles I've found so far refer to queries that have a large
number of elements in an IN clause, or a CASE statement with a large number
of WHEN clauses - niether of which fit this scenario.
Has anyone else had this problem and found a way around it. I can't
replicate the problem on any of our development or test servers - it only
happens on the production server. All servers are the same spec - W2K
Server and SQL Server 2000 - all service packed up.
Any help appreciated.
Cheers,
CameronCameron,
Can you provide more information? What is the structure of the table,
and what is the insert statement, or at the least, what form does it have -
insert .. values, insert into .. select? Are there any triggers on the
table?
What indexes are on the table? Are any of the tables involved actually
views?
It's hard to suggest a way around a problem with this little information
about what you are trying to do.
-- Steve Kass
-- Drew University
-- Ref: 20CAE9D5-49D4-43CF-A330-EE3EB766EF1A
cj wrote:
>I get the following message when I'm inserting a row into a table. The
>statement is very simple, but there is a large amount of data going into one
>column.
>Internal Query Processor Error: The query processor ran out of stack space
>during query optimization
>All the KB articles I've found so far refer to queries that have a large
>number of elements in an IN clause, or a CASE statement with a large number
>of WHEN clauses - niether of which fit this scenario.
>Has anyone else had this problem and found a way around it. I can't
>replicate the problem on any of our development or test servers - it only
>happens on the production server. All servers are the same spec - W2K
>Server and SQL Server 2000 - all service packed up.
>Any help appreciated.
>Cheers,
>Cameron
>
>|||even though the statement may be simple, inserts could
have complex parsing for NULL conditions etc
the standard windows program defaults to 1MB stack size.
however, i believe this is reduced to 256K on sql server
for performance reasons.
in either Visual Studio C/C++ or the Windows Server EE
Customer Support Diagnostic, there are the utilities
editbin.exe and imagecfg.exe that lets you reconfigure the
stack size (sizes are in hex)
but i would try modifying your insert statement before
modifying the sql server binary, since this needs to be
redone every hotfix, sp etc
>--Original Message--
>I get the following message when I'm inserting a row into
a table. The
>statement is very simple, but there is a large amount of
data going into one
>column.
>Internal Query Processor Error: The query processor ran
out of stack space
>during query optimization
>All the KB articles I've found so far refer to queries
that have a large
>number of elements in an IN clause, or a CASE statement
with a large number
>of WHEN clauses - niether of which fit this scenario.
>Has anyone else had this problem and found a way around
it. I can't
>replicate the problem on any of our development or test
servers - it only
>happens on the production server. All servers are the
same spec - W2K
>Server and SQL Server 2000 - all service packed up.
>Any help appreciated.
>Cheers,
>Cameron
>
>.
>|||I would try DBCCs on the database in question, and if that didn't help, I
would call product support services and get them to help you out if you can
afford it. That sounds bad.
And don't cross post!
--
----
--
Louis Davidson (drsql@.hotmail.com)
Compass Technology Management
Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies will be ignored :)
"cj" <smuffnstuff@.hotmail.com> wrote in message
news:O7Ptg8aoDHA.3612@.TK2MSFTNGP11.phx.gbl...
> I get the following message when I'm inserting a row into a table. The
> statement is very simple, but there is a large amount of data going into
one
> column.
> Internal Query Processor Error: The query processor ran out of stack space
> during query optimization
> All the KB articles I've found so far refer to queries that have a large
> number of elements in an IN clause, or a CASE statement with a large
number
> of WHEN clauses - niether of which fit this scenario.
> Has anyone else had this problem and found a way around it. I can't
> replicate the problem on any of our development or test servers - it only
> happens on the production server. All servers are the same spec - W2K
> Server and SQL Server 2000 - all service packed up.
> Any help appreciated.
> Cheers,
> Cameron
>|||What is this 1MB stack space ?
"joe chang" <anonymous@.discussions.microsoft.com> wrote in message
news:059e01c3a1bf$bba5da20$a401280a@.phx.gbl...
> even though the statement may be simple, inserts could
> have complex parsing for NULL conditions etc
> the standard windows program defaults to 1MB stack size.
> however, i believe this is reduced to 256K on sql server
> for performance reasons.
> in either Visual Studio C/C++ or the Windows Server EE
> Customer Support Diagnostic, there are the utilities
> editbin.exe and imagecfg.exe that lets you reconfigure the
> stack size (sizes are in hex)
> but i would try modifying your insert statement before
> modifying the sql server binary, since this needs to be
> redone every hotfix, sp etc
> >--Original Message--
> >I get the following message when I'm inserting a row into
> a table. The
> >statement is very simple, but there is a large amount of
> data going into one
> >column.
> >
> >Internal Query Processor Error: The query processor ran
> out of stack space
> >during query optimization
> >
> >All the KB articles I've found so far refer to queries
> that have a large
> >number of elements in an IN clause, or a CASE statement
> with a large number
> >of WHEN clauses - niether of which fit this scenario.
> >
> >Has anyone else had this problem and found a way around
> it. I can't
> >replicate the problem on any of our development or test
> servers - it only
> >happens on the production server. All servers are the
> same spec - W2K
> >Server and SQL Server 2000 - all service packed up.
> >
> >Any help appreciated.
> >
> >Cheers,
> >
> >Cameron
> >
> >
> >.
> >|||Have you got an insert trigger on the table? What are your settings for
nested and recursive triggers?
HTH,
Greg Low (MVP)
MSDE Manager SQL Tools
www.whitebearconsulting.com
"cj" <smuffnstuff@.hotmail.com> wrote in message
news:O7Ptg8aoDHA.3612@.TK2MSFTNGP11.phx.gbl...
> I get the following message when I'm inserting a row into a table. The
> statement is very simple, but there is a large amount of data going into
one
> column.
> Internal Query Processor Error: The query processor ran out of stack space
> during query optimization
> All the KB articles I've found so far refer to queries that have a large
> number of elements in an IN clause, or a CASE statement with a large
number
> of WHEN clauses - niether of which fit this scenario.
> Has anyone else had this problem and found a way around it. I can't
> replicate the problem on any of our development or test servers - it only
> happens on the production server. All servers are the same spec - W2K
> Server and SQL Server 2000 - all service packed up.
> Any help appreciated.
> Cheers,
> Cameron
>
Showing posts with label statement. Show all posts
Showing posts with label statement. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
query problem
hi,
I need this as a filter criteria in my where clause for my select statement,
I need to set
Year1 = current year and
Year2 = currentyear-1
when current date's [month-day] > july30th
current year is in the form of 07 or 06 or 05
i.e
Year1 = 07
Year2 = 06
when current date's[ month-day] < july01
Year1 = current year - 1 andYear2 = currentyear-2
Year1 = 06
Year2 = 05
I know I can use datepart, but current date from getdate() function gives
2007-10-07-hh.min.sec
and I need to compare this with
july 10th or june30th at any point of a year
also
set Year1
Year2 in the form 07 or 06 or 05
thnx
I suggest that this, and many date related queries you now have or will have in the future will be so much easier if you were to explore using a Calendar table. See this source.
Seems like you need to compare a date against Jul 01, so DATEDIFF() 'could' help.
Here is one simple use of DATEDIFF():
DECLARE
@.DateToVerify datetime,
@.FYear datetime
SELECT
@.DateToVerify = getdate(),
@.FYear = dateadd( day, 181, dateadd( year, datediff( year, 0, getdate() ), 0 ))
SELECT CASE
WHEN datediff( day, @.FYear, @.DateToVerify ) >= 0 THEN '07'
WHEN datediff( day, @.FYear, @.DateToVerify ) >= (-365) THEN '06'
WHEN datediff( day, @.FYear, @.DateToVerify ) >= (-730) THEN '05'
ELSE '04'
END
Most likely there are many ways to 'skin this cat', as the saying goes. But a Calendar table is often the most robust method of handling date span/range issues.
query problem
The subselect in the query returns 897 rows, but when it
is included in the
where clause of an update statement, the whole table is
returned and
updated. Why? And how can I change this to only update
the 897 rows that
the subselect is returning?
UPDATE Item
SET sz_amount = 1
WHERE item_id IN (SELECT Item.item_id FROM dbo.Item INNER
JOIN dbo.ItemExtended ON dbo.Item.item_id =
dbo.ItemExtended.item_id INNER JOIN dbo.IRISubcategory ON
dbo.IRISubcategory.iri_subcategory_id =
dbo.ItemExtended.iri_subcategory_id INNER JOIN
dbo.IRICategory ON dbo.IRISubcategory.iri_category_id
= dbo.IRICategory.iri_category_id WHERE
(dbo.Item.sz_amount = 10) AND
(dbo.IRICategory.code = '1820'))
I'm not sure but try this an tell me if it works.
UPDATE Item
SET sz_amount = 1
FROM dbo.Item INNER
JOIN dbo.ItemExtended ON dbo.Item.item_id = dbo.ItemExtended.item_id
JOIN dbo.IRISubcategory ON dbo.IRISubcategory.iri_subcategory_id =
dbo.ItemExtended.iri_subcategory_id
JOIN dbo.IRICategory ON dbo.IRISubcategory.iri_category_id =
dbo.IRICategory.iri_category_id
WHERE dbo.Item.sz_amount = 10 AND
dbo.IRICategory.code = '1820'
--Buddy
"Jamie Elliott" <jelliott@.alexlee.com> wrote in message
news:253d01c427cf$38e3e730$a501280a@.phx.gbl...
> The subselect in the query returns 897 rows, but when it
> is included in the
> where clause of an update statement, the whole table is
> returned and
> updated. Why? And how can I change this to only update
> the 897 rows that
> the subselect is returning?
>
> UPDATE Item
> SET sz_amount = 1
> WHERE item_id IN (SELECT Item.item_id FROM dbo.Item INNER
> JOIN dbo.ItemExtended ON dbo.Item.item_id =
> dbo.ItemExtended.item_id INNER JOIN dbo.IRISubcategory ON
> dbo.IRISubcategory.iri_subcategory_id =
> dbo.ItemExtended.iri_subcategory_id INNER JOIN
> dbo.IRICategory ON dbo.IRISubcategory.iri_category_id
> = dbo.IRICategory.iri_category_id WHERE
> (dbo.Item.sz_amount = 10) AND
> (dbo.IRICategory.code = '1820'))
>
>
>
|||Hi Jamie,
From your descriptions, I know your subselect query will work fine and get
the correct result alone. However it goes wrong when you make it as
subselect.
Would you please have a try on Buddy Ackerman's query and tell me whether
it works. If it doesn't, would you please show me your DDL and I could
reproduce it on my machine
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Support
************************************************** *********
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
is included in the
where clause of an update statement, the whole table is
returned and
updated. Why? And how can I change this to only update
the 897 rows that
the subselect is returning?
UPDATE Item
SET sz_amount = 1
WHERE item_id IN (SELECT Item.item_id FROM dbo.Item INNER
JOIN dbo.ItemExtended ON dbo.Item.item_id =
dbo.ItemExtended.item_id INNER JOIN dbo.IRISubcategory ON
dbo.IRISubcategory.iri_subcategory_id =
dbo.ItemExtended.iri_subcategory_id INNER JOIN
dbo.IRICategory ON dbo.IRISubcategory.iri_category_id
= dbo.IRICategory.iri_category_id WHERE
(dbo.Item.sz_amount = 10) AND
(dbo.IRICategory.code = '1820'))
I'm not sure but try this an tell me if it works.
UPDATE Item
SET sz_amount = 1
FROM dbo.Item INNER
JOIN dbo.ItemExtended ON dbo.Item.item_id = dbo.ItemExtended.item_id
JOIN dbo.IRISubcategory ON dbo.IRISubcategory.iri_subcategory_id =
dbo.ItemExtended.iri_subcategory_id
JOIN dbo.IRICategory ON dbo.IRISubcategory.iri_category_id =
dbo.IRICategory.iri_category_id
WHERE dbo.Item.sz_amount = 10 AND
dbo.IRICategory.code = '1820'
--Buddy
"Jamie Elliott" <jelliott@.alexlee.com> wrote in message
news:253d01c427cf$38e3e730$a501280a@.phx.gbl...
> The subselect in the query returns 897 rows, but when it
> is included in the
> where clause of an update statement, the whole table is
> returned and
> updated. Why? And how can I change this to only update
> the 897 rows that
> the subselect is returning?
>
> UPDATE Item
> SET sz_amount = 1
> WHERE item_id IN (SELECT Item.item_id FROM dbo.Item INNER
> JOIN dbo.ItemExtended ON dbo.Item.item_id =
> dbo.ItemExtended.item_id INNER JOIN dbo.IRISubcategory ON
> dbo.IRISubcategory.iri_subcategory_id =
> dbo.ItemExtended.iri_subcategory_id INNER JOIN
> dbo.IRICategory ON dbo.IRISubcategory.iri_category_id
> = dbo.IRICategory.iri_category_id WHERE
> (dbo.Item.sz_amount = 10) AND
> (dbo.IRICategory.code = '1820'))
>
>
>
|||Hi Jamie,
From your descriptions, I know your subselect query will work fine and get
the correct result alone. However it goes wrong when you make it as
subselect.
Would you please have a try on Buddy Ackerman's query and tell me whether
it works. If it doesn't, would you please show me your DDL and I could
reproduce it on my machine
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Support
************************************************** *********
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
Monday, March 26, 2012
query problem
The subselect in the query returns 897 rows, but when it
is included in the
where clause of an update statement, the whole table is
returned and
updated. Why? And how can I change this to only update
the 897 rows that
the subselect is returning?
UPDATE Item
SET sz_amount = 1
WHERE item_id IN (SELECT Item.item_id FROM dbo.Item INNER
JOIN dbo.ItemExtended ON dbo.Item.item_id = dbo.ItemExtended.item_id INNER JOIN dbo.IRISubcategory ON
dbo.IRISubcategory.iri_subcategory_id = dbo.ItemExtended.iri_subcategory_id INNER JOIN
dbo.IRICategory ON dbo.IRISubcategory.iri_category_id
= dbo.IRICategory.iri_category_id WHERE
(dbo.Item.sz_amount = 10) AND
(dbo.IRICategory.code = '1820'))I'm not sure but try this an tell me if it works.
UPDATE Item
SET sz_amount = 1
FROM dbo.Item INNER
JOIN dbo.ItemExtended ON dbo.Item.item_id = dbo.ItemExtended.item_id
JOIN dbo.IRISubcategory ON dbo.IRISubcategory.iri_subcategory_id =dbo.ItemExtended.iri_subcategory_id
JOIN dbo.IRICategory ON dbo.IRISubcategory.iri_category_id =dbo.IRICategory.iri_category_id
WHERE dbo.Item.sz_amount = 10 AND
dbo.IRICategory.code = '1820'
--Buddy
"Jamie Elliott" <jelliott@.alexlee.com> wrote in message
news:253d01c427cf$38e3e730$a501280a@.phx.gbl...
> The subselect in the query returns 897 rows, but when it
> is included in the
> where clause of an update statement, the whole table is
> returned and
> updated. Why? And how can I change this to only update
> the 897 rows that
> the subselect is returning?
>
> UPDATE Item
> SET sz_amount = 1
> WHERE item_id IN (SELECT Item.item_id FROM dbo.Item INNER
> JOIN dbo.ItemExtended ON dbo.Item.item_id => dbo.ItemExtended.item_id INNER JOIN dbo.IRISubcategory ON
> dbo.IRISubcategory.iri_subcategory_id => dbo.ItemExtended.iri_subcategory_id INNER JOIN
> dbo.IRICategory ON dbo.IRISubcategory.iri_category_id
> = dbo.IRICategory.iri_category_id WHERE
> (dbo.Item.sz_amount = 10) AND
> (dbo.IRICategory.code = '1820'))
>
>
>|||Hi Jamie,
From your descriptions, I know your subselect query will work fine and get
the correct result alone. However it goes wrong when you make it as
subselect.
Would you please have a try on Buddy Ackerman's query and tell me whether
it works. If it doesn't, would you please show me your DDL and I could
reproduce it on my machine
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Support
***********************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
is included in the
where clause of an update statement, the whole table is
returned and
updated. Why? And how can I change this to only update
the 897 rows that
the subselect is returning?
UPDATE Item
SET sz_amount = 1
WHERE item_id IN (SELECT Item.item_id FROM dbo.Item INNER
JOIN dbo.ItemExtended ON dbo.Item.item_id = dbo.ItemExtended.item_id INNER JOIN dbo.IRISubcategory ON
dbo.IRISubcategory.iri_subcategory_id = dbo.ItemExtended.iri_subcategory_id INNER JOIN
dbo.IRICategory ON dbo.IRISubcategory.iri_category_id
= dbo.IRICategory.iri_category_id WHERE
(dbo.Item.sz_amount = 10) AND
(dbo.IRICategory.code = '1820'))I'm not sure but try this an tell me if it works.
UPDATE Item
SET sz_amount = 1
FROM dbo.Item INNER
JOIN dbo.ItemExtended ON dbo.Item.item_id = dbo.ItemExtended.item_id
JOIN dbo.IRISubcategory ON dbo.IRISubcategory.iri_subcategory_id =dbo.ItemExtended.iri_subcategory_id
JOIN dbo.IRICategory ON dbo.IRISubcategory.iri_category_id =dbo.IRICategory.iri_category_id
WHERE dbo.Item.sz_amount = 10 AND
dbo.IRICategory.code = '1820'
--Buddy
"Jamie Elliott" <jelliott@.alexlee.com> wrote in message
news:253d01c427cf$38e3e730$a501280a@.phx.gbl...
> The subselect in the query returns 897 rows, but when it
> is included in the
> where clause of an update statement, the whole table is
> returned and
> updated. Why? And how can I change this to only update
> the 897 rows that
> the subselect is returning?
>
> UPDATE Item
> SET sz_amount = 1
> WHERE item_id IN (SELECT Item.item_id FROM dbo.Item INNER
> JOIN dbo.ItemExtended ON dbo.Item.item_id => dbo.ItemExtended.item_id INNER JOIN dbo.IRISubcategory ON
> dbo.IRISubcategory.iri_subcategory_id => dbo.ItemExtended.iri_subcategory_id INNER JOIN
> dbo.IRICategory ON dbo.IRISubcategory.iri_category_id
> = dbo.IRICategory.iri_category_id WHERE
> (dbo.Item.sz_amount = 10) AND
> (dbo.IRICategory.code = '1820'))
>
>
>|||Hi Jamie,
From your descriptions, I know your subselect query will work fine and get
the correct result alone. However it goes wrong when you make it as
subselect.
Would you please have a try on Buddy Ackerman's query and tell me whether
it works. If it doesn't, would you please show me your DDL and I could
reproduce it on my machine
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Support
***********************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
Query Problem
Can you tell me what is wrong with this line of SQL statement?
if not exists(select @.InitialPasswordInd = InitialPasswordInd
where signonname = @.signonname
and userpassword = @.userpassword)
goto passwordinvalid
Assuming that @.InitialPasswordInd is declare and is the same type as
"InitialPasswordInd".
It does not like @.InitialPasswordInd = InitialPasswordInd
Is this not the proper way to get values form the database?
The query should only return one record. I tried Select Distinct... but I
get the same error (Something wrong near the equals sign.
Thanks in advance for your assistance!!!!!!!!!!!!!!It may be a typo, but I didn't see an FROM clause. Let us know...
James|||You were correct, I did not have a "from" - But I still get same error.
if not exists(select @.InitialPasswordInd = InitialPasswordInd
from dbo.Signon
where signonname = @.signonname
and userpassword = @.userpassword)
goto passwordinvalid|||You can't assign to a variable in a subquery. If you want to get the value,
just
select @.InitialPasswordInd ...
If you want to use the existence in a query,
if not exists (
select InitialPasswordInd
from ...
)
You can't do both in the subquery.
SK
"CJ Silin" <cjssilin@.nospam.com> wrote in message
news:Xns9479867BCDF3Acsilinhotmailcom@.207.46.248.16...
> Can you tell me what is wrong with this line of SQL statement?
> if not exists(select @.InitialPasswordInd = InitialPasswordInd
> where signonname = @.signonname
> and userpassword = @.userpassword)
> goto passwordinvalid
> Assuming that @.InitialPasswordInd is declare and is the same type as
> "InitialPasswordInd".
> It does not like @.InitialPasswordInd = InitialPasswordInd
> Is this not the proper way to get values form the database?
> The query should only return one record. I tried Select Distinct... but I
> get the same error (Something wrong near the equals sign.
> Thanks in advance for your assistance!!!!!!!!!!!!!!|||An EXISTS test is not a data retrieval operation so you can't specify the
variable assignment in the WHERE clause. Also, you have no FROM clause.
If you need to check for data existence and retrieve data, you can check for
a NULL variable value (of a non-NULL column) following the select. For
example:
SELECT @.InitialPasswordInd = InitialPasswordInd
FROM MyTable
WHERE signonname = @.signonname
AND userpassword = @.userpassword
IF @.InitialPasswordInd IS NULL GOTO passwordinvalid
Hope this helps.
Dan Guzman
SQL Server MVP
"CJ Silin" <cjssilin@.nospam.com> wrote in message
news:Xns9479867BCDF3Acsilinhotmailcom@.207.46.248.16...
> Can you tell me what is wrong with this line of SQL statement?
> if not exists(select @.InitialPasswordInd = InitialPasswordInd
> where signonname = @.signonname
> and userpassword = @.userpassword)
> goto passwordinvalid
> Assuming that @.InitialPasswordInd is declare and is the same type as
> "InitialPasswordInd".
> It does not like @.InitialPasswordInd = InitialPasswordInd
> Is this not the proper way to get values form the database?
> The query should only return one record. I tried Select Distinct... but I
> get the same error (Something wrong near the equals sign.
> Thanks in advance for your assistance!!!!!!!!!!!!!!
if not exists(select @.InitialPasswordInd = InitialPasswordInd
where signonname = @.signonname
and userpassword = @.userpassword)
goto passwordinvalid
Assuming that @.InitialPasswordInd is declare and is the same type as
"InitialPasswordInd".
It does not like @.InitialPasswordInd = InitialPasswordInd
Is this not the proper way to get values form the database?
The query should only return one record. I tried Select Distinct... but I
get the same error (Something wrong near the equals sign.
Thanks in advance for your assistance!!!!!!!!!!!!!!It may be a typo, but I didn't see an FROM clause. Let us know...
James|||You were correct, I did not have a "from" - But I still get same error.
if not exists(select @.InitialPasswordInd = InitialPasswordInd
from dbo.Signon
where signonname = @.signonname
and userpassword = @.userpassword)
goto passwordinvalid|||You can't assign to a variable in a subquery. If you want to get the value,
just
select @.InitialPasswordInd ...
If you want to use the existence in a query,
if not exists (
select InitialPasswordInd
from ...
)
You can't do both in the subquery.
SK
"CJ Silin" <cjssilin@.nospam.com> wrote in message
news:Xns9479867BCDF3Acsilinhotmailcom@.207.46.248.16...
> Can you tell me what is wrong with this line of SQL statement?
> if not exists(select @.InitialPasswordInd = InitialPasswordInd
> where signonname = @.signonname
> and userpassword = @.userpassword)
> goto passwordinvalid
> Assuming that @.InitialPasswordInd is declare and is the same type as
> "InitialPasswordInd".
> It does not like @.InitialPasswordInd = InitialPasswordInd
> Is this not the proper way to get values form the database?
> The query should only return one record. I tried Select Distinct... but I
> get the same error (Something wrong near the equals sign.
> Thanks in advance for your assistance!!!!!!!!!!!!!!|||An EXISTS test is not a data retrieval operation so you can't specify the
variable assignment in the WHERE clause. Also, you have no FROM clause.
If you need to check for data existence and retrieve data, you can check for
a NULL variable value (of a non-NULL column) following the select. For
example:
SELECT @.InitialPasswordInd = InitialPasswordInd
FROM MyTable
WHERE signonname = @.signonname
AND userpassword = @.userpassword
IF @.InitialPasswordInd IS NULL GOTO passwordinvalid
Hope this helps.
Dan Guzman
SQL Server MVP
"CJ Silin" <cjssilin@.nospam.com> wrote in message
news:Xns9479867BCDF3Acsilinhotmailcom@.207.46.248.16...
> Can you tell me what is wrong with this line of SQL statement?
> if not exists(select @.InitialPasswordInd = InitialPasswordInd
> where signonname = @.signonname
> and userpassword = @.userpassword)
> goto passwordinvalid
> Assuming that @.InitialPasswordInd is declare and is the same type as
> "InitialPasswordInd".
> It does not like @.InitialPasswordInd = InitialPasswordInd
> Is this not the proper way to get values form the database?
> The query should only return one record. I tried Select Distinct... but I
> get the same error (Something wrong near the equals sign.
> Thanks in advance for your assistance!!!!!!!!!!!!!!
Query Problem
Hi, I have a problem with the WHERE IN statement
The following statement works ok.
SELECT Id
FROM table1
WHERE (Id IN
('{23ABFD83-0A00-40D2-8E1F-333055062862}','{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}','{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'))
But this statement throws a error : Conversion failed when converting
from a character string to uniqueidentifier.
declare @.Id varchar(4000)
set @.Id = '''{23ABFD83-0A00-40D2-8E1F-333055062862}''' + ',' +
'''{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}'''+ ','
+'''{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'''
SELECT Id
FROM table1
WHERE (Id IN (@.Id))
Anyone have any Ideas?
Thanks
Toby> SELECT Id
> FROM table1
> WHERE (Id IN (@.Id))
The IN clause will treat @.Id as a single value. If the list size is fixed,
you can specify multiple parameters:
SELECT Id
FROM table1
WHERE (Id IN (@.Id1, @.Id2, @.Id3))
See http://www.sommarskog.se/arrays-in-sql.html for a discussion of various
solutions. You also have additional XML options in SQL 2005.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Tobi" <toby.riley@.gmail.com> wrote in message
news:1154771947.162803.55670@.p79g2000cwp.googlegroups.com...
> Hi, I have a problem with the WHERE IN statement
> The following statement works ok.
> SELECT Id
> FROM table1
> WHERE (Id IN
> ('{23ABFD83-0A00-40D2-8E1F-333055062862}','{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}','{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'))
> But this statement throws a error : Conversion failed when converting
> from a character string to uniqueidentifier.
>
> declare @.Id varchar(4000)
> set @.Id = '''{23ABFD83-0A00-40D2-8E1F-333055062862}''' + ',' +
> '''{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}'''+ ','
> +'''{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'''
> SELECT Id
> FROM table1
> WHERE (Id IN (@.Id))
> Anyone have any Ideas?
> Thanks
> Toby
>|||Tobi
Another alternative to dan's suggestion would be to run the query using
sp_executesql which might be easier on your code if you need more than 3
parameters.
e.g.
declare @.Id varchar(4000)
set @.Id = '''{23ABFD83-0A00-40D2-8E1F-333055062862}''' + ',' +
'''{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}'''+ ','
+'''{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'''
declare @.QueryStr as nVarchar(3000)
Select @.QueryStr = 'Select * from table1 where id in (' + @.Id + ')'
exec sp_executesql @.querystr
"Dan Guzman" wrote:
> > SELECT Id
> > FROM table1
> > WHERE (Id IN (@.Id))
> The IN clause will treat @.Id as a single value. If the list size is fixed,
> you can specify multiple parameters:
> SELECT Id
> FROM table1
> WHERE (Id IN (@.Id1, @.Id2, @.Id3))
> See http://www.sommarskog.se/arrays-in-sql.html for a discussion of various
> solutions. You also have additional XML options in SQL 2005.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Tobi" <toby.riley@.gmail.com> wrote in message
> news:1154771947.162803.55670@.p79g2000cwp.googlegroups.com...
> > Hi, I have a problem with the WHERE IN statement
> >
> > The following statement works ok.
> >
> > SELECT Id
> > FROM table1
> > WHERE (Id IN
> > ('{23ABFD83-0A00-40D2-8E1F-333055062862}','{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}','{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'))
> >
> > But this statement throws a error : Conversion failed when converting
> > from a character string to uniqueidentifier.
> >
> >
> > declare @.Id varchar(4000)
> > set @.Id = '''{23ABFD83-0A00-40D2-8E1F-333055062862}''' + ',' +
> > '''{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}'''+ ','
> > +'''{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'''
> > SELECT Id
> > FROM table1
> > WHERE (Id IN (@.Id))
> >
> > Anyone have any Ideas?
> >
> > Thanks
> >
> > Toby
> >
>
>|||Excellent that'll work, thankyou.
t
Chris Hoare wrote:
> Tobi
> Another alternative to dan's suggestion would be to run the query using
> sp_executesql which might be easier on your code if you need more than 3
> parameters.
> e.g.
> declare @.Id varchar(4000)
> set @.Id = '''{23ABFD83-0A00-40D2-8E1F-333055062862}''' + ',' +
> '''{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}'''+ ','
> +'''{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'''
>
> declare @.QueryStr as nVarchar(3000)
> Select @.QueryStr = 'Select * from table1 where id in (' + @.Id + ')'
> exec sp_executesql @.querystr
> "Dan Guzman" wrote:
> > > SELECT Id
> > > FROM table1
> > > WHERE (Id IN (@.Id))
> >
> > The IN clause will treat @.Id as a single value. If the list size is fixed,
> > you can specify multiple parameters:
> >
> > SELECT Id
> > FROM table1
> > WHERE (Id IN (@.Id1, @.Id2, @.Id3))
> >
> > See http://www.sommarskog.se/arrays-in-sql.html for a discussion of various
> > solutions. You also have additional XML options in SQL 2005.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Tobi" <toby.riley@.gmail.com> wrote in message
> > news:1154771947.162803.55670@.p79g2000cwp.googlegroups.com...
> > > Hi, I have a problem with the WHERE IN statement
> > >
> > > The following statement works ok.
> > >
> > > SELECT Id
> > > FROM table1
> > > WHERE (Id IN
> > > ('{23ABFD83-0A00-40D2-8E1F-333055062862}','{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}','{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'))
> > >
> > > But this statement throws a error : Conversion failed when converting
> > > from a character string to uniqueidentifier.
> > >
> > >
> > > declare @.Id varchar(4000)
> > > set @.Id = '''{23ABFD83-0A00-40D2-8E1F-333055062862}''' + ',' +
> > > '''{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}'''+ ','
> > > +'''{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'''
> > > SELECT Id
> > > FROM table1
> > > WHERE (Id IN (@.Id))
> > >
> > > Anyone have any Ideas?
> > >
> > > Thanks
> > >
> > > Toby
> > >
> >
> >
> >
The following statement works ok.
SELECT Id
FROM table1
WHERE (Id IN
('{23ABFD83-0A00-40D2-8E1F-333055062862}','{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}','{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'))
But this statement throws a error : Conversion failed when converting
from a character string to uniqueidentifier.
declare @.Id varchar(4000)
set @.Id = '''{23ABFD83-0A00-40D2-8E1F-333055062862}''' + ',' +
'''{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}'''+ ','
+'''{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'''
SELECT Id
FROM table1
WHERE (Id IN (@.Id))
Anyone have any Ideas?
Thanks
Toby> SELECT Id
> FROM table1
> WHERE (Id IN (@.Id))
The IN clause will treat @.Id as a single value. If the list size is fixed,
you can specify multiple parameters:
SELECT Id
FROM table1
WHERE (Id IN (@.Id1, @.Id2, @.Id3))
See http://www.sommarskog.se/arrays-in-sql.html for a discussion of various
solutions. You also have additional XML options in SQL 2005.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Tobi" <toby.riley@.gmail.com> wrote in message
news:1154771947.162803.55670@.p79g2000cwp.googlegroups.com...
> Hi, I have a problem with the WHERE IN statement
> The following statement works ok.
> SELECT Id
> FROM table1
> WHERE (Id IN
> ('{23ABFD83-0A00-40D2-8E1F-333055062862}','{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}','{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'))
> But this statement throws a error : Conversion failed when converting
> from a character string to uniqueidentifier.
>
> declare @.Id varchar(4000)
> set @.Id = '''{23ABFD83-0A00-40D2-8E1F-333055062862}''' + ',' +
> '''{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}'''+ ','
> +'''{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'''
> SELECT Id
> FROM table1
> WHERE (Id IN (@.Id))
> Anyone have any Ideas?
> Thanks
> Toby
>|||Tobi
Another alternative to dan's suggestion would be to run the query using
sp_executesql which might be easier on your code if you need more than 3
parameters.
e.g.
declare @.Id varchar(4000)
set @.Id = '''{23ABFD83-0A00-40D2-8E1F-333055062862}''' + ',' +
'''{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}'''+ ','
+'''{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'''
declare @.QueryStr as nVarchar(3000)
Select @.QueryStr = 'Select * from table1 where id in (' + @.Id + ')'
exec sp_executesql @.querystr
"Dan Guzman" wrote:
> > SELECT Id
> > FROM table1
> > WHERE (Id IN (@.Id))
> The IN clause will treat @.Id as a single value. If the list size is fixed,
> you can specify multiple parameters:
> SELECT Id
> FROM table1
> WHERE (Id IN (@.Id1, @.Id2, @.Id3))
> See http://www.sommarskog.se/arrays-in-sql.html for a discussion of various
> solutions. You also have additional XML options in SQL 2005.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Tobi" <toby.riley@.gmail.com> wrote in message
> news:1154771947.162803.55670@.p79g2000cwp.googlegroups.com...
> > Hi, I have a problem with the WHERE IN statement
> >
> > The following statement works ok.
> >
> > SELECT Id
> > FROM table1
> > WHERE (Id IN
> > ('{23ABFD83-0A00-40D2-8E1F-333055062862}','{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}','{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'))
> >
> > But this statement throws a error : Conversion failed when converting
> > from a character string to uniqueidentifier.
> >
> >
> > declare @.Id varchar(4000)
> > set @.Id = '''{23ABFD83-0A00-40D2-8E1F-333055062862}''' + ',' +
> > '''{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}'''+ ','
> > +'''{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'''
> > SELECT Id
> > FROM table1
> > WHERE (Id IN (@.Id))
> >
> > Anyone have any Ideas?
> >
> > Thanks
> >
> > Toby
> >
>
>|||Excellent that'll work, thankyou.
t
Chris Hoare wrote:
> Tobi
> Another alternative to dan's suggestion would be to run the query using
> sp_executesql which might be easier on your code if you need more than 3
> parameters.
> e.g.
> declare @.Id varchar(4000)
> set @.Id = '''{23ABFD83-0A00-40D2-8E1F-333055062862}''' + ',' +
> '''{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}'''+ ','
> +'''{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'''
>
> declare @.QueryStr as nVarchar(3000)
> Select @.QueryStr = 'Select * from table1 where id in (' + @.Id + ')'
> exec sp_executesql @.querystr
> "Dan Guzman" wrote:
> > > SELECT Id
> > > FROM table1
> > > WHERE (Id IN (@.Id))
> >
> > The IN clause will treat @.Id as a single value. If the list size is fixed,
> > you can specify multiple parameters:
> >
> > SELECT Id
> > FROM table1
> > WHERE (Id IN (@.Id1, @.Id2, @.Id3))
> >
> > See http://www.sommarskog.se/arrays-in-sql.html for a discussion of various
> > solutions. You also have additional XML options in SQL 2005.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Tobi" <toby.riley@.gmail.com> wrote in message
> > news:1154771947.162803.55670@.p79g2000cwp.googlegroups.com...
> > > Hi, I have a problem with the WHERE IN statement
> > >
> > > The following statement works ok.
> > >
> > > SELECT Id
> > > FROM table1
> > > WHERE (Id IN
> > > ('{23ABFD83-0A00-40D2-8E1F-333055062862}','{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}','{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'))
> > >
> > > But this statement throws a error : Conversion failed when converting
> > > from a character string to uniqueidentifier.
> > >
> > >
> > > declare @.Id varchar(4000)
> > > set @.Id = '''{23ABFD83-0A00-40D2-8E1F-333055062862}''' + ',' +
> > > '''{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}'''+ ','
> > > +'''{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'''
> > > SELECT Id
> > > FROM table1
> > > WHERE (Id IN (@.Id))
> > >
> > > Anyone have any Ideas?
> > >
> > > Thanks
> > >
> > > Toby
> > >
> >
> >
> >
Query Problem
Hi, I have a problem with the WHERE IN statement
The following statement works ok.
SELECT Id
FROM table1
WHERE (Id IN
('{23ABFD83-0A00-40D2-8E1F-333055062862}','{B5F98C5E-F899-4EEC-BA7
5-AF6DFC6773FB}','{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'))
But this statement throws a error : Conversion failed when converting
from a character string to uniqueidentifier.
declare @.Id varchar(4000)
set @.Id = '''{23ABFD83-0A00-40D2-8E1F-333055062862}''' + ',' +
'''{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}'''+ ','
+'''{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'''
SELECT Id
FROM table1
WHERE (Id IN (@.Id))
Anyone have any Ideas?
Thanks
Toby> SELECT Id
> FROM table1
> WHERE (Id IN (@.Id))
The IN clause will treat @.Id as a single value. If the list size is fixed,
you can specify multiple parameters:
SELECT Id
FROM table1
WHERE (Id IN (@.Id1, @.Id2, @.Id3))
See http://www.sommarskog.se/arrays-in-sql.html for a discussion of various
solutions. You also have additional XML options in SQL 2005.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tobi" <toby.riley@.gmail.com> wrote in message
news:1154771947.162803.55670@.p79g2000cwp.googlegroups.com...
> Hi, I have a problem with the WHERE IN statement
> The following statement works ok.
> SELECT Id
> FROM table1
> WHERE (Id IN
> ('{23ABFD83-0A00-40D2-8E1F-333055062862}','{B5F98C5E-F899-4EEC-B
A75-AF6DFC6773FB}','{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'))
> But this statement throws a error : Conversion failed when converting
> from a character string to uniqueidentifier.
>
> declare @.Id varchar(4000)
> set @.Id = '''{23ABFD83-0A00-40D2-8E1F-333055062862}''' + ',' +
> '''{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}'''+ ','
> +'''{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'''
> SELECT Id
> FROM table1
> WHERE (Id IN (@.Id))
> Anyone have any Ideas?
> Thanks
> Toby
>|||Tobi
Another alternative to dan's suggestion would be to run the query using
sp_executesql which might be easier on your code if you need more than 3
parameters.
e.g.
declare @.Id varchar(4000)
set @.Id = '''{23ABFD83-0A00-40D2-8E1F-333055062862}''' + ',' +
'''{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}'''+ ','
+'''{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'''
declare @.QueryStr as nVarchar(3000)
Select @.QueryStr = 'Select * from table1 where id in (' + @.Id + ')'
exec sp_executesql @.querystr
"Dan Guzman" wrote:
> The IN clause will treat @.Id as a single value. If the list size is fixed
,
> you can specify multiple parameters:
> SELECT Id
> FROM table1
> WHERE (Id IN (@.Id1, @.Id2, @.Id3))
> See http://www.sommarskog.se/arrays-in-sql.html for a discussion of variou
s
> solutions. You also have additional XML options in SQL 2005.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Tobi" <toby.riley@.gmail.com> wrote in message
> news:1154771947.162803.55670@.p79g2000cwp.googlegroups.com...
>
>|||Excellent that'll work, thankyou.
t
Chris Hoare wrote:[vbcol=seagreen]
> Tobi
> Another alternative to dan's suggestion would be to run the query using
> sp_executesql which might be easier on your code if you need more than 3
> parameters.
> e.g.
> declare @.Id varchar(4000)
> set @.Id = '''{23ABFD83-0A00-40D2-8E1F-333055062862}''' + ',' +
> '''{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}'''+ ','
> +'''{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'''
>
> declare @.QueryStr as nVarchar(3000)
> Select @.QueryStr = 'Select * from table1 where id in (' + @.Id + ')'
> exec sp_executesql @.querystr
> "Dan Guzman" wrote:
>
The following statement works ok.
SELECT Id
FROM table1
WHERE (Id IN
('{23ABFD83-0A00-40D2-8E1F-333055062862}','{B5F98C5E-F899-4EEC-BA7
5-AF6DFC6773FB}','{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'))
But this statement throws a error : Conversion failed when converting
from a character string to uniqueidentifier.
declare @.Id varchar(4000)
set @.Id = '''{23ABFD83-0A00-40D2-8E1F-333055062862}''' + ',' +
'''{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}'''+ ','
+'''{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'''
SELECT Id
FROM table1
WHERE (Id IN (@.Id))
Anyone have any Ideas?
Thanks
Toby> SELECT Id
> FROM table1
> WHERE (Id IN (@.Id))
The IN clause will treat @.Id as a single value. If the list size is fixed,
you can specify multiple parameters:
SELECT Id
FROM table1
WHERE (Id IN (@.Id1, @.Id2, @.Id3))
See http://www.sommarskog.se/arrays-in-sql.html for a discussion of various
solutions. You also have additional XML options in SQL 2005.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tobi" <toby.riley@.gmail.com> wrote in message
news:1154771947.162803.55670@.p79g2000cwp.googlegroups.com...
> Hi, I have a problem with the WHERE IN statement
> The following statement works ok.
> SELECT Id
> FROM table1
> WHERE (Id IN
> ('{23ABFD83-0A00-40D2-8E1F-333055062862}','{B5F98C5E-F899-4EEC-B
A75-AF6DFC6773FB}','{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'))
> But this statement throws a error : Conversion failed when converting
> from a character string to uniqueidentifier.
>
> declare @.Id varchar(4000)
> set @.Id = '''{23ABFD83-0A00-40D2-8E1F-333055062862}''' + ',' +
> '''{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}'''+ ','
> +'''{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'''
> SELECT Id
> FROM table1
> WHERE (Id IN (@.Id))
> Anyone have any Ideas?
> Thanks
> Toby
>|||Tobi
Another alternative to dan's suggestion would be to run the query using
sp_executesql which might be easier on your code if you need more than 3
parameters.
e.g.
declare @.Id varchar(4000)
set @.Id = '''{23ABFD83-0A00-40D2-8E1F-333055062862}''' + ',' +
'''{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}'''+ ','
+'''{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'''
declare @.QueryStr as nVarchar(3000)
Select @.QueryStr = 'Select * from table1 where id in (' + @.Id + ')'
exec sp_executesql @.querystr
"Dan Guzman" wrote:
> The IN clause will treat @.Id as a single value. If the list size is fixed
,
> you can specify multiple parameters:
> SELECT Id
> FROM table1
> WHERE (Id IN (@.Id1, @.Id2, @.Id3))
> See http://www.sommarskog.se/arrays-in-sql.html for a discussion of variou
s
> solutions. You also have additional XML options in SQL 2005.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Tobi" <toby.riley@.gmail.com> wrote in message
> news:1154771947.162803.55670@.p79g2000cwp.googlegroups.com...
>
>|||Excellent that'll work, thankyou.
t
Chris Hoare wrote:[vbcol=seagreen]
> Tobi
> Another alternative to dan's suggestion would be to run the query using
> sp_executesql which might be easier on your code if you need more than 3
> parameters.
> e.g.
> declare @.Id varchar(4000)
> set @.Id = '''{23ABFD83-0A00-40D2-8E1F-333055062862}''' + ',' +
> '''{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}'''+ ','
> +'''{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'''
>
> declare @.QueryStr as nVarchar(3000)
> Select @.QueryStr = 'Select * from table1 where id in (' + @.Id + ')'
> exec sp_executesql @.querystr
> "Dan Guzman" wrote:
>
query problem
The subselect in the query returns 897 rows, but when it
is included in the
where clause of an update statement, the whole table is
returned and
updated. Why? And how can I change this to only update
the 897 rows that
the subselect is returning?
UPDATE Item
SET sz_amount = 1
WHERE item_id IN (SELECT Item.item_id FROM dbo.Item INNER
JOIN dbo.ItemExtended ON dbo.Item.item_id =
dbo.ItemExtended.item_id INNER JOIN dbo.IRISubcategory ON
dbo.IRISubcategory.iri_subcategory_id =
dbo.ItemExtended.iri_subcategory_id INNER JOIN
dbo.IRICategory ON dbo.IRISubcategory.iri_category_id
= dbo.IRICategory.iri_category_id WHERE
(dbo.Item.sz_amount = 10) AND
(dbo.IRICategory.code = '1820'))I'm not sure but try this an tell me if it works.
UPDATE Item
SET sz_amount = 1
FROM dbo.Item INNER
JOIN dbo.ItemExtended ON dbo.Item.item_id = dbo.ItemExtended.item_id
JOIN dbo.IRISubcategory ON dbo.IRISubcategory.iri_subcategory_id =
dbo.ItemExtended.iri_subcategory_id
JOIN dbo.IRICategory ON dbo.IRISubcategory.iri_category_id =
dbo.IRICategory.iri_category_id
WHERE dbo.Item.sz_amount = 10 AND
dbo.IRICategory.code = '1820'
--Buddy
"Jamie Elliott" <jelliott@.alexlee.com> wrote in message
news:253d01c427cf$38e3e730$a501280a@.phx.gbl...
> The subselect in the query returns 897 rows, but when it
> is included in the
> where clause of an update statement, the whole table is
> returned and
> updated. Why? And how can I change this to only update
> the 897 rows that
> the subselect is returning?
>
> UPDATE Item
> SET sz_amount = 1
> WHERE item_id IN (SELECT Item.item_id FROM dbo.Item INNER
> JOIN dbo.ItemExtended ON dbo.Item.item_id =
> dbo.ItemExtended.item_id INNER JOIN dbo.IRISubcategory ON
> dbo.IRISubcategory.iri_subcategory_id =
> dbo.ItemExtended.iri_subcategory_id INNER JOIN
> dbo.IRICategory ON dbo.IRISubcategory.iri_category_id
> = dbo.IRICategory.iri_category_id WHERE
> (dbo.Item.sz_amount = 10) AND
> (dbo.IRICategory.code = '1820'))
>
>
>|||Hi Jamie,
From your descriptions, I know your subselect query will work fine and get
the correct result alone. However it goes wrong when you make it as
subselect.
Would you please have a try on Buddy Ackerman's query and tell me whether
it works. If it doesn't, would you please show me your DDL and I could
reproduce it on my machine
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Support
****************************************
*******************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.sql
is included in the
where clause of an update statement, the whole table is
returned and
updated. Why? And how can I change this to only update
the 897 rows that
the subselect is returning?
UPDATE Item
SET sz_amount = 1
WHERE item_id IN (SELECT Item.item_id FROM dbo.Item INNER
JOIN dbo.ItemExtended ON dbo.Item.item_id =
dbo.ItemExtended.item_id INNER JOIN dbo.IRISubcategory ON
dbo.IRISubcategory.iri_subcategory_id =
dbo.ItemExtended.iri_subcategory_id INNER JOIN
dbo.IRICategory ON dbo.IRISubcategory.iri_category_id
= dbo.IRICategory.iri_category_id WHERE
(dbo.Item.sz_amount = 10) AND
(dbo.IRICategory.code = '1820'))I'm not sure but try this an tell me if it works.
UPDATE Item
SET sz_amount = 1
FROM dbo.Item INNER
JOIN dbo.ItemExtended ON dbo.Item.item_id = dbo.ItemExtended.item_id
JOIN dbo.IRISubcategory ON dbo.IRISubcategory.iri_subcategory_id =
dbo.ItemExtended.iri_subcategory_id
JOIN dbo.IRICategory ON dbo.IRISubcategory.iri_category_id =
dbo.IRICategory.iri_category_id
WHERE dbo.Item.sz_amount = 10 AND
dbo.IRICategory.code = '1820'
--Buddy
"Jamie Elliott" <jelliott@.alexlee.com> wrote in message
news:253d01c427cf$38e3e730$a501280a@.phx.gbl...
> The subselect in the query returns 897 rows, but when it
> is included in the
> where clause of an update statement, the whole table is
> returned and
> updated. Why? And how can I change this to only update
> the 897 rows that
> the subselect is returning?
>
> UPDATE Item
> SET sz_amount = 1
> WHERE item_id IN (SELECT Item.item_id FROM dbo.Item INNER
> JOIN dbo.ItemExtended ON dbo.Item.item_id =
> dbo.ItemExtended.item_id INNER JOIN dbo.IRISubcategory ON
> dbo.IRISubcategory.iri_subcategory_id =
> dbo.ItemExtended.iri_subcategory_id INNER JOIN
> dbo.IRICategory ON dbo.IRISubcategory.iri_category_id
> = dbo.IRICategory.iri_category_id WHERE
> (dbo.Item.sz_amount = 10) AND
> (dbo.IRICategory.code = '1820'))
>
>
>|||Hi Jamie,
From your descriptions, I know your subselect query will work fine and get
the correct result alone. However it goes wrong when you make it as
subselect.
Would you please have a try on Buddy Ackerman's query and tell me whether
it works. If it doesn't, would you please show me your DDL and I could
reproduce it on my machine
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Support
****************************************
*******************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.sql
Friday, March 23, 2012
Query permissions on objects for roles?
How do I query the sys tables in a database to find what objects have what permissions for a given role? A SELECT statement is desired, and I'd like to avoid using sp_ stored procedures, but mention of a few to look at would be appreciated.
Thanks to anyone who can help.Here is part of a script I wrote for restoring server and database configuration. It creates an sql script for restoring user and role permissions. It uses the bitwise & operator to decode values in the sypermissions table. If it's not exactly what you are looking for, you can deconstruct it to see how to pull the data you need.
blindman
-------
set nocount on
select '--OBJECT LEVEL PERMISSIONS'
select 'GRANT SELECT ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 1 & syspermissions.actadd > 0
go
select 'DENY SELECT ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 1 & syspermissions.actmod > 0
go
select 'GRANT UPDATE ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 2 & syspermissions.actadd > 0
go
select 'DENY UPDATE ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 2 & syspermissions.actmod > 0
go
select 'GRANT REFERENCES ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 4 & syspermissions.actadd > 0
go
select 'DENY REFERENCES ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 4 & syspermissions.actmod > 0
go
select 'GRANT INSERT ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 8 & syspermissions.actadd > 0
go
select 'DENY INSERT ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 8 & syspermissions.actmod > 0
go
select 'GRANT DELETE ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 16 & syspermissions.actadd > 0
go
select 'DENY DELETE ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 16 & syspermissions.actmod > 0
go
select 'GRANT EXECUTE ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 32 & syspermissions.actadd > 0
go
select 'DENY EXECUTE ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 32 & syspermissions.actmod > 0
go|||I would suggest looking at sp_helprotect , you may be able to borrow code from it and my code below to do what you want. Also, look at the view INFORMATION_SCHEMA.TABLE_PRIVILEGES in master DB.
Tim S
Here's my sproc to script permissions:
CREATE proc dbo.adm_script_permissions
(
@.name ncharacter varying(776) = NULL,
@.username sysname = NULL,
@.grantorname sysname = NULL,
@.permissionarea character varying(10) = 'o s'
)AS
BEGIN
SET NOCOUNT ON
CREATE Table #tmp_protect_data ( OwnerName sysname NOT Null, ObjectName sysname NOT Null, GranteeName sysname NOT Null,
GrantorName sysname NOT Null, ProtectType char(10) Null, ActionName varchar(20) Null, ColumnName sysname Null)
SET @.username = PARSENAME ( @.username , 1 ) -- Remove []
insert into #tmp_protect_data (OwnerName, ObjectName, GranteeName, GrantorName, ProtectType, ActionName, ColumnName )
exec sp_helprotect @.name = @.name, @.username = @.username, @.grantorname = @.grantorname, @.permissionarea = @.permissionarea
SELECT RTRIM(ProtectType) + ' ' + ActionName +
CASE
WHEN ColumnName IN ('.','(All+New)','(All)','(New)') THEN ''
ELSE ' (' + ColumnName + ')'
END +
' ON ' + OwnerName + '.' + ObjectName + ' TO ' + GranteeName + char(13)+char(10)
FROM #tmp_protect_data
WHERE ColumnName IS NOT NULL AND -- ColumnName is NULL on deleted columns
OwnerName <> 'INFORMATION_SCHEMA' AND ObjectName NOT LIKE 'dt[_]%' AND ObjectName NOT IN ('dtproperties')
ORDER BY OwnerName + '.' + ObjectName, GranteeName, ActionName, RTRIM(ProtectType)
DROP Table #tmp_protect_data
SET NOCOUNT OFF
END
Thanks to anyone who can help.Here is part of a script I wrote for restoring server and database configuration. It creates an sql script for restoring user and role permissions. It uses the bitwise & operator to decode values in the sypermissions table. If it's not exactly what you are looking for, you can deconstruct it to see how to pull the data you need.
blindman
-------
set nocount on
select '--OBJECT LEVEL PERMISSIONS'
select 'GRANT SELECT ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 1 & syspermissions.actadd > 0
go
select 'DENY SELECT ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 1 & syspermissions.actmod > 0
go
select 'GRANT UPDATE ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 2 & syspermissions.actadd > 0
go
select 'DENY UPDATE ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 2 & syspermissions.actmod > 0
go
select 'GRANT REFERENCES ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 4 & syspermissions.actadd > 0
go
select 'DENY REFERENCES ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 4 & syspermissions.actmod > 0
go
select 'GRANT INSERT ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 8 & syspermissions.actadd > 0
go
select 'DENY INSERT ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 8 & syspermissions.actmod > 0
go
select 'GRANT DELETE ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 16 & syspermissions.actadd > 0
go
select 'DENY DELETE ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 16 & syspermissions.actmod > 0
go
select 'GRANT EXECUTE ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 32 & syspermissions.actadd > 0
go
select 'DENY EXECUTE ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 32 & syspermissions.actmod > 0
go|||I would suggest looking at sp_helprotect , you may be able to borrow code from it and my code below to do what you want. Also, look at the view INFORMATION_SCHEMA.TABLE_PRIVILEGES in master DB.
Tim S
Here's my sproc to script permissions:
CREATE proc dbo.adm_script_permissions
(
@.name ncharacter varying(776) = NULL,
@.username sysname = NULL,
@.grantorname sysname = NULL,
@.permissionarea character varying(10) = 'o s'
)AS
BEGIN
SET NOCOUNT ON
CREATE Table #tmp_protect_data ( OwnerName sysname NOT Null, ObjectName sysname NOT Null, GranteeName sysname NOT Null,
GrantorName sysname NOT Null, ProtectType char(10) Null, ActionName varchar(20) Null, ColumnName sysname Null)
SET @.username = PARSENAME ( @.username , 1 ) -- Remove []
insert into #tmp_protect_data (OwnerName, ObjectName, GranteeName, GrantorName, ProtectType, ActionName, ColumnName )
exec sp_helprotect @.name = @.name, @.username = @.username, @.grantorname = @.grantorname, @.permissionarea = @.permissionarea
SELECT RTRIM(ProtectType) + ' ' + ActionName +
CASE
WHEN ColumnName IN ('.','(All+New)','(All)','(New)') THEN ''
ELSE ' (' + ColumnName + ')'
END +
' ON ' + OwnerName + '.' + ObjectName + ' TO ' + GranteeName + char(13)+char(10)
FROM #tmp_protect_data
WHERE ColumnName IS NOT NULL AND -- ColumnName is NULL on deleted columns
OwnerName <> 'INFORMATION_SCHEMA' AND ObjectName NOT LIKE 'dt[_]%' AND ObjectName NOT IN ('dtproperties')
ORDER BY OwnerName + '.' + ObjectName, GranteeName, ActionName, RTRIM(ProtectType)
DROP Table #tmp_protect_data
SET NOCOUNT OFF
END
Wednesday, March 21, 2012
Query Performance
Hi Gurus,
I have run DBCC SHOWCONTIG statement against the table X as shown below:
DBCC SHOWCONTIG (X)
GO
and received the following result:
DBCC SHOWCONTIG scanning 'X' table...
Table: 'X' (885578193); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned........................: 1400
- Extents Scanned.......................: 300
- Extent Switches.......................: 300
- Avg. Pages per Extent..................: 4.7
- Scan Density [Best Count:Actual Count]......: 50.00% [200:400]
- Logical Scan Fragmentation ..............: 21.43%
- Extent Scan Fragmentation ...............: 33.33%
- Avg. Bytes Free per Page................: 2107.1
- Avg. Page Density (full)................: 73.97%
Which one of the following is more recommended way to increase query performance against the table X?
A. Run DBCC DBREINDEX statement with high fill factor value to rebuild the clustered index.
B. Run DBCC DBREINDEX statement with low fill factor value to rebuild the clustered index.
C. Drop and re-create the clustered index by using DROP INDEX and CREATE INDEX statements with low fill factor value.
D. Drop and re-create the clustered index by using DROP INDEX and CREATE INDEX statements with high fill factor value.
E. Set the truncate log on checkpoint option for the database which contain the Employee table.
Thank youIf they are asking for high query performance, then you don't want a low fill factor, you want as much data per data page. That being said, I would choose (A). (D) would be OK too but it is one more process as you would drop the clustered, recreate it, then any non-clustereds would be rebuilt, that's three steps instead of two.
HTH|||HTH,
Thanks very much.
I have run DBCC SHOWCONTIG statement against the table X as shown below:
DBCC SHOWCONTIG (X)
GO
and received the following result:
DBCC SHOWCONTIG scanning 'X' table...
Table: 'X' (885578193); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned........................: 1400
- Extents Scanned.......................: 300
- Extent Switches.......................: 300
- Avg. Pages per Extent..................: 4.7
- Scan Density [Best Count:Actual Count]......: 50.00% [200:400]
- Logical Scan Fragmentation ..............: 21.43%
- Extent Scan Fragmentation ...............: 33.33%
- Avg. Bytes Free per Page................: 2107.1
- Avg. Page Density (full)................: 73.97%
Which one of the following is more recommended way to increase query performance against the table X?
A. Run DBCC DBREINDEX statement with high fill factor value to rebuild the clustered index.
B. Run DBCC DBREINDEX statement with low fill factor value to rebuild the clustered index.
C. Drop and re-create the clustered index by using DROP INDEX and CREATE INDEX statements with low fill factor value.
D. Drop and re-create the clustered index by using DROP INDEX and CREATE INDEX statements with high fill factor value.
E. Set the truncate log on checkpoint option for the database which contain the Employee table.
Thank youIf they are asking for high query performance, then you don't want a low fill factor, you want as much data per data page. That being said, I would choose (A). (D) would be OK too but it is one more process as you would drop the clustered, recreate it, then any non-clustereds would be rebuilt, that's three steps instead of two.
HTH|||HTH,
Thanks very much.
Tuesday, March 20, 2012
Query Performance
I am attempting to get a better understanding of why my SQL 2005 setup when running a simple select statement on a large table is displaying very low IO in performance monitor. If i run a single Select * From testtable i see 4mb\sec transfer and Disk reads\sec is around 8-9. This particular table is sitting on a single U320 10k drive so i expecting to see far more substantial IO. Does anyone have any information on how IO is consumed using different SQL operations so i can obtain a better understanding?
Flush the cache. Some, and possibly a lot, of data may be cached in memory.
Also, since a select query does not require any transaction logging or updating of indexes or statistics, a select query would have substantially less IO than an insert, update, or delete query.
Flush the cache and then run: Insert Into testtable Select * From testtable Order By SomeNonClusteredField
|||hi
I did flush the cache prior to running this statement. Why doent SQL use the I\O to its full potential if that is the only process running on the box? I was expecting to see the I\O near to saturated.
Monday, March 12, 2012
Query Optimizer Question #2
Please consider the following Select statement. I'm not sure how this is
going to end up getting formatted when posted but please notice the two
sub-queries in the main Select statement. They are identical except for the
"AS" clause. I have the following questions:
1) Will the SQL 2000 or 2005 optimizer see these are identical and execute
them only once instead of twice?
2) If the answer to #1 is that they will be executed twice, is there a way
to optimize this so that the two subqueries are only executed once?
SELECT
ProjectTran.ProjectRowID,
ProjectTran.RowID,
ProjectTran.ProjectWBSRowID,
PA.BillingTypeRowID,
'REVENUE',
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,
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 ProjectTran
LEFT OUTER JOIN @.ProjectAccount PA ON
dbo.ProjectTran.ProjectAccountRowID = PA.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')In general, the query optimizer will not search for equivalent subqueries
and remove them if they are written twice in the same query, no. It is
fairly expensive in compilation time to try to do this, and it is so rarely
written that the payoff is very, very low to using this approach vs. the
general case.
You can:
1. Write the subquery once (somehow - there are multiple ways). For
example, I believe you can use nested blocks to write the subquery once,
give the result an alias, and then reference it in a subsequent block twice.
2. Use CROSS APPLY and make it a join, then reference the resulting column
twice. You should take care here to make sure that the join does not change
your output cardinality. This require some expertise, so try (1) unless you
are very comfortable with relational algebra equivalences.
Good luck.
Conor Cunningham
SQL Server Query Optimization Development Lead
"Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
news:e1FxxLzMGHA.140@.TK2MSFTNGP12.phx.gbl...
> Please consider the following Select statement. I'm not sure how this is
> going to end up getting formatted when posted but please notice the two
> sub-queries in the main Select statement. They are identical except for
> the "AS" clause. I have the following questions:
> 1) Will the SQL 2000 or 2005 optimizer see these are identical and execute
> them only once instead of twice?
> 2) If the answer to #1 is that they will be executed twice, is there a way
> to optimize this so that the two subqueries are only executed once?
> SELECT
> ProjectTran.ProjectRowID,
> ProjectTran.RowID,
> ProjectTran.ProjectWBSRowID,
> PA.BillingTypeRowID,
> 'REVENUE',
> 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,
> 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 ProjectTran
> LEFT OUTER JOIN @.ProjectAccount PA ON
> dbo.ProjectTran.ProjectAccountRowID = PA.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')
>|||It is highly unlikely that the subquery will be executed once, because
that is not how query plans work in SQL Server. However, the any I/O
will probably only be done once and retained in the data cache for the
second execution.
I am not sure if it is faster (you would have to test this), but you
could rewrite it as below:
SELECT
ProjectTran.ProjectRowID,
ProjectTran.RowID,
ProjectTran.ProjectWBSRowID,
PA.BillingTypeRowID,
'REVENUE',
ProjectTran.PriorInvoiceAmount - I.SumOfInvoiceAmount AS Revenue,
ProjectTran.PriorInvoiceAmount - I.SumOfInvoiceAmount AS BilledAmount
FROM ProjectTran
LEFT OUTER JOIN @.ProjectAccount PA
ON dbo.ProjectTran.ProjectAccountRowID = PA.ProjectAccountRowID
INNER JOIN dbo.ProjectDoc
ON dbo.ProjectTran.ProjectDocRowID = dbo.ProjectDoc.RowID
LEFT OUTER JOIN (
SELECT dbo.ProjectInvoiceDetail.ProjectTranRowID,
COALESCE(SUM(dbo.ProjectInvoiceDetail.InvoiceAmount),0) AS
SumOfInvoiceAmount
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
GROUP BY dbo.ProjectInvoiceDetail.ProjectTranRowID
) AS I
ON I.ProjectTranRowID = ProjectTran.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'
)
HTH,
Gert-Jan
Amos Soma wrote:
> Please consider the following Select statement. I'm not sure how this is
> going to end up getting formatted when posted but please notice the two
> sub-queries in the main Select statement. They are identical except for the
> "AS" clause. I have the following questions:
> 1) Will the SQL 2000 or 2005 optimizer see these are identical and execute
> them only once instead of twice?
> 2) If the answer to #1 is that they will be executed twice, is there a way
> to optimize this so that the two subqueries are only executed once?
> SELECT
> ProjectTran.ProjectRowID,
> ProjectTran.RowID,
> ProjectTran.ProjectWBSRowID,
> PA.BillingTypeRowID,
> 'REVENUE',
> 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,
> 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 ProjectTran
> LEFT OUTER JOIN @.ProjectAccount PA ON
> dbo.ProjectTran.ProjectAccountRowID = PA.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')|||If they are identical and you want to optimize it, take a CTE statement
to pre define the query. The CTE is available since SQL 2005
WITH T_Revenue
AS
(
SELECT COALESCE(SUM(dbo.ProjectInvoiceDetail.InvoiceAmount),0) AS Revenue
FROM dbo.ProjectInvoiceDetail PID
INNER JOIN dbo.ProjectInvoice PI
on PID.ProjectInvoiceRowID = PI.RowID
WHERE PI.LedgerDocRowID IS NOT NULL
AND PI.StatusRowID = 1165
AND PID.InvoiceAmount <> 0
AND PID.ProjectTranRowID = ProjectTran.RowID
)
SELECT PT.ProjectRowID,
PT.RowID,
PT.ProjectWBSRowID,
PA.BillingTypeRowID,
'REVENUE',
PT.PriorInvoiceAmount-(SELECT Revenue
FROM T_REvenue) AS Revenue,
PT.PriorInvoiceAmount - (SELECT Revenue
FROM T_REvenue) AS BilledAmount
FROM ProjectTran PT
LEFT OUTER JOIN @.ProjectAccount PA
ON PT.ProjectAccountRowID = PA.ProjectAccountRowID
INNER JOIN dbo.ProjectDoc PD
ON PT.ProjectDocRowID = PD.RowID
Where PT.ProjectRowID in (Select RowID
From #ProjectTemp)
AND PT.ProjectDocRowID IS NOT NULL
AND PD.StatusRowID = 1003
AND PT.RowID NOT IN (Select TransactionRowID
From @.ProjectDataDetail
WHERE AccountTypeDescription = 'REVENUE')
Take a look at the paper I wrote :
http://www.sqlservercentral.com/columnists/fBROUARD/recursivequeriesinsql1999andsqlserver2005.asp
A +
Amos Soma a écrit :
> Please consider the following Select statement. I'm not sure how this is
> going to end up getting formatted when posted but please notice the two
> sub-queries in the main Select statement. They are identical except for the
> "AS" clause. I have the following questions:
> 1) Will the SQL 2000 or 2005 optimizer see these are identical and execute
> them only once instead of twice?
> 2) If the answer to #1 is that they will be executed twice, is there a way
> to optimize this so that the two subqueries are only executed once?
> SELECT
> ProjectTran.ProjectRowID,
> ProjectTran.RowID,
> ProjectTran.ProjectWBSRowID,
> PA.BillingTypeRowID,
> 'REVENUE',
> 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,
> 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 ProjectTran
> LEFT OUTER JOIN @.ProjectAccount PA ON
> dbo.ProjectTran.ProjectAccountRowID = PA.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')
>
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||Frédéric (is that your name? MVP's do not usually post messages
anonymously),
Have you actually seen performance improvement using a CTE for this type
of problem?
I have tried to write a CTE in a simplified experiment (see below), and
it clearly shows that the CTE query is executed more than once. In the
example below, the first query requires 6 logical reads, the second only
3, which indicates that the CTE is executed twice in the first query.
-- create tables and load a few rows
CREATE TABLE projects
(row_id int not null PRIMARY KEY
,prior_invoice_amount int not null
)
CREATE TABLE project_invoice_details
(row_id int not null REFERENCES projects
,project_invoice_row_id int not null
,invoice_amount int not null
,CONSTRAINT PK_ProjectInvoiceDetails PRIMARY KEY
(row_id,project_invoice_row_id)
)
set nocount on
INSERT INTO projects VALUES (1, 0)
INSERT INTO projects VALUES (2,100)
INSERT INTO projects VALUES (3,200)
INSERT INTO project_invoice_details VALUES (1, 1, 10)
INSERT INTO project_invoice_details VALUES (1, 2, 20)
INSERT INTO project_invoice_details VALUES (1, 3, 30)
INSERT INTO project_invoice_details VALUES (2, 1, 25)
INSERT INTO project_invoice_details VALUES (3, 1, 80)
set nocount off
go
set statistics io on
go
-- query with 2 calls to the CTE
WITH T_revenue AS (
SELECT row_id, COALESCE(SUM(invoice_amount),0) AS revenue
FROM project_invoice_details PID
GROUP BY row_id
)
SELECT P.row_id,
P.prior_invoice_amount - (SELECT revenue
FROM T_revenue
WHERE T_revenue.row_id=P.row_id) AS
revenue,
P.prior_invoice_amount - (SELECT revenue
FROM T_revenue
WHERE T_revenue.row_id=P.row_id) AS
billed_amount
FROM projects P
go
-- query with only 1 call to the CTE
WITH T_revenue AS (
SELECT row_id, COALESCE(SUM(invoice_amount),0) AS revenue
FROM project_invoice_details PID
GROUP BY row_id
)
SELECT P.row_id,
P.prior_invoice_amount - (SELECT revenue
FROM T_revenue
WHERE T_revenue.row_id=P.row_id) AS
revenue,
P.prior_invoice_amount AS billed_amount
FROM projects P
go
set statistics io off
go
DROP TABLE project_invoice_details
DROP TABLE projects
Gert-Jan
P.S. I did not read your article, because the site requires
registration.
"SQLpro [MVP]" wrote:
> If they are identical and you want to optimize it, take a CTE statement
> to pre define the query. The CTE is available since SQL 2005
> WITH T_Revenue
> AS
> (
> SELECT COALESCE(SUM(dbo.ProjectInvoiceDetail.InvoiceAmount),0) AS Revenue
> FROM dbo.ProjectInvoiceDetail PID
> INNER JOIN dbo.ProjectInvoice PI
> on PID.ProjectInvoiceRowID = PI.RowID
> WHERE PI.LedgerDocRowID IS NOT NULL
> AND PI.StatusRowID = 1165
> AND PID.InvoiceAmount <> 0
> AND PID.ProjectTranRowID = ProjectTran.RowID
> )
> SELECT PT.ProjectRowID,
> PT.RowID,
> PT.ProjectWBSRowID,
> PA.BillingTypeRowID,
> 'REVENUE',
> PT.PriorInvoiceAmount-(SELECT Revenue
> FROM T_REvenue) AS Revenue,
> PT.PriorInvoiceAmount - (SELECT Revenue
> FROM T_REvenue) AS BilledAmount
> FROM ProjectTran PT
> LEFT OUTER JOIN @.ProjectAccount PA
> ON PT.ProjectAccountRowID = PA.ProjectAccountRowID
> INNER JOIN dbo.ProjectDoc PD
> ON PT.ProjectDocRowID = PD.RowID
> Where PT.ProjectRowID in (Select RowID
> From #ProjectTemp)
> AND PT.ProjectDocRowID IS NOT NULL
> AND PD.StatusRowID = 1003
> AND PT.RowID NOT IN (Select TransactionRowID
> From @.ProjectDataDetail
> WHERE AccountTypeDescription = 'REVENUE')
> Take a look at the paper I wrote :
> http://www.sqlservercentral.com/columnists/fBROUARD/recursivequeriesinsql1999andsqlserver2005.asp
[snip]|||I haven't try yet to experiment performances about CTE. I just post a
complete study about CTE and many ways to do with CTE and recursive queries.
The fact I post without my complete name is due to the fact I am
actually in a firm that is badly organize about Internet access and I
cannot have my own newreader with my personnal settings.
Yes I am Fred Brouard.
The ways you does it is interesting.
If I have time I will experiment performances about CTE in the next monthes.
A +
Gert-Jan Strik a écrit :
> Frédéric (is that your name? MVP's do not usually post messages
> anonymously),
> Have you actually seen performance improvement using a CTE for this type
> of problem?
> I have tried to write a CTE in a simplified experiment (see below), and
> it clearly shows that the CTE query is executed more than once. In the
> example below, the first query requires 6 logical reads, the second only
> 3, which indicates that the CTE is executed twice in the first query.
>
> -- create tables and load a few rows
> CREATE TABLE projects
> (row_id int not null PRIMARY KEY
> ,prior_invoice_amount int not null
> )
> CREATE TABLE project_invoice_details
> (row_id int not null REFERENCES projects
> ,project_invoice_row_id int not null
> ,invoice_amount int not null
> ,CONSTRAINT PK_ProjectInvoiceDetails PRIMARY KEY
> (row_id,project_invoice_row_id)
> )
> set nocount on
> INSERT INTO projects VALUES (1, 0)
> INSERT INTO projects VALUES (2,100)
> INSERT INTO projects VALUES (3,200)
> INSERT INTO project_invoice_details VALUES (1, 1, 10)
> INSERT INTO project_invoice_details VALUES (1, 2, 20)
> INSERT INTO project_invoice_details VALUES (1, 3, 30)
> INSERT INTO project_invoice_details VALUES (2, 1, 25)
> INSERT INTO project_invoice_details VALUES (3, 1, 80)
> set nocount off
> go
> set statistics io on
> go
> -- query with 2 calls to the CTE
> WITH T_revenue AS (
> SELECT row_id, COALESCE(SUM(invoice_amount),0) AS revenue
> FROM project_invoice_details PID
> GROUP BY row_id
> )
> SELECT P.row_id,
> P.prior_invoice_amount - (SELECT revenue
> FROM T_revenue
> WHERE T_revenue.row_id=P.row_id) AS
> revenue,
> P.prior_invoice_amount - (SELECT revenue
> FROM T_revenue
> WHERE T_revenue.row_id=P.row_id) AS
> billed_amount
> FROM projects P
> go
> -- query with only 1 call to the CTE
> WITH T_revenue AS (
> SELECT row_id, COALESCE(SUM(invoice_amount),0) AS revenue
> FROM project_invoice_details PID
> GROUP BY row_id
> )
> SELECT P.row_id,
> P.prior_invoice_amount - (SELECT revenue
> FROM T_revenue
> WHERE T_revenue.row_id=P.row_id) AS
> revenue,
> P.prior_invoice_amount AS billed_amount
> FROM projects P
> go
> set statistics io off
> go
> DROP TABLE project_invoice_details
> DROP TABLE projects
>
> Gert-Jan
> P.S. I did not read your article, because the site requires
> registration.
>
> "SQLpro [MVP]" wrote:
>> If they are identical and you want to optimize it, take a CTE statement
>> to pre define the query. The CTE is available since SQL 2005
>> WITH T_Revenue
>> AS
>> (
>> SELECT COALESCE(SUM(dbo.ProjectInvoiceDetail.InvoiceAmount),0) AS Revenue
>> FROM dbo.ProjectInvoiceDetail PID
>> INNER JOIN dbo.ProjectInvoice PI
>> on PID.ProjectInvoiceRowID = PI.RowID
>> WHERE PI.LedgerDocRowID IS NOT NULL
>> AND PI.StatusRowID = 1165
>> AND PID.InvoiceAmount <> 0
>> AND PID.ProjectTranRowID = ProjectTran.RowID
>> )
>> SELECT PT.ProjectRowID,
>> PT.RowID,
>> PT.ProjectWBSRowID,
>> PA.BillingTypeRowID,
>> 'REVENUE',
>> PT.PriorInvoiceAmount-(SELECT Revenue
>> FROM T_REvenue) AS Revenue,
>> PT.PriorInvoiceAmount - (SELECT Revenue
>> FROM T_REvenue) AS BilledAmount
>> FROM ProjectTran PT
>> LEFT OUTER JOIN @.ProjectAccount PA
>> ON PT.ProjectAccountRowID = PA.ProjectAccountRowID
>> INNER JOIN dbo.ProjectDoc PD
>> ON PT.ProjectDocRowID = PD.RowID
>> Where PT.ProjectRowID in (Select RowID
>> From #ProjectTemp)
>> AND PT.ProjectDocRowID IS NOT NULL
>> AND PD.StatusRowID = 1003
>> AND PT.RowID NOT IN (Select TransactionRowID
>> From @.ProjectDataDetail
>> WHERE AccountTypeDescription = 'REVENUE')
>> Take a look at the paper I wrote :
>> http://www.sqlservercentral.com/columnists/fBROUARD/recursivequeriesinsql1999andsqlserver2005.asp
> [snip]
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************
going to end up getting formatted when posted but please notice the two
sub-queries in the main Select statement. They are identical except for the
"AS" clause. I have the following questions:
1) Will the SQL 2000 or 2005 optimizer see these are identical and execute
them only once instead of twice?
2) If the answer to #1 is that they will be executed twice, is there a way
to optimize this so that the two subqueries are only executed once?
SELECT
ProjectTran.ProjectRowID,
ProjectTran.RowID,
ProjectTran.ProjectWBSRowID,
PA.BillingTypeRowID,
'REVENUE',
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,
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 ProjectTran
LEFT OUTER JOIN @.ProjectAccount PA ON
dbo.ProjectTran.ProjectAccountRowID = PA.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')In general, the query optimizer will not search for equivalent subqueries
and remove them if they are written twice in the same query, no. It is
fairly expensive in compilation time to try to do this, and it is so rarely
written that the payoff is very, very low to using this approach vs. the
general case.
You can:
1. Write the subquery once (somehow - there are multiple ways). For
example, I believe you can use nested blocks to write the subquery once,
give the result an alias, and then reference it in a subsequent block twice.
2. Use CROSS APPLY and make it a join, then reference the resulting column
twice. You should take care here to make sure that the join does not change
your output cardinality. This require some expertise, so try (1) unless you
are very comfortable with relational algebra equivalences.
Good luck.
Conor Cunningham
SQL Server Query Optimization Development Lead
"Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
news:e1FxxLzMGHA.140@.TK2MSFTNGP12.phx.gbl...
> Please consider the following Select statement. I'm not sure how this is
> going to end up getting formatted when posted but please notice the two
> sub-queries in the main Select statement. They are identical except for
> the "AS" clause. I have the following questions:
> 1) Will the SQL 2000 or 2005 optimizer see these are identical and execute
> them only once instead of twice?
> 2) If the answer to #1 is that they will be executed twice, is there a way
> to optimize this so that the two subqueries are only executed once?
> SELECT
> ProjectTran.ProjectRowID,
> ProjectTran.RowID,
> ProjectTran.ProjectWBSRowID,
> PA.BillingTypeRowID,
> 'REVENUE',
> 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,
> 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 ProjectTran
> LEFT OUTER JOIN @.ProjectAccount PA ON
> dbo.ProjectTran.ProjectAccountRowID = PA.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')
>|||It is highly unlikely that the subquery will be executed once, because
that is not how query plans work in SQL Server. However, the any I/O
will probably only be done once and retained in the data cache for the
second execution.
I am not sure if it is faster (you would have to test this), but you
could rewrite it as below:
SELECT
ProjectTran.ProjectRowID,
ProjectTran.RowID,
ProjectTran.ProjectWBSRowID,
PA.BillingTypeRowID,
'REVENUE',
ProjectTran.PriorInvoiceAmount - I.SumOfInvoiceAmount AS Revenue,
ProjectTran.PriorInvoiceAmount - I.SumOfInvoiceAmount AS BilledAmount
FROM ProjectTran
LEFT OUTER JOIN @.ProjectAccount PA
ON dbo.ProjectTran.ProjectAccountRowID = PA.ProjectAccountRowID
INNER JOIN dbo.ProjectDoc
ON dbo.ProjectTran.ProjectDocRowID = dbo.ProjectDoc.RowID
LEFT OUTER JOIN (
SELECT dbo.ProjectInvoiceDetail.ProjectTranRowID,
COALESCE(SUM(dbo.ProjectInvoiceDetail.InvoiceAmount),0) AS
SumOfInvoiceAmount
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
GROUP BY dbo.ProjectInvoiceDetail.ProjectTranRowID
) AS I
ON I.ProjectTranRowID = ProjectTran.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'
)
HTH,
Gert-Jan
Amos Soma wrote:
> Please consider the following Select statement. I'm not sure how this is
> going to end up getting formatted when posted but please notice the two
> sub-queries in the main Select statement. They are identical except for the
> "AS" clause. I have the following questions:
> 1) Will the SQL 2000 or 2005 optimizer see these are identical and execute
> them only once instead of twice?
> 2) If the answer to #1 is that they will be executed twice, is there a way
> to optimize this so that the two subqueries are only executed once?
> SELECT
> ProjectTran.ProjectRowID,
> ProjectTran.RowID,
> ProjectTran.ProjectWBSRowID,
> PA.BillingTypeRowID,
> 'REVENUE',
> 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,
> 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 ProjectTran
> LEFT OUTER JOIN @.ProjectAccount PA ON
> dbo.ProjectTran.ProjectAccountRowID = PA.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')|||If they are identical and you want to optimize it, take a CTE statement
to pre define the query. The CTE is available since SQL 2005
WITH T_Revenue
AS
(
SELECT COALESCE(SUM(dbo.ProjectInvoiceDetail.InvoiceAmount),0) AS Revenue
FROM dbo.ProjectInvoiceDetail PID
INNER JOIN dbo.ProjectInvoice PI
on PID.ProjectInvoiceRowID = PI.RowID
WHERE PI.LedgerDocRowID IS NOT NULL
AND PI.StatusRowID = 1165
AND PID.InvoiceAmount <> 0
AND PID.ProjectTranRowID = ProjectTran.RowID
)
SELECT PT.ProjectRowID,
PT.RowID,
PT.ProjectWBSRowID,
PA.BillingTypeRowID,
'REVENUE',
PT.PriorInvoiceAmount-(SELECT Revenue
FROM T_REvenue) AS Revenue,
PT.PriorInvoiceAmount - (SELECT Revenue
FROM T_REvenue) AS BilledAmount
FROM ProjectTran PT
LEFT OUTER JOIN @.ProjectAccount PA
ON PT.ProjectAccountRowID = PA.ProjectAccountRowID
INNER JOIN dbo.ProjectDoc PD
ON PT.ProjectDocRowID = PD.RowID
Where PT.ProjectRowID in (Select RowID
From #ProjectTemp)
AND PT.ProjectDocRowID IS NOT NULL
AND PD.StatusRowID = 1003
AND PT.RowID NOT IN (Select TransactionRowID
From @.ProjectDataDetail
WHERE AccountTypeDescription = 'REVENUE')
Take a look at the paper I wrote :
http://www.sqlservercentral.com/columnists/fBROUARD/recursivequeriesinsql1999andsqlserver2005.asp
A +
Amos Soma a écrit :
> Please consider the following Select statement. I'm not sure how this is
> going to end up getting formatted when posted but please notice the two
> sub-queries in the main Select statement. They are identical except for the
> "AS" clause. I have the following questions:
> 1) Will the SQL 2000 or 2005 optimizer see these are identical and execute
> them only once instead of twice?
> 2) If the answer to #1 is that they will be executed twice, is there a way
> to optimize this so that the two subqueries are only executed once?
> SELECT
> ProjectTran.ProjectRowID,
> ProjectTran.RowID,
> ProjectTran.ProjectWBSRowID,
> PA.BillingTypeRowID,
> 'REVENUE',
> 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,
> 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 ProjectTran
> LEFT OUTER JOIN @.ProjectAccount PA ON
> dbo.ProjectTran.ProjectAccountRowID = PA.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')
>
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||Frédéric (is that your name? MVP's do not usually post messages
anonymously),
Have you actually seen performance improvement using a CTE for this type
of problem?
I have tried to write a CTE in a simplified experiment (see below), and
it clearly shows that the CTE query is executed more than once. In the
example below, the first query requires 6 logical reads, the second only
3, which indicates that the CTE is executed twice in the first query.
-- create tables and load a few rows
CREATE TABLE projects
(row_id int not null PRIMARY KEY
,prior_invoice_amount int not null
)
CREATE TABLE project_invoice_details
(row_id int not null REFERENCES projects
,project_invoice_row_id int not null
,invoice_amount int not null
,CONSTRAINT PK_ProjectInvoiceDetails PRIMARY KEY
(row_id,project_invoice_row_id)
)
set nocount on
INSERT INTO projects VALUES (1, 0)
INSERT INTO projects VALUES (2,100)
INSERT INTO projects VALUES (3,200)
INSERT INTO project_invoice_details VALUES (1, 1, 10)
INSERT INTO project_invoice_details VALUES (1, 2, 20)
INSERT INTO project_invoice_details VALUES (1, 3, 30)
INSERT INTO project_invoice_details VALUES (2, 1, 25)
INSERT INTO project_invoice_details VALUES (3, 1, 80)
set nocount off
go
set statistics io on
go
-- query with 2 calls to the CTE
WITH T_revenue AS (
SELECT row_id, COALESCE(SUM(invoice_amount),0) AS revenue
FROM project_invoice_details PID
GROUP BY row_id
)
SELECT P.row_id,
P.prior_invoice_amount - (SELECT revenue
FROM T_revenue
WHERE T_revenue.row_id=P.row_id) AS
revenue,
P.prior_invoice_amount - (SELECT revenue
FROM T_revenue
WHERE T_revenue.row_id=P.row_id) AS
billed_amount
FROM projects P
go
-- query with only 1 call to the CTE
WITH T_revenue AS (
SELECT row_id, COALESCE(SUM(invoice_amount),0) AS revenue
FROM project_invoice_details PID
GROUP BY row_id
)
SELECT P.row_id,
P.prior_invoice_amount - (SELECT revenue
FROM T_revenue
WHERE T_revenue.row_id=P.row_id) AS
revenue,
P.prior_invoice_amount AS billed_amount
FROM projects P
go
set statistics io off
go
DROP TABLE project_invoice_details
DROP TABLE projects
Gert-Jan
P.S. I did not read your article, because the site requires
registration.
"SQLpro [MVP]" wrote:
> If they are identical and you want to optimize it, take a CTE statement
> to pre define the query. The CTE is available since SQL 2005
> WITH T_Revenue
> AS
> (
> SELECT COALESCE(SUM(dbo.ProjectInvoiceDetail.InvoiceAmount),0) AS Revenue
> FROM dbo.ProjectInvoiceDetail PID
> INNER JOIN dbo.ProjectInvoice PI
> on PID.ProjectInvoiceRowID = PI.RowID
> WHERE PI.LedgerDocRowID IS NOT NULL
> AND PI.StatusRowID = 1165
> AND PID.InvoiceAmount <> 0
> AND PID.ProjectTranRowID = ProjectTran.RowID
> )
> SELECT PT.ProjectRowID,
> PT.RowID,
> PT.ProjectWBSRowID,
> PA.BillingTypeRowID,
> 'REVENUE',
> PT.PriorInvoiceAmount-(SELECT Revenue
> FROM T_REvenue) AS Revenue,
> PT.PriorInvoiceAmount - (SELECT Revenue
> FROM T_REvenue) AS BilledAmount
> FROM ProjectTran PT
> LEFT OUTER JOIN @.ProjectAccount PA
> ON PT.ProjectAccountRowID = PA.ProjectAccountRowID
> INNER JOIN dbo.ProjectDoc PD
> ON PT.ProjectDocRowID = PD.RowID
> Where PT.ProjectRowID in (Select RowID
> From #ProjectTemp)
> AND PT.ProjectDocRowID IS NOT NULL
> AND PD.StatusRowID = 1003
> AND PT.RowID NOT IN (Select TransactionRowID
> From @.ProjectDataDetail
> WHERE AccountTypeDescription = 'REVENUE')
> Take a look at the paper I wrote :
> http://www.sqlservercentral.com/columnists/fBROUARD/recursivequeriesinsql1999andsqlserver2005.asp
[snip]|||I haven't try yet to experiment performances about CTE. I just post a
complete study about CTE and many ways to do with CTE and recursive queries.
The fact I post without my complete name is due to the fact I am
actually in a firm that is badly organize about Internet access and I
cannot have my own newreader with my personnal settings.
Yes I am Fred Brouard.
The ways you does it is interesting.
If I have time I will experiment performances about CTE in the next monthes.
A +
Gert-Jan Strik a écrit :
> Frédéric (is that your name? MVP's do not usually post messages
> anonymously),
> Have you actually seen performance improvement using a CTE for this type
> of problem?
> I have tried to write a CTE in a simplified experiment (see below), and
> it clearly shows that the CTE query is executed more than once. In the
> example below, the first query requires 6 logical reads, the second only
> 3, which indicates that the CTE is executed twice in the first query.
>
> -- create tables and load a few rows
> CREATE TABLE projects
> (row_id int not null PRIMARY KEY
> ,prior_invoice_amount int not null
> )
> CREATE TABLE project_invoice_details
> (row_id int not null REFERENCES projects
> ,project_invoice_row_id int not null
> ,invoice_amount int not null
> ,CONSTRAINT PK_ProjectInvoiceDetails PRIMARY KEY
> (row_id,project_invoice_row_id)
> )
> set nocount on
> INSERT INTO projects VALUES (1, 0)
> INSERT INTO projects VALUES (2,100)
> INSERT INTO projects VALUES (3,200)
> INSERT INTO project_invoice_details VALUES (1, 1, 10)
> INSERT INTO project_invoice_details VALUES (1, 2, 20)
> INSERT INTO project_invoice_details VALUES (1, 3, 30)
> INSERT INTO project_invoice_details VALUES (2, 1, 25)
> INSERT INTO project_invoice_details VALUES (3, 1, 80)
> set nocount off
> go
> set statistics io on
> go
> -- query with 2 calls to the CTE
> WITH T_revenue AS (
> SELECT row_id, COALESCE(SUM(invoice_amount),0) AS revenue
> FROM project_invoice_details PID
> GROUP BY row_id
> )
> SELECT P.row_id,
> P.prior_invoice_amount - (SELECT revenue
> FROM T_revenue
> WHERE T_revenue.row_id=P.row_id) AS
> revenue,
> P.prior_invoice_amount - (SELECT revenue
> FROM T_revenue
> WHERE T_revenue.row_id=P.row_id) AS
> billed_amount
> FROM projects P
> go
> -- query with only 1 call to the CTE
> WITH T_revenue AS (
> SELECT row_id, COALESCE(SUM(invoice_amount),0) AS revenue
> FROM project_invoice_details PID
> GROUP BY row_id
> )
> SELECT P.row_id,
> P.prior_invoice_amount - (SELECT revenue
> FROM T_revenue
> WHERE T_revenue.row_id=P.row_id) AS
> revenue,
> P.prior_invoice_amount AS billed_amount
> FROM projects P
> go
> set statistics io off
> go
> DROP TABLE project_invoice_details
> DROP TABLE projects
>
> Gert-Jan
> P.S. I did not read your article, because the site requires
> registration.
>
> "SQLpro [MVP]" wrote:
>> If they are identical and you want to optimize it, take a CTE statement
>> to pre define the query. The CTE is available since SQL 2005
>> WITH T_Revenue
>> AS
>> (
>> SELECT COALESCE(SUM(dbo.ProjectInvoiceDetail.InvoiceAmount),0) AS Revenue
>> FROM dbo.ProjectInvoiceDetail PID
>> INNER JOIN dbo.ProjectInvoice PI
>> on PID.ProjectInvoiceRowID = PI.RowID
>> WHERE PI.LedgerDocRowID IS NOT NULL
>> AND PI.StatusRowID = 1165
>> AND PID.InvoiceAmount <> 0
>> AND PID.ProjectTranRowID = ProjectTran.RowID
>> )
>> SELECT PT.ProjectRowID,
>> PT.RowID,
>> PT.ProjectWBSRowID,
>> PA.BillingTypeRowID,
>> 'REVENUE',
>> PT.PriorInvoiceAmount-(SELECT Revenue
>> FROM T_REvenue) AS Revenue,
>> PT.PriorInvoiceAmount - (SELECT Revenue
>> FROM T_REvenue) AS BilledAmount
>> FROM ProjectTran PT
>> LEFT OUTER JOIN @.ProjectAccount PA
>> ON PT.ProjectAccountRowID = PA.ProjectAccountRowID
>> INNER JOIN dbo.ProjectDoc PD
>> ON PT.ProjectDocRowID = PD.RowID
>> Where PT.ProjectRowID in (Select RowID
>> From #ProjectTemp)
>> AND PT.ProjectDocRowID IS NOT NULL
>> AND PD.StatusRowID = 1003
>> AND PT.RowID NOT IN (Select TransactionRowID
>> From @.ProjectDataDetail
>> WHERE AccountTypeDescription = 'REVENUE')
>> Take a look at the paper I wrote :
>> http://www.sqlservercentral.com/columnists/fBROUARD/recursivequeriesinsql1999andsqlserver2005.asp
> [snip]
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************
Query Optimizer Question #2
Please consider the following Select statement. I'm not sure how this is
going to end up getting formatted when posted but please notice the two
sub-queries in the main Select statement. They are identical except for the
"AS" clause. I have the following questions:
1) Will the SQL 2000 or 2005 optimizer see these are identical and execute
them only once instead of twice?
2) If the answer to #1 is that they will be executed twice, is there a way
to optimize this so that the two subqueries are only executed once?
SELECT
ProjectTran.ProjectRowID,
ProjectTran.RowID,
ProjectTran.ProjectWBSRowID,
PA.BillingTypeRowID,
'REVENUE',
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,
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 ProjectTran
LEFT OUTER JOIN @.ProjectAccount PA ON
dbo.ProjectTran.ProjectAccountRowID = PA.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')In general, the query optimizer will not search for equivalent subqueries
and remove them if they are written twice in the same query, no. It is
fairly expensive in compilation time to try to do this, and it is so rarely
written that the payoff is very, very low to using this approach vs. the
general case.
You can:
1. Write the subquery once (somehow - there are multiple ways). For
example, I believe you can use nested blocks to write the subquery once,
give the result an alias, and then reference it in a subsequent block twice.
2. Use CROSS APPLY and make it a join, then reference the resulting column
twice. You should take care here to make sure that the join does not change
your output cardinality. This require some expertise, so try (1) unless you
are very comfortable with relational algebra equivalences.
Good luck.
Conor Cunningham
SQL Server Query Optimization Development Lead
"Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
news:e1FxxLzMGHA.140@.TK2MSFTNGP12.phx.gbl...
> Please consider the following Select statement. I'm not sure how this is
> going to end up getting formatted when posted but please notice the two
> sub-queries in the main Select statement. They are identical except for
> the "AS" clause. I have the following questions:
> 1) Will the SQL 2000 or 2005 optimizer see these are identical and execute
> them only once instead of twice?
> 2) If the answer to #1 is that they will be executed twice, is there a way
> to optimize this so that the two subqueries are only executed once?
> SELECT
> ProjectTran.ProjectRowID,
> ProjectTran.RowID,
> ProjectTran.ProjectWBSRowID,
> PA.BillingTypeRowID,
> 'REVENUE',
> 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,
> 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 ProjectTran
> LEFT OUTER JOIN @.ProjectAccount PA ON
> dbo.ProjectTran.ProjectAccountRowID = PA.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')
>|||It is highly unlikely that the subquery will be executed once, because
that is not how query plans work in SQL Server. However, the any I/O
will probably only be done once and retained in the data cache for the
second execution.
I am not sure if it is faster (you would have to test this), but you
could rewrite it as below:
SELECT
ProjectTran.ProjectRowID,
ProjectTran.RowID,
ProjectTran.ProjectWBSRowID,
PA.BillingTypeRowID,
'REVENUE',
ProjectTran.PriorInvoiceAmount - I.SumOfInvoiceAmount AS Revenue,
ProjectTran.PriorInvoiceAmount - I.SumOfInvoiceAmount AS BilledAmount
FROM ProjectTran
LEFT OUTER JOIN @.ProjectAccount PA
ON dbo.ProjectTran.ProjectAccountRowID = PA.ProjectAccountRowID
INNER JOIN dbo.ProjectDoc
ON dbo.ProjectTran.ProjectDocRowID = dbo.ProjectDoc.RowID
LEFT OUTER JOIN (
SELECT dbo.ProjectInvoiceDetail.ProjectTranRowID,
COALESCE(SUM(dbo.ProjectInvoiceDetail.InvoiceAmount),0) AS
SumOfInvoiceAmount
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
GROUP BY dbo.ProjectInvoiceDetail.ProjectTranRowID
) AS I
ON I.ProjectTranRowID = ProjectTran.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'
)
HTH,
Gert-Jan
Amos Soma wrote:
> Please consider the following Select statement. I'm not sure how this is
> going to end up getting formatted when posted but please notice the two
> sub-queries in the main Select statement. They are identical except for th
e
> "AS" clause. I have the following questions:
> 1) Will the SQL 2000 or 2005 optimizer see these are identical and execute
> them only once instead of twice?
> 2) If the answer to #1 is that they will be executed twice, is there a way
> to optimize this so that the two subqueries are only executed once?
> SELECT
> ProjectTran.ProjectRowID,
> ProjectTran.RowID,
> ProjectTran.ProjectWBSRowID,
> PA.BillingTypeRowID,
> 'REVENUE',
> 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,
> 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 ProjectTran
> LEFT OUTER JOIN @.ProjectAccount PA ON
> dbo.ProjectTran.ProjectAccountRowID = PA.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')|||If they are identical and you want to optimize it, take a CTE statement
to pre define the query. The CTE is available since SQL 2005
WITH T_Revenue
AS
(
SELECT COALESCE(SUM(dbo.ProjectInvoiceDetail.InvoiceAmount),0) AS Revenue
FROM dbo.ProjectInvoiceDetail PID
INNER JOIN dbo.ProjectInvoice PI
on PID.ProjectInvoiceRowID = PI.RowID
WHERE PI.LedgerDocRowID IS NOT NULL
AND PI.StatusRowID = 1165
AND PID.InvoiceAmount <> 0
AND PID.ProjectTranRowID = ProjectTran.RowID
)
SELECT PT.ProjectRowID,
PT.RowID,
PT.ProjectWBSRowID,
PA.BillingTypeRowID,
'REVENUE',
PT.PriorInvoiceAmount-(SELECT Revenue
FROM T_REvenue) AS Revenue,
PT.PriorInvoiceAmount - (SELECT Revenue
FROM T_REvenue) AS BilledAmount
FROM ProjectTran PT
LEFT OUTER JOIN @.ProjectAccount PA
ON PT.ProjectAccountRowID = PA.ProjectAccountRowID
INNER JOIN dbo.ProjectDoc PD
ON PT.ProjectDocRowID = PD.RowID
Where PT.ProjectRowID in (Select RowID
From #ProjectTemp)
AND PT.ProjectDocRowID IS NOT NULL
AND PD.StatusRowID = 1003
AND PT.RowID NOT IN (Select TransactionRowID
From @.ProjectDataDetail
WHERE AccountTypeDescription = 'REVENUE')
Take a look at the paper I wrote :
http://www.sqlservercentral.com/col...lserver2005.asp
A +
Amos Soma a crit :
> Please consider the following Select statement. I'm not sure how this is
> going to end up getting formatted when posted but please notice the two
> sub-queries in the main Select statement. They are identical except for th
e
> "AS" clause. I have the following questions:
> 1) Will the SQL 2000 or 2005 optimizer see these are identical and execute
> them only once instead of twice?
> 2) If the answer to #1 is that they will be executed twice, is there a way
> to optimize this so that the two subqueries are only executed once?
> SELECT
> ProjectTran.ProjectRowID,
> ProjectTran.RowID,
> ProjectTran.ProjectWBSRowID,
> PA.BillingTypeRowID,
> 'REVENUE',
> 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,
> 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 ProjectTran
> LEFT OUTER JOIN @.ProjectAccount PA ON
> dbo.ProjectTran.ProjectAccountRowID = PA.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')
>
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||Frdric (is that your name? MVP's do not usually post messages
anonymously),
Have you actually seen performance improvement using a CTE for this type
of problem?
I have tried to write a CTE in a simplified experiment (see below), and
it clearly shows that the CTE query is executed more than once. In the
example below, the first query requires 6 logical reads, the second only
3, which indicates that the CTE is executed twice in the first query.
-- create tables and load a few rows
CREATE TABLE projects
(row_id int not null PRIMARY KEY
,prior_invoice_amount int not null
)
CREATE TABLE project_invoice_details
(row_id int not null REFERENCES projects
,project_invoice_row_id int not null
,invoice_amount int not null
,CONSTRAINT PK_ProjectInvoiceDetails PRIMARY KEY
(row_id,project_invoice_row_id)
)
set nocount on
INSERT INTO projects VALUES (1, 0)
INSERT INTO projects VALUES (2,100)
INSERT INTO projects VALUES (3,200)
INSERT INTO project_invoice_details VALUES (1, 1, 10)
INSERT INTO project_invoice_details VALUES (1, 2, 20)
INSERT INTO project_invoice_details VALUES (1, 3, 30)
INSERT INTO project_invoice_details VALUES (2, 1, 25)
INSERT INTO project_invoice_details VALUES (3, 1, 80)
set nocount off
go
set statistics io on
go
-- query with 2 calls to the CTE
WITH T_revenue AS (
SELECT row_id, COALESCE(SUM(invoice_amount),0) AS revenue
FROM project_invoice_details PID
GROUP BY row_id
)
SELECT P.row_id,
P.prior_invoice_amount - (SELECT revenue
FROM T_revenue
WHERE T_revenue.row_id=P.row_id) AS
revenue,
P.prior_invoice_amount - (SELECT revenue
FROM T_revenue
WHERE T_revenue.row_id=P.row_id) AS
billed_amount
FROM projects P
go
-- query with only 1 call to the CTE
WITH T_revenue AS (
SELECT row_id, COALESCE(SUM(invoice_amount),0) AS revenue
FROM project_invoice_details PID
GROUP BY row_id
)
SELECT P.row_id,
P.prior_invoice_amount - (SELECT revenue
FROM T_revenue
WHERE T_revenue.row_id=P.row_id) AS
revenue,
P.prior_invoice_amount AS billed_amount
FROM projects P
go
set statistics io off
go
DROP TABLE project_invoice_details
DROP TABLE projects
Gert-Jan
P.S. I did not read your article, because the site requires
registration.
"SQLpro [MVP]" wrote:
> If they are identical and you want to optimize it, take a CTE statement
> to pre define the query. The CTE is available since SQL 2005
> WITH T_Revenue
> AS
> (
> SELECT COALESCE(SUM(dbo.ProjectInvoiceDetail.InvoiceAmount),0) AS Revenue
> FROM dbo.ProjectInvoiceDetail PID
> INNER JOIN dbo.ProjectInvoice PI
> on PID.ProjectInvoiceRowID = PI.RowID
> WHERE PI.LedgerDocRowID IS NOT NULL
> AND PI.StatusRowID = 1165
> AND PID.InvoiceAmount <> 0
> AND PID.ProjectTranRowID = ProjectTran.RowID
> )
> SELECT PT.ProjectRowID,
> PT.RowID,
> PT.ProjectWBSRowID,
> PA.BillingTypeRowID,
> 'REVENUE',
> PT.PriorInvoiceAmount-(SELECT Revenue
> FROM T_REvenue) AS Revenue,
> PT.PriorInvoiceAmount - (SELECT Revenue
> FROM T_REvenue) AS BilledAmount
> FROM ProjectTran PT
> LEFT OUTER JOIN @.ProjectAccount PA
> ON PT.ProjectAccountRowID = PA.ProjectAccountRowID
> INNER JOIN dbo.ProjectDoc PD
> ON PT.ProjectDocRowID = PD.RowID
> Where PT.ProjectRowID in (Select RowID
> From #ProjectTemp)
> AND PT.ProjectDocRowID IS NOT NULL
> AND PD.StatusRowID = 1003
> AND PT.RowID NOT IN (Select TransactionRowID
> From @.ProjectDataDetail
> WHERE AccountTypeDescription = 'REVENUE')
> Take a look at the paper I wrote :
> http://www.sqlservercentral.com/col...r />
r2005.asp
[snip]|||I haven't try yet to experiment performances about CTE. I just post a
complete study about CTE and many ways to do with CTE and recursive queries.
The fact I post without my complete name is due to the fact I am
actually in a firm that is badly organize about Internet access and I
cannot have my own newreader with my personnal settings.
Yes I am Fred Brouard.
The ways you does it is interesting.
If I have time I will experiment performances about CTE in the next monthes.
A +
Gert-Jan Strik a crit :
> Frdric (is that your name? MVP's do not usually post messages
> anonymously),
> Have you actually seen performance improvement using a CTE for this type
> of problem?
> I have tried to write a CTE in a simplified experiment (see below), and
> it clearly shows that the CTE query is executed more than once. In the
> example below, the first query requires 6 logical reads, the second only
> 3, which indicates that the CTE is executed twice in the first query.
>
> -- create tables and load a few rows
> CREATE TABLE projects
> (row_id int not null PRIMARY KEY
> ,prior_invoice_amount int not null
> )
> CREATE TABLE project_invoice_details
> (row_id int not null REFERENCES projects
> ,project_invoice_row_id int not null
> ,invoice_amount int not null
> ,CONSTRAINT PK_ProjectInvoiceDetails PRIMARY KEY
> (row_id,project_invoice_row_id)
> )
> set nocount on
> INSERT INTO projects VALUES (1, 0)
> INSERT INTO projects VALUES (2,100)
> INSERT INTO projects VALUES (3,200)
> INSERT INTO project_invoice_details VALUES (1, 1, 10)
> INSERT INTO project_invoice_details VALUES (1, 2, 20)
> INSERT INTO project_invoice_details VALUES (1, 3, 30)
> INSERT INTO project_invoice_details VALUES (2, 1, 25)
> INSERT INTO project_invoice_details VALUES (3, 1, 80)
> set nocount off
> go
> set statistics io on
> go
> -- query with 2 calls to the CTE
> WITH T_revenue AS (
> SELECT row_id, COALESCE(SUM(invoice_amount),0) AS revenue
> FROM project_invoice_details PID
> GROUP BY row_id
> )
> SELECT P.row_id,
> P.prior_invoice_amount - (SELECT revenue
> FROM T_revenue
> WHERE T_revenue.row_id=P.row_id) AS
> revenue,
> P.prior_invoice_amount - (SELECT revenue
> FROM T_revenue
> WHERE T_revenue.row_id=P.row_id) AS
> billed_amount
> FROM projects P
> go
> -- query with only 1 call to the CTE
> WITH T_revenue AS (
> SELECT row_id, COALESCE(SUM(invoice_amount),0) AS revenue
> FROM project_invoice_details PID
> GROUP BY row_id
> )
> SELECT P.row_id,
> P.prior_invoice_amount - (SELECT revenue
> FROM T_revenue
> WHERE T_revenue.row_id=P.row_id) AS
> revenue,
> P.prior_invoice_amount AS billed_amount
> FROM projects P
> go
> set statistics io off
> go
> DROP TABLE project_invoice_details
> DROP TABLE projects
>
> Gert-Jan
> P.S. I did not read your article, because the site requires
> registration.
>
> "SQLpro [MVP]" wrote:
> [snip]
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************
going to end up getting formatted when posted but please notice the two
sub-queries in the main Select statement. They are identical except for the
"AS" clause. I have the following questions:
1) Will the SQL 2000 or 2005 optimizer see these are identical and execute
them only once instead of twice?
2) If the answer to #1 is that they will be executed twice, is there a way
to optimize this so that the two subqueries are only executed once?
SELECT
ProjectTran.ProjectRowID,
ProjectTran.RowID,
ProjectTran.ProjectWBSRowID,
PA.BillingTypeRowID,
'REVENUE',
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,
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 ProjectTran
LEFT OUTER JOIN @.ProjectAccount PA ON
dbo.ProjectTran.ProjectAccountRowID = PA.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')In general, the query optimizer will not search for equivalent subqueries
and remove them if they are written twice in the same query, no. It is
fairly expensive in compilation time to try to do this, and it is so rarely
written that the payoff is very, very low to using this approach vs. the
general case.
You can:
1. Write the subquery once (somehow - there are multiple ways). For
example, I believe you can use nested blocks to write the subquery once,
give the result an alias, and then reference it in a subsequent block twice.
2. Use CROSS APPLY and make it a join, then reference the resulting column
twice. You should take care here to make sure that the join does not change
your output cardinality. This require some expertise, so try (1) unless you
are very comfortable with relational algebra equivalences.
Good luck.
Conor Cunningham
SQL Server Query Optimization Development Lead
"Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
news:e1FxxLzMGHA.140@.TK2MSFTNGP12.phx.gbl...
> Please consider the following Select statement. I'm not sure how this is
> going to end up getting formatted when posted but please notice the two
> sub-queries in the main Select statement. They are identical except for
> the "AS" clause. I have the following questions:
> 1) Will the SQL 2000 or 2005 optimizer see these are identical and execute
> them only once instead of twice?
> 2) If the answer to #1 is that they will be executed twice, is there a way
> to optimize this so that the two subqueries are only executed once?
> SELECT
> ProjectTran.ProjectRowID,
> ProjectTran.RowID,
> ProjectTran.ProjectWBSRowID,
> PA.BillingTypeRowID,
> 'REVENUE',
> 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,
> 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 ProjectTran
> LEFT OUTER JOIN @.ProjectAccount PA ON
> dbo.ProjectTran.ProjectAccountRowID = PA.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')
>|||It is highly unlikely that the subquery will be executed once, because
that is not how query plans work in SQL Server. However, the any I/O
will probably only be done once and retained in the data cache for the
second execution.
I am not sure if it is faster (you would have to test this), but you
could rewrite it as below:
SELECT
ProjectTran.ProjectRowID,
ProjectTran.RowID,
ProjectTran.ProjectWBSRowID,
PA.BillingTypeRowID,
'REVENUE',
ProjectTran.PriorInvoiceAmount - I.SumOfInvoiceAmount AS Revenue,
ProjectTran.PriorInvoiceAmount - I.SumOfInvoiceAmount AS BilledAmount
FROM ProjectTran
LEFT OUTER JOIN @.ProjectAccount PA
ON dbo.ProjectTran.ProjectAccountRowID = PA.ProjectAccountRowID
INNER JOIN dbo.ProjectDoc
ON dbo.ProjectTran.ProjectDocRowID = dbo.ProjectDoc.RowID
LEFT OUTER JOIN (
SELECT dbo.ProjectInvoiceDetail.ProjectTranRowID,
COALESCE(SUM(dbo.ProjectInvoiceDetail.InvoiceAmount),0) AS
SumOfInvoiceAmount
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
GROUP BY dbo.ProjectInvoiceDetail.ProjectTranRowID
) AS I
ON I.ProjectTranRowID = ProjectTran.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'
)
HTH,
Gert-Jan
Amos Soma wrote:
> Please consider the following Select statement. I'm not sure how this is
> going to end up getting formatted when posted but please notice the two
> sub-queries in the main Select statement. They are identical except for th
e
> "AS" clause. I have the following questions:
> 1) Will the SQL 2000 or 2005 optimizer see these are identical and execute
> them only once instead of twice?
> 2) If the answer to #1 is that they will be executed twice, is there a way
> to optimize this so that the two subqueries are only executed once?
> SELECT
> ProjectTran.ProjectRowID,
> ProjectTran.RowID,
> ProjectTran.ProjectWBSRowID,
> PA.BillingTypeRowID,
> 'REVENUE',
> 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,
> 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 ProjectTran
> LEFT OUTER JOIN @.ProjectAccount PA ON
> dbo.ProjectTran.ProjectAccountRowID = PA.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')|||If they are identical and you want to optimize it, take a CTE statement
to pre define the query. The CTE is available since SQL 2005
WITH T_Revenue
AS
(
SELECT COALESCE(SUM(dbo.ProjectInvoiceDetail.InvoiceAmount),0) AS Revenue
FROM dbo.ProjectInvoiceDetail PID
INNER JOIN dbo.ProjectInvoice PI
on PID.ProjectInvoiceRowID = PI.RowID
WHERE PI.LedgerDocRowID IS NOT NULL
AND PI.StatusRowID = 1165
AND PID.InvoiceAmount <> 0
AND PID.ProjectTranRowID = ProjectTran.RowID
)
SELECT PT.ProjectRowID,
PT.RowID,
PT.ProjectWBSRowID,
PA.BillingTypeRowID,
'REVENUE',
PT.PriorInvoiceAmount-(SELECT Revenue
FROM T_REvenue) AS Revenue,
PT.PriorInvoiceAmount - (SELECT Revenue
FROM T_REvenue) AS BilledAmount
FROM ProjectTran PT
LEFT OUTER JOIN @.ProjectAccount PA
ON PT.ProjectAccountRowID = PA.ProjectAccountRowID
INNER JOIN dbo.ProjectDoc PD
ON PT.ProjectDocRowID = PD.RowID
Where PT.ProjectRowID in (Select RowID
From #ProjectTemp)
AND PT.ProjectDocRowID IS NOT NULL
AND PD.StatusRowID = 1003
AND PT.RowID NOT IN (Select TransactionRowID
From @.ProjectDataDetail
WHERE AccountTypeDescription = 'REVENUE')
Take a look at the paper I wrote :
http://www.sqlservercentral.com/col...lserver2005.asp
A +
Amos Soma a crit :
> Please consider the following Select statement. I'm not sure how this is
> going to end up getting formatted when posted but please notice the two
> sub-queries in the main Select statement. They are identical except for th
e
> "AS" clause. I have the following questions:
> 1) Will the SQL 2000 or 2005 optimizer see these are identical and execute
> them only once instead of twice?
> 2) If the answer to #1 is that they will be executed twice, is there a way
> to optimize this so that the two subqueries are only executed once?
> SELECT
> ProjectTran.ProjectRowID,
> ProjectTran.RowID,
> ProjectTran.ProjectWBSRowID,
> PA.BillingTypeRowID,
> 'REVENUE',
> 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,
> 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 ProjectTran
> LEFT OUTER JOIN @.ProjectAccount PA ON
> dbo.ProjectTran.ProjectAccountRowID = PA.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')
>
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||Frdric (is that your name? MVP's do not usually post messages
anonymously),
Have you actually seen performance improvement using a CTE for this type
of problem?
I have tried to write a CTE in a simplified experiment (see below), and
it clearly shows that the CTE query is executed more than once. In the
example below, the first query requires 6 logical reads, the second only
3, which indicates that the CTE is executed twice in the first query.
-- create tables and load a few rows
CREATE TABLE projects
(row_id int not null PRIMARY KEY
,prior_invoice_amount int not null
)
CREATE TABLE project_invoice_details
(row_id int not null REFERENCES projects
,project_invoice_row_id int not null
,invoice_amount int not null
,CONSTRAINT PK_ProjectInvoiceDetails PRIMARY KEY
(row_id,project_invoice_row_id)
)
set nocount on
INSERT INTO projects VALUES (1, 0)
INSERT INTO projects VALUES (2,100)
INSERT INTO projects VALUES (3,200)
INSERT INTO project_invoice_details VALUES (1, 1, 10)
INSERT INTO project_invoice_details VALUES (1, 2, 20)
INSERT INTO project_invoice_details VALUES (1, 3, 30)
INSERT INTO project_invoice_details VALUES (2, 1, 25)
INSERT INTO project_invoice_details VALUES (3, 1, 80)
set nocount off
go
set statistics io on
go
-- query with 2 calls to the CTE
WITH T_revenue AS (
SELECT row_id, COALESCE(SUM(invoice_amount),0) AS revenue
FROM project_invoice_details PID
GROUP BY row_id
)
SELECT P.row_id,
P.prior_invoice_amount - (SELECT revenue
FROM T_revenue
WHERE T_revenue.row_id=P.row_id) AS
revenue,
P.prior_invoice_amount - (SELECT revenue
FROM T_revenue
WHERE T_revenue.row_id=P.row_id) AS
billed_amount
FROM projects P
go
-- query with only 1 call to the CTE
WITH T_revenue AS (
SELECT row_id, COALESCE(SUM(invoice_amount),0) AS revenue
FROM project_invoice_details PID
GROUP BY row_id
)
SELECT P.row_id,
P.prior_invoice_amount - (SELECT revenue
FROM T_revenue
WHERE T_revenue.row_id=P.row_id) AS
revenue,
P.prior_invoice_amount AS billed_amount
FROM projects P
go
set statistics io off
go
DROP TABLE project_invoice_details
DROP TABLE projects
Gert-Jan
P.S. I did not read your article, because the site requires
registration.
"SQLpro [MVP]" wrote:
> If they are identical and you want to optimize it, take a CTE statement
> to pre define the query. The CTE is available since SQL 2005
> WITH T_Revenue
> AS
> (
> SELECT COALESCE(SUM(dbo.ProjectInvoiceDetail.InvoiceAmount),0) AS Revenue
> FROM dbo.ProjectInvoiceDetail PID
> INNER JOIN dbo.ProjectInvoice PI
> on PID.ProjectInvoiceRowID = PI.RowID
> WHERE PI.LedgerDocRowID IS NOT NULL
> AND PI.StatusRowID = 1165
> AND PID.InvoiceAmount <> 0
> AND PID.ProjectTranRowID = ProjectTran.RowID
> )
> SELECT PT.ProjectRowID,
> PT.RowID,
> PT.ProjectWBSRowID,
> PA.BillingTypeRowID,
> 'REVENUE',
> PT.PriorInvoiceAmount-(SELECT Revenue
> FROM T_REvenue) AS Revenue,
> PT.PriorInvoiceAmount - (SELECT Revenue
> FROM T_REvenue) AS BilledAmount
> FROM ProjectTran PT
> LEFT OUTER JOIN @.ProjectAccount PA
> ON PT.ProjectAccountRowID = PA.ProjectAccountRowID
> INNER JOIN dbo.ProjectDoc PD
> ON PT.ProjectDocRowID = PD.RowID
> Where PT.ProjectRowID in (Select RowID
> From #ProjectTemp)
> AND PT.ProjectDocRowID IS NOT NULL
> AND PD.StatusRowID = 1003
> AND PT.RowID NOT IN (Select TransactionRowID
> From @.ProjectDataDetail
> WHERE AccountTypeDescription = 'REVENUE')
> Take a look at the paper I wrote :
> http://www.sqlservercentral.com/col...r />
r2005.asp
[snip]|||I haven't try yet to experiment performances about CTE. I just post a
complete study about CTE and many ways to do with CTE and recursive queries.
The fact I post without my complete name is due to the fact I am
actually in a firm that is badly organize about Internet access and I
cannot have my own newreader with my personnal settings.
Yes I am Fred Brouard.
The ways you does it is interesting.
If I have time I will experiment performances about CTE in the next monthes.
A +
Gert-Jan Strik a crit :
> Frdric (is that your name? MVP's do not usually post messages
> anonymously),
> Have you actually seen performance improvement using a CTE for this type
> of problem?
> I have tried to write a CTE in a simplified experiment (see below), and
> it clearly shows that the CTE query is executed more than once. In the
> example below, the first query requires 6 logical reads, the second only
> 3, which indicates that the CTE is executed twice in the first query.
>
> -- create tables and load a few rows
> CREATE TABLE projects
> (row_id int not null PRIMARY KEY
> ,prior_invoice_amount int not null
> )
> CREATE TABLE project_invoice_details
> (row_id int not null REFERENCES projects
> ,project_invoice_row_id int not null
> ,invoice_amount int not null
> ,CONSTRAINT PK_ProjectInvoiceDetails PRIMARY KEY
> (row_id,project_invoice_row_id)
> )
> set nocount on
> INSERT INTO projects VALUES (1, 0)
> INSERT INTO projects VALUES (2,100)
> INSERT INTO projects VALUES (3,200)
> INSERT INTO project_invoice_details VALUES (1, 1, 10)
> INSERT INTO project_invoice_details VALUES (1, 2, 20)
> INSERT INTO project_invoice_details VALUES (1, 3, 30)
> INSERT INTO project_invoice_details VALUES (2, 1, 25)
> INSERT INTO project_invoice_details VALUES (3, 1, 80)
> set nocount off
> go
> set statistics io on
> go
> -- query with 2 calls to the CTE
> WITH T_revenue AS (
> SELECT row_id, COALESCE(SUM(invoice_amount),0) AS revenue
> FROM project_invoice_details PID
> GROUP BY row_id
> )
> SELECT P.row_id,
> P.prior_invoice_amount - (SELECT revenue
> FROM T_revenue
> WHERE T_revenue.row_id=P.row_id) AS
> revenue,
> P.prior_invoice_amount - (SELECT revenue
> FROM T_revenue
> WHERE T_revenue.row_id=P.row_id) AS
> billed_amount
> FROM projects P
> go
> -- query with only 1 call to the CTE
> WITH T_revenue AS (
> SELECT row_id, COALESCE(SUM(invoice_amount),0) AS revenue
> FROM project_invoice_details PID
> GROUP BY row_id
> )
> SELECT P.row_id,
> P.prior_invoice_amount - (SELECT revenue
> FROM T_revenue
> WHERE T_revenue.row_id=P.row_id) AS
> revenue,
> P.prior_invoice_amount AS billed_amount
> FROM projects P
> go
> set statistics io off
> go
> DROP TABLE project_invoice_details
> DROP TABLE projects
>
> Gert-Jan
> P.S. I did not read your article, because the site requires
> registration.
>
> "SQLpro [MVP]" wrote:
> [snip]
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************
Query Optimizer Question #2
Please consider the following Select statement. I'm not sure how this is
going to end up getting formatted when posted but please notice the two
sub-queries in the main Select statement. They are identical except for the
"AS" clause. I have the following questions:
1) Will the SQL 2000 or 2005 optimizer see these are identical and execute
them only once instead of twice?
2) If the answer to #1 is that they will be executed twice, is there a way
to optimize this so that the two subqueries are only executed once?
SELECT
ProjectTran.ProjectRowID,
ProjectTran.RowID,
ProjectTran.ProjectWBSRowID,
PA.BillingTypeRowID,
'REVENUE',
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,
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 ProjectTran
LEFT OUTER JOIN @.ProjectAccount PA ON
dbo.ProjectTran.ProjectAccountRowID = PA.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')
In general, the query optimizer will not search for equivalent subqueries
and remove them if they are written twice in the same query, no. It is
fairly expensive in compilation time to try to do this, and it is so rarely
written that the payoff is very, very low to using this approach vs. the
general case.
You can:
1. Write the subquery once (somehow - there are multiple ways). For
example, I believe you can use nested blocks to write the subquery once,
give the result an alias, and then reference it in a subsequent block twice.
2. Use CROSS APPLY and make it a join, then reference the resulting column
twice. You should take care here to make sure that the join does not change
your output cardinality. This require some expertise, so try (1) unless you
are very comfortable with relational algebra equivalences.
Good luck.
Conor Cunningham
SQL Server Query Optimization Development Lead
"Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
news:e1FxxLzMGHA.140@.TK2MSFTNGP12.phx.gbl...
> Please consider the following Select statement. I'm not sure how this is
> going to end up getting formatted when posted but please notice the two
> sub-queries in the main Select statement. They are identical except for
> the "AS" clause. I have the following questions:
> 1) Will the SQL 2000 or 2005 optimizer see these are identical and execute
> them only once instead of twice?
> 2) If the answer to #1 is that they will be executed twice, is there a way
> to optimize this so that the two subqueries are only executed once?
> SELECT
> ProjectTran.ProjectRowID,
> ProjectTran.RowID,
> ProjectTran.ProjectWBSRowID,
> PA.BillingTypeRowID,
> 'REVENUE',
> 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,
> 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 ProjectTran
> LEFT OUTER JOIN @.ProjectAccount PA ON
> dbo.ProjectTran.ProjectAccountRowID = PA.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')
>
|||It is highly unlikely that the subquery will be executed once, because
that is not how query plans work in SQL Server. However, the any I/O
will probably only be done once and retained in the data cache for the
second execution.
I am not sure if it is faster (you would have to test this), but you
could rewrite it as below:
SELECT
ProjectTran.ProjectRowID,
ProjectTran.RowID,
ProjectTran.ProjectWBSRowID,
PA.BillingTypeRowID,
'REVENUE',
ProjectTran.PriorInvoiceAmount - I.SumOfInvoiceAmount AS Revenue,
ProjectTran.PriorInvoiceAmount - I.SumOfInvoiceAmount AS BilledAmount
FROM ProjectTran
LEFT OUTER JOIN @.ProjectAccount PA
ON dbo.ProjectTran.ProjectAccountRowID = PA.ProjectAccountRowID
INNER JOIN dbo.ProjectDoc
ON dbo.ProjectTran.ProjectDocRowID = dbo.ProjectDoc.RowID
LEFT OUTER JOIN (
SELECT dbo.ProjectInvoiceDetail.ProjectTranRowID,
COALESCE(SUM(dbo.ProjectInvoiceDetail.InvoiceAmoun t),0) AS
SumOfInvoiceAmount
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
GROUP BY dbo.ProjectInvoiceDetail.ProjectTranRowID
) AS I
ON I.ProjectTranRowID = ProjectTran.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'
)
HTH,
Gert-Jan
Amos Soma wrote:
> Please consider the following Select statement. I'm not sure how this is
> going to end up getting formatted when posted but please notice the two
> sub-queries in the main Select statement. They are identical except for the
> "AS" clause. I have the following questions:
> 1) Will the SQL 2000 or 2005 optimizer see these are identical and execute
> them only once instead of twice?
> 2) If the answer to #1 is that they will be executed twice, is there a way
> to optimize this so that the two subqueries are only executed once?
> SELECT
> ProjectTran.ProjectRowID,
> ProjectTran.RowID,
> ProjectTran.ProjectWBSRowID,
> PA.BillingTypeRowID,
> 'REVENUE',
> 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,
> 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 ProjectTran
> LEFT OUTER JOIN @.ProjectAccount PA ON
> dbo.ProjectTran.ProjectAccountRowID = PA.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')
|||If they are identical and you want to optimize it, take a CTE statement
to pre define the query. The CTE is available since SQL 2005
WITH T_Revenue
AS
(
SELECT COALESCE(SUM(dbo.ProjectInvoiceDetail.InvoiceAmoun t),0) AS Revenue
FROM dbo.ProjectInvoiceDetail PID
INNER JOIN dbo.ProjectInvoice PI
on PID.ProjectInvoiceRowID = PI.RowID
WHERE PI.LedgerDocRowID IS NOT NULL
AND PI.StatusRowID = 1165
AND PID.InvoiceAmount <> 0
AND PID.ProjectTranRowID = ProjectTran.RowID
)
SELECT PT.ProjectRowID,
PT.RowID,
PT.ProjectWBSRowID,
PA.BillingTypeRowID,
'REVENUE',
PT.PriorInvoiceAmount-(SELECT Revenue
FROM T_REvenue) AS Revenue,
PT.PriorInvoiceAmount - (SELECT Revenue
FROM T_REvenue) AS BilledAmount
FROM ProjectTran PT
LEFT OUTER JOIN @.ProjectAccount PA
ON PT.ProjectAccountRowID = PA.ProjectAccountRowID
INNER JOIN dbo.ProjectDoc PD
ON PT.ProjectDocRowID = PD.RowID
Where PT.ProjectRowID in (Select RowID
From #ProjectTemp)
AND PT.ProjectDocRowID IS NOT NULL
AND PD.StatusRowID = 1003
AND PT.RowID NOT IN (Select TransactionRowID
From @.ProjectDataDetail
WHERE AccountTypeDescription = 'REVENUE')
Take a look at the paper I wrote :
http://www.sqlservercentral.com/colu...server2005.asp
A +
Amos Soma a crit :
> Please consider the following Select statement. I'm not sure how this is
> going to end up getting formatted when posted but please notice the two
> sub-queries in the main Select statement. They are identical except for the
> "AS" clause. I have the following questions:
> 1) Will the SQL 2000 or 2005 optimizer see these are identical and execute
> them only once instead of twice?
> 2) If the answer to #1 is that they will be executed twice, is there a way
> to optimize this so that the two subqueries are only executed once?
> SELECT
> ProjectTran.ProjectRowID,
> ProjectTran.RowID,
> ProjectTran.ProjectWBSRowID,
> PA.BillingTypeRowID,
> 'REVENUE',
> 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,
> 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 ProjectTran
> LEFT OUTER JOIN @.ProjectAccount PA ON
> dbo.ProjectTran.ProjectAccountRowID = PA.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')
>
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************
|||Frdric (is that your name? MVP's do not usually post messages
anonymously),
Have you actually seen performance improvement using a CTE for this type
of problem?
I have tried to write a CTE in a simplified experiment (see below), and
it clearly shows that the CTE query is executed more than once. In the
example below, the first query requires 6 logical reads, the second only
3, which indicates that the CTE is executed twice in the first query.
-- create tables and load a few rows
CREATE TABLE projects
(row_id int not null PRIMARY KEY
,prior_invoice_amount int not null
)
CREATE TABLE project_invoice_details
(row_id int not null REFERENCES projects
,project_invoice_row_id int not null
,invoice_amount int not null
,CONSTRAINT PK_ProjectInvoiceDetails PRIMARY KEY
(row_id,project_invoice_row_id)
)
set nocount on
INSERT INTO projects VALUES (1, 0)
INSERT INTO projects VALUES (2,100)
INSERT INTO projects VALUES (3,200)
INSERT INTO project_invoice_details VALUES (1, 1, 10)
INSERT INTO project_invoice_details VALUES (1, 2, 20)
INSERT INTO project_invoice_details VALUES (1, 3, 30)
INSERT INTO project_invoice_details VALUES (2, 1, 25)
INSERT INTO project_invoice_details VALUES (3, 1, 80)
set nocount off
go
set statistics io on
go
-- query with 2 calls to the CTE
WITH T_revenue AS (
SELECT row_id, COALESCE(SUM(invoice_amount),0) AS revenue
FROM project_invoice_details PID
GROUP BY row_id
)
SELECT P.row_id,
P.prior_invoice_amount - (SELECT revenue
FROM T_revenue
WHERE T_revenue.row_id=P.row_id) AS
revenue,
P.prior_invoice_amount - (SELECT revenue
FROM T_revenue
WHERE T_revenue.row_id=P.row_id) AS
billed_amount
FROM projects P
go
-- query with only 1 call to the CTE
WITH T_revenue AS (
SELECT row_id, COALESCE(SUM(invoice_amount),0) AS revenue
FROM project_invoice_details PID
GROUP BY row_id
)
SELECT P.row_id,
P.prior_invoice_amount - (SELECT revenue
FROM T_revenue
WHERE T_revenue.row_id=P.row_id) AS
revenue,
P.prior_invoice_amount AS billed_amount
FROM projects P
go
set statistics io off
go
DROP TABLE project_invoice_details
DROP TABLE projects
Gert-Jan
P.S. I did not read your article, because the site requires
registration.
"SQLpro [MVP]" wrote:
> If they are identical and you want to optimize it, take a CTE statement
> to pre define the query. The CTE is available since SQL 2005
> WITH T_Revenue
> AS
> (
> SELECT COALESCE(SUM(dbo.ProjectInvoiceDetail.InvoiceAmoun t),0) AS Revenue
> FROM dbo.ProjectInvoiceDetail PID
> INNER JOIN dbo.ProjectInvoice PI
> on PID.ProjectInvoiceRowID = PI.RowID
> WHERE PI.LedgerDocRowID IS NOT NULL
> AND PI.StatusRowID = 1165
> AND PID.InvoiceAmount <> 0
> AND PID.ProjectTranRowID = ProjectTran.RowID
> )
> SELECT PT.ProjectRowID,
> PT.RowID,
> PT.ProjectWBSRowID,
> PA.BillingTypeRowID,
> 'REVENUE',
> PT.PriorInvoiceAmount-(SELECT Revenue
> FROM T_REvenue) AS Revenue,
> PT.PriorInvoiceAmount - (SELECT Revenue
> FROM T_REvenue) AS BilledAmount
> FROM ProjectTran PT
> LEFT OUTER JOIN @.ProjectAccount PA
> ON PT.ProjectAccountRowID = PA.ProjectAccountRowID
> INNER JOIN dbo.ProjectDoc PD
> ON PT.ProjectDocRowID = PD.RowID
> Where PT.ProjectRowID in (Select RowID
> From #ProjectTemp)
> AND PT.ProjectDocRowID IS NOT NULL
> AND PD.StatusRowID = 1003
> AND PT.RowID NOT IN (Select TransactionRowID
> From @.ProjectDataDetail
> WHERE AccountTypeDescription = 'REVENUE')
> Take a look at the paper I wrote :
> http://www.sqlservercentral.com/colu...server2005.asp
[snip]
|||I haven't try yet to experiment performances about CTE. I just post a
complete study about CTE and many ways to do with CTE and recursive queries.
The fact I post without my complete name is due to the fact I am
actually in a firm that is badly organize about Internet access and I
cannot have my own newreader with my personnal settings.
Yes I am Fred Brouard.
The ways you does it is interesting.
If I have time I will experiment performances about CTE in the next monthes.
A +
Gert-Jan Strik a crit :
> Frdric (is that your name? MVP's do not usually post messages
> anonymously),
> Have you actually seen performance improvement using a CTE for this type
> of problem?
> I have tried to write a CTE in a simplified experiment (see below), and
> it clearly shows that the CTE query is executed more than once. In the
> example below, the first query requires 6 logical reads, the second only
> 3, which indicates that the CTE is executed twice in the first query.
>
> -- create tables and load a few rows
> CREATE TABLE projects
> (row_id int not null PRIMARY KEY
> ,prior_invoice_amount int not null
> )
> CREATE TABLE project_invoice_details
> (row_id int not null REFERENCES projects
> ,project_invoice_row_id int not null
> ,invoice_amount int not null
> ,CONSTRAINT PK_ProjectInvoiceDetails PRIMARY KEY
> (row_id,project_invoice_row_id)
> )
> set nocount on
> INSERT INTO projects VALUES (1, 0)
> INSERT INTO projects VALUES (2,100)
> INSERT INTO projects VALUES (3,200)
> INSERT INTO project_invoice_details VALUES (1, 1, 10)
> INSERT INTO project_invoice_details VALUES (1, 2, 20)
> INSERT INTO project_invoice_details VALUES (1, 3, 30)
> INSERT INTO project_invoice_details VALUES (2, 1, 25)
> INSERT INTO project_invoice_details VALUES (3, 1, 80)
> set nocount off
> go
> set statistics io on
> go
> -- query with 2 calls to the CTE
> WITH T_revenue AS (
> SELECT row_id, COALESCE(SUM(invoice_amount),0) AS revenue
> FROM project_invoice_details PID
> GROUP BY row_id
> )
> SELECT P.row_id,
> P.prior_invoice_amount - (SELECT revenue
> FROM T_revenue
> WHERE T_revenue.row_id=P.row_id) AS
> revenue,
> P.prior_invoice_amount - (SELECT revenue
> FROM T_revenue
> WHERE T_revenue.row_id=P.row_id) AS
> billed_amount
> FROM projects P
> go
> -- query with only 1 call to the CTE
> WITH T_revenue AS (
> SELECT row_id, COALESCE(SUM(invoice_amount),0) AS revenue
> FROM project_invoice_details PID
> GROUP BY row_id
> )
> SELECT P.row_id,
> P.prior_invoice_amount - (SELECT revenue
> FROM T_revenue
> WHERE T_revenue.row_id=P.row_id) AS
> revenue,
> P.prior_invoice_amount AS billed_amount
> FROM projects P
> go
> set statistics io off
> go
> DROP TABLE project_invoice_details
> DROP TABLE projects
>
> Gert-Jan
> P.S. I did not read your article, because the site requires
> registration.
>
> "SQLpro [MVP]" wrote:
> [snip]
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************
going to end up getting formatted when posted but please notice the two
sub-queries in the main Select statement. They are identical except for the
"AS" clause. I have the following questions:
1) Will the SQL 2000 or 2005 optimizer see these are identical and execute
them only once instead of twice?
2) If the answer to #1 is that they will be executed twice, is there a way
to optimize this so that the two subqueries are only executed once?
SELECT
ProjectTran.ProjectRowID,
ProjectTran.RowID,
ProjectTran.ProjectWBSRowID,
PA.BillingTypeRowID,
'REVENUE',
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,
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 ProjectTran
LEFT OUTER JOIN @.ProjectAccount PA ON
dbo.ProjectTran.ProjectAccountRowID = PA.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')
In general, the query optimizer will not search for equivalent subqueries
and remove them if they are written twice in the same query, no. It is
fairly expensive in compilation time to try to do this, and it is so rarely
written that the payoff is very, very low to using this approach vs. the
general case.
You can:
1. Write the subquery once (somehow - there are multiple ways). For
example, I believe you can use nested blocks to write the subquery once,
give the result an alias, and then reference it in a subsequent block twice.
2. Use CROSS APPLY and make it a join, then reference the resulting column
twice. You should take care here to make sure that the join does not change
your output cardinality. This require some expertise, so try (1) unless you
are very comfortable with relational algebra equivalences.
Good luck.
Conor Cunningham
SQL Server Query Optimization Development Lead
"Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
news:e1FxxLzMGHA.140@.TK2MSFTNGP12.phx.gbl...
> Please consider the following Select statement. I'm not sure how this is
> going to end up getting formatted when posted but please notice the two
> sub-queries in the main Select statement. They are identical except for
> the "AS" clause. I have the following questions:
> 1) Will the SQL 2000 or 2005 optimizer see these are identical and execute
> them only once instead of twice?
> 2) If the answer to #1 is that they will be executed twice, is there a way
> to optimize this so that the two subqueries are only executed once?
> SELECT
> ProjectTran.ProjectRowID,
> ProjectTran.RowID,
> ProjectTran.ProjectWBSRowID,
> PA.BillingTypeRowID,
> 'REVENUE',
> 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,
> 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 ProjectTran
> LEFT OUTER JOIN @.ProjectAccount PA ON
> dbo.ProjectTran.ProjectAccountRowID = PA.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')
>
|||It is highly unlikely that the subquery will be executed once, because
that is not how query plans work in SQL Server. However, the any I/O
will probably only be done once and retained in the data cache for the
second execution.
I am not sure if it is faster (you would have to test this), but you
could rewrite it as below:
SELECT
ProjectTran.ProjectRowID,
ProjectTran.RowID,
ProjectTran.ProjectWBSRowID,
PA.BillingTypeRowID,
'REVENUE',
ProjectTran.PriorInvoiceAmount - I.SumOfInvoiceAmount AS Revenue,
ProjectTran.PriorInvoiceAmount - I.SumOfInvoiceAmount AS BilledAmount
FROM ProjectTran
LEFT OUTER JOIN @.ProjectAccount PA
ON dbo.ProjectTran.ProjectAccountRowID = PA.ProjectAccountRowID
INNER JOIN dbo.ProjectDoc
ON dbo.ProjectTran.ProjectDocRowID = dbo.ProjectDoc.RowID
LEFT OUTER JOIN (
SELECT dbo.ProjectInvoiceDetail.ProjectTranRowID,
COALESCE(SUM(dbo.ProjectInvoiceDetail.InvoiceAmoun t),0) AS
SumOfInvoiceAmount
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
GROUP BY dbo.ProjectInvoiceDetail.ProjectTranRowID
) AS I
ON I.ProjectTranRowID = ProjectTran.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'
)
HTH,
Gert-Jan
Amos Soma wrote:
> Please consider the following Select statement. I'm not sure how this is
> going to end up getting formatted when posted but please notice the two
> sub-queries in the main Select statement. They are identical except for the
> "AS" clause. I have the following questions:
> 1) Will the SQL 2000 or 2005 optimizer see these are identical and execute
> them only once instead of twice?
> 2) If the answer to #1 is that they will be executed twice, is there a way
> to optimize this so that the two subqueries are only executed once?
> SELECT
> ProjectTran.ProjectRowID,
> ProjectTran.RowID,
> ProjectTran.ProjectWBSRowID,
> PA.BillingTypeRowID,
> 'REVENUE',
> 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,
> 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 ProjectTran
> LEFT OUTER JOIN @.ProjectAccount PA ON
> dbo.ProjectTran.ProjectAccountRowID = PA.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')
|||If they are identical and you want to optimize it, take a CTE statement
to pre define the query. The CTE is available since SQL 2005
WITH T_Revenue
AS
(
SELECT COALESCE(SUM(dbo.ProjectInvoiceDetail.InvoiceAmoun t),0) AS Revenue
FROM dbo.ProjectInvoiceDetail PID
INNER JOIN dbo.ProjectInvoice PI
on PID.ProjectInvoiceRowID = PI.RowID
WHERE PI.LedgerDocRowID IS NOT NULL
AND PI.StatusRowID = 1165
AND PID.InvoiceAmount <> 0
AND PID.ProjectTranRowID = ProjectTran.RowID
)
SELECT PT.ProjectRowID,
PT.RowID,
PT.ProjectWBSRowID,
PA.BillingTypeRowID,
'REVENUE',
PT.PriorInvoiceAmount-(SELECT Revenue
FROM T_REvenue) AS Revenue,
PT.PriorInvoiceAmount - (SELECT Revenue
FROM T_REvenue) AS BilledAmount
FROM ProjectTran PT
LEFT OUTER JOIN @.ProjectAccount PA
ON PT.ProjectAccountRowID = PA.ProjectAccountRowID
INNER JOIN dbo.ProjectDoc PD
ON PT.ProjectDocRowID = PD.RowID
Where PT.ProjectRowID in (Select RowID
From #ProjectTemp)
AND PT.ProjectDocRowID IS NOT NULL
AND PD.StatusRowID = 1003
AND PT.RowID NOT IN (Select TransactionRowID
From @.ProjectDataDetail
WHERE AccountTypeDescription = 'REVENUE')
Take a look at the paper I wrote :
http://www.sqlservercentral.com/colu...server2005.asp
A +
Amos Soma a crit :
> Please consider the following Select statement. I'm not sure how this is
> going to end up getting formatted when posted but please notice the two
> sub-queries in the main Select statement. They are identical except for the
> "AS" clause. I have the following questions:
> 1) Will the SQL 2000 or 2005 optimizer see these are identical and execute
> them only once instead of twice?
> 2) If the answer to #1 is that they will be executed twice, is there a way
> to optimize this so that the two subqueries are only executed once?
> SELECT
> ProjectTran.ProjectRowID,
> ProjectTran.RowID,
> ProjectTran.ProjectWBSRowID,
> PA.BillingTypeRowID,
> 'REVENUE',
> 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,
> 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 ProjectTran
> LEFT OUTER JOIN @.ProjectAccount PA ON
> dbo.ProjectTran.ProjectAccountRowID = PA.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')
>
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************
|||Frdric (is that your name? MVP's do not usually post messages
anonymously),
Have you actually seen performance improvement using a CTE for this type
of problem?
I have tried to write a CTE in a simplified experiment (see below), and
it clearly shows that the CTE query is executed more than once. In the
example below, the first query requires 6 logical reads, the second only
3, which indicates that the CTE is executed twice in the first query.
-- create tables and load a few rows
CREATE TABLE projects
(row_id int not null PRIMARY KEY
,prior_invoice_amount int not null
)
CREATE TABLE project_invoice_details
(row_id int not null REFERENCES projects
,project_invoice_row_id int not null
,invoice_amount int not null
,CONSTRAINT PK_ProjectInvoiceDetails PRIMARY KEY
(row_id,project_invoice_row_id)
)
set nocount on
INSERT INTO projects VALUES (1, 0)
INSERT INTO projects VALUES (2,100)
INSERT INTO projects VALUES (3,200)
INSERT INTO project_invoice_details VALUES (1, 1, 10)
INSERT INTO project_invoice_details VALUES (1, 2, 20)
INSERT INTO project_invoice_details VALUES (1, 3, 30)
INSERT INTO project_invoice_details VALUES (2, 1, 25)
INSERT INTO project_invoice_details VALUES (3, 1, 80)
set nocount off
go
set statistics io on
go
-- query with 2 calls to the CTE
WITH T_revenue AS (
SELECT row_id, COALESCE(SUM(invoice_amount),0) AS revenue
FROM project_invoice_details PID
GROUP BY row_id
)
SELECT P.row_id,
P.prior_invoice_amount - (SELECT revenue
FROM T_revenue
WHERE T_revenue.row_id=P.row_id) AS
revenue,
P.prior_invoice_amount - (SELECT revenue
FROM T_revenue
WHERE T_revenue.row_id=P.row_id) AS
billed_amount
FROM projects P
go
-- query with only 1 call to the CTE
WITH T_revenue AS (
SELECT row_id, COALESCE(SUM(invoice_amount),0) AS revenue
FROM project_invoice_details PID
GROUP BY row_id
)
SELECT P.row_id,
P.prior_invoice_amount - (SELECT revenue
FROM T_revenue
WHERE T_revenue.row_id=P.row_id) AS
revenue,
P.prior_invoice_amount AS billed_amount
FROM projects P
go
set statistics io off
go
DROP TABLE project_invoice_details
DROP TABLE projects
Gert-Jan
P.S. I did not read your article, because the site requires
registration.
"SQLpro [MVP]" wrote:
> If they are identical and you want to optimize it, take a CTE statement
> to pre define the query. The CTE is available since SQL 2005
> WITH T_Revenue
> AS
> (
> SELECT COALESCE(SUM(dbo.ProjectInvoiceDetail.InvoiceAmoun t),0) AS Revenue
> FROM dbo.ProjectInvoiceDetail PID
> INNER JOIN dbo.ProjectInvoice PI
> on PID.ProjectInvoiceRowID = PI.RowID
> WHERE PI.LedgerDocRowID IS NOT NULL
> AND PI.StatusRowID = 1165
> AND PID.InvoiceAmount <> 0
> AND PID.ProjectTranRowID = ProjectTran.RowID
> )
> SELECT PT.ProjectRowID,
> PT.RowID,
> PT.ProjectWBSRowID,
> PA.BillingTypeRowID,
> 'REVENUE',
> PT.PriorInvoiceAmount-(SELECT Revenue
> FROM T_REvenue) AS Revenue,
> PT.PriorInvoiceAmount - (SELECT Revenue
> FROM T_REvenue) AS BilledAmount
> FROM ProjectTran PT
> LEFT OUTER JOIN @.ProjectAccount PA
> ON PT.ProjectAccountRowID = PA.ProjectAccountRowID
> INNER JOIN dbo.ProjectDoc PD
> ON PT.ProjectDocRowID = PD.RowID
> Where PT.ProjectRowID in (Select RowID
> From #ProjectTemp)
> AND PT.ProjectDocRowID IS NOT NULL
> AND PD.StatusRowID = 1003
> AND PT.RowID NOT IN (Select TransactionRowID
> From @.ProjectDataDetail
> WHERE AccountTypeDescription = 'REVENUE')
> Take a look at the paper I wrote :
> http://www.sqlservercentral.com/colu...server2005.asp
[snip]
|||I haven't try yet to experiment performances about CTE. I just post a
complete study about CTE and many ways to do with CTE and recursive queries.
The fact I post without my complete name is due to the fact I am
actually in a firm that is badly organize about Internet access and I
cannot have my own newreader with my personnal settings.
Yes I am Fred Brouard.
The ways you does it is interesting.
If I have time I will experiment performances about CTE in the next monthes.
A +
Gert-Jan Strik a crit :
> Frdric (is that your name? MVP's do not usually post messages
> anonymously),
> Have you actually seen performance improvement using a CTE for this type
> of problem?
> I have tried to write a CTE in a simplified experiment (see below), and
> it clearly shows that the CTE query is executed more than once. In the
> example below, the first query requires 6 logical reads, the second only
> 3, which indicates that the CTE is executed twice in the first query.
>
> -- create tables and load a few rows
> CREATE TABLE projects
> (row_id int not null PRIMARY KEY
> ,prior_invoice_amount int not null
> )
> CREATE TABLE project_invoice_details
> (row_id int not null REFERENCES projects
> ,project_invoice_row_id int not null
> ,invoice_amount int not null
> ,CONSTRAINT PK_ProjectInvoiceDetails PRIMARY KEY
> (row_id,project_invoice_row_id)
> )
> set nocount on
> INSERT INTO projects VALUES (1, 0)
> INSERT INTO projects VALUES (2,100)
> INSERT INTO projects VALUES (3,200)
> INSERT INTO project_invoice_details VALUES (1, 1, 10)
> INSERT INTO project_invoice_details VALUES (1, 2, 20)
> INSERT INTO project_invoice_details VALUES (1, 3, 30)
> INSERT INTO project_invoice_details VALUES (2, 1, 25)
> INSERT INTO project_invoice_details VALUES (3, 1, 80)
> set nocount off
> go
> set statistics io on
> go
> -- query with 2 calls to the CTE
> WITH T_revenue AS (
> SELECT row_id, COALESCE(SUM(invoice_amount),0) AS revenue
> FROM project_invoice_details PID
> GROUP BY row_id
> )
> SELECT P.row_id,
> P.prior_invoice_amount - (SELECT revenue
> FROM T_revenue
> WHERE T_revenue.row_id=P.row_id) AS
> revenue,
> P.prior_invoice_amount - (SELECT revenue
> FROM T_revenue
> WHERE T_revenue.row_id=P.row_id) AS
> billed_amount
> FROM projects P
> go
> -- query with only 1 call to the CTE
> WITH T_revenue AS (
> SELECT row_id, COALESCE(SUM(invoice_amount),0) AS revenue
> FROM project_invoice_details PID
> GROUP BY row_id
> )
> SELECT P.row_id,
> P.prior_invoice_amount - (SELECT revenue
> FROM T_revenue
> WHERE T_revenue.row_id=P.row_id) AS
> revenue,
> P.prior_invoice_amount AS billed_amount
> FROM projects P
> go
> set statistics io off
> go
> DROP TABLE project_invoice_details
> DROP TABLE projects
>
> Gert-Jan
> P.S. I did not read your article, because the site requires
> registration.
>
> "SQLpro [MVP]" wrote:
> [snip]
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************
Subscribe to:
Posts (Atom)