CLR UDTs bigger than 8000 bytes

Hi there,
Say I wanted to create a "compound" type, purely to pass as a parameter
to a stored procedure, but one of the components was NTEXT or
NVARCHAR(MAX). Say it was something like:
- Author NVARCHAR(256)
- IP address VARCHAR(16)
- Comment NVARCHAR(MAX)
How would I go about doing this? My code works fine if the UDT package
is less than 8000 bytes, but fails if it's greater than that (which is
understandable since I had to put MaxByteSize = 8000 into the UDT
definition). If the package is too big, I get an
IndexOutOfRangeException in
System.Data.SqlClient.TdsParser.TdsExecuteRPC().
Is the 8000-byte limit a "hard" limit that I can't work around? As I
mentioned, this is purely to pass as a parameter to a stored procedure,
it'll never be used as a column in a table.
Or am I misunderstanding something and I don't even need to make this a
real UDT since it won't ever be stored as a column? Is there a way for
me to pass a regular .NET object to a CLR stored procedure?
(This example is very simplistic, I know - I could just pass three
individual parameters instead of one compound one in this case. The
actual situation is more complicated though.)
Many thanks,
GeoffGeoff (opinionatedg@.gmail.com) writes:
> Say I wanted to create a "compound" type, purely to pass as a parameter
> to a stored procedure, but one of the components was NTEXT or
> NVARCHAR(MAX). Say it was something like:
> - Author NVARCHAR(256)
> - IP address VARCHAR(16)
> - Comment NVARCHAR(MAX)
> How would I go about doing this? My code works fine if the UDT package
> is less than 8000 bytes, but fails if it's greater than that (which is
> understandable since I had to put MaxByteSize = 8000 into the UDT
> definition). If the package is too big, I get an
> IndexOutOfRangeException in
> System.Data.SqlClient.TdsParser.TdsExecuteRPC().
> Is the 8000-byte limit a "hard" limit that I can't work around?
That's right.

> Or am I misunderstanding something and I don't even need to make this a
> real UDT since it won't ever be stored as a column? Is there a way for
> me to pass a regular .NET object to a CLR stored procedure?
Check out the current thread "DataRow in a CLR Stored Procedure". There
are some suggestions on binary serialization.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi,
thanks for that - at least I know I'm not missing something simple at
the UDT/parameter level.
I've checked out the thread "DataRow in a CLR Stored Procedure" as you
suggested - interesting stuff. It would just mean serializing the
values manually before the call, rather than the serialization
happening as part of the call... The objects themselves wouldn't then
need to be UDTs, so there'd be no limit. Cool.
Many thanks,
Geoff

Client Statistics / Network Statistics

I am trying to track down a slowly preforming query and am looking at the client statistics tab; specifically the Network Statistics and Bytes received from server.

I have two servers one running SQL Server 2000 and the other running SQL Server 2005, 64-bit.

The data on the servers is similar, some schema differences, some record differences -- but nearly 32 million records in one table.

The same query is taking longer on the new SQL Server 2005 machine. The main difference that I see on the client statistics tab is the bytes received from server is almost always triple on the new server.

Were there any changes in the information SQL Server 2000 and 2005 reports back? Could the 64-bit be the cause for the increase (might make sense if it were double, but not triple)?

Any other ways I can eliminate this metric from being the cause of the slow running queries?

Thanks, Richard

If I run a simple SELECT * FROM Orders against the Northwind database on SQL Server 2000 with Query Analyzer or on SQL Server 2005 (32-bit) with Management Studio I get 830 rows on each, and 157,065 bytes on 2000 vs 157,097 on 2005. (Get the Northwind database from here http://msdn2.microsoft.com/en-us/library/ms143221.aspx). Try it on your 64-bit server and see what you get. If you also get 157,097 then maybe you're using different data types in your database on the 64-bit server?|||

Hi Richard,

I don't know the answer to the question of client statistics but I would personally check the execution of both of your queries through Profiler. Look at READS and CPU for both versions and see if there is a difference internally to SQL Server before checking to see the results being sent back to the client.

The DURATION counter could be misleading in your situation because the time taken to transfer the data back to the client (and the client to close the resultset) will factor into duration (actually, the DURATION counter is usually misleading due to issues of locking, blocking, client conneciton remaining open, low bandwidth to server, Disk bottlenecks, etc).

In regards to 64-bit showing double the number of characters, everything I've read suggests that 64-bit is the same code base and that your client app should not notice any difference between connecting to a 32-bit SQL Server or a 64-bit SQL Server.

You didn't mention how many rows and columns were returned by each query but if your data and schema is not 100% identical on both servers then I would assume the issue is in the data being returned.

Jared

|||

I started with SQL Profiler and saw that the duration for my complex query was almost always slower on my new server. There are definite differences in the execution plans, but the number of rows returned is 23 and the fields requested are identical -- there are differences in the length of some of the varchars, but the actual data returned is identical.

Schema and rows shouldn't be a factor -- indexes and statistics are the same, execution plans are different and the "Bytes received from server" are different. Time statistics are slower.

I'm in the process on loading Northwind on these two servers so that I can compare with the other respondent. However, a DBA in another department ran a similar test against SQL Server 2000, SQL Server 2005, and SQL Server 2005, 64-bit and found that the Bytes received was greater (double to triple) on SQL Server 2005; reguardless of the 64-bit.

I'll run some more tests and post my results -- I just hope that I'm not chasing some difference in the "Client Statistics" tab between 2000 and 2005.

Thanks for your interest, Richard