i have a query that takes forever. it is a join between two tables on two columns. each table has an index using both of those columns. one of the tables has 139 million records, and the other has 145 million. the first table has many records where the indexed columns are the same, the second one does not.
the query updates one field in one of the tables from a field in the other table. the two tables are on different databases.
any suggestions on how to speed this up?
i'm running sql server 2008 on a dual quad core server running server 2008. each database's data file and log file are on different physical drives. one database has the mdf and ldf on separate SAS 15K drives. the other has the MDF on a SATA 2 drive, and the log file on one of the SAS drives.