SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


I need a opinion


I need a opinion

Author
Message
msimone
msimone
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1220 Visits: 597
Hello, 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))

go

create 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=@id

go

create procedure dbo.test2 (@id smallint,@rows int)
as
select top (@rows) id,description from dbo.test where @id>=@id


In yours opinión, what is the best solution for a lot of executions?




GilaMonster
GilaMonster
SSC Guru
SSC Guru (887K reputation)SSC Guru (887K reputation)SSC Guru (887K reputation)SSC Guru (887K reputation)SSC Guru (887K reputation)SSC Guru (887K reputation)SSC Guru (887K reputation)SSC Guru (887K reputation)

Group: General Forum Members
Points: 887664 Visits: 48655
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Subramaniam Chandrasekar
Subramaniam Chandrasekar
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3340 Visits: 531
msimone - Thursday, February 1, 2018 4:42 AM
Hello, 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))

go

create 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=@id

go

create procedure dbo.test2 (@id smallint,@rows int)
as
select top (@rows) id,description from dbo.test where @id>=@id


In yours opinión, what is the best solution for a lot of executions?

It is based on your need and requirements

xsevensinzx
xsevensinzx
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20159 Visits: 5433
GilaMonster - Thursday, February 1, 2018 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?

Agreed, simpler is easier and better unless there is a reason to go complex.

Sean Lange
Sean Lange
SSC Guru
SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)

Group: General Forum Members
Points: 250233 Visits: 19267
msimone - Thursday, February 1, 2018 4:42 AM
Hello, 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))

go

create 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=@id

go

create procedure dbo.test2 (@id smallint,@rows int)
as
select top (@rows) id,description from dbo.test where @id>=@id


In 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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (380K reputation)SSC Guru (380K reputation)SSC Guru (380K reputation)SSC Guru (380K reputation)SSC Guru (380K reputation)SSC Guru (380K reputation)SSC Guru (380K reputation)SSC Guru (380K reputation)

Group: General Forum Members
Points: 380492 Visits: 42209
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).

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
msimone
msimone
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1220 Visits: 597
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.



GilaMonster
GilaMonster
SSC Guru
SSC Guru (887K reputation)SSC Guru (887K reputation)SSC Guru (887K reputation)SSC Guru (887K reputation)SSC Guru (887K reputation)SSC Guru (887K reputation)SSC Guru (887K reputation)SSC Guru (887K reputation)

Group: General Forum Members
Points: 887664 Visits: 48655
msimone - Thursday, February 1, 2018 8:59 AM

The 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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Eric M Russell
Eric M Russell
SSC Guru
SSC Guru (105K reputation)SSC Guru (105K reputation)SSC Guru (105K reputation)SSC Guru (105K reputation)SSC Guru (105K reputation)SSC Guru (105K reputation)SSC Guru (105K reputation)SSC Guru (105K reputation)

Group: General Forum Members
Points: 105335 Visits: 14223
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.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Chris Harshman
Chris Harshman
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35559 Visits: 6834
msimone - Thursday, February 1, 2018 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.

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?

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search