December 12, 2016 at 9:56 am
Hi,
The following query runs in <1s when running straight:
SELECT
zpsinvoice1_.ClientId AS col_0_0_,
zpsinvoice1_.InvoiceGroupId AS col_1_0_,
zpsinvoice1_.Id AS col_2_0_,
zpsinvoice1_.Amount AS col_3_0_,
zpsexpense4_.ExpenseTypeCodeId AS col_4_0_,
zpsinvoice3_.ExternalId AS col_5_0_,
zpsinvoice0_.DocumentId AS col_6_0_
FROM Z_PS_InvoiceDocument zpsinvoice0_ INNER JOIN Z_PS_Invoice zpsinvoice1_ ON zpsinvoice0_.InvoiceId = zpsinvoice1_.Id
INNER JOIN VT_ClientCore vtclientco2_ ON zpsinvoice1_.ClientId = vtclientco2_.[EntityCode#ClientCore]
LEFT OUTER JOIN Z_PS_InvoiceGroup zpsinvoice3_ ON zpsinvoice1_.InvoiceGroupId = zpsinvoice3_.Id
LEFT OUTER JOIN Z_PS_ExpenseTypeMap zpsexpense4_ ON zpsinvoice1_.ExpenseTypeMapId = zpsexpense4_.Id
WHERE zpsinvoice1_.ClientId = 'GUID_d2b64f6d-2d3c-412b-81d5-a344c46198ad'
when running the same using sp_prepexec:
declare @p1 int
exec sp_prepexec @p1 output,N'@P0 nvarchar(4000)',N'select zpsinvoice1_.ClientId as col_0_0_, zpsinvoice1_.InvoiceGroupId as col_1_0_, zpsinvoice1_.Id as col_2_0_, zpsinvoice1_.Amount as col_3_0_, zpsexpense4_.ExpenseTypeCodeId as col_4_0_, zpsinvoice3_.ExternalId as col_5_0_, zpsinvoice0_.DocumentId as col_6_0_ from Z_PS_InvoiceDocument zpsinvoice0_ inner join Z_PS_Invoice zpsinvoice1_ on zpsinvoice0_.InvoiceId=zpsinvoice1_.Id inner join VT_ClientCore vtclientco2_ on zpsinvoice1_.ClientId=vtclientco2_.[EntityCode#ClientCore] left outer join Z_PS_InvoiceGroup zpsinvoice3_ on zpsinvoice1_.InvoiceGroupId=zpsinvoice3_.Id left outer join Z_PS_ExpenseTypeMap zpsexpense4_ on zpsinvoice1_.ExpenseTypeMapId=zpsexpense4_.Id where zpsinvoice1_.ClientId=@P0',N'GUID_d2b64f6d-2d3c-412b-81d5-a344c46198ad'
it takes 18s 🙁 and the query plan is slightly (not a lot) different
problem is I'm using hibernate (java) so there is afaik no way to not use jdbc prepared statements, without going native query.
This is one of a few examples that run really badly; for the most part queries run quick.
Any ideas how i can troubleshoot why the prep part takes so long?
December 12, 2016 at 12:14 pm
Data value skew is the most likely cause. Or at least statistics that reflect that. Your direct query is giving the optimizer the exact value it needs to optimize for the "right" plan. Parameterization doesn't do that.
Can you post up the plans here? What you thing are minor differences could be meaningful to a more experienced eye. One possible issue is something (hash join maybe?) getting spilled to tempdb.
Can you get nHibernate to put OPTION (RECOMPILE) at the end of the query?
OH WAIT!!!! Classic BAD THING about ORMs: your variable is declared as a Nvarchar(4000). Simply stupid of the ORMs to do that. Assuming clientid field is a varchar something that one is the magic-bullet fix. Tell your ORM to use EXACTLY the same data type as the column and the problem will go away.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply