Solved

SQL Server 7 : View MASSIVELY slower than T SQL

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

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.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

809 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