Solved

SQL Server 7 : View MASSIVELY slower than T SQL

Posted on 2004-10-05
4
783 Views
Last Modified: 2008-03-17
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
Comment
Question by:JXCovert
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 12

Accepted Solution

by:
monosodiumg earned 500 total points
ID: 12230987
Is [Pre-AuthThin] the same server?
0
 

Author Comment

by:JXCovert
ID: 12231055
Yes it is.
0
 

Author Comment

by:JXCovert
ID: 12231098
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
 
LVL 12

Expert Comment

by:monosodiumg
ID: 12231338
:):) Love it!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question