asked on

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

Anthony Perkins

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.

Anthony Perkins

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

ASKER CERTIFIED SOLUTION

Ryan McCauley

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PeterFrb

ASKER

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

Ryan McCauley

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 :)