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?

Tiro

Do you have anything specific in mind? – Yangyangtze

No. just a general question,so that i can sharpen my skill while writting join statement – Tiro

Then you need to provide more details, regarding what you think is standard. Are you looking for alternatives to joins or alternative ways to write joins? Your question is not very focused or clear. – Yangyangtze

Answer 1

Using (NOLOCK) on joins may increase performance, if you want/can read uncommitted records. When should you use "with (nolock)"

Sudbury

#1453496 Nolock shouldn't be used all the time or for all the tables. It is actually a good idea to use for old/temp/archive tables, not everyday transaction tables. Utilize indexed tables/view, partition tables, cursors etc. – Milner

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.

Docila

that's really amazing, i didn't know that the foreign keys does not have an index by default. that improved my query performance by 100 times, joining 5 Tables, results are ~7500 rows !! (before was 5.5 seconds, now is 55 ms) +1 – Measured

Can we get same behaviour with MySQL as well? – Sectary

Answer 3

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 *

Load

Good tip on not using SELECT *, I never use it any more, only select the fields you need and this can sometimes hugely speed up queries. – Niagara

Answer 4

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.

Cementum

Answer 5

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)

Wisteria

Answer 6

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.

Yangyangtze

Answer 7

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 ?

Makings

Answer 8

Comnbine and Group has worked the fastest for me, but you have to write some extra steps to clean up the data so it provides the right output.

Twofold

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Proxy