Re: SQL performance with Local journalling

If they didn't do a commit after every update (or every several updates) 
they ran the whole thing as a single transaction. That WILL slow the 
processing.

Dan




Evan Harris <spanner@xxxxxxxxxx> 
Sent by: midrange-l-bounces@xxxxxxxxxxxx
02/23/2005 12:46 PM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
midrange-l@xxxxxxxxxxxx
cc

Subject
SQL performance with Local journalling






Hi all

I have a customer that is starting out on a HA implementation who wondered 

about the additional overhead of journaling.

To test the impact they wrote a simple SQL update statement and ran it on 
a 
journaled file, then ran it again after turning journaling off. The impact 

was significantly different (in the order of 1000%) and although they 
didn't process a whole lot of records (a couple of thousand) the result is 

enough to have them concerned.

After I spoke to them a couple of days later I had them run the statement 
again, but asked them to run the SQL statement while the file was not 
journaled first to see if the order of execution had had any effect on the 

relative performance of the two operations. This had essentially the same 
result.

I am not overly concerned about the likely impact of journaling on their 
system as the hardware should handle it and my experience is that it will 
not add anywhere near the overhead that they are seeing in their 
admittedly 
limited testing, however, I am curious as to what could cause such a weird 

result.

The SQL itself was selecting approximately 2000 records via the relative 
record number and performing a simple update on a field in the record 
layout. The file was created by doing a copy file and there were no 
logicals over the new file. Relative record was uses to avoid using a key 
for selection. The SQL was submitted to batch in both cases via some kind 
of RUNSQL command they have access to. Essentially they were trying to 
assess the raw impact of journaling by removing all the variables that 
might have influenced the performance of the SQL.

Is there anything that anyone has seen that could account for this result 
? 
Is there any relationship between SQL and journaling that I should take 
into account ?

Regards
Evan Harris 

-- 
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.