test insert and join vs in where clause
#!/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))) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment