I'm seeing an issue that I hope someone here has dealt with before.

I'm doing a query on a couple of tables, each with several million rows of data.

LLBL is throwing a ORM exception due to a timeout.

My DBA has found that if we execute the QueryExecuted, SQL Server (05) is very slow to return a response.

If we modify the query to use hard-coded values instead of parameters, it returns nearly instantaneously.

Has anyone else dealt with this issue?

Thanks!

Parameteres avoid sql injection. Could you show the code that generates the query? How much performance do you gain using the query without parameters?

daelmo wrote:

Could you show the code that generates the query?

LinqMetaData metaData = new LinqMetaData(adapter);
var q = (from cm in metaData.ContractMaster
    select cm)
    .WithPath(p => p.Prefetch(cm => cm.ContractDetail))
    .WithPath(p => p.Prefetch(cm => cm.CustMaster));

if (!string.IsNullOrEmpty(dealerId))
{
    q = q.Where(all => all.DealerId == dealerId);
}

if (!string.IsNullOrEmpty(lastName))
{
    q = q.Where(all => all.CustMaster.LastName == lastName);
}

daelmo wrote:

How much performance do you gain using the query without parameters?

I'll have to see. The difference is significant.

One thing that I just noticed is that if I copy the query I see (via Profiler) LLBL execute into a SQL Management instance, the query executes fine.

I'm not sure if this is what was happening when my DBA investigated, but, again, I'll check and get back to you.

Offhand, do you know any reason why the same query executed interactively and via LLBL would have two wildly different completion times? (I executed the one in Management Studio first, so if there's any caching benefit, it should have been seen by the LLBL-based query.)

Thanks!

EDIT: I verified the queries that I'm seeing in Profiler are identical.

I think it's caused by a thing called 'parameter sniffing'. Please google on it to see several examples of sqlserver 2005 users who ran into this problem. This problem occurs when sqlserver, based on a previous execution plan, decides to re-use it, but the new parameter values would actually mean the previous plan is slow.

Sometimes re-arranging predicates helps. though I'm not sure your particular query is a result of this. If you can reproduce the slowness with a parameterized query in SSMS, please try to re-arrange parameters to see if it is faster.

Don't re-run the same query in the same window, it might be the results you get back are cached ones and it isn't a representative run.