Re: [sqlite] Can I get help with db design for SQLite use?
On Tue, Apr 2, 2019 at 18:21 Tom Browder wrote: > On Tue, Apr 2, 2019 at 17:30 am...@juno.com wrote: > >> You might want to import everything into SQLite Studio > > It's SQLite Studio, and i used it a bit many years ago, but it has had a recent update so I will give it a try--and report results. And I also have the commercial Razor SQL to use. Thanks again. -Tom -To > or SQLite Suite I forget the exact name)--a freebie on the internet. I >> found it worked for me. In order to help others, I would appreciate it if >> you tell us on this usergroup how you made out. May it work for you. Peace! >> Alex > > > Thanks for the idea, Alex. > > I'm doing all programmatically at the moment (using a Perl 6 module: > DB::SQLite), but using one of those tools you mentioned would help in > design for sure! > > -Tom > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can I get help with db design for SQLite use?
On Tue, Apr 2, 2019 at 17:30 am...@juno.com wrote: > You might want to import everything into SQLite Studio or SQLite Suite I > forget the exact name)--a freebie on the internet. I found it worked for > me. In order to help others, I would appreciate it if you tell us on this > usergroup how you made out. May it work for you. Peace! Alex Thanks for the idea, Alex. I'm doing all programmatically at the moment (using a Perl 6 module: DB::SQLite), but using one of those tools you mentioned would help in design for sure! -Tom ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can I get help with db design for SQLite use?
You might want to import everything into SQLite Studio or SQLite Suite I forget the exact name)--a freebie on the internet. I found it worked for me. In order to help others, I would appreciate it if you tell us on this usergroup how you made out. May it work for you. Peace! Alex -- Original Message -- From: Tom Browder To: SQLite mailing list Subject: Re: [sqlite] Can I get help with db design for SQLite use? Date: Tue, 2 Apr 2019 15:07:58 -0500 On Tue, Apr 2, 2019 at 10:26 Simon Slavin wrote: > On 2 Apr 2019, at 3:48pm, Tom Browder wrote: > > I need help with a db design to be modeled for use with SQLite. The design > > uses foreign keys and I am just now using the foreign pragmas and other > > parts of SQLite to help me make it all work together. > > > > Is this list appropriate for presenting my design and requesting advice? ... > Yes, you can post a schema here (paste it into your message, attachments are > stripped) ... > Do be aware that almost all of us are just users like you. We are not > professional > designers, we argue with one-another, and you can't sue us if we give you bad > advice. Thanks, Simon, I will try to be a courteous and objective participant! -Tom ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users Our Hearts Go Out To Denzel Washington go.dedicatedoffers.com http://thirdpartyoffers.juno.com/TGL3131/5ca3e2679432962674b79st04vuc ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can I get help with db design for SQLite use?
On Tue, Apr 2, 2019 at 10:40 AM Don V Nielsen wrote: > > > Do be aware that almost all of us are just users like you. > > And be aware these guys are freakin brilliant. No lie. I don't doubt it at all, but thanks for the warning, Don! -Tom ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can I get help with db design for SQLite use?
On Tue, Apr 2, 2019 at 10:26 Simon Slavin wrote: > On 2 Apr 2019, at 3:48pm, Tom Browder wrote: > > I need help with a db design to be modeled for use with SQLite. The design > > uses foreign keys and I am just now using the foreign pragmas and other > > parts of SQLite to help me make it all work together. > > > > Is this list appropriate for presenting my design and requesting advice? ... > Yes, you can post a schema here (paste it into your message, attachments are > stripped) ... > Do be aware that almost all of us are just users like you. We are not > professional > designers, we argue with one-another, and you can't sue us if we give you bad > advice. Thanks, Simon, I will try to be a courteous and objective participant! -Tom ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can I get help with db design for SQLite use?
> Do be aware that almost all of us are just users like you. And be aware these guys are freakin brilliant. No lie. On Tue, Apr 2, 2019 at 10:26 AM Simon Slavin wrote: > On 2 Apr 2019, at 3:48pm, Tom Browder wrote: > > > I need help with a db design to be modeled for use with SQLite. The > design uses foreign keys and I am just now using the foreign pragmas and > other parts of SQLite to help me make it all work together. > > > > Is this list appropriate for presenting my design and requesting advice? > > Yes, you can post a schema here (paste it into your message, attachments > are stripped) and ask for advice on whether you have the right tables, > columns, indexes and keys. Be prepared to get the advice that you're doing > everything wrong and should rethink it all from scratch. > > Do be aware that almost all of us are just users like you. We are not > professional designers, we argue with one-another, and you can't sue us if > we give you bad advice. > ___ > 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
Re: [sqlite] Can I get help with db design for SQLite use?
On 2 Apr 2019, at 3:48pm, Tom Browder wrote: > I need help with a db design to be modeled for use with SQLite. The design > uses foreign keys and I am just now using the foreign pragmas and other parts > of SQLite to help me make it all work together. > > Is this list appropriate for presenting my design and requesting advice? Yes, you can post a schema here (paste it into your message, attachments are stripped) and ask for advice on whether you have the right tables, columns, indexes and keys. Be prepared to get the advice that you're doing everything wrong and should rethink it all from scratch. Do be aware that almost all of us are just users like you. We are not professional designers, we argue with one-another, and you can't sue us if we give you bad advice. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Can I get help with db design for SQLite use?
I need help with a db design to be modeled for use with SQLite. The design uses foreign keys and I am just now using the foreign pragmas and other parts of SQLite to help me make it all work together. Is this list appropriate for presenting my design and requesting advice? Best regards, -Tom ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Reporting two queries with performance regression
Hello, Thanks for the previous advice. We are reporting two interesting cases with different bisecting result using "sql-perf-fuzz". Here are the steps for reproducing our observations. All steps are same except for the link for downloading new test-cases: [Our test environment] * Ubuntu 16.04 machine "Linux sludge 4.4.0-116-generic #140-Ubuntu SMP Mon Feb 12 21:23:04 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux" * Database: TPC-C benchmark [Setup Test Environment] 1. build SQLite 3.27.2 (verion of Feb 2019) $ wget https://www.sqlite.org/2019/sqlite-src-3270200.zip $ unzip sqlite-src-3270200.zip $ mv sqlite-src-3270200 sqlite327 $ cd sqlite327 $ ./configure $ make $ cd .. 2. build SQLite 3.23.0 (verion of Apr 2018) $ wget https://www.sqlite.org/2018/sqlite-src-323.zip $ unzip sqlite-src-323.zip $ mv sqlite-src-323 sqlite323 $ cd sqlite323 $ ./configure $ make $ cd .. 3. download tpc-c for sqlite3 (scale-factor of 1) $ mkdir testcase $ cd testcase $ wget https://gts3.org/~/jjung/sqlite/tpcc_sqlite.tar.gz $ tar xzvf tpcc_sqlite.tar.gz ; download regression queries $ wget https://gts3.org/~/jjung/sqlite/report2.tar.gz $ tar xzvf report1.tar.gz $ cd .. 4. launch two SQLites - start $ sqlite327/sqlite3 testcase/test.db $ sqlite323/sqlite3 testcase/test.db - for each DB, set up timer sqlite> .timer on - copy and paste extracted queries Here’s the time taken to execute four SQL queries on old (v3.23) and newer version (v3.27.2) of SQLite (in milliseconds). We also try bisecting to know which commit activate the regression. +--++ | Query| Time | +--++ | 6.sql (v3.23)| 3 | | 6.sql (v3.27.2) |156 | +--++ | 19.sql (v3.23) |720 | | 19.sql (v3.27.2) | 1747 | +--++ 1) 6.sql shows x52 slow query execution. - bisect fossil commit: === 2018-07-26 === [57eb2abd5b] Generalize the constant propagation optimization so that it applies on every WHERE close, not just those that contain a subquery. This then demonstrates that the current implementation is inadequate since it does not take into account collating sequences. (user: drh tags: propagate-const-opt) 2) 19.sql shows x2.5 slow query execution - bisect fossil commit: === 2018-08-04 === [7d9072b027] Further logic simplifications that flow out of the omission of the column cache. (user: drh tags: omit-column-cache) Thanks for your support. Best regards, Jinho Jung ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] importing a large TSV file
On Mon, 1 Apr 2019 at 19:20, Domingo Alvarez Duarte wrote: > Hello Gert ! > > I normally do this (be aware that if there is a power outage the > database is screwed): > > === > > PRAGMA synchronous = OFF; > begin; > > --processing here > > commit; > PRAGMA synchronous = ON; > You can probably leave the pragma alone without overly affecting import time tbh. The main thing is putting all the work into one transaction, and at that point you're down to 2 or 3 sync() calls. I guess there's still value in not having to wait for the journal to hit disk though. Maybe even PRAGMA journal_mode = OFF would be appropriate. -Rowan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
