Aide pour optimiser une requête SQL / C#
Bonjour à tous,
dans le cadre de mon travail on m'a transmis cette requête qui aujourd'hui prend 7 secondes, l'objectif est de diviser par 3 son temps d'exécution. Quelqu'un a une idée de génie?
Merci à tous.
---- mise en place du test
declare @idCustomer nvarchar(8),@customerSubsidiary int,@MessageContact nvarchar(6)
Select @idCustomer = PKCustomer
from Customer with(nolock) where AccountNumber='7522008'
---------------------------------------------------------------------------
Select @customerSubsidiary = PKEntity
from Entity with(nolock)
where FunctionalID='ESS_FR'
print @customerSubsidiary
print @idCustomer
select TOP 1000 Request.PKRequest, FKCustomer_OrderedByDenorm as FKCustomer into #tempRequest
from Request with (NOLOCK)
INNER JOIN Customer AS FullCustomer WITH(NOLOCK) ON FKCustomer_OrderedByDenorm = FullCustomer.PKCustomer
INNER JOIN CustomerEntity ON CustomerEntity.FKCustomer = FullCustomer.PKcustomer
INNER JOIN CustomerEntity CECC ON CECC.FKCustomer = FullCustomer.PKcustomer
LEFT OUTER JOIN ProductOrderRequest with (NOLOCK) ON Request.PKRequest = ProductOrderRequest.IHProductOrderRequest
WHERE FKCustomer_OrderedByDenorm = @idCustomer
AND CustomerEntity.FKEntity = @customerSubsidiary
AND ( (Request.RequestType = 'ProductOrderRequest' AND ProductOrderRequest.FKProductOrderRequestType in (1,2,3,4,5,6,7,8,9,21,22) AND ( (( (( Request.FKRequestInvoicingStatus NOT IN (5,6) AND Request.FKRequestProcessingStatus NOT IN (7,9)AND Request.FKRequestProcessingStatus NOT IN (6) )) OR (Request.FKRequestProcessingStatus IN (7,9) AND Request.FKRequestInvoicingStatus NOT IN (5,6)) )) OR (Request.FKRequestProcessingStatus IN (7,9) AND Request.FKRequestInvoicingStatus NOT IN (5,6)) ))
OR (Request.RequestType = 'ProductQuotationRequest' AND ( ( Request.FKRequestProcessingStatus IN (1, 2, 4, 5, 7, 9, 3, 10)) OR (Request.FKRequestProcessingStatus IN (7,9) AND Request.FKRequestInvoicingStatus NOT IN (5,6)) ))
)
ORDER BY PKRequest Desc
SELECT DISTINCT
-- Contact
FullCustomer.PKCustomer,
FullCustomer.AccountNumber,
FullCustomer.CommercialName,
Address.StreetLine1,
Address.StreetLine2,
Address.StreetLine3,
Address.City,
Address.ZipCode,
Address.Name,
Address.FKCountry, --Address
Contact.PhoneNumber,
Contact.ShortPhoneNumber,
Contact.FaxNumber,
Contact.Email,
-- Contact
DefaultDeliveryMode.Name as CustomerDeliveryModeName,
DefaultDeliveryMode.Code as CustomerDeliveryModeCode,--CustomerDeliveryMode
FullCustomer.FKCommercialStatus,
ProductOrderRequestDeliveryMode.Name as RequestDeliveryModeName,
ProductOrderRequestDeliveryMode.Code as RequestDeliveryModeCode,
-- Request
Request.PKRequest,
Request.FKCustomer_ToDeliver,
isnull(RequestLegacyData.DeliveryNoteNumber, Request.FunctionalId),
Request.FunctionalId,
RequestProcessingStatus.Code RequestStatus, -- Etat
RR.Code as StatusReasonCode, -- Status Reason
rvm.Message as RequestValidityMessage,
Request.RequestProcessingStatusStartDateDenorm, -- Date du dernier changement d'état
RequestInvoicingStatus.Code RequestInvoicingStatusCode, -- Etat de Facturation
Request.ProductComponentCustomerReferenceDenorm, -- Contre Marque
Request.MainOfferId,
Request.MainOfferSumUp,
Product.ProductCodeSumUp,
Product.ProductNameSumUp,
--OrderNumber
CustomerReference.OrderNumber,
--Patient.FamilyName, -- Carte De Vue
Patient.FullName, -- Carte De Vue / A rétablir après que les données de ref soit bien insérées.
Patient.Title,
patient.GivenName,
patient.FamilyName,
TrackingRequestInformation.CurrentServerCode AS EntityCode, -- Serveur Code
TrackingRequestInformation.CurrentServerName AS EntityName, -- Serveur
Session.PKSession, -- Session
Session.RegistrationEndDate, -- Date PDC
ProductOrderRequest.CustomerCommittedDate, -- Date Initiale
ProductOrderRequest.LastRevisedDeliveryCommittedDateDenorm, -- Nouveau Délai
ProductOrderRequest.IsCustomerCommittedDateForced, -- Délai Forcée
-- Type Of Request
case when ProductQuotationRequest.IHProductQuotationRequest is not null then 1
--Warining !!!!!!!!
--This is for displaying the request with calculation requested (productOrderRequest in db)
--like quotation until OCE is not working.
else case when ProductOrderRequest.IHProductOrderRequest is not null AND RequestProcessingStatus.Code = 'RPS003' AND RR.Code = 'RPSR003' then 7 --
--else case when ProductOrderRequest.IHProductOrderRequest is not null AND ProductOrderRequestType.Code = 'PORT007' then 7 -- Common
else case when ProductOrderRequest.IHProductOrderRequest is not null then 2
else case when ProductReturnRequest.IHProductReturnRequest is not null then 3
else case when ProductAdjustmentRequestType.Code = 'PART001' then 4
else case when ProductAdjustmentRequestType.Code = 'PART002' then 5
else 6 end end end end end end as ProductType,
-- Messages
dbo.fn_GetMessageCount(Request.PKRequest,@MessageContact) AS MessageCount, -- Nombre de messages pour une request
dbo.fn_GetReadMessageCount(Request.PKRequest) AS MessageRead, -- Nombre de messages lus pour un message contact donnée
dbo.fn_GetUnReadMessageCount(Request.PKRequest) AS MessageUnRead, -- Nombre de messages lus pour un message contact donnée
--DeliveredCustomer
LightCustomer.PKCustomer AS LightPKCustomer,
--CustomerRelationProtocol
CustomerRelationProtocol.PKCustomerRelationProtocol,
--InternalRegistrationSoftware
InternalRegistrationSoftware.Code,
--RequestRangeType
TrackingRequestInformation.FKRangeType,
-- TrackingProductFamily
TrackingRequestInformation.FKTrackingProductFamily,
--Waiting for blank
dbo.IsRequestWaitingForBlank(Request.PKRequest),
--Chrono Lens ?
TrackingRequestInformation.LeadTimeManagement,
--Different Lens ?
TrackingRequestInformation.HasDifferentLenses,
--Different Lens ?
dbo.fn_HasHierarchicalLink(Request.PKRequest) AS HasHierarchicalLink,
-- ProductOrderRequestType : to know if a request is out of range
ProductOrderRequestType.Code,
--ShippingMode
FullCustomer.FKShippingMode,
Carrier.FunctionalId,
Carrier.Name,
ProductQuotationRequest.NumberOfDaysForDelivery,
Request.RequestType,
ProductOrderRequest.FKProductOrderRequestType,
ProductQuotationRequest.FKProductQuotationRequestType,
ProductAdjustmentRequest.FKProductAdjustmentRequestType,
ProductReturnRequest.FKProductReturnRequestType,
case when ProductOrderRequest.IsPriceonDeliveryNote =0 then null else ProductOrderRequest.IsPriceonDeliveryNote end as IsPriceonDeliveryNote
--CustomerReference
,CustomerReference.LinkedOrderNumber
--RequestLock
, RequestLock.PKRequestLock
, RequestLock.FKRequestLockReason
, RequestLock.FKUser
, RequestLock.CreationDate
FROM (SELECT TOP 1000 * FROM #tempRequest) #tempRequest
INNER JOIN Request WITH(NOLOCK) on Request.PKRequest = #tempRequest.PKRequest
-- This requests are needed to join the customer to the request
LEFT OUTER JOIN ProductQuotationRequest WITH(NOLOCK) ON Request.PKRequest = ProductQuotationRequest.IHProductQuotationRequest
LEFT OUTER JOIN ProductOrderRequest WITH(NOLOCK) ON Request.PKRequest = ProductOrderRequest.IHProductOrderRequest
INNER JOIN Session WITH(NOLOCK) ON Session.PKSession = Request.FKSession
-- Tracking request information
LEFT OUTER JOIN TrackingRequestInformation WITH(NOLOCK) ON TrackingRequestInformation.FKRequest = Request.PKRequest
-- Customer with informations
INNER JOIN Customer AS FullCustomer WITH(NOLOCK) ON Request.FKCustomer_OrderedByDenorm = FullCustomer.PKCustomer
INNER JOIN RequestProcessingStatus WITH(NOLOCK) ON Request.FKRequestProcessingStatus = RequestProcessingStatus.PKRequestProcessingStatus
INNER JOIN RequestInvoicingStatus WITH(NOLOCK) ON Request.FKRequestInvoicingStatus = RequestInvoicingStatus.PKRequestInvoicingStatus
INNER JOIN CustomerRelationProtocol WITH(NOLOCK) ON Session.FKCustomerRelationProtocol = CustomerRelationProtocol.PKCustomerRelationProtocol
INNER JOIN InternalRegistrationSoftware WITH(NOLOCK) ON Session.FKInternalRegistrationSoftware = InternalRegistrationSoftware.PKInternalRegistrationSoftware
LEFT OUTER JOIN CustomerAddress WITH(NOLOCK) ON FullCustomer.PKCustomer = CustomerAddress.FKCustomer
LEFT OUTER JOIN Address WITH(NOLOCK) ON CustomerAddress.IHCustomerAddress = Address.PKAddress -- Left JoinAddress
LEFT OUTER JOIN Contact WITH(NOLOCK) ON FullCustomer.PKCustomer = Contact.FKCustomer-- Left Join Contact
LEFT OUTER JOIN DeliveryMode DefaultDeliveryMode WITH(NOLOCK) ON FullCustomer.FKDeliveryMode = DefaultDeliveryMode.PKDeliveryMode--Left Join Customer delivery mode
LEFT OUTER JOIN CustomerReference WITH(NOLOCK) ON Request.FKCustomerReference = CustomerReference.PKCustomerReference--left join CustomerReference
LEFT OUTER JOIN DeliveryMode ProductOrderRequestDeliveryMode WITH(NOLOCK) ON ProductOrderRequest.FKDeliveryMode = ProductOrderRequestDeliveryMode.PKDeliveryMode
-- Request Information
LEFT OUTER JOIN RequestLegacyData WITH(NOLOCK) ON Request.PKRequest = RequestLegacyData.FKRequest
LEFT OUTER JOIN Patient WITH(NOLOCK) ON Request.FKPatient = Patient.PKPatient
LEFT OUTER JOIN Product WITH(NOLOCK) ON Request.FKProduct_Requested = Product.PKProduct
-- RequestType
LEFT OUTER JOIN ProductAdjustmentRequest WITH(NOLOCK) ON Request.PKRequest = ProductAdjustmentRequest.IHProductAdjustmentRequest
LEFT OUTER JOIN ProductAdjustmentRequestType WITH(NOLOCK) ON ProductAdjustmentRequest.FKProductAdjustmentRequestType = ProductAdjustmentRequestType.PKProductAdjustmentRequestType
LEFT OUTER JOIN ProductReturnRequest WITH(NOLOCK) ON Request.PKRequest = ProductReturnRequest.IHProductReturnRequest
-- DeliveredCustomer
LEFT OUTER JOIN Customer AS LightCustomer WITH(NOLOCK) ON Request.FKCustomer_ToDeliver = LightCustomer.PKCustomer
-- Demande de calcul
LEFT OUTER JOIN RequestProcessingStatusReason RR WITH(NOLOCK)
on RR.PKRequestProcessingStatusReason = Request.FKRequestProcessingStatusReason
-- Info transporteur : Carrier
LEFT OUTER JOIN Carrier WITH(NOLOCK) on Carrier.PKCarrier = FullCustomer.FKCarrier
-- Out of Range
LEFT OUTER JOIN ProductOrderRequestType WITH(NOLOCK) on ProductOrderRequest.FKProductOrderRequestType = ProductOrderRequestType.PKProductOrderRequestType
LEFT OUTER JOIN RequestValidityMessage rvm WITH(NOLOCK) on Request.PKRequest = rvm.FKRequest
LEFT OUTER JOIN RequestLock WITH(NOLOCK) ON Request.PKRequest = RequestLock.FKRequest--Left Join RequestLock
--ORDER BY Request.PKRequest DESC
--DROP TABLE #tempRequest
ORDER BY PKRequest Desc
DROP TABLE #tempRequest
Répondre avec citation
Il y a beaucoup de jointure externes. tu devrais essayer de la coller dans une nouvelle requête sur Sql server management studio et afficher le plan d'exécution estimé. Ca te donnera ce qui coûte cher en temps de traitement.
: "from Customer with(nolock) where AccountNumber='7522008'"
Partager