Is it possible to make a HTTP Post in TSQL? (SQL Server 2000)

I am new to TSQL and cannot find anything in my reference book or on the
internet about how this can be done.
If so, can you let me know how.
Many thanks
TomI am not sure if this is what you are talking about, but in 2k there is the
Web Assistant Wizard in Enterprise Manager. This will create an HTML file
based on your query. You can also use sp_makeweb in SQL 2k. If you are
using 2005 you must enable these as default is set to off. Use:
sp_configure 'Web Assistant Procedures', 1;
GO
Although this feature is still supported in 2005 it will be deprecated in
future releases as Microsoft wishes to focus more on Reporting Services for
this functionality.
BOL in both 2k and 2005 covers these features.
Hope this is helpful
"Tom" wrote:

> I am new to TSQL and cannot find anything in my reference book or on the
> internet about how this can be done.
> If so, can you let me know how.
> Many thanks
> Tom|||Thanks I'll look into this.
To be more explicit on what I am trying to do - I am converting a JAVA
procedure to T-SQL and need to post an XML document. (open a URL connection
and send an XML document to that URL)
In PL/SQL I know I would have to declare a PL/SQL function that refers to
(wraps a)a JAVA method that will do the actual posting. The PL/SQL function
can then be reffered to in other PL/SQL code as though it is a PL/SQL
function.
Can I use the wizard to do this? Are we talking at cross purposes...
"Derekman" wrote:
> I am not sure if this is what you are talking about, but in 2k there is th
e
> Web Assistant Wizard in Enterprise Manager. This will create an HTML file
> based on your query. You can also use sp_makeweb in SQL 2k. If you are
> using 2005 you must enable these as default is set to off. Use:
> sp_configure 'Web Assistant Procedures', 1;
> GO
> Although this feature is still supported in 2005 it will be deprecated in
> future releases as Microsoft wishes to focus more on Reporting Services fo
r
> this functionality.
> BOL in both 2k and 2005 covers these features.
> Hope this is helpful
> "Tom" wrote:
>

Is it ok for a SQL2K5 TSQL job to use a UNC pointing to a db on same

Microsoft generally recommends against networked db's per
http://support.microsoft.com/kb/304261. Unfortunately, there is a
dispute in our organization and advice from local Microsoft resources
against changing Trace flag 1807 to use UNCs even if the UNC points to
a local db (i.e., dbms, job and db are on the same server). A
Microsoft SQL SME even suggested the use of UNCs makes the local db i/
o go through the server's network stack even though everything is on
the same machine.
Currently, our SQL jobs use mapped drives. In order to for our SQL
jobs to be imported into our Tidal Enterprise Scheduler environment we
have to use UNCs for them to be parsed corrected by Tidal.
Any substantive clarity would be greatly appreciated.I fail to see what you would change the location of the database files (to UNC), where you really
seem to be talking about jobs. Two different things.
I too believe that UNC result in some pass through some network stack. For instance, try starting
SQL Server using LocalSystem and do some xp_cmdshell or backup to an UNC, even if local. ... from
memory, I should add.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<kkekoa@.gmail.com> wrote in message
news:ec667a6f-3cb1-4d7b-96f8-79472c2eac7b@.p69g2000hsa.googlegroups.com...
> Microsoft generally recommends against networked db's per
> http://support.microsoft.com/kb/304261. Unfortunately, there is a
> dispute in our organization and advice from local Microsoft resources
> against changing Trace flag 1807 to use UNCs even if the UNC points to
> a local db (i.e., dbms, job and db are on the same server). A
> Microsoft SQL SME even suggested the use of UNCs makes the local db i/
> o go through the server's network stack even though everything is on
> the same machine.
> Currently, our SQL jobs use mapped drives. In order to for our SQL
> jobs to be imported into our Tidal Enterprise Scheduler environment we
> have to use UNCs for them to be parsed corrected by Tidal.
> Any substantive clarity would be greatly appreciated.|||Jobs should have no direct contact with ANY of the SQL Server database
files, so the question should not come up. The only process that
touches those files is the SQL Server service. When jobs - or
anything else - needs to interact with a database it interacts with
the SQL Server service, and the service does all the work with the
files.
For whatever a job has to do that relates to files UNC paths are fine.
And I agree that there will be another layer of work using a UNC path
rather than the letter of a directly connected drive. I don't believe
there would be any difference between a mapped network drive and a UNC
reference to the same drive.
Roy Harvey
Beacon Falls, CT
On Wed, 30 Jan 2008 00:00:45 -0800 (PST), kkekoa@.gmail.com wrote:
>Microsoft generally recommends against networked db's per
>http://support.microsoft.com/kb/304261. Unfortunately, there is a
>dispute in our organization and advice from local Microsoft resources
>against changing Trace flag 1807 to use UNCs even if the UNC points to
>a local db (i.e., dbms, job and db are on the same server). A
>Microsoft SQL SME even suggested the use of UNCs makes the local db i/
>o go through the server's network stack even though everything is on
>the same machine.
>Currently, our SQL jobs use mapped drives. In order to for our SQL
>jobs to be imported into our Tidal Enterprise Scheduler environment we
>have to use UNCs for them to be parsed corrected by Tidal.
>Any substantive clarity would be greatly appreciated.|||<kkekoa@.gmail.com> wrote in message
news:ec667a6f-3cb1-4d7b-96f8-79472c2eac7b@.p69g2000hsa.googlegroups.com...
> Microsoft generally recommends against networked db's per
> http://support.microsoft.com/kb/304261. Unfortunately, there is a
> dispute in our organization and advice from local Microsoft resources
> against changing Trace flag 1807 to use UNCs even if the UNC points to
> a local db (i.e., dbms, job and db are on the same server). A
> Microsoft SQL SME even suggested the use of UNCs makes the local db i/
> o go through the server's network stack even though everything is on
> the same machine.
I believe that's correct. It would be a very bad idea to use Trace flag 1807
and UNCs for local database files. Why would you want to do that?
> Currently, our SQL jobs use mapped drives. In order to for our SQL
> jobs to be imported into our Tidal Enterprise Scheduler environment we
> have to use UNCs for them to be parsed corrected by Tidal.
>
I don't understand what you mean. Jobs should have nothing to do with where
your databases are located. In fact jobs reside in the MSDB database. Jobs
are processed by the SQL Agent service so they will have access to whatever
drives the agent does - drive letters or UNC. Could you explain a bit more
about what the problem is.
--
David Portas

Is it a bug of SQL Server 2000 SP4?

Database backup file: 
http://www.keepmyfile.com/download/c58b2a565144

Environment:
SQL Server 2000 SP4

Problem:
The following two statements returns different number of records:
Exec GenPeriodical1 102, null, '20050601', '20050630', null, null, 0 

SELECT *
FROM dbo.OtherFee (null, '20050601', '20050630', null, null, 0)
WHERE flow_id = 102

This problem wasn't found in SQL Server 2000 original version and SQL Server 
2005.
Any help is appreciated!

Is it possible to see text for dbo.OtherFee and GenPeriodical1?|||

Thanks for your reply!

I've got the answer from the newsgroup. The replyer said it seems a bug of SP4 and gave a work-around. If you are interested in this issue, you can download the backup file, it's just 1.18MB.

The store proc and function call other functions, thus not convenient to paste them here.

Is IIS always required for replication?

I would like to take advantage of either Merge or RDA replication over HTTP, but would like to avoid setting up IIS.

Is IIS always required to support connectivity for SQL Server Mobile replication back to a desktop SQL Server database?

Thanks, Tim

Yes IIS is very much required for Merge or RDA with SQL Server. However, setting up IIS is not a big thing as we have a "Configure Web Synchronization" wizard that is very helpful for you in this regard.

Thanks,

Laxmi NRO, MSFT, SQL Mobile, Microsoft Corporation

|||Hi

And how setting up a replication bettween a Windows Mobile 5 Pocket PC and a Windows XP Home Edition PC when IIS can't be install on Home Edition ?

?

Thanks for help
Jo?l

|||

1) What is your backend database?

2) Can I know what is the problem in moving to Win XP Professional Edition?

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||1) To replace synchronisation between Access/PocketAccess (not more supported with WM5), we use know a MSDE database (on PC) with a SQL Server CE 2.0 database (on PocketPC). The replication work fine on Windows XP Pro, but on Windows XP Home there is no IIS to do the replication.

2) Our customer have Windows XP Home Edition. I'm ok with you that's not very "professional", but we can't tell to our customer : "Hey guys, you must move your forty PC to Windows XP Pro, because our software don't run on XP Home". They will say us "bye bye".

And they have already some old PocketPC (before WM5) with this application installed and it works with XP Home ... that's a big problem for us.

Thanks for helping us

|||I have heard that we can synchronize using ActiveSync is it correct or not, because on Online Book I read we can synchronize using IIS. I need to synchronize my database on Pocket PC to my Desktop PC without using IIS (if we have the cradle why we need access to internet from Pocket PC).

And if synchronized only can work with IIS than use the old way try to send each data by client server application. Its a silly things, but thats the only way if we can only synchronize using IIS.

Is IIS always required for replication?

I would like to take advantage of either Merge or RDA replication over HTTP, but would like to avoid setting up IIS.

Is IIS always required to support connectivity for SQL Server Mobile replication back to a desktop SQL Server database?

Thanks, Tim

Yes IIS is very much required for Merge or RDA with SQL Server. However, setting up IIS is not a big thing as we have a "Configure Web Synchronization" wizard that is very helpful for you in this regard.

Thanks,

Laxmi NRO, MSFT, SQL Mobile, Microsoft Corporation

|||Hi

And how setting up a replication bettween a Windows Mobile 5 Pocket PC and a Windows XP Home Edition PC when IIS can't be install on Home Edition ?

?

Thanks for help
Jo?l|||

1) What is your backend database?

2) Can I know what is the problem in moving to Win XP Professional Edition?

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||1) To replace synchronisation between Access/PocketAccess (not more supported with WM5), we use know a MSDE database (on PC) with a SQL Server CE 2.0 database (on PocketPC). The replication work fine on Windows XP Pro, but on Windows XP Home there is no IIS to do the replication.

2) Our customer have Windows XP Home Edition. I'm ok with you that's not very "professional", but we can't tell to our customer : "Hey guys, you must move your forty PC to Windows XP Pro, because our software don't run on XP Home". They will say us "bye bye".

And they have already some old PocketPC (before WM5) with this application installed and it works with XP Home ... that's a big problem for us.

Thanks for helping us|||I have heard that we can synchronize using ActiveSync is it correct or not, because on Online Book I read we can synchronize using IIS. I need to synchronize my database on Pocket PC to my Desktop PC without using IIS (if we have the cradle why we need access to internet from Pocket PC).

And if synchronized only can work with IIS than use the old way try to send each data by client server application. Its a silly things, but thats the only way if we can only synchronize using IIS.

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)

Is bug 351711 fixed in MSDE 2000 SP1 or later?

I'm reading this article http://support.microsoft.com/?id=285100. I wonder
whether it is fixed or not in MSDE 2000 installation. If yes, fixed in which
SP?
The hot fix is released JAN-22-2001 and the article is Last Review : October
7, 2005. The article does not mention anything about whether the hot fix is
included in SP and it does not mention which SP does this bug applies to. By
reading the article, I will assume this only applies to MSDE 2000 without SP
and fixed in SP1. But can I assume that?Just download the latest MSDE version (Microsoft SQL Server 2000 Service Pack
4
http://www.microsoft.com/downloads/details.aspx?familyid=8E2DFC8D-C20E-4446-99A9-B7F0213F8BC5&displaylang=en
or directly from
http://www.microsoft.com/downloads/info.aspx?na=46&p=6&SrcDisplayLang=en&SrcCategoryId=&SrcFamilyId=8E2DFC8D-C20E-4446-99A9-B7F0213F8BC5&u=http%3a%2f%2fdownload.microsoft.com%2fdownload%2f1%2fb%2fd%2f1bdf5b78-584e-4de0-b36f-c44e06b0d2a3%2fSQL2000.MSDE-KB884525-SP4-x86-ENU.EXE&oRef=http%3a%2f%2fwww.microsoft.com%2fdownloads%2fdetails.aspx%3fFamilyId%3d413744D1-A0BC-479F-BAFA-E4B278EB9147%26displaylang%3den
"Peter" wrote:
> I'm reading this article http://support.microsoft.com/?id=285100. I wonder
> whether it is fixed or not in MSDE 2000 installation. If yes, fixed in which
> SP?
> The hot fix is released JAN-22-2001 and the article is Last Review : October
> 7, 2005. The article does not mention anything about whether the hot fix is
> included in SP and it does not mention which SP does this bug applies to. By
> reading the article, I will assume this only applies to MSDE 2000 without SP
> and fixed in SP1. But can I assume that?

Is bug 351711 fixed in MSDE 2000 SP1 or later?

I'm reading this article http://support.microsoft.com/?id=285100. I wonder
whether it is fixed or not in MSDE 2000 installation. If yes, fixed in whic
h
SP?
The hot fix is released JAN-22-2001 and the article is Last Review : October
7, 2005. The article does not mention anything about whether the hot fix i
s
included in SP and it does not mention which SP does this bug applies to. By
reading the article, I will assume this only applies to MSDE 2000 without SP
and fixed in SP1. But can I assume that?Just download the latest MSDE version (Microsoft SQL Server 2000 Service Pac
k
4)
http://www.microsoft.com/downloads/...&displaylang=en
or directly from:
http://www.microsoft.com/downloads/...r />
.MSDE-KB8
84525-SP4-x86-ENU.EXE&oRef=http%3a%2f%2fwww.microsoft.com%2fdownloads%2fdeta
ils.aspx%3fFamilyId%3d413744D1-A0BC-479F-BAFA-E4B278EB9147%26displaylang%3de
n
"Peter" wrote:

> I'm reading this article http://support.microsoft.com/?id=285100. I wonde
r
> whether it is fixed or not in MSDE 2000 installation. If yes, fixed in wh
ich
> SP?
> The hot fix is released JAN-22-2001 and the article is Last Review : Octob
er
> 7, 2005. The article does not mention anything about whether the hot fix
is
> included in SP and it does not mention which SP does this bug applies to.
By
> reading the article, I will assume this only applies to MSDE 2000 without
SP
> and fixed in SP1. But can I assume that?