January 31, 2022 at 10:51 pm
Hi,
This is part of the query which I want to change by Removing OR. What is the best way to write a condition like this for better performance? Should I use IN( Looks like OR and IN works same when it comes to execution plan. Please advise.
Where test_ID =@Some_CustID OR abcd.oldsome_custID =@Some_CustID
If there is huge difference between estimated and actual rows in a execution plan..where should one focus in the query..is it where clause or indexes on the tabel. Could duplicate indexes on the table cause issue of scanning?
January 31, 2022 at 11:34 pm
"OR" is good choice in this scenario, & conveys intent clearly. You could try unioning two separate selects, w/ test_ID in the where clause of one, & oldsome_cust_ID in the where clause of the other (watch out for duplicates) to avoid the OR, but that's generally more expensive.
RE. difference between estimated & actual rows:
Look at the actual (not estimated) execution plan. What's happening? What index(es) are/are not being used? Are you seeing table/index scans where one would expect to be able to seek?
Are statistics up to date?
Duplicate indexes probably aren't in themselves going to result in a scan. But if you really have duplicate indexes, eliminate the duplicates -- they add unnecessary overhead.
February 1, 2022 at 1:02 am
Would need to see the tables' DDL and the actual query plan. Can't troubleshoot air.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
February 1, 2022 at 1:20 am
I plan on sharing query so that it helps you advise. Thanks
February 1, 2022 at 1:38 am
How can I check if stats are update to date..is it by going to table ( expand) statistics folder and see properties? or can I use sample below query? if not, then please share any best pratice here.
Update STATISTICS dbo.Employee IX_Employee_Organization Node WITH FULLSCAN
also, should I update stats before testing? thanks!
February 1, 2022 at 2:57 pm
If you're exploring if stats are the problem, no, I wouldn't update first. Instead, look to the execution plans and see what the estimated number of rows is. Then, look at the data and see what the actual number of rows is (also, looking at an execution plan with runtime metrics, compare actual & estimate row counts). That will tell you more about your statistics. Then you can decide whether or not to update them, and if you update them, whether or not you need FULLSCAN.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 7, 2022 at 3:34 pm
This is a tricky question that depends on the product. In the case of SQL Server, the IN() is a shorthand for OR-ed predicates. We added it to the SQL language by stealing it from Pascal. However, if you use DB2, Postgres, and probably some other SQL versions, the performance depends on the length of the IN() list. When it gets to a certain size, the optimizer kicks in. The first thing it does is order the parameters, and remove duplicates (this is usually redundant but it's the first step the optimizer does) . The second step in many of these products is to either build a binary search tree (so the expected search time drops to log2(n) instead of n) or a hash table (so the expected search time drops to one probe). Even better, if it creates a minimally perfect hash, and there's no wasted space. This leads to some funny performance issues. If the optimization doesn't kick in until (k) parameters, then you can have a situation where it pays you to make a longer parameter last to get over the critical size..
Even for short parameter lists, I like using the IN() instead of explicitly expanding it out to OR-ed search conditions; I find it more readable.
Please post DDL and follow ANSI/ISO standards when asking for help.
February 21, 2022 at 5:36 am
This was removed by the editor as SPAM
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply