Solved

SQL Server 7 : View MASSIVELY slower than T SQL

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

911 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now