Is it possible to put several queries into one sp

I have an update query which either inserts a row or increases quantity, depending if row exists or not. It works, better than my explanation probably.

After that query could be a good time to count total of all calculated sub sums.

Something like this.

previous query

END

go
SELECT SUM(SubTotal)
FROM dbo.t_Shoppings

I have tried this on the tool which has a long name, but I think my way didn't work. (Microsoft sql server management studio express)

Is this possible or do I have make and call another stored procedure.

I can send my sp if someone wants.

No, this is for sure possible. Try taking the "END" between your first and second query and put it after your second query. Let me know what errors you are getting when you try to execute it if that doesn't work

|||

you can just put queries on eby one withiout any separator , just start every select statement in new line. Do not use GO it unless you run your query in Management studio environment (Go is not SQL comamnd)

|||

Thank you both for your advises. I'll test the sp later today. And I have to check how to return parameters and so on first.

I'll mark your answers as answers then too.

Regards

Leif

|||

Hi

I tried today my sp and got it working. Server management studio didn't show output parameter right but the query itself seems to work. I'll test it with code later.

Thanks for help.

Here is my stored procedure now. I didn't translate it, so variables may look strange. Kokonaissumma means total value btw.

create PROCEDURE [dbo].[kori2]
(
@.Tuotekoodi varchar(20),
@.kokonaissumma money output
)
AS
BEGIN
SET NOCOUNT ON;
IF NOT EXISTS(SELECT * FROM dbo.t_osto WHERE Tuotekoodi=@.Tuotekoodi)
BEGIN
INSERT dbo.t_osto (Tuotekoodi, Nimi,Malli,Toimittajanimi,Ryhma,Myyntihinta,Alv)
SELECT Tuotekoodi, Nimi,Malli,Toimittajanimi,Ryhma,Myyntihinta,Alv
FROM dbo.t_Tuote
WHERE Tuotekoodi= @.Tuotekoodi
END
ELSE
BEGIN
UPDATE dbo.t_osto
SET Maara=Maara+1
WHERE Tuotekoodi=@.Tuotekoodi
END
END

return (SELECT count(*) FROM dbo.t_osto)

(select @.kokonaissumma=sum(Yhteensa)FROM dbo.t_osto)

is it possible to process the cube using vb.net/c# which exists in the remote server?

Hi

can we process the cube exists in the remote system, using vb.net/c#

i am able to process in the same system, where analysis service available.

i couln't process this from the client system.

is there any soluctions?

You can use either the AMO or ADOMD libraries to send processing commands to a remote server.

The AMO library is easier to work with for admin tasks, but you would have to redeploy the dll's. Where as any client that has to query the cubes will already need have ADOMD installed.

The following send an XMLA processing command using ADOMD, all you need to do is to create a console application, add a reference to Microsoft.AnalysisServices.AdomdClient and paste in the following:

Code Snippet

Sub Main()

Dim serverName As String = "Server1"

Dim databaseName As String = "Adventure Works DW"

Dim databaseID As String = databaseName

Dim cubeID As String = "Adventure Works"

Dim cn As New AdomdConnection("Provider=MSOLAP;Data Source=" & serverName & ";Initial Catalog=" & databaseName)

Console.WriteLine("Opening Connection...")

cn.Open()

Dim cmd As AdomdCommand

cmd = cn.CreateCommand()

cmd.CommandType = CommandType.Text

cmd.CommandText = "<Batch xmlns=""http://schemas.microsoft.com/analysisservices/2003/engine""><Parallel><Process> <Object>" & _

"<DatabaseID>" & DatabaseID & "</DatabaseID>" & _

"<CubeID>" & cubeID & "</CubeID> " & _

"</Object><Type>ProcessFull</Type><WriteBackTableCreation>UseExisting</WriteBackTableCreation> </Process> </Parallel> </Batch>"

Try

Console.WriteLine("Executing Command...")

cmd.ExecuteNonQuery()

Console.WriteLine("Command Complete")

Catch ex As Exception

Console.WriteLine(" --== ERROR ==--")

Console.WriteLine(ex.Message)

Console.WriteLine()

Finally

cn.Close()

Console.WriteLine("Finished")

End Try

End Sub

|||

Hi Darren

I tried your solution using AdoMd 8.0 on AS 2005,

but it seems that no data are refreshed,

neverthless the command text is executed without any error

Do I miss any other instruction (i.e: 'comitt transaction', .)?

Thanks

|||

I don't think ADOMD 8.0 has the execute method you need 9.0 to work with AS2005.

I just double checked and if your account has permission to process the server, database and cube properties are set correctly it should work. Try running a profiler session on the server and see if there are any errors being thrown at that end.

is Exists faster than a Join

From http://www.sql-server-performance.co...sis_tuning.asp
B) Can standard JOIN's be replaced with EXISTS statements?
If you need to join tables to ensure XYZ value exists or a specific set of
rows exists, then considering porting to EXISTS or NOT EXISTS statements.
At the same time is may also alleviate the need for DISTINCT clauses.
So is a Join really potentiall slower?
The Exists clause can be faster than a join because it just searches long
enough to find the first "Hit" as opposed to returning all rows that meet
the join criteria.
Greg Jackson
PDX, Oregon
|||Could you please elaborate? An Exists will meet all the rows that meet the
query criteria. Given that the criteria should return the same data as a
join, how could it be faster?
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:eZWWYB7aFHA.3848@.TK2MSFTNGP10.phx.gbl...
> The Exists clause can be faster than a join because it just searches long
> enough to find the first "Hit" as opposed to returning all rows that meet
> the join criteria.
>
> Greg Jackson
> PDX, Oregon
>
|||I say
SELECT * FROM Main
WHERE Exists(SELECT 1 FROM MyJoinTable a WHERE a.foo = main.foo)
then it will only check for the first hit.
still probably not making sense.
show us YOUR query and I can probably explain a bit better
GAJ
|||create table #Table1(T1C1 int identity (1,1), T1C2 char(10))
create table #Table2(T2C1 int, T2C2 char(10))
insert into #Table1 (T1C2) values ('T1C2')
insert into #Table1 (T1C2) values ('T1C2-2')
insert into #Table2 (T2C1,T2C2) values (1,'T1C2')
select t1.T1C1,T1C2
from #Table1 t1
inner join #Table2 t2 on t1.t1c1 = t2.t2c1
or
select t1.T1C1,T1C2
from #table1 t1
where exists
(select * from #Table2 t2
where t2.T2C1 = T1.T1C1)
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:%23fZBCd7aFHA.464@.TK2MSFTNGP15.phx.gbl...
>I say
> SELECT * FROM Main
> WHERE Exists(SELECT 1 FROM MyJoinTable a WHERE a.foo = main.foo)
> then it will only check for the first hit.
> still probably not making sense.
> show us YOUR query and I can probably explain a bit better
>
> GAJ
>
|||that's faster than a Join.
the where clause will return TRUE as soon as it finds a SINGLE record in
TABLE 2 that mathces Table1 (It will not bother returning more than 1 record
that matches to table 1)
A join will return ALL Records in T2 that match T1. IF there are millions of
records in each table, this would be exponentially faster.
Greg Jackson
PDX, Oregon
|||On Tue, 7 Jun 2005 15:11:21 -0700, ChrisR wrote:

>From http://www.sql-server-performance.co...sis_tuning.asp
>B) Can standard JOIN's be replaced with EXISTS statements?
>If you need to join tables to ensure XYZ value exists or a specific set of
>rows exists, then considering porting to EXISTS or NOT EXISTS statements.
>At the same time is may also alleviate the need for DISTINCT clauses.
>
>So is a Join really potentiall slower?
Hi Chris,
The question is actually irrelevant. A join is different: a query such
as
SELECT a.*
FROM a
WHERE EXISTS (SELECT * FROM b WHERE b.xx = a.xx)
will never return more than one copy of each row in a. Using a join
instead, such as this:
SELECT a.*
FROM a
JOIN b ON b.xx = a.xx
might return multiple copies of rows in a for values of xx that are not
unique in table b.
If no value of xx is ever duplicated in b, then the results will be the
same, but the EXISTS version might still be faster. Since SQL Server
doesn't know that there is only one row in b with a particular value of
xx, it still has to search the rest of the table after encoutering one;
if EXISTS is used, the search is aborted after the first match.
If SQL Server knows that values of b.xx are unique (because there is a
PRIMARY KEY or UNIQUE constraint on b.xx), then the performance MIGHT be
the same - the optimizer MIGHT decide to use the knowledge of this
constraint and createt the same execution plan it'll also use for the
EXISTS version.
I have never seen or heard of any situation where the JOIN would be
fafster than the "equivalent" EXISTS version. And I don't think I ever
will.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hi Hugo and Chris,
"Hugo Kornelis" wrote:
> On Tue, 7 Jun 2005 15:11:21 -0700, ChrisR wrote:
>
> Hi Chris,
> The question is actually irrelevant. A join is different: a query such
> as
> SELECT a.*
> FROM a
> WHERE EXISTS (SELECT * FROM b WHERE b.xx = a.xx)
> will never return more than one copy of each row in a. Using a join
> instead, such as this:
> SELECT a.*
> FROM a
> JOIN b ON b.xx = a.xx
> might return multiple copies of rows in a for values of xx that are not
> unique in table b.
> If no value of xx is ever duplicated in b, then the results will be the
> same, but the EXISTS version might still be faster. Since SQL Server
> doesn't know that there is only one row in b with a particular value of
> xx, it still has to search the rest of the table after encoutering one;
> if EXISTS is used, the search is aborted after the first match.
> If SQL Server knows that values of b.xx are unique (because there is a
> PRIMARY KEY or UNIQUE constraint on b.xx), then the performance MIGHT be
> the same - the optimizer MIGHT decide to use the knowledge of this
> constraint and createt the same execution plan it'll also use for the
> EXISTS version.
> I have never seen or heard of any situation where the JOIN would be
> fafster than the "equivalent" EXISTS version. And I don't think I ever
> will.
>
I think you have to try the statement both ways.
I'm not sure about EXISTS, but I have a fairly complex multi-way join that
is much faster with
a LEFT JOIN b on a.id = b.id ... WHERE b.id is null
than with
WHERE NOT EXISTS (select 1 from b where a.id = b.id)
Regards,
Jonathan
|||On Thu, 9 Jun 2005 12:52:03 -0700, Jonathan Levine wrote:
(snip)
>I'm not sure about EXISTS, but I have a fairly complex multi-way join that
>is much faster with
>a LEFT JOIN b on a.id = b.id ... WHERE b.id is null
>than with
>WHERE NOT EXISTS (select 1 from b where a.id = b.id)
Hi Jonathan,
Thanks for adding this. This is not the first time I witness or hear
reports of this either. It seems that the SQL Server development team
put most effort in the optimization of join strategies, since they are
used most often in typical queries.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Also, be careful of the Optimizer Estimated Execution Plans. I especially
see this in comparisons between LEFT OUTER JOINS versus the use of EXISTS or
NOT EXISTS.
Even though the JOIN may produce a better ESTIMATED execution cost, it will
often create a HASH table for the JOIN, where the EXISTS solution will often
use a Nested Loop JOIN. The HASH will ALWAYS create temporary object, which
will have physical impact that the Optimizer does not take into account.
You're best bet would be to SET STATISTICS time and I/O on and run them both
ways, making sure to flush the Buffer Pool before each execution. This will
give you ACTUAL run information, not just ESTIMATED.
Sincerely,
Anthony Thomas

"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:mnfha11l8fukurbcocmi9rg30s47q0lj6j@.4ax.com...
On Thu, 9 Jun 2005 12:52:03 -0700, Jonathan Levine wrote:
(snip)
>I'm not sure about EXISTS, but I have a fairly complex multi-way join that
>is much faster with
>a LEFT JOIN b on a.id = b.id ... WHERE b.id is null
>than with
>WHERE NOT EXISTS (select 1 from b where a.id = b.id)
Hi Jonathan,
Thanks for adding this. This is not the first time I witness or hear
reports of this either. It seems that the SQL Server development team
put most effort in the optimization of join strategies, since they are
used most often in typical queries.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

is Exists faster than a Join

From http://www.sql-server-performance.c...ysis_tuning.asp
B) Can standard JOIN's be replaced with EXISTS statements?
If you need to join tables to ensure XYZ value exists or a specific set of
rows exists, then considering porting to EXISTS or NOT EXISTS statements.
At the same time is may also alleviate the need for DISTINCT clauses.
So is a Join really potentiall slower?The Exists clause can be faster than a join because it just searches long
enough to find the first "Hit" as opposed to returning all rows that meet
the join criteria.
Greg Jackson
PDX, Oregon|||Could you please elaborate? An Exists will meet all the rows that meet the
query criteria. Given that the criteria should return the same data as a
join, how could it be faster?
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:eZWWYB7aFHA.3848@.TK2MSFTNGP10.phx.gbl...
> The Exists clause can be faster than a join because it just searches long
> enough to find the first "Hit" as opposed to returning all rows that meet
> the join criteria.
>
> Greg Jackson
> PDX, Oregon
>|||I say
SELECT * FROM Main
WHERE Exists(SELECT 1 FROM MyJoinTable a WHERE a.foo = main.foo)
then it will only check for the first hit.
still probably not making sense.
show us YOUR query and I can probably explain a bit better
GAJ|||create table #Table1(T1C1 int identity (1,1), T1C2 char(10))
create table #Table2(T2C1 int, T2C2 char(10))
insert into #Table1 (T1C2) values ('T1C2')
insert into #Table1 (T1C2) values ('T1C2-2')
insert into #Table2 (T2C1,T2C2) values (1,'T1C2')
select t1.T1C1,T1C2
from #Table1 t1
inner join #Table2 t2 on t1.t1c1 = t2.t2c1
or
select t1.T1C1,T1C2
from #table1 t1
where exists
(select * from #Table2 t2
where t2.T2C1 = T1.T1C1)
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:%23fZBCd7aFHA.464@.TK2MSFTNGP15.phx.gbl...
>I say
> SELECT * FROM Main
> WHERE Exists(SELECT 1 FROM MyJoinTable a WHERE a.foo = main.foo)
> then it will only check for the first hit.
> still probably not making sense.
> show us YOUR query and I can probably explain a bit better
>
> GAJ
>|||that's faster than a Join.
the where clause will return TRUE as soon as it finds a SINGLE record in
TABLE 2 that mathces Table1 (It will not bother returning more than 1 record
that matches to table 1)
A join will return ALL Records in T2 that match T1. IF there are millions of
records in each table, this would be exponentially faster.
Greg Jackson
PDX, Oregon|||On Tue, 7 Jun 2005 15:11:21 -0700, ChrisR wrote:

>From http://www.sql-server-performance.c...ysis_tuning.asp
>B) Can standard JOIN's be replaced with EXISTS statements?
>If you need to join tables to ensure XYZ value exists or a specific set of
>rows exists, then considering porting to EXISTS or NOT EXISTS statements.
>At the same time is may also alleviate the need for DISTINCT clauses.
>
>So is a Join really potentiall slower?
Hi Chris,
The question is actually irrelevant. A join is different: a query such
as
SELECT a.*
FROM a
WHERE EXISTS (SELECT * FROM b WHERE b.xx = a.xx)
will never return more than one copy of each row in a. Using a join
instead, such as this:
SELECT a.*
FROM a
JOIN b ON b.xx = a.xx
might return multiple copies of rows in a for values of xx that are not
unique in table b.
If no value of xx is ever duplicated in b, then the results will be the
same, but the EXISTS version might still be faster. Since SQL Server
doesn't know that there is only one row in b with a particular value of
xx, it still has to search the rest of the table after encoutering one;
if EXISTS is used, the search is aborted after the first match.
If SQL Server knows that values of b.xx are unique (because there is a
PRIMARY KEY or UNIQUE constraint on b.xx), then the performance MIGHT be
the same - the optimizer MIGHT decide to use the knowledge of this
constraint and createt the same execution plan it'll also use for the
EXISTS version.
I have never seen or heard of any situation where the JOIN would be
fafster than the "equivalent" EXISTS version. And I don't think I ever
will.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Hugo and Chris,
"Hugo Kornelis" wrote:
> On Tue, 7 Jun 2005 15:11:21 -0700, ChrisR wrote:
>
> Hi Chris,
> The question is actually irrelevant. A join is different: a query such
> as
> SELECT a.*
> FROM a
> WHERE EXISTS (SELECT * FROM b WHERE b.xx = a.xx)
> will never return more than one copy of each row in a. Using a join
> instead, such as this:
> SELECT a.*
> FROM a
> JOIN b ON b.xx = a.xx
> might return multiple copies of rows in a for values of xx that are not
> unique in table b.
> If no value of xx is ever duplicated in b, then the results will be the
> same, but the EXISTS version might still be faster. Since SQL Server
> doesn't know that there is only one row in b with a particular value of
> xx, it still has to search the rest of the table after encoutering one;
> if EXISTS is used, the search is aborted after the first match.
> If SQL Server knows that values of b.xx are unique (because there is a
> PRIMARY KEY or UNIQUE constraint on b.xx), then the performance MIGHT be
> the same - the optimizer MIGHT decide to use the knowledge of this
> constraint and createt the same execution plan it'll also use for the
> EXISTS version.
> I have never seen or heard of any situation where the JOIN would be
> fafster than the "equivalent" EXISTS version. And I don't think I ever
> will.
>
I think you have to try the statement both ways.
I'm not sure about EXISTS, but I have a fairly complex multi-way join that
is much faster with
a LEFT JOIN b on a.id = b.id ... WHERE b.id is null
than with
WHERE NOT EXISTS (select 1 from b where a.id = b.id)
Regards,
Jonathan|||On Thu, 9 Jun 2005 12:52:03 -0700, Jonathan Levine wrote:
(snip)
>I'm not sure about EXISTS, but I have a fairly complex multi-way join that
>is much faster with
>a LEFT JOIN b on a.id = b.id ... WHERE b.id is null
>than with
>WHERE NOT EXISTS (select 1 from b where a.id = b.id)
Hi Jonathan,
Thanks for adding this. This is not the first time I witness or hear
reports of this either. It seems that the SQL Server development team
put most effort in the optimization of join strategies, since they are
used most often in typical queries.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Also, be careful of the Optimizer Estimated Execution Plans. I especially
see this in comparisons between LEFT OUTER JOINS versus the use of EXISTS or
NOT EXISTS.
Even though the JOIN may produce a better ESTIMATED execution cost, it will
often create a HASH table for the JOIN, where the EXISTS solution will often
use a Nested Loop JOIN. The HASH will ALWAYS create temporary object, which
will have physical impact that the Optimizer does not take into account.
You're best bet would be to SET STATISTICS time and I/O on and run them both
ways, making sure to flush the Buffer Pool before each execution. This will
give you ACTUAL run information, not just ESTIMATED.
Sincerely,
Anthony Thomas
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:mnfha11l8fukurbcocmi9rg30s47q0lj6j@.
4ax.com...
On Thu, 9 Jun 2005 12:52:03 -0700, Jonathan Levine wrote:
(snip)
>I'm not sure about EXISTS, but I have a fairly complex multi-way join that
>is much faster with
>a LEFT JOIN b on a.id = b.id ... WHERE b.id is null
>than with
>WHERE NOT EXISTS (select 1 from b where a.id = b.id)
Hi Jonathan,
Thanks for adding this. This is not the first time I witness or hear
reports of this either. It seems that the SQL Server development team
put most effort in the optimization of join strategies, since they are
used most often in typical queries.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

is Exists faster than a Join

From http://www.sql-server-performance.com/query_analysis_tuning.asp
B) Can standard JOIN's be replaced with EXISTS statements?
If you need to join tables to ensure XYZ value exists or a specific set of
rows exists, then considering porting to EXISTS or NOT EXISTS statements.
At the same time is may also alleviate the need for DISTINCT clauses.
So is a Join really potentiall slower?The Exists clause can be faster than a join because it just searches long
enough to find the first "Hit" as opposed to returning all rows that meet
the join criteria.
Greg Jackson
PDX, Oregon|||Could you please elaborate? An Exists will meet all the rows that meet the
query criteria. Given that the criteria should return the same data as a
join, how could it be faster?
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:eZWWYB7aFHA.3848@.TK2MSFTNGP10.phx.gbl...
> The Exists clause can be faster than a join because it just searches long
> enough to find the first "Hit" as opposed to returning all rows that meet
> the join criteria.
>
> Greg Jackson
> PDX, Oregon
>|||I say
SELECT * FROM Main
WHERE Exists(SELECT 1 FROM MyJoinTable a WHERE a.foo = main.foo)
then it will only check for the first hit.
still probably not making sense.
show us YOUR query and I can probably explain a bit better
GAJ|||create table #Table1(T1C1 int identity (1,1), T1C2 char(10))
create table #Table2(T2C1 int, T2C2 char(10))
insert into #Table1 (T1C2) values ('T1C2')
insert into #Table1 (T1C2) values ('T1C2-2')
insert into #Table2 (T2C1,T2C2) values (1,'T1C2')
select t1.T1C1,T1C2
from #Table1 t1
inner join #Table2 t2 on t1.t1c1 = t2.t2c1
or
select t1.T1C1,T1C2
from #table1 t1
where exists
(select * from #Table2 t2
where t2.T2C1 = T1.T1C1)
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:%23fZBCd7aFHA.464@.TK2MSFTNGP15.phx.gbl...
>I say
> SELECT * FROM Main
> WHERE Exists(SELECT 1 FROM MyJoinTable a WHERE a.foo = main.foo)
> then it will only check for the first hit.
> still probably not making sense.
> show us YOUR query and I can probably explain a bit better
>
> GAJ
>|||that's faster than a Join.
the where clause will return TRUE as soon as it finds a SINGLE record in
TABLE 2 that mathces Table1 (It will not bother returning more than 1 record
that matches to table 1)
A join will return ALL Records in T2 that match T1. IF there are millions of
records in each table, this would be exponentially faster.
Greg Jackson
PDX, Oregon|||On Tue, 7 Jun 2005 15:11:21 -0700, ChrisR wrote:
>From http://www.sql-server-performance.com/query_analysis_tuning.asp
>B) Can standard JOIN's be replaced with EXISTS statements?
>If you need to join tables to ensure XYZ value exists or a specific set of
>rows exists, then considering porting to EXISTS or NOT EXISTS statements.
>At the same time is may also alleviate the need for DISTINCT clauses.
>
>So is a Join really potentiall slower?
Hi Chris,
The question is actually irrelevant. A join is different: a query such
as
SELECT a.*
FROM a
WHERE EXISTS (SELECT * FROM b WHERE b.xx = a.xx)
will never return more than one copy of each row in a. Using a join
instead, such as this:
SELECT a.*
FROM a
JOIN b ON b.xx = a.xx
might return multiple copies of rows in a for values of xx that are not
unique in table b.
If no value of xx is ever duplicated in b, then the results will be the
same, but the EXISTS version might still be faster. Since SQL Server
doesn't know that there is only one row in b with a particular value of
xx, it still has to search the rest of the table after encoutering one;
if EXISTS is used, the search is aborted after the first match.
If SQL Server knows that values of b.xx are unique (because there is a
PRIMARY KEY or UNIQUE constraint on b.xx), then the performance MIGHT be
the same - the optimizer MIGHT decide to use the knowledge of this
constraint and createt the same execution plan it'll also use for the
EXISTS version.
I have never seen or heard of any situation where the JOIN would be
fafster than the "equivalent" EXISTS version. And I don't think I ever
will.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Hugo and Chris,
"Hugo Kornelis" wrote:
> On Tue, 7 Jun 2005 15:11:21 -0700, ChrisR wrote:
> >From http://www.sql-server-performance.com/query_analysis_tuning.asp
> >
> >B) Can standard JOIN's be replaced with EXISTS statements?
> >If you need to join tables to ensure XYZ value exists or a specific set of
> >rows exists, then considering porting to EXISTS or NOT EXISTS statements.
> >At the same time is may also alleviate the need for DISTINCT clauses.
> >
> >So is a Join really potentiall slower?
> Hi Chris,
> The question is actually irrelevant. A join is different: a query such
> as
> SELECT a.*
> FROM a
> WHERE EXISTS (SELECT * FROM b WHERE b.xx = a.xx)
> will never return more than one copy of each row in a. Using a join
> instead, such as this:
> SELECT a.*
> FROM a
> JOIN b ON b.xx = a.xx
> might return multiple copies of rows in a for values of xx that are not
> unique in table b.
> If no value of xx is ever duplicated in b, then the results will be the
> same, but the EXISTS version might still be faster. Since SQL Server
> doesn't know that there is only one row in b with a particular value of
> xx, it still has to search the rest of the table after encoutering one;
> if EXISTS is used, the search is aborted after the first match.
> If SQL Server knows that values of b.xx are unique (because there is a
> PRIMARY KEY or UNIQUE constraint on b.xx), then the performance MIGHT be
> the same - the optimizer MIGHT decide to use the knowledge of this
> constraint and createt the same execution plan it'll also use for the
> EXISTS version.
> I have never seen or heard of any situation where the JOIN would be
> fafster than the "equivalent" EXISTS version. And I don't think I ever
> will.
>
I think you have to try the statement both ways.
I'm not sure about EXISTS, but I have a fairly complex multi-way join that
is much faster with
a LEFT JOIN b on a.id = b.id ... WHERE b.id is null
than with
WHERE NOT EXISTS (select 1 from b where a.id = b.id)
Regards,
Jonathan|||On Thu, 9 Jun 2005 12:52:03 -0700, Jonathan Levine wrote:
(snip)
>I'm not sure about EXISTS, but I have a fairly complex multi-way join that
>is much faster with
>a LEFT JOIN b on a.id = b.id ... WHERE b.id is null
>than with
>WHERE NOT EXISTS (select 1 from b where a.id = b.id)
Hi Jonathan,
Thanks for adding this. This is not the first time I witness or hear
reports of this either. It seems that the SQL Server development team
put most effort in the optimization of join strategies, since they are
used most often in typical queries.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Also, be careful of the Optimizer Estimated Execution Plans. I especially
see this in comparisons between LEFT OUTER JOINS versus the use of EXISTS or
NOT EXISTS.
Even though the JOIN may produce a better ESTIMATED execution cost, it will
often create a HASH table for the JOIN, where the EXISTS solution will often
use a Nested Loop JOIN. The HASH will ALWAYS create temporary object, which
will have physical impact that the Optimizer does not take into account.
You're best bet would be to SET STATISTICS time and I/O on and run them both
ways, making sure to flush the Buffer Pool before each execution. This will
give you ACTUAL run information, not just ESTIMATED.
Sincerely,
Anthony Thomas
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:mnfha11l8fukurbcocmi9rg30s47q0lj6j@.4ax.com...
On Thu, 9 Jun 2005 12:52:03 -0700, Jonathan Levine wrote:
(snip)
>I'm not sure about EXISTS, but I have a fairly complex multi-way join that
>is much faster with
>a LEFT JOIN b on a.id = b.id ... WHERE b.id is null
>than with
>WHERE NOT EXISTS (select 1 from b where a.id = b.id)
Hi Jonathan,
Thanks for adding this. This is not the first time I witness or hear
reports of this either. It seems that the SQL Server development team
put most effort in the optimization of join strategies, since they are
used most often in typical queries.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)