April 16, 2024 at 7:18 am
hi,
I have seen people use exists when exists is required and no cols are required in select.
But I notice sometimes exists slows down and join performs better
q1) is there any news i should know , that one should not use exists or in , and should go for join etc etc
yours sincerely
April 16, 2024 at 7:32 am
If relationship is 1 - 1 -> inner join
If relationship is 1 - n -> exists
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 16, 2024 at 2:44 pm
I've always thought you should only use inner join if you want to return columns from the other table, but this link suggests join is preferable if there is an index on the join column(s). https://www.mssqltips.com/sqlservertip/6659/sql-exists-vs-in-vs-join-performance-comparison/
I used to refer to articles by Gail Shaw, but they don't seem to be working and could be out of date now. When it comes to left join vs not exists, I usually use not exists. https://erikdarling.com/why-not-exists-makes-more-sense-than-left-joins/
April 16, 2024 at 5:56 pm
I've always thought you should only use inner join if you want to return columns from the other table, but this link suggests join is preferable if there is an index on the join column(s). https://www.mssqltips.com/sqlservertip/6659/sql-exists-vs-in-vs-join-performance-comparison
That is NOT what that article suggests. The article discusses a methodology for comparing various alternatives and determining which is more efficient. While it did find that a join performed better in this VERY SPECIFIC CASE, it did not draw any conclusions about how that might generalize.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
April 16, 2024 at 6:34 pm
100% agree with Drew here and in your specific scenario, JOIN may perform better or worse. My advice is to test on your system as is (ie don't add extra indexes, don't partition the table, don't change anything at the DDL level) but do a test on a test system and verify the performance differences. Also, do more than 1 test of each type so you can get an average run time, not just a single run. First run will create a new plan, running it a second time will reuse the plan, so second run will be a little bit faster from start to finish (sometimes the difference though is so close to 0.000 seconds you can't tell), so I usually do 3 to 4 runs of each query so I can get a good average run time and can say that method A is faster for this specific query.
I did a live demo one time trying to show how indexes helped performance of SELECTs but how HEAPs were faster for inserts and I ran it on my personal laptop during a presentation. ALL the times I ran it previously, it worked how I described. In the actual live presentation, Windows updates kicked in during the HEAP insert and HEAP was about 5 times slower than inserting into a table with 2 indexes (1 clustered, 1 non-clustered). Made me look like a liar, but I found the issue and corrected it for future demos (update prior to the demo and use a VM that is internet-disabled or use screenshots to show things).
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 17, 2024 at 9:42 am
This was removed by the editor as SPAM
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply