June 23, 2015 at 7:55 pm
For what it's worth, those implicit conversions can hit hard.
SQL Server Execution Times:
CPU time = 11701 ms, elapsed time = 12828 ms.
SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 0 ms.
Only difference between those two queries, was just the letter 'N', as the ORM (in this case, nHibernate) didn't much care for the particulars between nvarchar and varchar the same way SQL Server does. This also cut out a bunch of unnecessary disk (350k down to 10) and memory (512kb instead of 5,561,768kb).
Now while those numbers may change depending on the scale and size of your system, that yellow warning symbol is so calmly misleading.
June 24, 2015 at 7:15 am
Orlando Colamatteo (6/23/2015)
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 CollectionEXEC 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.
Exactly, it's a single conversion per call vs. a per row conversion which was the primary hit.
Not saying you shouldn't pick the low hanging fruit where you can so to speak, but there's usually enough other stuff going on that something like won't usually even show up on the radar. Especially not when you're trying to wean people off writing cursors, joining across linked servers and the like.
June 25, 2015 at 7:38 am
g.britton (6/22/2015)
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.
I totally agree.
July 28, 2015 at 3:14 pm
Nice description of a not so obvious performance pitfall, i'm adding this gem to my list of things to scan for when debugging slow code. 🙂
Grtz,
Theo
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply