Update to slow - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Update to slow

Author

Topic

derach2000
Starting Member

37 Posts

Posted - 2010-08-02 : 10:29:01

Hi,

i have this update statement

UPDATE a SET column=(select top 1 v.column from TABLE2 v where dbo.FUNCTION (a.COLUMN2,v.Column2)<=(v.column3))
from TABLE1 a

I have 100 000 rows. When the innes sql returns a value for every row, the update last for 19s.
If the result is NULL it takes abou 4m 30s for the update statement to complete.

???

any ideas

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-02 : 11:00:18

do you want to update the NULL values as well?
obviously 4m30s it the time to update NULL values with NULL values.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-03 : 02:04:00

[code]UPDATE a
SET a.column=
from TABLE1 a
CROSS APPLY
(select top 1 column from TABLE2 where dbo.FUNCTION (a.COLUMN2,Column2)<=column3 ORDER BY PK)v
[/code]
where PK is primary key of TABLE2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

derach2000
Starting Member

37 Posts

Posted - 2010-08-03 : 02:54:18

Hi,

The code you posted did not really help me at all. It's even slower, much slower then the one I posted earlier.

The thing is, I don't understand, why NULL values slow down the processing.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-03 : 03:00:22

what does FUNCTIOn do?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

derach2000
Starting Member

37 Posts

Posted - 2010-08-03 : 03:21:30

The function just calculates the difference between two values in the system.
So, i know that the function can't be the issue here, it's a small function that returns values for any given parameters quickly.
I use it as a part of a filter for the select of top 1.

The main problem is, when the inner select has a value to return, for each row, the update is very quick.
When the inner select returns NULL, becouse no values satisfy the filter, the update last 10 times more.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-03 : 03:33:51

then why dont you use filter where value IS NOT NULL?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

derach2000
Starting Member

37 Posts

Posted - 2010-08-03 : 03:53:33

the select =(select top 1 v.column from TABLE2 v where dbo.FUNCTION (a.COLUMN2,v.Column2)<=(v.column3))

returns null, so the only way to use Is Not NUll with this select

where is not null (select top 1 v.column from TABLE2 v where dbo.FUNCTION (a.COLUMN2,v.Column2)<=(v.column3))

i can try it, but....

derach2000
Starting Member

37 Posts

Posted - 2010-08-03 : 08:05:56

Hi,

I want to tell that I have finnaly found the solution.
I have deleted the primary key on the second table and now it works fine.
Here are the images http://buuuhahaha.blogspot.com/

I dont understand it, but it works...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources