November 21, 2007 at 1:30 pm
Hi there,
I have had the following question for some time, does anyone know the answer?
What is faster:
SELECT
t1.colx,
t2.coly
FROM table1 t1
INNER JOIN table2 t2 ON t1.id = t2.id
WHERE t1.some_col = 'something'
or:
SELECT
t1.colx,
(SELECT t2.coly FROM table2 t2 WHERE t2.id = t1.id) AS coly
FROM table1 t1
WHERE t1.some_col = 'something'
I sometimes have queries with even up to 10 or 15 INNER JOINS, so if the other method is slightly faster, it may improve performance I guess...
Thanks,
Raymond
November 21, 2007 at 1:32 pm
It looks like your post got cut off, can you re-post your code.
Also, your question states 'what is faster', what do your tests show?
November 21, 2007 at 1:48 pm
In general:
SELECT
t1.colx,
(SELECT t2.coly FROM table2 t2 WHERE t2.id = t1.id) AS coly
FROM table1 t1
WHERE t1.some_col = 'something'
.. this will be slower. Also, depending on cardinality it does not produce an equivalent resultset to the INNER JOIN. What if there are 2 or more records in table2 with a matching id value ?
If you have 15 joins and you're concerned about slowness, then correct indexing, good data modelling and up to date statistics should be the things you consider first before trying to re-write the SQL code to trick the optimizer.
November 22, 2007 at 12:17 am
The second is a correlated sub query. Depending on the optimiser, it's ikely that the subquery will be executed for each row of the outer query. Essentially, it's a cursor in disguise.
Sometimes the optimiser is smart enough to convert the subquery into a join and evaluate it just like the first option, but not always
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 22, 2007 at 12:17 am
Thanks for your comments, I'm glad to know what approach is best.
FYI, what I use it mostly for is a lookup table. For example, "gender" is stored as 1 or 2, and the value that is displayed on the user's screen is read from a lookup table (1=male, 2=female). In these cases there is always just one recordset to be found.
Thanks again,
Raymond
November 23, 2007 at 10:08 am
>>FYI, what I use it mostly for is a lookup table. For example, "gender" is stored as 1 or 2,
>>and the value that is displayed on the user's screen is read from a lookup table (1=male, 2=female).
>>In these cases there is always just one recordset to be found.
Your approach to solving this solution is suboptimal.
Fastest is to simply hard code it thusly:
select field1, case gender when 1 then 'Male' when 2 then 'Female' else 'Unknown' end
from mytable
Since most people don't like hardcoding, this is a better alternative:
declare @male varchar(10), @female varchar(10)
select @male = gendername from gendertable where id = 1
select @female = gendername from gendertable where id = 2
--actually you could combine those two hits into one with a case also
select field1, case gender when 1 then @male when 2 then @female else 'Unknown' end
from mytable
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 23, 2007 at 10:21 am
>>Your approach to solving this solution is suboptimal.
So, for every query in your system where there is a join to a small "lookup" table, you'd replace the join with a pre-select into variables ? Just to avoid the join, because the join is not "optimal" ?
What happens when requirements change ? What happens when your business needs to capture transgendered or gender re-assignment ? Now you have to find all this "optimized" code and fix it, instead of just adding a record to the "lookup" table.
November 23, 2007 at 10:28 am
The OP asked what was faster. I was answering his/her question. And yes, I have used the approach I mention to achieve optimal performance in production systems. In large scale reporting environments a variety of steps to improve performance often carry consequences like you mention.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 25, 2007 at 2:38 am
Hi again,
In addition to the discussion..... a lookup table for male/female was just an example.
A reason for me to use lookup tables is that users can fill their own selectboxes. For example: businessunits. My users are businesses, and they each have their own businessunits. Instead of having to deal with who-has-what-businessunit-and-who-hasn't myself, I made a page on which they can define their own businessunits, stored in their own database, and which will appear in a selectbox when needed.
So using a lookup table or not is no issue for me; what way to retrieve the data (join or select) when thinking about performance is.
Thanks,
Raymond
November 26, 2007 at 10:18 am
In that case, the JOIN should out perform the SELECT most of the time as PW and Gail have already posted. Write both SQL statements and do a performance comparison and see what it does in your case.
November 26, 2007 at 1:32 pm
There are some other ways you can get good performance on lookup tables while avoiding a join. If you have some lookup tables that you want to avoid joining against, you could always try logic like this, assuming you find a table that you have a lot of reads against:
[font="System"]declare @businessUnit varchar(50);
declare @businessUnitID int;
select @businessUnitID=BusinessUnitID
from BusinessUnitLookup
where BusinessUnitName = @businessUnit;
select ...
from MainTable
inner join...
...
where ... and BusinessUnitID = @businessUnitID;[/font]
This may not work for all cases, but you have to compromise here between code complexity and speed. You can get some good speed out of this, depending on index selectivity, etc. There are other options, too. I've used dynamic SQL to add and remove unnecessary joins depending on what filtering tables are needed. If the performance gains are way better than recompile cost, it could be a good thing to try. This should give you a couple ideas to try, but it is an iterative process.
Thanks,
Eric
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply