Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 790
  • Last Modified:

SQL Server 7 : View MASSIVELY slower than T SQL

Good day, all.  I have an issue where running a query against a view is about 50 times slower than running the same query against the view "source" is.

Here are the two queries:

--- USING THE VIEW
SELECT
    vwMainPreAuthInfo.EpisodeID, vwMainPreAuthInfo.EpisodeDate, vwMainPreAuthInfo.AuthStatus, vwMainPreAuthInfo.InsCarrier, vwMainPreAuthInfo.PatientID, vwMainPreAuthInfo.PatientMemberCode, vwMainPreAuthInfo.PatientName
FROM
    [Pre-AuthThin].dbo.vwMainPreAuthInfo vwMainPreAuthInfo
    WHERE vwMainPreAuthInfo.InsCarrier = 'AETNA-NNJ' AND vwMainPreAuthInfo.AuthStatus = 'D';
-- THAT TAKES OVER ONE MINUTE

-- NEXT WE TOOK THE "SOURCE OF THE VIEW AND ADDED THE WHERE CLAUSE TO IT

SELECT
    tblAuthorization.EpisodeID, tblAuthorization.EpisodeDate, tblAuthorization.OAOEpisodeID, tblAuthorization.OAOEpisodeDate, tblAuthorization.AuthStatus, tblAuthorization.MedRevStatus,     tblAuthorization.AuthApprovedDate, tblAuthorization.SentToReviewDate, tblAuthorization.ExpirationDate, tblAuthorization.Priority, tblAuthorization.InsCarrier, tblAuthorization.InsPlanCode,
    tblAuthorization.CPTCode, tblAuthorization.ICD9Code, tblAuthorization.TypeOfService, tblAuthorization.PlaceOfService, tblAuthorization.ReferralCodes, tblAuthorization.CaseNote,     tblAuthorization.DecisionCodes, tblAuthorization.MedRevNote, tblAuthorization.PhysicianCalled, tblAuthorization.CallerName, tblAuthorization.Ext, tblAuthorization.AwaitingInfo,     tblAuthorization.TestPerformed, tblAuthorization.FaxAttached, tblAuthorization.DenialPrinted, tblAuthorization.DenialLetterSentDate, tblAuthorization.Determinator, tblAuthorization.VerbalNoticeDate,    tblAuthorization.Recon, tblAuthorization.ReconDate, tblAuthorization.ReconOutcomeDate, tblAuthorization.Appeal, tblAuthorization.AppealDate, tblAuthorization.AppealOutcomeDate,     tblAuthorization.CobFlag, tblAuthorization.CobText, tblAuthorization.TimeStartDate, tblAuthorization.TimeEndDate, tblAuthorization.CreatedID, tblAuthorization.LastModifiedBy,
    tblAuthorization.LastModifiedDate, tblAuthorization.ReviewID, tblAuthorization.ApprovedFaxFLAG, tblAuthorization.ReExportYN, tblSite.OAOSiteID, tblSite.NonParSiteID, tblSite.SiteName, tblSite.SiteAddr1,     tblSite.SiteAddr2, tblSite.SiteCity, tblSite.SiteState, tblSite.SiteZip, tblSite.SitePhone, tblSite.SiteFax, tblSite.SiteSpec1, tblSite.SiteSpec2, tblSite.SiteSpecDesc1, tblSite.SiteSpecDesc2,     tblSite.SiteAlternateID, tblSite.SiteNYMIPar, tblPhysician.OAOPhysID, tblPhysician.NonParPhysID, tblPhysician.PhysName, tblPhysician.PhysAddr1, tblPhysician.PhysAddr2, tblPhysician.PhysCity,    tblPhysician.PhysState, tblPhysician.PhysZip, tblPhysician.PhysPhone, tblPhysician.PhysFax, tblPhysician.PhysSpec1, tblPhysician.PhysSpec2, tblPhysician.PhysSpecDesc1, tblPhysician.PhysSpecDesc2,     tblPhysician.PhysAlternateID, tblPatient.OAOSubNo, tblPatient.OAOPerNo, tblPatient.OAOEmpNo, tblPatient.PatientID, tblPatient.PatientMemberCode, tblPatient.PatientName, tblPatient.PatientAddr1,    tblPatient.PatientAddr2, tblPatient.PatientCity, tblPatient.PatientState, tblPatient.PatientZip, tblPatient.PatientSex, tblPatient.PatientPhone, tblPatient.PatientDOB, tblPatient.GroupNumber,    tblPatient.PlanType, tblPatient.LineOfBusiness, tblICD9.ICD9Desc, tblCPTProcedure.CPTName, tblCPTProcedure.CPTModality, tblCarrier.InsWritten, tblCarrier.InsGoldCard, tblCarrier.InsNCQA,
    tblCarrier.InsVisible, tblCarrier.InsNYMIPays, tblCarrier.Char5, tblCarrier.Char6, tblCarrier.EligPlan1, tblCarrier.EligPlan2, tblCarrier.Location, tblAuthorization.CERMReviewID,    tblAuthorization.TelephonicRequest, tblAuthorization.EligPatient, tblAuthorization.EligPhysician, tblAuthorization.EligSite, tblPatient.PatientPlanType, tblPatient.PatientEntity, tblCPTProcedure.CPTFullDesc
FROM tblAuthorization (NOLOCK)
    INNER JOIN tblCPTProcedure (NOLOCK) ON
        tblAuthorization.CPTCode = tblCPTProcedure.CPTCode
    INNER JOIN tblPatient (NOLOCK) ON
        tblAuthorization.EpisodeID = tblPatient.EpisodeID
        AND tblAuthorization.EpisodeDate = tblPatient.EpisodeDate
    INNER JOIN tblPhysician (NOLOCK) ON
        tblAuthorization.EpisodeID = tblPhysician.EpisodeID
        AND tblAuthorization.EpisodeDate = tblPhysician.EpisodeDate
    INNER JOIN tblSite (NOLOCK) ON
        tblAuthorization.EpisodeID = tblSite.EpisodeID
        AND tblAuthorization.EpisodeDate = tblSite.EpisodeDate
    LEFT JOIN tblICD9 (NOLOCK) ON
        tblAuthorization.ICD9Code = tblICD9.ICD9Code
    INNER JOIN tblCarrier (NOLOCK) ON
        tblAuthorization.InsCarrier = tblCarrier.InsCarrier
WHERE
    tblCPTProcedure.InsCarrier = 'BLANK'
      and tblAuthorization.InsCarrier = 'AETNA-NNJ' AND tblAuthorization.AuthStatus = 'D';

-- THIS RUNS IN UNDER FOUR SECONDS

How can this possibly be?  When I run execution plans for BOTH queries, the former one (using the view) is estimated to cost 99.80% of the batch.  Unreal.  Running the view table scans several tables.  The TSQL uses only indexes.

Letting alone the poor database design, what is it that is happening here?

This is SQL 7.  No indexed views.

Regards,

John
0
JXCovert
Asked:
JXCovert
  • 2
  • 2
1 Solution
 
monosodiumgCommented:
Is [Pre-AuthThin] the same server?
0
 
JXCovertAuthor Commented:
Yes it is.
0
 
JXCovertAuthor Commented:
Actually, no it was not.  I was running both queries in a smaller database, but the view being hit was in the larger db.

Thanks for the catch.
0
 
monosodiumgCommented:
:):) Love it!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now