Querying against a view or underlying tables: performance/reusability?

  • 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!

  • 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.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Thanks for the reply!

    I previously had been writing stored procs this way, but in my optimization reading I found this article:

    http://www.sqlskills.com/BLOGS/KIMBERLY/post/Stored-Procedure-Parameters-giving-you-grief-in-a-multi-purpose-procedure.aspx

    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 🙂

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Clearly I should stick to SSIS questions and leave this stuff to people who know what they're talking about 🙂

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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! 🙂

  • 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.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply