Bug in Grid filtering?

[Bob]

I have found what I think is a bug in the Telerik MVC Grid control. I am using VS 2008 with MVC 2 and the Telerik 2010.1.416 build. I am running on Windows XP SP3 with all latest patches. I execute the following query to the database:

var predicate = PredicateBuilder.True<MvcQuerySystem.Models.Data.domain_models.adv_expenditure_model>();
predicate = predicate.And(o => o.spender.Equals("TUDOR CHRISTOPHER"));
predicate = predicate.And(o => o.electionYear.Equals("2008"));
IEnumerable<MvcQuerySystem.Models.Data.domain_models.adv_expenditure_model> advanced =
    (from c1 in c1s
    join c4 in c4s on new { c1.filer_id, c1.election_year } equals new { c4.filer_id, c4.election_year }
    join expn in expns on c4.repno equals expn.repno
    join report in reports on c4.repno equals report.repno
    where report.superseded == null && expn.date_paid != null
    orderby expn.amount descending, expn.name
    select new MvcQuerySystem.Models.Data.domain_models.adv_expenditure_model
    {
        spender = c1.name,
        vendor = expn.name,
        date = (DateTime)expn.date_paid,
        amount = (decimal)expn.amount,
        city = expn.city,
        state = expn.state,
        zip = expn.zip4,
        description = expn.expn_desc,
        repno = (int)c4.repno,
        filer_id = c1.filer_id,
        electionYear = c1.election_year,
    }).Where(predicate);

 

It works OK until I try to filter by state. When I do this, I get the following error:
 
An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll but was not handled in user code

Additional information: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

The state column in the database is not required, and in some cases is null (in case that matters).

In LINQPad, I am able to add .Where(o => o.state.Equals("WA")) after .Where(predicate) without getting any errors, and I get the correct information returned.

Adding .Where(o => object.Equals("WA", o.state)) also works correctly in LINQPad, as does adding an additional predicate:
  predicate = predicate.And(o => o.state.Equals("WA")) or predicate = predicate.And(o => o.state.Equals("WA"));

[Atanas Korchev]

Hi Bob,

There is a forum thread which describes a similar problem - timeout during filtering. There I describe what kind of expressions are generated by the grid when performing filtering:

item => (item.Column?? "").ToLower().Equals("value".ToLower())

Could you try running this query in linqpad (change "Column") to the appropriate value)? Does it timeout?

Regards,
Atanas Korchev
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.

[Bob]

Hi Atanas,

Thanks for your fast response. I tried your suggestion, and it did not time out:

var predicate = PredicateBuilder.True<MvcQuerySystem.Models.Data.domain_models.adv_expenditure_model>();
predicate = predicate.And(o => o.spender.Equals("TUDOR CHRISTOPHER"));
predicate = predicate.And(o => o.electionYear.Equals("2008"));
predicate = predicate.And(o => (o.state?? "").ToLower().Equals("WA".ToLower()));
IEnumerable<MvcQuerySystem.Models.Data.domain_models.adv_expenditure_model> advanced =
    (from c1 in c1s
    join c4 in c4s on new { c1.filer_id, c1.election_year } equals new { c4.filer_id, c4.election_year }
    join expn in expns on c4.repno equals expn.repno
    join report in reports on c4.repno equals report.repno
    where report.superseded == null && expn.date_paid != null
    orderby expn.amount descending, expn.name
    select new MvcQuerySystem.Models.Data.domain_models.adv_expenditure_model
    {
        spender = c1.name,
        vendor = expn.name,
        date = (DateTime)expn.date_paid,
        amount = (decimal)expn.amount,
        city = expn.city,
        state = expn.state,
        zip = expn.zip4,
        description = expn.expn_desc,
        repno = (int)c4.repno,
        filer_id = c1.filer_id,
        electionYear = c1.election_year,
    }).Where(predicate).Dump();

[Atanas Korchev]

Hello Bob,

Is the grid configured for custom binding? If not the end query might be too complex in order to complete without timeout. You can try debugging the source code to see what the query which hits the database actually is. To do so you can set a break point in the EnsureDataSourceIsProcessed method of the GridDataProcessor class. Perhaps this would shed more light on the problem.

In any case it would be best if you manage to send us a sample project which reproduces the timeout. We will debug it and see what went wrong. Since we allow attachments only in support tickets you can host the sample project in some public fire storage web site (e.g. mediafire.com or rapidshare.com).

Regards,
Atanas Korchev
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.

[Bob]

Thanks, Atanas. I will try to put together a test project and sample database that demonstrates the problem. I am not familiar with using medaifire or rapidshare, but I can put it on our website where you can get it. I'll send you the details when I get it ready!

Thanks again for your terrific product and great support!

Bob

[Bob]

Oh boy. We have a live one here... I produced an mdf version of our database with just enough information that I could reporduce the problem I am having. The problem did not occur! I then tried a different SQL server (our "main" SQL server, Zeus, is on the same network segment that I am on, while the server (Orion) that I had been using is replicated from Zeus, and is on a "public-facing" network segment.) The problem does not occur on Zeus, either! When I move back to Orion, the problem occurs every time!

I have no idea why this happens, or what I can do about it. I am hoping that you are going to have some ideas about using a replicated SQL server, and why there is a difference. In the meantime, I think I will just disable filtering on that one column, and leave the database as Orion.

Any ideas would be much appreciated!

Bob

[Atanas Korchev]

Hello Bob,

Unfortunately we cannot tell why this timeout is happening only on one of the servers. You can check the query execution plan - it could be a missing index issue or something. To me this is a timeout due to a very complex SQL query. Perhaps the failing server needs index rebuilding. Still we can only speculate what the actual problem is until we somehow reproduce it locally.

I hope this helps,
Atanas Korchev
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.

[Bob]

Hi Atanas,

I found the problem! Although I never do anything but read the database, it started working when I changed the SQL user to one with owner permissions! Does your grid control do something that requires more than read access?

At any rate, this issue is now closed!

Thanks for your help,

Bob

[Atanas Korchev]

Hi Bob,

The grid executes a LINQ query which as far as I know should never require owner permissions. But since that query is executed by the underlying LINQ provider one may never be 100% sure.

All the best,
Atanas Korchev
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.