Opened 10 years ago
Last modified 6 years ago
#2587 new enhancement
copying attribute tables very inefficient if tables are in same database
Reported by: | mlennert | Owned by: | |
---|---|---|---|
Priority: | normal | Milestone: | 7.6.2 |
Component: | Database | Version: | svn-trunk |
Keywords: | libdb copy table | Cc: | |
CPU: | Unspecified | Platform: | Unspecified |
Description
The function copy_table in lib/db/dbmi_client/copy_tab.c seems very inefficient to me when both the old and the new table are in the same database and it is a database with real SQL functionalities. IIUC, the function actually selects all rows from the input table into a cursor and the inserts them, row by row, into the output table. With a large table this takes a long time.
Using a simple "CREATE TABLE new_table AS SELECT [*, ListOfColumns] FROM old_table WHERE [Conditions]" is much faster !
So, it would be a great addition if for the relevant drivers, such as SQLite and PostgreSQL, could use this approach.
P.S. There is no LibDB component in the tracker. Don't know if there should be...
Change History (10)
comment:1 by , 9 years ago
comment:4 by , 7 years ago
Milestone: | 7.2.1 → 7.2.2 |
---|
comment:5 by , 7 years ago
Milestone: | 7.2.2 → 7.4.0 |
---|
All enhancement tickets should be assigned to 7.4 milestone.
comment:7 by , 6 years ago
Milestone: | 7.4.1 → 7.4.2 |
---|
comment:8 by , 6 years ago
Milestone: | 7.4.2 → 7.6.0 |
---|
All enhancement tickets should be assigned to 7.6 milestone.
To reinforce this:
I have a vector map with 881,120 points. I have to loop over this map, extracting points according to one attribute. When I use v.extract to extract the points and to create the table it takes quite a long time, most of it in "writing attributes":
i.e. 20s, although this only extracts 93 points !
Using a script that extracts the points without attribute table and then creates the table using different tools, it is much quicker. Here's the script (t.sh):
The result is identical except that cat is renamed cat_ by db.in.ogr...