• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 678
  • Last Modified:

SQL Query Doesn't ever (over 1 hour) finish

TABLE1        
----------------------------------------------------------
Table2ID         |Table2Realtionship   |Email     |
----------------------------------------------------------

TABLE2        
----------------------------------------------------------
Table2ID         |Email                |                 |
----------------------------------------------------------


update TABLE2
set TABLE2S.Email = TABLE1.Email
from TABLE1 join TABLE2 on TABLE1.Table2ID = TABLE1.Table2ID
where TABLE1.Table2Realtionship = '1'
and TABLE1.Email like '%@%'
and not(TABLE2.Email like '%@%');

Table2ID is a key index field

This update works on my local test data with only 1000 records in table1 and about 700 in table2.  It copies the email address from table1 to table2 when the records share the table2id and table2relationship equals 1 and when there is an email in table1 and not one in table2.  

When I run it on a client machine, even when the client isn't using the data, the query never stops running, at least not after about an hour and a half.

Any ideas?  This is a pervasive SQL version 9.71, I have rebuilt both files.
0
bcolladay
Asked:
bcolladay
  • 10
  • 6
1 Solution
 
Bill BachPresidentCommented:
While other SQL engines use a transaction log that can simply be truncated in the event of a failure, PSQLv9 uses a single Btrieve-level transaction for each SQL query to ensure atomicity.  On large data sets, this can actually run out of memory (and return a Btrieve Stats 100), or it can perform very poorly as memory get sucked up inside the transaction.  This is simply the way the engine operates and is not expected to change in any newer release.  

Your best option is to re-do this operation as a stored procedure (without wrapping a transaction around the SP).  This will give you a fast and efficient operation.

You may already have SP experience, so I won't bore you with the details.  Let me know if you need help building it.
0
 
bcolladayAuthor Commented:
Thanks Bill, I was wondering if that would be a better solution.  I will try to write one and post back if I have questions.  Thanks
0
 
bcolladayAuthor Commented:
So something like this:

CREATE PROCEDURE TABLE2UPDATE();

BEGIN
update TABLE2
set TABLE2.Email = TABLE1.Email
FROM TABLE2, TABLE1
where TABLE1.TABLE2id = TABLE2.TABLE2ID
and TABLE1.Relationship = '1'
and not(TABLE1.Email = '')
and TABLE2.Email = '';
END

Execute TABLE2UPDATE();
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
Bill BachPresidentCommented:
Nope.  That'll still do it in a single transaction.  Here's a sample of what you want to be doing.  It's not exact, but it gives you the idea of the type of procedural code that you need.  If you need help updating it for your exact need, let me know.

CREATE PROCEDURE TabVotes() WITH DEFAULT HANDLER
AS BEGIN
	DECLARE :v1 CHAR(1);
	DECLARE :v2 CHAR(1);
	DECLARE :v3 CHAR(1);
	DECLARE :p CHAR(1);
	DECLARE :Result Integer;
	DECLARE curs CURSOR FOR select Vote1, Vote2, Vote3, Passes  from VoteFile  FOR UPDATE;
	OPEN curs;
	FETCH NEXT FROM curs INTO :v1, :v2, :v3, :p;
	WHILE(SQLSTATE = '00000') DO
		SET :Result = 0;
		IF :v1 = 'Y' THEN SET :Result = :Result + 1; END IF;
		IF :v2 = 'Y' THEN SET :Result = :Result + 1; END IF;
		IF :v3 = 'Y' THEN SET :Result = :Result + 1; END IF;
		IF :Result >= 2 THEN
			SET :p = 'Y';
		ELSE
			SET :p = 'N';
		END IF;
		UPDATE SET Passes  = :p WHERE CURRENT OF curs;
		FETCH NEXT FROM curs INTO :v1, :v2, :v3, :p;
	END WHILE;
	CLOSE curs;
END;

Open in new window

0
 
bcolladayAuthor Commented:
Thanks, very helpful!
0
 
bcolladayAuthor Commented:
When I Execute this, it gets in a loop - apparently - a dataset with only 1000 record in the table went on for 25 minutes....

CREATE PROCEDURE SyncEmail() WITH DEFAULT HANDLER
AS BEGIN
      DECLARE :BillToEmail CHAR(40);
      DECLARE :PatientEmail CHAR(40);
      DECLARE :Relationship CHAR(1);
      DECLARE :BTBilltoID IDENTITY;
      DECLARE :PTBilltoID IDENTITY;
      DECLARE curs CURSOR FOR select BILLTOS.BillToID, PATIENTS.BillToID, PATIENTS.Relationship, PATIENTS.Email, BILLTOS.Email  from BILLTOS, PATIENTS  FOR UPDATE;
      OPEN curs;
      FETCH NEXT FROM curs INTO :BTBilltoID, :PTBilltoID, :Relationship, :PatientEmail, :BillToEmail;
      WHILE(SQLSTATE = '00000') DO
            IF :BTBilltoID = :PTBilltoID
            AND :Relationship = '1'
            AND :PatientEmail LIKE '%@%'
            AND :BillToEmail = ''  
            THEN UPDATE SET BILLTOS.Email  = :PatientEmail WHERE CURRENT OF curs;
            END IF;            
            FETCH NEXT FROM curs INTO :BTBilltoID, :PTBilltoID, :Relationship, :PatientEmail, :BillToEmail;
      END WHILE;
      CLOSE curs;
END;
0
 
Bill BachPresidentCommented:
You cannot do an update on a multiple-table query.  I'm working on a critical server issue for a customer right now -- give me a few minutes to re-work this SP and get back to you.
0
 
Bill BachPresidentCommented:
Note that I removed the excess stuff from the query, since the joined query is not really what you want in a procedure like this.  I also eliminated the extra variables for Relationship and PatID, which is really immaterial in the process.

The TOP 1 clause may or may not be needed -- I just wanted to avoid a conflict if multiple records came back.  Also, there may be additional optimization possible, but I don't have much time right now.
CREATE PROCEDURE SyncEmail() WITH DEFAULT HANDLER
AS BEGIN
      DECLARE :BillToEmail CHAR(40);
      DECLARE :PatientEmail CHAR(40);
      DECLARE :BTBilltoID INTEGER;
      DECLARE curs CURSOR FOR SELECT BillToID, Email FROM BILLTOS WHERE EMail = '' FOR UPDATE;
      OPEN curs;
      FETCH NEXT FROM curs INTO :BTBilltoID, :BillToEmail;
      WHILE(SQLSTATE = '00000') DO
            IF :BillToEmail = '' THEN
                SET :PatientEMail = '';
                SELECT TOP 1 EMail INTO :PatientEMail FROM PATIENTS WHERE BillToID = :BTBilltoID AND Relationship = '1' AND EMail like '%@%';            
                UPDATE SET Email  = :PatientEmail WHERE CURRENT OF curs;
            END IF;   
            FETCH NEXT FROM curs INTO :BTBilltoID, :BillToEmail;
      END WHILE;
      CLOSE curs;
END;

Open in new window

0
 
bcolladayAuthor Commented:
For some reason I get this when I execute

<<<<<<<<<<<<<<<<<<<<<<<<
Execute SyncEmail()
SQL statement(script) has executed successfully.
-1 rows were affected.
>>>>>>>>>>>>>>>>>>>>>>>>

It doesn't do the update that the plain update statement did.  No rush.  If you ever get anytime it would be great.  Thanks again.
0
 
bcolladayAuthor Commented:
<<<<<<<<<<<<<<<<<<<<<<<<
CREATE PROCEDURE SyncEmail() WITH DEFAULT HANDLER
AS BEGIN
      DECLARE :BillToEmail CHAR(40);
      DECLARE :PatientEmail CHAR(40);
      DECLARE :BTBilltoID INTEGER;
      DECLARE curs CURSOR FOR SELECT BillToID, Email FROM BILLTOS WHERE EMail = '' FOR UPDATE;
      OPEN curs;
      FETCH NEXT FROM curs INTO :BTBilltoID, :BillToEmail;
      WHILE(SQLSTATE = '00000') DO
            IF :BillToEmail = '' THEN
                SET :PatientEMail = '';
                SELECT TOP 1 EMail INTO :PatientEMail FROM PATIENTS WHERE BillToID = :BTBilltoID AND Relationship = '1' AND Email like '%@%';            
                UPDATE BILLTOS SET Email  = :PatientEmail WHERE CURRENT OF curs;
            END IF;  
            FETCH NEXT FROM curs INTO :BTBilltoID, :BillToEmail;
      END WHILE;
      CLOSE curs;
END
SQL statement(script) has executed successfully.
0 rows were affected.
>>>>>>>>>>>>>>>>>>>>>>>>

<<<<<<<<<<<<<<<<<<<<<<<<
Execute SyncEmail()
SQL statement(script) has executed successfully.
-1 rows were affected.
>>>>>>>>>>>>>>>>>>>>>>>>

<<<<<<<<<<<<<<<<<<<<<<<<
update BILLTOS
set BILLTOS.Email = PATIENTS.Email
from PATIENTS, BILLTOS
where PATIENTS.BillToID = BILLTOS.BillToID
and PATIENTS.Relationship = '1'
and PATIENTS.Email like '%@%'
and not(BILLTOS.Email like '%@%')
SQL statement(script) has executed successfully.
5 rows were affected.
>>>>>>>>>>>>>>>>>>>>>>>>
0
 
Bill BachPresidentCommented:
Try taking the "WHERE EMail = ''" off of the Cursor query -- there could be an issue with it changing the first record and then repositioning to the end of the data set, since the WHERE clause value is changing inside the query.

0
 
bcolladayAuthor Commented:
Bill, I'm going to ask this as a related question.  Thanks for your help on this.  You answered my original question.  

Thanks, Bob
0
 
Bill BachPresidentCommented:
OK.  Customer I was working with is back up and running, but I have a corrupted PSQL file that I am fixing for a dentist in the UK.  It's definitely a Monday.  Once I get caught up, I'll look for the new question and see if I can find out what is wrong.
0
 
bcolladayAuthor Commented:
Thanks.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 10
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now