speed

Posted by: clbirk

speed
has no ratings.

Published: 28 May 2013
Revised: 30 May 2013 - 3975 days ago
Last viewed on: 16 Apr 2024 (4142 views)

Main URL:

speed Published by: clbirk on 28 May 2013 view comments(3)

I had 135,000 sql statements to process against 5 different files. I had the jr. programmer write out sql statements (meaning write out of a program, not "write" with a pen), and then I copied like 45,000 into inav's sql interface to run.

Slow was an understatement. Took like 5 or more hours for those 45,000.

So for the 90,000 remaining I found runsqlstm and while it asks for a source file and member, it didn't like my flat file in qs36f, so I simply dumped them to the ifs and used the stream file option. Took maybe 10 minutes to do the rest.

While the inav feature is nice as it will show you an error, slow is the nature of the beast. Sure I use it alot when I have a dbfile that I need to reorganize some fields or such that inav (SQL/DDL) won't let you do, as I will simply click generate sql, take the sql make my changes and go delete the file and then run that window. But if you have alot of transactions, it is not the way to go.

chris

Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)

Posted by: DaleB

Premium member *

Reading, PA

Comment on: speed
Posted: 10 years 10 months 20 days 15 hours ago

For future reference, you can CPYF a flat file into a source file. See SRCOPT() and SRCSEQ() keywords (though defaults are probably OK for what you describe). One advantage of this is relative ease of using a user-defined PDM option to do the RUNSQLSTM. Or to do the CPYF, for that matter.

Posted by: Paulster

Premium member *

Sweden and The Netherlands

Comment on: speed
Posted: 10 years 10 months 19 days 20 hours 1 minutes ago

...this just leaves one curious as to the sheer number of SQLs to be run...

Posted by: clbirk

Premium member *

Comment on: speed
Posted: 10 years 10 months 19 days 10 hours 56 minutes ago

These were all delete statements and to delete records out of 5 tables. Every couple of months we go through and delete these out of our system as these 5 tables are used in an order entry system (that once a phone order is placed it goes into the regular erp system). These tables are used by php code on the box to do a gui order entry system.

Yes I could run them every week (or every day) and get rid of those 90 days or oolder but but I hadn't ran it for a while. I was going to directly delete them in an rpg program but the jr. programmer was to write it and further more there are different keys and it is a situation of in some cases getting keys from various files also doing setll, etc. and so I wanted to see the transactions.