Re: [sqlite] DB Partitioning and Performance

[Michael Hari]

Thanks for the reply Simon. 

We have database pooling since our databases are read only and we wanted to 
take advantage of multithreading. Each database connection had the temp view 
defined, but I saw a huge performance drop when it came to queries.

I’m going to try sub-selects for now with the suggestion from R Smith that I 
filter those sub-selects. Hopefully it works. 



Thanks Simon and R Smith!

On 5/27/16, 5:29 AM, "sqlite-users-boun...@mailinglists.sqlite.org on behalf of 
Simon Slavin" <sqlite-users-boun...@mailinglists.sqlite.org on behalf of 
slav...@bigfraud.org> wrote:

>
>On 27 May 2016, at 3:17am, Michael Hari <michael.h...@slalom.com> wrote:
>
>> I have a database that contains 2014,2015 and 2016’s worth of data broken 
>> down by year and quarter. In total, this db was 2.36 GB. Because of a 
>> replication requirement where the DB has to be under 2 GB, I’ve split the DB 
>> into 3 smaller databases by year (2014.db,2015.db,2016.db).
>> 
>> I would need to access at most two databases in one query for a year over 
>> year calculation over 6 quarters. Do I have the same performance as having 
>> one table with all the data if I write my sql this way?
>> 
>> Attach 2014.db as 2014
>> Attach 2015.db as 2015
>> 
>> (..Calculation..) FROM (SELECT * FROM 2014.datatable  UNION ALL SELECT * 
>> FROM 2015.datatable)
>> 
>> You can assume the separate databases have the same indexing as the single 
>> db, where it’s indexed by year and quarter.
>
>Thank you for your very clear explanation of your setup and your question, 
>which has saved lots of back-and-forth.  Your performance will be a little 
>slower than it would be if all the data was in one database file, but not 
>much.  Given what you're doing and why you're doing it I think you have found 
>a good solution and would not do it any other way.  A couple of notes:
>
>A) Using a database alias that starts with a digit makes me uncomfortable.  I 
>can't point at any documentation or particular problem this will cause but if 
>I was doing it I'd put at least a 'Y' for 'year' in front of the database 
>name, as in "ATTACH 2014.db AS Y2014".
>
>B) For equal treatment you may want to create an empty database and use that 
>as the one you connect to.  Then attach the yearly databases to this 
>connection including the one for the current year.  This may simplify what 
>needs to be done at year-end.
>
>C) Once you have attached all your databases you can create a view which will 
>simplify your access to the data:
>
>CREATE TEMPORARY VIEW datatableall AS SELECT * FROM Y2014.datatable UNION ALL 
>SELECT * FROM Y2015.datatable UNION ALL SELECT * FROM Y2016.datatable
>
>From then on you don't need to mention the separate tables in code which 
>consults the tables, just treat datatableall as if it's one table.
>
>D) VACUUM databases which will no longer change.  VACUUM the current year's 
>database at year-end.
>
>Simon.
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers=CwIGaQ=fa_WZs7nNMvOIDyLmzi2sMVHyyC4hN9WQl29lWJQ5Y4=Za210bkAr8T8OGv7-RSux2dLtMfHgyYbTEN9AO6LXXg=Iv0ThknGqZ6jlx5nm2YCXf3G5PvjrMtxSMJCSiOMmcI=T4x-97XOSoM43DLhZGThEZOOht0kLdrxKTkHQv6_Sd0=
> 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] DB Partitioning and Performance

[Michael Hari]

I have a database that contains 2014,2015 and 2016’s worth of data broken down 
by year and quarter. In total, this db was 2.36 GB. Because of a replication 
requirement where the DB has to be under 2 GB, I’ve split the DB into 3 smaller 
databases by year (2014.db,2015.db,2016.db).

I would need to access at most two databases in one query for a year over year 
calculation over 6 quarters. Do I have the same performance as having one table 
with all the data if I write my sql this way?

Attach 2014.db as 2014
Attach 2015.db as 2015

(..Calculation..) FROM (SELECT * FROM 2014.datatable  UNION ALL SELECT * FROM 
2015.datatable)

You can assume the separate databases have the same indexing as the single db, 
where it’s indexed by year and quarter.

Thanks,
Michael
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Attach Database and Database pooling

[Michael Hari]

Never mind, there was an error in my code.




On 5/26/16, 11:52 AM, "sqlite-users-boun...@mailinglists.sqlite.org on behalf 
of Michael Hari" <sqlite-users-boun...@mailinglists.sqlite.org on behalf of 
michael.h...@slalom.com> wrote:

>Hi All,
>
>
>
>I’m trying to preallocate about 20 sqlite connections each with the same 
>“ATTACH DB” command. I’m getting “database x already in use” errors. Is this 
>not possible to do? Can each independent X connection not have the same 
>attached DB command? If not, what’s the preferred approach?
>
>
>
>Thanks,
>
>Michael
>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers=CwIGaQ=fa_WZs7nNMvOIDyLmzi2sMVHyyC4hN9WQl29lWJQ5Y4=Za210bkAr8T8OGv7-RSux2dLtMfHgyYbTEN9AO6LXXg=HhCYpnNqJbSVCUsukMrpMTa_qXf1gNz3onTzaC3UKsQ=7BA0OPqKASv6CXn2c9tmtmY8AXKQ3Ye2cuwdz6tIYR0=
> 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Attach Database and Database pooling

[Michael Hari]

Hi All,

I’m trying to preallocate about 20 sqlite connections each with the same 
“ATTACH DB” command. I’m getting “database x already in use” errors. Is this 
not possible to do? Can each independent X connection not have the same 
attached DB command? If not, what’s the preferred approach?

Thanks,
Michael
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Binary Difference in Sqlite database files instead of SQLdiff and patch ?

[Michael Hari]

What about if we shard the tables in the database across multiple databases? Is 
that possible to do? Does SQLite support the concept of sharding?

Thanks,
Michael


On 5/24/16, 10:20 AM, "sqlite-users-boun...@mailinglists.sqlite.org on behalf 
of Simon Slavin" <sqlite-users-boun...@mailinglists.sqlite.org on behalf of 
slav...@bigfraud.org> wrote:

>
>On 24 May 2016, at 6:04pm, Michael Hari <michael.h...@slalom.com> wrote:
>
>> I’ve come to the conclusion that I would need to “split the file into two 
>> databases” as well. The challenge here is that the larger db will have to 
>> have a limit of 2 GB and will need to make a new db when it hits that limit. 
>> It would have to split it into multiple Dbs as the data grows (so 
>> db1.sqlite, db2.sqlite, etc.). Would you happen to know any strategies to 
>> accomplish this in sqlite? Either through SQL or scripting?
>
>Create a versioning system for your database which is not the same as the 
>version of your application.  Continue to use Sparkle for your application but 
>not for the database file.
>
>Upgrading your database from one version to another can be done in three ways:
>
>A) Distribute an additional .sqlite file.  To update the local database have 
>the application read the data from this file and add it to the local copy of 
>the database.
>
>B) Distribute a .zipped text file containing the SQL commands needed to update 
>the local copy of the database.  To update the local database have the 
>application read the commands from this file and execute them.
>
>C) Distribute a .zip containing a .csv file for each table.  To update the 
>local database have the application read the text from this file and convert 
>it to INSERT commands.
>
>Simon.
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers=CwIGaQ=fa_WZs7nNMvOIDyLmzi2sMVHyyC4hN9WQl29lWJQ5Y4=Za210bkAr8T8OGv7-RSux2dLtMfHgyYbTEN9AO6LXXg=ahSUBK1T7j-1qWdH9sKUO09_FAWT0Eeb1s32vYabAg0=9lhqAP5mAfvcMjMuMHs6L0HxVgtTW0uXHq79XI8KeYg=
> 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Binary Difference in Sqlite database files instead of SQLdiff and patch ?

[Michael Hari]

Hi Simon,

We don’t want to zip up the entire db unfortunately. It would be around 250 MB 
zipped versus having Sparkle update with a 38 MB patch. (Bandwidth costs are 
important for us)

I’ve come to the conclusion that I would need to “split the file into two 
databases” as well. The challenge here is that the larger db will have to have 
a limit of 2 GB and will need to make a new db when it hits that limit. It 
would have to split it into multiple Dbs as the data grows (so db1.sqlite, 
db2.sqlite, etc.). Would you happen to know any strategies to accomplish this 
in sqlite? Either through SQL or scripting?

Thanks,
Michael


On 5/23/16, 1:46 PM, "sqlite-users-boun...@mailinglists.sqlite.org on behalf of 
Simon Slavin"  wrote:

>
>On 23 May 2016, at 9:08pm, Piyush Shah  wrote:
>
>> We know about sqldiff 
>> https://urldefense.proofpoint.com/v2/url?u=https-3A__www.sqlite.org_sqldiff.html=CwICAg=fa_WZs7nNMvOIDyLmzi2sMVHyyC4hN9WQl29lWJQ5Y4=Za210bkAr8T8OGv7-RSux2dLtMfHgyYbTEN9AO6LXXg=H8MM4zcxx8e1CF2D98PKVDGp8SvCTyw-dv7XoQ5KPw4=8qlz1JEuXvYSnt4FvMj1V_UVQ2pQqaCG78j0tbSf3pU=
>>and would use it to generate diffs and patch them but we were hoping 
>> there was a way to do do a binary diff of the database because otherwise we 
>> will have to figure out a way for the client to run the sqldiff utility to 
>> patch the database.
>
>There's no easy way to integrate Sparkle and sqldiff so that they work 
>together like that.  The amount of work it would take would be equivalent to 
>writing your own tool which updated the database across the internet.
>
>If the database file is small, just let Sparkle update it with the other 
>components of the application.  If the database file is big, it may be worth 
>doing work on it.
>
>Does the database file compress well as a ZIP file ?  Then distribute it as a 
>.zip.  On startup, detect if the decompressed version is not present, or has a 
>datestamp before the .zip version.
>
>Do you have roughly equal parts of the file taken up with tables which never 
>change and tables which change with each update ?  Then split the file up into 
>two database files and use ATTACH to access them both via one connection.
>
>Simon.
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers=CwICAg=fa_WZs7nNMvOIDyLmzi2sMVHyyC4hN9WQl29lWJQ5Y4=Za210bkAr8T8OGv7-RSux2dLtMfHgyYbTEN9AO6LXXg=H8MM4zcxx8e1CF2D98PKVDGp8SvCTyw-dv7XoQ5KPw4=deDylKqCP4ECaSOv3XoqH0xfznS3YHz3ZlsPDbzxbXE=
> 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users