The Case for Specifying Data Types and Query Performance

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

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

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

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

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

  • Very good and clear article Chad. Thanks!

  • 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

  • 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, Pluralsight courses

  • 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

  • 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

  • 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

  • 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

  • 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

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

  • 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