1

I've converted and updated some python scripts that exported data from an iSeries database (HTE) to Access to export the same data to a fgdb. My biggest issue is the time it's taking to export the data and update the table in my gdb. I'm not sure what exactly is missing to make this Py go faster, but I'm sure someone out there knows the answer. I'm using 10.2. The code is attached for your enjoyment!

#imports
import pyodbc
import arcpy

#---------------------------------CONNECTION FOR iSERIES 

DATABASE------------------------------------------------------

#Creating a connection string for iSeries
conn_iseries = xxxxxxxxx

#Creating first SQL cursor based on the iSeries connection string
iseries_cursor = conn_iseries.cursor()

#---------------------------------QUERY iSERIES AND INSERT INTO MS ACCESS 

DATABASE-------------------------------------


#Create iSeries SQL query string
sql_qry_iseries = """
                    SELECT  DISTINCT
                            TRIM(WFAHREP.AHALCD) AS FAC_ID, /* Facility ID */
                            TRIM(WFAHREP.AHAOCD) AS FAC_PARENT, /* Parent Facility */
                            TRIM(WFAHREP.AHAGTX) AS FAC_DESC, /* Facility Description */
                            TRIM(WFAHREP.AHAMCD) AS DEPT_RESP, /* Department Responsible */
                            TRIM(WFAHREP.AHAHST) AS FAC_TYPE, /* Facility Type */
                            TRIM(WFAHREP.AHC3CD) AS FAC_SUBTYPE, /* Facility sub Type */
                            TRIM(WFBHREP.BHCVTX) AS SUBTYPE_DESC, /* Facility Subtype Description */
                            TRIM(WFAHREP.AHESCD) AS LOC_CODE, /* General Location Code */
                            TRIM(WFBHREP.BHCNTX) AS TITLE1, /* Facility Code Field 1 Description */
                            TRIM(WFAHREP.AHC8CD) AS CODE1, /* User Code Field 1 */
                            TRIM(WFBIREP1.BICWTX) AS CODEDESC1, /* User Field Code Description */
                            TRIM(WFBHREP.BHCOTX) AS TITLE2, /* Facility Code Field 2 Description */
                            TRIM(WFAHREP.AHC5CD) AS CODE2, /* User Code Field 2 */
                            TRIM(WFBIREP2.BICWTX) AS CODEDESC2, /* User Field Code Description */
                            TRIM(WFBHREP.BHCPTX) AS TITLE3, /* Facility Code Field 3 Description */
                            TRIM(WFAHREP.AHC6CD) AS CODE3, /* User Code Field 3 */
                            TRIM(WFBIREP3.BICWTX) AS CODEDESC3, /* User Field Code Description */
                            TRIM(WFBHREP.BHCQTX) AS TITLE4, /* Facility Code Field 4 Description */
                            TRIM(WFAHREP.AHC7CD) AS CODE4, /* User Code Field 4 */
                            TRIM(WFBIREP4.BICWTX) AS CODEDESC4, /* User Field Code Description */
                            TRIM(WFBHREP.BHCRTX) AS TITLE5, /* Facility Date Field 1 Description */
                            DATEFMT1.DATE_FMT AS DATE5, /* Real Date Field for Facility Date Field 1 

Description */
                            TRIM(WFBHREP.BHCSTX) AS TITLE6, /* Facility Date Field 2 Description */
                            DATEFMT2.DATE_FMT AS DATE6, /* Real Date Field for Facility Date Field 2 

Description */
                            TRIM(WFBHREP.BHCTTX) AS TITLE7, /* Facility Number Field 1 Description */
                            WFAHREP.AHCBNB AS NUMBER7, /* User Numeric Field 1 */
                            TRIM(WFBHREP.BHCUTX) AS TITLE8, /* Facility Numeric Field 2 Description */
                            WFAHREP.AHCCNB AS NUMBER8, /* User Numeric Field 2 */
                            WFAHREP.AHDVST AS ACTIVEFLAG /* Active Flag */
                    FROM
                            HTEDTA.WFAHREP WFAHREP /* Facility Master */

                    LEFT OUTER JOIN
                            HTEDTA.WFBHREP WFBHREP /* Facility Sub-type */
                    ON      WFAHREP.AHAHST=WFBHREP.BHAHST /* Facility type */
                    AND     WFAHREP.AHC3CD=WFBHREP.BHC3CD /* Facility Sub-Type Code */

                    LEFT OUTER JOIN
                            HTEDTA.WFBIREP WFBIREP1 /* Facility Sub-type Codes */
                    ON      WFAHREP.AHAHST = WFBIREP1.BIAHST /* Facility type */
                    AND     WFAHREP.AHC3CD = WFBIREP1.BIC3CD /* Facility Sub-Type Code */
                    AND     WFAHREP.AHB9ST = WFBIREP1.BIB5ST /* User Field 1 ID */
                    AND     WFAHREP.AHC8CD = WFBIREP1.BIC4CD /* User Code Field 1 */

                    LEFT OUTER JOIN
                            HTEDTA.WFBIREP WFBIREP2 /* Facility Sub-type Codes */
                    ON      WFAHREP.AHAHST = WFBIREP2.BIAHST /* Facility type */
                    AND     WFAHREP.AHC3CD = WFBIREP2.BIC3CD /* Facility Sub-Type Code */
                    AND     WFAHREP.AHB6ST = WFBIREP2.BIB5ST /* User Field 2 ID */
                    AND     WFAHREP.AHC5CD = WFBIREP2.BIC4CD /* User Code Field 2 */

                    LEFT OUTER JOIN
                            HTEDTA.WFBIREP WFBIREP3 /* Facility Sub-type Codes */
                    ON      WFAHREP.AHAHST = WFBIREP3.BIAHST /* Facility type */
                    AND     WFAHREP.AHC3CD = WFBIREP3.BIC3CD /* Facility Sub-Type Code */
                    AND     WFAHREP.AHB7ST = WFBIREP3.BIB5ST /* User Field 3 ID */
                    AND     WFAHREP.AHC6CD = WFBIREP3.BIC4CD /* User Code Field 3 */

                    LEFT OUTER JOIN
                            HTEDTA.WFBIREP WFBIREP4 /* Facility Sub-type Codes */
                    ON      WFAHREP.AHAHST = WFBIREP4.BIAHST /* Facility type */
                    AND     WFAHREP.AHC3CD = WFBIREP4.BIC3CD /* Facility Sub-Type Code */
                    AND     WFAHREP.AHB8ST = WFBIREP4.BIB5ST /* User Field 4 ID */
                    AND     WFAHREP.AHC7CD = WFBIREP4.BIC4CD /* User Code Field 4 */

                    LEFT OUTER JOIN
                            COLLIB.DATEFMT DATEFMT1 /* Date Format */
                    ON      WFAHREP.AHAVDT = DATEFMT1.CYYMMDD /* User Date Field 1 */

                    LEFT    OUTER JOIN
                            COLLIB.DATEFMT DATEFMT2 /* Date Format */
                    ON      WFAHREP.AHAWDT = DATEFMT2.CYYMMDD /* User Date Field 2 */

                    WHERE
                            WFAHREP.AHALCD NOT LIKE '%''%' /* Facility ID NOT HAVING ANY SINGLE QUOTE */

                """

#Execute the sql string
iseries_cursor.execute(sql_qry_iseries)

#Main while loop - get the SQL data
while 1:

    #Fetch one row from the cursor1 to the row1
    iseries_row = iseries_cursor.fetchone()

    #if row1 does not have any record/data then break the 'while' loop
    if not iseries_row:
        break

    #-------------------------------------------------------------------
    #Assigning the row field data to veriables

    #Facility ID
    FAC_ID1 = str(iseries_row.FAC_ID)
    #Parent Facility
    FAC_PARENT1 = str(iseries_row.FAC_PARENT)

    #Facility Description
    #If the Facility Description has a single quote (') inside, then Access database SQL will not accept that
    FAC_DESC1 = str(iseries_row.FAC_DESC)
    #Replacing all tthe single quote (if any) with two single quote
    NEW_FAC_DESC1 = FAC_DESC1.replace("'","''")

    #Department Responsible
    DEPT_RESP1 = str(iseries_row.DEPT_RESP)
    #Facility Type
    FAC_TYPE1 = str(iseries_row.FAC_TYPE)
    #Facility sub Type
    FAC_SUBTYPE1 = str(iseries_row.FAC_SUBTYPE)

    #Facility Subtype Description
    #If the Facility Subtype Description has a single quote (') inside, then Access database SQL will not 

accept that
    SUBTYPE_DESC1 = str(iseries_row.SUBTYPE_DESC)
    #Replacing all tthe single quote (if any) with two single quote
    NEW_SUBTYPE_DESC1 = SUBTYPE_DESC1.replace("'","''")

    #General Location Code
    LOC_CODE1 = str(iseries_row.LOC_CODE)

    #Facility Code Field 1 Description
    TITLE1 = str(iseries_row.TITLE1)

    #User Code Field 1
    CODE1 = str(iseries_row.CODE1)

    #User Field Code Description
    #If the Facility Subtype Description has a single quote (') inside, then Access database SQL will not 

accept that
    CODEDESC1 = str(iseries_row.CODEDESC1)
    #Replacing all tthe single quote (if any) with two single quote
    NEWCODEDESC1 = CODEDESC1.replace("'","''")

    #Facility Code Field 2 Description
    TITLE2 = str(iseries_row.TITLE2)

    #User Code Field 2
    CODE2 = str(iseries_row.CODE2)

    #User Field Code Description
    #If the Facility Subtype Description has a single quote (') inside, then Access database SQL will not 

accept that
    CODEDESC2 = str(iseries_row.CODEDESC2)
    #Replacing all tthe single quote (if any) with two single quote
    NEWCODEDESC2 = CODEDESC2.replace("'","''")

    #Facility Code Field 3 Description
    TITLE3 = str(iseries_row.TITLE3)

    #User Code Field 3
    CODE3 = str(iseries_row.CODE3)

    #User Field Code Description
    #If the Facility Subtype Description has a single quote (') inside, then Access database SQL will not 

accept that
    CODEDESC3 = str(iseries_row.CODEDESC3)
    #Replacing all tthe single quote (if any) with two single quote
    NEWCODEDESC3 = CODEDESC3.replace("'","''")

    #Facility Code Field 4 Description
    TITLE4 = str(iseries_row.TITLE4)

    #User Code Field 4
    CODE4 = str(iseries_row.CODE4)

    #User Field Code Description
    #If the Facility Subtype Description has a single quote (') inside, then Access database SQL will not 

accept that
    CODEDESC4 = str(iseries_row.CODEDESC4)
    #Replacing all tthe single quote (if any) with two single quote
    NEWCODEDESC4 = CODEDESC4.replace("'","''")


    #Facility Date Field 1 Description
    TITLE5 = str(iseries_row.TITLE5)

    #Real Date Field for Facility Date Field 1 Description
    #If the date has a None value returned then change it to 'Null' as Access SQL date field understands Null 

Date
    if str(iseries_row.DATE5) == 'None':
    #   MSAccess Data import --- DATE5 = 'Null'
        DATE5 = 0
    #Else assign the date value to the variable
    else:
    #   MSAccess Date Formating --- DATE5 = '#' + str(iseries_row.DATE5) + '#'
        DATE5 = str(iseries_row.DATE5)

    #Facility Date Field 2 Description
    TITLE6 = str(iseries_row.TITLE6)

    #Real Date Field for Facility Date Field 2 Description
    #If the date has a None value returned then change it to 'Null' as Access SQL date field understands Null 

Date
    if str(iseries_row.DATE6) == 'None':
    #   MSAccess Data import --- DATE6 = 'Null'
        DATE6 = 0
    #Else assign the date value to the variable
    else:
    #   MSAccess Date Formating --- DATE6 = '#' + str(iseries_row.DATE6) + '#'
        DATE6 = str(iseries_row.DATE6)


    #Facility Number Field 1 Description
    TITLE7 = str(iseries_row.TITLE7)

    #User Numeric Field 1
    #If the numeric field returns 'None' value, we will change it to '0' (Zero)
    if str(iseries_row.NUMBER7) == 'None':
        NUMBER7 = '0'
    #Else keep the returned value
    else:
        NUMBER7 = str(iseries_row.NUMBER7)


    #Facility Number Field 2 Description
    TITLE8 = str(iseries_row.TITLE8)

    #User Numeric Field 2
    #If the numeric field returns 'None' value, we will change it to '0' (Zero)
    if str(iseries_row.NUMBER8) == 'None':
        NUMBER8 = '0'
    #Else keep the returned value
    else:
        NUMBER8 = str(iseries_row.NUMBER8)

    #Active Flag
    ACTIVEFLAG = str(iseries_row.ACTIVEFLAG)


    #ArcPy insert code goes here!
    # Create insert cursor for table
    #
    cursor = arcpy.da.InsertCursor("D:\GIS\Users\xxxxx\xxxxxxx\U_test.gdb\WFAHREP",
                               ["FAC_ID", "FAC_PARENT", "FAC_DESC", "DEPT_RESP",
                               "FAC_TYPE", "FAC_SUBTYP", "SUBTYPDESC", "LOC_CODE",
                               "TITLE1", "CODE1", "CODEDESC1", "TITLE2", "CODE2",
                               "CODEDESC2", "TITLE3", "CODE3", "CODEDESC3", "TITLE4",
                               "CODE4", "CODEDESC4", "TITLE5", "DATE5", "TITLE6",
                               "DATE6", "TITLE7", "NUMBER7", "TITLE8", "NUMBER8",
                               "ACTIVEFLAG"])



    #Inserting into gdb
    cursor.insertRow([FAC_ID1, FAC_PARENT1, NEW_FAC_DESC1, DEPT_RESP1, FAC_TYPE1, FAC_SUBTYPE1, 

NEW_SUBTYPE_DESC1,
    LOC_CODE1, TITLE1, CODE1, NEWCODEDESC1, TITLE2, CODE2, NEWCODEDESC2, TITLE3, CODE3,NEWCODEDESC3, TITLE4,
    CODE4, NEWCODEDESC4, TITLE5, DATE5, TITLE6, DATE6, TITLE7, NUMBER7, TITLE8, NUMBER8, ACTIVEFLAG])


#Close the iSeries SQL cursor
iseries_cursor.close()


# Delete arcpy cursor object
del cursor

print 'WFAHREP table is updated.'
Improve this question

1 Answer 1

Reset to default
1

To determine where in your long script your performance bottlenecks are located, I recommend that you use Python's time module, in a way similar to how I did in this answer to another question.

Once you know where any bottlenecks are you should be able to isolate them into test code snippets that can be used to try and develop alternative (faster) coding patterns from.

Each of those code snippets could become the focus of a new Q&A here but I suspect that in the process of extracting them you may well figure out how to speed them up or to be able to conclude whether you really are hitting an ArcPy/Python/iSeries performance limitation.

Improve this answer
3
  • I took your advice and added the time function to the script. It takes @ 0.03 seconds to import each record, but there are 59K records to populate and it still takes @ 60min to complete the script. Is there any to speed this up? Also, when I run this script to export to Access it takes @2min to run.
    – Winston
    Commented Oct 29, 2014 at 15:33
  • Did some more work with the script and created the cursor outside of the loop and cut the processing time down to 3min and change. It seems that my script was creating a cursor for each record that was input.... that takes up a lot of time.
    – Winston
    Commented Oct 29, 2014 at 20:13
  • A 20x improvement sounds great!
    – PolyGeo ♦
    Commented Oct 29, 2014 at 20:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.