Thursday, February 21, 2013 8:13 PM
please help me,
i am putting my spCREATE PROCEDURE [dbo].[sp_Report_RxLastNameReport](@LastName VARCHAR(50),@StartDate DATE , @EndDate DATE )
set nocount on;
;WITH CTE_PatientLastName(firstname,middlename ,gender,PersonID,DOB,Street,city,Zip)
(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)
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]
its taking 38 seconds for
i need to decrease it
please give me solution
- Edited by Kalman TothMicrosoft Community Contributor, Moderator Friday, February 22, 2013 1:08 AM meaningful
Thursday, February 21, 2013 8:20 PM
Thursday, February 21, 2013 8:26 PM
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
Thursday, February 21, 2013 8:47 PM
Thanks for posting the complete query. Posting the query plan would be helpful too.
Here are a few index suggestions:
- Make sure there is an index on person_mASter(PersonID), preferably a clustered index
- 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.
- Create an index on LastName (if it doesn't exist already)
So in general, make sure all join columns of each table are indexed!
Thursday, February 21, 2013 8:49 PM
,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 HelperBlog Xing
Thursday, February 21, 2013 8:51 PM
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
Thursday, February 21, 2013 8:52 PM
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".
Thursday, February 21, 2013 9:06 PM
i tried tuning advisor ,it
says, the event was ignored because it does not have nay statement that can be tuned
Thursday, February 21, 2013 9:12 PM
after creating index on datefilled and lastname(nonclustered)
i m getting same result.
any other way
Thursday, February 21, 2013 10:41 PM
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)
Friday, February 22, 2013 1:05 AMModerator
>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