ADO Performance Problem with FoxPro 6.0 & AS/400 use  
Author Message
eRoHaN





PostPosted: Visual FoxPro General, ADO Performance Problem with FoxPro 6.0 & AS/400 use Top

Hi all,

I used ADO to connect AS/400 machine from foxpro 6.0 and i get data through
processing SQL on it.The performance is very low when we try to connect
to a AS/400 machine in a far place.

Here is my code :

* Procedure to get connection from AS/400
procedure GET_SERVER_CONN
local Err, cOldErr, cn
cOldErr = On("error")
On Error Err = .T.
cn = createobject('adodb.connection')
rs = createobject('adodb.recordset')
* MERKEZ10 - Name of server in network
cn.open("Provider=IBMDA400;Data Source=MERKEZ10", "", "")
if Err
wait window "No connection with server!!!" nowait
else
return cn
endif
endproc



Here is the procedure which gets customer list from server if flag field of

table is not equal to 1 :

* Procedure to get customer data!
procedure MUSTERILERI_AL
Local sqlCommand
* Control the flag and do the job for local *.dbf file.
* Finally, update the flag on server!
* 0 - New record 2 - Updated, 1 - Okay, 3 - Unknown state
sqlCommand = "select * from " + musteriLibrary + ".hbsanap where flag != '1'"
* con -> Connection object
rs = con.execute(sqlCommand)
local _u_no, mus_kod, mus_unvan, _adres1, _adres2, _telefon, _ilkod, _ilcekod, _vergi_no, _vergi_dr, _mus_ref
do while !rs.eof
mus_kod = rs.fields("musno").value
mus_unvan = rs.fields("adr1").value + rs.fields("adr2").value
_adres1 = rs.fields("adr3").value + rs.fields("adr4").value
_adres2 = rs.fields("adr5").value
_telefon = rs.fields("telk1").value + rs.fields("tel11").value
_ilkod = ""
_ilcekod = ""
_vergi_no = rs.fields("verno").value
_vergi_dr = rs.fields("verda").value
_mus_ref = mus_kod
* Getting max id for 'musteri'
select max(u_no) as maxx from musteri into cursor maxNumCursor
_u_no = maxNumCursor.maxx + 1
if used('maxNumCursor')
select maxNumCursor
use
endif
* Opening the musteri.dbf file as exclusively to process it!
selectx("musteri", 1, 1)
if rs.fields("flag").value = "0"
* Control musteri if any same coded
locate for kod = mus_kod
if !found()
* Must be inserted!
INSERT INTO musteri (kod, ad, telefon, fax, adres1, adres2, sehir, ilce, vergi_dr, vergi_no, u_no, mus_ref);
VALUES (mus_kod, mus_unvan, _telefon, "", _adres1, _adres2, _ilkod, _ilcekod, _vergi_dr, _vergi_no, _u_no, _mus_ref)
endif
else
locate for kod = mus_kod
if found()
* Must be updated! Also, for flag = 3!
update musteri set ad = mus_unvan,;
telefon = _telefon,;
fax = "",;
adres1 = _adres1,;
adres2 = _adres2,;
sehir = _ilkod,;
ilce = _ilcekod,;
vergi_dr = _vergi_dr,;
vergi_no = _vergi_no;
where kod = mus_kod
else
INSERT INTO musteri (kod, ad, telefon, fax, adres1, adres2, sehir, ilce, vergi_dr, vergi_no, u_no, mus_ref);
VALUES (mus_kod, mus_unvan, _telefon, "", _adres, "", _ilkod, _ilcekod, _vergi_dr, _vergi_no, _u_no, _mus_ref)
endif
endif
* The flag on server side must be updated as "1" also!
updateSQL = "update " + musteriLibrary + ".hbsanap set flag = '1' where musno = '" + rs.fields("musno").value + "'"
con.execute(updateSQL)
rs.MoveNext
enddo
rs.close
endproc

When i try to use IBM's 'Data Transfer from iSeries' utility tool to get data it

does the same job much more fast approximately taking 10000 records in 1 minute.

But our code takes only 18 records in 1 minute. So buffering is not the
solution i think like taking records partially as for example 100 records sets.

So, what will you be able to suggest to improve performance




Visual FoxPro1  
 
 
eRoHaN





PostPosted: Visual FoxPro General, ADO Performance Problem with FoxPro 6.0 & AS/400 use Top

I must also indicate that after each record is updated on local *.dbf file
(The FoxPro use this *.dbf file like a table!), the server's related record on
customer table is updated as doing the flag field as 1. So, we synchronize
the data.

 
 
CetinBasoz





PostPosted: Visual FoxPro General, ADO Performance Problem with FoxPro 6.0 & AS/400 use Top

Your code has unnecessary duplications, not using m. prefix, doing unnecessary "select" multiple times etc to further slow down the process. However most strikingly you're sending an update command per record! Instead use a client side cursor with a batchupdatelock and do a batch update. To further speed it up and hold lock for a shorter time you could:

-Do initial select
-Update flag
-Batch update
-Save rs locally
-Disconnect
-Open locally saved rs
-Process VFP side

PS: Do not undertake m. prefix. (in a loop it really matters speed wise) If for nothing else your variable names are great candidates to be real field names. Also check if an ODBC driver exists. If there is then you could do this with an updatable SPT in an easier way.