user2263417 user2263417 - 6 months ago 14
SQL Question

Performance issue with SQL server due to sql query

Need some help to solve this error:


The query processor ran out of internal resources and could not
produce a query plan. This is a rare event and only expected for
extremely complex queries or queries that reference a very large
number of tables or partitions. Please simplify the query. If you
believe you have received this message in error, contact Customer
Support Services for more information.


SQL query: (I am just putting one of the query. I got 9 such queries running)

var query1 = from article in _db.Articles
from scl in article.Scls
where article.publishDate < DateTime.Now
&& article.removalDate > DateTime.Now
&& article.finished == true
&& article.flagged== true
&& listOfScl.Contains(scl.id)
select article;

var query2 = from article in _db.Articles
from com in article.Coms
where article.publishDate < DateTime.Now
&& article.removalDate > DateTime.Now
&& article.finished == true
&& article.flagged== true
&& listOfCom.Contains(com.id)
select article;

query = (query1.Union(query2)).Distinct();


Don't know what is wrong with query its working fine but giving some performance issue on SQL Server.

I need some help to avoid such error and re-write this SQL query without using
UNION
.

Thanks in advance.

Answer

Also note that by default LINQ does UNION, which will eliminate duplicates, therefore there is no need for Distinct, which will likely improve performance.

"re-write this SQL query without using UNION.". How is this?

var query1 = from article in _db.Articles
             from scl in article.Scls
             where article.publishDate < DateTime.Now
                && article.removalDate > DateTime.Now
                && article.finished == true
                && article.flagged== true
                && ( 
                     listOfScl.Contains(scl.id)
                     || listOfCom.Contains(com.id) 
                   )
             select article;

You could try the performance of this, so that you don't need a distinct. It will involve a subquery though, so it's hard to say what will perform better without testing:

var query1 = _db.Articles.Where(article => article.publishDate < DateTime.Now
                && article.removalDate > DateTime.Now
                && article.finished == true
                && article.flagged== true
                && ( article.Scls.Any(s=> listOfScl.Contains(s.id))
                     || article.Coms.Any(c=> listOfCom.Contains(c.id))
                   )
                );
Comments