Re: SQL Performance between platforms

Hi Chuck,

I'm nowhere near an expert, but I believe one of the challenges that we
"old" (perhaps I should say "fully ripe" :-) programmers face in adopting
SQL is resisting the tendency to process data in a record-by-record
fashion. SQL is designed to handle sets of records (of course, a set may
contain one or zero records), which -- to me, anyway -- means a change in
thinking.

You might try a combination of the following:

to insert missing records:

Insert [fields from AS/400 table] into [Access table]
        where [AS/400 key] not in (select * from [Access table] where [Access
key] = [AS/400 key])

to update records:

Update [Access table] set [Access field1] = [AS/400 field1], etc.
        where [Access key] =[AS/400 key> and
        ([Access field 1] <> [AS/400 field 1]) or . . . )

My syntax probably isn't quite right, but the idea would be to do both the
inserts and updates as a group, rather than one at a time. . .

hth

MIDRANGE-L@midrange.com writes:
>I am updating an Access database on a PC from a database on the AS/400. 
>I am using a VB program to perform the update.  The logic looks like this:
>
>Select all records from the AS/400 table
>Loop through the record set
>   Select the corresponding record from the Access database
>   If it does not exist Then
>     Insert the record into the Access database
>   Else
>     If the data is different between the two records Then
>        Update the Access database record
>     End If
>   End If
>End Loop
>
>The problem I have is the amount of time this takes to run (almost
>370,000 records in the table).  I know that this is sloppy SQL style, but
>(as far as I know) I can only access one database (either AS/400 or
>Access) with each SQL statement.  Does anyone have suggestions on how to
>streamline such a process?
>
>Thanks!
>
>Chuck Morehead
>Nokuse Consulting - Providing the highest value in Information Systems
>Services.
>Native Bear Software - Providing Industry Specific Software Solutions to
>small- and medium-sized businesses.
>http://www.nokuse.com



Mike Naughton
Senior Programmer/Analyst
Judd Wire, Inc.
124 Turnpike Road
Turners Falls, MA  01376
413-863-4357 x444
mnaughton@juddwire.com

+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---