Solved

faster sql

Posted on 2013-12-17
14
317 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 34

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

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.

Join & Write a Comment

Suggested Solutions

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that useā€¦
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

744 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

16 Experts available now in Live!

Get 1:1 Help Now