473,287 Members | 1,813 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

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.US990010 and A.col002=B.US990020
where B.US990010 is not null
UPDATE STATEMENT:
update US99000D
set US990030=A.col003,
US990040=A.col004,
US990050=A.col005,
US990060=A.col006,
US990070=A.col007
from USDay A
join (
select US990010, US990020, US990030, US990040, US990050, US990060,
US990070 from US99000D where US990010=@IDate
) B on A.col001=B.US990010 and A.col002=B.US990020
where B.US990010 is not null
INDEX:
clustered index: US990020, US990010
non-unique index: US990010, US990020
Nov 7 '05 #1
4 2216
001 (00*@ms8.url.com.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.US990010 and A.col002=B.US990020
where B.US990010 is not null
UPDATE STATEMENT:
update US99000D
set US990030=A.col003,
US990040=A.col004,
US990050=A.col005,
US990060=A.col006,
US990070=A.col007
from USDay A
join (
select US990010, US990020, US990030, US990040, US990050, US990060,
US990070 from US99000D where US990010=@IDate
) B on A.col001=B.US990010 and A.col002=B.US990020
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.col003,
US990040=A.col004,
US990050=A.col005,
US990060=A.col006,
US990070=A.col007
from US99000D
join USDay A on A.col001=B.US990010 and A.col002=B.US990020
where B.US990010=@IDate
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.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.hinet.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****@sommarskog.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.US990010 and A.col002=B.US990020
where B.US990010 is not null
UPDATE STATEMENT:
update US99000D
set US990030=A.col003,
US990040=A.col004,
US990050=A.col005,
US990060=A.col006,
US990070=A.col007
from USDay A
join (
select US990010, US990020, US990030, US990040, US990050, US990060,
US990070 from US99000D where US990010=@IDate
) B on A.col001=B.US990010 and A.col002=B.US990020
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.US990010 and A.col002=B.US990020
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
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...
3
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...
2
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 =...
0
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...
3
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...
1
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...
6
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, ...
9
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...
3
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...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

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.