Is there an alternative to joins to increase performance?

Question

Is there an alternative to joins to increase performance?

Edit (gbn): related to join-or-correlated-subquery-with-exists-clause-which-one-is-better

---

Why didn't anyone mention about nested loop joins?

Answer 1

Relational databases are optimized to use Joins, so in most cases using the is the most preformant thing you can do.

If your queries are slow, you need to optimize them - perhaps you are missing an index or two, perhaps you can rewrite the where clauses to reduce the number of returned rows.

You can use sub-queries and temp tables, but chances are that a join would still be fastest. You will have to test in your own environment to see.

Answer 2

Not an "alternate" way to JOINs, but a tip to increase JOIN performance: in SQL Server that a lot of folks don't know is that you should always put a non-clustered index on a foreign key column. Several folks believe SQL Server does this automatically - it does not.

So if you have a table Customer, it probably has a primary key something like CustomerID. SQL Server will put an index on that automatically.

However, if you have a table Order that has a foreign key relationship with Customer, there is by default no index on the column Order.CustomerID. But such an index is very useful and helpful for joins and lookups, so that's a best practice I always recommend: put an index on all your foreign key columns in a table.

Answer 3

In any non-trivial DB driven application there is no way ... for you to avoid joins.

Joins...themselves are not the root cause of the problem but bad performance could be the result of anything from poorly written queries to poorly designed database.

Yes...in some cases joins encapsulated in stored functions can be avoided by using prepared fields. That is, if you are sure you will be needing a resultant value from a certain join for repeated use..you might as well calculate it once and store it for repeated use.

Correlated Sub-queries are another alternative.

In general if you are looking to sharpen your skill the ...question you should be asking is: How to write efficient queries ?

Answer 4

From your other question

select * 
from ContactInformation c 
where exists (select * from Department d where d.Id = c.DepartmentId )

select * 
from ContactInformation c 
inner join Department d on c.DepartmentId = d.Id  

If you want output from both tables, then you have option other then JOIN. The 2nd query here.

If it's slow, then generally:

• you have primary key/indexes?
• consistent datatypes (the DepartmentId/id columns)
• don't use SELECT *

Answer 5

Strategies for mitigating performance of joins:

• Indexing
• Denormalization
• Caching results
• Using a NoSQL database (no SQL = no joins, q.e.d.)

All of these strategies optimize for specific queries. You can't make a general-purpose solution that can improve all queries.

Answer 6

Syntactically, there is no alternative way but just a few techniques that might help you regarding query performance with very large volumes of data:

• If applicable and the number of columns returned by the query are not many you can use INTERSECT, EXCEPT OR UNION
• If the query is very complex and is of many steps on very large volumes of data, divide and conquer with temp tables.
• If the query is back to a report presenting some information that could be of yesterday's image of the data you can use sql server agent jobs to calculate and save the result in a table to be used as a back for the report instead of the query or as an alternative use indexed views to get the result.
• If some information like count of rows in a table takes too long to get you can use the metadata tables of the table to get such piece of information.This is not only for the count of rows in a table.You can get a lot of information from the metadata with no need to calculate it.(Keep in touch with this site)