How can i make this more efficient? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How can i make this more efficient?

Author

Topic

em172967
Starting Member

10 Posts

Posted - 2014-08-12 : 10:27:06

[code]SELECT BarVisits.AccountNumber, BarChargeTransactions.TransactionProcedureID, BarChargeTransactions.ProcedureDescription, BarChargeTransactions.Amount,
DBarProcAltCodeEffectDates.EffectiveDateTime, DBarProcAltCodeEffectDates.Code, MONTH(BarChargeTransactions.ServiceDateTime) AS Month,
YEAR(BarChargeTransactions.ServiceDateTime) AS Year, BarVisits.InpatientOrOutpatient, DBarProcStats.Multiplier, DBarProcStats.Override,
BarChargeTransactions.PerformingProviderName, BarChargeTransactions.ProcedureChargeCategory, BarChargeTransactions.ProcedureChargeCategoryName,
BarChargeTransactions.ProcedureChargeDept, BarChargeTransactions.ProcedureTypeOfCharge, BarChargeTransactions.TransactionCount, BarVisits.Name,
BarChargeTransactions.ProfessionalComponent, BarChargeTransactions.PerformingProviderTypeName, BarChargeTransactions.BatchDateTime,
BarChargeTransactions.Batch, BarVisitFinancialData2.AttendProviderName
FROM DBarProcStats INNER JOIN
DBarProcedures ON DBarProcStats.ProcedureID = DBarProcedures.ProcedureID AND DBarProcStats.SourceID = DBarProcedures.SourceID INNER JOIN
DBarProcAltCodeEffectDates INNER JOIN
BarChargeTransactions INNER JOIN
BarVisits ON BarChargeTransactions.SourceID = BarVisits.SourceID AND BarChargeTransactions.BillingID = BarVisits.BillingID ON
DBarProcAltCodeEffectDates.EffectiveDateTime < BarChargeTransactions.ServiceDateTime AND
DBarProcAltCodeEffectDates.SourceID = BarChargeTransactions.SourceID AND
DBarProcAltCodeEffectDates.ProcedureID = BarChargeTransactions.TransactionProcedureID ON
DBarProcedures.SourceID = DBarProcAltCodeEffectDates.SourceID AND DBarProcedures.ProcedureID = DBarProcAltCodeEffectDates.ProcedureID INNER JOIN
BarVisitFinancialData2 ON BarVisits.VisitID = BarVisitFinancialData2.VisitID AND BarVisits.SourceID = BarVisitFinancialData2.SourceID AND
BarVisits.BillingID = BarVisitFinancialData2.BillingID
WHERE (BarChargeTransactions.ServiceDateTime > CONVERT(DATETIME, '2014-06-01 00:00:00', 102)) AND (DBarProcAltCodeEffectDates.EffectiveDateTime =
(SELECT MAX(EffectiveDateTime) AS pricedate
FROM DBarProcAltCodeEffectDates AS t1
WHERE (ProcedureID = DBarProcAltCodeEffectDates.ProcedureID))) AND (DBarProcStats.ExpressionID = '230')
ORDER BY BarVisits.AccountNumber, DBarProcAltCodeEffectDates.EffectiveDateTime DESC[/code]

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2014-08-12 : 11:02:57

Do you know what it is doing? What does the execution plan say? Do you have indexes?

djj

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-12 : 11:04:41

Let's start by making it easier to read:


SELECT BarVisits.AccountNumber
    ,BarChargeTransactions.TransactionProcedureID
    ,BarChargeTransactions.ProcedureDescription
    ,BarChargeTransactions.Amount
    ,DBarProcAltCodeEffectDates.EffectiveDateTime
    ,DBarProcAltCodeEffectDates.Code
    ,MONTH(BarChargeTransactions.ServiceDateTime) AS Month
    ,YEAR(BarChargeTransactions.ServiceDateTime) AS Year
    ,BarVisits.InpatientOrOutpatient
    ,DBarProcStats.Multiplier
    ,DBarProcStats.Override
    ,BarChargeTransactions.PerformingProviderName
    ,BarChargeTransactions.ProcedureChargeCategory
    ,BarChargeTransactions.ProcedureChargeCategoryName
    ,BarChargeTransactions.ProcedureChargeDept
    ,BarChargeTransactions.ProcedureTypeOfCharge
    ,BarChargeTransactions.TransactionCount
    ,BarVisits.NAME
    ,BarChargeTransactions.ProfessionalComponent
    ,BarChargeTransactions.PerformingProviderTypeName
    ,BarChargeTransactions.BatchDateTime
    ,BarChargeTransactions.Batch
    ,BarVisitFinancialData2.AttendProviderName
FROM DBarProcStats
INNER JOIN DBarProcedures ON DBarProcStats.ProcedureID = DBarProcedures.ProcedureID
    AND DBarProcStats.SourceID = DBarProcedures.SourceID
INNER JOIN DBarProcAltCodeEffectDates
INNER JOIN BarChargeTransactions
INNER JOIN BarVisits ON BarChargeTransactions.SourceID = BarVisits.SourceID
    AND BarChargeTransactions.BillingID = BarVisits.BillingID ON DBarProcAltCodeEffectDates.EffectiveDateTime < BarChargeTransactions.ServiceDateTime
    AND DBarProcAltCodeEffectDates.SourceID = BarChargeTransactions.SourceID
    AND DBarProcAltCodeEffectDates.ProcedureID = BarChargeTransactions.TransactionProcedureID ON DBarProcedures.SourceID = DBarProcAltCodeEffectDates.SourceID
    AND DBarProcedures.ProcedureID = DBarProcAltCodeEffectDates.ProcedureID INNER JOIN BarVisitFinancialData2 ON BarVisits.VisitID = BarVisitFinancialData2.VisitID
    AND BarVisits.SourceID = BarVisitFinancialData2.SourceID
    AND BarVisits.BillingID = BarVisitFinancialData2.BillingID WHERE (BarChargeTransactions.ServiceDateTime > CONVERT(DATETIME, '2014-06-01 00:00:00', 102))
    AND (
        DBarProcAltCodeEffectDates.EffectiveDateTime = (
            SELECT MAX(EffectiveDateTime) AS pricedate
            FROM DBarProcAltCodeEffectDates AS t1
            WHERE (ProcedureID = DBarProcAltCodeEffectDates.ProcedureID)
            )
        )
    AND (DBarProcStats.ExpressionID = '230') ORDER BY BarVisits.AccountNumber
    ,DBarProcAltCodeEffectDates.EffectiveDateTime DESC

em172967
Starting Member

10 Posts

Posted - 2014-08-12 : 11:32:26

Source ID is indexed in every table. BillingId is indexed for Barchargetransactions, Barvisits, BarvisitFinancialData2 and procedureid is indexed for DbarProcstats, dbarprocedures and dbarprocaltcodeeffectdate.

I don't really know what you mean by execution plan, the purpose of the query is to retrieve a list of medical providers, all the procedures they performed and get charges, wRVU's (which is the "multiplier" tied to expressionid = 230 in DBarprocstats and count of transactions by service month.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-08-12 : 12:10:05

Yeah, this is complicated enough that we would need to see the full table and index definitions. And the query plan from SQL. You can get a query plan be adding this statement above the query before you run it:
SET STATISTICS XML ON
Then in the query results, you'll see the plan, in XML. Copy the XML onto the site.



SELECT BarVisits.AccountNumber
    ,BarChargeTransactions.TransactionProcedureID
    ,BarChargeTransactions.ProcedureDescription
    ,BarChargeTransactions.Amount
    ,DBarProcAltCodeEffectDates.EffectiveDateTime
    ,DBarProcAltCodeEffectDates.Code
    ,MONTH(BarChargeTransactions.ServiceDateTime) AS Month
    ,YEAR(BarChargeTransactions.ServiceDateTime) AS Year
    ,BarVisits.InpatientOrOutpatient
    ,DBarProcStats.Multiplier
    ,DBarProcStats.Override
    ,BarChargeTransactions.PerformingProviderName
    ,BarChargeTransactions.ProcedureChargeCategory
    ,BarChargeTransactions.ProcedureChargeCategoryName
    ,BarChargeTransactions.ProcedureChargeDept
    ,BarChargeTransactions.ProcedureTypeOfCharge
    ,BarChargeTransactions.TransactionCount
    ,BarVisits.NAME
    ,BarChargeTransactions.ProfessionalComponent
    ,BarChargeTransactions.PerformingProviderTypeName
    ,BarChargeTransactions.BatchDateTime
    ,BarChargeTransactions.Batch
    ,BarVisitFinancialData2.AttendProviderName
FROM DBarProcStats
INNER JOIN DBarProcedures ON 
    DBarProcStats.SourceID = DBarProcedures.SourceID AND
    DBarProcStats.ProcedureID = DBarProcedures.ProcedureID
INNER JOIN DBarProcAltCodeEffectDates ON 
    DBarProcedures.SourceID = DBarProcAltCodeEffectDates.SourceID AND
    DBarProcedures.ProcedureID = DBarProcAltCodeEffectDates.ProcedureID 
INNER JOIN BarChargeTransactions ON 
    DBarProcAltCodeEffectDates.EffectiveDateTime < BarChargeTransactions.ServiceDateTime AND
    DBarProcAltCodeEffectDates.SourceID = BarChargeTransactions.SourceID AND
    DBarProcAltCodeEffectDates.ProcedureID = BarChargeTransactions.TransactionProcedureID
INNER JOIN BarVisits ON 
    BarChargeTransactions.SourceID = BarVisits.SourceID AND
    BarChargeTransactions.BillingID = BarVisits.BillingID    
INNER JOIN BarVisitFinancialData2 ON
    BarVisits.VisitID = BarVisitFinancialData2.VisitID AND
    BarVisits.SourceID = BarVisitFinancialData2.SourceID AND
    BarVisits.BillingID = BarVisitFinancialData2.BillingID 
WHERE 
    (BarChargeTransactions.ServiceDateTime > '20140601 00:00:00') AND
    (DBarProcAltCodeEffectDates.EffectiveDateTime = (
        SELECT MAX(EffectiveDateTime) AS pricedate
        FROM DBarProcAltCodeEffectDates AS t1
        WHERE (ProcedureID = DBarProcAltCodeEffectDates.ProcedureID)
        )
     ) AND
     (DBarProcStats.ExpressionID = '230') 
ORDER BY BarVisits.AccountNumber
    ,DBarProcAltCodeEffectDates.EffectiveDateTime DESC

em172967
Starting Member

10 Posts

Posted - 2014-08-12 : 14:52:34

Alright. I don't think I can do that as I am just running out of VS express. Any work around or am I just out of luck?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-12 : 14:59:22

quote:
Originally posted by em172967

Alright. I don't think I can do that as I am just running out of VS express. Any work around or am I just out of luck?

Download and install SQL Server Management Studio Express.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources