Hi,

First off - again, thanks so much for this product, you've changed everything for our company - and we're extremely grateful.

I've noticed a massive difference in response times between an example query like this:

AND NOT ( [LPA_L1].[IsLocked] = @IsLocked5) @IsLocked5 bit etc..

----------------(28s)

and the exact same query WITHOUT parameters being used. Just using the parameters injected into the sql:

AND NOT ( [LPA_L1].[IsLocked] = 1)

---------------- (10ms!)

Is this to do with MS SQL plans - Is the parameter in some way restricting the query optimiser from doing its job? and if so can LLBLGEN linq be made to write the queries as simple SQL statements...

Thanks, Frank.

Ok, sorry about this - I realised it's part of the massive 'parameter sniffing bug' I couldn't find any mention of a fix, only that v3 may include something? Is that the case?

Thanks, Frank.

They only thing we are looking at for 'fixing' this (which isn't really fixing it, it's a workaround which might work in some cases, and also to be clear: it's not our bug but SQLServer's bug) is the workaround where the parameters are declared up front, but it's not that easy to build in and also: the command to switch the particular query to this mode is problematic.

Another workaround which we haven't implemented is to embed numeric constants etc. as constants in the query, instead of as a parameter. It's also hard to do in our framework as our framework is designed to have any constant be passed as a parameter, as that actually improves query execution in general and also prevents any injection attacks.

For v3.0, we don't think we can implement a big change in this area. Future v3.x releases will have more changes to the runtime and therefore we'll also try to include these workarounds.

What you can do yourself: re-order predicates in your query if it doesn't change the nature of your query to see if that helps (might sound weird, but it might avoid the sniffing issue)

Hi Frans,

As always thanks for your quick reply. Yep, I'm aware this has absolutely nothing to do with LLBLGen

I've disected it down to its parts, even with just ONE parameter it's broken. It's a huge query, but essentially:

select .. where x=@1 takes 30s select .. where x= 1 instant

I can rename the parameter -does nothing, option recompile does nothing. It simply doesn't work when I use a param - The execution plan is different.

Is there any stuff I could investigate (im using linq / self serv) that may help - perhaps index table hints / options etc? I just need a work around, anything. I'm trying 're-ordering' now.

Again, cheers for for your time, f

Hi Frans,

Re-ordering the constraints did it - completely different results, everything working perfectly.

I found the contraint that caused the problem, and moved it up 'front' - earlier on in the chain of

query = query.where(x=> ..)

commands..

Thanks, Frank.

The reordering works because the slowness is caused by a bad decision based on what the RDBMS thinks should be a fast execution plan, based on the values of the parameters it receives. So re-ordering the predicates (e.g. if you have all and-predicates, this can be done without problems) will make the RDBMS read different parameters first, and therefore the influence on which plan to choose might be smaller for the parameters examined after that

On MS-SQL Server 2008 only I have use OPTION (OPTIMIZE FOR UNKNOWN) to solve the parameter sniffing problem. It works.

HTH

Neil