Better proformance using join or sub-query?

All,
I am using a trigger to keep a "transaction date" column up-to-date with
the datetime the record was lasted inserted/updated. My question is which
SQL statement would provide better performance:
Update tablename set transdate = getdate() where primarykey in (select
primarykey from inserted)
or
update tablename set transdate = getdate
from tablename, inserted
where tablename.primarykey = inserted.primarykey
Thanks,
James K.I recommend
update tablename set transdate = getdate
from tablename, inserted
where tablename.primarykey = inserted.primarykey
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"-=JLK=-" <jknowlto-nospam@.dtic.mil> wrote in message
news:eowog0nGFHA.2740@.TK2MSFTNGP12.phx.gbl...
> All,
> I am using a trigger to keep a "transaction date" column up-to-date
> with the datetime the record was lasted inserted/updated. My question is
> which SQL statement would provide better performance:
> Update tablename set transdate = getdate() where primarykey in (select
> primarykey from inserted)
> or
> update tablename set transdate = getdate
> from tablename, inserted
> where tablename.primarykey = inserted.primarykey
> Thanks,
> James K.
>|||I think the second one
Madhivanan|||JKL
I'd go with the first one. The probelm with the second one is that under
some conditions you can get a different ( wrong) output
David Portas has written some script/test about this
CREATE TABLE Countries
(countryname VARCHAR(20) NOT NULL PRIMARY KEY,
capitalcity VARCHAR(20));
CREATE TABLE Cities
(cityname VARCHAR(20) NOT NULL,
countryname VARCHAR(20) NOT NULL
REFERENCES Countries (countryname),
CONSTRAINT PK_Cities
PRIMARY KEY (cityname, countryname));
INSERT INTO Countries (countryname, capitalcity) VALUES ('USA', NULL);
INSERT INTO Countries (countryname, capitalcity) VALUES ('UK', NULL);
INSERT INTO Cities VALUES ('Washington', 'USA');
INSERT INTO Cities VALUES ('London', 'UK');
INSERT INTO Cities VALUES ('Manchester', 'UK');
The MS-syntax makes it all too easy for the developer to slip-up by
writing ambiguous UPDATE...FROM statements where the JOIN criteria is
not unique on the right side of the join.
Try these two identical UPDATE statements with a small change to the
primary key in between.
UPDATE Countries
SET capitalcity = cityname
FROM Countries JOIN Cities /* evil UPDATE... FROM syntax */
ON Countries.countryname = Cities.countryname;
SELECT * FROM Countries;
ALTER TABLE Cities DROP CONSTRAINT PK_Cities;
ALTER TABLE Cities ADD CONSTRAINT PK_Cities PRIMARY KEY (countryname,
cityname);
UPDATE Countries
SET capitalcity = cityname
FROM Countries JOIN Cities /* don't do this! */
ON Countries.countryname = Cities.countryname;
SELECT * FROM Countries;
You get this from the first SELECT statement:
countryname capitalcity
-- --
UK London
USA Washington
and this from the second:
countryname capitalcity
-- --
UK Manchester
USA Washington
"-=JLK=-" <jknowlto-nospam@.dtic.mil> wrote in message
news:eowog0nGFHA.2740@.TK2MSFTNGP12.phx.gbl...
> All,
> I am using a trigger to keep a "transaction date" column up-to-date
with
> the datetime the record was lasted inserted/updated. My question is which
> SQL statement would provide better performance:
> Update tablename set transdate = getdate() where primarykey in (select
> primarykey from inserted)
> or
> update tablename set transdate = getdate
> from tablename, inserted
> where tablename.primarykey = inserted.primarykey
> Thanks,
> James K.
>|||Uri,
The original poster want to update the column the getDate().
I think, As long as you dont depend on the joined table for the
value to update, you are safe.
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:Of9GdAoGFHA.2420@.TK2MSFTNGP14.phx.gbl...
> JKL
> I'd go with the first one. The probelm with the second one is that under
> some conditions you can get a different ( wrong) output
> David Portas has written some script/test about this
> CREATE TABLE Countries
> (countryname VARCHAR(20) NOT NULL PRIMARY KEY,
> capitalcity VARCHAR(20));
> CREATE TABLE Cities
> (cityname VARCHAR(20) NOT NULL,
> countryname VARCHAR(20) NOT NULL
> REFERENCES Countries (countryname),
> CONSTRAINT PK_Cities
> PRIMARY KEY (cityname, countryname));
> INSERT INTO Countries (countryname, capitalcity) VALUES ('USA', NULL);
> INSERT INTO Countries (countryname, capitalcity) VALUES ('UK', NULL);
> INSERT INTO Cities VALUES ('Washington', 'USA');
> INSERT INTO Cities VALUES ('London', 'UK');
> INSERT INTO Cities VALUES ('Manchester', 'UK');
> The MS-syntax makes it all too easy for the developer to slip-up by
> writing ambiguous UPDATE...FROM statements where the JOIN criteria is
> not unique on the right side of the join.
> Try these two identical UPDATE statements with a small change to the
> primary key in between.
> UPDATE Countries
> SET capitalcity = cityname
> FROM Countries JOIN Cities /* evil UPDATE... FROM syntax */
> ON Countries.countryname = Cities.countryname;
> SELECT * FROM Countries;
> ALTER TABLE Cities DROP CONSTRAINT PK_Cities;
> ALTER TABLE Cities ADD CONSTRAINT PK_Cities PRIMARY KEY (countryname,
> cityname);
> UPDATE Countries
> SET capitalcity = cityname
> FROM Countries JOIN Cities /* don't do this! */
> ON Countries.countryname = Cities.countryname;
> SELECT * FROM Countries;
> You get this from the first SELECT statement:
> countryname capitalcity
> -- --
> UK London
> USA Washington
> and this from the second:
> countryname capitalcity
> -- --
> UK Manchester
> USA Washington
>
>
> "-=JLK=-" <jknowlto-nospam@.dtic.mil> wrote in message
> news:eowog0nGFHA.2740@.TK2MSFTNGP12.phx.gbl...
> with
>|||On Fri, 25 Feb 2005 13:00:35 +0530, avnrao wrote:

>Umi, can you please explain the rationale behind it.
Hi Av.,
Mind if I do instead of Uri?
The ANSI syntax and the proprietary UPDATE FROM syntax behave only the
same if for each row in the updated table that satisfies the WHERE
clause, exactly one row in the other table matches the join criteria.
If it's possible that no rows are matched, the ANSI syntax will set the
column to NULL, unless excluded from the update in the WHERE clause. The
UPDATE FROM syntax will not update this row: since it doesn't satisfy
the join criteria, it won't be updated even if it does meet the
requirements of the WHERE clause.
(Note: this difference can be circumvened by using outer join instead of
inner join to make the joined update behave exactly as the ANSI version)
The biggest difference is when one row to be updated can be matched
against more than one row in the source table. This is what's happening
in David's script, as posted by Uri.
In the ANSI version, if the subquery in the SET clause returns more than
one row, you'll get an error message. That ensures that you will revisit
the query and change it so that it will always return exactly one row -
the row that you want it to return.
The UPDATE FROM syntax won't throw an error if a row in the table to be
updated matches against multiple rows from the source. Instead, SQL
Server will just pick one of these rows and use that one to determine
the new values in your table. Or rather, in case you want the full
details, it will update the same row over and over again with each
matching row - and since the result of all but the last change is
overwritten, the net effect is that only the results from the row
processed last will stick.
The above explains why the result of an UPDATE FROM where one row to be
updated matches more than one row from the source is completely
unpredictable: the result will be from the last row processed, but there
is no way to predict the order of evaluation SQL Server will choose for
your query. David's example shows how an index can change the order, but
there are other possibilities: parallellism, available memory, workload,
and the possibility to "piggy-back" another query are just some examples
of how the order of evaluation (and hence the result of the update) may
change between successive execution, even if no schema or data has been
changed!
I hope this helps.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Best Way to transfer tables via Terminal Server ?

I have updated a number of tables at home (size is around
200MB) and I am able to connect to office via Terminal
Server.
I would like to know what is the best tools I can use to
transfer the tables to my office and update them.
Should I transfer the whole database (around 1 GB) and use
DTS on Terminal Server to update the whole database ?
Thanks
If you have any backup encryption type of software (SQL LiteSpeed) then that
would be the way to go. A gig may be painful, really a call for your
company.
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:054e01c593f5$1d51fc30$a401280a@.phx.gbl...
>I have updated a number of tables at home (size is around
> 200MB) and I am able to connect to office via Terminal
> Server.
> I would like to know what is the best tools I can use to
> transfer the tables to my office and update them.
> Should I transfer the whole database (around 1 GB) and use
> DTS on Terminal Server to update the whole database ?
> Thanks
|||Dear Chris,
Is there any way that I only transfer those tables to the Terminal Server
and use DTS there to update the SQL Server ?
Thanks
Peter
"ChrisR" <noemail@.bla.com> wrote in message
news:eMhOclFlFHA.3260@.TK2MSFTNGP10.phx.gbl...
> If you have any backup encryption type of software (SQL LiteSpeed) then
> that would be the way to go. A gig may be painful, really a call for your
> company.
>
> "Peter" <anonymous@.discussions.microsoft.com> wrote in message
> news:054e01c593f5$1d51fc30$a401280a@.phx.gbl...
>
|||Hi
If you don't have SQL Server port access, or file access to the remote
server, you can't transfer the data from your home machine.
Terminal server does not allow local copy to a remote server.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:eq4SdMKlFHA.2852@.TK2MSFTNGP14.phx.gbl...
> Dear Chris,
> Is there any way that I only transfer those tables to the Terminal Server
> and use DTS there to update the SQL Server ?
> Thanks
> Peter
> "ChrisR" <noemail@.bla.com> wrote in message
> news:eMhOclFlFHA.3260@.TK2MSFTNGP10.phx.gbl...
>
|||Dear Mike,
I am able to access the Terminal Server. From the Terminal Server, I am
able to connect to the SQL Server via Remote Access. On the other hand, we
have installed Enterprise Manager on the Terminal Server.
Thanking you in anticipation.
Peter
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:Oe9ZlbNlFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Hi
> If you don't have SQL Server port access, or file access to the remote
> server, you can't transfer the data from your home machine.
> Terminal server does not allow local copy to a remote server.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Peter" <anonymous@.discussions.microsoft.com> wrote in message
> news:eq4SdMKlFHA.2852@.TK2MSFTNGP14.phx.gbl...
>

Best Way to transfer tables via Terminal Server ?

I have updated a number of tables at home (size is around
200MB) and I am able to connect to office via Terminal
Server.
I would like to know what is the best tools I can use to
transfer the tables to my office and update them.
Should I transfer the whole database (around 1 GB) and use
DTS on Terminal Server to update the whole database ?
ThanksIf you have any backup encryption type of software (SQL LiteSpeed) then that
would be the way to go. A gig may be painful, really a call for your
company.
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:054e01c593f5$1d51fc30$a401280a@.phx.gbl...
>I have updated a number of tables at home (size is around
> 200MB) and I am able to connect to office via Terminal
> Server.
> I would like to know what is the best tools I can use to
> transfer the tables to my office and update them.
> Should I transfer the whole database (around 1 GB) and use
> DTS on Terminal Server to update the whole database ?
> Thanks|||Dear Chris,
Is there any way that I only transfer those tables to the Terminal Server
and use DTS there to update the SQL Server ?
Thanks
Peter
"ChrisR" <noemail@.bla.com> wrote in message
news:eMhOclFlFHA.3260@.TK2MSFTNGP10.phx.gbl...
> If you have any backup encryption type of software (SQL LiteSpeed) then
> that would be the way to go. A gig may be painful, really a call for your
> company.
>
> "Peter" <anonymous@.discussions.microsoft.com> wrote in message
> news:054e01c593f5$1d51fc30$a401280a@.phx.gbl...
>|||Hi
If you don't have SQL Server port access, or file access to the remote
server, you can't transfer the data from your home machine.
Terminal server does not allow local copy to a remote server.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:eq4SdMKlFHA.2852@.TK2MSFTNGP14.phx.gbl...
> Dear Chris,
> Is there any way that I only transfer those tables to the Terminal Server
> and use DTS there to update the SQL Server ?
> Thanks
> Peter
> "ChrisR" <noemail@.bla.com> wrote in message
> news:eMhOclFlFHA.3260@.TK2MSFTNGP10.phx.gbl...
>|||Dear Mike,
I am able to access the Terminal Server. From the Terminal Server, I am
able to connect to the SQL Server via Remote Access. On the other hand, we
have installed Enterprise Manager on the Terminal Server.
Thanking you in anticipation.
Peter
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:Oe9ZlbNlFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Hi
> If you don't have SQL Server port access, or file access to the remote
> server, you can't transfer the data from your home machine.
> Terminal server does not allow local copy to a remote server.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Peter" <anonymous@.discussions.microsoft.com> wrote in message
> news:eq4SdMKlFHA.2852@.TK2MSFTNGP14.phx.gbl...
>

Best Way to transfer tables via Terminal Server ?

I have updated a number of tables at home (size is around
200MB) and I am able to connect to office via Terminal
Server.
I would like to know what is the best tools I can use to
transfer the tables to my office and update them.
Should I transfer the whole database (around 1 GB) and use
DTS on Terminal Server to update the whole database ?
ThanksIf you have any backup encryption type of software (SQL LiteSpeed) then that
would be the way to go. A gig may be painful, really a call for your
company.
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:054e01c593f5$1d51fc30$a401280a@.phx.gbl...
>I have updated a number of tables at home (size is around
> 200MB) and I am able to connect to office via Terminal
> Server.
> I would like to know what is the best tools I can use to
> transfer the tables to my office and update them.
> Should I transfer the whole database (around 1 GB) and use
> DTS on Terminal Server to update the whole database ?
> Thanks|||Dear Chris,
Is there any way that I only transfer those tables to the Terminal Server
and use DTS there to update the SQL Server ?
Thanks
Peter
"ChrisR" <noemail@.bla.com> wrote in message
news:eMhOclFlFHA.3260@.TK2MSFTNGP10.phx.gbl...
> If you have any backup encryption type of software (SQL LiteSpeed) then
> that would be the way to go. A gig may be painful, really a call for your
> company.
>
> "Peter" <anonymous@.discussions.microsoft.com> wrote in message
> news:054e01c593f5$1d51fc30$a401280a@.phx.gbl...
>>I have updated a number of tables at home (size is around
>> 200MB) and I am able to connect to office via Terminal
>> Server.
>> I would like to know what is the best tools I can use to
>> transfer the tables to my office and update them.
>> Should I transfer the whole database (around 1 GB) and use
>> DTS on Terminal Server to update the whole database ?
>> Thanks
>|||Hi
If you don't have SQL Server port access, or file access to the remote
server, you can't transfer the data from your home machine.
Terminal server does not allow local copy to a remote server.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:eq4SdMKlFHA.2852@.TK2MSFTNGP14.phx.gbl...
> Dear Chris,
> Is there any way that I only transfer those tables to the Terminal Server
> and use DTS there to update the SQL Server ?
> Thanks
> Peter
> "ChrisR" <noemail@.bla.com> wrote in message
> news:eMhOclFlFHA.3260@.TK2MSFTNGP10.phx.gbl...
>> If you have any backup encryption type of software (SQL LiteSpeed) then
>> that would be the way to go. A gig may be painful, really a call for your
>> company.
>>
>> "Peter" <anonymous@.discussions.microsoft.com> wrote in message
>> news:054e01c593f5$1d51fc30$a401280a@.phx.gbl...
>>I have updated a number of tables at home (size is around
>> 200MB) and I am able to connect to office via Terminal
>> Server.
>> I would like to know what is the best tools I can use to
>> transfer the tables to my office and update them.
>> Should I transfer the whole database (around 1 GB) and use
>> DTS on Terminal Server to update the whole database ?
>> Thanks
>>
>|||Dear Mike,
I am able to access the Terminal Server. From the Terminal Server, I am
able to connect to the SQL Server via Remote Access. On the other hand, we
have installed Enterprise Manager on the Terminal Server.
Thanking you in anticipation.
Peter
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:Oe9ZlbNlFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Hi
> If you don't have SQL Server port access, or file access to the remote
> server, you can't transfer the data from your home machine.
> Terminal server does not allow local copy to a remote server.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Peter" <anonymous@.discussions.microsoft.com> wrote in message
> news:eq4SdMKlFHA.2852@.TK2MSFTNGP14.phx.gbl...
>> Dear Chris,
>> Is there any way that I only transfer those tables to the Terminal Server
>> and use DTS there to update the SQL Server ?
>> Thanks
>> Peter
>> "ChrisR" <noemail@.bla.com> wrote in message
>> news:eMhOclFlFHA.3260@.TK2MSFTNGP10.phx.gbl...
>> If you have any backup encryption type of software (SQL LiteSpeed) then
>> that would be the way to go. A gig may be painful, really a call for
>> your company.
>>
>> "Peter" <anonymous@.discussions.microsoft.com> wrote in message
>> news:054e01c593f5$1d51fc30$a401280a@.phx.gbl...
>>I have updated a number of tables at home (size is around
>> 200MB) and I am able to connect to office via Terminal
>> Server.
>> I would like to know what is the best tools I can use to
>> transfer the tables to my office and update them.
>> Should I transfer the whole database (around 1 GB) and use
>> DTS on Terminal Server to update the whole database ?
>> Thanks
>>
>>
>

best way to keep updated database copy on same server

Hi,
I was hoping to get some advise on my SQL 2000 server: I would like an
automatically updated copy of the main database kept on the same server. Any
changes made to this copy would NOT be synchronized to the main database,
however, changes made to the main database need to be immediately synched to
the copy. This would give me an up-to-date copy that can be worked with and
manipulated in ways that would be inappropriate for the actual database. Any
thoughts on the easiest solution would be greately appreciated.
thx,
AF
AF,
transactional replication and log shipping are often used for reporting
databases. However you say you want to actually manipulate the data - if
this means data changes are required, then the above 2 methods will not work
(log shipping forces the database into read-only mode and tr will probably
have synchronization errors) so a different solution is required. In this
case I'd use either snapshot replication or ship the backups and restore -
obviously both methods will have a sizeable delay in data transfer.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I checked in to transactional replicaiton, which looks promising. If I could
have the main database update/overwrite the copy every 30 minutes or so, that
should work. Since I am not worried about replicating to a different
physical server, do you know if there is a simpler method for such updates?
I appreciate the help,
AF
"Paul Ibison" wrote:

> AF,
> transactional replication and log shipping are often used for reporting
> databases. However you say you want to actually manipulate the data - if
> this means data changes are required, then the above 2 methods will not work
> (log shipping forces the database into read-only mode and tr will probably
> have synchronization errors) so a different solution is required. In this
> case I'd use either snapshot replication or ship the backups and restore -
> obviously both methods will have a sizeable delay in data transfer.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||I have concerns about using transactional replication for your purposes. You
mention that there will be manipulation of the data on the subscriber. If by
this you mean data changes, this'll likely cause errors and a failure of the
distribution agent. If you're just doing reads then it'll be ok.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)