July 13, 2011 at 9:00 am
Hello,
I am new to writing optimized code; I tried to do this before but never had an application that was large enough to know that I might not have been doing things the 'best' way. So, my question is this: I have a relatively simple query:
SELECT PO.purchaseOrderID
, PO.POnumber
, PO.dateInitiated
, PO.dateComplete
, VE.vendorName
, POI.qtyOrdered
, POI.qtyReceived
, POI.qtyCurrent
, IV.vendorItemNumber
, IT.companyItemNumber
, IT.itemDescript
, POI.qtyOrdered-ISNULL(POI.qtyReceived, 0) AS qtyOutstanding
FROM tblPurchaseOrder PO
-- may not be using vendors
LEFT OUTER JOIN tblVendor VE ON PO.vendorID = VE.vendorID
INNER JOIN tblPOItem POI ON PO.purchaseOrderID = POI.purchaseOrderID
LEFT OUTER JOIN tblItemVendor IV ON POI.itemID = IV.itemID
AND PO.vendorID = IV.vendorID
INNER JOIN tblItem IT ON POI.itemID = IT.itemID
The interface will need to call this by certain parameters, but not all parameters at the same time. The parameters will be:
purchaseOrderID
PONumber
dateCompleted (more a null check for all POs that aren't completed, etc.)
itemID
These parameters won't be called at the same time (only one per call), so I was going to write 4 stored procedures, one with each parameter as input.
Would the reusability of a view (using a stored procedure to query the view versus writing the table code out each time) outweigh performance loss?
If the view seems warranted, would indexing the view be recommended?
Thanks in advance for any answers!
July 13, 2011 at 9:06 am
Just a single stored proc with four input parameters should be fine. Deal with the fact that some will be NULL in the logic of the proc.
If you will only ever have one parameter at a time, merely adding that column as an index in the underlying table should give you the necessary performance.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 13, 2011 at 9:16 am
Thanks for the reply!
I previously had been writing stored procs this way, but in my optimization reading I found this article:
which explains why I should try to avoid doing so. Thus, the attempt to find a good alternate method that doesn't leave me having to change 3 or more stored procedures if a field gets added to the output 🙂
July 13, 2011 at 9:18 am
Sounds like this... http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
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
July 13, 2011 at 9:19 am
If it's only one per call I would recommend 4 procedures. You can write them to query a view, no difference, the view is inlined into the query during execution.
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
July 13, 2011 at 9:40 am
Clearly I should stick to SSIS questions and leave this stuff to people who know what they're talking about 🙂
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 13, 2011 at 2:07 pm
Phil Parkin (7/13/2011)
Clearly I should stick to SSIS questions and leave this stuff to people who know what they're talking about 🙂
Nah. This just happens to be one of the cases where the obvious and elegant solution doesn't work well.
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
July 14, 2011 at 5:51 am
That's okay - I'm sure I'll have a ton of SSIS questions once I figure out how it fits into the scheme of things! 🙂
July 14, 2011 at 6:00 am
Lisee (7/14/2011)
That's okay - I'm sure I'll have a ton of SSIS questions once I figure out how it fits into the scheme of things! 🙂
Haha, that will give me a chance to fix up the 'Could do better' assessment from this thread.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply