Update query 1

[ianicr]

I have this query:

update may9 set may9.URN = urns.URN
from urns
where may9.OSISID = urns.OSISID

Is this the most efficient way to run this query? The may9 table contains 21,000,000 records and the urns contains 24,000,000 records. Also what would be the best way to index the files?
create index osisid on may9 (OSISID)?

Thanks for any help

 

[skuhlman]

make sure that the OSISID field on each table is indexed

update may9 set may9.URN = urns.URN
from may9 join urns on may9.OSISID = urns.OSISID

 

[ianicr]

Will that join speed it up? Thanx

 

[skuhlman]

I believe that the combination of the join and the indexes will make it as fast as an update statement can get.

Another aproach wich is faster is to use a select statement which joins the two tables together and produces a result set in a new table (may9new) that has the same structure as the may9 table. Then destroy the may9 table and rename the man9new table to may9. Then you'd need to recreate any indexes and constraints that the may9 table may have had.

This works faster because the update statement will log each of the 21,000,000 updates of the may9 table if UPDATE is used. When "SELECT... INTO... FROM may9 JOIN urns..." is used to create a new table, the transactions are not logged.

The down side to this is having to remove and recreate any constraints and indexes on the may9 table. Also, if users are using the may9 table while the process is running, the new table could end up missing any changes that occured in the original may9 table. On the other hand, using UPDATE would LOCK the table for a LONG time so the users couldn't make changes anyway.

Pluses and Minuses each way. Your solution may depend upon your environment. Good luck.

 

[ianicr]

As I'm the only person here who uses that database it doesn't really matter about locks and people editing the wrong table. I'm still quite new at this so and I'm not really sure how joins work... Could you give me a better clue on the select into query?

Thanks

 

[skuhlman]

I don't know what all your fields are named, but here's an example...

select may9.OSISID,
      may9.field1, 
      may9.field2,
      may9.field3,
      ...
      case when urns.OSISID is null
            then may9.URN else urns.URN end as URN,
      may9.fieldx,
      may9.fieldy,
      may9.fieldz
   into MAY9NEW
   from may9 LEFT JOIN urns on may9.OSISID = urns.OSISID

Having all of your fields specified in the same order as they are in the may9 table will give the MAY9NEW table the same appearance. The case statement for the URN field will insure that if no corresponding record exists in the urns table, the current value will be left alone. The LEFT JOIN will insure that every record from the may9 table gets a corresponding record in the MAY9NEW table.

All of this works ASSUMING that the OSISID field in the urns table contains distinct values (no duplicates).

 

[ianicr]

Thanks mate. Thats loads quicker. Have a star!

 

[skuhlman]

Thanks

And you also don't have to worry about filling up your log and expanding the size of your database as much.