| 248 |
return self.ReadRowAsDict(row)[self.column_map[col].name] |
return self.ReadRowAsDict(row)[self.column_map[col].name] |
| 249 |
|
|
| 250 |
def ValueRange(self, col): |
def ValueRange(self, col): |
| 251 |
|
# Performance notes: |
| 252 |
|
# |
| 253 |
|
# In sqlite 2.8.6 the min and max aggregate functions can use an |
| 254 |
|
# index but only when used as the only expression in the select |
| 255 |
|
# statement (i.e. 'select min(col), max(col) from tbl;' will not |
| 256 |
|
# use the index but 'select min(col) from tbl;' will) so we |
| 257 |
|
# query the minimum and maximum separately. |
| 258 |
|
# |
| 259 |
|
# With the separate statements we can take advantage of an index |
| 260 |
|
# if it exists. If the index doesn't exist, creating it first |
| 261 |
|
# and then using it in the query is slower than the queries |
| 262 |
|
# without an index. Creating the index is only an advantage if |
| 263 |
|
# the queries are performed multiple times. With the current use |
| 264 |
|
# patterns where ValueRange is only used occasionally by the |
| 265 |
|
# classification generation dialog creating the index only for |
| 266 |
|
# this usage is not really worth it, so we don't. |
| 267 |
col = self.column_map[col] |
col = self.column_map[col] |
| 268 |
iname = col.internal_name |
iname = col.internal_name |
| 269 |
min, max = self.db.execute("SELECT min(%s), max(%s) FROM %s;" |
min = self.db.execute("SELECT min(%s) FROM %s;" |
| 270 |
% (iname, iname, self.tablename)) |
% (iname, self.tablename))[0] |
| 271 |
|
max = self.db.execute("SELECT max(%s) FROM %s;" |
| 272 |
|
% (iname, self.tablename))[0] |
| 273 |
converter = type_converter_map[col.type] |
converter = type_converter_map[col.type] |
| 274 |
return (converter(min), converter(max)) |
return (converter(min), converter(max)) |
| 275 |
|
|
| 276 |
def UniqueValues(self, col): |
def UniqueValues(self, col): |
| 277 |
|
# Performance notes: |
| 278 |
|
# |
| 279 |
|
# In sqlite 2.8.6 there doesn't seem to be a way to query the |
| 280 |
|
# unique items that uses an index. I've tried |
| 281 |
|
# |
| 282 |
|
# SELECT col FROM tbl GROUP BY col; |
| 283 |
|
# |
| 284 |
|
# and |
| 285 |
|
# |
| 286 |
|
# SELECT DISTINCT col FROM tbl; |
| 287 |
|
# |
| 288 |
|
# and in both cases the index is not used. If the index isn't |
| 289 |
|
# used it doesn't make sense to call self.ensure_index. |
| 290 |
iname = self.column_map[col].internal_name |
iname = self.column_map[col].internal_name |
| 291 |
cursor = self.db.cursor() |
cursor = self.db.cursor() |
| 292 |
cursor.execute("SELECT %s FROM %s GROUP BY %s;" |
cursor.execute("SELECT %s FROM %s GROUP BY %s;" |
| 610 |
return self.t_table |
return self.t_table |
| 611 |
|
|
| 612 |
def ValueRange(self, col): |
def ValueRange(self, col): |
| 613 |
if self.t_table is None: |
# Performance of sqlite vs. DBF for this method: |
| 614 |
self.copy_to_transient() |
# |
| 615 |
return self.t_table.ValueRange(col) |
# If the table has been copied to the sqlite database it's |
| 616 |
|
# faster to use it even if there is no index on that column. |
| 617 |
|
# Otherwise it's faster to simply loop through all rows in the |
| 618 |
|
# DBF file. Copying the data to the sqlite database can take |
| 619 |
|
# very long for large amounts of data |
| 620 |
|
# |
| 621 |
|
# Of course if the table is not a DBF file the issue could be |
| 622 |
|
# different, although copying the data into sqlite first will |
| 623 |
|
# likely always be slower than simply querying the non-sqlite |
| 624 |
|
# table directly. Currently only DBFfiles and memory tables are |
| 625 |
|
# used as the underlying non-sqlite table, though. |
| 626 |
|
if self.t_table is not None: |
| 627 |
|
return self.t_table.ValueRange(col) |
| 628 |
|
else: |
| 629 |
|
return self.table.ValueRange(col) |
| 630 |
|
|
| 631 |
def UniqueValues(self, col): |
def UniqueValues(self, col): |
| 632 |
if self.t_table is None: |
# The performance trade-offs for this method are basically the |
| 633 |
self.copy_to_transient() |
# same as for ValueRange except that currently there doesn't |
| 634 |
return self.t_table.UniqueValues(col) |
# seem to be a way to take advantage of indexes in this case in |
| 635 |
|
# sqlite. However, but it's still faster to query the transient |
| 636 |
|
# table if it already exists. |
| 637 |
|
if self.t_table is not None: |
| 638 |
|
return self.t_table.UniqueValues(col) |
| 639 |
|
else: |
| 640 |
|
return self.table.UniqueValues(col) |
| 641 |
|
|
| 642 |
def SimpleQuery(self, left, comparison, right): |
def SimpleQuery(self, left, comparison, right): |
| 643 |
if self.t_table is None: |
if self.t_table is None: |
Parent Directory
| 
Revision Log
| 
Patch