Build Version: 5.5 (5.5.1) RTM Build Date: 17-Jan-2019 Project: Adapter project targetting .NET 4.5.2 Database: MS SQL 2019

I have a query that runs slowly when using the dynamic SQL LLBLGen Pro generates and executes with sp_executesql. That same query runs very quickly when executed as standard SQL. After a little digging, I found that the cause was related to parameter sniffing causing a sub-optimal execution plan.

You can disable parameter sniffing using the following hint.

OPTION (USE HINT ('DISABLE_PARAMETER_SNIFFING'))

If I add the above to the bottom of the generated query (after the final closing parentheses) and run it in SSMS the query is nearly instant.

I've attempted to use the following to add this hint to my query, but DISABLE_PARAMETER_SNIFFING doesn't show up in the generated SQL:

.WithHint("DISABLE_PARAMETER_SNIFFING", HintType.QueryHint)

If I use HintType.TargetHint I get an error about how DISABLE_PARAMETER_SNIFFING isn't a valid table hint (which makes sense).

Any thoughts on what I should be doing differently or if this is supported?

Thanks!

Hi there!

Aside from the actual problem which is why you DB is not picking the right index because parameter sniffing, we should focus on the hint you want to use. What your code looks like?

Have you tried something like this? (I tested it and seems ok):

using (var adapter = new DataAccessAdapter())
{
    var metaData = new LinqMetaData(adapter);
    var q = (from a in metaData.Customer
            where a.City.Contains("Stadt")
            select a)
            .WithHint("USE HINT ('DISABLE_PARAMETER_SNIFFING')", HintType.QueryHint);

    var results = q.ToList();
}     

Aside from the actual problem which is why your DB is not picking the right index because parameter sniffing

Yeah, that's fair, unfortunately, this is a query running on the database of another application, and we don't have permission to add our own indexes otherwise that would be the right option.

Here's an example unit test. We're pulling data from a pricing table we have and we're looking for duplicate records using a nested query. A duplicate record is a price where another price exists in the same group (instrument), has the same effective dates, and has a different primary key value (CurvePointId). The filter on the publisher isn't really important to the logic of the query, but it was just the query we were trying to test in this case.

public IQueryable<CurvePointEntity> GetQueryable()
{
    var adapter = new DataAccessAdapter();
    var metaData = new LinqMetaData(adapter); 
    var query = (IQueryable<CurvePointEntity>) metaData.GetQueryableForEntity<CurvePointEntity>();
    return query;
}


[TestMethod]
public void SlowQueryTest()
{
    var query = GetQueryable();

    // resolve an IQueriable out of our DI container
    query = query.Where(x => x.PriceInstrument.PricePublisherId == 86);

    var query2 = query.Where(main => query.Any(inner =>
        inner.PriceInstrumentId == main.PriceInstrumentId
        && inner.EffectiveFromDateTime == main.EffectiveFromDateTime
        && inner.EffectiveToDateTime == main.EffectiveToDateTime
        && inner.CurvePointId != main.CurvePointId
    )).WithHint("USE HINT ('DISABLE_PARAMETER_SNIFFING')", HintType.QueryHint);

    var count = query2.Count();

    Console.Out.WriteLine($"{count}");
}

And what we get is this query which doesn't seem to have the hint:

exec sp_executesql N'
SELECT TOP (@p2)
    COUNT(*) AS [LPAV_]
FROM ([dbo].[PriceInstrument] [LPA_L1]
INNER JOIN [dbo].[CurvePoint] [LPA_L2]
    ON [LPA_L1].[PriceInstrumentID] = [LPA_L2].[PriceInstrumentID])
WHERE (((((([LPA_L1].[PricePublisherID] = @p3))
AND EXISTS (SELECT
        [LPA_L3].[CredentialId]
    FROM (SELECT
            [LPA_L5].[CredentialId]
           ,[LPA_L5].[CurvePointId] AS [CurvePointId]
           ,[LPA_L5].[CurvePointTypeCvId]
           ,[LPA_L5].[EffectiveFromDateTime]
           ,[LPA_L5].[EffectiveToDateTime]
           ,[LPA_L5].[EstimateActual]
           ,[LPA_L5].[IsActive]
           ,[LPA_L5].[PriceInstrumentId] AS [PriceInstrumentId]
           ,[LPA_L5].[SourceId] AS [SourceId]
           ,[LPA_L5].[TradePeriodFromDateTime]
           ,[LPA_L5].[TradePeriodId]
           ,[LPA_L5].[TradePeriodToDateTime]
           ,[LPA_L5].[UnitofMeasureID]
           ,[LPA_L5].[UpdatedDateTime]
        FROM ([dbo].[PriceInstrument] [LPA_L4]
        INNER JOIN [dbo].[CurvePoint] [LPA_L5]
            ON [LPA_L4].[PriceInstrumentId] = [LPA_L5].[PriceInstrumentId])
        WHERE (([LPA_L4].[PricePublisherID] = @p4))) [LPA_L3]
    WHERE ((((([LPA_L3].[PriceInstrumentID] = [LPA_L2].[PriceInstrumentID])
    AND ([LPA_L3].[EffectiveFromDateTime] = [LPA_L2].[EffectiveFromDateTime]))
    AND ([LPA_L3].[EffectiveToDateTime] = [LPA_L2].[EffectiveToDateTime]))
    AND ([LPA_L3].[CurvePointID] <> [LPA_L2].[CurvePointID]))))
))))
',N'@p2 bigint,@p3 int,@p4 int',@p2=1,@p3=86,@p4=86

The query you execute results in a ScalarQueryExpression internally and is then transformed into a normal query but that route loses the query hint specification you made. It's currently a limitation of the linq provider, as that specific case uses a scalarqueryexpression and therefore doesn't work. The parameter sniffing problem is a shitty problem to have and the reluctance of Microsoft to properly fix this is depressing. We're seeing this problem appear on our forums now for over 15 years (if not more) and all we can do is offer workarounds which sometimes work.

I must say it's odd you can't add indexes to a database of another application, considering they're outside the model. You're not allowed by the vendor or by the client? If the latter, it shouldn't be too hard to convince them to apply some indexes that make live better?

You do have an alternative however, and you might not going to like it, but it's using a QuerySpec query. With QuerySpec you can specify the Count() aggregate in the projection in a normal query to which you can append the query hint so it doesn't get lost in translation. I tried to find a workaround with Linq but it's too rigid to let you specify the projection with the rest of the query body.

I hope this doesn't give your team members ammo to switch to EF (although good luck with EF and battling the parameter sniffing problem )

I must say it's odd you can't add indexes to a database of another application, considering they're outside the model. You're not allowed by the vendor or by the client?

The vendor in this case. It violates an SLA and they won't really budge (I guess they're concerned additional indexes will degrade performance in other places and they don't want to support that).

You do have an alternative, however, and you might not going to like it, but it's using a QuerySpec query. With QuerySpec you can specify the Count() aggregate in the projection in a normal query to which you can append the query hint so it doesn't get lost in translation. I tried to find a workaround with Linq but it's too rigid to let you specify the projection with the rest of the query body.

I'll look into that, this is a lower-level method that will be re-used a lot so its probably fine to get into that, we just want to keep things consistent with our developers in general, but if we have explicit exceptions that people don't have to really understand or interact with on the day to day its less of an issue.

I hope this doesn't give your team members ammo to switch to EF (although good luck with EF and battling the parameter sniffing problem wink)

I don't think it will, I think everyone recognizes this would be the same no matter what, and I very much doubt we'd get this level of support with EF anyway (one of the big reasons I like LLBLGen Pro).

One odd thing that I noticed, I reorganized the code to look like this:

[TestMethod]
public void QueryTest()
{
    var query = GetQueryable();
    var outerQuery = query.Where(x => x.PriceInstrument.PricePublisherId == 86);
    var innerQuery = query;

    var finalQuery = outerQuery.Where(outer => innerQuery.Any(inner =>
        inner.PriceInstrumentId == outer.PriceInstrumentId
        && inner.EffectiveFromDateTime == outer.EffectiveFromDateTime
        && inner.EffectiveToDateTime == outer.EffectiveToDateTime
        && inner.CurvePointId != outer.CurvePointId
    )).WithHint("USE HINT ('DISABLE_PARAMETER_SNIFFING')", HintType.QueryHint);

    var count = finalQuery.Count();
    Console.Out.WriteLine($"{count}");
}

And it generates a totally different, and much simpler, query:

EXEC sp_executesql N'
SELECT TOP (@p2)
COUNT(*) AS [LPAV_]
FROM ([dbo].[PriceInstrument] [LPA_L1]
INNER JOIN [dbo].[CurvePoint] [LPA_L2]
    ON [LPA_L1].[PriceInstrumentID] = [LPA_L2].[PriceInstrumentID])
WHERE (((((([LPA_L1].[PricePublisherID] = @p3))
AND EXISTS (SELECT
        [LPLA_3].[UpdatedDateTime]
    FROM [dbo].[CurvePoint] [LPLA_3]
    WHERE ((((([LPLA_3].[PriceInstrumentID] = [LPA_L2].[PriceInstrumentID])
    AND ([LPLA_3].[EffectiveFromDateTime] = [LPA_L2].[EffectiveFromDateTime]))
    AND ([LPLA_3].[EffectiveToDateTime] = [LPA_L2].[EffectiveToDateTime]))
    AND ([LPLA_3].[CurvePointID] <> [LPA_L2].[CurvePointID]))))
))))
',N'@p2 bigint,@p3 int',@p2 = 1,@p3 = 86

It seems like when I use a different queryable for the inner and outer queries, either through using a where clause or just by resolving another one, we get the above query. There's still no hint, but it runs instantly, so it meets our need.

Are there issues with doing nested queries like this? Or is this the recommended best practice?

I don't see any issues. You have specified 2 different queries in both cases and you got them right

var outerQuery = query.Where(x => x.PriceInstrument.PricePublisherId == 86);
   var innerQuery = query;

See, the trick is in the above 2 lines used in the second fast sceanrio... The Queryable is CurvePoint, which is what "query" is about in the above code. While Outerquery, has the queryable "curvepoint" joined with PriceInstrument.

So in the fast scenario, you have the outermost query with the join, and the innerQuery (which is the original Queryable) without that Join.

Now back to the slow scenario, you have specified the inner-query to be the same as the outermost query.... i.e. with the join. So that extra Join in the inner query made all the difference. As the slow query had to do the join twice, with extra filters in the second time (inner-query).

Glad you found a workaround! the Count() call which makes the linq provider choosing the scalarqueryexpression route and thus forgetting the hint specification is a bug we'll try to fix in the next release.

Awesome thanks to both of you for your help and quick responses!