UPDATE comparing to tables and get error
-
I have this problem:
I perform an update using an INNER JOIN to check data to be update.
I have used INNER JOIN, that is between 2 identical tables (T1Temp and T1), no any difference.
have a look:
UPDATE DB1.dbo.t1 SET col1=temp.col1, col2=temp.col2,...coln=temp.coln
FROM tempDB.dbo.T1Temp temp
INNER JOIN
DB1.dbo.T1 perm
ON temp.col1 = perm.col1
The result is an error:
Server: Msg 1540, Level 16, State 1, Line 1
Cannot sort a row of size xxxx, which is greater than the allowable maximum of 8094.
So I added: OPTION (ROBUST PLAN)
and this is useful: the quey is successful.
My questions:
1) why I have the error (without using the option) ? (When tables are created I have no problem of size!!!)
2) Is there any other solutions to make update comparing 2 identical tables avoid using INNER JOIN and avoid using OPTION (ROBUST PLAN)? later, OPTION (ROBUST PLAN) may be result unusable (size to big)!!
Thank
-
I have made onother test and I have found out that the OPTION (ROBUST PLAN) is OK for tables with only some record, I have tested onother update on a table with more 150000 rows :
UPDATE without OPTION (ROBUST PLAN): 52 sec
UPDATE with OPTION (ROBUST PLAN): after 10 mins still running. (may ne stuck, I have stopped running the query..)
Any suggestion on how to make UPDATE in a different manner or resolve performance problem with OPTION (ROBUST PLAN)?
Thank
-
That many columns in one table means that you missed to read or/and understand some basic educational things about relational databases.
Not sure any "ROBUST PLAN" can help you with this.
Sorry.
_____________
Code for TallyGenerator -
I have NOT created the Database, I am building some store procedures in tsql to work ON this database, and of course I cannot change anythings (as just written:it is not mine).
I have not set a thread to know If I have understood or read about relational database, I have, only, written to have some helps.
So if anyone can help me, everything will be very apprciated.
Thank
-
You may:
1) get it changed;
2) get out of that job (project)
You to choose.
I would not even touch such c..p.
There is no way to succeed using this kind of database design.
_____________
Code for TallyGenerator -
Why would u update a column thats used in the JOIN?
UPDATE DB1.dbo.t1 SET col1=temp.col1, ....
INNER JOIN
DB1.dbo.T1 perm
ON temp.col1 = perm.col1
Like Sergiy said "Understand some basic educational things about relational databases".Please Stop Complaining about how things were handed to you.
Thanks
Sreejith
-
Sorry,
but I do not understand why you are so hostile with me, I asked only some suggestion and how to workaround the problem, because I am sure there is lots of people prepared and can exchange useful information.
Anyway, avoid complaining because this is not my job, I have to try a solution, this is what me asked; so if you do not want to try to understand what I mean, leave the thread to others that may be are interesting in this topics.
About the UPDATE statement I have always used for years and so this not the first time, I have MCDBA on sql server 2000 certification (4 years ago) + MCSE in 2000 Server and MCSE in server 2003.
I like to keep informed in new technologies and I like read and study and as you know to take certications I had studied a lot.
Reading the code again, yes there is a mistake:The problem is that sometimes the code above was written very fast on the topics, is not from my original code, and with the copy and past from the first row, an error may occur? I think yes...
the last row is comparing string: the pk value of each table, and update all the cols if different, of course not the pk.
I hope everything is OK, the only thing is that it is better avoid give sentences before knowing people.
I always give my help in other topics, keep in mind that sometimes is not easy to explain the problem, and sometimes is not easy to give the right answer.
We are humans not machine.
Thank foy your understanding
-
Do you have to UPDATE? Can you TRUNCATE DB1.dbo.t1 and re-populate it with the tempDB.dbo.T1Temp data, (although I'm not sure why this is coming from tempDB)?
I wasn't born stupid - I had to study.
-
No one is hostile with you.
If you have been asked to carry bunch of logs with your Porche you better don't take the challange. Because you gonna lose your Porsche and not succeed with the task.
Or you may try to convince your customer to change the carrying tool and use proper log truck instead of your precious Porsche.
It's your choice which way to go.
The fact you don't understand the situation you are in just shows what those MS certifications really worth.
As my friend likes to say (very proudly): "I'm a Microsoft certified idiot!"
_____________
Code for TallyGenerator -
...may be your friend cannot read and write, this is the reason
Thank Farrel,
I ask some suggestion just to validate the problem that occur.
the problem is transfer data from one DB to another, DBs are the same, after a process like a snapshot (i am not using replication) I have always update data in the destination DB; Replication is never used and implemented. Is not my decision (just to be claer, otherwise there some teachers around the world that say sentence before asking and understand the real problem)
So I cannot delete the table, (just to carry update data, if delete everytime, performance decrease and time increase, but may be AN EASY solution: if this is the only one !). Tables are more than 1, but, I am like, there is only one that get this error, the other table are inserted and update without problem. For this fucking table, the cols are not too many, and datatype are not too large (in the case of varchar).
But, I repeat, deleting in this case may be a SOLUTION (for everybody that reads, so there is a solution for everything!!!!!!! for someone is very strange !!!!!!!), especially if table do not increase to much time by time, deleting and inserting may be better than insert and update (update is very expensive and time consuming).
Thank for your contribution
-
net,
As to why you are getting the error:
Are the rows in your table more than 4,047 bytes wide? If they are the combination of the two rows in the cartesian product of the join will be more than the maximum of 8,094 as was stated in the error message.
BTW, you don't need to update perm.col1 with the temp.col1 value. Since col1 is your join column, and you are doing an equal join, the two columns will already have the same value.
This is what I found in the Query Analyzer Books On Line when I looked up "ROBUST PLAN". Notice the phrases, "possibly at the expense of performance", and "When the query is processed, intermediate tables and operators may need to store and process rows that are wider than any of the input rows. The rows may be so wide that, in some cases, the particular operator cannot process the row".
ROBUST PLAN
Forces the query optimizer to attempt a plan that works for the maximum potential row size, possibly at the expense of performance. When the query is processed, intermediate tables and operators may need to store and process rows that are wider than any of the input rows. The rows may be so wide that, in some cases, the particular operator cannot process the row. If this happens, SQL Server produces an error during query execution. By using ROBUST PLAN, you instruct the query optimizer not to consider any query plans that may encounter this problem.
As to how you can do the update without joining those two tables:
You can create a temporary table of only col1 values from TempDB.dbo.T1Temp; then delete the col1 values in your new temporary table that do not match col1 values in DB1.dbo.T1; then delete the rows from DB1.dbo.T1 where col1 value matches the col1 value in your temporary table; then copy TempDB.dbo.T1Temp rows into DB1.dbo.T1 where the TempDB.dbo.T1Temp col1 values match the col1 values in the new temporary table.
-
Thank Edwin for your help,
As said, the code on my test environment have right SQL code: col1 is not update, is only my mistake during copy and past in the topic and I forgot to change the name of pk cols.
I will try your suggestion that is interesting for me, especially for others job that I am building.
In the previous reply, I appreciated the idea to delete the table; this is not to big, and do not increase too much by time, so deleting and inserting at the moment is the best for me.
I have tried this solution and at the moment everything works great, without problem; also time econsuming is not too much and better than using update for that table.
Thank everybody for your help
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply