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/

[Back to original message]