|
|
Posted by Wes Groleau on 02/05/07 23:59
I STILL don't understand the server timeout.
Erland Sommarskog wrote:
> And if only the timeout bothers you, run the UPDATE from QA. QA does not
> have any timeouts.
Surprise! I went back to QA with your JOIN method. It would not accept
it, nor various modifications of it.
I ended up using more UPDATE queries similar to the ones I posted
before. The last two are particularly interesting. Each updated three
columns in Inv_Data. Each had the exact same structure except for the
WHERE clause. One came from table X, one from table Y (I forget the
exact names, but they don't matter).
ALL referenced columns in X and Y and Inv_Data are indexed in the same
manner (not clustered) except for the three fields being changed.
X and Y have the same number of rows and the fields used to select are
the same type and name. (And they have a small fraction of the number
of rows that did not cause a timeout in an earlier similar update)
The query that ended WHERE X.Seg_Nbr > Inv_Data.CAS_Seg completed
in under ten seconds.
The one that ended
WHERE X.Seg_Nbr BETWEEN Inv_Data.CLP_Seg AND Inv_Data.CAS_Seg
got a timeout from the server after about a minute.
(So perhaps QA doesn't timeout, but the server still does!)
The "estimated execution plans" were the same.
Changing the BETWEEN to the equivalent < CAS and > CLP didn't help.
Exiting QA and EM, defragmenting the drive, logging out and back in
and opening only QA -- didn't help.
I am now trying to do the work one input file at a time and then
insert the end result in the desired output. This way X and Y will
have 800-1500 rows instead of over 400K (but 16 MILLION did not get
a timeout!)
Curiouser and curiouser....
--
Wes Groleau
Heroes, Heritage, and History
http://freepages.genealogy.rootsweb.com/~wgroleau/
Navigation:
[Reply to this message]
|