Query longer in SP: joins

Showing posts with label joins. Show all posts

Wednesday, March 28, 2012

Query problem ...

I'm creating a query that has a number of joins and I'm getting duplicate
values displayed nested as follows:
<Root>
<Paint StorePaintID="1">
<Brush StoreBrushID="10001" StoreBrokerageID="113"
receivedDate="2004-01-26T00:00:00" EstimatedValue="1.2">
<GlueLocation GlueLocationID="5" GlueLocation="New York, NY"
StoreType1="Housekeeping" StoreType2="Housekeeping"
StoreType3="Structured Product">
<CurrencyType CurrencyType="U.S. Dollar">
<Storywriter StorywriterPersonID="283">
<Artist ArtistPersonID="810">
<Tree TreeID="175">
<TreeType TreeDescription="RC" TreeCode="AB">
<ProductType ProductID="16" ProductDescription="Professional
Liability - Financial Institution D&O"
ProductCode="FDO">
<StoreUnit StoreUnit="1" LaHouseBrushStatus="Type A"
LaHouseBrushDate="2004-04-14T16:13:05.873"
LaHouseBrushStatusID="4795">
<Comments CommentText="sdagsadgsda"
commentDate="2004-01-26T00:00:00">
<House RBIStatusID="1926" statusDate="2004-02-19T00:00:00"
description="Red" />
<House RBIStatusID="1926" statusDate="2004-02-19T00:00:00"
description="Red" />
<House RBIStatusID="4795" statusDate="2004-04-14T16:13:05.873"
description="Green" />
<House RBIStatusID="4795" statusDate="2004-04-14T16:13:05.873"
description="Green" />
</Comments>
</StoreUnit>
</ProductType>
</TreeType>
</Tree>
</Artist>
</Storywriter>
</CurrencyType>
</GlueLocation>
</Brush>
</Paint>
<Paint StorePaintID="2">
<Brush StoreBrushID="10002" StoreBrokerageID="113"
receivedDate="2004-01-26T00:00:00" effectiveDate="2004-03-31T00:00:00"
EstimatedValue="3.1">
<GlueLocation GlueLocationID="5" GlueLocation="New York, NY"
StoreType1="Housekeeping" StoreType2="Structured Product"
StoreType3="Structured Product">
<CurrencyType CurrencyType="Yen">
<Storywriter StorywriterPersonID="746">
<Artist ArtistPersonID="810">
<Tree TreeID="176">
<TreeType TreeDescription="T1" TreeCode="CA">
<ProductType ProductID="9" ProductDescription="Professional
Liability - Commercial D&O" ProductCode="CDO">
<StoreUnit StoreUnit="1" LaHouseBrushStatus="Typce B"
LaHouseBrushDate="2004-03-24T08:45:56.107"
LaHouseBrushStatusID="3249">
<Comments CommentText="g" commentDate="2004-01-26T00:00:00">
<House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
description="Red" />
</Comments>
<Comments CommentText="asfsdaf"
commentDate="2004-03-08T13:54:25.200">
<House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
description="Red" />
</Comments>
<Comments CommentText="asdfasdg"
commentDate="2004-03-19T13:59:07.450">
<House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
description="Red" />
</Comments>
<Comments CommentText="sdagasdg"
commentDate="2004-03-24T13:47:11.733">
<House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
description="Red" />
</Comments>
<Comments CommentText="asgasg"
commentDate="2004-03-26T13:16:44.797">
<House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
description="Red" />
</Comments>
<Comments CommentText="asdgasd"
commentDate="2004-03-26T14:52:12.607">
<House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
description="Red" />
</Comments>
<Comments CommentText="gasdgasd"
commentDate="2004-03-29T12:18:06.480">
<House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
description="Red" />
</Comments>
<Comments CommentText="gasdgasd"
commentDate="2004-03-31T10:15:17.513">
<House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
description="Red" />
</Comments>
<Comments CommentText="asdgsdag" commentDate="2004-01-26T00:00:00">
<House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
description="Red" />
</Comments>
<Comments CommentText="sadgsadgsadgsad"
commentDate="2004-03-08T13:54:25.200">
<House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
description="Red" />
</Comments>
<Comments CommentText="gasdg"
commentDate="2004-03-19T13:59:07.450">
<House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
description="Red" />
</Comments>
<Comments CommentText="sdgasdg"
commentDate="2004-03-24T13:47:11.733">
<House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
description="Red" />
</Comments>
<Comments CommentText="asg" commentDate="2004-03-26T13:16:44.797">
<House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
description="Red" />
</Comments>
<Comments CommentText="asdgasdgsadg"
commentDate="2004-03-26T14:52:12.607">
<House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
description="Red" />
</Comments>
<Comments CommentText="sadgs"
commentDate="2004-03-29T12:18:06.480">
<House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
description="Red" />
</Comments>
<Comments CommentText="adgsadgsdag"
commentDate="2004-03-31T10:15:17.513">
<House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
description="Red" />
</Comments>
</StoreUnit>
</ProductType>
</TreeType>
</Tree>
</Artist>
</Storywriter>
</CurrencyType>
</GlueLocation>
</Brush>
</Paint>
</Root>
since I am using Biztalk I must use FOR XML AUTO for the query, could
someone please explain how I can get rid of the duplication of values?
The duplication on the leaf nodes indicate that you get two rows in your SQL
query.
Can you rewrite the SQL part of the query to only result in one row (e.g.,
using distinct)? Or would that remove duplicates that you want to have
exposed?
Thanks
Michael
"Keith Adler" <KeithAdler@.discussions.microsoft.com> wrote in message
news:9B608829-A601-44C8-B361-FA51530561BB@.microsoft.com...
> I'm creating a query that has a number of joins and I'm getting duplicate
> values displayed nested as follows:
> <Root>
> <Paint StorePaintID="1">
> <Brush StoreBrushID="10001" StoreBrokerageID="113"
> receivedDate="2004-01-26T00:00:00" EstimatedValue="1.2">
> <GlueLocation GlueLocationID="5" GlueLocation="New York, NY"
> StoreType1="Housekeeping" StoreType2="Housekeeping"
> StoreType3="Structured Product">
> <CurrencyType CurrencyType="U.S. Dollar">
> <Storywriter StorywriterPersonID="283">
> <Artist ArtistPersonID="810">
> <Tree TreeID="175">
> <TreeType TreeDescription="RC" TreeCode="AB">
> <ProductType ProductID="16" ProductDescription="Professional
> Liability - Financial Institution D&O"
> ProductCode="FDO">
> <StoreUnit StoreUnit="1" LaHouseBrushStatus="Type A"
> LaHouseBrushDate="2004-04-14T16:13:05.873"
> LaHouseBrushStatusID="4795">
> <Comments CommentText="sdagsadgsda"
> commentDate="2004-01-26T00:00:00">
> <House RBIStatusID="1926" statusDate="2004-02-19T00:00:00"
> description="Red" />
> <House RBIStatusID="1926" statusDate="2004-02-19T00:00:00"
> description="Red" />
> <House RBIStatusID="4795" statusDate="2004-04-14T16:13:05.873"
> description="Green" />
> <House RBIStatusID="4795" statusDate="2004-04-14T16:13:05.873"
> description="Green" />
> </Comments>
> </StoreUnit>
> </ProductType>
> </TreeType>
> </Tree>
> </Artist>
> </Storywriter>
> </CurrencyType>
> </GlueLocation>
> </Brush>
> </Paint>
> <Paint StorePaintID="2">
> <Brush StoreBrushID="10002" StoreBrokerageID="113"
> receivedDate="2004-01-26T00:00:00" effectiveDate="2004-03-31T00:00:00"
> EstimatedValue="3.1">
> <GlueLocation GlueLocationID="5" GlueLocation="New York, NY"
> StoreType1="Housekeeping" StoreType2="Structured Product"
> StoreType3="Structured Product">
> <CurrencyType CurrencyType="Yen">
> <Storywriter StorywriterPersonID="746">
> <Artist ArtistPersonID="810">
> <Tree TreeID="176">
> <TreeType TreeDescription="T1" TreeCode="CA">
> <ProductType ProductID="9" ProductDescription="Professional
> Liability - Commercial D&O" ProductCode="CDO">
> <StoreUnit StoreUnit="1" LaHouseBrushStatus="Typce B"
> LaHouseBrushDate="2004-03-24T08:45:56.107"
> LaHouseBrushStatusID="3249">
> <Comments CommentText="g" commentDate="2004-01-26T00:00:00">
> <House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
> description="Red" />
> </Comments>
> <Comments CommentText="asfsdaf"
> commentDate="2004-03-08T13:54:25.200">
> <House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
> description="Red" />
> </Comments>
> <Comments CommentText="asdfasdg"
> commentDate="2004-03-19T13:59:07.450">
> <House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
> description="Red" />
> </Comments>
> <Comments CommentText="sdagasdg"
> commentDate="2004-03-24T13:47:11.733">
> <House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
> description="Red" />
> </Comments>
> <Comments CommentText="asgasg"
> commentDate="2004-03-26T13:16:44.797">
> <House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
> description="Red" />
> </Comments>
> <Comments CommentText="asdgasd"
> commentDate="2004-03-26T14:52:12.607">
> <House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
> description="Red" />
> </Comments>
> <Comments CommentText="gasdgasd"
> commentDate="2004-03-29T12:18:06.480">
> <House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
> description="Red" />
> </Comments>
> <Comments CommentText="gasdgasd"
> commentDate="2004-03-31T10:15:17.513">
> <House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
> description="Red" />
> </Comments>
> <Comments CommentText="asdgsdag" commentDate="2004-01-26T00:00:00">
> <House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
> description="Red" />
> </Comments>
> <Comments CommentText="sadgsadgsadgsad"
> commentDate="2004-03-08T13:54:25.200">
> <House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
> description="Red" />
> </Comments>
> <Comments CommentText="gasdg"
> commentDate="2004-03-19T13:59:07.450">
> <House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
> description="Red" />
> </Comments>
> <Comments CommentText="sdgasdg"
> commentDate="2004-03-24T13:47:11.733">
> <House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
> description="Red" />
> </Comments>
> <Comments CommentText="asg" commentDate="2004-03-26T13:16:44.797">
> <House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
> description="Red" />
> </Comments>
> <Comments CommentText="asdgasdgsadg"
> commentDate="2004-03-26T14:52:12.607">
> <House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
> description="Red" />
> </Comments>
> <Comments CommentText="sadgs"
> commentDate="2004-03-29T12:18:06.480">
> <House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
> description="Red" />
> </Comments>
> <Comments CommentText="adgsadgsdag"
> commentDate="2004-03-31T10:15:17.513">
> <House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
> description="Red" />
> </Comments>
> </StoreUnit>
> </ProductType>
> </TreeType>
> </Tree>
> </Artist>
> </Storywriter>
> </CurrencyType>
> </GlueLocation>
> </Brush>
> </Paint>
> </Root>
> since I am using Biztalk I must use FOR XML AUTO for the query, could
> someone please explain how I can get rid of the duplication of values?
|||The problem is that i have many joins on this query, and each join results in
slightly more data being created. Some of the joined tables have n records.
It's a mess because I cannot figure out how to predict how the XML AUTO will
layout the elements. Unfortunately, because I'm using BizTalk 2004 I cannot
use XML EXPLICIT.
"Michael Rys [MSFT]" wrote:

> The duplication on the leaf nodes indicate that you get two rows in your SQL
> query.
> Can you rewrite the SQL part of the query to only result in one row (e.g.,
> using distinct)? Or would that remove duplicates that you want to have
> exposed?
> Thanks
> Michael
> "Keith Adler" <KeithAdler@.discussions.microsoft.com> wrote in message
> news:9B608829-A601-44C8-B361-FA51530561BB@.microsoft.com...
>
>
|||Auto mode works as follows:
It will generate a new element nesting for every source table/view, where
the nesting is determined left to right according to the order of the
columns in the select statement.
E.g.,
select T2.x, T1.x, T1.y, T2.x, T3.a
...
for xml auto
will result in
<T2 x=".." y="..">
<T1 x=".." y="..">
<T3 a=".."/>
</T1>
</T2>
In principle, every row will be mapped to a top-level element (starting at
T2), unless we can identify, that row number X is somehow related to row
number X-1. The way we do this is that we compare the values of the row with
the values of the previous row (except for image and (n)text columns).
So if you have
T2.x T1.x T1.y T2.x T3.a
1 3 4 2 5
1 3 4 3 5
1 3 5 3 6
1 3 5 3 6
We will first look at the values on the first row. Since it is the first
row. we will output:
<T2 x="1" y="2">
<T1 x="3" y="4">
<T3 a="5"/>
Then we look at the second row and see that T2.y has changed the value. Thus
the heuristic assumes a new T2 element and we close the old ones and open a
new one:
</T1>
<T2>
<T2 x="1" y="2">
<T1 x="3" y="4">
<T3 a="5"/>
The third row stays the same for the values of T2, but T1.y changes. So we
infer that we are still in the same T2 but have a new T1. So we produce:
</T1>
<T1 x="3" y="5">
<T3 a="6"/>
The fourth and last row is exactly the same as the previous one. However
since T3 is a leaf element, we will repeat it and then close the elements.
<T3 a="6"/>
</T1>
</T3>
In conclusion, this means that you want to always order by the key
properties of every level to get the right ordering and that you want to
eliminate duplicate rows for the leafs (T3.a in the example above).
HTH
Michael
"Keith Adler" <KeithAdler@.discussions.microsoft.com> wrote in message
news:491EE50B-21E0-4F98-A1DE-358D8441086A@.microsoft.com...[vbcol=seagreen]
> The problem is that i have many joins on this query, and each join results
> in
> slightly more data being created. Some of the joined tables have n
> records.
> It's a mess because I cannot figure out how to predict how the XML AUTO
> will
> layout the elements. Unfortunately, because I'm using BizTalk 2004 I
> cannot
> use XML EXPLICIT.
> "Michael Rys [MSFT]" wrote:

Wednesday, March 21, 2012

query performance issue of Sql server 2005 Mobile edition on device

Executing a select query with left outer joins etc takes .53 seconds to execute on sqlce 2.0.

Same query on sql 2005 mobile ed. takes 11 minutes .

on database having same data.

Sample query

SELECT routes.location,routes.equipment_type, routes.contract_type,

routes.maintenance_interval,routes.bank_description,routes.Unit_Des,

routes.Unit_no,max(task_last_completed.date_completed)as date1,min(case when

task_last_completed.due_date is NULL then getdate()-1 else due_date end) as

due_date FROM routes left outer join tasks on tasks.model = routes.model and

tasks.eqtyp = routes.equipment_type inner join task_by_contract_type on

tasks.task_id = task_by_contract_type.task_id and

task_by_contract_type.contract_type = routes.contract_type and

task_by_contract_type.model = routes.model left outer join

task_last_completed on routes.unit_no = task_last_completed.equipment_Id and

tasks.task_Id = task_last_completed.task_Id WHERE routes.location LIKE

'S153825-01%' group by

routes.location,routes.equipment_type,routes.contract_type,routes.maintenanc

e_interval,routes.bank_description,routes.unit_des,routes.unit_no ORDER BY

routes.location, routes.bank_description, routes.Unit_Des

WHY?

Hi Vijay,

Can you answer the following questions to help us understand your scenario better:

What is the configuration of the device (make/OS version/processor type/processor speed/total memory/available memory for use at the time query was run) on which the query was run against SQL Mobile database?|||

Hi Vijay,

Can you answer the following questions to help us understand your scenario better:

Answer as follows:

1- verizon/Windows Mobile 5.0/os5.1.1700(build 14354.0.1.1)/PXA270-416MHz/49.45MB/~23MB

2 - Yes

3 - Yes as existing schema of Sqlce 2.0 is upgraded to sql mobile 5.0 using upgrade.exe

4 - 9.66 MB sql ce 2.0 and 8.09 MB sql mobile

5 - Running the query through query analyser.

6. can we set buffer pool etc. for query analyser on device.

One thing i have identified later in our datbase it hase number of duplicate records but even then sql ce performance is comparative very high.

Even after deleting duplicate records difference and read that sql mobile deals with the outer joins differently as compared to sqlce 2.0 but even then performance is the issue,

Sql ce 2.0 takes 0.53 Minutes

Sql Mobile takes 1.40 Minutes

Thanks

vijay

Query Performance

Hello all-

I have a general question. I realize that joins are most of the time better than subqueries. Now that we have the derived tables, if I put the subquery as a derived table and join it with the other tables, will it make any difference in the performance?

eg:

SELECT EmpId
FROM Emp
WHERE EmpSalary = (SELECT MAX(EmpSalary)
FROM Emp Emp2)

Vs

SELECT Emp1.EmpId
FROM Emp Emp1
INNER JOIN (SELECT MAX(EmpSalary) FROM Emp) Emp2
ON Emp1.EmpSalary = Emp2.EmpSalary

(I know that there are better ways of writing this query, sorting my empsalary and getting the top 1 etc. This is just an example to demostrate my question.)

Appreciate your time
ThnxDid you do a show plan on the 2?

That will show you...but I don't think you'll see a difference...

It depeneds more on the number of rows you have and the indexing...

Monday, March 12, 2012

Query Parallelism (maxdop)

I have 4 processor SQL server. When I run this query in query analyzer
(two table joins with w

ly aggregation approx 500,000 rows each) ...
it takes only 7 seconds... when I run it in a stored procedure... it
takes about 60 minutes. I dont see parallelism in the execution plan of
SP... I have already tried hint maxdop... it did'nt work... Im not
sure what I might be running into'
Any help is greatly appreciated...|||Try using WITH RECOMPILE on your stored procedure just as an initial test.
It may be getting a less than optimal execution plan. See
http://www.dbtalk.net/microsoft-pub...ter-203396.html
for some thoughts.
I'm running into some similar issues with ADO taking ludicrously long while
Query Analyzer goes quickly. Haven't tracked down the exact cause yet,
though it appears many other people have had the same issue.
Mike
"zomer" <noneee@.gmail.com> wrote in message
news:1145384015.190289.310680@.e56g2000cwe.googlegroups.com...
>I have 4 processor SQL server. When I run this query in query analyzer
> (two table joins with w

ly aggregation approx 500,000 rows each) ...
> it takes only 7 seconds... when I run it in a stored procedure... it
> takes about 60 minutes. I dont see parallelism in the execution plan of
> SP... I have already tried hint maxdop... it did'nt work... Im not
> sure what I might be running into'
> Any help is greatly appreciated...
>|||I think what you're seeing is data and/or plan caching - and that's by
design. When a query first enters SQL Server, a plan is created and cached.
This takes time. Upon subsequent execution, the original plan is (likely)
used, and this goes faster. However, any data that were read by the
just-executed query are likely now in data cache. Thus, the next time the
query is run, it's getting the data from cache - not from disk. The more
RAM you have, the bigger the cache you have - and the faster (in most cases)
that SQL Server will run.
If you want a pure apples to apples comparison, run the following before
each test:
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
This flushes the proc and data caches, respectively. Now, you get a fresh
plan - and you get your data from disk. A subsequent run without running
these DBCC's should go significantly faster.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
news:uTK$ihxYGHA.1192@.TK2MSFTNGP03.phx.gbl...
Tom -
Are there other things besides the parameter sniffing that could cause the
performance difference?
I'm running into a similar problem, except here's what's happening:
1. I have SQL Profiler on in production.
2. I catch the "slow" procedure
3. Immediately I copy and paste the text into Query Analyzer and execute the
query on the same production server -- and it executes quickly.
Same parameters. Same execution plan I would think (I'm not seeing any
recompiles). The parameter sniffing discussion made sense but it doesn't
seem to fit this scenario since the procedure is already compiled and I'm
executing it with the same paramters that made it run slow from the app.
Thanks for any help,
Mike
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ucLVCUxYGHA.3880@.TK2MSFTNGP04.phx.gbl...
> Google "parameter sniffing".
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "zomer" <noneee@.gmail.com> wrote in message
> news:1145384015.190289.310680@.e56g2000cwe.googlegroups.com...
> I have 4 processor SQL server. When I run this query in query analyzer
> (two table joins with w

Friday, March 9, 2012

query optimizer

Does anyone know if transact sql joins will work faster than ansii
standard: ex:
ANSI standard is this
Select field1, field2
>From table1, table2
Where table1ID=table2ID
Transact SQL is this
Select field1, field2
>From table1
Join table2 on table1ID=table2ID
Does the optimizer see these differently on a table with a lot of
joins?Both of those alternatives are standard ANSI code, both work find in
Transact-SQL, and SQL Server's optimizer will produce the same
execution plan for both.
Roy Harvey
Beacon Falls, CT
On Wed, 13 Jun 2007 12:23:04 -0700, Kristina <KristinaDBA@.gmail.com>
wrote:
>Does anyone know if transact sql joins will work faster than ansii
>standard: ex:
>ANSI standard is this
>Select field1, field2
>>From table1, table2
>Where table1ID=table2ID
>Transact SQL is this
>Select field1, field2
>>From table1
>Join table2 on table1ID=table2ID
>Does the optimizer see these differently on a table with a lot of
>joins?|||As Roy said, they will produce the same query plan. HOWEVER, I would advise
against using the 'old-style' joins if they are outer because they will not
be supported in future editions of SQL Server.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1181762584.269445.155360@.q19g2000prn.googlegroups.com...
> Does anyone know if transact sql joins will work faster than ansii
> standard: ex:
> ANSI standard is this
> Select field1, field2
>>From table1, table2
> Where table1ID=table2ID
> Transact SQL is this
> Select field1, field2
>>From table1
> Join table2 on table1ID=table2ID
> Does the optimizer see these differently on a table with a lot of
> joins?
>|||On Jun 14, 8:26 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> As Roy said, they will produce the same query plan. HOWEVER, I would advise
> against using the 'old-style' joins if they are outer because they will not
> be supported in future editions of SQL Server.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Kristina" <Kristina...@.gmail.com> wrote in message
> news:1181762584.269445.155360@.q19g2000prn.googlegroups.com...
>
> > Does anyone know if transact sql joins will work faster than ansii
> > standard: ex:
> > ANSI standard is this
> > Select field1, field2
> >>From table1, table2
> > Where table1ID=table2ID
> > Transact SQL is this
> > Select field1, field2
> >>From table1
> > Join table2 on table1ID=table2ID
> > Does the optimizer see these differently on a table with a lot of
> > joins... Hide quoted text -
> - Show quoted text -
Good point, I read that =* isn't supported in 2005. is that correct?|||It is indeed correct. Try this in SQL 2k5:
select *
from sys.objects o, sys.indexes c
where o.object_id *= c.object_id
and you get this informative error:
Msg 4147, Level 15, State 1, Line 3
The query uses non-ANSI outer join operators ("*=" or "=*"). To run this
query without modification, please set the compatibility level for current
database to 80 or lower, using stored procedure sp_dbcmptlevel. It is
strongly recommended to rewrite the query using ANSI outer join operators
(LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server,
non-ANSI join operators will not be supported even in backward-compatibility
modes.
TheSQLGuru
President
Indicium Resources, Inc.
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1181863946.324696.186190@.o11g2000prd.googlegroups.com...
> On Jun 14, 8:26 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
>> As Roy said, they will produce the same query plan. HOWEVER, I would
>> advise
>> against using the 'old-style' joins if they are outer because they will
>> not
>> be supported in future editions of SQL Server.
>> --
>> TheSQLGuru
>> President
>> Indicium Resources, Inc.
>> "Kristina" <Kristina...@.gmail.com> wrote in message
>> news:1181762584.269445.155360@.q19g2000prn.googlegroups.com...
>>
>> > Does anyone know if transact sql joins will work faster than ansii
>> > standard: ex:
>> > ANSI standard is this
>> > Select field1, field2
>> >>From table1, table2
>> > Where table1ID=table2ID
>> > Transact SQL is this
>> > Select field1, field2
>> >>From table1
>> > Join table2 on table1ID=table2ID
>> > Does the optimizer see these differently on a table with a lot of
>> > joins... Hide quoted text -
>> - Show quoted text -
> Good point, I read that =* isn't supported in 2005. is that correct?
>|||On Jun 14, 9:39 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> It is indeed correct. Try this in SQL 2k5:
> select *
> from sys.objects o, sys.indexes c
> where o.object_id *= c.object_id
> and you get this informative error:
> Msg 4147, Level 15, State 1, Line 3
> The query uses non-ANSI outer join operators ("*=" or "=*"). To run this
> query without modification, please set the compatibility level for current
> database to 80 or lower, using stored procedure sp_dbcmptlevel. It is
> strongly recommended to rewrite the query using ANSI outer join operators
> (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server,
> non-ANSI join operators will not be supported even in backward-compatibility
> modes.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Kristina" <Kristina...@.gmail.com> wrote in message
> news:1181863946.324696.186190@.o11g2000prd.googlegroups.com...
>
> > On Jun 14, 8:26 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> >> As Roy said, they will produce the same query plan. HOWEVER, I would
> >> advise
> >> against using the 'old-style' joins if they are outer because they will
> >> not
> >> be supported in future editions of SQL Server.
> >> --
> >> TheSQLGuru
> >> President
> >> Indicium Resources, Inc.
> >> "Kristina" <Kristina...@.gmail.com> wrote in message
> >>news:1181762584.269445.155360@.q19g2000prn.googlegroups.com...
> >> > Does anyone know if transact sql joins will work faster than ansii
> >> > standard: ex:
> >> > ANSI standard is this
> >> > Select field1, field2
> >> >>From table1, table2
> >> > Where table1ID=table2ID
> >> > Transact SQL is this
> >> > Select field1, field2
> >> >>From table1
> >> > Join table2 on table1ID=table2ID
> >> > Does the optimizer see these differently on a table with a lot of
> >> > joins... Hide quoted text -
> >> - Show quoted text -
> > Good point, I read that =* isn't supported in 2005. is that correct... Hide quoted text -
> - Show quoted text -
Thanks for the help! I just left a job where I upgraded all the db's
to SQL 2005 and my current contract is in SQL 2000 and all the SQL is
ansii standard - I have never seen anything like it!!! Trying to work
through all the new issues.

query optimizer

Query Optimization Assistance w/ Joins

I have a couple of tables that look like this (not excactly but close
enough):

[Contact]
id int
fname varchar(50)
lname varchar(50)

[ContactPhoneNumber]
id int
number varchar(15)
ext varchar(6)
contact_id int
priority int (indicates primary, secondary... numbers)
type int (indicates type of number: fax, cell, land line)

I'm looking for a more optimized method of displaying this information
in this format:

fname, primary business phone

Using a derived column like this works, but seems to be slow with many
records, despite tuning indexes:

SELECT c.fname AS [First Name],
( SELECT TOP 1
number
FROM ContactPhoneNumber cpn
WHERE cpn.type = 1
AND cpn.contact_id = c.id
ORDER BY cpn.priority) AS Number
FROM Contact c

I can get the same results using a join, and it's a lot faster. But I'm
not sure how to select only the primary phone number this way...
basically the first phone number whose priority is either NULL or 1.

Any suggestions?

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!On 12 Jul 2004 16:05:51 GMT, Kenneth Courville wrote:

[snip]

> I can get the same results using a join, and it's a lot faster. But I'm
> not sure how to select only the primary phone number this way...
> basically the first phone number whose priority is either NULL or 1.

Select TOP 1 c.fname AS [First Name], cpn.Number as Number
FROM Contact c
INNER JOIN ContactPhoneNumber cpn
ON cpn.contact_id = c.id
WHERE cpn.type=1 AND (cpn.priority = 1 OR cpn.priority IS NULL)
ORDER BY cpn.ID

Since you didn't specify what "first" means if there are multiple phone
numbers matching (priority=1 or priority is null), I took the hint that
cpn.ID might be an autonumber, meaning that lower numbers mean entered into
the table earlier, so the lowest ID would be the first.|||Yes... "first" means if there are multiple phone
numbers matching (priority=1 or priority is null)... then just return
the top one in the list... no matter what the order is... the ORDER BY
cpn.id isn't necessary.

I guess the other thing I should've mentioned is that I'm displaying
contacts that are assigned to a particular client location.

The problem with your modifications is it would only return the first
contact... whereas the output can be:

First Name Number
John 555-1234
Sally 555-7891
Jill 555-9713

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!|||Kenneth Courville (krcourville@.-nospam-msn.com) writes:
> Using a derived column like this works, but seems to be slow with many
> records, despite tuning indexes:
> SELECT c.fname AS [First Name],
> ( SELECT TOP 1
> number
> FROM ContactPhoneNumber cpn
> WHERE cpn.type = 1
> AND cpn.contact_id = c.id
> ORDER BY cpn.priority) AS Number
> FROM Contact c
> I can get the same results using a join, and it's a lot faster. But I'm
> not sure how to select only the primary phone number this way...
> basically the first phone number whose priority is either NULL or 1.

SELECT c.fname AS "First Name", cpn.Number
FROM Contact c
JOIN (SELECT contact_id, MIN(Number)
FROM ContactPhoneNumber a
JOIN (SELECT contact_id, priority = MIN(priority)
FROM ContactPhoneNumber
WHERE type = 1
GROUP BY contact_id) AS b
ON a.contact_id = b.contact_id
AND a.priority = b.priority
-- (OR a.priority IS NULL AND b.priority IS NULL)
WHERE a.type = 1
GROUP BY contact_id) AS cpn
ON c.id = cpn.contact_id

I think this will cut it, but with CREATE TABLE statements and INSERT
statements with sample data, it is difficult to test. (Hint, hint!)

If priority can be NULL, you should uncomment the commented line.
I have assumed that two numbers can have equal priority.

Here I am using a derived table, actually even two. I have found that in
many cases this gives better performance than correlated subqueries in the
SELECT list.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On 12 Jul 2004 19:15:51 GMT, Kenneth Courville wrote:

> Yes... "first" means if there are multiple phone
> numbers matching (priority=1 or priority is null)... then just return
> the top one in the list... no matter what the order is... the ORDER BY
> cpn.id isn't necessary.
> I guess the other thing I should've mentioned is that I'm displaying
> contacts that are assigned to a particular client location.
> The problem with your modifications is it would only return the first
> contact... whereas the output can be:
> First Name Number
> John 555-1234
> Sally 555-7891
> Jill 555-9713
Ah. My brain appears to have conflated your question with someone else's.
Sorry.

How about this:

Select c.fname AS [First Name], Min(cpn.Number) as Number
FROM Contact c
INNER JOIN ContactPhoneNumber cpn
ON cpn.contact_id = c.id
WHERE cpn.type=1 AND (cpn.priority = 1 OR cpn.priority IS NULL)
GROUP BY c.ID, c.Fname

I know you said you explicitly want the "first" one, but since the order
isn't well defined, SQL server is free to return them in any random order
it wishes ... which means that the "first" one won't necessarily be the
same twice in a row. So maybe you can get by with Min?|||duh.. This should help.

Hadn't thought of a derived table... still kind of new to them.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Wednesday, March 28, 2012

Query problem ...

Wednesday, March 21, 2012

query performance issue of Sql server 2005 Mobile edition on device

Query Performance

Monday, March 12, 2012

Query Parallelism (maxdop)

Friday, March 9, 2012

query optimizer

query optimizer

query optimizer

Query Optimization Assistance w/ Joins

Query longer in SP

Blog Archive

About Me