June 18, 2009 at 9:43 am
Greetz
I'm not sure what information you need to form a response to my question but I have attached table definitions for the 2 tables in the join as well as the functions definitions used in the column creations and where clause.
I am using SQL Server 2000 Enterprise on Windows Server 2003 Enterprise.
I have a Stored procedure with no parameters that returns a recordset to my asp.net application. I recently added a clustered index to the PurchaseOrderDetail table and performance improved dramatically. but while researching on the web I've heard that the use of UDFs can be a bad thing to use in your sql statements because they force the optimizer to not look at the indexes. Since my query actually got faster after adding an index this may not be the case, however I am wondering how this statement could be improved. I am using a distinct which will cause groans but in the current incarnation of this query to not use it would be to get incorrect results. 8 of the 12 columns are retrieved via UDFs and there are 2 UDFs in the where clause.
Here is the Stored Procedures Select Statement:
SELECT distinct dbo.fnGetDisplayPONumber(PurchaseOrderDetail.Final_POID) as DisplayID,
dbo.fnGetPurchaseOrderVoidState(PurchaseOrderDetail.Final_POID) as voidstate,
PurchaseOrderDetail.Final_POID,
PurchaseOrderDetail.PurchaseOrderGeneralID,
dbo.fnGetStaffFullNameWithID(PurchaseOrder.Originator_StaffRosterID,'') AS StaffName,
dbo.fnGetPurchaseOrder_Items_FIN(PurchaseOrder.PurchaseOrderGeneralID, PurchaseOrderDetail.Final_POID, 1) AS Items,
dbo.fnGetPurchaseOrder_Statuses(PurchaseOrder.PurchaseOrderGeneralID, PurchaseOrderDetail.Final_POID,1) AS Reviewinfo,
dbo.fnGetPurchaseOrder_SubTotal(PurchaseOrder.PurchaseOrderGeneralID, PurchaseOrderDetail.Final_POID,1) AS PO_SubTotal,
dbo.fnGetApprovedDate(PurchaseOrder.PurchaseOrderGeneralID,PurchaseOrderDetail.Final_POID) as PurchasingProcessDate,
dbo.fnGetHotel_Occupants(PurchaseOrder.PurchaseOrderGeneralID, PurchaseOrderDetail.Final_POID,1) as Hotel_Occupants,
PurchaseOrder.WillInvoice,
PurchaseOrder.IsVisa
FROM PurchaseOrder INNER JOIN
PurchaseOrderDetail on PurchaseOrder.PurchaseOrderGeneralID = PurchaseOrderDetail.PurchaseOrderGeneralID
WHERE (
dbo.fnCheckPurchaseOrder_Acctg(PurchaseOrder.PurchaseOrderGeneralID,PurchaseOrderDetail.Final_POID) = 0 and
dbo.fnGetPurchaseOrderVoidState(PurchaseOrderDetail.Final_POID) = 0
AND PurchaseOrderDetail.Purchasing_ReviewDate >= '04/27/2009'
)
The date in the where cla2se is actually set in a variable (@startDate) at the top of the stored procedure.
Here is the index definition on the purchaseorder detail table:
CREATE INDEX [CINCL_POGPODFP] ON [dbo].[PurchaseOrderDetail]([PurchaseOrderGeneralID], [PurchaseOrderDetailID], [Final_POID] DESC ) WITH FILLFACTOR = 75 ON [PRIMARY]
GO
I have also attached the Query Plans.
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
June 18, 2009 at 10:04 am
Please post index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
You can also have a look at this blog post on the performance implications of scalar UDFs http://sqlinthewild.co.za/index.php/2009/04/29/functions-io-statistics-and-the-execution-plan/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 18, 2009 at 11:26 am
Thanks. I've added those items to my original post. There is only one defined index outside of the clustered primary key which is defined as an identity. I know woefully little about sql ..enough to be dangerous i guess, but have recently begun learning more about it. I read the SQL In the Wild blog post and most of it went over my head buyt perhaps when I finish Ken England's book I'll be in a better position ot read and make use of it.
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
June 18, 2009 at 11:44 am
I made some changes to the query and reran it with the stats. i've attached the updated query plan to this post.
I replaced 2 functions in the select with ..don't know what they're called..computed columns maybe? and also replaced the udfs in the where clause by joining a table. Here is the updated Query. If someone could help me to understand the Query Plans I'd be grateful. I'm not looking for hand outs..honest!
SELECT distinct dbo.fnGetDisplayPONumber(PurchaseOrderDetail.Final_POID) as DisplayID,
dbo.fnGetPurchaseOrderVoidState(PurchaseOrderDetail.Final_POID) as voidstate,
PurchaseOrderDetail.Final_POID,
PurchaseOrderDetail.PurchaseOrderGeneralID,
(Select FirstName + ' ' + LastName From StaffRoster Where StaffRosterID = PurchaseOrder.Originator_StaffRosterID) AS StaffName,
dbo.fnGetPurchaseOrder_Items_FIN(PurchaseOrder.PurchaseOrderGeneralID, PurchaseOrderDetail.Final_POID, 1) AS Items,
dbo.fnGetPurchaseOrder_Statuses(PurchaseOrder.PurchaseOrderGeneralID, PurchaseOrderDetail.Final_POID,1) AS Reviewinfo,
dbo.fnGetPurchaseOrder_SubTotal(PurchaseOrder.PurchaseOrderGeneralID, PurchaseOrderDetail.Final_POID,1) AS PO_SubTotal,
(convert(varchar, PurchaseOrderDetail.Purchasing_ReviewDate, 101) + ' Purchasing
') as PurchasingProcessDate,
PurchaseOrder.WillInvoice,
PurchaseOrder.IsVisa
FROM PurchaseOrder INNER JOIN
PurchaseOrderDetail on PurchaseOrder.PurchaseOrderGeneralID = PurchaseOrderDetail.PurchaseOrderGeneralID
RIGHT OUTER JOIN PurchaseOrderNumber on PurchaseOrderDetail.Final_POID = PurchaseOrderNumber.PurchaseOrderNumber
WHERE (
(PurchaseOrderNumber.Void_StaffRosterID is null and PurchaseOrderNumber.Acctg_ProcessedDate is Null)
AND PurchaseOrderDetail.Purchasing_ReviewDate >= '04/27/2009'
)
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
June 18, 2009 at 11:48 pm
Perhaps a better question is how can the distinct be removed from the query?
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
June 19, 2009 at 12:14 am
To remove distinct you need to make sure that the parent child relationship be maintained in the tables and the columns should not have the redundant values .
So you will have to normalize the tables .
for example if you have booking ID and ticket id columns and in one booking you can book many tickets then one day you might end up writing a query that will use distinct clause on ticketID or booking id (just giving you a vague idea) if these columns are in same table .
So you need to have separate tables for bookingIDs and ticket IDs and have the parent child relationship among them .
This is the only way you can avoid using distincts ..
HTH
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
June 19, 2009 at 7:21 am
Thanks for your reply. I suspected that the rushed table construction would come back to haunt us! I've been able to get the execution time form 15 seconds down to 2 seconds by indexing one of the tables so I'm happy for now. Thanks for the help!
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply