The Case for Specifying Data Types and Query Performance

Question

The Case for Specifying Data Types and Query Performance

Chad Feghali

SSC Veteran

Points: 227
More actions
June 21, 2015 at 10:48 pm

#398629

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

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

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

Marwa Joseph SSC Journeyman Points: 86 More actions · Answer 1

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 More actions · Answer 2

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: 363 More actions · Answer 3

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: 1816 More actions · Answer 4

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: 2837 More actions · Answer 5

Narud

SSCrazy

Points: 2837

June 22, 2015 at 9:37 am

#1807091

Very good and clear article Chad. Thanks!

Alan Burstein SSC Guru Points: 61141 More actions · Answer 6

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: 13863 More actions · Answer 7

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

Chad Feghali SSC Veteran Points: 227 More actions · Answer 8

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 More actions · Answer 9

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 More actions · Answer 10

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 More actions · Answer 11

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: 182279 More actions · Answer 12

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: 363 More actions · Answer 13

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

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 14

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