June 5, 2003 at 12:48 am
Pl. see below given query , with most of tables outer joined with
Transactions table (5MB) are used. what is best way to optimize this query.
Select
therapyTypes.TherapyTypeID,
Transactions.OID,Transactions.OrgID,Transactions.LocationID,Transactions.Cre
atedBy,Transactions.CreatedOn,Transactions.ModifiedBy,
Transactions.ModifiedOn,Transactions.DeleteOID ,(CASE WHEN
Transactions.OrderType = 1 THEN (Select RxOrderHeader.PrescriptionNo FROM
RxOrderHeader
WHERE RxOrderHeader.OID = Transactions.OrderHeaderOID) ELSE (Select
HMEOrderHeader.OrderID
FROM HMEOrderHeader WHERE HMEOrderHeader.OID = Transactions.OrderHeaderOID)
END) as OrderID,
Transactions.OrderHeaderOID,DispensingHeader.OrderHeaderOID,
DispensingHeader.DispensingID, DispensingHeader.RefillNumber,
Transactions.ConfirmationDate,Transactions.DosesConfirmed,Transactions.Bille
dUnits,
Transactions.DeliveryDate, Transactions.OrderDetailOID,
Transactions.ItemDesc,Transactions.TransactionAmount,Transactions.OrderType,
Transactions.TaxRate,Transactions.isItemInvoiceTaxable,Transactions.Procedur
eCodeSource2,
Transactions.AuthNarrativesSource2,Transactions.AuthNarratives,Transactions.
TransactionBillingStatus,
Transactions.Modifier1Source2,Transactions.Modifier2Source2,Transactions.Mod
ifer3Source2,
Transactions.Modifier4Source2,Transactions.Modifier1,Transactions.Modifier2,
Transactions.Modifer3,Transactions.Modifier4,
Transactions.PriceAllowableSource2,Transactions.PriceAllowable,Transactions.
EquipmentOID,
Transactions.PayerAuthSource2OID,
Transactions.AuthOID,Transactions.DispensingHeaderOID,Transactions.BillingDa
te,
Transactions.DispensingDetailOID,Transactions.TherapyOID,Transactions.PriceT
ypeSource2,
Transactions.PricingType,Transactions.PayerSource2OID,Transactions.PayerSour
ceOID,
Transactions.IsAddedInBilling,Transactions.ProcedureCode,Transactions.Modifi
er1,Transactions.Modifier2,
Transactions.Modifer3,Transactions.Modifier4,
SOC.SOCID,SOC.SOCDesc,SOC.NDCMfgCodesOID,SOC.NDC,NDCMfgCodes.NDCMfgID,NDCMfg
Codes.MfgName,NDCMfgCodes.Abbriviation,
Transactions.ReBillOID,Transactions.ItemFromDate,Transactions.ItemThruDate,T
ickets.TicketID,Transactions.TRID, (Select InsuranceComp
from InsurancePlanHeader
where OID = Transactions.PayerSourceOID ) as Payer1,
(Select InsuranceComp from InsurancePlanHeader
where OID = Transactions.PayerSource2OID ) as Payer2,
(Select Status from InsurancePlanHeader where OID
=Transactions.PayerSourceOID
) as Payer1Status,
(Select Status from InsurancePlanHeader where OID
=Transactions.PayerSource2OID ) as Payer2Status,
Transactions.ListPrice,DispensingDetail.ClaimAmtPaid,
DispensingDetail.DispensingDate,Rebills.DateLastBilled,
Rebills.ChargeBillingDate,
Rebills.TotalNumberOfCharges, ( Select AuthID from AuthHeader
where OID = Transactions.AuthOID) as AuthID1,
(Select AuthID from AuthHeader
where OID = Transactions.PayerAuthSource2OID) as AuthID2,Equipment.SerialNo,
Equipment.ControlNo,Equipment.Manfacturer,
( Select BillingFormID from BillingForm, InsuranceBillingForm
where BillingForm.OID = InsuranceBillingForm.BillingFormOID and
InsuranceBillingForm.OID =Transactions.InsuranceFormOID )
as Insurance1,
( Select BillingFormID from BillingForm,
InsuranceBillingForm
where BillingForm.OID = InsuranceBillingForm.BillingFormOID and
InsuranceBillingForm.OID = Transactions.InvoiceFormOID ) as Invoice1,
( Select BillingFormID from BillingForm, InsuranceBillingForm
where BillingForm.OID = InsuranceBillingForm.BillingFormOID and
InsuranceBillingForm.OID =Transactions.InsuranceFormSource2OID ) as
Insurance2,
( Select BillingFormID from BillingForm,InsuranceBillingForm
where BillingForm.OID = InsuranceBillingForm.BillingFormOID and
InsuranceBillingForm.OID = Transactions.InvoiceFormSource2OID ) as Invoice2,
( Select Description from BillingForm, InsuranceBillingForm where
BillingForm.OID = InsuranceBillingForm.BillingFormOID and
InsuranceBillingForm.OID =
Transactions.InsuranceFormOID ) as InsuranceDesc1, ( Select Description
from
BillingForm, InsuranceBillingForm where BillingForm.OID
=InsuranceBillingForm.BillingFormOID
and InsuranceBillingForm.OID = Transactions.InvoiceFormOID ) as
InvoiceDesc1,( Select Description from BillingForm, InsuranceBillingForm
where
BillingForm.OID = InsuranceBillingForm.BillingFormOID and
InsuranceBillingForm.OID =
Transactions.InsuranceFormSource2OID ) as InsuranceDesc2, ( Select
Description
from BillingForm, InsuranceBillingForm where BillingForm.OID =
InsuranceBillingForm.BillingFormOID and InsuranceBillingForm.OID =
Transactions.InvoiceFormSource2OID
) as InvoiceDesc2
from Transactions with
(index(trtemp)),Therapy,TherapyTypes,DispensingHeader,DispensingDetail,
SOC,NDCMfgCodes,Tickets,Rebills,Equipment
where
Transactions.OID in ( '450622', '450637', '458892','441011' ) and
Transactions.SOCOID = SOC.OID and
Transactions.TicketOID *= Tickets.OID and
Transactions.RebillOID *= Rebills.OID and
Transactions.EquipmentOID *= Equipment.OID and
Transactions.DispensingHeaderOID *= DispensingHeader.OID and
Transactions.DispensingDetailOID *= DispensingDetail.OID and
Transactions.TherapyOID =Therapy.OID and
Therapy.TherapyTypeOID = TherapyTypes.OID and
SOC.NDCMfgCodesOID *= NDCMfgCodes.OID
order by TRANSACTIONS.OrderHeaderOID
TIA,
Sheilesh
June 5, 2003 at 8:12 am
OK, here goes:
1) Remove the INDEX HINT. Let SQL Server optimize on its own; it will usually pick the right path if the SQL is well-written.
2) Use modern SQL syntax. Much easier to pick out JOIN mistakes...
3) What on Earth is the normalized structure of your data? I know the insurance indusry is a nightmare, but this is crazy! 🙂
4) You have WAY to many tables JOINed to have a decent shot of optimization by SQL. Consider breaking the query into four or five resultsets with less joins. I am assuming this query is so you can output basically an entire insurance form into a single report, or PDF??? Try using more than one resultset.
5) Reduce the number of outputted fields in a single resultset. You have 91 output fields in a single query!!!
6) Here is your query using modern join syntax and a bit of formatting so that it's more easily read:
SELECT
tht.TherapyTypeID,
tr.OID,
tr.OrgID,
tr.LocationID,
tr.CreatedBy,
tr.CreatedOn,
tr.ModifiedBy,
tr.ModifiedOn,
tr.DeleteOID ,
(CASE
WHEN tr.OrderType = 1 THEN
(
SELECT RxOrderHeader.PrescriptionNo
FROM RxOrderHeader
WHERE RxOrderHeader.OID = tr.OrderHeaderOID
)
ELSE
(
SELECT HMEOrderHeader.OrderID
FROM HMEOrderHeader
WHERE HMEOrderHeader.OID = tr.OrderHeaderOID
)
END) AS OrderID,
tr.OrderHeaderOID,
dh.OrderHeaderOID,
dh.DispensingID,
dh.RefillNumber,
tr.ConfirmationDate,
tr.DosesConfirmed,
tr.BilledUnits,
tr.DeliveryDate,
tr.OrderDetailOID,
tr.ItemDesc,
tr.TransactionAmount,
tr.OrderType,
tr.TaxRate,
tr.isItemInvoiceTaxable,
tr.ProcedureCodeSource2,
tr.AuthNarrativesSource2,
tr.AuthNarratives,
tr.TransactionBillingStatus,
tr.Modifier1Source2,
tr.Modifier2Source2,
tr.Modifer3Source2,
tr.Modifier4Source2,
tr.Modifier1,
tr.Modifier2,
tr.Modifer3,
tr.Modifier4,
tr.PriceAllowableSource2,
tr.PriceAllowable,
tr.EquipmentOID,
tr.PayerAuthSource2OID,
tr.AuthOID,
tr.DispensingHeaderOID,
tr.BillingDate,
tr.DispensingDetailOID,
tr.TherapyOID,
tr.PriceTypeSource2,
tr.PricingType,
tr.PayerSource2OID,
tr.PayerSourceOID,
tr.IsAddedInBilling,
tr.ProcedureCode,
tr.Modifier1,
tr.Modifier2,
tr.Modifer3,
tr.Modifier4,
SOC.SOCID,
SOC.SOCDesc,
SOC.NDCMfgCodesOID,
SOC.NDC,
nd.NDCMfgID,
nd.MfgName,
nd.Abbriviation,
tr.ReBillOID,
tr.ItemFromDate,
tr.ItemThruDate,
tc.TicketID,
tr.TRID,
(
SELECT InsuranceComp
FROM InsurancePlanHeader
WHERE OID = tr.PayerSourceOID
) AS Payer1,
(
SELECT InsuranceComp
FROM InsurancePlanHeader
WHERE OID = tr.PayerSource2OID
) AS Payer2,
(
SELECT Status
FROM InsurancePlanHeader
WHERE OID =tr.PayerSourceOID
) AS Payer1Status,
(
SELECT Status
FROM InsurancePlanHeader
WHERE OID =tr.PayerSource2OID
) AS Payer2Status,
tr.ListPrice,
dd.ClaimAmtPaid,
dd.DispensingDate,
rb.DateLastBilled,
rb.ChargeBillingDate,
rb.TotalNumberOfCharges,
(
SELECT AuthID
FROM AuthHeader
WHERE OID = tr.AuthOID
) AS AuthID1,
(
SELECT AuthID
FROM AuthHeader
WHERE OID = tr.PayerAuthSource2OID
) AS AuthID2,
e.SerialNo,
e.ControlNo,
e.Manfacturer,
(
SELECT BillingFormID
FROM BillingForm
INNER JOIN InsuranceBillingForm
ON BillingForm.OID = InsuranceBillingForm.BillingFormOID
WHERE InsuranceBillingForm.OID = tr.InsuranceFormOID
) AS Insurance1,
(
SELECT BillingFormID
FROM BillingForm,
INNER JOIN InsuranceBillingForm
ON BillingForm.OID = InsuranceBillingForm.BillingFormOID
WHERE InsuranceBillingForm.OID = tr.InvoiceFormOID
) AS Invoice1,
(
SELECT BillingFormID
FROM BillingForm
INNER JOIN InsuranceBillingForm
ON BillingForm.OID = InsuranceBillingForm.BillingFormOID
WHERE InsuranceBillingForm.OID = tr.InsuranceFormSource2OID
) AS Insurance2,
(
SELECT BillingFormID
FROM BillingForm,
INNER JOIN InsuranceBillingForm
ON BillingForm.OID = InsuranceBillingForm.BillingFormOID
WHERE InsuranceBillingForm.OID = tr.InvoiceFormSource2OID
) AS Invoice2,
(
SELECT Description
FROM BillingForm
INNER JOIN InsuranceBillingForm
ON BillingForm.OID = InsuranceBillingForm.BillingFormOID
WHERE InsuranceBillingForm.OID = tr.InsuranceFormOID
) AS InsuranceDesc1,
(
SELECT Description
FROM BillingForm,
INNER JOIN InsuranceBillingForm
ON BillingForm.OID = InsuranceBillingForm.BillingFormOID
WHERE InsuranceBillingForm.OID = tr.InvoiceFormOID
) AS InvoiceDesc1,
(
SELECT Description
FROM BillingForm
INNER JOIN InsuranceBillingForm
ON BillingForm.OID = InsuranceBillingForm.BillingFormOID
WHERE InsuranceBillingForm.OID = tr.InsuranceFormSource2OID
) AS InsuranceDesc2,
(
SELECT Description
FROM BillingForm,
INNER JOIN InsuranceBillingForm
ON BillingForm.OID = InsuranceBillingForm.BillingFormOID
WHERE InsuranceBillingForm.OID = tr.InvoiceFormSource2OID
) AS InvoiceDesc2
FROM Transactions tr
INNER JOIN Therapy th
ON tr.TherapyOID = th.OID
INNER JOIN TherapyTypes tht
ON tr.TherapyType.OID = tht.OID
LEFT JOIN DispensingHeader dh
ON tr.DispendingHeaderOID = dh.OID
LEFT JOIN DispensingDetail dd
ON tr.DispensingDetail.OID = dd.OID
INNER JOIN SOC
ON tr.SOCOID = SOC.OID
LEFT JOIN NDCMfgCodes nd
ON SOC.NDCMfgCodesOID = nd.OID
LEFT JOIN Tickets tc
ON tr.TicketOID = tc.OID
LEFT JOIN Rebills rb
ON tr.RebillOID = rb.OID
LEFT JOIN Equipment e
ON tr.EquipmentOID = e.OID
WHERE
tr.OID in ( '450622', '450637', '458892','441011' ) and
ORDER BY tr.OrderHeaderOID
6) You can get rid of half of your sub-selects by joining on a derived table:
For instance, Insurance1 and InsuranceDesc1 can be redone like so:
SELECT ...fields...
, dt_i1.BillingFormID AS Insurance1
, dt_i1.Description AS InsuranceDesc1
...more fields...
FROM Transactions tr
...more joins...
INNER JOIN
(
SELECT BillingFormID , Description
FROM BillingForm
INNER JOIN InsuranceBillingForm
ON BillingForm.OID = InsuranceBillingForm.BillingFormOID
) AS dt_i1
ON dt_i1.BillingFormOID = tr.InsuranceFormOID
7) Whenever I see fields marked as Desc1, Desc2 and so on, it first makes me think that the data is itself unnormalized. Check to see whether the data can be normalized to one-to-many table structures.
8) Bottom line, though, you are trying to accomplish way too much in a single query. Splitting this into 3-5 separate procedures will do wonders in efficiency.
HTH,
Jay
Edited by - jpipes on 06/05/2003 08:14:17 AM
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply