Posted by Erland Sommarskog on 02/03/07 11:03

Wes Groleau (groleau+news@freeshell.org) writes:
> No triggers. I tried to script the table (actually I tried to script
> a similar table to save myself some typing) but the wizard saved no file
> and gave no error message. So I copied the table and used the GUI to
> strip out the fields I didn't need/add a few others
>
> All the fields referenced in the UPDATE statements are indexed.
>
> The update statements are almost identical--the difference is that in
>
> UPDATE Output SET xyz = (SELECT Max(Seg_Nbr) FROM Raw_Segs
> WHERE Output.FID = Raw_Segs AND Seg_Nbr BETWEEN abc and pqr)
>
> xyz, abc, & pqr are different columns, such that pqr - abc is
> a wider range in the one that works; narrower in the one that
> dies with timeout.

Again, CREATE TABLE and CREATE INDEX statements for your two tables
would help. Knowing that "all fields ... are indexed" is not a very
useful piece of information. I would need to know where in the index
the column appears, and which index that is the clustered index, if
there is any.

But you could try:

UPDATE Output
SET xyz = R.maxseg
FROM Output o
JOIN (SELECT Raw_segs, maxseg = Max(Seg_Nbr)
FROM Raw_Segs
WHERE Seg_Nbr BETWEEN abc AND pqr) R
ON Output.FID = R.Raw_Segs

While this syntax is proprietary and not portable, it often yields better
results than a correlated subquery.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]