May 17, 2021 at 4:31 pm
Hi,
There is a select query which is running very slow.
Select
customerAcc_ID,
Balance,
Additiondate,
FROM
dbo.fnGet_NAME] (@processDate) -- This function is hitting a very big table and I do see some missing IDX hints, however, apart from IDX's I wanted to find out more on this.
Here is my questions, is using table valued function after FROM a bad for performance? is it the same as using fn in the where clause?
Also, If I have to rewrite this query, what could be a better way, please advise.
Thanks!
May 17, 2021 at 5:08 pm
The execution plan of the above select is giving a table scan and also missing idx hints, is it due to the fact that the function is not using an existing index on the customerAcc_Id col?
May 17, 2021 at 5:34 pm
Without CREATE TABLE and CREATE INDEX scripts and the actual execution plan, there's not much anybody here will be able to suggest, I don't think.
May 17, 2021 at 5:43 pm
To add to what Pietlinden said, we also have no visibility into that function. You MAY be able to tune the function.
As far as I know, using a table valued function is not bad for performance as long as the table valued function is properly tuned.
With your query too, it LOOKS like it is part of a stored procedure. If this is correct, you may be having a parameter sniffing problem and you MAY benefit from adding the query hint "OPTIMIZE FOR UNKNOWN" or possibly optimizing it for a known value for your parameter.
As for a "better" way to write that, it is impossible to say without having an execution plan to go off of as well as the DDL. As far as we know, your table valued function could have a WAITFOR as the first line in it (note - I am not 100% sure you can do that, just throwing it out there).
You also did not attach the execution plan.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 17, 2021 at 6:26 pm
FROM
dbo.fnGet_NAME] (@processDate) -- This function is hitting a very big table and I do see some missing IDX hints, however, apart from IDX's I wanted to find out more on this.
Here is my questions, is using table valued function after FROM a bad for performance?
It can be, especially if the index is a multi-line function rather than inline. If you'd post the function, perhaps we could convert it from multi-line into inline.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
May 17, 2021 at 9:34 pm
A "band-aid" for complex multiline table-valued functions is to use them only to write the data out to a #temp table, which you can then use for subsequent queries with appropriate indexing. However, I would join the chorus encouraging you to use inline table-valued functions whenever possible.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
May 19, 2021 at 11:42 am
Multi-Statement, Table Valued, User Defined Functions have a very useful acronym that is worth remembering:
EVIL
Yes, I know it doesn't match MSTVUDF. Doesn't mean I'm wrong.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 19, 2021 at 4:54 pm
The most obvious thing you can do is to add the missing indexes to see what difference they make.
I can't see why doing a SELECT * FROM dbo.MyMultilinedTableValuedFunction(@Parm) could be much worse than just running the query that is inside the function.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply