Solved

Writing fast-acting queries...without stored procedures

Posted on 2013-01-14
5
252 Views
Last Modified: 2013-01-15
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
Comment
Question by:PeterFrb
  • 2
  • 2
5 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38778514
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38778546
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
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 500 total points
ID: 38778976
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
 

Author Closing Comment

by:PeterFrb
ID: 38779478
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
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 38780049
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now