test insert and join vs in where clause

test.py

#!/usr/bin/env python
# * coding: utf8 *
'''
test.py

A module that tests inserting and joining records
'''

import arcpy
from os.path import basename
from os.path import exists
from os.path import join
from time import clock


source_workspace = 'C:\\Projects\\Cruft\\InsertAndJoin\\SourceData.gdb'
source_table = 'AddressPoints'
destination_workspace = 'C:\\Projects\\Cruft\\InsertAndJoin\\DestinationData.gdb'
destination_table = 'to_be_joined'
destination = join(destination_workspace, destination_table)
source = join(source_workspace, source_table)
id_count = 100000


def _create_workspace(workspace):
    if exists(workspace):
        return

    gdb_name = basename(workspace)
    workspace = workspace.replace(gdb_name, '')

    arcpy.CreateFileGDB_management(workspace, gdb_name, 'CURRENT')


def _create_join_table(workspace, table):
    if arcpy.Exists(destination):
        arcpy.Delete_management(destination)

    arcpy.CreateFeatureclass_management(workspace, table, geometry_type='POINT')
    arcpy.AddField_management(destination, 'Join', 'LONG')


def format_time(seconds):
    minute = 60.00
    hour = 60.00 * minute

    if seconds < 30:
        return '{} ms'.format(int(seconds * 1000))

    if seconds < 90:
        return '{} seconds'.format(round(seconds, 2))

    if seconds < 90 * minute:
        return '{} minutes'.format(round(seconds / minute, 2))

    return '{} hours'.format(round(seconds / hour, 2))


print('starting join and insert test')
_create_workspace(destination_workspace)

start_seconds = clock()
_create_join_table()
print('create temp table {}'.format(format_time(clock() - start_seconds)))

i = 1
temp_timer = clock()
with arcpy.da.SearchCursor(source, ['OID@']) as search,\
        arcpy.da.InsertCursor(destination, 'Join') as insert:
    for row in search:
        if i > id_count:
            break

        i += 1

        insert.insertRow(row)

print('insert {} rows into temp table {}'.format(id_count, format_time(clock() - temp_timer)))

temp_timer = clock()


arcpy.JoinField_management(destination, 'Join', source, 'OBJECTID')

print('join {}'.format(format_time(clock() - temp_timer)))
print('total operation {}'.format(format_time(clock() - start_seconds)))