· 6 years ago · Jun 15, 2019, 06:36 PM
1Tot (all) = 6.1800
2 Mean (all) = 0.0618
3 Mean (exi) = 0.0920
4 Mean (dne) = 0.0057
5 List elapsed = 3.4900
6 List total = 3.4900 (65 found)
7 SQL elapsed = 0.0200
8 SQL total = 0.0200 (65 found)
9----------
10 Tot (all) = 5.6300
11 Mean (all) = 0.0563
12 Mean (exi) = 0.0832
13 Mean (dne) = 0.0063
14 List elapsed = 2.6800
15 List total = 2.6800 (65 found)
16 SQL elapsed = 0.0100
17 SQL total = 0.0100 (65 found)
18----------
19 Tot (all) = 6.5800
20 Mean (all) = 0.0658
21 Mean (exi) = 0.0986
22 Mean (dne) = 0.0049
23 List elapsed = 3.3600
24 List total = 3.3600 (65 found)
25 SQL elapsed = 0.0100
26 SQL total = 0.0100 (65 found)
27
28import math
29import os
30import random
31from datetime import datetime
32
33print("Importing arcpy...")
34import arcpy
35
36# -----------------------------------
37def findTables(names,schema,fmt,count):
38 found = []
39 d = {}
40 for name in names:
41 parts = name.split('.')
42 d[parts[2]] = name
43 for i in range(count):
44 table = fmt.format(i)
45 if (table in d):
46 found.append(table)
47 return found
48# -----------------------------------
49
50verbose=False
51keep=65
52fudge=keep-12
53total=100
54schema=""
55geomcol="geom"
56tableFmt="xxtmp_table{:02d}"
57random.seed(42)
58
59dropStatement = "DROP TABLE IF EXISTS {:s}{:s}"
60
61createStatement6 = """CREATE TABLE {:s}{:s} (
62 objectid serial NOT NULL,
63 col1 smallint NULL,
64 col2 integer NULL,
65 col3 real NULL,
66 col4 varchar(20) NULL,
67 {:<12s} geometry NULL,
68 CONSTRAINT {:s}_pkey PRIMARY KEY (objectid),
69 CONSTRAINT enforce_srid_{:s} CHECK (st_srid({:s}) = 4326)
70)
71WITH (
72 OIDS=FALSE
73)"""
74
75createStatement2 = """CREATE TABLE {:s}{:s} (
76 objectid serial NOT NULL,
77 {:<12s} geometry NULL,
78 CONSTRAINT {:s}_pkey PRIMARY KEY (objectid),
79 CONSTRAINT enforce_srid_{:s} CHECK (st_srid({:s}) = 4326)
80)
81WITH (
82 OIDS=FALSE
83)"""
84
85createStatement40 = """CREATE TABLE {:s}{:s} (
86 objectid serial NOT NULL,
87 col1 smallint NULL,
88 col2 integer NULL,
89 col3 real NULL,
90 col4 varchar(20) NULL,
91 col5 varchar(20) NULL,
92 col6 smallint NULL,
93 col7 integer NULL,
94 col8 real NULL,
95 col9 varchar(20) NULL,
96 col10 varchar(20) NULL,
97 col11 smallint NULL,
98 col12 integer NULL,
99 col13 real NULL,
100 col14 varchar(20) NULL,
101 col15 varchar(20) NULL,
102 col16 smallint NULL,
103 col17 integer NULL,
104 col18 real NULL,
105 col19 varchar(20) NULL,
106 col20 varchar(20) NULL,
107 col21 smallint NULL,
108 col22 integer NULL,
109 col23 real NULL,
110 col24 varchar(20) NULL,
111 col25 varchar(20) NULL,
112 col26 smallint NULL,
113 col27 integer NULL,
114 col28 real NULL,
115 col29 varchar(20) NULL,
116 col30 varchar(20) NULL,
117 col31 smallint NULL,
118 col32 integer NULL,
119 col33 real NULL,
120 col34 varchar(20) NULL,
121 col35 varchar(20) NULL,
122 col36 smallint NULL,
123 col37 integer NULL,
124 col38 real NULL,
125 {:<12s} geometry NULL,
126 CONSTRAINT {:s}_pkey PRIMARY KEY (objectid),
127 CONSTRAINT enforce_srid_{:s} CHECK (st_srid({:s}) = 4326)
128)
129WITH (
130 OIDS=FALSE
131)"""
132
133listStatement="""SELECT (table_catalog ||'.' || table_schema || '.' || table_name)::varchar(100) as name
134FROM information_schema.tables
135WHERE table_type = 'BASE TABLE'
136 AND table_schema NOT IN ('pg_catalog', 'information_schema')"""
137
138conn = os.path.join(os.getcwd(),"bench.sde")
139cursor = arcpy.ArcSDESQLExecute(conn)
140
141for createStatement in [createStatement6, createStatement2, createStatement40]:
142 count = 0
143 print '----------'
144 # .. (re)Create tables
145 start = datetime.datetime.now()
146 created=[]
147 for i in range(total):
148 if (math.floor(random.random() * 100) < fudge or (total - i) < (total - keep)):
149 table = tableFmt.format(i)
150
151 sql = dropStatement.format(schema,table)
152 if (verbose): print("{:s};".format(sql))
153 cursor.execute(sql)
154
155 sql = createStatement.format(schema,table,geomcol,table,geomcol,geomcol)
156 if (verbose): print("{:s};n".format(sql))
157 cursor.execute(sql)
158
159 created.append(table)
160 count += 1
161 if (count >= keep): break
162
163 elapsed = (datetime.datetime.now() - start).total_seconds()
164 #print("{:>16s} = {:.4f} secs".format("Prep",elapsed))
165
166 # .. Measure Exists
167
168 allSum = 0.0
169 exiSum = 0.0
170 dneSum = 0.0
171 for i in range(total):
172 table = tableFmt.format(i)
173 start = datetime.datetime.now()
174 doesExist = arcpy.Exists(os.path.join(conn,"{:s}{:s}".format(schema,table)))
175 elapsed = (datetime.datetime.now() - start).total_seconds()
176 allSum += elapsed
177 if (doesExist):
178 exiSum += elapsed
179 else:
180 dneSum += elapsed
181 if (verbose): print("{:s} ({:s})".format(table,"Y" if doesExist else "N"))
182
183 print("{:>16s} = {:.4f}".format("Tot (all)",allSum))
184 print("{:>16s} = {:.4f}".format("Mean (all)",allSum / float(total)))
185 print("{:>16s} = {:.4f}".format("Mean (exi)",exiSum / float(keep)))
186 print("{:>16s} = {:.4f}".format("Mean (dne)",dneSum / float(total-keep)))
187
188 # .. Measure ListFC
189
190 arcpy.env.workspace = conn
191 start = datetime.datetime.now()
192 allTables = arcpy.ListFeatureClasses()
193 elapsed = (datetime.datetime.now() - start).total_seconds()
194
195 print("{:>16s} = {:.4f}".format("List elapsed",elapsed))
196 start = datetime.datetime.now()
197 found = findTables(allTables,schema,tableFmt,total)
198 elapsed += (datetime.datetime.now() - start).total_seconds()
199 print("{:>16s} = {:.4f} ({:d} found)".format("List total",elapsed,len(found)))
200
201 # .. Measure SQL
202
203 start = datetime.datetime.now()
204 sql = listStatement
205 allTables = [row[0] for row in cursor.execute(sql)]
206 elapsed = (datetime.datetime.now() - start).total_seconds()
207
208 print("{:>16s} = {:.4f}".format("SQL elapsed",elapsed))
209 start = datetime.datetime.now()
210 found = findTables(allTables,schema,tableFmt,total)
211 elapsed += (datetime.datetime.now() - start).total_seconds()
212 print("{:>16s} = {:.4f} ({:d} found)".format("SQL total",elapsed,len(found)))
213
214 # .. Cleanup
215
216 start = datetime.datetime.now()
217 for table in created:
218 sql = dropStatement.format(schema,table)
219 if (verbose): print("{:s};".format(sql))
220 cursor.execute(sql)
221 elapsed = (datetime.datetime.now() - start).total_seconds()
222 #print("{:>16s} = {:.4f} secs".format("Cleanup",elapsed))
223
224cursor = None
225
226#EOF