2

One of the SQL queries I am working on is slow in performance. I have been trying to fix it for the last two days, unsuccessfully. The query is generated from an application (Linq to sql -EFF), and based on the user search criteria, it passes parameters to the query.

I looked into the execution plan and I see sort operations. One of the sort operation is SORT(DISTINCT). I checked the query and I found some order by in the query. Removing order by didn't help, and I dont have a DISTINCT in the query. On checking the properties of the SORT(DISTINCT) it is not showing the field on which it is doing sort. I have attached the screenshot of that.

After doing some research I learned that OR in where conditions can generate SORT operations as the MERGE required some kind of sort, and I do have OR in where conditions which looks like:

where (column1 = @param1) OR (column1 IS NULL) OR (@param1 IS NULL)

Someone suggested me in an earlier post that, this are "Kitchen sink" queries and it will cause performance issues.

Is there anyone who had to deal with similar situation? Any solutions?

SORT(DISTINCT)

  • Aaron Bertrand has a lovely article for this: blogs.sentryone.com/aaronbertrand/… – Jacob H Jun 22 '18 at 15:50
  • I have already read that article and it is very much informative.But i couldn't find any info SORT(DISTINCT) anywhere and why is occurring. I don't even have a DISTINCT in my query.That is the reason i posted this in here. – user9516827 Jun 22 '18 at 15:58
  • Is the @param1 is null correct? What would be the reason for that in a query? – Aaron Jun 22 '18 at 15:59
  • @Aaron :What do you mean by null correct.When generating SQL query using Linq-SQL ,i assume that null checking is getting generated.Other reason i think is,the query has multiple search criteria and if one of the search criteria is null,it should be ignored and move on. – user9516827 Jun 22 '18 at 16:06
  • Since it’s not used to filter on any of the data in the query when param1 is null it is equivalent to having no where clause since the where clause is or based – Aaron Jun 22 '18 at 19:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.