October 21, 2015 at 7:43 am
I have spoken with our development group about revising these updates. The other odd thing is that some don't use sp_executesql like most of our queries ( which are ORM generated ). Those deadlocking this morning that I captured with a server side trace were using sp_executesql however
exec sp_executesql N'UPDATE SERVICE_REQUEST SET SERVICE_REQUEST_STATUS = @DerivedTable01_SERVICE_REQUEST_STATUS5529, LAST_PRINTED_ON_REPORT_DATE = @DerivedTable01_LAST_PRINTED_ON_REPORT_DATE5730 etc etc
Join Join Join
where CollateralGroupRequest02.RECORDED_REQUEST_DATE <= @DerivedTable01_RECORDED_REQUEST_DATE92 ETC ETC
VARIABLE DECLARATIONS
,N'@DerivedTable01_REQUEST_TRANSACTION_TYPE40 varchar(8000), ETC ETC
VARIABLES ASSIGNED VALUES
,@DerivedTable01_REQUEST_TRANSACTION_TYPE40='ReleaseToRegisteredOwner' ETC ETC
October 21, 2015 at 9:39 am
Well, I see the problem, ORM Generated queries. They may be fine for trivial queries, but you really should write the more complex ones yourself to allow for performance tuning and optimization. In addition there may be performance enhancing techniques that an ORM isn't going to even consider writing.
Show me an ORM generated query utilizing a dynamic Tally table to boost performance.
October 21, 2015 at 10:01 am
Will it scale? Yes we have a shop, like others I'm sure, where DBAs are not in the loop when it comes to developing sql to satisfy business requirements. So we don't know the business logic well enough to even begin changing queries. Doesn't do much good to improve performance if the result is wrong.
Thanks for all of the feedback. I think I'm done for now.
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply