December 24, 2016 at 10:15 am
I'm trying to use plan guides to help some bad ORM queries ( hinting option(recompile ). Now I see that nearly identical queries may be called assigning different aliases to the same columns. I suspect this is going to cause plan guides to not be used, unless I'm going to create dozens of them. ?
AS Account02_LAST_MODIFIER38
AS Account02_LAST_MODIFIER56
AS Account02_LAST_MODIFIER32
SELECT Account02.ACCOUNT_ID AS Account02_ACCOUNT_ID6,Account02.LAST_MODIFIER AS Account02_LAST_MODIFIER38,Account02.LAST_MOD_DATE_TIME AS Account02_LAST_MOD_DATE_TIME40,Account02.TRANS_SEQ_NUM AS Account02_TRANS_SEQ_NUM42,Account02.CLIENT_ID AS Account02_CLIENT_ID8,Account02.CATEGORY AS Account02_CATEGORY12,Account02.SUB_CATEGORY AS Account02_SUB_CATEGORY48,Account02.CUSTOM_ATTRIBUTE_1 AS Account02_CUSTOM_ATTRIBUTE_150,Account02.CUSTOM_ATTRIBUTE_2 AS Account02_CUSTOM_ATTRIBUTE_252,Account02.CUSTOM_ATTRIBUTE_3 AS Account02_CUSTOM_ATTRIBUTE_354,Account02.CUSTOM_ATTRIBUTE_4 AS Account02_CUSTOM_ATTRIBUTE_427,Account02.CUSTOM_ATTRIBUTE_5 AS Account02_CUSTOM_ATTRIBUTE_558,Account02.CUSTOM_ATTRIBUTE_6 AS Account02_CUSTOM_ATTRIBUTE_660,Account02.BOOKED_DATE AS Account02_BOOKED_DATE62,Account02.FINANCED_DATE AS Account02_FINANCED_DATE10,Account02.AMOUNT_FINANCED AS Account02_AMOUNT_FINANCED66,Account02.CREATED_DATE_TIME AS Account02_CREATED_DATE_TIME68,Account02.EXPECTED_PAYOFF_DATE AS Account02_EXPECTED_PAYOFF_DATE70,Account02.ACTUAL_PAYOFF_DATE AS Account02_ACTUAL_PAYOFF_DATE72,Account02.RECOVERY_STATUS AS Account02_RECOVERY_STATUS5,Account02.BUSINESS_OBJECT_STATUS AS Account02_BUSINESS_OBJECT_STATUS0,Account02.LAST_STATUS_CHANGE AS Account02_LAST_STATUS_CHANGE78,Account02.PERFECTED_DATE_TIME AS Account02_PERFECTED_DATE_TIME2,Account02.FOLLOWUP_ENTITYLEGAL_ENTITY_ID AS Account02_FOLLOWUP_ENTITYLEGAL_ENTITY_ID82,Account02.USER_DEFINED_2 AS Account02_USER_DEFINED_224,Account02.USER_DEFINED_1 AS Account02_USER_DEFINED_186,Account02.USER_DEFINED_3 AS Account02_USER_DEFINED_388,Account02.LIEN_FILING_63DAY_MESSAGE_COUNT AS Account02_LIEN_FILING_63DAY_MESSAGE_COUNT90,Account02.TITLING_AGENCY_LIENHOLDERLIENHOLDER_ID AS Account02_TITLING_AGENCY_LIENHOLDERLIENHOLDER_ID92,Account02.BUSINESS_UNIT_ID AS Account02_BUSINESS_UNIT_ID94,Account02.CLOSED_REASON AS Account02_CLOSED_REASON96,Account02.IS_CLIENT_CONVERSION AS Account02_IS_CLIENT_CONVERSION98,Account02.LIENHOLDER_STATUS_CODE_ID AS Account02_LIENHOLDER_STATUS_CODE_ID100,Account02.IS_DOCUMENT_REQUIRED_FOR_PERFECTION AS Account02_IS_DOCUMENT_REQUIRED_FOR_PERFECTION102,Account02.CONTRACT_RELEASE_TYPE AS Account02_CONTRACT_RELEASE_TYPE104,Account02.CONTRACT_IMAGE_UNC_FULL_PATH AS Account02_CONTRACT_IMAGE_UNC_FULL_PATH106,Account02.NUMBER_OF_CONTRACT_IMAGE_PAGES AS Account02_NUMBER_OF_CONTRACT_IMAGE_PAGES108,Account02.CLOSING_REQUEST_TRANSACTION_TYPE AS Account02_CLOSING_REQUEST_TRANSACTION_TYPE110,Account02.CONTRACT_STATUS_AT_RELEASE AS Account02_CONTRACT_STATUS_AT_RELEASE112,Account02.CONTRACT_NOT_PRINTED_PULLED_REASON AS Account02_CONTRACT_NOT_PRINTED_PULLED_REASON114,Account02.IS_MTS AS Account02_IS_MTS116,Account02.IS_MULTICOLLATERAL AS Account02_IS_MULTICOLLATERAL118,Account02.IS_CLOSED_WITH_PAPER_TITLE_REQUEST AS Account02_IS_CLOSED_WITH_PAPER_TITLE_REQUEST120,Account02.CEASE_AND_DESIST AS Account02_CEASE_AND_DESIST122,Account02.DLS_DMV_FEE AS Account02_DLS_DMV_FEE124,Account02.LETTER_LANGUAGE_TYPE AS Account02_LETTER_LANGUAGE_TYPE126,Account02.LOAN_BALANCE AS Account02_LOAN_BALANCE128 FROM ACCOUNT AS Account02 INNER JOIN PROPERTY AS AccountProperty13 ON Account02.ACCOUNT_ID=AccountProperty13.ACCOUNT_ID INNER JOIN LEGAL_ENTITY AS FollowupEntity14 ON Account02.FOLLOWUP_ENTITYLEGAL_ENTITY_ID=FollowupEntity14.LEGAL_ENTITY_ID WHERE ((Account02.BUSINESS_OBJECT_STATUS = @DerivedTable01_BUSINESS_OBJECT_STATUS10 AND Account02.PERFECTED_DATE_TIME IS NULL AND AccountProperty13.EEE_DATE_TIME IS NULL AND Account02.RECOVERY_STATUS IS NULL AND Account02.ACCOUNT_ID NOT IN (SELECT ServicedAccount02_ACCOUNT_ID4 FROM (SELECT ServicedAccount02.ACCOUNT_ID AS ServicedAccount02_ACCOUNT_ID4 FROM SERVICED_COLLATERAL_GROUP_ITEM AS ServicedAccount02 INNER JOIN (SERVICE_REQUEST AS CollateralGroupRequest13 CROSS JOIN TITLING_AGENCY_REQUEST_TRANSACTION_TYPE AS ServiceRequestTransactionType14) ON ServicedAccount02.COLLATERAL_GROUP_REQUESTSERVICE_REQUEST_ID=CollateralGroupRequest13.SERVICE_REQUEST_ID WHERE ((CollateralGroupRequest13.CLIENT_ID = @DerivedTable01_CLIENT_ID31 AND ServicedAccount02.ACCOUNT_ID = Account02.ACCOUNT_ID AND (ServiceRequestTransactionType14.REQUEST_TRANSACTION_TYPE = CollateralGroupRequest13.REQUEST_TRANSACTION_TYPE AND ServiceRequestTransactionType14.REQUEST_TYPE IN ( @DerivedTable01_REQUEST_TYPE92, @DerivedTable01_REQUEST_TYPE103, @DerivedTable01_REQUEST_TYPE114, @DerivedTable01_REQUEST_TYPE125, @DerivedTable01_REQUEST_TYPE136) AND CollateralGroupRequest13.BUSINESS_PROCESS_STATUS = @DerivedTable01_BUSINESS_PROCESS_STATUS157 AND ServicedAccount02.ACCOUNT_ID = Account02.ACCOUNT_ID OR (CollateralGroupRequest13.REQUEST_TRANSACTION_TYPE = @DerivedTable01_REQUEST_TRANSACTION_TYPE198))) AND ((ServicedAccount02.CONCRETE_TYPE IN ( @DerivedTable01_209)) AND (CollateralGroupRequest13.CONCRETE_TYPE IN ( @DerivedTable01_2110, @DerivedTable01_2211, @DerivedTable01_2312, @DerivedTable01_2413, @DerivedTable01_2514, @DerivedTable01_2615, @DerivedTable01_2716, @DerivedTable01_2817, @DerivedTable01_2918, @DerivedTable01_3019, @DerivedTable01_3120, @DerivedTable01_3221, @DerivedTable01_3322, @DerivedTable01_3423, @DerivedTable01_3524, @DerivedTable01_3625, @DerivedTable01_3726, @DerivedTable01_3827, @DerivedTable01_3928, @DerivedTable01_4029, @DerivedTable01_4130, @DerivedTable01_4231, @DerivedTable01_4332, @DerivedTable01_4433, @DerivedTable01_4534, @DerivedTable01_4635, @DerivedTable01_4736, @DerivedTable01_4837, @DerivedTable01_4938, @DerivedTable01_5039, @DerivedTable01_5140, @DerivedTable01_5241, @DerivedTable01_5342, @DerivedTable01_5443, @DerivedTable01_5544, @DerivedTable01_5645, @DerivedTable01_5746, @DerivedTable01_5847, @DerivedTable01_5948, @DerivedTable01_6049, @DerivedTable01_6150, @DerivedTable01_6251, @DerivedTable01_6352, @DerivedTable01_6453))))) AS ScalarQueryTable) AND (Account02.CLIENT_ID = @DerivedTable01_CLIENT_ID954 AND (Account02.FINANCED_DATE >= @DerivedTable01_FINANCED_DATE1155) AND (Account02.CATEGORY = @DerivedTable01_CATEGORY1356) AND ((AccountProperty13.EXPECTED_TITLING_STATE_ABBR IN ( @DerivedTable01_EXPECTED_TITLING_STATE_ABBR1557, @DerivedTable01_EXPECTED_TITLING_STATE_ABBR1658))) AND (FollowupEntity14.ENTITY_TYPE = @DerivedTable01_ENTITY_TYPE1959 AND FollowupEntity14.CLIENTORGANIZATION_ID = @DerivedTable01_CLIENTORGANIZATION_ID2160 AND (FollowupEntity14.ENTITY_CODE NOT IN ( @DerivedTable01_ENTITY_CODE2361))) AND ((Account02.USER_DEFINED_2 NOT IN ( @DerivedTable01_USER_DEFINED_22562)) OR (Account02.USER_DEFINED_2 IS NULL )) AND ((Account02.CUSTOM_ATTRIBUTE_4 NOT IN ( @DerivedTable01_CUSTOM_ATTRIBUTE_42863)) OR (Account02.CUSTOM_ATTRIBUTE_4 IS NULL ))) AND (DateDiff(Day, Account02.FINANCED_DATE, GETDATE()) >= @DerivedTable01_3364)) AND ((AccountProperty13.CONCRETE_TYPE IN ( @DerivedTable01_3465)) AND (FollowupEntity14.CONCRETE_TYPE IN ( @DerivedTable01_3566))))
December 24, 2016 at 11:19 am
For the benefit of others, here's that query again, with line-breaks :w00t:
SELECT
Account02_ACCOUNT_ID6 = Account02.ACCOUNT_ID
, Account02_LAST_MODIFIER38 = Account02.LAST_MODIFIER
, Account02_LAST_MOD_DATE_TIME40 = Account02.LAST_MOD_DATE_TIME
, Account02_TRANS_SEQ_NUM42 = Account02.TRANS_SEQ_NUM
, Account02_CLIENT_ID8 = Account02.CLIENT_ID
, Account02_CATEGORY12 = Account02.CATEGORY
, Account02_SUB_CATEGORY48 = Account02.SUB_CATEGORY
, Account02_CUSTOM_ATTRIBUTE_150 = Account02.CUSTOM_ATTRIBUTE_1
, Account02_CUSTOM_ATTRIBUTE_252 = Account02.CUSTOM_ATTRIBUTE_2
, Account02_CUSTOM_ATTRIBUTE_354 = Account02.CUSTOM_ATTRIBUTE_3
, Account02_CUSTOM_ATTRIBUTE_427 = Account02.CUSTOM_ATTRIBUTE_4
, Account02_CUSTOM_ATTRIBUTE_558 = Account02.CUSTOM_ATTRIBUTE_5
, Account02_CUSTOM_ATTRIBUTE_660 = Account02.CUSTOM_ATTRIBUTE_6
, Account02_BOOKED_DATE62 = Account02.BOOKED_DATE
, Account02_FINANCED_DATE10 = Account02.FINANCED_DATE
, Account02_AMOUNT_FINANCED66 = Account02.AMOUNT_FINANCED
, Account02_CREATED_DATE_TIME68 = Account02.CREATED_DATE_TIME
, Account02_EXPECTED_PAYOFF_DATE70 = Account02.EXPECTED_PAYOFF_DATE
, Account02_ACTUAL_PAYOFF_DATE72 = Account02.ACTUAL_PAYOFF_DATE
, Account02_RECOVERY_STATUS5 = Account02.RECOVERY_STATUS
, Account02_BUSINESS_OBJECT_STATUS0 = Account02.BUSINESS_OBJECT_STATUS
, Account02_LAST_STATUS_CHANGE78 = Account02.LAST_STATUS_CHANGE
, Account02_PERFECTED_DATE_TIME2 = Account02.PERFECTED_DATE_TIME
, Account02_FOLLOWUP_ENTITYLEGAL_ENTITY_ID82 = Account02.FOLLOWUP_ENTITYLEGAL_ENTITY_ID
, Account02_USER_DEFINED_224 = Account02.USER_DEFINED_2
, Account02_USER_DEFINED_186 = Account02.USER_DEFINED_1
, Account02_USER_DEFINED_388 = Account02.USER_DEFINED_3
, Account02_LIEN_FILING_63DAY_MESSAGE_COUNT90 = Account02.LIEN_FILING_63DAY_MESSAGE_COUNT
, Account02_TITLING_AGENCY_LIENHOLDERLIENHOLDER_ID92 = Account02.TITLING_AGENCY_LIENHOLDERLIENHOLDER_ID
, Account02_BUSINESS_UNIT_ID94 = Account02.BUSINESS_UNIT_ID
, Account02_CLOSED_REASON96 = Account02.CLOSED_REASON
, Account02_IS_CLIENT_CONVERSION98 = Account02.IS_CLIENT_CONVERSION
, Account02_LIENHOLDER_STATUS_CODE_ID100 = Account02.LIENHOLDER_STATUS_CODE_ID
, Account02_IS_DOCUMENT_REQUIRED_FOR_PERFECTION102 = Account02.IS_DOCUMENT_REQUIRED_FOR_PERFECTION
, Account02_CONTRACT_RELEASE_TYPE104 = Account02.CONTRACT_RELEASE_TYPE
, Account02_CONTRACT_IMAGE_UNC_FULL_PATH106 = Account02.CONTRACT_IMAGE_UNC_FULL_PATH
, Account02_NUMBER_OF_CONTRACT_IMAGE_PAGES108 = Account02.NUMBER_OF_CONTRACT_IMAGE_PAGES
, Account02_CLOSING_REQUEST_TRANSACTION_TYPE110 = Account02.CLOSING_REQUEST_TRANSACTION_TYPE
, Account02_CONTRACT_STATUS_AT_RELEASE112 = Account02.CONTRACT_STATUS_AT_RELEASE
, Account02_CONTRACT_NOT_PRINTED_PULLED_REASON114 = Account02.CONTRACT_NOT_PRINTED_PULLED_REASON
, Account02_IS_MTS116 = Account02.IS_MTS
, Account02_IS_MULTICOLLATERAL118 = Account02.IS_MULTICOLLATERAL
, Account02_IS_CLOSED_WITH_PAPER_TITLE_REQUEST120 = Account02.IS_CLOSED_WITH_PAPER_TITLE_REQUEST
, Account02_CEASE_AND_DESIST122 = Account02.CEASE_AND_DESIST
, Account02_DLS_DMV_FEE124 = Account02.DLS_DMV_FEE
, Account02_LETTER_LANGUAGE_TYPE126 = Account02.LETTER_LANGUAGE_TYPE
, Account02_LOAN_BALANCE128 = Account02.LOAN_BALANCE
FROM
ACCOUNT Account02
INNER JOIN PROPERTY AccountProperty13
ON Account02.ACCOUNT_ID = AccountProperty13.ACCOUNT_ID
INNER JOIN LEGAL_ENTITY FollowupEntity14
ON Account02.FOLLOWUP_ENTITYLEGAL_ENTITY_ID = FollowupEntity14.LEGAL_ENTITY_ID
WHERE
(
(
Account02.BUSINESS_OBJECT_STATUS = @DerivedTable01_BUSINESS_OBJECT_STATUS10
AND Account02.PERFECTED_DATE_TIME IS NULL
AND AccountProperty13.EEE_DATE_TIME IS NULL
AND Account02.RECOVERY_STATUS IS NULL
AND Account02.ACCOUNT_ID NOT IN
(
SELECT ServicedAccount02_ACCOUNT_ID4
FROM
(
SELECT ServicedAccount02_ACCOUNT_ID4 = ServicedAccount02.ACCOUNT_ID
FROM
SERVICED_COLLATERAL_GROUP_ITEM ServicedAccount02
INNER JOIN(SERVICE_REQUEST CollateralGroupRequest13
CROSS JOIN TITLING_AGENCY_REQUEST_TRANSACTION_TYPE ServiceRequestTransactionType14)
ON ServicedAccount02.COLLATERAL_GROUP_REQUESTSERVICE_REQUEST_ID = CollateralGroupRequest13.SERVICE_REQUEST_ID
WHERE
(
(
CollateralGroupRequest13.CLIENT_ID = @DerivedTable01_CLIENT_ID31
AND ServicedAccount02.ACCOUNT_ID = Account02.ACCOUNT_ID
AND
(
ServiceRequestTransactionType14.REQUEST_TRANSACTION_TYPE = CollateralGroupRequest13.REQUEST_TRANSACTION_TYPE
AND ServiceRequestTransactionType14.REQUEST_TYPE IN ( @DerivedTable01_REQUEST_TYPE92
, @DerivedTable01_REQUEST_TYPE103
, @DerivedTable01_REQUEST_TYPE114
, @DerivedTable01_REQUEST_TYPE125
, @DerivedTable01_REQUEST_TYPE136
)
AND CollateralGroupRequest13.BUSINESS_PROCESS_STATUS = @DerivedTable01_BUSINESS_PROCESS_STATUS157
AND ServicedAccount02.ACCOUNT_ID = Account02.ACCOUNT_ID
OR (CollateralGroupRequest13.REQUEST_TRANSACTION_TYPE = @DerivedTable01_REQUEST_TRANSACTION_TYPE198)
)
)
AND
(
( ServicedAccount02.CONCRETE_TYPE IN ( @DerivedTable01_209 ))
AND (CollateralGroupRequest13.CONCRETE_TYPE IN ( @DerivedTable01_2110, @DerivedTable01_2211
, @DerivedTable01_2312, @DerivedTable01_2413
, @DerivedTable01_2514, @DerivedTable01_2615
, @DerivedTable01_2716, @DerivedTable01_2817
, @DerivedTable01_2918, @DerivedTable01_3019
, @DerivedTable01_3120, @DerivedTable01_3221
, @DerivedTable01_3322, @DerivedTable01_3423
, @DerivedTable01_3524, @DerivedTable01_3625
, @DerivedTable01_3726, @DerivedTable01_3827
, @DerivedTable01_3928, @DerivedTable01_4029
, @DerivedTable01_4130, @DerivedTable01_4231
, @DerivedTable01_4332, @DerivedTable01_4433
, @DerivedTable01_4534, @DerivedTable01_4635
, @DerivedTable01_4736, @DerivedTable01_4837
, @DerivedTable01_4938, @DerivedTable01_5039
, @DerivedTable01_5140, @DerivedTable01_5241
, @DerivedTable01_5342, @DerivedTable01_5443
, @DerivedTable01_5544, @DerivedTable01_5645
, @DerivedTable01_5746, @DerivedTable01_5847
, @DerivedTable01_5948, @DerivedTable01_6049
, @DerivedTable01_6150, @DerivedTable01_6251
, @DerivedTable01_6352, @DerivedTable01_6453
)
)
)
)
) ScalarQueryTable
)
AND
(
Account02.CLIENT_ID = @DerivedTable01_CLIENT_ID954
AND (Account02.FINANCED_DATE >= @DerivedTable01_FINANCED_DATE1155)
AND (Account02.CATEGORY = @DerivedTable01_CATEGORY1356)
AND ((AccountProperty13.EXPECTED_TITLING_STATE_ABBR IN ( @DerivedTable01_EXPECTED_TITLING_STATE_ABBR1557
, @DerivedTable01_EXPECTED_TITLING_STATE_ABBR1658
)
)
)
AND
(
FollowupEntity14.ENTITY_TYPE = @DerivedTable01_ENTITY_TYPE1959
AND FollowupEntity14.CLIENTORGANIZATION_ID = @DerivedTable01_CLIENTORGANIZATION_ID2160
AND (FollowupEntity14.ENTITY_CODE NOT IN ( @DerivedTable01_ENTITY_CODE2361 ))
)
AND
(
( Account02.USER_DEFINED_2 NOT IN ( @DerivedTable01_USER_DEFINED_22562 ))
OR (Account02.USER_DEFINED_2 IS NULL)
)
AND
(
( Account02.CUSTOM_ATTRIBUTE_4 NOT IN ( @DerivedTable01_CUSTOM_ATTRIBUTE_42863 ))
OR (Account02.CUSTOM_ATTRIBUTE_4 IS NULL)
)
)
AND (DATEDIFF(DAY, Account02.FINANCED_DATE, GETDATE()) >= @DerivedTable01_3364)
)
AND
(
( AccountProperty13.CONCRETE_TYPE IN ( @DerivedTable01_3465 ))
AND (FollowupEntity14.CONCRETE_TYPE IN ( @DerivedTable01_3566 ))
)
);
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
December 24, 2016 at 12:19 pm
Thanks. Now instead of the ORM-generated query looking vaguely awful, it is crystal-clear awful. It ran for 30 minutes in prod this morning before I had to leave the house.
I'll try again, but the estimated plan shown by sp_whoisactive during that run didn't indicate plan guide usage.
RetrievedFromCache="true" StatementSubTreeCost="0.105361" StatementEstRows="1" StatementOptmLevel="FULL" QueryHash="0xDC1A51A1EAEED72C" QueryPlanHash="0x75F3EB51F8C2007" StatementOptmEarlyAbortReason="GoodEnoughPlanFound">
December 26, 2016 at 5:55 am
Thom A (12/26/2016)
Sorry I'm not contributing but that is Beautiful, Phil 😀
SQL Prompt to the rescue!
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
December 27, 2016 at 8:20 am
As far as I can see the changing alias names are defeating the plan guide. Not to mention creating single-use plans.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply