Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Stored procedure working very slow Expand / Collapse
Author
Message
Posted Thursday, July 18, 2013 2:04 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 13,085, Visits: 12,551
huum (7/18/2013)
No no as you mentioned about un necessary repetative part of SP, thats what i meant


Ahh gotcha.

Consider this piece of code.

INSERT INTO @OfficeAccess (lOffice)
SELECT map.loffice
FROM map_PatientToOffice map
INNER JOIN map_PatientAtOfficeToCustodian mapc ON mapc.lpatient = map.lpatient
AND mapc.loffice = map.loffice
INNER JOIN @PhysiciansAccess PA ON PA.lphysician = mapc.lPhysician
WHERE map.lpatient = @lPatient
--and PA.lPhysician is not null

UNION

SELECT map.loffice
FROM map_PatientToOffice map
LEFT JOIN map_PatientAtOfficeToCustodian mapc ON mapc.lpatient = map.lpatient
AND mapc.loffice = map.loffice
WHERE map.lpatient = @lPatient
AND isNull(mapc.lPhysician, 0) <= 0

Those two queries are VERY similar. I am assuming that since you used UNION you want to exclude duplicates?

I don't have a table to work with to test but I THINK this should do the same thing.

INSERT INTO @OfficeAccess (lOffice)
SELECT map.loffice
FROM map_PatientToOffice map
INNER JOIN map_PatientAtOfficeToCustodian mapc ON mapc.lpatient = map.lpatient
AND mapc.loffice = map.loffice
left /*INNER JOIN*/ @PhysiciansAccess PA ON PA.lphysician = mapc.lPhysician
WHERE map.lpatient = @lPatient
and (PA.lPhysician is not null or isNull(mapc.lPhysician, 0) <= 0)
group by map.loffice

Assuming that returns the same data this would be a lot simpler. I think you could do something similar to the first insert/union too.



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1475211
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse