Solved

SQL Server 7 : View MASSIVELY slower than T SQL

Posted on 2004-10-05
4
785 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

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

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

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.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

728 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