query that take long time - 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)

query that take long time

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-02-24 : 02:22:06

i have 3 queries:

sum is numeric
num is decimal

1.
UPDATE Table_A
SET sum=sum*num
FROM Table_A a JOIN TABLE_B b
on a.date=b.date and a.currency=b.currency
WHERE company='COM'

2.
UPDATE Table_A
SET coin=sum/num
FROM Table_A a JOIN TABLE_B b
on a.date=b.date and a.currency='USD'
WHERE company='COM'

3.
UPDATE Table_A
SET coin=sum/num
FROM Table_A a JOIN TABLE_B b
on a.date=b.date and a.currency='EURO'
WHERE company='COM'

the problem is that qury take so long time i mean:
every day it take a 3 minutes (i have 850,000 rows)
in weekend it takes a 4 hours (i have 2,500,000 rows)

where is the problem?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-02-24 : 04:25:31

It is just a suspicion, but try to use the ALIAS like this:

UPDATE a
SET sum=sum*num
FROM Table_A a JOIN TABLE_B b
on a.date=b.date and a.currency=b.currency
WHERE company='COM'

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-24 : 10:17:44

did you had a look at execution plan for query?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources