On 2 Jun 2013, at 8:26pm, James Mahon <[email protected]> wrote:
> I'm trying to find the best method to create new tables in SQLite derived
> from a select statement on an existing SQLite table. I've currently tried
> the two methods, one in Python and one in the SQLite command shell (both
> illustrated below). Although I generally prefer to code in Python, I find
> it much slower. I also find the SQLite command line much slower when I use
> the INSERT INTO ... SELECT query. What are best coding practices in
> building derived SQLite tables?
The delay is partly the effort of moving all the data about and partly the task
of searching through your panels table for rows where sales >= 30000. All of
your methods would be faster if you already had an index on panel.panel(sales)
. If you're going to do this more than once and speed is an issue it might be
an idea to make that index.
> sales decimal(30,4)
SQLite doesn't have a decimal type. It is interpreting these numbers as REAL
numbers and it will not truncate them to four digits. If you are storing
numbers which are inherently integer define the columns as INTEGER and multiply
the numbers by (in your case) 10000 before storing them. This will speed up
everything including searching, sorting and maths, but only a bit.
Lastly, the question of why you need to make this second table anyway. If
you're just to be reading from it (rather than modifying it) then it will be
more efficient to make a VIEW. Add the index I wrote above then do something
like
CREATE VIEW clients AS
SELECT
cust_id,
year,
clerk_id,
sales
FROM panel.panel
WHERE sales >= 30000;
Note that creating the VIEW does not duplicate the data in the original table.
It's a way of saving a SELECT statement for convenient use, not a way of
copying the data from the table. Consequently, creating the view takes hardly
any time. You might want your view to be temporary. Read about it here:
<http://www.sqlite.org/lang_createview.html>
Simon.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
