List:General Discussion« Previous MessageNext Message »
From:James W. McNeely Date:August 23 2012 6:30pm
Subject:view query is slow
View as plain text  
I am working on a view based on this query:

=======================================
SELECT
-- Patient Info
p.IdPatient,
p.IdLastword MRN, 
p.NameLast, 
p.NameFirst, 
p.Addr1, 
p.Addr2, 
p.AddrCity, 
p.AddrState, 
p.AddrZip,
p.Gender, 
p.DateOfBirth,
-- Provider Info
af.IdAffil,
af.PractName, 
af.OfficeName,
-- Exam Info
e.IdExam,
e.dateexam, 
a.WorkArea dept, 
a.Room location, 
e.ProcModeCode,
e.ProcName,
e.IdRefSite,
ec.IdCPT,
e.zzk exam_zzk,
ec.zzk examcpt_zzk
FROM patient_ p
LEFT JOIN exams e ON e.IdPatient = p.IdPatient
LEFT JOIN Examcpt_ ec ON (e.IdExam = ec.IdExam AND '1' = Quantity)
LEFT JOIN Copy_ c ON CONCAT(e.IdAppt , '0') = c.IdApptType
LEFT JOIN Appt_ a ON e.IdAppt = a.IdAppt
LEFT JOIN Affil_ af ON c.IdPractAffil = af.IdAffil
WHERE 
p.AddrState = 'WA'
AND e.statusnumber = '4'
AND e.IdRefSite <> 'S50'
AND e.IdRefSite <> 'S51' 
AND e.IdREfSite <> 'S63' 
AND p.DateOfBirth < DATE_ADD(CURDATE(), INTERVAL '-2' MONTH) 
AND a.zzk IS NOT NULL
============================================

If I run this query itself (not in the view), and add this:

AND e.dateexam = '2012-08-13'

it runs like lightning, super fast. But if I run the query against the view, for example
"SELECT * FROM exam_view WHERE dateexam = '2012-08-13'

It is so glacially slow that I end up having to kill the query. What is going on, and how
can I fix this?

Jim McNeely
Northwest Radiologists
Senior Database Programmer
360-788-9022 desk
360-303-3332 mobile 
Thread
view query is slowJames W. McNeely23 Aug
  • RE: view query is slowRick James23 Aug
  • RE: view query is slowMartin Gainty23 Aug
    • Re: view query is slowJames W. McNeely23 Aug
      • Re: view query is slowJames W. McNeely23 Aug
  • Re: view query is slowShawn Green23 Aug
    • Re: view query is slowJames W. McNeely23 Aug
  • Re: view query is slowSergei Petrunia23 Aug