Which is better for performance

Question

0.00/5 (No votes)

See more:

Which is a better statement in terms of performace

This way:

Declare @xx int
@xx= select *from tableX where fieldX =@input1

Select *from table1
Join table2 
on Table1.F1=Table2.F1
And Table2.xx=@xx
Join Table3
On Table3.D1=Table2.D1
And Table3.xx=@xx

---------------------------------
Or is this better

Select *from table1
Join table2 
on Table1.F1=Table2.F1
Join Table3
On Table3.D1=Table2.D1
Join TableX
On TableX.xx = Table2.xx
And TableX.xx=Table3.xx
Where TableX.FieldX=@input

What I have tried:

As you can see above, which is better? selecting tableX first to get the value then use it in on condition
Or join to tableX

Posted 27-Dec-21 10:43am

Member 14800672

Updated 29-Dec-21 6:53am

Add a Solution

Comments

Afzaal Ahmad Zeeshan 27-Dec-21 17:05pm

This depends heavily on the query planner of your SQL engine. Consult the documentation of your SQL engine.

The query planner decides how to execute a query, and it will make sure that the best approach is taken. If you want to see for yourself, you can use the tools for the SQL engine to preview the query plan; PostgreSQL has an explain for this, and SQL Server can show the query plan. Then you can review both and see for yourself.

Mohibur Rashid 27-Dec-21 23:22pm

This is the answer.

Afzaal Ahmad Zeeshan 28-Dec-21 17:38pm

Thanks, Rashid. :-)

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2021-12-29T00:10:00

The comment from @afzaal-Ahmad is the right way forward.

There are many articles here on CodeProject that will help you analyse the performance of your queries. Here is a small selection
- SQL Server Profiler Step by Step[^]
- How to Analyze SQL Server Performance[^]
- SQL Tuning Tutorial - Understanding a Database Execution Plan (1)[^]
And this one gives tips on how to improve performance
- SQL Server Performance Tips and Guidelines[^]

A couple of things to point out with your examples
- Your first example does not compile in SQL Server - it's a good idea to tag the actual RDBMS that you are using.
- If tableX has more than one row that matches the criteria then you are going to get an error with the line

SQL

set @xx= (select * from tableX where fieldX =@input1);

namely

Quote:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

- Similarly if tableX has more than one column you are then going to get the error

Quote:
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

My advice is to get your logic right first, then worry about the performance

Maciej Los · Answer 2 · 2021-12-29T06:53:00

Solution 2

You check this out by yourself - using SQL Server Profiler and Query Analyzer | Microsoft Docs[^]

Posted 29-Dec-21 6:53am

Maciej Los