Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

sp_prepexec and nHibernate Expand / Collapse
Author
Message
Posted Wednesday, December 7, 2011 9:53 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:40 AM
Points: 2,923, Visits: 1,875
Comments posted to this topic are about the item sp_prepexec and nHibernate

LinkedIn Profile
Newbie on www.simple-talk.com
Post #1218360
Posted Thursday, December 8, 2011 1:07 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 11, 2014 3:46 AM
Points: 1,256, Visits: 773
Good article, and better than its rating indicates. (So I gave it 5 * to balance it.)

A few days ago I captured this in a trace, when a customer complained of a few time-outs. I suspect that one of their developer uses a self-made program to query the database for our application:

declare @p1 int
set @p1=1
declare @p2 bigint
set @p2=175483777308924
exec [sys].sp_getschemalock @p1 output,@p2 output,N'"dbo"."customer"'
select @p1, @p2

declare @p1 int
set @p1=41
exec sp_prepexec @p1 output,N'@P1 int',N'SELECT "Tbl1005"."customer_id" "Col1127","Tbl1005"."code" "Col1136" FROM "dbo"."customer" "Tbl1005" WHERE @P1="Tbl1005"."customer_id"',99534
select @p1
go

exec sp_execute 41,99551
go

>>> Many dozens of sp_execute....

exec sp_unprepare 41
go

exec [sys].sp_releaseschemalock 1
go

And within a few seconds the same code, and again, and again...

I would very much like to know what the effect is of this snippet:

exec [sys].sp_getschemalock @p1 output,@p2 output,N'"dbo"."customer"'

... but haven't found the time to investigate it.





Dutch Anti-RBAR League
Post #1218422
Posted Thursday, December 8, 2011 1:37 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:40 AM
Points: 2,923, Visits: 1,875
I've seen something like this before with linked servers.

In that particular instance the code below alleviated the problem

EXEC master.dbo.sp_serveroption @server=N'YourServer', @optname=N'lazy schema validation', @optvalue=N'true'

Thanks for the star rating. If people want to give an article a low rating that's fine, but it would help if they said why.


LinkedIn Profile
Newbie on www.simple-talk.com
Post #1218426
Posted Thursday, December 8, 2011 1:45 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, December 6, 2014 9:55 AM
Points: 81, Visits: 908
Excellent article, thank you for providing a rationale for the gut feeling acquired when working with ORM developers.
Post #1218431
Posted Thursday, December 8, 2011 4:16 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 6:57 AM
Points: 27, Visits: 377
Great article as always Dave. When you spend a lot more time with nHibernate there are plenty of interesting/challenging things to find. I forget the details now but when we made the changes about 2 years ago to use the 'prepare_sql' option we managed to kill our site so we had to undo that change quickly. I'll admit our developers use of this ORM tool seems to me as pretty bad as I have had a long standing battle to get them to either use it properly or bin it and go with something better. Don't get me wrong, we've got some extremely good developers and some are the best I've worked with. However, SQL remains a black box for most of them.

The queries you get when tracing are nigh on unreadable and needs a fair bit of formatting before you can actually work with it and I find that a lot of the queries I see are very long so this makes this more time consuming. When you look at the way it approaches aliases, it's not something easy on the eye. It often lumps statements together but there is no visible seperation/formatting in the code.

One of the main problems I see with nHibernate is the way it approaches parameters for IN statements. I fully admit it's most likely our developers way of using it, but essentially when you pass in a list of ID's they come through in the underlying query as 'SELECT blah from myTable WHERE Id in (@p0, @p1, @p2.....)'. This ends up with a different hash of the execution plan for each variation of the same query. If I pass in 100 parameters I get one variation. If I pass in 101 I get another identical plan, albeit with a different hash so technically it's a different plan. As such we spend most of our time compiling execution plans that we only use once. Imagine the above scenario whereby we get up to 2000 params and you can see the problem (2000 potential different variations on a query). Yes, we've actually maxed out on params passed to a statement. I reckon we use 80% of the plans once before they are aged out. I've been told that the more recent versions have addressed this but I haven't seen it yet. The width of an nVarChar seems to cause it confusion. If I have a param with 7 characters I get one variation with the query using a param of nVarChar(7). If I change the value being passed in to 8 characters it changes the param in the query to nVarChar(8). Combine the two of these comments above and it gets silly. I haven't yet found anyone to agree to setting this to (and here is my crazy idea) the width of the column.

When I run a side by side comparison with properly sized params and passing in a table variable or even a varbinary and parsing that into a table variable the performance difference is huge. Then with plan re-use, a proper version becomes so much more scalable and performant.

I challenged our developers to find a suitable solution and that I would buy lunch for whoever solved it in nHibernate. My money remains in my pocket.

I found an interesting read on http://www.codeproject.com/KB/database/NHibernate_Perf2.aspx and figured that I could use nHibernate's HBM file to set a really performant query in the config and map the output to the object that the developers wanted. For me this seemed to work. The developers I've spoken to have said no, because they don't want to alter the HBM config file. Argghhhh ! It's a shame because if I'm right this would solve it for us all and allow the query to be source controlled along with the code.

The end conclusion I've come to after just over 2 years is that ultimately it does save time when coding, but for a high concurrency site it gives us a glass ceiling that will hamper us at some point. I can counter a decent chunk of this, but we give up a lot of tuning options, diagnosis, performance and so on. I've had to resort on some occasion to figuring out patterns of index use over time intervals to try and reverse engineer things so that I can guess what query is likely to be used and then trying to help figure out where the query came from so that we can track it back to the code. Do-able, but not exactly fun. Finding and diagosing a problem nHibernate query turns it into finding a needle in a stack of needles or death by a thousand cuts.

Overall, it's fine for gaining development speed but developers seem to forget the trade off of them doing this work (they only want the initial development benefit, not the full cost) so we effectively give up any hope of making the queries perform well when you throw load at them. I DO think it can work, but I haven't seen anything impressive from the DBA side of things yet. It's school dinner coding. Everyone gets fed, but it's not exactly Claridges is it ?

Recently, we've been putting together something new so I took the approach of tuning the hell out of everything before seeing if they could get nHibernate to match it. The answer was quick and simple as it was 'no'. We went with using a call to a stored proc. It was surprisingly easy to call this in code.
Post #1218509
Posted Thursday, December 8, 2011 4:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 25, 2013 6:43 AM
Points: 6, Visits: 49
I have a couple of questions about this article:

1.) Are the plans cached by sp_prepexec stored in the same place as the plans cached when using Stored Procedures?

2.) Do you think that it is a bug with SQL Server that plans created using sp_prepexec are not re-used across connections?

3.) Does the "per connection" plan re-use behaviour change between versions of sql server 2000/2005/2008?

4.) If you are calling SP's from ADO.net are the plans re-used across connections in a web application?
Post #1218516
Posted Thursday, December 8, 2011 4:44 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:26 AM
Points: 14,205, Visits: 28,536
Excellent write up. 5 Stars. Useful information for anyone that has to deal with this stuff.

I'm not an nHibernate expert, nor do I play one, anywhere, but I'm pretty sure the prepare statements are necessary to avoid the issues you get with parameter sizing in nHibernate. Here's a link to solution and here's one to the problem.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of:
SQL Server Query Performance Tuning
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1218528
Posted Thursday, December 8, 2011 4:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 25, 2013 6:43 AM
Points: 6, Visits: 49
ryan.offord (12/8/2011)

One of the main problems I see with nHibernate is the way it approaches parameters for IN statements. I fully admit it's most likely our developers way of using it, but essentially when you pass in a list of ID's they come through in the underlying query as 'SELECT blah from myTable WHERE Id in (@p0, @p1, @p2.....)'. This ends up with a different hash of the execution plan for each variation of the same query. If I pass in 100 parameters I get one variation. If I pass in 101 I get another identical plan, albeit with a different hash so technically it's a different plan. As such we spend most of our time compiling execution plans that we only use once. Imagine the above scenario whereby we get up to 2000 params and you can see the problem (2000 potential different variations on a query). Yes, we've actually maxed out on params passed to a statement. I reckon we use 80% of the plans once before they are aged out. I've been told that the more recent versions have addressed this but I haven't seen it yet.


I use NHibernate on a daily basis and I sometimes need to use the IN clause and indeed the behaviour is as your say. Could you give me an example using SQL Server 2008 of what you would expect to see instead of the below?

select * from TableA where id in (@p1, @p2,@p3)



ryan.offord (12/8/2011)

The width of an nVarChar seems to cause it confusion. If I have a param with 7 characters I get one variation with the query using a param of nVarChar(7). If I change the value being passed in to 8 characters it changes the param in the query to nVarChar(8). Combine the two of these comments above and it gets silly. I haven't yet found anyone to agree to setting this to (and here is my crazy idea) the width of the column.


This has been fixed in the versions of NHibernate since 2.1 I believe by setting the parameter sizes by defult and not just when prepare_sql is set to true (you will also need to make sure you have defined your "length" attributes in your mapping files). Your developers may want to consider updating.
Post #1218533
Posted Thursday, December 8, 2011 5:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 6:57 AM
Points: 27, Visits: 377
rough example as it's nearly lunchtime

nHibernate version
==================

(this is roughly what it comes out as - made slightly easier for reading)

DECLARE @p0 INT --etc...for each param
SET @p0 = 1234 --and so on
SELECT * FROM Blah WHERE (this_.ID IN(@p0 , @p1 , @p2 , @p3 , @p4 , @p5 , @p6 , @p7 , @p8)

Better version
==============

DECLARE @BinaryIdListInput VarBinary(8000)
SET @BinaryIdListInput = '1234, 5678' --etc...

CREATE PROCEDURE GetMyList (@BinaryIdList varbinary(8000))
AS
DECLARE @IdList TABLE (Id INT PRIMARY KEY CLUSTERED)
INSERT INTO @IdList(Id) SELECT PIA.Value FROM ParseIntegerArray(@BinaryIdList, 4) AS PIA
SELECT * FROM Blah WHERE (this_.ID IN(SELECT Id From @IdList))

I'd then call a stored proc using EXEC GetMyList @BinaryIdList = @BinaryIdListInput

There is a more performant way of doing this with joining on the table variable, but the above gives you the closest comparable. The single version of the execution plan comes from passing in the varbinary in this case (I've set it above to simplify things a touch for reading)

I do use a function to parse the array and this is as follows :

CREATE FUNCTION [ParseIntegerArray]
(
@Binary IMAGE
,@ElementSize TINYINT
)
RETURNS @Array TABLE
(
[Index] SMALLINT IDENTITY(0,1)
,[Value] VARBINARY(8) NOT NULL
)
AS
BEGIN
IF ( @ElementSize IS NOT NULL AND @ElementSize IN ( 1, 2, 4, 8 ) )
BEGIN
DECLARE @Value VARBINARY(8)
,@Length SMALLINT
,@Index SMALLINT;

-- initialize variables
SET @Index = 1;
SET @Length = DATALENGTH( @Binary );

-- extract values
WHILE ( @Index <= @Length )
BEGIN
-- get value
SET @Value = SUBSTRING( @Binary, @Index, @ElementSize );

-- insert into table
INSERT INTO @Array VALUES( @Value );

-- increment index
SET @Index = @Index + @ElementSize;
END;
END;

RETURN;
END

GO
Post #1218541
Posted Thursday, December 8, 2011 5:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 25, 2013 6:43 AM
Points: 6, Visits: 49
@Grasshopper
I realise this is not strictly on topic but we use a very similar tactic but instead use varchar(8000) parameters using a dbo.Split function which utilises a number table. This removes the need for a loop in your function. Also why varbinary over a varchar(8000) or similar?
Post #1218549
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse