sp_executesql question
-
Hello Everyone,
I've read this very interesting article refered by Frank Kalis on a similar post: http://www.sommarskog.se/dynamic_sql.html.
But I still have a question:
Is there a difference (in term of performance) on issuing those two batches:
exec sp_executesql N'SELECT * FROM booking WHERE start_date = @sd', N'@sd nvarchar(4000)', @sd = N'2003-01-30'
go
exec sp_executesql N'SELECT * FROM booking WHERE start_date = @sd', N'@sd datetime', @sd = 'Jan 30 2003 12:00AM'
go
In the first one, the paramater is passed as a string and in the second, the parameter is passed as a datetime.
In a more general way, is there a difference on passing parameters to the sp_executesql as nvarchar or as the actual type that is passed?
Hope I was clear.
Regards,
Carl
-
Good question.
At first I would no, there is no real difference.
However have you compared both alternatives using SET SHOWPLAN_TEXT setting?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
It seem that the server uses the same exec plan in the tests I've done.
But, for sure, one of those two method may incur, at least, a little overhead. No?
Regards,
Carl
-
Would you like a yes or a no as the answer?
I know, I know. Every little bit counts, but I would say if there is an overhead at all, it's negligible.
...and BTW, you should get better performance when using this within a stored procedure and avoid dynamic sql at all.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Frank, honestly, I would preferred a "yes" followed by a "why".
We are using ADO.NET and those dynamic SQL are "generated" by ADO.NET and, for now we don't want to use stored proc to excute our queries.
Carl
-
All I can offer right now, is to contact Erland (as the author of my reference article) and ask him. I know he's working on an update of the article to include some .Net stuff.
Curious what he'll respond.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Good idea Frank,
I will post conclusions here if we end up with.
Thank's
Carl
-
Mail sent. I'll post his answer here.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Although he mailed you directly, Carl, I'm posting the answer from Erland here for the community to enjoy:
>But I still have a question:
>Is there a difference (in term of performance) on issuing those two batches:
>exec sp_executesql N'SELECT * FROM booking WHERE start_date = @sd',
> N'@sd nvarchar(4000)', @sd = N'2003-01-30'
>go
>exec sp_executesql N'SELECT * FROM booking WHERE start_date = @sd',
> N'@sd datetime', @sd = 'Jan 30 2003 12:00AM'
>go
>In the first one, the paramater is passed as a string and in the second,
>the parameter is passed as a datetime.
>...
>We are using ADO.NET and those dynamic SQL are "generated" by ADO.NET and,
If the calls appear in a stored procedure or in a plain SQL batch, I
find it difficult that there any significant difference. Somewhere
on the line the date literal must be converted to datetime. Sounds
like swings and roundabout to me.
In you case, you were making calls from ADO .Net, and since ADO .Net
calls sp_executesql through RPC, this means that with the datetime
parameter, the conversion from string to datetime to string is performed
on the client, which is probably good, because it means that you
scale out. However, the effect of this is likely to be negligible, unless
the system is completely bogged down.
From a functional point of view, there is a big advantage of the datetime
version. As I said, conversion happens on the client, with the regional
settings of the client. While you can control this on server level with
sp_defaultlanguage, having it in the client is a lot simpler.
/Erland
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hi also post my answer to him for the community:
I just want to clarify something: I don't think that it is ADO.NET that convert anything.
When the server "receive" the following batch:
>>exec sp_executesql N'SELECT * FROM booking WHERE start_date = @sd', N'@sd datetime', @sd = 'Jan 30 2003 12:00AM'
>>go
This is the source code that generate that:
>>DATETIME:
>>=========
>>SqlConnection MyCnn = new SqlConnection(<ConnectionString>
;>>SqlCommand MyCmd;
>>SqlDataReader MyCur;
>>SqlParameter MyPar;
>>String Qry = "SELECT * FROM booking WHERE start_date = @sd";
>>DateTime MyDate = new DateTime(2003, 1, 30);
>>MyCmd = new SqlCommand(Qry, MyCnn);
>>MyPar = MyCmd.Parameters.Add("@sd", DbType.DateTime);
>>MyPar.Value = MyDate;
>>MyCur = MyCmd.ExecuteReader();
>>While (MyCur.Read())
>> {
>> ...
>> }
>>MyCur.Close();
>>MyCnn.Close();
When the server "receive" the following batch:
>>exec sp_executesql N'SELECT * FROM booking WHERE start_date = @sd', N'@sd nvarchar(4000)', @sd = N'2003-01-30'
>>go
This is the source code that generate that:
>>STRING:
>>=======
>>SqlConnection MyCnn = new SqlConnection(<ConnectionString>
;>>SqlCommand MyCmd;
>>SqlDataReader MyCur;
>>SqlParameter MyPar;
>>String Qry = "SELECT * FROM booking WHERE start_date = @sd";
>>MyCmd = new SqlCommand(Qry, MyCnn);
>>MyPar = MyCmd.Parameters.Add("@sd", DbType.String);
>>MyPar.Value = "2003-01-30";
>>MyCur = MyCmd.ExecuteReader();
>>While (MyCur.Read())
>> {
>> ...
>> }
>>MyCur.Close();
>>MyCnn.Close();
So, in those two cases, the code control what is "sent" to the server. ADO.NET does not convert anything (at my sense).
Can you clarify where you see there is a conversion?
Also, I dont understand one point in your sentence: "... the conversion from string to datetime to string is performed ...". What do you mean by "conversion from string to datetime to string"?
Thank's a lot
Carl
-
Unfortunately he is no member of SSC
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Here is its answer:
>I just want to clarify something: I don't think that it is ADO.NET that
>convert anything.
>...
>>DATETIME:
>>=========
>>SqlConnection MyCnn = new SqlConnection(<ConnectionString>
;>>SqlCommand MyCmd;
>>SqlDataReader MyCur;
>>SqlParameter MyPar;
>>String Qry = "SELECT * FROM booking WHERE start_date = @sd";
>>DateTime MyDate = new DateTime(2003, 1, 30);
>>MyCmd = new SqlCommand(Qry, MyCnn);
>>MyPar = MyCmd.Parameters.Add("@sd", DbType.DateTime);
>>MyPar.Value = MyDate;
>>MyCur = MyCmd.ExecuteReader();
>>While (MyCur.Read())
>> {
>> ...
>> }
>>MyCur.Close();
>>MyCnn.Close();
OK, so .Net have a constructor you can create a datetime object directly.
But most of the time, the date comes from user input, so the string
has to be parsed and interpreted as a date somewhere. When you use
a datetime parameter, this happens client-side. When you pass the date
as a string, this happens server-side.
/Erland
Thank's also to Frank Kalis.
Carl
-
If you run profiler you can corroborate that
* Noel -
As I don't know .Net yet. What is the outcome, Carl? No big difference in both alternatives as I suspected or something to watch out for?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Frank,
Today we will make some other "isolated" performance tests, but since now we cannot see much difference between those two.
I will post the result later today if I have the time to.
Best regards,
Carl
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply