Query xml field in Query Plan DMV

I anot expertin XML and need to query a value in the field "query_plan" of
the "sys.dm_exec_query_plan" DMV. The value I is the whether queries has
Parallelism value more than 0. I remeber have seen something like
sys.dm_exec_query_plan.query_plan.value ('declare namespace.......
Is there in BOL a chapter on how querying special SQL xml schemas ?
Thanks in advanceHello eliassal,
You could query the XML column in the DMV as any other XML column in SQL by
using the XQuery.
For example, I use this Statement to query the query_Plan:
select query_plan.query('declare namespace
showplan="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
/showplan:ShowPlanXML/showplan:BatchSequence/showplan:Batch') from
sys.dm_exec_query_plan(<my plan handle id> )
For more detailed information about how to query the XML date using XQuery,
please refer this article:
XQuery Against the xml Data Type
http://msdn2.microsoft.com/en-us/library/ms189075.aspx
For more detailed information about the schema of the query_plan, pleaser
refer this article:
sys.dm_exec_query_plan
http://msdn2.microsoft.com/en-us/ms189747.aspx
http://schemas.microsoft.com/sqlserver/
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==========
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||So many thanks, I tried so many times, so many syntaxes to work with the
"RelOp" element without success
In the sql schemq it is defined as follows :
<xsd:element name="RelOp" type="shp:RelOpType" />
<xsd:element name="ParameterList" type="shp:ColumnReferenceListType"
minOccurs="0" maxOccurs="1" />
As I said yesterday, I understood in a sql article that if the
max(...RelOp/@.parelle.....) > 0 gives us an idea if the query would be
paralleize.
I am deserate!!!HELP :-)
Thanks again
"Wei Lu [MSFT]" wrote:

> Hello eliassal,
> You could query the XML column in the DMV as any other XML column in SQL b
y
> using the XQuery.
> For example, I use this Statement to query the query_Plan:
> select query_plan.query('declare namespace
> showplan="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
> /showplan:ShowPlanXML/showplan:BatchSequence/showplan:Batch') from
> sys.dm_exec_query_plan(<my plan handle id> )
> For more detailed information about how to query the XML date using XQuery
,
> please refer this article:
> XQuery Against the xml Data Type
> http://msdn2.microsoft.com/en-us/library/ms189075.aspx
> For more detailed information about the schema of the query_plan, pleaser
> refer this article:
> sys.dm_exec_query_plan
> http://msdn2.microsoft.com/en-us/ms189747.aspx
> http://schemas.microsoft.com/sqlserver/
> Hope this will be helpful!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ========================================
==========
> Get notification to my posts through email? Please refer to
> l]
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> [url]http://msdn.microsoft.com/subscriptions/support/default.aspx." target="_blank">http://msdn.microsoft.com/subscript...t/default.aspx.
> ========================================
==========
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>
>|||Hello Eliassal,
I found that article. The statement should be:
select
p.*,
q.*,
cp.plan_handle
from
sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) p
cross apply sys.dm_exec_sql_text(cp.plan_handle) as q
where
cp.cacheobjtype = 'Compiled Plan' and
p.query_plan.value('declare namespace
p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
max(//p:RelOp/@.Parallel)', 'float') > 0
Troubleshooting Performance Problems in SQL Server 2005
https://www.microsoft.com.nsatc.net...005/tsprfprb.ms
px
Is this article you want?
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==========
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||LOVELY, so many thanks
"Wei Lu [MSFT]" wrote:

> Hello Eliassal,
> I found that article. The statement should be:
> select
> p.*,
> q.*,
> cp.plan_handle
> from
> sys.dm_exec_cached_plans cp
> cross apply sys.dm_exec_query_plan(cp.plan_handle) p
> cross apply sys.dm_exec_sql_text(cp.plan_handle) as q
> where
> cp.cacheobjtype = 'Compiled Plan' and
> p.query_plan.value('declare namespace
> p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
> max(//p:RelOp/@.Parallel)', 'float') > 0
>
> Troubleshooting Performance Problems in SQL Server 2005
> l]
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> [url]http://msdn.microsoft.com/subscriptions/support/default.aspx." target="_blank">https://www.microsoft.com.nsatc.net...t/default.aspx.
> ========================================
==========
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Lines: 33
X-Tomcat-ID: 34576541
MIME-Version: 1.0
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Organization: Microsoft
X-Tomcat-NG: microsoft.public.sqlserver.xml
NNTP-Posting-Host: tomcatimport2.phx.gbl 10.201.218.182
Xref: leafnode.mcse.ms microsoft.public.sqlserver.xml:1829
Hello Eliassal,
My pleasure. If you have any question, please feel free to let me know.
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==========
(This posting is provided "AS IS", with no warranties, and confers no
rights.)sql

Query xml field in Query Plan DMV

I anot expertin XML and need to query a value in the field "query_plan" of
the "sys.dm_exec_query_plan" DMV. The value I is the whether queries has
Parallelism value more than 0. I remeber have seen something like
sys.dm_exec_query_plan.query_plan.value ('declare namespace.......
Is there in BOL a chapter on how querying special SQL xml schemas ?
Thanks in advance
Hello eliassal,
You could query the XML column in the DMV as any other XML column in SQL by
using the XQuery.
For example, I use this Statement to query the query_Plan:
select query_plan.query('declare namespace
showplan="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
/showplan:ShowPlanXML/showplan:BatchSequence/showplan:Batch') from
sys.dm_exec_query_plan(<my plan handle id>)
For more detailed information about how to query the XML date using XQuery,
please refer this article:
XQuery Against the xml Data Type
http://msdn2.microsoft.com/en-us/library/ms189075.aspx
For more detailed information about the schema of the query_plan, pleaser
refer this article:
sys.dm_exec_query_plan
http://msdn2.microsoft.com/en-us/ms189747.aspx
http://schemas.microsoft.com/sqlserver/
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
|||So many thanks, I tried so many times, so many syntaxes to work with the
"RelOp" element without success
In the sql schemq it is defined as follows :
<xsd:element name="RelOp" type="shp:RelOpType" />
<xsd:element name="ParameterList" type="shp:ColumnReferenceListType"
minOccurs="0" maxOccurs="1" />
As I said yesterday, I understood in a sql article that if the
max(...RelOp/@.parelle.....) > 0 gives us an idea if the query would be
paralleize.
I am deserate!!!HELP :-)
Thanks again
"Wei Lu [MSFT]" wrote:

> Hello eliassal,
> You could query the XML column in the DMV as any other XML column in SQL by
> using the XQuery.
> For example, I use this Statement to query the query_Plan:
> select query_plan.query('declare namespace
> showplan="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
> /showplan:ShowPlanXML/showplan:BatchSequence/showplan:Batch') from
> sys.dm_exec_query_plan(<my plan handle id>)
> For more detailed information about how to query the XML date using XQuery,
> please refer this article:
> XQuery Against the xml Data Type
> http://msdn2.microsoft.com/en-us/library/ms189075.aspx
> For more detailed information about the schema of the query_plan, pleaser
> refer this article:
> sys.dm_exec_query_plan
> http://msdn2.microsoft.com/en-us/ms189747.aspx
> http://schemas.microsoft.com/sqlserver/
> Hope this will be helpful!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>
>
|||Hello Eliassal,
I found that article. The statement should be:
select
p.*,
q.*,
cp.plan_handle
from
sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) p
cross apply sys.dm_exec_sql_text(cp.plan_handle) as q
where
cp.cacheobjtype = 'Compiled Plan' and
p.query_plan.value('declare namespace
p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
max(//p:RelOp/@.Parallel)', 'float') > 0
Troubleshooting Performance Problems in SQL Server 2005
https://www.microsoft.com.nsatc.net/technet/prodtechnol/sql/2005/tsprfprb.ms
px
Is this article you want?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||LOVELY, so many thanks
"Wei Lu [MSFT]" wrote:

> Hello Eliassal,
> I found that article. The statement should be:
> select
> p.*,
> q.*,
> cp.plan_handle
> from
> sys.dm_exec_cached_plans cp
> cross apply sys.dm_exec_query_plan(cp.plan_handle) p
> cross apply sys.dm_exec_sql_text(cp.plan_handle) as q
> where
> cp.cacheobjtype = 'Compiled Plan' and
> p.query_plan.value('declare namespace
> p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
> max(//p:RelOp/@.Parallel)', 'float') > 0
>
> Troubleshooting Performance Problems in SQL Server 2005
> https://www.microsoft.com.nsatc.net/technet/prodtechnol/sql/2005/tsprfprb.ms
> px
> Is this article you want?
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>
|||Hello Eliassal,
My pleasure. If you have any question, please feel free to let me know.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

Query works in 2000 but not 2005

I noticed that some queries work in 2000, but not in 2005, for example
SELECT columnA,
columnB,
case when columnC = 'BLAH' then dbo.fn_func(columnD)
sum(columnE)
FROM TableA
GROUP BY
columnA,
columnB,
case when columnC = 'BLAH' then dbo.fn_func(columnD)
Works for 2K, but for 2K5 i get an error message saying "columnD is
invalid in the select list because it is not contained...etc etc"
ANyone know of the change?
It shouldn't work on SQL2000 either. Your CASE expression is not complete and
there should be a comma before sum(columnE).
The following should work on both versions:
SELECT columnA,
columnB,
case when columnC = 'BLAH' then dbo.fn_func(columnD) end,
sum(columnE)
FROM TableA
GROUP BY
columnA,
columnB,
case when columnC = 'BLAH' then dbo.fn_func(columnD) end
Linchi
"adauti@.gmail.com" wrote:

> I noticed that some queries work in 2000, but not in 2005, for example
>
> SELECT columnA,
> columnB,
> case when columnC = 'BLAH' then dbo.fn_func(columnD)
> sum(columnE)
> FROM TableA
> GROUP BY
> columnA,
> columnB,
> case when columnC = 'BLAH' then dbo.fn_func(columnD)
> Works for 2K, but for 2K5 i get an error message saying "columnD is
> invalid in the select list because it is not contained...etc etc"
> ANyone know of the change?
>
sql

Query works in 2000 but not 2005

I noticed that some queries work in 2000, but not in 2005, for example
SELECT columnA,
columnB,
case when columnC = 'BLAH' then dbo.fn_func(columnD)
sum(columnE)
FROM TableA
GROUP BY
columnA,
columnB,
case when columnC = 'BLAH' then dbo.fn_func(columnD)
Works for 2K, but for 2K5 i get an error message saying "columnD is
invalid in the select list because it is not contained...etc etc"
ANyone know of the change?It shouldn't work on SQL2000 either. Your CASE expression is not complete and
there should be a comma before sum(columnE).
The following should work on both versions:
SELECT columnA,
columnB,
case when columnC = 'BLAH' then dbo.fn_func(columnD) end,
sum(columnE)
FROM TableA
GROUP BY
columnA,
columnB,
case when columnC = 'BLAH' then dbo.fn_func(columnD) end
Linchi
"adauti@.gmail.com" wrote:
> I noticed that some queries work in 2000, but not in 2005, for example
>
> SELECT columnA,
> columnB,
> case when columnC = 'BLAH' then dbo.fn_func(columnD)
> sum(columnE)
> FROM TableA
> GROUP BY
> columnA,
> columnB,
> case when columnC = 'BLAH' then dbo.fn_func(columnD)
> Works for 2K, but for 2K5 i get an error message saying "columnD is
> invalid in the select list because it is not contained...etc etc"
> ANyone know of the change?
>

Query works in 2000 but not 2005

I noticed that some queries work in 2000, but not in 2005, for example
SELECT columnA,
columnB,
case when columnC = 'BLAH' then dbo.fn_func(columnD)
sum(columnE)
FROM TableA
GROUP BY
columnA,
columnB,
case when columnC = 'BLAH' then dbo.fn_func(columnD)
Works for 2K, but for 2K5 i get an error message saying "columnD is
invalid in the select list because it is not contained...etc etc"
ANyone know of the change?It shouldn't work on SQL2000 either. Your CASE expression is not complete an
d
there should be a comma before sum(columnE).
The following should work on both versions:
SELECT columnA,
columnB,
case when columnC = 'BLAH' then dbo.fn_func(columnD) end,
sum(columnE)
FROM TableA
GROUP BY
columnA,
columnB,
case when columnC = 'BLAH' then dbo.fn_func(columnD) end
Linchi
"adauti@.gmail.com" wrote:

> I noticed that some queries work in 2000, but not in 2005, for example
>
> SELECT columnA,
> columnB,
> case when columnC = 'BLAH' then dbo.fn_func(columnD)
> sum(columnE)
> FROM TableA
> GROUP BY
> columnA,
> columnB,
> case when columnC = 'BLAH' then dbo.fn_func(columnD)
> Works for 2K, but for 2K5 i get an error message saying "columnD is
> invalid in the select list because it is not contained...etc etc"
> ANyone know of the change?
>

query with time period spanning two days

I would like to run queries with data that sometimes span two days. The queries require start and end dates as well as start and end times. The following code works fine if the start time is less than the end time:

select * from tst01 where convert(varchar, [DateTime],126) between '2005-09-15' and

'2006-01-27' and convert(varchar, [DateTime],114) between '09:00:00' and

'17:00:00' order by [DateTime]

However, if I try to run a query where the start time is greater than the end time (e.g., start time 5:00pm on one day until 9:00am the next day), the query returns an empty table.

select * from tst01 where convert(varchar, [DateTime],126) between '2005-09-15' and

'2006-01-27' and convert(varchar, [DateTime],114) between '17:00:00' and

'09:00:00' order by [DateTime]

I need a way to indicate that the start and end times span two days. Can anybody help with this?

I think I found the answer:

select * from tst01 where convert(varchar, [DateTime],126) between '2005-09-15' and

'2006-01-27' and ((convert(varchar, [DateTime],114) between '17:00:00' and

'23:59:59') or (convert(varchar, [DateTime],114) between '00:00:00' and

'09:00:00')) order by [DateTime]

|||

I'm not sure exactly what you're after, but you should investigate the DATEADD() or DATEDIFF() functions. They will handle the day differences for you.

CREATE TABLE #T1 (StartTime datetime, EndTime datetime)

INSERT INTO #T1

VALUES ('2000-01-01 8:00', '2000-01-01 8:05')

INSERT INTO #T1

VALUES ('2000-01-02 8:00', '2000-01-01 7:59')

SELECT DATEDIFF(hh, EndTime, StartTime)

FROM #T1

You may have to play with either of these functions if you want the full year, month, day, etc., but you just string them together to get that.

|||

I have numerous tables which contain data recorded over a 24hr period for several years. The tables each contain a column called [DateTime] (for historic reasons), as well as several other columns containing data recorded during those datetimes (in each record).

At times, it is necessary to examine data recorded during non-business hours (e.g., 5:00pm to 9:00am the following day). Hence, the start time (17:00:00 ) is greater than the end time (09:00:00) which falls on the following date (see code in posts above). The code in my second posting while a bit of a kludge seems to work. I'm not sure how using dateadd or datediff would help. But, thanks anyway.

|||I see. DATEADD() or DATEDIFF() will handle the day span problem for you, since they are aware (as shown in the example) that a day has passed between two hour or minute comparisons. You can use that logic to find and process the rows that have that condition.

Query Wait Option

How can I determine what resources my query is waiting for?
If the query is waiting on memory how can I determine this?
We have several queries in a batch job we are trying to
determine why the query is waiting so long to complete.
Thanks
Mark
FROM BOL:
A transaction containing the waiting query may hold locks
while the query waits for memory. In rare situations, it
is possible for an undetectable deadlock to occur.
Decreasing the query wait time lowers the probability of
such deadlocks. Eventually, a waiting query will be
terminated and the transaction locks released. However,
increasing the maximum wait time may increase the amount
of time for the query to be terminated. Changes to this
option are not recommended.
There are a numbe rof columns in master..sysprocesses that help you look at
WAITTYPE info for a process. You should also take a look at dbcc
sqlperf(waitstats).
Tom Davidson, from MS, has written a nice article on this at SQLMag.com
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Mark" <anonymous@.discussions.microsoft.com> wrote in message
news:2b3fe01c46820$14c51900$a401280a@.phx.gbl...
> How can I determine what resources my query is waiting for?
> If the query is waiting on memory how can I determine this?
> We have several queries in a batch job we are trying to
> determine why the query is waiting so long to complete.
> Thanks
> Mark
>
> FROM BOL:
> A transaction containing the waiting query may hold locks
> while the query waits for memory. In rare situations, it
> is possible for an undetectable deadlock to occur.
> Decreasing the query wait time lowers the probability of
> such deadlocks. Eventually, a waiting query will be
> terminated and the transaction locks released. However,
> increasing the maximum wait time may increase the amount
> of time for the query to be terminated. Changes to this
> option are not recommended.
>

Query Wait Option

How can I determine what resources my query is waiting for?
If the query is waiting on memory how can I determine this?
We have several queries in a batch job we are trying to
determine why the query is waiting so long to complete.
Thanks
Mark
FROM BOL:
A transaction containing the waiting query may hold locks
while the query waits for memory. In rare situations, it
is possible for an undetectable deadlock to occur.
Decreasing the query wait time lowers the probability of
such deadlocks. Eventually, a waiting query will be
terminated and the transaction locks released. However,
increasing the maximum wait time may increase the amount
of time for the query to be terminated. Changes to this
option are not recommended.There are a numbe rof columns in master..sysprocesses that help you look at
WAITTYPE info for a process. You should also take a look at dbcc
sqlperf(waitstats).
Tom Davidson, from MS, has written a nice article on this at SQLMag.com
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Mark" <anonymous@.discussions.microsoft.com> wrote in message
news:2b3fe01c46820$14c51900$a401280a@.phx.gbl...
> How can I determine what resources my query is waiting for?
> If the query is waiting on memory how can I determine this?
> We have several queries in a batch job we are trying to
> determine why the query is waiting so long to complete.
> Thanks
> Mark
>
> FROM BOL:
> A transaction containing the waiting query may hold locks
> while the query waits for memory. In rare situations, it
> is possible for an undetectable deadlock to occur.
> Decreasing the query wait time lowers the probability of
> such deadlocks. Eventually, a waiting query will be
> terminated and the transaction locks released. However,
> increasing the maximum wait time may increase the amount
> of time for the query to be terminated. Changes to this
> option are not recommended.
>sql

Query Wait Option

How can I determine what resources my query is waiting for?
If the query is waiting on memory how can I determine this?
We have several queries in a batch job we are trying to
determine why the query is waiting so long to complete.
Thanks
Mark
FROM BOL:
A transaction containing the waiting query may hold locks
while the query waits for memory. In rare situations, it
is possible for an undetectable deadlock to occur.
Decreasing the query wait time lowers the probability of
such deadlocks. Eventually, a waiting query will be
terminated and the transaction locks released. However,
increasing the maximum wait time may increase the amount
of time for the query to be terminated. Changes to this
option are not recommended.There are a numbe rof columns in master..sysprocesses that help you look at
WAITTYPE info for a process. You should also take a look at dbcc
sqlperf(waitstats).
Tom Davidson, from MS, has written a nice article on this at SQLMag.com
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Mark" <anonymous@.discussions.microsoft.com> wrote in message
news:2b3fe01c46820$14c51900$a401280a@.phx
.gbl...
> How can I determine what resources my query is waiting for?
> If the query is waiting on memory how can I determine this?
> We have several queries in a batch job we are trying to
> determine why the query is waiting so long to complete.
> Thanks
> Mark
>
> FROM BOL:
> A transaction containing the waiting query may hold locks
> while the query waits for memory. In rare situations, it
> is possible for an undetectable deadlock to occur.
> Decreasing the query wait time lowers the probability of
> such deadlocks. Eventually, a waiting query will be
> terminated and the transaction locks released. However,
> increasing the maximum wait time may increase the amount
> of time for the query to be terminated. Changes to this
> option are not recommended.
>

Query very slow via JDBC compared to Query Analyzer

I am trying out the JDBC driver SP3 for data access in a Java application,
and I find that some of the queries I had tested in Query Analyzer are
returning very slowly in my Java application. The queries not extremely
complex (just a couple joins), and they return small datasets (on the order
of 2-20 rows with 2 columns each).
In one example, I am waiting about 20 seconds for a query to return in my
Java application where it took less than one second in Query Analyzer.
I'm not really sure where to look to address these performance issues. Is
there something obvious I might have wrong on my connection string? I connect
to SQL Server over 100Mb Ethernet.
I'd appreciate any help!
Thanks
OK, it seems I did not choose very good search parameters when I looked for
answers previously... turns out the SendStringParametersAsUnicode as the
culprit. Sorry =) But thanks to those who posted previously.
"bbabey" wrote:

> I am trying out the JDBC driver SP3 for data access in a Java application,
> and I find that some of the queries I had tested in Query Analyzer are
> returning very slowly in my Java application. The queries not extremely
> complex (just a couple joins), and they return small datasets (on the order
> of 2-20 rows with 2 columns each).
> In one example, I am waiting about 20 seconds for a query to return in my
> Java application where it took less than one second in Query Analyzer.
> I'm not really sure where to look to address these performance issues. Is
> there something obvious I might have wrong on my connection string? I connect
> to SQL Server over 100Mb Ethernet.
> I'd appreciate any help!
> Thanks

Query using comparison between two fields as criteria

Hi all,
Apologies if this is the incorrect group. I couldn't find a group that
specifically handled queries in SQL Server. I originally posted this in the
Access Queries group, but since my back-end database is actually SQL Server
and I am just using Access as my front-end I need suggestions that are
specific to SQL Server.
Let's say I have a table with three fields in it: effective_date, entry_date
and amount. Is there a way that I could contruct a query such that I could
return all the records from the table where the latter of the effective_date
and entry_date fields falls within some target date range?
So for example, let's say the following data is in my table:
effective_date entry_date amount
1/1/2003 4/1/2004 $200
6/1/2004 9/1/2003 $120
5/1/2004 6/1/2005 $150
7/1/2004 4/1/2004 $170
2/1/2006 8/1/2004 $130
I want all the records where the *latter* of the two date fields falls
between 1/1/2004 and 12/31/2004. What I'd need to see in my result set based
on the data above is:
effective_date entry_date amount
1/1/2003 4/1/2004 $200
6/1/2004 9/1/2003 $120
7/1/2004 4/1/2004 $170
The third data item dropped out because even though the effective_date for
that record falls within our range, the latter of the two dates, the entry
date in this case, falls outside of our interested date range. The fifth
data item similarly falls out because its latest date, the effective date,
also falls outside our range.
How do I write a query to accomplish this?
Thanks for any help!
-Jeff VanBishlerThis is a good candidate for the CASE expression in SQL:
SELECT effective_date, entry_date, amount
FROM [Your Table]
WHERE
CASE
WHEN DATEDIFF(dd, effective_date, entry_date) < 0 THEN effective_date
ELSE entry_date
END
BETWEEN '1/1/2004' AND '12/31/2004'
The DATEDIFF function above is an easy way to find out which date is later
than the other. If effective_date is prior to entry_date, the DATEDIFF
function will return a number greater than one. If entry_date is prior to
effective_date, it will return a number less than one.
"Jeff VanBishler" wrote:

> Hi all,
> Apologies if this is the incorrect group. I couldn't find a group that
> specifically handled queries in SQL Server. I originally posted this in th
e
> Access Queries group, but since my back-end database is actually SQL Serve
r
> and I am just using Access as my front-end I need suggestions that are
> specific to SQL Server.
> Let's say I have a table with three fields in it: effective_date, entry_da
te
> and amount. Is there a way that I could contruct a query such that I could
> return all the records from the table where the latter of the effective_da
te
> and entry_date fields falls within some target date range?
> So for example, let's say the following data is in my table:
> effective_date entry_date amount
> 1/1/2003 4/1/2004 $200
> 6/1/2004 9/1/2003 $120
> 5/1/2004 6/1/2005 $150
> 7/1/2004 4/1/2004 $170
> 2/1/2006 8/1/2004 $130
> I want all the records where the *latter* of the two date fields falls
> between 1/1/2004 and 12/31/2004. What I'd need to see in my result set bas
ed
> on the data above is:
> effective_date entry_date amount
> 1/1/2003 4/1/2004 $200
> 6/1/2004 9/1/2003 $120
> 7/1/2004 4/1/2004 $170
> The third data item dropped out because even though the effective_date for
> that record falls within our range, the latter of the two dates, the entry
> date in this case, falls outside of our interested date range. The fifth
> data item similarly falls out because its latest date, the effective date,
> also falls outside our range.
> How do I write a query to accomplish this?
> Thanks for any help!
>
> -Jeff VanBishler
>
>|||Worked like a dream!
Thanks Mark!
-Jeff VanBishler
"Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:E5710BB0-9319-43C2-88A8-2E6307B9A5CD@.microsoft.com...
> This is a good candidate for the CASE expression in SQL:
> SELECT effective_date, entry_date, amount
> FROM [Your Table]
> WHERE
> CASE
> WHEN DATEDIFF(dd, effective_date, entry_date) < 0 THEN effective_date
> ELSE entry_date
> END
> BETWEEN '1/1/2004' AND '12/31/2004'
> The DATEDIFF function above is an easy way to find out which date is later
> than the other. If effective_date is prior to entry_date, the DATEDIFF
> function will return a number greater than one. If entry_date is prior to
> effective_date, it will return a number less than one.
> "Jeff VanBishler" wrote:
>

query tuning

I am not very good at writing queries - but I need help
tuning this one. Can someone help make this better. It's
retrieving from 3 different tables. Thank you.
Mary...
Select distinct CaseNo, courtCode, courtType, Amount,
DTaxID, DZip, DLastName, DFirstName, DMidName, DSuffix,
CoDLastName, CoDFirstName, CoDMidName,
CoDSuffix, CoDTaxID, DStAddress, DCity, DState,
PLastName, PFirstName, PMidName, PstAddress, PCity,
PState, PZip,
'FilingType'=(Case When FilingType='EV' Then 'CJ' Else
FilingType End), CourtCode, efxCourtCode, ResearcherNo,
JudgmentDate,
SatisfiedDate, c.Efx_prt, c.EFX_action_code,
c.Efx_status_code from tblCases with (Index = idx_BBB),
BBB_Client_CourtCodes,
EFXNDR_RecordType c WHERE PostedDate = '20040524' and
c.ndr_rt=rt and (Amount >= '00000050' or Amount >= 50) and
DType = 'I' and (CoDType = '' or CoDType = 'I') and
DStAddress <>'' and DStAddress is not null and DCity <>''
and DCity is not null and
DState <>'' and DState is not null and CourtCode = CC and
CourtType = CT and
RT = isnull(nullif((Case When DispositionType='ES'
Then 'JS' Else DispositionType End),''),
(Case When FilingType='EV' Then 'CJ' Else FilingType End))
and efxCourtCode is not null order by CourtCode
Hard to say without seeing more information such as DDL (CREATE TABLE
statements, constraints, etc.)
With this little information all I can suggest is to remove the index
hint, so SQL-Server can figure out for itself what the best access path
is.
Also, as a general advice, make sure you defined Primary Keys on all
tables, and that you have indexed all Foreign Keys.
A thing that will not improve performance is to rewrite the statement in
a more readable form, with some consistent spacing, indentation and LFs.
You may also want to switch from "SELECT ... FROM Table1, Table2 WHERE
Table1.something = Table2.something" to "SELECT ... FROM Table1 INNER
JOIN Table2 ON Table1.something = Table2.something", if only just for
readability.
Hope this helps,
Gert-Jan
Mary Lou wrote:
> I am not very good at writing queries - but I need help
> tuning this one. Can someone help make this better. It's
> retrieving from 3 different tables. Thank you.
> Mary...
> Select distinct CaseNo, courtCode, courtType, Amount,
> DTaxID, DZip, DLastName, DFirstName, DMidName, DSuffix,
> CoDLastName, CoDFirstName, CoDMidName,
> CoDSuffix, CoDTaxID, DStAddress, DCity, DState,
> PLastName, PFirstName, PMidName, PstAddress, PCity,
> PState, PZip,
> 'FilingType'=(Case When FilingType='EV' Then 'CJ' Else
> FilingType End), CourtCode, efxCourtCode, ResearcherNo,
> JudgmentDate,
> SatisfiedDate, c.Efx_prt, c.EFX_action_code,
> c.Efx_status_code from tblCases with (Index = idx_BBB),
> BBB_Client_CourtCodes,
> EFXNDR_RecordType c WHERE PostedDate = '20040524' and
> c.ndr_rt=rt and (Amount >= '00000050' or Amount >= 50) and
> DType = 'I' and (CoDType = '' or CoDType = 'I') and
> DStAddress <>'' and DStAddress is not null and DCity <>''
> and DCity is not null and
> DState <>'' and DState is not null and CourtCode = CC and
> CourtType = CT and
> RT = isnull(nullif((Case When DispositionType='ES'
> Then 'JS' Else DispositionType End),''),
> (Case When FilingType='EV' Then 'CJ' Else FilingType End))
> and efxCourtCode is not null order by CourtCode
(Please reply only to the newsgroup)
|||There are a couple of more things I might add...
SInce you do not have any join criteria you are doing a cross join... Each
court code row is joined with each case... I suspect this is not what you
wish...Unless (CourtCode = CC and
> CourtType = CT ) is part of the join criteria
There probably should be something like... And I would recommend re-writing
this using the ANSI Style Join as below...
from tblCases inner join BBB_Client_CourtCodes on SomcolumnintablesCases =
somecolumninBBB_Client_CourtCodes
inner join EFXNDR_Recordtype C on SomeColumninTablesCases =
somecoluminRecordtype
Amount >= '00000050' or Amount >= 50 - this is also strange... Is the amount
column character or numeric?
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mary Lou" <anonymous@.discussions.microsoft.com> wrote in message
news:1c0e501c4524c$d1f4f3e0$a601280a@.phx.gbl...
> I am not very good at writing queries - but I need help
> tuning this one. Can someone help make this better. It's
> retrieving from 3 different tables. Thank you.
> Mary...
>
> Select distinct CaseNo, courtCode, courtType, Amount,
> DTaxID, DZip, DLastName, DFirstName, DMidName, DSuffix,
> CoDLastName, CoDFirstName, CoDMidName,
> CoDSuffix, CoDTaxID, DStAddress, DCity, DState,
> PLastName, PFirstName, PMidName, PstAddress, PCity,
> PState, PZip,
> 'FilingType'=(Case When FilingType='EV' Then 'CJ' Else
> FilingType End), CourtCode, efxCourtCode, ResearcherNo,
> JudgmentDate,
> SatisfiedDate, c.Efx_prt, c.EFX_action_code,
> c.Efx_status_code from tblCases with (Index = idx_BBB),
> BBB_Client_CourtCodes,
> EFXNDR_RecordType c WHERE PostedDate = '20040524' and
> c.ndr_rt=rt and (Amount >= '00000050' or Amount >= 50) and
> DType = 'I' and (CoDType = '' or CoDType = 'I') and
> DStAddress <>'' and DStAddress is not null and DCity <>''
> and DCity is not null and
> DState <>'' and DState is not null and CourtCode = CC and
> CourtType = CT and
> RT = isnull(nullif((Case When DispositionType='ES'
> Then 'JS' Else DispositionType End),''),
> (Case When FilingType='EV' Then 'CJ' Else FilingType End))
> and efxCourtCode is not null order by CourtCode

query tuning

I am not very good at writing queries - but I need help
tuning this one. Can someone help make this better. It's
retrieving from 3 different tables. Thank you.
Mary...
Select distinct CaseNo, courtCode, courtType, Amount,
DTaxID, DZip, DLastName, DFirstName, DMidName, DSuffix,
CoDLastName, CoDFirstName, CoDMidName,
CoDSuffix, CoDTaxID, DStAddress, DCity, DState,
PLastName, PFirstName, PMidName, PstAddress, PCity,
PState, PZip,
'FilingType'=(Case When FilingType='EV' Then 'CJ' Else
FilingType End), CourtCode, efxCourtCode, ResearcherNo,
JudgmentDate,
SatisfiedDate, c.Efx_prt, c.EFX_action_code,
c.Efx_status_code from tblCases with (Index = idx_BBB),
BBB_Client_CourtCodes,
EFXNDR_RecordType c WHERE PostedDate = '20040524' and
c.ndr_rt=rt and (Amount >= '00000050' or Amount >= 50) and
DType = 'I' and (CoDType = '' or CoDType = 'I') and
DStAddress <>'' and DStAddress is not null and DCity <>''
and DCity is not null and
DState <>'' and DState is not null and CourtCode = CC and
CourtType = CT and
RT = isnull(nullif((Case When DispositionType='ES'
Then 'JS' Else DispositionType End),''),
(Case When FilingType='EV' Then 'CJ' Else FilingType End))
and efxCourtCode is not null order by CourtCodeHard to say without seeing more information such as DDL (CREATE TABLE
statements, constraints, etc.)
With this little information all I can suggest is to remove the index
hint, so SQL-Server can figure out for itself what the best access path
is.
Also, as a general advice, make sure you defined Primary Keys on all
tables, and that you have indexed all Foreign Keys.
A thing that will not improve performance is to rewrite the statement in
a more readable form, with some consistent spacing, indentation and LFs.
You may also want to switch from "SELECT ... FROM Table1, Table2 WHERE
Table1.something = Table2.something" to "SELECT ... FROM Table1 INNER
JOIN Table2 ON Table1.something = Table2.something", if only just for
readability.
Hope this helps,
Gert-Jan
Mary Lou wrote:
> I am not very good at writing queries - but I need help
> tuning this one. Can someone help make this better. It's
> retrieving from 3 different tables. Thank you.
> Mary...
> Select distinct CaseNo, courtCode, courtType, Amount,
> DTaxID, DZip, DLastName, DFirstName, DMidName, DSuffix,
> CoDLastName, CoDFirstName, CoDMidName,
> CoDSuffix, CoDTaxID, DStAddress, DCity, DState,
> PLastName, PFirstName, PMidName, PstAddress, PCity,
> PState, PZip,
> 'FilingType'=(Case When FilingType='EV' Then 'CJ' Else
> FilingType End), CourtCode, efxCourtCode, ResearcherNo,
> JudgmentDate,
> SatisfiedDate, c.Efx_prt, c.EFX_action_code,
> c.Efx_status_code from tblCases with (Index = idx_BBB),
> BBB_Client_CourtCodes,
> EFXNDR_RecordType c WHERE PostedDate = '20040524' and
> c.ndr_rt=rt and (Amount >= '00000050' or Amount >= 50) and
> DType = 'I' and (CoDType = '' or CoDType = 'I') and
> DStAddress <>'' and DStAddress is not null and DCity <>''
> and DCity is not null and
> DState <>'' and DState is not null and CourtCode = CC and
> CourtType = CT and
> RT = isnull(nullif((Case When DispositionType='ES'
> Then 'JS' Else DispositionType End),''),
> (Case When FilingType='EV' Then 'CJ' Else FilingType End))
> and efxCourtCode is not null order by CourtCode
--
(Please reply only to the newsgroup)|||There are a couple of more things I might add...
SInce you do not have any join criteria you are doing a cross join... Each
court code row is joined with each case... I suspect this is not what you
wish...Unless (CourtCode = CC and
> CourtType = CT ) is part of the join criteria
There probably should be something like... And I would recommend re-writing
this using the ANSI Style Join as below...
from tblCases inner join BBB_Client_CourtCodes on SomcolumnintablesCases =somecolumninBBB_Client_CourtCodes
inner join EFXNDR_Recordtype C on SomeColumninTablesCases =somecoluminRecordtype
Amount >= '00000050' or Amount >= 50 - this is also strange... Is the amount
column character or numeric?
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mary Lou" <anonymous@.discussions.microsoft.com> wrote in message
news:1c0e501c4524c$d1f4f3e0$a601280a@.phx.gbl...
> I am not very good at writing queries - but I need help
> tuning this one. Can someone help make this better. It's
> retrieving from 3 different tables. Thank you.
> Mary...
>
> Select distinct CaseNo, courtCode, courtType, Amount,
> DTaxID, DZip, DLastName, DFirstName, DMidName, DSuffix,
> CoDLastName, CoDFirstName, CoDMidName,
> CoDSuffix, CoDTaxID, DStAddress, DCity, DState,
> PLastName, PFirstName, PMidName, PstAddress, PCity,
> PState, PZip,
> 'FilingType'=(Case When FilingType='EV' Then 'CJ' Else
> FilingType End), CourtCode, efxCourtCode, ResearcherNo,
> JudgmentDate,
> SatisfiedDate, c.Efx_prt, c.EFX_action_code,
> c.Efx_status_code from tblCases with (Index = idx_BBB),
> BBB_Client_CourtCodes,
> EFXNDR_RecordType c WHERE PostedDate = '20040524' and
> c.ndr_rt=rt and (Amount >= '00000050' or Amount >= 50) and
> DType = 'I' and (CoDType = '' or CoDType = 'I') and
> DStAddress <>'' and DStAddress is not null and DCity <>''
> and DCity is not null and
> DState <>'' and DState is not null and CourtCode = CC and
> CourtType = CT and
> RT = isnull(nullif((Case When DispositionType='ES'
> Then 'JS' Else DispositionType End),''),
> (Case When FilingType='EV' Then 'CJ' Else FilingType End))
> and efxCourtCode is not null order by CourtCodesql

query tuning

I am not very good at writing queries - but I need help
tuning this one. Can someone help make this better. It's
retrieving from 3 different tables. Thank you.
Mary...
Select distinct CaseNo, courtCode, courtType, Amount,
DTaxID, DZip, DLastName, DFirstName, DMidName, DSuffix,
CoDLastName, CoDFirstName, CoDMidName,
CoDSuffix, CoDTaxID, DStAddress, DCity, DState,
PLastName, PFirstName, PMidName, PstAddress, PCity,
PState, PZip,
'FilingType'=(Case When FilingType='EV' Then 'CJ' Else
FilingType End), CourtCode, efxCourtCode, ResearcherNo,
JudgmentDate,
SatisfiedDate, c.Efx_prt, c.EFX_action_code,
c.Efx_status_code from tblCases with (Index = idx_BBB),
BBB_Client_CourtCodes,
EFXNDR_RecordType c WHERE PostedDate = '20040524' and
c.ndr_rt=rt and (Amount >= '00000050' or Amount >= 50) and
DType = 'I' and (CoDType = '' or CoDType = 'I') and
DStAddress <>'' and DStAddress is not null and DCity <>''
and DCity is not null and
DState <>'' and DState is not null and CourtCode = CC and
CourtType = CT and
RT = isnull(nullif((Case When DispositionType='ES'
Then 'JS' Else DispositionType End),''),
(Case When FilingType='EV' Then 'CJ' Else FilingType End))
and efxCourtCode is not null order by CourtCodeHard to say without seeing more information such as DDL (CREATE TABLE
statements, constraints, etc.)
With this little information all I can suggest is to remove the index
hint, so SQL-Server can figure out for itself what the best access path
is.
Also, as a general advice, make sure you defined Primary Keys on all
tables, and that you have indexed all Foreign Keys.
A thing that will not improve performance is to rewrite the statement in
a more readable form, with some consistent spacing, indentation and LFs.
You may also want to switch from "SELECT ... FROM Table1, Table2 WHERE
Table1.something = Table2.something" to "SELECT ... FROM Table1 INNER
JOIN Table2 ON Table1.something = Table2.something", if only just for
readability.
Hope this helps,
Gert-Jan
Mary Lou wrote:
> I am not very good at writing queries - but I need help
> tuning this one. Can someone help make this better. It's
> retrieving from 3 different tables. Thank you.
> Mary...
> Select distinct CaseNo, courtCode, courtType, Amount,
> DTaxID, DZip, DLastName, DFirstName, DMidName, DSuffix,
> CoDLastName, CoDFirstName, CoDMidName,
> CoDSuffix, CoDTaxID, DStAddress, DCity, DState,
> PLastName, PFirstName, PMidName, PstAddress, PCity,
> PState, PZip,
> 'FilingType'=(Case When FilingType='EV' Then 'CJ' Else
> FilingType End), CourtCode, efxCourtCode, ResearcherNo,
> JudgmentDate,
> SatisfiedDate, c.Efx_prt, c.EFX_action_code,
> c.Efx_status_code from tblCases with (Index = idx_BBB),
> BBB_Client_CourtCodes,
> EFXNDR_RecordType c WHERE PostedDate = '20040524' and
> c.ndr_rt=rt and (Amount >= '00000050' or Amount >= 50) and
> DType = 'I' and (CoDType = '' or CoDType = 'I') and
> DStAddress <>'' and DStAddress is not null and DCity <>''
> and DCity is not null and
> DState <>'' and DState is not null and CourtCode = CC and
> CourtType = CT and
> RT = isnull(nullif((Case When DispositionType='ES'
> Then 'JS' Else DispositionType End),''),
> (Case When FilingType='EV' Then 'CJ' Else FilingType End))
> and efxCourtCode is not null order by CourtCode
(Please reply only to the newsgroup)|||There are a couple of more things I might add...
SInce you do not have any join criteria you are doing a cross join... Each
court code row is joined with each case... I suspect this is not what you
wish...Unless (CourtCode = CC and
> CourtType = CT ) is part of the join criteria
There probably should be something like... And I would recommend re-writing
this using the ANSI Style Join as below...
from tblCases inner join BBB_Client_CourtCodes on SomcolumnintablesCases =
somecolumninBBB_Client_CourtCodes
inner join EFXNDR_Recordtype C on SomeColumninTablesCases =
somecoluminRecordtype
Amount >= '00000050' or Amount >= 50 - this is also strange... Is the amount
column character or numeric?
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mary Lou" <anonymous@.discussions.microsoft.com> wrote in message
news:1c0e501c4524c$d1f4f3e0$a601280a@.phx
.gbl...
> I am not very good at writing queries - but I need help
> tuning this one. Can someone help make this better. It's
> retrieving from 3 different tables. Thank you.
> Mary...
>
> Select distinct CaseNo, courtCode, courtType, Amount,
> DTaxID, DZip, DLastName, DFirstName, DMidName, DSuffix,
> CoDLastName, CoDFirstName, CoDMidName,
> CoDSuffix, CoDTaxID, DStAddress, DCity, DState,
> PLastName, PFirstName, PMidName, PstAddress, PCity,
> PState, PZip,
> 'FilingType'=(Case When FilingType='EV' Then 'CJ' Else
> FilingType End), CourtCode, efxCourtCode, ResearcherNo,
> JudgmentDate,
> SatisfiedDate, c.Efx_prt, c.EFX_action_code,
> c.Efx_status_code from tblCases with (Index = idx_BBB),
> BBB_Client_CourtCodes,
> EFXNDR_RecordType c WHERE PostedDate = '20040524' and
> c.ndr_rt=rt and (Amount >= '00000050' or Amount >= 50) and
> DType = 'I' and (CoDType = '' or CoDType = 'I') and
> DStAddress <>'' and DStAddress is not null and DCity <>''
> and DCity is not null and
> DState <>'' and DState is not null and CourtCode = CC and
> CourtType = CT and
> RT = isnull(nullif((Case When DispositionType='ES'
> Then 'JS' Else DispositionType End),''),
> (Case When FilingType='EV' Then 'CJ' Else FilingType End))
> and efxCourtCode is not null order by CourtCode

Query Tuning

I want to take the execution plan of some transact sql queries, I took the execution plan as text based one, that having

index scan, index seek

Remote scan, remote Update

sort order by cluases

in the above clauses what is the high performance, and how will i change to the high performance clauses by changin the query to improve the execution speed of the query

Please guide me

the execution plan is

SELECT [Inventory_Profile].[InventoryID] ,[Inventory_Profile].[Alias] ,[Inventory_Profile].[InventoryStatusID] ,[Inventory_Profile].[InventorySubTypeID] ,[Inventory_Profile].[InventoryTypeID] ,[Inventory_Profile].[AcquisitionDate] ,[Inventory_Profile].[UnitNumber] ,[Inventory_Profile].[YearOfManufacture] ,[Inventory_Profile].[Manufacturer] ,[Inventory_Profile].[Make] ,[Inventory_Profile].[Model] ,[Inventory_Profile].[SerialNumber] ,[Inventory_Profile].[UsageConditionID] ,[Inventory_Profile].[Description1] ,[Inventory_Profile].[Description2] ,[Inventory_Profile].[LocationEffectiveFromDate] ,[Inventory_Profile].[IsFlaggedForSale] ,[Inventory_Profile].[RentalPurchaseOrderNumber] ,[Inventory_Profile].[AquisitionPurchaseOrderNumber] ,[Inventory_Profile].[SortOrder] ,[Inventory_Profile].[IsSaleLeaseBack] ,[Inventory_Profile].[InterimRentReceivableUpfrontTaxModeID] ,[Inventory_Profile].[LeaseRentalReceivableUpfrontTaxModeID] ,[Inventory_Profile].[OverTermReceivableUpfrontTaxModeID] ,[TaxDepreciation_Inventory].[IsTaxDepreciationRequired] ,[TaxDepreciation_Inventory].[IsComputationPending] ,[TaxDepreciation_Inventory].[TaxDepreciationTemplateID] ,[TaxDepreciation_Inventory].[InventoryCostBasisAmount] ,[TaxDepreciation_Inventory].[DepreciationBeginDate] ,[TaxDepreciation_Inventory].[DepreciationEndDate] ,[TaxDepreciation_Inventory].[IsTaxDepreciationTerminated] ,[TaxDepreciation_Inventory].[IsStraightLineMethodUsed] ,[TaxDepreciation_Inventory].[IsLeaseTermUsedForStraightLineMethod] ,[Inventory_PTMS].[Division] ,[Inventory_PTMS].[Branch] ,[Inventory_PTMS].[SalesTaxPercent] ,[Inventory_PTMS].[SalesTaxAmount] ,[Inventory_PTMS].[IsSalesTaxIncluded] ,[Inventory_PTMS].[GLExpenseAccount] ,[Inventory_PTMS].[GLAssetAccount] ,[Inventory_PTMS].[SoftwareExclusionAmount] ,[Inventory_PTMS].[AssetCategoryCodeID] ,[Inventory_PTMS].[OwnershipCodeID] ,[Inventory_PTMS].[ManufacturingCodeID] ,[Inventory_PTMS].[ReimburseCodeID] ,[Inventory_PTMS].[BillingStatusID] ,[Inventory_PTMS].[PropertyTaxExemptionCodeID] ,[Inventory_PTMS].[UserDefinedField1] ,[Inventory_PTMS].[UserDefinedField2] ,[Inventory_PTMS].[Notes] FROM [Inventory_Profile] INNER JOIN [TaxDepreciation_Inventory] ON [Inventory_Profile].[InventoryID]=[TaxDepreciation_Inventory].[InventoryID] INNER JOIN [Inventory_PTMS] ON [Inventory_Profile].[InventoryID]=[Inventory_PTMS].[InventoryID] INNER JOIN [Inventory_Status_CnfgLocale] ON [Inventory_Profile].[InventoryStatusID] in (SELECT InventoryStatusID FROM Inventory_Status_CnfgLocale WHERE InventoryStatusName <> 'Donated' and InventoryStatusName <> 'Scrap' and InventoryStatusName <>'Write Off' and InventoryStatusName <> 'Sold')

|--Nested Loops(Inner Join)
|--Nested Loops(Inner Join, OUTER REFERENCES:([LW_Winthrop].[dbo].[TaxDepreciation_Inventory].[InventoryTaxDepreciationDetailID]))
| |--Nested Loops(Inner Join, OUTER REFERENCES:([LW_Winthrop].[dbo].[Inventory_PTMS].[InventoryID]))
| | |--Nested Loops(Left Semi Join, OUTER REFERENCES:([LW_Winthrop].[dbo].[Inventory_Profile].[InventoryStatusID]))
| | | |--Nested Loops(Inner Join, OUTER REFERENCES:([LW_Winthrop].[dbo].[Inventory_PTMS].[InventoryID]))
| | | | |--Clustered Index Scan(OBJECT:([LW_Winthrop].[dbo].[Inventory_PTMS].[PK_IInvPTMS]))
| | | | |--Clustered Index Seek(OBJECT:([LW_Winthrop].[dbo].[Inventory_Profile].[PK_IInventory_InvID]), SEEK:([LW_Winthrop].[dbo].[Inventory_Profile].[InventoryID]=[LW_Winthrop].[dbo].[Inventory_PTMS].[InventoryID]) ORDERED FORWARD)
| | | |--Clustered Index Seek(OBJECT:([LW_Winthrop].[dbo].[Inventory_Status_CnfgLocale].[PK_cdInvStatus_Locale]), SEEK:([LW_Winthrop].[dbo].[Inventory_Status_CnfgLocale].[InventoryStatusID]=[LW_Winthrop].[dbo].[Inventory_Profile].[InventoryStatusID]), WHERE:([LW_Winthrop].[dbo].[Inventory_Status_CnfgLocale].[InventoryStatusName]<>N'Donated' AND [LW_Winthrop].[dbo].[Inventory_Status_CnfgLocale].[InventoryStatusName]<>N'Scrap' AND [LW_Winthrop].[dbo].[Inventory_Status_CnfgLocale].[InventoryStatusName]<>N'Sold' AND [LW_Winthrop].[dbo].[Inventory_Status_CnfgLocale].[InventoryStatusName]<>N'Write Off') ORDERED FORWARD)
| | |--Index Seek(OBJECT:([LW_Winthrop].[dbo].[TaxDepreciation_Inventory].[UK_ITaxDepInfo]), SEEK:([LW_Winthrop].[dbo].[TaxDepreciation_Inventory].[InventoryID]=[LW_Winthrop].[dbo].[Inventory_PTMS].[InventoryID]) ORDERED FORWARD)
| |--Clustered Index Seek(OBJECT:([LW_Winthrop].[dbo].[TaxDepreciation_Inventory].[PK_ITaxDepInfo]), SEEK:([LW_Winthrop].[dbo].[TaxDepreciation_Inventory].[InventoryTaxDepreciationDetailID]=[LW_Winthrop].[dbo].[TaxDepreciation_Inventory].[InventoryTaxDepreciationDetailID]) LOOKUP ORDERED FORWARD)
|--Clustered Index Scan(OBJECT:([LW_Winthrop].[dbo].[Inventory_Status_CnfgLocale].[PK_cdInvStatus_Locale]))

Hello,

could you please provide the full execution plan as output by Query Analyzer, as well as the query you are running?

|||

Hi,

--index scan good, index seek better :-)

--Remote scan und update is always involved if your doing something on a remote basis, like a linked server. If you want to use the remote indexes you will have to create a logic / stored procedureon the remote side on execute that via a linked server command.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Index scan sometimes means bad news :)
Say, when a clustered index is defined on a table, the query plan will never show a "table scan" operation, it will always show "index scan", even if it is scanning the table.
Index seek means that your index is actually been used, this is generally a good news. But nothing general can be said, sometimes index seek is worse than index scan, and vice vers. For example, if you perform a range query and have a clustered index on appropriate columns, this results in an index scan, which is the fastest plan in most cases. Meanwhile, if you have a non-clustered index on those columns, you will get an index seek(or table scan, depending on statistics). This is almost always worse that index seek. If you do not have clustered index on the columns in where clause, but you have some other clustered index, you will get an index scan which is bad, it is a table scan in fact. So in the first case scan > seek and in the second case seek > scan.

Post the query, and table schema, we will be able to be more definite in that case.

Query training -- Complex queries

Can anyone please suggest a book (or a training class) that gets into
the guts of complex queries? I need to understand, for example, the
internal differences between this update query:
Update PositionsEOM
Set MktVal = PM.Price * PositionsEOM.Quantity_ASD
From PositionsEOM Inner Join [Price Master] As PM On
PositionsEOM.Fund_ID = PM.Fund_ID And
PositionsEOM.Position_Date = PM.Price_Date
and this one:
Update PositionsEOM
Set MktVal = Derived.MktVal From
(Select PM.Price * PositionsEOM.Quantity_ASD As MktVal
From PositionsEOM Inner Join [Price Master] As PM On
PositionsEOM.Fund_ID = PM.Fund_ID And
PositionsEOM.Position_Date = PM.Price_Date) As Derived
The second example ran for 20 minutes and I cancelled it; the first one
finished in 2.5 minutes. You wouldn't KNOW that you could write "Set"
expressions like the first example, from ANY of the online
documentation. Even though the second example uses a derived table, I
don't see much of a logical difference between them, and would not
expect the run time to be different.
Also, I decided that when you mix inner and outer joins in a select,
which I do sometimes, if you change the order, the result set WILL be
different (not just the time it takes). (I think this is true...) When
you build complicated select statements in Enterprise Manager's view
designer window, it doesn't really take this into account (you can say
"Select all rows from tableA", but you might really be getting all rows
from the result of the inner join on tableA and tableB).
I found one online article that mentioned that if you mix inner and
outer joins in a query, you CAN control the order of the joins -- and it
shows various ways to do that, including parentheses and derived or
virtual tables. But only ONE online article. It's VERY rare to see any
of this documented, and SQL Books Online sure doesn't show
examples.......
The examples for Update queries usually show that you can set price to
price*1.05, and helpfully explains that this will increase all prices by
5 percent. That's trivial. Then they move on to other subjects...
I don't know where to come across such learning; SQL Books Online sure
doesn't go into these kinds of examples. Their syntax diagrams are
probably complete, but of course they are huge and somewhat hard to
understand.
Any help will be greatly appreciated.
David WalkerYou might want to check out _Advanced Transact-SQL For SQL Server_ by Itizik
Ben-Gan and Tom Moreau. Pretty good book.
As for your queries, the first one filters the rows to update with the JOIN.
The second one will update every single row in the PositionsEOM table once
for every row returned in the derived table, as there is no correlation
between the two tables (you have not JOINed them.)
"DW" <None> wrote in message news:u#t6Wj##DHA.3272@.TK2MSFTNGP09.phx.gbl...
> Can anyone please suggest a book (or a training class) that gets into
> the guts of complex queries? I need to understand, for example, the
> internal differences between this update query:
> Update PositionsEOM
> Set MktVal = PM.Price * PositionsEOM.Quantity_ASD
> From PositionsEOM Inner Join [Price Master] As PM On
> PositionsEOM.Fund_ID = PM.Fund_ID And
> PositionsEOM.Position_Date = PM.Price_Date
> and this one:
> Update PositionsEOM
> Set MktVal = Derived.MktVal From
> (Select PM.Price * PositionsEOM.Quantity_ASD As MktVal
> From PositionsEOM Inner Join [Price Master] As PM On
> PositionsEOM.Fund_ID = PM.Fund_ID And
> PositionsEOM.Position_Date = PM.Price_Date) As Derived
> The second example ran for 20 minutes and I cancelled it; the first one
> finished in 2.5 minutes. You wouldn't KNOW that you could write "Set"
> expressions like the first example, from ANY of the online
> documentation. Even though the second example uses a derived table, I
> don't see much of a logical difference between them, and would not
> expect the run time to be different.
> Also, I decided that when you mix inner and outer joins in a select,
> which I do sometimes, if you change the order, the result set WILL be
> different (not just the time it takes). (I think this is true...) When
> you build complicated select statements in Enterprise Manager's view
> designer window, it doesn't really take this into account (you can say
> "Select all rows from tableA", but you might really be getting all rows
> from the result of the inner join on tableA and tableB).
> I found one online article that mentioned that if you mix inner and
> outer joins in a query, you CAN control the order of the joins -- and it
> shows various ways to do that, including parentheses and derived or
> virtual tables. But only ONE online article. It's VERY rare to see any
> of this documented, and SQL Books Online sure doesn't show
> examples.......
> The examples for Update queries usually show that you can set price to
> price*1.05, and helpfully explains that this will increase all prices by
> 5 percent. That's trivial. Then they move on to other subjects...
> I don't know where to come across such learning; SQL Books Online sure
> doesn't go into these kinds of examples. Their syntax diagrams are
> probably complete, but of course they are huge and somewhat hard to
> understand.
> Any help will be greatly appreciated.
> David Walker|||DW (None) writes:
> Can anyone please suggest a book (or a training class) that gets into
> the guts of complex queries? I need to understand, for example, the
> internal differences between this update query:
> Update PositionsEOM
> Set MktVal = PM.Price * PositionsEOM.Quantity_ASD
> From PositionsEOM Inner Join [Price Master] As PM On
> PositionsEOM.Fund_ID = PM.Fund_ID And
> PositionsEOM.Position_Date = PM.Price_Date
> and this one:
> Update PositionsEOM
> Set MktVal = Derived.MktVal From
> (Select PM.Price * PositionsEOM.Quantity_ASD As MktVal
> From PositionsEOM Inner Join [Price Master] As PM On
> PositionsEOM.Fund_ID = PM.Fund_ID And
> PositionsEOM.Position_Date = PM.Price_Date) As Derived
> The second example ran for 20 minutes and I cancelled it; the first one
> finished in 2.5 minutes.
As Adam pointed out, the problem with the second query is that the derived
table is not correlated with the updated table, so you get a magnificent
cross join, and I'm not surprise that it did not complete in 20 minutes.
Since I don't know your tables, I cannot really a suggest a better way
to use the derived table. What you could write, is this:
Update PositionsEOM
Set MktVal = PositionsEOM.Quantity_ASD *
(Select PM.Price
FROM [Price Master] As PM
WHERE PositionsEOM.Fund_ID = PM.Fund_ID And
AND PositionsEOM.Position_Date = PM.Price_Date)
Here you don't have a derived table, but a correlated subquery. This is
actually the way you should do it, if you stick to ANSI. I need to
confess, though, that I prefer the syntax with FROM over the using
a correlated subquery.
> You wouldn't KNOW that you could write "Set" expressions like the
> first example, from ANY of the online documentation.
Nah, I would say that this is quite clear from the syntax diagram for
UPDATE in Books Online.
> Also, I decided that when you mix inner and outer joins in a select,
> which I do sometimes, if you change the order, the result set WILL be
> different (not just the time it takes). (I think this is true...)
Yes, this is correct. As long as you are only using inner joins, you
can have them in any order, but with left joins the table are turn.
Consider:
SELECT ...
FROM a
LEFT JOIN b ON a.col = b.col
JOIN c ON b.col2 = c.col2
What you probably want is:
SELECT
FROM a
LEFT JOIN (b JOIN c ON b.col2 = c.col2) ON a.col = b.col
In the first case, you take all rows in a, and if there is no matching
rows in b, you set all b:s colums to NULL. But then join the table
(a LEFT JOIN b) with c, and you will include rows that matches in c,
so all those NULL rows in b are now dropped.
In the second case you first join b with c, and then join this table
with a.
> I found one online article that mentioned that if you mix inner and
> outer joins in a query, you CAN control the order of the joins -- and it
> shows various ways to do that, including parentheses and derived or
> virtual tables. But only ONE online article. It's VERY rare to see any
> of this documented, and SQL Books Online sure doesn't show
> examples.......
It should be in the ANSI-92 standard for SQL, but it's hardly the kind
of book you read first.
The best book for reading about the various join techniques, is Joe
Celko's "SQL For Smarties", but of course it has the drawback that it
is not written for SQL Server. Tom Moreau's and Itzik Ben-Gan's book
is a better choice, if you want something which is more on target. And
quite some of these subjects are covered in Kalen Delaney's "Inside
SQL Server 2000". It was from her SQL7 book, that I learnt to use
paranthesis with JOIN.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in
news:Xns949B671D1EA5Yazorman@.127.0.0.1:
> As Adam pointed out, the problem with the second query is that the
> derived table is not correlated with the updated table, so you get a
> magnificent cross join, and I'm not surprise that it did not complete
> in 20 minutes.
Thanks to both of you for the answers. I will check out those books on
Amazon.
Regarding this join:
Update PositionsEOM
Set MktVal = Derived.MktVal From
(Select PM.Price * PositionsEOM.Quantity_ASD As MktVal
From PositionsEOM Inner Join [Price Master] As PM On
PositionsEOM.Fund_ID = PM.Fund_ID And
PositionsEOM.Position_Date = PM.Price_Date) As Derived
Why is this a cross join? It has the phrase "Inner Join" in it (with
two conditions, hence the AND). I'm confused...
Thanks.
David Walker|||Hi David,
Thanks for you update.
First, I recommend the book I always use: <Inside SQL Server 2000> by Kalen
Delaney.
Second, let's take a little example, you could run in you Query Analyzer:
use pubs
go
if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[authorsx]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[authorsx]
GO
CREATE TABLE [dbo].[authorsx] (
[au_id] [id] NOT NULL ,
[au_lname] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[au_fname] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[phone] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[address] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[city] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[state] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[zip] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[contract] [bit] NOT NULL
) ON [PRIMARY]
GO
insert into authorsx select * from authors
Then
SET SHOWPLAN_TEXT ON
go
update authors
set authors.state=authorsx.state
from authors inner join authorsx
on authors.au_id=authorsx.au_id
go
update authors
set authors.state=xxx.state
from
(select authors.state
from authors inner join authorsx
on authors.au_id=authorsx.au_id) as xxx
--execution plan of first one ( similar to your first query)
|--Clustered Index
Update(OBJECT:([pubs].[dbo].[authors].[UPKCL_auidind]),
SET:([authors].[state]=[authorsx].[state]))
|--Top(ROWCOUNT est 0)
|--Sort(DISTINCT ORDER BY:([Bmk1000] ASC))
|--Nested Loops(Inner Join, OUTER
REFERENCES:([authorsx].[au_id]))
|--Table Scan(OBJECT:([pubs].[dbo].[authorsx]))
|--Clustered Index
Seek(OBJECT:([pubs].[dbo].[authors].[UPKCL_auidind]),
SEEK:([authors].[au_id]=[authorsx].[au_id]) ORDERED FORWARD)
--execution plan of the second one (simular to your second query)
|--Clustered Index
Update(OBJECT:([pubs].[dbo].[authors].[UPKCL_auidind]),
SET:([authors].[state]=[authors].[state]))
|--Top(ROWCOUNT est 0)
|--Sort(DISTINCT ORDER BY:([Bmk1000] ASC))
|--Nested Loops(Inner Join)
|--Nested Loops(Inner Join, OUTER
REFERENCES:([authorsx].[au_id]))
| |--Table Scan(OBJECT:([pubs].[dbo].[authorsx]))
| |--Clustered Index
Seek(OBJECT:([pubs].[dbo].[authors].[UPKCL_auidind]),
SEEK:([authors].[au_id]=[authorsx].[au_id]) ORDERED FORWARD)
|--Index
Scan(OBJECT:([pubs].[dbo].[authors].[test_index]), ORDERED FORWARD)
go
You will notice that there are tow nested loops in your second query but
there is only one in the first query.
So, in you last query, when update one row in your table authors, you have
to scan the table 'authorsx' one time. this is just like how the cross join
works:
From the SQL Server Books Online:
A cross join that does not have a WHERE clause produces the Cartesian
product of the tables involved in the join. The size of a Cartesian product
result set is the number of rows in the first table multiplied by the
number of rows in the second table. This is an example of a Transact-SQL
cross join:
USE pubs
SELECT au_fname, au_lname, pub_name
FROM authors CROSS JOIN publishers
ORDER BY au_lname DESC
The result set contains 184 rows (authors has 23 rows and publishers has 8;
23 multiplied by 8 equals 184).
Hope this helps.
Best regards
Baisong Wei
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. Thanks.|||Thanks for the response. I'll have to study this to understand why, when I
say "Inner Join", I get a cross join. I believe you, I just have to figure
it out in order to understand it.
Thanks again to all for helping me with the detailed responses.
David Walker|||I think I can sum it up for you quite simply:
If you INNER JOIN but fail to provide a correlation (e.g. this thing in this
table = this thing in that table), the server has no way of knowing which
row in each table belongs with each row in the other table. So instead it
does the only option left: It pairs EVERY row of each table with EVERY row
of the other table.
Does that make sense?
"DW" <None> wrote in message news:uv8SpTI$DHA.1456@.TK2MSFTNGP09.phx.gbl...
> Thanks for the response. I'll have to study this to understand why, when
I
> say "Inner Join", I get a cross join. I believe you, I just have to
figure
> it out in order to understand it.
> Thanks again to all for helping me with the detailed responses.
> David Walker|||Could you guys, who have been so helpful, please explain how the cross join
comes about in the "derived" case?
It's clearly there, I just want to know how to align my thinking so that
it's obvious to me!!!
Thanks.
David Walker|||"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in
news:eIwyUbI$DHA.392@.TK2MSFTNGP12.phx.gbl:
> I think I can sum it up for you quite simply:
> If you INNER JOIN but fail to provide a correlation (e.g. this thing
> in this table = this thing in that table), the server has no way of
> knowing which row in each table belongs with each row in the other
> table. So instead it does the only option left: It pairs EVERY row
> of each table with EVERY row of the other table.
> Does that make sense?
>
Yes, that helps. It's becoming clearer as I read these responses.
So where I have:
Set tblA.field = derivedtbl.field from (inner join) as derivedtbl
...doesn't "correlate" the Set statement?
David Walker|||"DW" <None> wrote in message news:#7CLN2I$DHA.2072@.TK2MSFTNGP11.phx.gbl...
> Set tblA.field = derivedtbl.field from (inner join) as derivedtbl
> ...doesn't "correlate" the Set statement?
No; roughly translated to English, that says ONLY: "Set the value of
tblA.field to the value of derivedtbl.field."
What you want it to say is: "Set the value of tblA.field to the value of
derivedtbl.field when the row to update in tblA is identified as a row that
corresponds to a row in derivedtbl." (or even more precisely, "Update the
value of tblA.field only when it is identified as a corresponding row to one
in derivedtbl, and use only the value from derivedtbl.field from that
corresponding row to update tblA.field.")
In other words, the first statement gives the database no indication
about how or in what cases to update tblA.field; so it does so for every row
of tblA, and it does so once for every value it sees in derivedtbl. It has
no other choice. What you want to do instead is something like:
UPDATE tblA
SET tblA.field = derivedtbl.field
FROM TblA
INNER JOIN (SELECT field, id FROM ...) derivedtbl ON TblA.id =derivedtbl.id
In this case, you're saying that for every row in derivedtbl, the server
should look for the row (or those rows) in tblA where the value of the
column "id" is the same as the value of the column "id" in derivedtbl. So
now the server knows which rows match and can therefore update the correct
rows, only once, rather than every row, for every possible value.|||AH! That helps. I could use a derived table, but I still need to join
that derived table with the original table.
The first example that doesn't use a derived table works fine, but I
certainly want to understand the differences in the approaches.
Some better tools than Enterprise Manager would also be nice ;-) where
I could say what you just said, and have it implemented.
Thanks to all of you for the help.
David Walker
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in
news:OI7KL$I$DHA.2512@.TK2MSFTNGP11.phx.gbl:
> "DW" <None> wrote in message
> news:#7CLN2I$DHA.2072@.TK2MSFTNGP11.phx.gbl...
>> Set tblA.field = derivedtbl.field from (inner join) as derivedtbl
>> ...doesn't "correlate" the Set statement?
> No; roughly translated to English, that says ONLY: "Set the value
> of
> tblA.field to the value of derivedtbl.field."
> What you want it to say is: "Set the value of tblA.field to the
> value of
> derivedtbl.field when the row to update in tblA is identified as a row
> that corresponds to a row in derivedtbl." (or even more precisely,
> "Update the value of tblA.field only when it is identified as a
> corresponding row to one in derivedtbl, and use only the value from
> derivedtbl.field from that corresponding row to update tblA.field.")
> In other words, the first statement gives the database no
> indication
> about how or in what cases to update tblA.field; so it does so for
> every row of tblA, and it does so once for every value it sees in
> derivedtbl. It has no other choice. What you want to do instead is
> something like:
> UPDATE tblA
> SET tblA.field = derivedtbl.field
> FROM TblA
> INNER JOIN (SELECT field, id FROM ...) derivedtbl ON TblA.id => derivedtbl.id
> In this case, you're saying that for every row in derivedtbl, the
> server should look for the row (or those rows) in tblA where the value
> of the column "id" is the same as the value of the column "id" in
> derivedtbl. So now the server knows which rows match and can
> therefore update the correct rows, only once, rather than every row,
> for every possible value.
>
>|||P.S. The Update query (the fast one that works):
Update PositionsEOM
Set MktVal = PM.Price * PositionsEOM.Quantity_ASD
From PositionsEOM Inner Join [Price Master] As PM On
PositionsEOM.Fund_ID = PM.Fund_ID And
PositionsEOM.Position_Date = PM.Price_Date
doesn't clearly say that the rows should "correspond", does it?
David Walker
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in
news:OI7KL$I$DHA.2512@.TK2MSFTNGP11.phx.gbl:
> "DW" <None> wrote in message
> news:#7CLN2I$DHA.2072@.TK2MSFTNGP11.phx.gbl...
>> Set tblA.field = derivedtbl.field from (inner join) as derivedtbl
>> ...doesn't "correlate" the Set statement?
> No; roughly translated to English, that says ONLY: "Set the value
> of
> tblA.field to the value of derivedtbl.field."
> What you want it to say is: "Set the value of tblA.field to the
> value of
> derivedtbl.field when the row to update in tblA is identified as a row
> that corresponds to a row in derivedtbl." (or even more precisely,
> "Update the value of tblA.field only when it is identified as a
> corresponding row to one in derivedtbl, and use only the value from
> derivedtbl.field from that corresponding row to update tblA.field.")
> In other words, the first statement gives the database no
> indication
> about how or in what cases to update tblA.field; so it does so for
> every row of tblA, and it does so once for every value it sees in
> derivedtbl. It has no other choice. What you want to do instead is
> something like:
> UPDATE tblA
> SET tblA.field = derivedtbl.field
> FROM TblA
> INNER JOIN (SELECT field, id FROM ...) derivedtbl ON TblA.id => derivedtbl.id
> In this case, you're saying that for every row in derivedtbl, the
> server should look for the row (or those rows) in tblA where the value
> of the column "id" is the same as the value of the column "id" in
> derivedtbl. So now the server knows which rows match and can
> therefore update the correct rows, only once, rather than every row,
> for every possible value.
>|||"DW" <None> wrote in message news:#CwLzmL$DHA.2800@.tk2msftngp13.phx.gbl...
> P.S. The Update query (the fast one that works):
> Update PositionsEOM
> Set MktVal = PM.Price * PositionsEOM.Quantity_ASD
> From PositionsEOM Inner Join [Price Master] As PM On
> PositionsEOM.Fund_ID = PM.Fund_ID And
> PositionsEOM.Position_Date = PM.Price_Date
> doesn't clearly say that the rows should "correspond", does it?
Sure it does...
General UPDATE syntax:
UPDATE X
...
FROM X
...
X is the same table in both places... So in your query, you're actually
saying, "First join PositionsEOM and [Price Master] where the Fund_ID
columns are equal to each other and the Position_Date is equal to the
Price_Date. Then, update those rows in PositionsEOM that satisfied the
join."
As for better tools than Enterprise Manager, maybe you should give Query
Analyzer a try.|||Yes, I use QA also. I want to be able to drag and drop two tables, link
the fields with an inner join, then put huge parentheses around those
two tables and the iner join, then drag another table in there and make
an outer join. Can any tools do that?
Thanks.
David
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in
news:#ZBeOqL$DHA.2800@.tk2msftngp13.phx.gbl:
> "DW" <None> wrote in message
> news:#CwLzmL$DHA.2800@.tk2msftngp13.phx.gbl...
>> P.S. The Update query (the fast one that works):
>> Update PositionsEOM
>> Set MktVal = PM.Price * PositionsEOM.Quantity_ASD
>> From PositionsEOM Inner Join [Price Master] As PM On
>> PositionsEOM.Fund_ID = PM.Fund_ID And
>> PositionsEOM.Position_Date = PM.Price_Date
>> doesn't clearly say that the rows should "correspond", does it?
> Sure it does...
> General UPDATE syntax:
> UPDATE X
> ...
> FROM X
> ...
> X is the same table in both places... So in your query, you're
> actually
> saying, "First join PositionsEOM and [Price Master] where the Fund_ID
> columns are equal to each other and the Position_Date is equal to the
> Price_Date. Then, update those rows in PositionsEOM that satisfied
> the join."
> As for better tools than Enterprise Manager, maybe you should give
> Query
> Analyzer a try.
>
>|||DW (None) writes:
> Regarding this join:
> Update PositionsEOM
> Set MktVal = Derived.MktVal From
> (Select PM.Price * PositionsEOM.Quantity_ASD As MktVal
> From PositionsEOM Inner Join [Price Master] As PM On
> PositionsEOM.Fund_ID = PM.Fund_ID And
> PositionsEOM.Position_Date = PM.Price_Date) As Derived
> Why is this a cross join? It has the phrase "Inner Join" in it (with
> two conditions, hence the AND). I'm confused...
The derived table has an inner join, but you have hidden cross join
between PositionsEOM and the derived table. (The fact that PositionsEOM
appears in the derived table is irrelevant.)
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Hi David,
Thanks for using your update.
Well, as far as I know, on SQL Server side ,there is no such an amazing
tool for us to use.
For design database, Microsoft Viso 2000 could be a good tool, but I could
not remember any tool that could create queries like that.
Thanks.
Best regards
Baisong Wei
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. Thanks.|||"DW" <None> wrote in message news:eDZuU4L$DHA.808@.TK2MSFTNGP12.phx.gbl...
> Yes, I use QA also. I want to be able to drag and drop two tables, link
> the fields with an inner join, then put huge parentheses around those
> two tables and the iner join, then drag another table in there and make
> an outer join. Can any tools do that?
I know this is going to sound strange... But you actually DON'T want to
do that. If you were to use a graphical tool like that, you would never
learn the reasons why, for instance, your first query and second query
weren't doing the same thing. Because you'd never see them, just some
pretty pictures on the screen. Maybe someday there will be a tool like
you're talking about (there's not right now, as far as I know), but when
that day comes you will have been typing this stuff for so long that you'll
no longer want it.|||Is this example:
USE pubs
GO
SELECT au_lname, au_fname
FROM authors
WHERE exists
(SELECT *
FROM publishers
WHERE authors.city = publishers.city)
GO
... a correlated subquery? In the subquery, you don't specify the
authors table, and the subquery won't work standing alone. Is there a
join in here?
Thanks.
David Walker|||Since everyone here has been so helpful, is there a way to do this:
Update PositionsEOM
Set Sec_Type = AM.Sec_Type
From PositionsEOM Inner Join [Asset Master] As AM On
PositionsEOM.Fund_ID = AM.Fund_ID
Without a Join?
Thanks.
David Walker|||You could, but you're probably better off with a JOIN... but here's how
anyway:
Update PositionsEOM
Set Sec_Type = (SELECT AM.Sec_Type FROM [Asset Master] As AM WHERE
PositionsEOM.Fund_ID = AM.Fund_ID)
This assumes that there is exactly one row in [Asset Master] for each row in
PositionsEOM. If there are less rows, you will end up with NULL Sec_Type,
and if there are more you will get the following error: "Subquery returned
more than 1 value. This is not permitted when the subquery follows =, !=, <,
<= , >, >= or when the subquery is used as an expression."
So, you're much better off with a JOIN.
"DW" <None> wrote in message news:eszH8uW$DHA.2308@.tk2msftngp13.phx.gbl...
> Since everyone here has been so helpful, is there a way to do this:
> Update PositionsEOM
> Set Sec_Type = AM.Sec_Type
> From PositionsEOM Inner Join [Asset Master] As AM On
> PositionsEOM.Fund_ID = AM.Fund_ID
> Without a Join?
> Thanks.
> David Walker|||The Fund_ID is the primary key, and dups are not allowed. But I'll
leave the Join. There are some missing assets, so I occasionally get
nulls for the security type. (We get data from an outside source, and
there are some referential integrity problems. We can't enforce
referential integrity; I wish we could, but we need to accept
transactions even if the asset doesn't exist, and so on.)
So the subquery is an "expression" in your example, and it magically
knows which rows of PositionsEOM go with which rows of Asset Master?
I have written SQL statements and views, mostly with Enterprise Manager,
but now I want to understand them 100%.
Thanks so much for the help. I ordered two books that were recommended,
so when I'm done with them, all of this should be crystal clear!
David Walker|||"DW" <None> wrote in message news:u3JrcDY$DHA.3536@.tk2msftngp13.phx.gbl...
> So the subquery is an "expression" in your example, and it magically
> knows which rows of PositionsEOM go with which rows of Asset Master?
The subquery in the example is referred to as a "correlated subquery"
because the inner query correlates to the outer query... Nothing magical
about it ;) It looks for tables referenced in the outer query and if
they're referenced in the inner query then their used (and therefore
"correlated").
As for the RI, at least in the JOIN we've been considering here you have
no reason not to be able to enforce a foreign key from [Asset Master]
pointing to the Fund_ID primary key on PositionsEOM...
Anyway, good luck w/ those books, I'm sure you'll enjoy them!|||Adam Machanic (amachanic@.air-worldwide.nospamallowed.com) writes:
> This assumes that there is exactly one row in [Asset Master] for each
> row in PositionsEOM. If there are less rows, you will end up with NULL
> Sec_Type, and if there are more you will get the following error:
> "Subquery returned more than 1 value. This is not permitted when the
> subquery follows =, !=, <,
><= , >, >= or when the subquery is used as an expression."
> So, you're much better off with a JOIN.
Hm, that is more the argument for the subquery.
As I think I said earlier, ANSI SQL does not have the JOIN syntax. That
is an extention which is peculiar to Microsoft, Sybase and I think Informix.
The advocates of the subquery thinks the JOIN is bad, because if your
join conditions are too few, you will update the same row many times,
but not notice, execpt that performance is bad.
Myself, I've used the JOIN syntax for many years, and I prefer it. Since
it is the same syntax as for SELECT, it's the same concept to understand.
Also, it's easier to test what rows you match, by rewriting the UPDATE into
a SELECT.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns949D2D61EA6BYazorman@.127.0.0.1...
> As I think I said earlier, ANSI SQL does not have the JOIN syntax. That
> is an extention which is peculiar to Microsoft, Sybase and I think
Informix.
Not sure what you mean here. AFAIK, the SQL-92 standard has JOIN as
part of the SELECT syntax.|||Adam Machanic (amachanic@.air-worldwide.nospamallowed.com) writes:
> "Erland Sommarskog" <sommar@.algonet.se> wrote in message
> news:Xns949D2D61EA6BYazorman@.127.0.0.1...
>> As I think I said earlier, ANSI SQL does not have the JOIN syntax. That
>> is an extention which is peculiar to Microsoft, Sybase and I think
> Informix.
> Not sure what you mean here. AFAIK, the SQL-92 standard has JOIN as
> part of the SELECT syntax.
Yes, but this applied to UPDATE (and DELETE) where ANSI SQL does not
have FROM (and thus not JOIN).
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp