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 |
|
rinku_h
Starting Member
2 Posts |
 Posted - 2004-01-02 : 06:05:12
|
| I Have Two tables one table have 4 million records and second table have 2000 records i want to update 4 million records with join 2000 records. and i want best performacne for this as i try this query but it will take very big time.Here is my Script. UPDATE Des SET Col1 = Des.Col1 FROM Des join Sou on Des.Col2 = Sou.Col2 and Des.Col3 = Sou.Col3 Jatin Purohit |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2004-01-02 : 06:35:06
|
| That update won't do anything useful....because (as written) it'll update col1 to col1....ie no change....however that's a minor problem and solely due to the way you have described your issue.you have a couple of things to look at.1. FROM Des join Sou on should be written as INNER JOIN to make it dead obvious what style of join is being invoked.2. write the query as a 'SELECT'...show the execution plan....and see if there are any table/index scans....if so, you could do with some indices (and updating of statistics) on des.col2 and des.col3....'presuming des is the larger table'....it may also be useful to put an index on sou for it's version of col2,col3 as well....once the 'execution plan' shows up 'improved/as good as it can get'....then run the update. |
 |
|
|
rinku_h
Starting Member
2 Posts |
Posted - 2004-01-05 : 04:42:47
|
| I Put Inner in this Query but this not improve performance.Even when i check select and see execution plan it will take 65% to 70% time in Scan Des table which have 4 million records in.And also i check the Index any index not at all.So please suggesh me how can i Impliment the Update Statistics in this?Jatin Purohit |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2004-01-05 : 10:11:01
|
| What do you mean by "any index not at all"?Is there a) no index on this table b) at leat one index on this table, BUT NOT one that includes one (or all) of the columns mentioned in the join..ie col2 or col3 c) now an index on table...but statistics have not yet been run.To update the statistics...look up BOL for examples....or do a search here...there surely is an example in this site. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-01-05 : 11:19:46
|
| Have we forgotten that it's updating it to itself?Brett8-) |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2004-01-05 : 11:22:39
|
| Updating statistics is important but probably won't give you much benefit without there being an index on the table that applies to your statement. But before you just slap a CREATE INDEX statement on a 4,000,000 row table, you should consult with your DBA (you DO have an experienced DBA for a database with 4,000,000 rows in it, don't you?) Indices are great things, but they take some time to implement and if done haphazardly can actually cause you performance trouble instead of gains.--------------------------------------------------------------Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url] |
|
|
|
|
|
|
UPDATE QUERY PERFORMANCE