quote : "Is there a reason why you can't create a copy of the database and work with a copy?"
I never said I couldn't, and if I misled you I'm sorry. Of course I can play with the DB, since I'm in a dev environment, and I'm open to any alteration on the tables. I conceed I'm not giving away a lot information, but when I do I feel like there is not much to say or too add. quote:"Now, you need to create a primary/foreign keys and indexes on the tables you are creating. Then the execution time will improve even more" I will probably try that tomorrow and I'll tell you how that goes, but I'm not sure if that will be very effective since i activated the PRAGMA to create indexes automatically. I'm guessing it has created some (hopefully). Anyway, thank you all for having the patience to read and reply. 2016-09-07 18:52 GMT+02:00 Igor Korot <ikoro...@gmail.com>: > Hi, Laura, > > On Wed, Sep 7, 2016 at 12:25 PM, Laura BERGOENS > <laura.bergo...@imerir.com> wrote: > > Hi Dominique, > > > > I guess it's not, but I'm not quite sure on what I can share, I'm a > newbie > > in a professional environment. I would be more confortable if I ask my > boss > > first. In the meantime, all I can say is that there are no primary keys > nor > > foreign keys in the table, and no indexes at all. I activated the > > automatic_index PRAGMA. > > > > All tables look the same more or less : > > > > CREATE TABLE TABLEA (ID_A INTEGER,NUM_A INTEGER,NUM_GROUPE_A > INTEGER,TYPE_A > > VARCHAR(255),LIBELLE_A VARCHAR(255),NOM_A VARCHAR(255)); > > If you don't have keys nor indexes in any of the tables than the > query time will definitely be slower. > > Now let me ask you this: > Is there a reason why you can't create a copy of the database and work > with a copy? > Just open the Terminal and do: > > cp real_database.db my_database.db > > Then you can experiment with the database itself without screwing up > the real data. > Besides, that's how actual dev environment should be - you have access > to the development > database on which you do the experiments and then the DB Admin will > populate the schema > based on you analysis for production. > > Now, you need to create a primary/foreign keys and indexes on the > tables you are creating. > Then the execution time will improve even more. > > Try it and see. > > Then when you are ready ask the DB Admin to make those changes for > production database. > > Thank you. > > BTW, if its so scary, you can make the field name as field1, field2, > field3, etc. > That way the design of the database and the application will stay as > the company secret. > > Thank you. > > > > > 2016-09-07 18:13 GMT+02:00 Dominique Devienne <ddevie...@gmail.com>: > > > >> On Wed, Sep 7, 2016 at 6:00 PM, Laura BERGOENS < > laura.bergo...@imerir.com> > >> wrote: > >> > >> > Then I create real tables in the in-memory clone that contain the > content > >> > of the views : INSERT INTO tableA SELECT * from viewA > >> > > >> > >> What matters is how you create the tables, > >> and in particular what primary key and indexes you use on them. > >> > >> If you don't have indexes (or a PK) on the columns you're joining on, > >> it can't be as fast as it could be (and SQLite might end-up creating > >> those "indexes" on-the-fly, and recreate them on the next query, > etc...). > >> > >> So as advised, share your schema (see Richard's post), not your data, > >> and share your queries and explain query plan for your queries. > >> > >> You're schema is not confidential, is it? The data, sure. But the > schema??? > >> --DD > >> _______________________________________________ > >> sqlite-users mailing list > >> sqlite-users@mailinglists.sqlite.org > >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >> > > > > > > > > -- > > Laura BERGOENS > > Technicienne supérieure en Informatique et étudiante à l'IMERIR de > Perpignan > > > > *Institut Méditerranéen d'Étude etde Recherche en Informatique* > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Laura BERGOENS Technicienne supérieure en Informatique et étudiante à l'IMERIR de Perpignan *Institut Méditerranéen d'Étude etde Recherche en Informatique* _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
