February 1, 2018 at 4:42 am
create table dbo.test (id smallint primary key, description nvarchar(50))
In yours opinión, what is the best solution for a lot of executions?
February 1, 2018 at 4:58 am
2
Simpler. Easier to read. Less prone to mistakes. Doesn't require permissions granted directly on the tables.
Though the two aren't equivalent. The WHERE clauses are different. Did you mean id = @id in the second?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 1, 2018 at 5:48 am
msimone - Thursday, February 1, 2018 4:42 AMHello, i have a discussion with a co-worker about normal code or dynamic code in a OLTP.
The example is:create table dbo.test (id smallint primary key, description nvarchar(50))
gocreate procedure dbo.test1 (@Id smallint = null)
as
declare @comando nvarchar(1024)
set @comando='select id,description from dbo.test '
set @inter='@id smllint'
if @id is not null
set @comando=@comando+'where id=@id'exec sp_executesql @comando,@inter,@id=@idgocreate procedure dbo.test2 (@id smallint,@rows int)
as
select top (@rows) id,description from dbo.test where @id>=@idIn yours opinión, what is the best solution for a lot of executions?
It is based on your need and requirements
February 1, 2018 at 7:08 am
GilaMonster - Thursday, February 1, 2018 4:58 AM2Simpler. Easier to read. Less prone to mistakes. Doesn't require permissions granted directly on the tables.
Though the two aren't equivalent. The WHERE clauses are different. Did you mean id = @id in the second?
Agreed, simpler is easier and better unless there is a reason to go complex.
February 1, 2018 at 7:56 am
msimone - Thursday, February 1, 2018 4:42 AMHello, i have a discussion with a co-worker about normal code or dynamic code in a OLTP.
The example is:create table dbo.test (id smallint primary key, description nvarchar(50))
gocreate procedure dbo.test1 (@Id smallint = null)
as
declare @comando nvarchar(1024)
set @comando='select id,description from dbo.test '
set @inter='@id smllint'
if @id is not null
set @comando=@comando+'where id=@id'exec sp_executesql @comando,@inter,@id=@idgocreate procedure dbo.test2 (@id smallint,@rows int)
as
select top (@rows) id,description from dbo.test where @id>=@idIn yours opinión, what is the best solution for a lot of executions?
To be completely honest I would say they are both flawed. The first one is just a strange way to use an optional parameter and dynamic sql just adds layers of complexity where it isn't needed. The second one at first glance is good but you are using top and there is no order by. That means you have no way of knowing what rows you will get because it can and will change between executions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 1, 2018 at 8:27 am
If you don't need to use dynamic SQL, don't use it. It adds complexity and can be difficult to debug when there are problems. This comes from experience as I find myself writing quite a bit of dynamic SQL where I work by the nature of the database and its design (or lack there of).
February 1, 2018 at 8:59 am
Hello, thanks for yours answers.
The idea is that procedures return one row or all, simple idea for a simple example.
The table is a simple and little table, with a real table the complexity is bigger.
Procedure one, the developer can pass a value or not, when @id is null, it doesn't concat the clause while and return all rows; when is not null, return the row if exists because the code concatenated the clause while.
Procedure two, the devoleper pass a value for @id and for @rows, because @rows will determinate the rows returned (one or all) and it will return row(s) from the value passed in @id. When the value for @id is the first key in the table or a value lesser than all keys, it will return all rows.
I always thought between procedure one and procedure two, compilations, recompilations and CPU cost was bigger in procedure one than procedure two.
For that, whether I am not correct, i needed a opinion about this question.
February 1, 2018 at 10:01 am
msimone - Thursday, February 1, 2018 8:59 AMThe idea is that procedures return one row or all, simple idea for a simple example.
That is a really, really bad idea.
Write procedures that do one thing and one thing only (single responsibility principle). So one procedure to fetch a filtered row. A different procedure to return all rows. No parameters for TOP, no weird dynamic SQL.
If you try to do both in one procedure, I promise you it will be a performance nightmare.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 1, 2018 at 10:06 am
Regarding dynamic sql versus paramaterized sql, probably the example you've provided has been simplified for illustration purposes, and this is really an attempt to get a more general answer intended to apply to a variety of similar problems. However, in the real world the answer depends. The most important thing is that you want to examine the execution plan for each case and confirm it's using an indexes efficiently as expected, because one method may result in an index covered query and the other method might not depending on the specifics. If you have something like a generic search procedure that implements a handful or more of optional WHERE clause predicates, then sometimes it makes sense just to return more rows than are needed and then apply additional filtering and sorting on the application side. What I mean is that an indexed seek that returns 10 rows, or even 100 rows, will often times perform better than a table scan that returns 1 row.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 1, 2018 at 10:40 am
msimone - Thursday, February 1, 2018 8:59 AMHello, thanks for yours answers.
The idea is that procedures return one row or all, simple idea for a simple example.
The table is a simple and little table, with a real table the complexity is bigger.
Procedure one, the developer can pass a value or not, when @id is null, it doesn't concat the clause while and return all rows; when is not null, return the row if exists because the code concatenated the clause while.
Procedure two, the devoleper pass a value for @id and for @rows, because @rows will determinate the rows returned (one or all) and it will return row(s) from the value passed in @id. When the value for @id is the first key in the table or a value lesser than all keys, it will return all rows.
I always thought between procedure one and procedure two, compilations, recompilations and CPU cost was bigger in procedure one than procedure two.
For that, whether I am not correct, i needed a opinion about this question.
I'd also say you're probably better off with separate procedures for one row vs all. It's likely that a different execution plan would be optimal for each case, so why risk getting stuck with the "wrong" plan in cache?
February 2, 2018 at 12:52 am
Thanks for all, yours opinions are importants for me.
February 2, 2018 at 5:48 pm
msimone - Friday, February 2, 2018 12:52 AMThanks for all, yours opinions are importants for me.
If you want to go beyond opinions, spend some time with this: Understanding Performance Mysteries
February 7, 2018 at 3:33 am
This was removed by the editor as SPAM
February 7, 2018 at 7:15 am
subramaniam.chandrasekar - Thursday, February 1, 2018 5:48 AMmsimone - Thursday, February 1, 2018 4:42 AMHello, i have a discussion with a co-worker about normal code or dynamic code in a OLTP.
The example is:create table dbo.test (id smallint primary key, description nvarchar(50))
gocreate procedure dbo.test1 (@Id smallint = null)
as
declare @comando nvarchar(1024)
set @comando='select id,description from dbo.test '
set @inter='@id smllint'
if @id is not null
set @comando=@comando+'where id=@id'exec sp_executesql @comando,@inter,@id=@idgocreate procedure dbo.test2 (@id smallint,@rows int)
as
select top (@rows) id,description from dbo.test where @id>=@idIn yours opinión, what is the best solution for a lot of executions?
It is based on your need and requirements
Not in this case. The code in the first snippet uses totally unnecessary Dynamic SQL and also contains a serious misunderstand of the rules of NULL and the second doesn't come close to the requirements implied by the first code.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 7, 2018 at 7:16 am
shwetakakran01 - Wednesday, February 7, 2018 3:33 AMyour code is good and there less chance of error.
Not correct. The second snippet doesn't do the same thing as the first snippet. The first snippet is also terrible for the reasons I've previously stated.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply