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.
| Author |
Topic |
|
kory27
Starting Member
35 Posts |
 Posted - 2008-02-24 : 12:00:23
|
| I have written this update query to update the productnavId field in a table called tbl product. it is updating from a table called fix_productnavid that was imported from a csv into my sql server 2005. the field properties are set to the same and neither field is a primary key. there are the same number of records in each table. the problem is the query keeps on running and running and never ends. i don't think it is circular. does anyone see anything wrong with this or have any ideas on what might be the problem with it?Update tblProductSet productNavId=fix_productnavid.productNavIdFrom fix_productNavId INNER JOIN tblProduct on fix_productNavid.ProductCode=tblProduct.productCodei didn't say this before but the product code is what they have in common and the select query pulls exactly what i want to pull. Thanks so much. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-02-24 : 12:03:30
|
| The query looks ok. Are you running this alone or is it among a query batch? |
 |
|
|
kory27
Starting Member
35 Posts |
Posted - 2008-02-24 : 12:04:50
|
| alone. it has been running for 31 minutes so far and there are 991000+records, but i think that is far too long. |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-02-24 : 12:15:26
|
Is it possible that there is a one to many relationship causing multiple updates per row? that would seriously slow down the update.If you have 991,000 records it should go pretty quick...but unless you have 991,000 UNIQUE records in the ProductNavID table, it is not practical to store the product code in each row...What does the execution plan show? are the product code columns indexed in both tables?
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
kory27
Starting Member
35 Posts |
Posted - 2008-02-24 : 12:22:33
|
| well, my box froze, so i don't know about the index question. while i restart it, should they be indexed? basically, i have 991K products, and they all need a productNavid which then is handled via a many to many in another query to push the categories of my navigation. but, each product needs one parent category which then serves to propagate the product throughout the navigation. i only have 1 navid per productcode being updated. |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-02-24 : 12:29:17
|
Yes, should be indexed--read books online regarding indexes. In this case, it sounds like the result should be built into (or act as) a primary key. If it is already the primary key, it is indexed automatically.
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
kory27
Starting Member
35 Posts |
Posted - 2008-02-25 : 15:17:24
|
| it ended up working after all, just took about 2.5 hours to run. |
|
|
|
|
|
|
|
|
is there something wrong with this query