Function quick...where clause not :(

Question

Post reply

Function quick...where clause not :(

Vets5

SSC Eights!

Points: 982
More actions
August 15, 2017 at 2:00 am

#339316

Hi,
I have a function which returns a table really quickly but when I add a where clause after it it takes a long time and causes the SSIS tasks to timeout.
The base table it is pulling from is approx 1.3 million rows, the function restricts this to about when run on its own returns about 400 rows in less than one second. I think want to use a where clause to split this based on one column which is either 0 or 1. Once i add this time goes up to approx. 3.5 to 4 minutes.
Example:
Select * from dbo.fnGetData
370 rows in under 1 second
Select * from dbo.fnGetData WHERE IsTrue=1
365 row in 4 minutes
I have tried a temp table and its back down to 1 second but I'm keen to understand the what is going on the above. Ideally I would amend my code at this level to avoid making changes in the SSIS packages
Many thanks,
Steve

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

What type of function? If multi-statement, then not surprising. Those things are performance nightmares.

Going to have to see the function and preferably the execution plan to say anything useful.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 2

Vets5 - Tuesday, August 15, 2017 2:00 AM
Hi,
I have a function which returns a table really quickly but when I add a where clause after it it takes a long time and causes the SSIS tasks to timeout.
The base table it is pulling from is approx 1.3 million rows, the function restricts this to about when run on its own returns about 400 rows in less than one second. I think want to use a where clause to split this based on one column which is either 0 or 1. Once i add this time goes up to approx. 3.5 to 4 minutes.
Example:
Select * from dbo.fnGetData
370 rows in under 1 second
Select * from dbo.fnGetData WHERE IsTrue=1
365 row in 4 minutes
I have tried a temp table and its back down to 1 second but I'm keen to understand the what is going on the above. Ideally I would amend my code at this level to avoid making changes in the SSIS packages
Many thanks,
Steve

So what did you figure out, Steve?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)