Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Writing fast-acting queries...without stored procedures

Posted on 2013-01-14
5
Medium Priority
?
258 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

722 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