March 25, 2016 at 9:21 am
Greetings all 🙂
I am running into a situation I have never experienced in 12 years.
I have an Except query, comparing two tables. This query runs in 2 seconds. It rarely every returns data, so I use an 'If Exists' logic to see if there is data, otherwise move on in the proc.
For reasons unknown, when I wrap this in any sort of conditional logic, it takes longer than 30 seconds to execute, which is extremely baffling. I have tried If exists, while exists, wrapping it in a '(select count(*) from (subquery) as x) >0' type query. Nothing changes is.
Does anyone know why this occurs? The tables are not large, they are 40k records each, and only one column is being compared in Except query, which are both tables primary key. So logistically, there should be nothing from a table structure, or query structure standpoint causing this.
Any ideas as to why SQL is doing this?
Link to my blog http://notyelf.com/
March 25, 2016 at 8:50 pm
Try using @@Rowcount instead.But make sure nothing gets executed before the except clause and if it does then reset the @@rowcount value to 0.
select col1,col11 from table1
except
select col2,col22 from table1
if @@rowcount>=1
Exec MyProc
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
March 26, 2016 at 8:04 am
Execution plans to understand why this is occurring would be helpful.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 26, 2016 at 12:25 pm
Mostly: What Grant says: we need execution plans (actual execution plans, posted as sa .sqlplan file attachment) for the fast query as well as for the slow query, plus of course the query itself, and the table structures (posted as an executable SQL repro script).
Some wild speculation based on the limited description you gave:
For a normal query, the optimizer will create a plan that processes all data as fast as possible. When you use EXISTS, SQL Server instead creates a plan that is aimed at finding the first match as soon as possible - because after that first match, execution can stop anyway.
My guess is that your "normal" query has a plan that scans entire tables or indexes and uses hash match to join that together. With the EXISTS, SQL Server somehow (probably due to statistics or to a hard-to-interpret predicate) that a reasonable percentage of the rows will match, so instead of reading the entire table and hash matching, it will scan one table and then use a nested loops join into a seek in a joined table or even a lookup. If for instance the statistics indicate that 5% of the rows match, then this seek should execute on average 20 times until a hit is found, and the plan is indeed cheaper than the plan to get all results.
Unfortunately, the assumptions used to choose the plan are wrong, there are in reality no matches at all and the nested loops join ends up processing all rows from the first input, and executing the seek in the second input for each of those rows.
</speculation>
(And here starts the part where you post the actual plans and show that all of this is utter nonsense in your case...)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply