July 19, 2011 at 6:52 am
I have a table valued function which takes a date as a parameter. In that function, I am selecting from a table that joins on a few other tables, and uses the date in its where clause (there are no other conditions in the where clause).
If I run this:
[font="Courier New"]
declare @date datetime
set @date = CURRENT_TIMESTAMP
select * from fnMyFunction (@date)[/font]
It returns 145 000 + rows in 11 seconds
BUT, If I run this (which is the same thing, just done a different way)
[font="Courier New"]
select * from fnMyFunction (CURRENT_TIMESTAMP)[/font]
It ran for 20 minutes, and I eventually stopped it.
Any ideas?
July 19, 2011 at 7:18 am
Did you see any difference in the actual execution plans?
-- Gianluca Sartori
July 19, 2011 at 7:19 am
You should be very careful when using Table valued functions. It can cause huge performance problems. What does the function look like? How often is it used? Can they query be rewritten to not use the function?
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/
July 19, 2011 at 7:45 am
Mike01 (7/19/2011)
You should be very careful when using Table valued functions. It can cause huge performance problems. What does the function look like? How often is it used? Can they query be rewritten to not use the function?
Used properly, table valued functions can give an important performance boost. I would not blame the function until there's some kind of evidence in the execution plan.
-- Gianluca Sartori
July 19, 2011 at 7:50 am
I tried reproducing the behaviour you describe, but I'm getting the same query plans for both statements.
-- SETUP
-- CREATE TABLE
CREATE TABLE testTable (
Id int identity(1,1),
dateColumn datetime
)
-- POPULATE TABLE
INSERT INTO testTable (dateColumn)
SELECT DATEADD(second, ABS(CHECKSUM(NEWID())), 0)
FROM master.sys.all_columns
GO 10
-- CREATE FUNCTION
CREATE FUNCTION testFunction(@dt datetime)
RETURNS TABLE
AS RETURN (
SELECT *
FROM testTable
WHERE dateColumn > @dt
)
GO
-- SELECT WITH FUNCTION: Table scan with predicate "dateColumn > GETDATE()"
SELECT *
FROM testFunction(CURRENT_TIMESTAMP)
-- SELECT WITH VARIABLE
DECLARE @dt datetime
SET @dt = CURRENT_TIMESTAMP
-- Table scan with predicate "dateColumn > @dt"
SELECT *
FROM testFunction(@dt)
Can you post your actual code?
-- Gianluca Sartori
July 19, 2011 at 7:59 am
I agree, we need to see the DDL for the function. In addition we also need the DDL of the tables used on the function.
July 19, 2011 at 8:33 am
Here is the general structure of my function:
ALTER FUNCTION [dbo].[fnInventory] (@ClosingDate DateTime )
RETURNS TABLE
AS
RETURN
(
SELECT
*
FROM
InventoryCostUnit WITH (NOLOCK)
LEFT JOIN
(
SELECT *
FROM BIMovement
WHERE TransactionDateTime > @ClosingDate
GROUP BY BIMovement.StoreID, BIMovement.InventoryCostUnitID
) AS MovementAfter ON Store.StoreID = MovementAfter.StoreID AND InventoryCostUnit.InventoryCostUnitID = MovementAfter.InventoryCostUnitID
)
July 19, 2011 at 9:58 am
I don't see anything evil here (except the NOLOCK hint: seriously, get rid of that thing).
Can you post the estimated execution plan for both versions?
Also, can you post DDL for the tables accessed by the function? What datatype is TransactionDateTime? Maybe an implicit conversion taking place?
-- Gianluca Sartori
July 19, 2011 at 10:09 am
Besides the NOLOCK hint, my concern is the SELECT * FROM ... GROUP BY ... in the derived table.
It would help if you would provide the actual code for the function, the ddl for the tables used by the function, sample data for the tables, and the expected results based on the sample data.
Also, if you provide execution plans they should be the actual, not estimated, plans.
July 19, 2011 at 2:57 pm
Lynn Pettis (7/19/2011)
Besides the NOLOCK hint, my concern is the SELECT * FROM ... GROUP BY ... in the derived table.
Nice catch, Lynn. I suppose this is due to some simplification to the actual code. It would never run as is.
Also, if you provide execution plans they should be the actual, not estimated, plans.
Agreed. If you're unable to post the actual plans because the queries take too long to execute, I think we can gather some information from the estimated plans as well.
-- Gianluca Sartori
July 20, 2011 at 12:17 am
I also recall you mentioning HIPPA, so I want to make sure you understand that when we ask for sample data we aren't looking for real data but made up data that is representative of your problem domain. We also only need enough data to understand the problem and the expected results. This should be no more than 10 to 20 records in each table and should include data that will be selected as well as not selected when the query is run with a given set of parameters.
If you take the time to provide this info in a readily consumable format, you will get much better answers plus tested code in return.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply