I need a opinion

  • 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?

  • 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
  • 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

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

  • 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 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/

  • 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).

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

  • 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
  • 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

  • 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?

  • Thanks for all, yours opinions are importants for me.

  • msimone - Friday, February 2, 2018 12:52 AM

    Thanks for all, yours opinions are importants for me.

    If you want to go beyond opinions, spend some time with this:  Understanding Performance Mysteries

  • This was removed by the editor as SPAM

  • subramaniam.chandrasekar - Thursday, February 1, 2018 5:48 AM

    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

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • shwetakakran01 - Wednesday, February 7, 2018 3:33 AM

    your 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply