Ryan
Ryan

Reputation: 3492

TSQL query that uses function and view is very slow

Ok, first of all thanks in advance if you read through this whole thing as it may be quite painful on several levels.

  1. It's a long post
  2. It's gross
  3. It's going to probably make your brain hurt

But on the plus side, after reading through this whole thing I have a feeling the answer is very obvious and simple, so you have that going for you.

So I'll tell you the problem in a nutshell, and then in more detail:

Nutshell

Detailed description

Here are the tables that indicate children and parents. In this example we will be dealing with Fruits, Vegetables, and Planets.

Let's take a look at them...

Table 1 = Planets (I have no parents)

ID, Name
1, Earth
2, Saturn

Table 2 = Fruits (my parent is either a planet or a fruit)

ID, Name, PlanetName, FruitName
1, Kiwi, Earth, null
2, Strawberry, Saturn, null
3, Banana, null, Strawberry

Table 3 = Vegetables (my parent is planet or a fruit or a vegetable)

ID, Name, FruitName, PlanetName, VegetableName
1, Potato, Kiwi, null, null
2, Squash, null, Earth, null
3, Pumpkin, null, null, Potato

Table 4 = BigTable (this will be the one the main slow query is using. It has a column that contains just a child's name and it could be a planet or a fruit or a vegetable)

ID, Name, OneOfTheThree
1, John, Earth
2, Steve, Kiwi
3, Joe, Saturn
4, Jane, Potato

We have our tables and we have our data, what do I want to do now?

I want to create a query that looks at all of the OneOfTheThree values in the BigTable and find out what their lineage is (who there dads, grand parents etc are) and return that to the caller.

So my thought was to do this:

So I did it as follows:

My view

View = vwEverybodyAndTheirParents

-- Planets
SELECT Name, null AS Parent
FROM Planets
UNION
-- Fruits
SELECT Name, PlanetName AS Parent
FROM Fruits
UNION
-- Vegetables
SELECT Name, CASE WHEN FruitName IS NOT NULL THEN FruitName WHEN PlanetName IS NOT NULL THEN Planet ELSE NULL END AS Parent
FROM Vegetables

Ok, that gives me everything and it's parents. Now for the function to crawl that view and give me the period delimited string of the full ancestry:

My function

CREATE FUNCTION dbo.fnGetMyParent(@NameToGetParentsFor varchar(255))
RETURNS varchar(255)
AS
    DECLARE @InternalName varchar(255)
    DECLARE @ParentName varchar(255)
    DECLARE @ConcatenatedParentStringToReturn varchar(max)

    SELECT @ParentName = Parent
            ,@ConcatenatedParentStringToReturn = Name
    FROM vwEverybody
    WHERE Name = @NameToGetParentsFor

    WHILE @ParentName IS NOT NULL
    BEGIN
        SELECT @InternalName = Name, 
                @ParentName = Parent
        FROM vwEverybody
        WHERE Name = @ParentName

        SET @ConcatenatedParentStringToReturn = RTRIM(InternalName) + "." + RTRIM(@ConcatenatedParentStringToReturn)
    END

    RETURN @ConcatenatedParentStringToReturn
END 

This function works fine (though could be poorly coded and poorly performing?), so with all the above examples if I were to call it like so:

dbo.fnGetMyParent('Potato')

I get back the concatenated string of:

Earth.Kiwi.Potato

The problem

Ok, so now to finally get to the problem... the big query that takes forever:

SELECT Name,
       OneOfTheThree,
       fnGetMyParent(OneOfTheThree) as HeirarchyOfParents
FROM BigTable

I can see why it could take so long as for each value it executes the function which needs to then crawl a view. So...

My questions to you

A BIG THANK YOU if you made it this far!

Upvotes: 0

Views: 842

Answers (1)

Dumitrescu Bogdan
Dumitrescu Bogdan

Reputation: 7267

First of all when using sql you should avoid using loops as much as you can (unless the situation asks for it)

Second, there is no need of the view, or of the function as your query should be easily written in one go.

select 
  bt.Name
  ,bt.OneOfTheThree 
  ,p.Name+'.'+isnull(f.Name,'')+'.'+isnull(v.Name,'')+'.'+bt.Name as HeirarchyOfParents
from BigTable bt
left join Vegetables v
  on bt.OneOfTheThree = v.name
left join Fruits f
  on coalesce(v.FruitName,bt.OneOfTheThree) = f.Name
left join Planets p
  on coalesce(f.PlanetName,v.PlanetName,bt.OneOfTheThree) = p.Name

The last join you can remove if the table is consistent with the others, as it does not bring new information (the planet name is already there).

The improvements that you can bring here are with indexes on the tables, if you are able to do that.

Ok, with the new information, the easiest way I can think of is the following:

;with ftemp as (
  select 
    name as path
    ,PlanetName
    ,name as root
    ,name as name
    ,FruitName as parent
    ,0 as cnt
  from fruits
  union all
  select 
    fruits.name + '.' + ftemp.path
    ,ftemp.PlanetName
    ,root
    ,fruits.name
    ,cnt+1
  from fruits
  join ftemp
    on fruits.name= ftemp.parent
)
,fg as (
  select 
    name
    ,max(cnt) as cnt
  from ftemp
  group by name
)
,f as (
  select 
    ftemp.*
  from ftemp
  join fg
    on  ftemp.cnt = fg.cnt
    and ftemp.name = fg.name
)
,vtemp (same ideea)
,vg (same ideea)
,v (same ideea)
select 
  bt.Name
  ,bt.OneOfTheThree 
  ,p.Name+'.'+isnull(f.Path+'.','')+isnull(v.Path+'.','')+bt.Name as HeirarchyOfParents
from BigTable bt
left join v
  on bt.OneOfTheThree = v.name
left join f
  on coalesce(v.FruitName,bt.OneOfTheThree) = f.Name
left join Planets p
  on coalesce(f.PlanetName,v.PlanetName,bt.OneOfTheThree) = p.Name

With this approach though .. I have no idea on the performance it will yield. So it's up to you to complete the query and test.

Hope it helps.

Upvotes: 2

Related Questions