Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
toddhd
Yak Posting Veteran
55 Posts |
 Posted - 2006-04-13 : 13:39:46
|
| I just started a new job. I was asked to modify some search queries on the company website. The search queries are pretty long and intense. I noticed that the person who wrote the queries likes to inner join to subqueries. Pseudo-example:SELECT ... FROM <table> INNER JOIN(SELECT ... FROM <anothertable>) ON ...Quite often these are nested a few times as well. I remember a few years back that we ran some time tests to see which was faster - an inner join, or a subquery. The Inner Join's blew subqueries away in speed - much, much faster. So this technique of inner joining to a subquery screams out for attention. I haven't time tested it yet, but will soon. In any event, if someone else has had a similar situation, I'd like to know of ways to make it faster. For example, if I turned the subquery into a View for example, would that speed up the search? Or perhaps if I turned it into a UDF somehow? Or a temporary table? I was hoping someone else had come across this and already found an optimum way to pull the data.Thanks for any suggestions you might have. |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-13 : 13:46:31
|
The problem with most sub-queries that I have seen (i.e. which are like what I think you have) is that they are not constrained:SELECT *FROM EMPLOYES AS E JOIN ( SELECT emp_no FROM TimeSheet WHERE InvoicedFlag = 'N' ) AS T ON T.emp_no = E.emp_noWHERE emp_Department = 'PROGRAMMING' The inner sub-query has processed all employees, where the outer one is only interested in people who are in the PROGRAMMING department.In these circumstances if you duplicate the emp_Department = 'PROGRAMMING'logic in the inner sub-select, so it is constrained to return fewer rows, it makes the world of difference. (clearly in my example an INNER JOIN would do, but the Real World often isn't that simple!)Kristen |
 |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-04-13 : 13:50:01
|
| that is a join on a derived table. Derived tables help reduce disk i/o and are usually an optimization.you will have to look at the execution plan to see what is going on. I also recommend you SET STATISTICS IO ON and SET STATISTICS TIME ON in your query analyzer session. THis will show you how many disk IOs are actually occuring. You can then compare your different approaches (use view, derived table, temp table, etc.) and see exactly what is going on.-ec |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-13 : 13:55:01
|
| "THis will show you how many disk IOs are actually occuring"(toddhd: best to compare the Logical values, which are repeatable, rather than the Physical ones which aren't) |
|
|
|
toddhd
Yak Posting Veteran
55 Posts |
Posted - 2006-04-13 : 14:10:29
|
| Thanks for the suggestions, I appreciate it. :)-Todd Davis |
|
|
|
|
|
|
|
|
A question about speed