Ayende @ Rahien

Refunds available at head office

SQL Server 2008 Table Value Parameters and NHibernate

I just took a look at how this feature is exposed. I really wants this feature. I hit the 2,100 parameters limit of SQL Server too many times in the past, always when I had to do some large IN queries. So, I was very happy to hear about that feature, but I didn't really take a look until now.

Unfortunately, the way they are implemented requires a hard reference to them. You have to create the type in the server,  and then you have to reference it by name. Annoying, to say the least, and it looks like there isn't any generic solution that I can accept. This is bad because I can think of quite a few uses for this feature, from applying batches to complex queries, it can be very useful, but it is looked in its own safe, statically typed, world. Urgh!

Comments

Andrew Tobin
08/26/2008 04:07 AM by
Andrew Tobin

Hey Ayende,

Yeah, SQL 2008 needs to know the structure of the table "object", so you have to create that structure in SQL before you can utilise it. The other thing I haven't seen, albeit in my incredibly brief investigations, is a way to view what structures there are active against a db.

I have an extension method I talk about here:

http://grrargh.com/blog/playing-with-extension-methods-todatatable/

What that does is creates a DataTable from any type of IEnumerable, which you can then pass in to a call in .NET as a parameter for stored procs, etc, which accept a Table Valued Parameter "object".

Of course you have to place the DataTable columns in the same order as your TVP "object" is defined.

I have some very simple code and sql in C# that I used as a demo at a user group if you'd like me to shoot it through that would show you?

Andrew Tobin
08/26/2008 04:10 AM by
Andrew Tobin

The other thing I forgot to mention in the last comment - these things look like they'll work beautifully with the MERGE in SQL 2008, because the Merge does an Insert or an Update depending on whether an object exists, you could pass in a complete table of modifications as one object/one call to your SQL stored proc.

And hopefully the Merge would then have less reads/scans through the index on updating the rows cleverly.

Ayende Rahien
08/26/2008 04:16 AM by
Ayende Rahien

Andrew,

This isn't helpful to me for the simple reason that I need a generic approach to do it in order to integrate it with NHibernate.

Yes, I can do one off stuff, but I don't want that.

josh
08/26/2008 05:05 AM by
josh

sorry.. I just got stuck on the part where it says you've "hit the 2,100 parameters limit of SQL Server too many times in the past".. What in the world were you doing? Maybe it's because I've never done whatever you were doing, but it sounds like you needed to rethink your approach.

..and I agree that table value feature would be really useful.

Frans Bouma
08/26/2008 08:36 AM by
Frans Bouma

The hard-coded link is similar to the narrow minded solution they came up with for UDT's written in .NET code.

It's sad indeed, because all they really needed to do is adding something simple like Oracle has for years: array based parameters.

Andrey Shchekin
08/26/2008 10:51 AM by
Andrey Shchekin

Well, I would prefer the only way to do something to be typed than untyped (if the both ways are not possible). You can use Func<T1, T2> approach (which is ugly, I know).

Ayende Rahien
08/26/2008 11:47 AM by
Ayende Rahien

Josh

As I said, IN queries for data that reside outside the DB.

Example, I want to get all your direct reports. But that information is in AD, not in the DB, so I have to pass a list of all the IDs.

Some of the people have over 2,000 direct reports (leave that aside), now you hit the limits.

Ayende Rahien
08/26/2008 11:56 AM by
Ayende Rahien

Andrey,

But I don't have the generics approach.

I literally have to create a new type if I want to send an array to the database.

This is close to useless from my perspective

Jeremy
08/26/2008 05:35 PM by
Jeremy

Ever considered passing XML into a stored proc? It's quite easy to shred it into a temp table and use and join however you like.

Now this is weird...

I searched for an example of how to shred XML and found

http://weblogs.asp.net/jgalloway/archive/2007/02/16/passing-lists-to-sql-server-2005-with-xml-parameters.aspx

which strangely enough points right back to your blog.....

Ayende Rahien
08/26/2008 05:39 PM by
Ayende Rahien

Jeremy,

Yes, I did

I also tried fn_Split and bulk data entry.

They all have problems.

In particular, XML and splitting strings has high CPU cost.

Anastasiosyal
08/27/2008 09:55 AM by
Anastasiosyal

'I hit the 2,100 parameters limit of SQL Server too many times in the past, always when I had to do some large IN queries'

With Sql 2008 the answer is obvious, but on previous versions all you need is to pass a delimited string such as:

Select @params = '1,5,7,20,24,55'

and then split it and inner join it with your main data on the filtered column

Select * from MyData inner join dbo.Split(@params) Params on MyData.FilterColumn = Params.Data


Here is a splitting function taken from:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648&whichpage=1

CREATE FUNCTION dbo.Split

(

@RowData nvarchar(2000),

@SplitOn nvarchar(5)

)

RETURNS @RtnValue table

(

Id int identity(1,1),

Data nvarchar(100)

)

AS

BEGIN

Declare @Cnt int

Set @Cnt = 1


While (Charindex(@SplitOn,@RowData)>0)

Begin

    Insert Into @RtnValue (data)

    Select 

        Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))


    Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))

    Set @Cnt = @Cnt + 1

End


Insert Into @RtnValue (data)

Select Data = ltrim(rtrim(@RowData))


Return

END


Again, Its useful to be able to pass in full tables in Sql Server 2008, but I see this being mostly used to pass a List of Primary key values, rather than the table and its accompanying data, this would be more efficient. If this is a common case a couple of Table type definitions should suffice (one for passing int primary keys, varchar primary keys, etc)

Of course if you want to insert/update lots of data at once, I would avoid this technique and instead bulk insert all data to a temp table and then do a MERGE into the main table (since this is supported by sql 2008)

Anastasiosyal
08/27/2008 09:57 AM by
Anastasiosyal

Just noticed in your reply to jeremy that you also tried fn_split...

Was it really underperforming in your scenario? What was the reasoning behind prefering the maximum number of parameters instead?

Ayende Rahien
08/27/2008 11:42 AM by
Ayende Rahien

Anastasiosyal,

We noticed significant CPU work when doing splits for > 1000 items.

Using XML is better if it is > 1000 items, but slower if it is less.

For > 3000 items, you tend to want to bulk insert the data to the DB and manage on that

Rui
08/28/2008 03:13 PM by
Rui

Ayende,

If you need to be generic (and I think you need to implement TVP in NHibernate ;-), you should query the database to obtain the schema of your TVP of your procedure.

But in the facts, if a procedure use a TVP, this means that the TVP type is already present in the database and the only thing you have to do in your mapping is to obtain it's structure.

In .Net side a DataTable is the object you need to pass as parameter to the procedure, then build the DataTable based on the structure needed by the TVP(s) of the concerned procedure.

To query the base to obtain the schema you should do a query like this one:

SELECT P.name,C.name,C.systemtypeid,C.is_nullable,ST.name,ST.length

FROM sys.parameters P

INNER JOIN sys.objects O ON P.object_id = O.object_id

INNER JOIN sys.table_types T on P.user_type_id = T.user_type_id

INNER JOIN sys.columns C ON T.type_table_object_id = C.object_id

INNER JOIN sys.systypes ST ON C.system_type_id = ST.xusertype

WHERE 1=1

AND O.name = 'my_procedure_name'

This will give you a table with the table(s) value(s) parameter name, columns in TVP, their type and size.

Hope, it helps ;-)

Ayende Rahien
08/28/2008 03:31 PM by
Ayende Rahien

Rui,

I don't want to have a SP that uses that, I want to be able to send arrays to the DB, that is all.

Comments have been closed on this topic.