473,574 Members | 2,260 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

update spends 1800 times more than select

001
Hello,

The select statement needs only 1 second to complete the query.
But the update statement spends 30 minutes. Why?
SELECT STATEMENT:
declare @IDate smalldatetime
select @IDate=col001 from USDay
select * from USDay A
join (
select US990010, US990020, US990030, US990040, US990050, US990060,
US990070 from US99000D where US990010=@IDate
) B on A.col001=B.US99 0010 and A.col002=B.US99 0020
where B.US990010 is not null
UPDATE STATEMENT:
update US99000D
set US990030=A.col0 03,
US990040=A.col0 04,
US990050=A.col0 05,
US990060=A.col0 06,
US990070=A.col0 07
from USDay A
join (
select US990010, US990020, US990030, US990040, US990050, US990060,
US990070 from US99000D where US990010=@IDate
) B on A.col001=B.US99 0010 and A.col002=B.US99 0020
where B.US990010 is not null
INDEX:
clustered index: US990020, US990010
non-unique index: US990010, US990020
Nov 7 '05 #1
4 2239
001 (00*@ms8.url.co m.tw) writes:
The select statement needs only 1 second to complete the query.
But the update statement spends 30 minutes. Why?
SELECT STATEMENT:
declare @IDate smalldatetime
select @IDate=col001 from USDay
select * from USDay A
join (
select US990010, US990020, US990030, US990040, US990050, US990060,
US990070 from US99000D where US990010=@IDate
) B on A.col001=B.US99 0010 and A.col002=B.US99 0020
where B.US990010 is not null
UPDATE STATEMENT:
update US99000D
set US990030=A.col0 03,
US990040=A.col0 04,
US990050=A.col0 05,
US990060=A.col0 06,
US990070=A.col0 07
from USDay A
join (
select US990010, US990020, US990030, US990040, US990050, US990060,
US990070 from US99000D where US990010=@IDate
) B on A.col001=B.US99 0010 and A.col002=B.US99 0020
where B.US990010 is not null

Unless there is a blocking issue, I would suspect it is because the
US99000D is not match against the instance in the derived table. How this
work:

update US99000D
set US990030=A.col0 03,
US990040=A.col0 04,
US990050=A.col0 05,
US990060=A.col0 06,
US990070=A.col0 07
from US99000D
join USDay A on A.col001=B.US99 0010 and A.col002=B.US99 0020
where B.US990010=@IDa te
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Nov 7 '05 #2


It works fine. But I don't understand why you said *the
US99000D is not match against the instance in the derived table* ?

*** Sent via Developersdex http://www.developersdex.com ***
Nov 7 '05 #3
Peter Yu (yu******@ms3.h inet.net) writes:
It works fine. But I don't understand why you said *the
US99000D is not match against the instance in the derived table* ?


The UPDATE FROM syntax is proprietary for MS SQL Server and Sybase. It
works well most of the time, but there are some funny tweaks when you
leave the main row. I recommend that you always include the target table
as the first table in the FROM clause to avoid accidents like this. What
happened now was that you probably got a cartesian product between the
target table and the tables in the FROM clause.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Nov 7 '05 #4
On Mon, 7 Nov 2005 15:08:30 +0800, 001 wrote:
Hello,

The select statement needs only 1 second to complete the query.
But the update statement spends 30 minutes. Why?
SELECT STATEMENT:
declare @IDate smalldatetime
select @IDate=col001 from USDay
select * from USDay A
join (
select US990010, US990020, US990030, US990040, US990050, US990060,
US990070 from US99000D where US990010=@IDate
) B on A.col001=B.US99 0010 and A.col002=B.US99 0020
where B.US990010 is not null
UPDATE STATEMENT:
update US99000D
set US990030=A.col0 03,
US990040=A.col0 04,
US990050=A.col0 05,
US990060=A.col0 06,
US990070=A.col0 07
from USDay A
join (
select US990010, US990020, US990030, US990040, US990050, US990060,
US990070 from US99000D where US990010=@IDate
) B on A.col001=B.US99 0010 and A.col002=B.US99 0020
where B.US990010 is not null
INDEX:
clustered index: US990020, US990010
non-unique index: US990010, US990020


Hi 001,

The proprietary UPDATE FROM syntax has many issues. One of them is that
these two versions are considered equal:

UPDATE Table1
SET Something = SomethingElse
FROM Table2
WHERE xxx = yyy

or

UPDATE Table1
SET Something = SomethingElse
FROM Table2, Table1
WHERE xxx = yyy

The technicalities: the table name to be updated is looked for in the
FROM clause. If one match is found, that table is used. If two matches
are found, an error is raised (you'll have to use an alias in that
case). And if no matches is found, the table is implicitly added to the
FROM clause.

Since the tables in your UPDATE's FROM clause are called USDay, with
alias A and (no name for derived table), with alias B, the table name
US99000D is added to the FROM clause. Your UPDATE is equivalent, not to
the SELECT statement above, but to this one below:

select * from USDay A
join (
select US990010, US990020, US990030, US990040, US990050, US990060,
US990070 from US99000D where US990010=@IDate
) B on A.col001=B.US99 0010 and A.col002=B.US99 0020
CROSS JOIN US99000D
where B.US990010 is not null
The best way to prevent these problems is two teach yourself the
following guidelines:

1. Avoid the proprietary UPDATE ... FROM and DELETE FROM ... FROM when
ever possible. Only use it if ANSI syntax has distinct disadvantages.

2. If you have to use UODATE ... FROM, *always* include all tables
(including the one to update) in the FROM; *always* give all tables in
the FROM an alias, and (and here comes the hammer) **always** include
the _alias_, not the table name after the UPDATE keyword.

Example:
UPDATE a
SET Something = b.SomethingElse
FROM Table2 AS b
INNER JOIN Table1 AS a
ON a.xxx = b.yyy
Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Nov 7 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
6149
by: Gent | last post by:
am using FOR UPDATE triggers to audit a table that has 67 fields. My problem is that this slows down the system significantly. I have narrowed down the problem to the size (Lines of code) that need to be compiled after the trigger has been fired. There is about 67 IF Update(fieldName) inside the trigger and a not very complex select statement...
3
7501
by: Colin Spalding | last post by:
In Access, if I want to update one table with information from another, all I need to do is to create an Update query with the two tables, link the primary keys and reference the source table(s)/column(s) with the destination table(s)/column(s). How do I achieve the same thing in SQL? Regards Colin *** Sent via Developersdex...
2
5831
by: Joshua Moore-Oliva | last post by:
I have a query that is asking me to GROUP a column, yet when I GROUP it it causes an error near GROUP. What is very strange about the following query is that the line list_size_active = COUNT(NEW.active) PROPERLY sets the value to the number of new items.
0
1429
by: Bill Agee | last post by:
I have a Select query which contains a field called AMWnum (long) which represents a sequence # of sorts. If I want to copy a row of information and paste it on the bottom, all is OK. I have another query which finds the max AMWnum in the table and then adds 1. What I would like to do is automatically update the copied record's AMWnum...
3
2514
by: Hartmut Schroth | last post by:
Hi, I need a solution for the following problem: In the item template of a datalist control I have already a button control with the commandname set to "select" to perform some specific database action. I additionally want to update an integer value in the underlying data source by simply clicking an a check box or an imagebutton...
1
1154
by: Shawn | last post by:
Hi. I have a stored procedure in my Sybase 11.9, but I get an error when I try to update a row. This happends when I have more than 126 chars in one of my parameters. The parameter is declared varchar(254) and so is the field that I'm trying to update. This is the error I'm getting: WARNING - Fatal Error 4020 occurred at Feb 4 2005...
6
1899
by: PsyClone | last post by:
Im fairly new to access and would prefer not to use any scripting as such, but Im trying to create aselect query based on two tables: tblProduction, from which the query uses DeptID, ProductionQty, and ProductionDate fields; - and tblDepartment table, from which ive used the Department field to simply link a department name to the corresponding...
9
1700
by: pandaking | last post by:
Hi there everyone, new here but after reading around it seems like I might hang about - so many helpful threads! I have a slight problem. This flying club near me has employed me to make them a database. I got into it because I know one of the guys who works there, and he mentioned they need one and I did Computing A level a few years back so...
3
1658
by: lezilgeorge | last post by:
I wanted to run an undate query in a for loop several times.....But When i run this only the 1 st row is getting updated and shows an error which says String or binary number is truncated, i am using the same record set object for this.is that a problem as the same name gets repeated in the loop
0
7826
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
8085
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
1
7838
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8126
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5637
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3763
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3781
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1362
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1090
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.