WhereIf causes slow query #3774

fguo created 6 years ago

"WhereIf" is handy in scenario of multiple input items, and it works fine with small data, as my test. However, it seems something wrong when I recently worked with a database table with about 100 columns and 200k records. The table is named as "Worksheets". I am trying to make a "GetWorksheets" service. The code likes:

    public ListResultDto&lt;WorksheetListDto&gt; GetWorksheets(GetWorksheetsInput input)
    {
        var worksheets = _worksheetRepository
            .GetAll()
            //.Where(w=> (input.WorksheetIds != null && input.WorksheetIds.Any() && input.WorksheetIds.Contains(w.Id)))
            .WhereIf((input.WorksheetIds != null && input.WorksheetIds.Any()), w => input.WorksheetIds.Contains(w.Id))
            .OrderBy(w => w.Id)
            .ToList();
        return new ListResultDto&lt;WorksheetListDto&gt;(ObjectMapper.Map&lt;List&lt;WorksheetListDto&gt;>(worksheets));
    }

It takes about 3 minutes to return a result for a single Id in input, while I test it on Swagger.

I tried to replace the "WhereIf" with a regular "Where" (the comment out line above). It only takes 2 seconds to return a result.

Why the "WhereIf" is so slow? Did I miss something to use it?

Thanks,

11 Answer(s)

alper created 6 years ago

Support Team

hi the 2 where clauses are not identical. if you want to make it same condition then you have to rewrite the where clause. Please compare these 2 codes.. I don't think there will be performance issue with whereif. it's nothing to do with final query structure.

CODE#1

public ListResultDto<WorksheetListDto> GetWorksheets(GetWorksheetsInput input)
        {
            var query = _worksheetRepository.GetAll();
            if (input.WorksheetIds != null && input.WorksheetIds.Any())
            {
                query = query.Where(w => (input.WorksheetIds.Contains(w.Id)))
            }

            var worksheets = query.OrderBy(w => w.Id).ToList();
            return new ListResultDto<WorksheetListDto>(ObjectMapper.Map<List<WorksheetListDto>>(worksheets));
        }

CODE#2

public ListResultDto<WorksheetListDto> GetWorksheets(GetWorksheetsInput input)
        {
            var worksheets = _worksheetRepository
                .GetAll()
                .WhereIf((input.WorksheetIds != null && input.WorksheetIds.Any()), w => input.WorksheetIds.Contains(w.Id))
                .OrderBy(w => w.Id)
                .ToList();
            return new ListResultDto<WorksheetListDto>(ObjectMapper.Map<List<WorksheetListDto>>(worksheets));
        }

0

fguo created 6 years ago

Do you mean your 2 codes (CODE #1 AND CODE #2) identical? I copied exactly the 2 codes as a test. The CODE #1 works same as my "where" code, returns a correct record in 2 seconds. But, the CODE #2 takes 3 minutes and return an "IIS 10.0 Detailed Error - 502.3 - Bad Gateway" error. Below is the full message:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <title>IIS 10.0 Detailed Error - 502.3 - Bad Gateway</title> <style type="text/css">

</style>

</head> <body> <div id="content"> <div class="content-container"> <h3>HTTP Error 502.3 - Bad Gateway</h3> <h4>The specified CGI application encountered an error and the server terminated the process.</h4> </div> <div class="content-container"> <fieldset><h4>Most likely causes:</h4> <ul> <li>The CGI application did not return a valid set of HTTP errors.</li> <li>A server acting as a proxy or gateway was unable to process the request due to an error in a parent gateway.</li> </ul> </fieldset> </div> <div class="content-container"> <fieldset><h4>Things you can try:</h4> <ul> <li>Use DebugDiag to troubleshoot the CGI application.</li> <li>Determine if a proxy or gateway is responsible for this error.</li> </ul> </fieldset> </div>

<div class="content-container"> <fieldset><h4>Detailed Error Information:</h4> <div id="details-left"> <table border="0" cellpadding="0" cellspacing="0"> <tr class="alt"><th>Module</th><td> AspNetCoreModule</td></tr> <tr><th>Notification</th><td> ExecuteRequestHandler</td></tr> <tr class="alt"><th>Handler</th><td> aspNetCore</td></tr> <tr><th>Error Code</th><td> 0x80072ee2</td></tr>

</table> </div> <div id="details-right"> <table border="0" cellpadding="0" cellspacing="0"> <tr class="alt"><th>Requested URL</th><td> http://localhost:22742/api/services/app/Worksheet/GetWorksheets?Ids=1657668</td></tr> <tr><th>Physical Path</th><td> C:\dev\SNet\aspnet-core\src\SNet.Web.Host\api\services\app\Worksheet\GetWorksheets</td></tr> <tr class="alt"><th>Logon Method</th><td> Anonymous</td></tr> <tr><th>Logon User</th><td> Anonymous</td></tr> <tr class="alt"><th>Request Tracing Directory</th><td> C:\Users\fguo\Documents\IISExpress\TraceLogFiles\SNET.WEB.HOST</td></tr> </table> <div class="clear"></div> </div> </fieldset> </div>

<div class="content-container"> <fieldset><h4>More Information:</h4> This error occurs when a CGI application does not return a valid set of HTTP headers, or when a proxy or gateway was unable to send the request to a parent gateway. You may need to get a network trace or contact the proxy server administrator, if it is not a CGI problem. <p><a href="http://go.microsoft.com/fwlink/?LinkID=62293&IIS70Error=502,3,0x80072ee2,15063">View more information »</a></p>

</fieldset> </div> </div> </body> </html>

Any idea?
0

ismcagdas created 6 years ago
Support Team

Hi @fguo,

Do you have any other error message in Logs.txt ?

Thanks.
0

fguo created 6 years ago

In Log.txt, there is no ERROR, but INFO or DEBUG. Do you need it?
0
alper created 6 years ago
Support Team
hi,

I just tried to say the CODE#1 and CODE#2 is identical and shouldn't make difference on performance. So i am sharing the WhereIf code. As you can see below, it's very simple! If you think WhereIf doesn't work for your case you can use if-else condition on where clauses.

public static IQueryable<T> WhereIf<T>(this IQueryable<T> query, bool condition, Expression<Func<T, bool>> predicate) { return condition ? query.Where(predicate) : query; }
0

fguo created 6 years ago

Ok. They are identical from your code. Maybe it translates to different database queries? My table is large (100 columns with long column names). Maybe the SQL plan is over the SQL query maximum size?

How do I see the translated SQL plan? Is there a built-in method in abp to display/log the SQL plan before it executed?

Thanks,
0

fguo created 6 years ago

I found the discrepancy. Your above code states the WhereIf() is type of IQueryable<T>, but in my package (Core + Angular 4.4.0), it is IEnumerable<T>. It is in Assembly Abp, version 2.3.0.0.

I thought the new AspNetZero version was updated. Shall I manually update the whole solution by NuGet every time to use a new version? :?: If so, it is not a big deal for me.

As I experienced before, the NuGet updating sometimes break your package/sample code. I just checked the version 4.4.0 in my VS2017, and there are 48 updates are waiting in the NuGet list, including Abp, Microsoft, xunit, and some other 3rd parties. Can you advise me which one I need to update?

Thanks,
0

ismcagdas created 6 years ago
Support Team

Hi @fguo,

WhereIf exists both for IQueryable and IEnumerable. You should use the one for IQueryable, it might be related to your using statement in related class.
0

fguo created 6 years ago

Thank you ismcagdas! This answer is very informative. There are two WhereIf extensions in Abp assembly:

Abp.Collections.Extensions.EnumerableExtensions.WhereIf<T>(), which is type of IEnumerable<T>; and Abp.Linq.Extensions.QueryableExtensions.WhereIf<T>(), which is type of IQueryable<T>.

I should use IQueryable<T> WhereIf(), but carelessly selected "using Abp.Collections.Extensions;" because it is on top of intelligence popup in VS.

Hope this experience is useful for other subscribers.

BTW, as I mentioned on my last post, there are 48 updates are waiting in the NuGet list in my VS, most of them are Abp 3.0.0. Shall I update them from current Abp 2.3.0 in my AspNetZero v 4.4.0?

Thanks again!
0

strix20 created 6 years ago

Most of those updates are for .Net Core 2.0 I suspect, and should not be updated.

The better approach, while painful, would be to download the new 4.5 version that includes the .net core 2.0 upgrade, and apply your customizations to that.

We are currently going through that process our self.
0

alper created 6 years ago
Support Team

thanks for your feedback ;)

Have an answer to this question? Log in and write your answer.