RE: SQL performance with Local journalling

Maybe they need to purge the file from storage...

When they turned journaling off and reran the query, the object was already
paged into a storage pool, giving the illusion of exceptional performance.  

Use the CLRPOOL command before each run...

hth,

Eric DeLong
Sally Beauty Company
MIS-Project Manager (BSG)
940-297-2863 or ext. 1863



-----Original Message-----
From: Evan Harris [mailto:spanner@xxxxxxxxxx]
Sent: Wednesday, February 23, 2005 12:46 PM
To: midrange-l@xxxxxxxxxxxx
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.