October 4, 2015 at 10:31 am
Our application has serious problems after upgrading to Sql Server 2014. We tracked it down to the huge IN clauses running very slowly. It's not easy to eliminate these clauses because they are dynamically generated by the application. It's a medium term project to fix this.
We need immediate relief from this slowness as our customers are losing business.
Is there a configuration parameter that could help with this?
Thanks
October 4, 2015 at 10:44 am
erb2000 (10/4/2015)
Our application has serious problems after upgrading to Sql Server 2014. We tracked it down to the huge IN clauses running very slowly. It's not easy to eliminate these clauses because they are dynamically generated by the application. It's a medium term project to fix this.We need immediate relief from this slowness as our customers are losing business.
Is there a configuration parameter that could help with this?
Thanks
Have a look here regarding trace flag 9481. You can tell the optimiser to use the old (2012) cardinality estimator.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 4, 2015 at 12:10 pm
erb2000 (10/4/2015)
Our application has serious problems after upgrading to Sql Server 2014. We tracked it down to the huge IN clauses running very slowly. It's not easy to eliminate these clauses because they are dynamically generated by the application. It's a medium term project to fix this.We need immediate relief from this slowness as our customers are losing business.
Is there a configuration parameter that could help with this?
Thanks
Quick question, does the IN clause have more than 65 elements in it?
😎
October 4, 2015 at 1:17 pm
Yes, it is a huge IN clause with hundreds of elements.
October 4, 2015 at 1:32 pm
erb2000 (10/4/2015)
Yes, it is a huge IN clause with hundreds of elements.
Quick suggestion, have a look at this article When is a Seek not a Seek? by Paul White, sounds like this is your problem.
😎
As a fix you could try to change the IN clause to a table.
October 4, 2015 at 1:52 pm
Eirikur Eiriksson (10/4/2015)
erb2000 (10/4/2015)
Yes, it is a huge IN clause with hundreds of elements.Quick suggestion, have a look at this article When is a Seek not a Seek? by Paul White, sounds like this is your problem.
😎
As a fix you could try to change the IN clause to a table.
Yes, we will be fixing the product, but it is going to take some time.
The compatibility level is 100. We don't understand why upgrading from 2008 to 2014 caused this problem. We're looking for a quick fix, short of going back to 2008.
October 4, 2015 at 5:14 pm
If you're in compat mode 100, then it's not the cardinality estimator. That's usually what causes performance regressions in 2014. The traceflag Phil suggested won't help if it's not the cardinality estimator.
Can you pull the execution plan? Also, what wait types does the query incur?
Also, going back to 2008 won't be an easy process. You'll need a pre-upgrade backup and some way to migrate all data changes made since the upgrade.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 4, 2015 at 7:21 pm
GilaMonster (10/4/2015)
If you're in compat mode 100, then it's not the cardinality estimator. That's usually what causes performance regressions in 2014. The traceflag Phil suggested won't help if it's not the cardinality estimator.Can you pull the execution plan? Also, what wait types does the query incur?
Also, going back to 2008 won't be an easy process. You'll need a pre-upgrade backup and some way to migrate all data changes made since the upgrade.
You're right, there's no going back. I'll try to get the plan and wait types tomorrow. Thanks for the help.
October 6, 2015 at 8:37 am
We had a similar problem at our company where the entire SQL block was generated with a massive IN-list before being executed. Execution of the code would take forever and the execution time would steadily increase over the year as more values for the IN-list where added to the database. So in our case it was not the result of a SQL upgrade but I think the same solution might apply.
We used the same solution as Eirikur Eiriksson suggested. In our situation it was quite easy to change the code to put the IN-list into a table variable and then add an INNER JOIN to that table variable in the code instead of using the original IN-list.
I don't know how easily you can change the code, but if you are able to make changes you could try to insert the IN-list into a temp table or table variable and then use that.
So instead of using this:
SELECT ColumnA, ColumnB, ColumnC... FROM TableA WHERE ColumnA IN (Val1, Val2, Val3,…)
you could try something like this:
DECLARE @TTable (Tid int)
INSERT INTO @TTable (Tid)
VALUES (Val1), (Val2), (Val3),…
SELECT ColumnA, ColumnB, ColumnC... FROM TableA INNER JOIN @TTable ON TableA.ColumnA = @Ttable.Tid
The example is simplified code and, as always, should be tested before using it on a live-database. And it all depends on how easily you can change the code. We were able to change it fast without too much trouble, test it and implement it quickly. But you might not be that lucky.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply