Query takes too long to run - 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 2000 Forums

SQL Server Administration (2000)

Query takes too long to run

Author

Topic

jay1
Starting Member

17 Posts

Posted - 2010-09-17 : 09:46:00

I have the following query, which does an update statement but everytime I run it it took about 1hr and 20 mins to run so I created Indexes because that would solve the problem. After creating the Indexes I re-run the query but it was the same so I checked to see if it was blocked by running the following query and under the spid it had the same spid in under the blocked column, the lastwaittype is PAGEIOLATCH_SH.

UPDATE table2
SET table2.column1 = table1.column2
FROM table1 JOIN table2
ON table1.column= table2.column

Thank you

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-17 : 12:08:49

what index you added? on what column?

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

jay1
Starting Member

17 Posts

Posted - 2010-09-17 : 12:15:14

quote:
Originally posted by visakh16

what index you added? on what column?

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

Thank you for your reply I've added Non-Clustered INDEXES on the two joining columns.

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-17 : 12:31:35

does it have clustered index?

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

jay1
Starting Member

17 Posts

Posted - 2010-09-20 : 03:23:21

quote:
Originally posted by visakh16

does it have clustered index?

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

There are no Clustered Indexes.

Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-21 : 12:27:54

try creating one

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

Kristen
Test

22859 Posts

Posted - 2010-09-21 : 13:03:03

Any of the rows already have the correct value? If so add

WHERE table2.column1 <> table1.column2

or if either might be NULL then

WHERE (table2.column1 <> table1.column2 OR (table2.column1 IS NULL AND table1.column2 IS NOT NULL) OR (table2.column1 IS NOT NULL AND table1.column2 IS NULL))

Kristen
Test

22859 Posts

Posted - 2010-09-21 : 13:05:16

For varchar columns that are case and/or accent etc inseneitive and where the data might differ in Upper/Lower case / accent only then use:

table2.column1 <> table1.column2 COLLATE Latin1_General_BIN2

(or Latin1_General_BIN for older versions of SQL - I believe Latin1_General_BIN2 is slightly faster)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources