October 28, 2009 at 7:55 am
I have an inline table defined functions that returns 23000 rows of data in 1 second, only one column and I pass a datetime to the function as parameter.
I need to make a left join with the same function but passing another date. (also returns almost 22000 rows, one column in 1 second)
When i make a join then result comes inmediatly but when i make a left join it last like 3 minutes. How can it be????
Any ideas?
for example:
select item as item1 from funcItemsdate('20091023') as li
left join
(select item as item2 from funcItemsdate('20091020')) as lf
on li.item = lf.item2
October 28, 2009 at 8:01 am
Hi
Some questions:
* Do you pass a parameter of your first table/function to your left-joined function?
* Do you need the sub-query? If yes, did you try to move it into a CTE?
* Did you have a look to the execution plan?
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
October 28, 2009 at 8:26 am
It's interesting. I'm getting the same type of results. When I put the results into a temp table, then the query return immediately.
select item as item1 into #temp1 from funcItemsdate('20091023')
select item as item2 into #temp2 from funcItemsdate('20091020')
select item1
from #temp1 as li
left join
#temp2 as lf
on li.item1 = lf.item2
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 28, 2009 at 11:30 am
* No, i don´t pass a value from the function of the left side of the join to the function on the right side. Is the same table function executed twice, and i type the parameter value for each other.
* I tried without the subquery and is the same
* I looked the plan, it recommended an index, i´ve created and get the same result.
I tried to store the two results sets in temporal tables and joining them and goes much faster.
I don´t understand. it´s like the engine it´s getting inside the function in stead of crossing the two results sets. I don´t know.
October 28, 2009 at 2:10 pm
Do you have multi-line table valued functions?
For multi-line table valued functions the optimizer always estimates the number of rows returned as 1 - it is better at estimating the amount of rows for an inline table valued function.
And if the data is put into temp tables and those are used in joins then statistics are generated for temp tables which translate to better estimates in the joins.
Could you attach the execution plans for the query using the table valued functions? That might help in figuring out the cause of the issue.
October 28, 2009 at 2:22 pm
I wanted to recheck whether the estimated number of rows returned by a multi line table valued function is really always 1 - here is the sample code attached (since I wrote it and have nothing better to do with the code I'm putting it here :-P)
use tempdb;
GO
/*
-- object creation part
IF OBJECT_ID('dbo.TVFTestTable') IS NOT NULL
DROP TABLE dbo.TVFTestTable;
GO
CREATE TABLE dbo.TVFTestTable(ID int, SomeValue int);
GO
-- insert data for two sample IDs - skew data to around 20000 rows for each ID
INSERT dbo.TVFTestTable(ID,SomeValue)
SELECT 100, T1.column_id FROM
(SELECT TOP 200 column_id FROM master.sys.columns) AS T1 CROSS JOIN (SELECT TOP 100 column_id FROM master.sys.all_columns) T2
UNION ALL
SELECT 200, T1.column_id FROM
(SELECT TOP 200 column_id FROM master.sys.columns) AS T1 CROSS JOIN (SELECT TOP 100 column_id FROM master.sys.all_columns) T2
GO
IF OBJECT_ID('dbo.TVFTestInline') IS NOT NULL
DROP FUNCTION dbo.TVFTestInline;
GO
CREATE FUNCTION dbo.TVFTestInline(@ID int)
RETURNS TABLE
AS
RETURN
(
SELECT ID,SomeValue FROM dbo.TVFTestTable WHERE ID = @ID
)
GO
IF OBJECT_ID('dbo.TVFTestMultiLine') IS NOT NULL
DROP FUNCTION dbo.TVFTestMultiLine;
GO
CREATE FUNCTION dbo.TVFTestMultiLine(@ID int)
RETURNS @RetTab TABLE(ID int, SomeValue int)
AS
BEGIN
INSERT @RetTab(ID,SomeValue)
SELECT ID,SomeValue FROM dbo.TVFTestTable WHERE ID = @ID;
RETURN;
END
GO
*/
/*
-- testing out part
-- turn on show execution plan for these queries
--SELECT * FROM dbo.TVFTestInline(100);
--SELECT * FROM dbo.TVFTestInline(200);
-- inline TVF - estimated rows match actual rows (provided statistics up to date?)
SELECT T1.ID,T1.SomeValue,T2.ID,T2.SomeValue FROM
dbo.TVFTestInline(100) As T1 LEFT OUTER JOIN dbo.TVFTestInline(200) As T2
ON T1.ID = T2.ID
--SELECT * FROM dbo.TVFTestMultiLine(100);
--SELECT * FROM dbo.TVFTestMultiLine(200);
-- multiline TVF - estimated rows always 1 - query much slower
-- also look at the estimated rows v/s actual rows count
SELECT T1.ID,T1.SomeValue,T2.ID,T2.SomeValue FROM
dbo.TVFTestMultiLine(100) As T1 LEFT OUTER JOIN dbo.TVFTestMultiLine(200) As T2
ON T1.ID = T2.ID
-- now insert the data into temp tables and check the execution plans
SELECT ID,SomeValue INTO #TempTable1 FROM dbo.TVFTestMultiLine(100)
SELECT ID,SomeValue INTO #TempTable2 FROM dbo.TVFTestMultiLine(200)
-- temp tables - have statistics - estimated rows match actual rows
SELECT T1.ID,T1.SomeValue,T2.ID,T2.SomeValue FROM
#TempTable1 As T1 LEFT OUTER JOIN #TempTable2 As T2
ON T1.ID = T2.ID
DROP TABLE #TempTable1;
DROP TABLE #TempTable2;
*/
October 30, 2009 at 7:16 am
It is an inline table funtion. In side the function there is an "union all" of two selects.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply