December 30, 2015 at 1:25 pm
I have a function that performs joins using user defined table types. In the attached query plan, all of the highest cost operations (Clustered Index Scan) involve these user defined table types. All of the types have a single column that is a primary key.
In the case of the attached query, only one of these tables even has a single row (represented as Object2 in the query plan). All of the others are empty but still have very high costs (all of the other objects with high costs).
This snippet is not the exact code but should give an idea of how the types are used in my function:
ALTER FUNCTION [dbo].[GetItems] (
@StateAbbrTable StateAbbr READONLY
)
RETURNS TABLE
AS
RETURN
SELECT * FROM MyView -- MyView underlying table contains 50 million rows
LEFT JOIN [ItemDetails] on MyView.ItemId = ItemDetails.ItemId -- has 1 million rows, one to many relationship with MyView
LEFT JOIN [State] ON MyView.[StateId] = [State].[StateId] -- has 50 rows
LEFT JOIN @StateAbbrTable StateInput ON StateInput.[State] = [State].Abbreviation -- lets assume only 1 row is here (as is shown in the attched query plan)
WHERE
(
(
--this takes care of only including states that were passed through the tvp OR everything if no states were passed
SELECT count(*)
FROM @StateAbbrTable
) = 0
OR StateInput.[State] IS NOT NULL
)
MyView:
SELECT [all columns] FROM MyTable Where Category <> 'A' -- MyTable contains 15 million rows
Does anyone here have an idea as to why this might be occurring or what I might be doing wrong?
December 30, 2015 at 1:45 pm
Try to save result of "SELECT count(*) FROM @StateAbbrTable" into true/false variable and then use it in the query. Also add OPTION(RECOMPILE). This will help to eliminate ambiguity of OR conditions plus will allow for better table variable row estimations.
December 30, 2015 at 2:58 pm
Is there any way to do this and keep it as an inline function? Also, option(recompile) is already part of the query.
January 4, 2016 at 7:43 am
Cardinality = 15089400 for Seek Keys
Actual number = 2454167 for seek key.
2.4 million concatenations.
Let us know the 3 tables row counts so that we have an idea, script out the schemas and let us have it.
How many rows are being passed as a parameter?
Going on the fact that you have indexes called index7, I am guessing this is a datawarehousey type of db?
How long is it taking?
How long do you expect?
Are you perhaps joining views? Clustered index scan on object10.Index7, object9.index5 etc.
The query didn't show these objects.
January 4, 2016 at 4:05 pm
My sample query I am using to test (for the attached query plan) takes around 30 seconds but it is based on a date range selection which has a date range selection of anywhere from 1 year to 1 month (I am testing with 1 year and the run time is 30 seconds). It would be very nice if I could get this 30 second query down to sub 10 seconds but I don't know if that is possible without major changes. What worries me is the extremely high cost of the table parameter operations. They're the highest cost operations in the whole query plan and it doesn't seem that they should be.
Also, yes, what is labeled as "Table" above is in fact a view that filters out one column on the main table. I updated the original post to include some of this information, which I did not include initially because I didn't want it to distract from the question of why the TVP operations had such a high cost.
January 5, 2016 at 2:56 am
phosplait (1/4/2016)
which I did not include initially because I didn't want it to distract from the question of why the TVP operations had such a high cost.
These types of issues are tough to figure out on our own systems where we have access to everything, so handicapping us here will not help.
Looking at this and the list of parameters in this as found in the attached plan,
select * from Object1(?,?,Variable2,Variable3,Variable4,Variable5,?,Variable6,
Variable7,Variable8,Variable9,Variable10,Variable11,Variable12,Variable13) where Column1 is not null option(recompile)
I cannot see how the TVF is related to the query from the plan attached as they have differing parameter definitions.
The TVF initially has a table as a parameter.
The query from the plan has a list of variables and not a table.
There are a total of 10 tables in the plan, but the attached TVF only has 3.
Handicapped.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply