| Author |
 Topic  |
|
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
 Posted - 12/01/2000 : 15:23:46
|
Jim writes "[SQL 6.50.201 on Windows NT 4.00.1381, SP 6]
(Yes, I know 6.5 is evil incarnate, but I'm not at liberty to update the stupid thing right now. Trust me, my degree of loathing is mighty indeed.)
I'm having a wacky issue with 6.5 implementing a stored procedure, and it's driving me absolutely crazy. Without going into unnecessary detail, we have a series of SP'd match processes that check client information in one table against information in another table (or in this case, a pair of joined tables). The resulting match information is stored in another table. These procedures have been in existence for over a year and run reasonably well and pretty reliably. I use Enterprise Mgr's SQL Query Tool to run the SPs.
So here's the problem. We had to add a new matching routine (at the client's request), which I based on an existing (i.e., working) procedure. As with all these beasts, it uses a cursor (and boy do I love 'em--Not!) to temporarily store the results of a join query, then loops through the cursor's contents to make appropriate entries in another table.
The procedure as currently written worked fine on a small data set (about a thousand entries), but it simply doesn't work on the larger one I've attempted to work with. At all.
I've tried breaking down the various parts of the query, and they all run fine individually, yet don't do a thing when put together. At first assuming it was a problem with the query, I've checked permissions on the tables, and rebuilt indexes on all tables consulted. Didn't help. It's driving me in-bloody-sane, as well as making my workday miserable. HELP!
Here's the procedure:
----------------------
CREATE PROCEDURE prcMatching29 @ClientNo int AS
DECLARE @msg varchar(40)
DECLARE @PERSID int
DECLARE @ClientID varchar(13)
DECLARE @SelectLevel varchar (20)
DECLARE @rCount int
DECLARE results29Cursor CURSOR
FOR
SELECT InsAddr.PERSID, ClientID
FROM Person
INNER JOIN InsAddr
ON InsAddr.PERSID = Person.person_id
INNER JOIN ClientData
ON ClientData.LName = Person.last_name
AND InsAddr.ADDRESS1 = ClientData.H_STR1
WHERE ClientData.ClientNo = @ClientNo
FOR READ ONLY
OPEN results29Cursor
FETCH NEXT FROM results29Cursor INTO @PERSID, @ClientID
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SELECT @rCount = (SELECT Count(*)
FROM ClientResults
WHERE ClientNo = @ClientNo
AND PERSID = @PERSID
AND ClientID = @ClientID)
IF (@rCount = 0)
BEGIN
INSERT INTO ClientResults (ClientNo, PERSID, SelectLevel, ClientID, SelLevel, verlevel)
VALUES (@ClientNo, @PERSID, '9.5', @ClientID, 9.5, 2)
END
ELSE
BEGIN
SELECT @SelectLevel = (SELECT SelectLevel FROM ClientResults WHERE ClientNo = @ClientNo AND PERSID = @PERSID AND ClientID = @ClientID)
IF (PATINDEX(@SelectLevel, '%9.5%') = 0)
BEGIN
UPDATE ClientResults
SET SelectLevel = SelectLevel + ',9.5'
WHERE ClientNo = @ClientNo
AND PERSID = @PERSID
AND ClientID = @ClientID
END
END
END
FETCH NEXT FROM results29Cursor INTO @PERSID, @ClientID
END
CLOSE results29Cursor
DEALLOCATE results29Cursor
EXEC prcMatchingCleanup @ClientNo
GO -------
Before you ask, I wasn't the guy who designed the system, just the guy that maintains it and adds features...
The procedure called at the end isn't affecting it either--it's not getting to that stage (and I've tried running it without the call as well). I feel like I've been over every inch of this thing without finding ANY leads.
Any help you can give is very much appreciated....
-Jim" |
|
|
btrimpop
Posting Yak Master
USA
214 Posts |
Posted - 12/01/2000 : 20:22:36
|
Some things to try if you haven't already.
Have you tried using SQL Trace to trace the execution and see where it's hanging up? A simple approach but have you tried sticking in print statments after each line to see where it's hanging up?
Have you checked the DB for corruption? You said it works on a small table (1000 rows), how big is a large table? Are you sure it's really locked up and not just taking a long time to complete on the large table?
There is a good third party product called Xpediter from CompuWare that allows you to debug/step through stored procedures. It works pretty well on MSSQL7.0, I don't know if they have a version for 6.5 but you might want to check out their web site.
|
 |
|
|
jvowles
Starting Member
USA
1 Posts |
Posted - 12/05/2000 : 10:06:33
|
A little more info...
The process *is* actually running...it's just taking forever. I'm checking into data corruption but see no evidence of that so far.
The frustrating thing is that every little step is SUCH a pain in 6.5 when you're used to the convenience of 7.0.
Any other suggestions gladly entertained... |
|
|
|
btrimpop
Posting Yak Master
USA
214 Posts |
Posted - 12/06/2000 : 13:36:05
|
Since the procedure is running is sounds like one of two things to me. Maybe a deadlock situation. Is any thing/one else accessing the db/tables while the process is running?
Indexes can be a 2 edged sword especially if you are updating the key columns of the indexes. This can cause the index statistics to get stale meaning the query parser will not use the appropriate index when building the query plan (ie really slow table scans used instead). What indexes to you have on the tables? Also indexes can really slow down updates and inserts due to key value changes/additions.
Also, with a quick look at the procedure, you should be able to eliminate the cursor and simply issue an insert and an update using the cursor select statement to control the insert. Something along the lines of sample below (maybe some typo's but should be close). Use the same sort of thing for the update:
INSERT INTO ClientResults ClientNo, PERSID, SelectLevel, ClientID, SelLevel, verlevel)
SELECT @ClientNo, InsAddr.PERSID,'9.5', person.ClientID, 9.5, 2
FROM Person INNER JOIN InsAddr ON InsAddr.PERSID = Person.person_id INNER JOIN ClientData ON ClientData.LName = Person.last_name AND InsAddr.ADDRESS1 = ClientData.H_STR1
WHERE ClientData.ClientNo = @ClientNo AND
NOT EXISTS (SELECT 'x' FROM ClientResults WHERE ClientNo = @ClientNo AND PERSID = insaddr.persid AND ClientID = person.ClientID)
|
|
|
| |
Topic |
|
|
|
SQL 6.5 Stored Proc Failing?
