SqlParameter Size - negative effects of setting to max size?

Question

I have a SqlCommand that I want to call Prepare() on whose CommandType = Text (it cannot be a stored procedure). In order to do this, I need to set the Size attribute on the parameters to be non-zero otherwise an exception is thrown. Are there any negative effects from setting the Size on all parameters to the maximum possible size even if the size of the actual values will never come close to that? Is there a better way to do this?

Answer 1

This is the way the framework does it if you use the SqlParameter constructor that just takes a parameter name and a value. There might be a slight inefficiency on the client side, but I have never noticed a difference in query performance.

Answer 2

Given that you are using CommandType = Text, you should be able to set the size programmatically to the actual size of the parameter you are sending.

You should only see poorer performance when the size of your data approaches the max size of the data types your are sending. If your parameters are always large relative to the size of the command text, from a network traffic perspective, you'll only see minimal gains in performance by switching to stored procs.

Answer 3

I think the only potential negative side effect of doing something like that would be the cost of memory allocation for the parameters.

Since you're calling 'Prepare()' I'm guessing you're planning to use the SqlCommand multiple times against the same SqlConnection which suggests a discrete section of code where it's likely to be used (if the connection closes for a prepared command, the command text will have to be re-transmitted to the server on the next usage).

If you know the nature of your parameters, it seems like you might have some idea about their potential sizes. If not, then I don't see what alternative you have, really, than to declare a significantly large size for each - large enough to hold most/any potential values.

Answer 4

Please post a sample of your code. You shouldn't need to set the Size attributes on your parameters in order to call .Prepare().

Incidentally, you probably don't really need to call .Prepare(), especially if you're calling .Execute() immediately after.