Solved

subquery or join?

Posted on 2014-03-22
8
253 Views
Last Modified: 2014-03-25
Which has better performance?

1. SELECT * FROM CONTACTPHONE CE
LEFT JOIN CALLS CL ON SUBSTR(replace(replace(replace(replace(CE.NUMBER,' ',''),'-',''),'(',''),')',''), -10, 10)=SUBSTR(CL.NUMBER, -10, 10) AND CE.ORID=CL.ORID

2. SELECT * FROM CONTACTPHONE CE
LEFT JOIN CALLS CL ON SUBSTR(replace(replace(replace(replace(CE.NUMBER,' ',''),'-',''),'(',''),')',''), -10, 10)=(SELECT SUBSTR(CL.NUMBER, -10, 10) AND CE.ORID=CL.ORID

Thank you.
0
Comment
Question by:myyis
8 Comments
 
LVL 40

Expert Comment

by:Sharath
ID: 39948078
Can you explain what are you trying to achieve with some data?
0
 

Author Comment

by:myyis
ID: 39948450
The table  CONTACTPHONE is a list of customers with phone numbers.

Table CALLS is filled  when an outbound call is received. We get the number calling but we don't know whose number it is.

So the problem is reaching the customer name using the numbers in CALLS table
0
 

Author Comment

by:myyis
ID: 39948527
The 2nd alternative should be like this, I misedited, sorry for that

2. SELECT * FROM CONTACTPHONE
WHERE SUBSTR(replace(replace(replace(replace(NUMBER,' ',''),'-',''),'(',''),')',''), -10, 10)=(SELECT SUBSTR(NUMBER, -10, 10)  FROM CALLS )
0
 
LVL 24

Assisted Solution

by:Tomas Helgi Johannsson
Tomas Helgi Johannsson earned 250 total points
ID: 39948540
Hi!

Issue a explain on both queries and post them here.

http://dev.mysql.com/doc/refman/5.6/en/explain.html
http://www.sitepoint.com/using-explain-to-write-better-mysql-queries/

Depending on those result you will quickly see what query has the best performance. And also you will see if there is a need for extra indexes to aid your query performance.

Regards,
   Tomas Helgi
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 13

Accepted Solution

by:
magarity earned 250 total points
ID: 39948679
I suspect the optimizer will get either identical or nearly identical plans for both queries. Both will be bad. The substring functions are going to make it into table scans.  Since there is also the replace function, I doubt even a text index would help.
If you're having performance problems and this is an important query I suggest you look into making a persisted computed column and index it and then use that in your join instead.  There is some minor overhead to the function but it's not bad if you need it.
Here's the overview of computed columns on technet to get you started: http://technet.microsoft.com/en-us/library/ms191250(v=SQL.105).aspx
Here is the overview of creating an index on one:
http://technet.microsoft.com/en-us/library/ms189292.aspx

PS - Your question is filed generically under "SQL Server" and not any particular version. You may not be able to do this with an older version.
0
 

Author Comment

by:myyis
ID: 39948685
MySQL does have a computed column?
0
 
LVL 13

Expert Comment

by:magarity
ID: 39948689
Argh!  I thought I was looking through the SQL Server section, duh!  Sorry!  But the principle applies. Does MySQL not have computed columns?
0
 
LVL 13

Expert Comment

by:magarity
ID: 39954412
Thanks for the points and sorry again about the MS vs MY SQL confusion.  I assume you found out how to make a computed column in your MY SQL?
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

746 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

10 Experts available now in Live!

Get 1:1 Help Now