The Case for Specifying Data Types and Query Performance

  • Chad Feghali

    SSC Veteran

    Points: 227

    Comments posted to this topic are about the item The Case for Specifying Data Types and Query Performance

  • Marwa Joseph

    SSC Journeyman

    Points: 86

    Good job, you clearly explained the problem and the solution. Also specifying correct types should be of much assistance improving query performance.

  • RichB

    SSCrazy Eights

    Points: 9651

    May I suggest showing an extra couple of screenshots of the query plans showing where to see the implicit conversion? It might add a little to the learning experience. 😀

  • MarbryHardin

    Old Hand

    Points: 333

    Good to know.

    I'd point out that if someone's application is calling the same query repeatedly in a tight loop you might have a design issue though. You likely need to examine batching it via using a table type parameter to a stored procedure or some other method since individual DB calls each have a certain amount of overhead.

  • gary.strange-sqlconsumer

    SSCommitted

    Points: 1811

    Thanks Chad, this is exactly the sort of article I want read. It took me about 5mins to read and learnt a valuable lesson

  • Narud

    SSCrazy

    Points: 2826

    Very good and clear article Chad. Thanks!

  • Alan Burstein

    SSC Guru

    Points: 61088

    Great article Chad! I learned something new today.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • g.britton

    SSChampion

    Points: 13689

    Great article. Also a great take on some of the problems ORM can cause. It's one reason I'm not a LINQ-to-SQL fan. For anything but the simplest queries, you really need to work in SQL, get an efficient solution, then work back to your .Net program, probably using a stored procedure derived from your best SQL query.

    Gerald Britton, MCSE-DP, MVPToronto PASS Chapter[/url]

  • Chad Feghali

    SSC Veteran

    Points: 227

    I have enjoyed many great articles published to sqlservercentral.com. I am very happy to "pay it forward".


    Thank you all for the compliments!

    -Chad

  • Chad Feghali

    SSC Veteran

    Points: 227

    MarbryHardin (6/22/2015)


    Good to know.

    I'd point out that if someone's application is calling the same query repeatedly in a tight loop you might have a design issue though. You likely need to examine batching it via using a table type parameter to a stored procedure or some other method since individual DB calls each have a certain amount of overhead.

    Thank you for your feedback Marbry. Great point, I agree if the situation allows refactoring and making bulk calls, reduces chatter and contention.

    -Chad Feghali

  • Chad Feghali

    SSC Veteran

    Points: 227

    RichB (6/22/2015)


    May I suggest showing an extra couple of screenshots of the query plans showing where to see the implicit conversion? It might add a little to the learning experience. 😀

    Thank you for your feedback Rich. Good call, I will add that screenshot when an opportunity comes up for editing the article.

    -Chad Feghali

  • Chad Feghali

    SSC Veteran

    Points: 227

    I have enjoyed many great articles published to sqlservercentral.com. I am very happy to "pay it forward"!

    Thank you all for the compliments!

    -Chad Feghali

  • Orlando Colamatteo

    SSC Guru

    Points: 182269

    I love this article mainly because I can pass it on to one of senior .NET developers and do not have to write up anything myself to demo for him 😀

    I was just explaining this concept to him last week when asking if he declared his data types when adding items to the Parameters Collection and he confirmed he did not. Thanks for the clear and concise explanation of a very important topic!

    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

  • MarbryHardin

    Old Hand

    Points: 333

    Or of course you could just call a stored procedure and avoid this issue correct?

  • Orlando Colamatteo

    SSC Guru

    Points: 182269

    MarbryHardin (6/23/2015)


    Or of course you could just call a stored procedure and avoid this issue correct?

    Nope...the same issue persists with stored procedures but to a far, far, far lesser degree. Only the call into the proc interface would force an implicit type-conversion which is magnitudes better than using parameterized SQL as it would save the system from the scans shown in the article. It would look like this:

    -- call from .NET [without] strong types used in the Parameters Collection

    EXEC sys.sp_executesql

    N'exec dbo.storedProcedure @accountNumber',

    N'@accountNumber nvarchar(7)',

    @accountNumber = N'R123456';

    -- call from .NET [with] strong types used in the Parameters Collection

    EXEC sys.sp_executesql

    N'exec dbo.storedProcedure @accountNumber',

    N'@accountNumber char(20)', --< correct type

    @accountNumber = 'R123456';

    For a high volume system you might take a cumulative hit from the first type of arrangement but on the average system, probably not enough to register a noticeable dip in performance.

    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

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

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