hyper threading: I don't understand this "server timeout"

I was doing update statements in SQL Server 2000.

I have a table with over 16 million rows.

It came from several hundred delimited text files,
and two of the columns are file ID (int) and Line # (int)

Structure is X12 (835). For those unfamiliar with that,
each file has one to many BPR lines; each BPR line has
zero to many CLP lines, each of those has zero to many
SVC lines, each of those has zero to many CAS lines.

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.

Any suggestions?

--
Wes Groleau

Measure with a micrometer, mark with chalk, and cut with an axe.Wes Groleau (groleau+news@.freeshell.org) writes:

Quote:

Originally Posted by

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.

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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:

Quote:

Originally Posted by

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.

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.

--
Wes Groleau

A pessimist says the glass is half empty.

An optimist says the glass is half full.

An engineer says somebody made the glass
twice as big as it needed to be.|||Erland Sommarskog wrote:

Quote:

Originally Posted by

Sorry for the too-soon send.

As I said, I did the table design with the GUI but it would be equivalent to
( CAS_Seg int,
SVC_Seg int,
CLP_Seg int,
BPR_Seg int,
other fields )

CAS_Seg is loaded first, with an INSERT from a view of Raw_Segs.

Then BPR_Seg is updated with the highest Seg_Nbr lass than CAS_Seg in
the same file. Works.

Then one of the updates I just sent is tried and times out.

--
Wes Groleau
--
"The reason most women would rather have beauty than brains is
they know that most men can see better than they can think."
-- James Dobson|||Wes Groleau (groleau+news@.freeshell.org) writes:

Quote:

Originally Posted by

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.

Quote:

Originally Posted by

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.

I think I also tried that, but maybe not. I'm going in to work today,
so I'll make sure. And I'll make another try at extracting a script
from that thing.

--
Wes Groleau

Expert, n.:
Someone who comes from out of town and shows slides.|||Wes Groleau (groleau+news@.freeshell.org) writes:

Quote:

Originally Posted by

I think I also tried that, but maybe not. I'm going in to work today,
so I'll make sure. And I'll make another try at extracting a script
from that thing.

Note that you can also script from Query Analyzer.

And if only the timeout bothers you, run the UPDATE from QA. QA does not
have any timeouts.

Quote:

Originally Posted by

Or maybe

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

But unfortunately, Ent. Mgr/SQL Svr 2000 rejected this, saying that the
optional FROM syntax is not supported. By some experimentation, I got
THAT message to go away (even though both FROMs were still there).

But none of the ten variations I tried were accepted.
(By the way, page 72 and following of SQL Cookbook offers both this
approach and the one that my first approach was based on. But the
syntax it says will work for the first approach was also rejected
by my system--though I managed to alter it enough to work in the one case.

Apparently, the "optimizer" is not very smart. I eventually got the job
done as follows:

Load Raw_Segments

Create and insert Raw_BPR, Raw_CLP, Raw_SVC, Raw_CAS, Patient_Names, etc.

Index the above

Create Inv_Data with indexes

Crate view or table Selected_Adjustments from Raw_CAS

INSERT INTO Inv_Data
(FID, CAS_Seg, Adj_Group, Adj_Reason, Adj_Amount)
SELECT FID, Seg_Nbr, Type, Code, CAST(Amount AS money)
AS Expr1
FROM Selected_Adjustments

UPDATE Inv_Data
SET SVC_Seg = (SELECT MAX(Seg_Nbr)
FROM Raw_SVC AS Raw
WHERE Inv_Data.FID = Raw.FID
AND CAS_Seg Seg_Nbr)

UPDATE Inv_Data
SET Service = (SELECT Elem_01
FROM Raw_SVC AS Raw
WHERE Inv_Data.FID = Raw.FID
AND SVC_Seg Seg_Nbr)

UPDATE Inv_Data
SET CLP_Seg = (SELECT MAX(Seg_Nbr)
FROM Raw_CLP AS Raw
WHERE Inv_Data.FID = Raw.FID
AND CAS_Seg Seg_Nbr)

UPDATE Inv_Data
SET BPR_Seg = (SELECT MAX(Seg_Nbr)
FROM Raw_BPR AS Raw
WHERE Inv_Data.FID = Raw.FID
AND CAS_Seg Seg_Nbr)

Each update takes about ten seconds this way.

No doubt there's a simpler way, but I'm new at this.

--
Wes Groleau

He that complies against his will is of the same opinion still.
-- Samuel Butler, 1612-1680|||Erland Sommarskog wrote:

Quote:

Originally Posted by

And if only the timeout bothers you, run the UPDATE from QA. QA does not
have any timeouts.

How do you do that? When I click the icon that has the tooltip
"execution mode," it acts like it's doing something for a while,
and then it displays an execution plan. But the table is unchanged.

Then I select/copy the SQL, paste it into the Enterprise Manager and
click the exclamation point. It complains of a syntax error.

This happened before--I just forgot to mention it.

Removing the syntax error got the time out.

Maybe the timeout is because in one case (no timeout)
it first gathered part of the subquery (877 rows of 16 million)
making the second part 877 comparing to 31000. But if it
tries the other part first, it is checking the full 31000 rows
against the original 16 million.

I don't know whether that happened, but it would explain
why I did the job in less than sixty seconds by doing some
of the subqueries as separate extract and insert steps.

--
Wes Groleau
Heroes, Heritage, and History
http://freepages.genealogy.rootsweb.com/~wgroleau/|||Wes Groleau (groleau+news@.freeshell.org) writes:

Quote:

Originally Posted by

How do you do that? When I click the icon that has the tooltip
"execution mode,"

You click on the green arrow. (Or press F5 or CTRL/E.) Then it will
run the query which is the window.

I strongly encourage you to get acquianted with Query Analyzer to
run your queries. What you have in Enterprise Manager is a query
designer, and a fairly limited one as testified about the bogus message
about the FROM clause not being supported.

In Query Analyzer you are only limited by what SQL Server permits; the
tool itself does not limit you.

Quote:

Originally Posted by

Wes Groleau (groleau+news@.freeshell.org) writes:

Quote:

Originally Posted by

>How do you do that? When I click the icon that has the tooltip
>"execution mode,"

>
You click on the green arrow. (Or press F5 or CTRL/E.) Then it will
run the query which is the window.

Thanks. I don't recall what the icon was that I clicked,
only that it's tooltip said "execution mode" and that clicking it
caused around ten seconds of "hourglass" but no changes to the
table.

Quote:

Originally Posted by

Noted. I guess I'd better do so--although I'd rather get
SQL Server 2005, since that's what I went to class for.

--
Wes Groleau
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^^^^^^^
^ A UNIX signature isn't a return address, it's the ASCII equivalent ^
^ of a black velvet clown painting. It's a rectangle of carets ^
^ surrounding a quote from a literary giant of weeniedom like ^
^ Heinlein or Dr. Who. ^
^ -- Chris Maeda ^
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^^^^^^^|||Wes Groleau (groleau+news@.freeshell.org) writes:

Quote:

Originally Posted by

Quote:

Originally Posted by

>I strongly encourage you to get acquianted with Query Analyzer to
>run your queries. What you have in Enterprise Manager is a query
>designer, and a fairly limited one as testified about the bogus message
>about the FROM clause not being supported.

>
Noted. I guess I'd better do so--although I'd rather get
SQL Server 2005, since that's what I went to class for.

In SQL 2005, Enterprise Manager and Query Analyzer are both replaced
with SQL Server Management Studio. The Query Designer is still there,
and is still very limited, and you still do best to avoid it. Running
queries from the Query Editor is the way to go.

Quote:

Originally Posted by

Thanks for the tip. I'm saving it in case they ever actually
give us 2005.

We also have Oracle, but I don't have access to it.

--
Wes Groleau
Alive and Well
http://freepages.religions.rootsweb.com/~wgroleau/|||I STILL don't understand the server timeout.

Erland Sommarskog wrote:

Quote:

Originally Posted by

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/|||Wes Groleau (groleau+news@.freeshell.org) writes:

Quote:

Originally Posted by

Erland Sommarskog wrote:

Quote:

Originally Posted by

>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.

Well, since you never care about posting your table defitions or sample
data, do you really expect me to post a tested working query? Maybe you
could at least post the actual query you tried, and the error message?

I am sorry if my tone is somewhat irritated, but it is very difficult
to assist when the person asking for help refuse to dislose vital
information.

Quote:

Originally Posted by

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!)

Again, can you post the actual error message, and the exact query you are
using? If you get a timeout, it may be that you are using a linked server.
(Not that you ever said that you are using linked server, but at this
point I'm starting to feel like a participant in a quiz competition.)

Quote:

Originally Posted by

Again, can you post the actual error message, and the exact query you are
using? If you get a timeout, it may be that you are using a linked server.

Not sure what is meant by "linked" but when I start QA,
I leave the server choice on "(local)"

I will see whether I can capture some of this information.

--
Wes Groleau

Answer not a fool according to his folly,
lest thou also be like unto him.
Answer a fool according to his folly,
lest he be wise according to his own conceit.
-- Solomon

Are you saying there's no good way to answer a fool?
-- Groleau|||Erland Sommarskog wrote:

Quote:

Originally Posted by

I'm not trying to be obscure--it's just that (1) I don't have access
to that system and Usenet at the same time and place and (2) the table
definitions were done with the GUI and (3) I couldn't get the scripting
to work. (I've done it before, but this time it asks for a filename and
then doesn't create the file)

Quote:

Originally Posted by

Again, can you post the actual error message, and the exact query you are

Here is what I thought I had already posted, but I have added more
explanation to it since then:
Load Raw_Segments

Raw_Segments
( FID int,
Seg_Nbr int,
SegType varchar(5),
Elem_01 varchar(x),
Elem_02 varchar(y), x, y, ..., z are between 10 and 50
...
Elem_nn varchar(z) )

FID, Seg_Nbr, SegType are in the same nonclustered index

Raw_Segments was loaded from one humongous file by DTS.

Create and insert Raw_BPR, Raw_CLP, Raw_SVC, Raw_CAS, Patient_Names, etc.

I forget how I created these but each has the same structure/definition
as Raw_Segments but a subset of the data. BPR, CLP, SVC, CAS are
SegTypes and Patient_Names is all the segments where SegType = 'NM1'
and Elem_01 =s 'QC'

Index the above the same as Raw_Segments

Create Inv_Data with indexes

From memory, probably
Inv_Data
( FID int, -- A
CAS_Seg int, -- B
Adj_Group varchar, -- C
Adj_Reason varchar, -- D
Adj_Amount money, -- E
SVC_Seg int, -- F
Service varchar, -- G
Name varchar, -- H
CLP_Seg int, -- I
Claim varchar, -- J
Status varchar, -- K
Charges money, -- L
BPR_Seg int, -- M
RA_Date date, -- N
Check_Amount money, -- O
Provider varchar ) -- P

A,B,D,F,H,I,J,M,P are indexed

Create View Selected_Adjustments from Raw_CAS

INSERT INTO Raw_CAS SELECT * FROM Raw_Segments WHERE SegType = 'CAS'

A CAS Segment can have up to six adjustments in different columns in the
same row so Selected_Adjustments is a view giving the union of
six selects to map them all into one set per row. Plus each has a
filter of Code in ('22', 'B22', '12', '50')

Raw_? is like Raw_CAS

INSERT INTO Inv_Data -- which is empty until this happens
(FID, CAS_Seg, Adj_Group, Adj_Reason, Adj_Amount)
SELECT FID, Seg_Nbr, Type, Code, CAST(Amount AS money)
AS Expr1
FROM Selected_Adjustments -- this worked

-- Of the following updates, some worked and some timed out:
UPDATE Inv_Data
SET SVC_Seg = (SELECT MAX(Seg_Nbr)
FROM Raw_SVC AS Raw
WHERE Inv_Data.FID = Raw.FID
AND Inv_Data.CAS_Seg Raw.Seg_Nbr)

UPDATE Inv_Data
SET Service = (SELECT Elem_01
FROM Raw_SVC AS Raw
WHERE Inv_Data.FID = Raw.FID
AND SVC_Seg = Seg_Nbr)

DELETE
FROM Inv_Data AS I
WHERE Adj_Reason = '50'
AND Service not like '%GA%'

UPDATE Inv_Data
SET CLP_Seg = (SELECT MAX(Seg_Nbr)
FROM Raw_CLP AS Raw
WHERE Inv_Data.FID = Raw.FID
AND CAS_Seg Seg_Nbr)

UPDATE Inv_Data
SET BPR_Seg = (SELECT MAX(Seg_Nbr)
FROM Raw_BPR AS Raw
WHERE Inv_Data.FID = Raw.FID
AND CAS_Seg Seg_Nbr)

UPDATE Inv_Data
SET (fill in all the missing fields from Raw_whatever)

--
Wes Groleau

Change is inevitable. We need to learn that "inevitable" is
neither a synonym for "good" nor for "bad."
-- WWG|||Wes Groleau (groleau+news@.freeshell.org) writes:

Quote:

Originally Posted by

As I've said you can script from Query Analyzer as well.

And I am sorry, but I don't feel inclined to come with further
guesses without the error messages you are getting. Nor am I interested
in composing queries and then only hear "it wouldn't take them". I
understand that it may be difficult for you to bring the information
from one corner to another, but it is even more difficult for me
who don't even see the queries. And after all, copying an error message
from one corner of a room to another is not rocket science. Pen and
paper still works...

Quote:

Originally Posted by

>Not sure what is meant by "linked" but when I start QA,
>I leave the server choice on "(local)"

A linked server is a remote data source, which is accessed from SQL
Server in a distributed query. The linked server can be another SQL
Server, but can also be an Access database, Oracle database, Active
directory or anything else for which there is an ODBC driver or OLE DB
provider.

Quote:

Originally Posted by

I understand. What you did offer is appreciated.

Quote:

Originally Posted by

OK, then (as I suspected) it is not a linked server.

--
Wes Groleau

Nobody believes a theoretical analysis -- except the guy who did it.
Everybody believes an experimental analysis -- except the guy who
did it.
-- Unknown|||Wes Groleau (groleau+news@.freeshell.org) writes:

Quote:

Originally Posted by

Erland Sommarskog wrote:

Quote:

Originally Posted by

>And I am sorry, but I don't feel inclined to come with further
>guesses without the error messages you are getting. Nor am I interested
>in composing queries and then only hear "it wouldn't take them". I

>
I understand. What you did offer is appreciated.

Permit me to make the final remark, that I'm only asking for error
messages and that for my own sake. If you want help with your problems,
there is no reason why you shouldn't make the effort to transfer the
information to the newsgroup.

But if you don't want to make that effort to get your issues resovled -
well that's your call.

Quote:

Originally Posted by

Obviously no one here is obligated to help, so I can't complain
if I get even a crumb. I did not preserve any of the error messages,
as I am under pressure to show progress. I got the thing working
in Access with a subset of the files. But there is NO WAY Access
will handle the quantity of data that is in the full set of files.

So I will have to go back to SQL Server, and when that happens,
I will try to capture any error messages. If there are any.

Thanks for your kindness--and that of the few others who contributed.

--
Wes Groleau

Even if you do learn to speak correct English,
whom are you going to speak it to?
-- Clarence Darrow

Wednesday, March 28, 2012

I don't understand this "server timeout"

No comments:

Post a Comment

hyper threading

Blog Archive

About Me