Pharmacy sp performance

Question

hi

please help me,

i am putting my sp

CREATE PROCEDURE [dbo].[sp_Report_RxLastNameReport](@LastName VARCHAR(50),@StartDate DATE , @EndDate DATE )
AS

set nocount on;
BEGIN



       ;WITH CTE_PatientLastName(firstname,middlename ,gender,PersonID,DOB,Street,city,Zip)
  AS
 (SELECT firstname,middlename,gender,a.PersonID,DOB, [Address_1] AS Street,
 Address_City AS City,b.[Address_ZIP] AS Zip from person_mASter a,
 [dbo].[Person_Address] b where a.PersonID = b.PersonID 
 and LastName = @LastName ),

     CTE_LastNameRecord (firstname,middlename,gender,dob, [Address], Date_Written, DateFilled, Prescription_Number, PatientID, PharmacyID, PrescriberID, Quality_Dispensed, productid,Refill_Number,Refills_Authorised, Payment_Type,days_supply)
              AS
              (
              SELECT I.firstname,I.middlename,I.gender,I.DOB,I.Street + I.City + I.Zip AS [Address], A.Date_Written, A.DateFilled, A.Prescription_Number, A.PatientID, A.PharmacyID, A.PrescriberID, B.[Quality_Dispensed], b.ProductID,C.Refill_Number,C.[Refills_Authorised],D.[PaymentDesc] AS Payment_Type ,C.Days_Supply AS days_supply 
 FROM [RxTrackData_Header] AS A, [RxTrackData_Footer] AS B,
 [dbo].[Dispensing_Trans] C,[dbo].[PaymentType] D,CTE_PatientLastName I
 WHERE A.DSP_ID = B.DSP_ID and   C.DSP_ID = A.DSP_ID and c.Payment_Type = D.[PaymentType] and  
 a.active = 1 and  A.PatientID = I.PersonID 
              and a.DateFilled between  @StartDate and @EndDate
              )

              SELECT  X.firstname,x.middlename, x.gender,x.dob,X.DateFilled AS Dispense_Date,X.[Address], X.Date_Written AS Rx_WrittenDate, K.ProductName, K.Active_Numerator_Strength AS Strength, g.[PrescriberZip] AS PrescriberZip,
              X.Quality_Dispensed AS Qty,x.Refill_Number,X.Refills_Authorised, G.First_Name+'  '+ G.Last_Name AS Prescriber, G.DEA_Number AS PrescriberDEA, F.Pharmacy_Name AS Dispenser,k.Deschedule, F.DEA_Number AS DispenserDEA, F.Address_ZIP AS DispenserZip, x.Prescription_Number AS RX#,X.Payment_Type,X.days_supply
              FROM  CTE_LastNameRecord AS X, [Pharmacy_Detail] AS F, [Prescriber_Detail] AS G, [Rx_MASter].[dbo].[NDC_mASter] AS K
              WHERE F.[PharmacyID] = X.[PharmacyID] AND G.[PrescriberID] = X.[PrescriberID] AND k.NDCId = x.[ProductID] 

END




GO

its taking 38 seconds for 

131999 rows. i need to decrease it please give me solution

Answer 1

See your other post with the same question: Performance of sp

Have you check the execution plan of you SP if suitable indexes are used or may table/index scans occur?
In SSMS menu Query = "Show Execution Plan" and then run the SP to get the execution plan

---

Olaf Helper

Blog Xing

Answer 2

i saw my plan, there is only 1 column in my table which is primary key(by default clustered)

where clustered index scan is occuring,what should i do,i cannot remove primary key

Answer 3

Thanks for posting the complete query. Posting the query plan would be helpful too.

Here are a few index suggestions:

1. Make sure there is an index on person_mASter(PersonID), preferably a clustered index
2. Make sure there is an index on Person_Adress(PersonID). If PersonID is the key in table Person_Address, then preferably create a clusted index.
3. Create an index on LastName (if it doesn't exist already)

So in general, make sure all join columns of each table are indexed!

---

Gert-Jan

Answer 4

,i cannot remove primary key

Of course you shouldn't remove a primary key or anything else.

If you have to less knowledge to solve it on your own (when don't have your database, so we can't do it), the start the Database Tuning Advisor; see Tutorial: Database Engine Tuning Advisor . It will suggest you some index and/or statistic to improve performance. Those suggestions are not optimal, but basically good.

---

Olaf Helper

Blog Xing

Answer 5

hi,

personid onperson_master is the primary key ,so its clustered index.

personid on person_address is foreign to person_master(person_id)

lastname doesnt hv index,do i need to create non clustered index on it

Answer 6

You didn't post table DDL, so all we can do is guess.  But indexes supporting person_master.lastname and RxTrackData_Header.DateFilled might help.  Your query execution plan will also indicate any "missing indexes".

David

---

David http://blogs.msdn.com/b/dbrowne/

Answer 7

i tried tuning advisor ,it

says, the event was ignored because it does not have nay statement that can be  tuned

Answer 8

after creating index on datefilled and lastname(nonclustered) 

i m getting same result.

any other way

Answer 9

From this I take it that person_id is not (also) the primary key of person_address.

You should definitely consider adding an index on person_master(LastName) INCLUDE (person_id)

---

Gert-Jan

Answer 10

>CREATE PROCEDURE [dbo].[sp_Report_RxLastNameReport](@LastName VARCHAR(50),@StartDate DATE , @EndDate DATE )

This sp is wrongly defined. What if the last name is "Smith" ? I am sure the user (hospital staff) is not interested in all the smiths, rather a specific patient.

Here is what I suggest. Change the sp definition  to be exact, use PatientID as input parameter.

---

Kalman Toth Database & OLAP Architect
Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012