|
|
Posted by Erland Sommarskog on 02/02/07 23:33
Wes Groleau (groleau+news@freeshell.org) writes:
> Working with this through the Enterprise Manager MMC,
> a lot of things I tried got timeouts.
>
> So, I indexed File ID, Line number, and line type, and
> created a new table containing only the columns I knew
> I would need in the final output--selected fields from
> some of the line types mentioned, plus the line numbers
> and common file ID for those rows.
>
> I indexed every column in that table that I thought I might
> search on.
>
> I loaded it with 31 thousand rows using a select on a
> subset of the CAS rows. That took far less than a minute.
>
> I updated each row with the highest BPR line number not higher
> than the CASE line number. About a minute. Not bad, with having
> the worst case number of comparisons being 16 million times 31 thousand.
> Of course, the indexing should help plus it can be narrowed down by
> the "same file" and BPR # < CAS # criteria.
>
> But the next update should theoretically be faster: each row now has a
> BPR # and a CAS # and I am telling it to find the highest CLP number
> BETWEEN those two. So it should have a MUCH smaller set of to search
> through. Yet it thinks for about five minutes and then announces a
> timeout.
Unforunately there is very little here to work from. X12 tells me
nothing. And in any case you have added a number of indexes that are
unknown to me. But let me point out thing: indexing single columns is
far from always sufficient. Often you need composite indexes.
To be able to say something more useful, I would be able to see
the CREATE TABLE statements for the tables. (Or is there only one?),
as well as the indexes, including keys. And of course I would need
to know your UPDATE statements. And if there are any triggers, I
need to see those as well.
--
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
Navigation:
[Reply to this message]
|