Solved

faster sql

Posted on 2013-12-17
14
318 Views
Last Modified: 2014-01-02
Which one of these two sqls - SQL1 and SQL2 will be faster to execute and why -

SQL1:  
UPDATE mytable1  A SET A.XC  = ( SELECT B.PXC   FROM   mytable2 B WHERE A.PRCLID=B.PRCLID );
 
SQL2:
UPDATE mytable1  A SET A.XC  = ( SELECT B.PXC   FROM   mytable2 B WHERE A.PRCLID=B.PRCLID ) where exists( select c.prclid from mytable1 c, mytable2 d where c.prclid = d.prclid);

Or will it take same time to execute?

Or can you please give me tips how to make the first sql run faster?

thanks a lot
0
Comment
Question by:at999
  • 5
  • 5
  • 2
  • +2
14 Comments
 
LVL 29

Assisted Solution

by:MikeOM_DBA
MikeOM_DBA earned 100 total points
ID: 39724381
The statements may not produce the same results, the second one appears to be the correct one and may be the faster.
UPDATE mytable1 a
   SET a.xc      =
         (SELECT b.pxc
            FROM mytable2 b
           WHERE a.prclid = b.prclid)
 WHERE EXISTS
         (SELECT '?'
            FROM mytable1 c, mytable2 d
           WHERE c.prclid = d.prclid);

Open in new window

0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39724385
First, they are not equivalent in functionality.  So comparing execution time isn't particularly meaningful.

Second,  which will be faster depends on  how many rows this sub query returns and if the prclid columns are indexed.

(select c.prclid from mytable1 c, mytable2 d where c.prclid = d.prclid)

If that query returns one row, then SQL2 will probably be faster because it'll quickly find the one row and then update it.  Whereas SQL1 ALWAYS updates every row.  If it can't find a value it updates to NULL (even if already was NULL or had a value.)

If, on the other hand, the exists subquery returns every id, then it's a waste of time because SQL2 will do the same thing as SQL1 but with extra effort.
0
 
LVL 23

Expert Comment

by:David
ID: 39724536
There are many, many, many sources here on E-E, and of course on the Net, of tutorials and examples for statement tuning -- if you want to save your points.  Start with Oracle's EXPLAIN_PLAN to learn how to compare execution "cost".  See also the comment immediately above, that the tables' indexing and up-to-date statistics are used by the Optimizer to choose the fastest execution available to it.

The Search control in the top right corner is an undervalued friend.

dvz
0
 

Author Comment

by:at999
ID: 39724723
sdstuber,

d o i need this where clause( the one in bold)  too ?

UPDATE mytable1  A SET A.XC  = ( SELECT B.PXC   FROM   mytable2 B WHERE A.PRCLID=B.PRCLID ) where exists( select c.prclid from mytable1 c, mytable2 d where c.prclid = d.prclid
and c.prclid=a.prclid)
;

Or i dont need - c.prclid=a.prclid
this part is the most confusing
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39724896
and c.prclid=a.prclid


doesn't really add any functionality compared to SQL1

but it does make SQL2 behave differently.
0
 

Author Comment

by:at999
ID: 39724958
how sql2 behave differently ? will it not do what i am trying to do?
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39724962
however,  if you're going to add

and c.prclid=a.prclid

to SQL2

then it makes more sense to remove the copy of mytable1 from the subquery


UPDATE mytable1  A SET A.XC  = ( SELECT B.PXC   FROM   mytable2 B WHERE A.PRCLID=B.PRCLID ) where exists( select null from  mytable2 d where a.prclid = d.prclid)
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 200 total points
ID: 39724972
how will sql2 behave differently?


UPDATE mytable1  A SET A.XC  = ( SELECT B.PXC   FROM   mytable2 B WHERE A.PRCLID=B.PRCLID ) where exists( select c.prclid from mytable1 c, mytable2 d where c.prclid = d.prclid
)

Open in new window


This says update mytable1  for EVERY row as long as ANY row in mytable1 has a corresponding row in mytable2


UPDATE mytable1  A SET A.XC  = ( SELECT B.PXC   FROM   mytable2 B WHERE A.PRCLID=B.PRCLID ) where exists( select c.prclid from mytable1 c, mytable2 d where c.prclid = d.prclid
and c.prclid=a.prclid)

Open in new window


This says update mytable1 for EACH row that matches itself (which, of course, they all do) and has a corresponding row in mytable2


UPDATE mytable1  A SET A.XC  = ( SELECT B.PXC   FROM   mytable2 B WHERE A.PRCLID=B.PRCLID ) where exists( select null from  mytable2 d where a.prclid = d.prclid) 

Open in new window


This says update mytable1 for EACH row that has a corresponding row in mytable2
0
 

Author Comment

by:at999
ID: 39725009
So is this better
UPDATE mytable1  A SET A.XC  = ( SELECT B.PXC   FROM   mytable2 B WHERE A.PRCLID=B.PRCLID ) where exists( select null from  mytable2 d where a.prclid = d.prclid)

than

UPDATE mytable1  A SET A.XC  = ( SELECT B.PXC   FROM   mytable2 B WHERE A.PRCLID=B.PRCLID ) where exists( select c.prclid from mytable1 c, mytable2 d where c.prclid = d.prclid
and c.prclid=a.prclid)
;


pl guide thanks
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 39725046
First one is better, second one has redundant join.
0
 

Author Comment

by:at999
ID: 39725058
thanks so much
0
 

Author Comment

by:at999
ID: 39725061
i will shortly close the question

many thanks to all of you
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39725259
yes,

UPDATE mytable1  A SET A.XC  = ( SELECT B.PXC   FROM   mytable2 B WHERE A.PRCLID=B.PRCLID ) where exists( select null from  mytable2 d where a.prclid = d.prclid)


is better

you only have to read mytable1 once

the other version requires you to read the table twice.
0
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 200 total points
ID: 39725291
One minor point to add: I always recommend writing joins with the "known" values on the right (and I believe that Oracle's query optimizer expects this, so you may save a bit of CPU time if you write your queries this way.

So, I would say that your statement should be:

UPDATE mytable1  A SET A.XC  = ( SELECT B.PXC   FROM   mytable2 B WHERE B.PRCLID=A.PRCLID ) where exists( select null from  mytable2 d where d.prclid = a.prclid);
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Migrating an SQL 2008 database to Oracle 12c 3 90
PL/SQL - Leading zeros 7 60
join 2 views with 5 conditions 3 46
use lov values 2 36
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now