Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 259
  • Last Modified:

Writing fast-acting queries...without stored procedures

Working with SQL Server 2008, I'm having a devil of a time getting my query to deliver values efficiently.  Because of office politics, I do not have access to stored procedures, but I do have Inline table functions and Multi-line Table-valued functions available.  

The following is a simplified query showing what I'm trying to do, which I do now with the inline table function "fnct_VeryLargeDataset".

Select
    Itemized
,   Sum(Price)
From
    fnct_VeryLargeDataset('Parameter')
Group By
    Itemized
UNION ALL
Select
    'Total' as Itemized
,   Sum(Price)
From
    fnct_VeryLargeDataset('Parameter')

Open in new window


In this instance, I use the same function twice; although, in practice, I use them as many as 3 or 4 times, creating inefficiency by requiring the code to generate the recordset every time I call it.  If I had access to stored procedures, I would solve use the following code to solve the problem:

Create Procedure Gen_Itemized_Totals
(
    @Parameter nvarchar(75)
)
As 
Begin
    Select
        Itemized
    ,   Price
    Into
        #TempTable
    From
    (
       [Complex Query]
    )
    
    Select
        Itemized
    ,   Sum(Price) as Total
    From
        #TempTable
    Group By
        Itemized
    UNION ALL
    Select
        'Total' as Itemized
    ,   Sum(Price) As Total
    From
        #TempTable
END

Open in new window

   

Without question, the method above would achieve the desired results with maximum efficiency.  Within the context of a Multi-line Table-valued function, I thought that the code below would give me the same level of efficiency; but it is not to be!

I would like to know what the best way to achieve efficient results without the benefit of stored procedures.  While hoping for a break in the political impasse, I would like to know, in the interim, how best to create fast results.

Thanks, ~Peter Ferber

Create FUNCTION [dbo].[Gen_Itemized_Totals] 
(
    @Parameter nvarchar(75)
)
RETURNS 
@Output TABLE 
(
    Itemized nvarchar(75) Null
,   Total float Null
)
AS
BEGIN
    Declare @TempTable Table
    (
        Itemized nvarchar(75) Null
    ,   Total float Null
    )

    Select
        Itemized
    ,   Price
    Into
        @TempTable
    From
    (
       [Complex Query]
    )

    Select
        Itemized
    ,   Price as Total
    Into
        @Output
    From
    (
        Select
            Itemized
        ,   Total
        From
            @TempTable
        Group By
            Itemized
        UNION ALL
        Select
            'Total' as Itemized
        ,   Sum(Price) As Total
        From
            @TempTable
    ) As CombinedOutput

    Return
End

Open in new window

0
PeterFrb
Asked:
PeterFrb
  • 2
  • 2
1 Solution
 
Anthony PerkinsCommented:
To begin with I think you will find that UDF does not compile and you should change the SELECT ... INTO  to an INSERT.

Having said that, you will also find that multi-statement UDFs are slow as a dog and you would be wise to convert that to an inline function.
0
 
Anthony PerkinsCommented:
This should be more efficient:
CREATE FUNCTION [dbo].[Gen_Itemized_Totals] (
    @Parameter nvarchar(75))
RETURNS TABLE
AS

RETURN
    (
     SELECT CASE GROUPING(Itemized)
              WHEN 0 THEN Itemized
              ELSE 'Total'
            END Itemized,
            Price AS Total
     FROM   [Complex Query]
     GROUP BY ROLLUP(Itemized)
    )

Open in new window

0
 
Ryan McCauleyCommented:
Can you use a WITH statement to create a Common Table Expression (CTE) and pull the shared dataset up front? It's a bit tricky because you've got to put the UNION at a lower level if you want to share the CTE, but it should still work (as long as each time you call the function, you're using the same parameter - if each sub table in your example is using a different parameter to call the function, you can't use this method):

WITH VeryLargeDataset (Itemized, Price)
  AS (SELECT Itemized, Price
        FROM fnct_VeryLargeDataset('Parameter'))
Select
    Itemized
,   Price
FROM (SELECT Itemized,
             Sum(Price)
          From
               VeryLargeDataset
        Group By
              Itemized
         UNION ALL
        Select
            'Total' as Itemized,
            Sum(Price)
      From
           VeryLargeDataset) t

Open in new window


This way, it pulls it up front and then shares the results in your sub queries. If you've got a couple of copies of the function with different parameters, but they're reused, you can add multiple CTE functions to the top of your query.
0
 
PeterFrbAuthor Commented:
Ryan's solution most directly spoke to my question, and a quick test showed it working perfectly!  I am grateful to have this technique in my toolkit.  

Superb work.  With thanks and gratitude.  ~Peter
0
 
Ryan McCauleyCommented:
I actually just confirmed that I made it a bit more complicated than necessary - I thought the CTE would only hold for the first query in line, but the other queries after the UNION have access to it as well. Given that, you could do something like this instead:

WITH VeryLargeDataset (Itemized, Price)
  AS (SELECT Itemized, Price
        FROM fnct_VeryLargeDataset('Parameter'))
SELECT Itemized,
        Sum(Price)
     From
          VeryLargeDataset
   Group By
         Itemized
    UNION ALL
     Select
        'Total' as Itemized,
        Sum(Price)
  From
       VeryLargeDataset

Open in new window


Thanks for the points, but just wanted to clarify - I'm sure later viewers of this code in your application will thank you for the simplification :)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now