July 20, 2012 at 8:56 am
Dear All,
We have 3,00,000 rows in a table. This table has a Primary Key named In_Id.
To update this table 10 columns, we have separate-separate 10 scalar functions with different-different definitions. In these functions we have to pass Primary Key of this table & get the output. It works fine when we work with small-small bunch of rows. But, when we try to update these 10 columns for all 3,00,000 rows then it takes 45-60 minutes to update the table.
The frequency of updating these 3,00,000 rows is depends on user requirement, means as & when user generates the report then before showing the results this updation process needs to execute.
How do we cut down this time?
July 20, 2012 at 9:01 am
The problem looks to be the scalar functions. Unfortunately, we can't see what you see, so all we can give you is a shot in the dark.
I would look at rewriting your scalar functions as inline Table Valued Functions (iTVFs) and rewrite the update procedure to use those using CROSS APPLY.
July 20, 2012 at 10:13 pm
Scalar-valued User-defined Functions (sUDF) in SQL Server are known to cause performance problems, especially when they are used in a way in which it sounds like they are being used in the process you're describing. Here is some reading for you, to help you understand why I am saying this. The last article shows you a way to refactor your sUDFs into Inline Table-valued Functions (iTVFs) and reclaim some of the performance losses.
Do Scalar UDFs give SQL Server a Bad Name? By Tony Davis[/url]
Inline Scalar Functions by Itzik Ben-Gan
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 21, 2012 at 11:11 am
As mentioned functions are a bad idea. Code reuse in SQL Server doesn't work very well.
If you show some code and DDL, we may be able to help rewrite it.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply