[sqlite] Redundant link in documentation
Hi, In the following page: http://sqlite.org/c3ref/bind_parameter_index.html there is a link to itself: sqlite3_bind_parameter_index(). Maybe it should have been a link to: http://sqlite.org/c3ref/bind_parameter_name.html ? Regards.
[sqlite] Read data from database slow the first time but then very fast thereafter. How can I improve the speed for the first time read?
On 4 Aug 2015, at 6:35pm, Scott Hess wrote: > BUT, keep in mind that you might find that you've just moved the 1min time > from query time to preload time, in which case you've just complexified > without adding anything. That's why I didn't give any advice. /Something/ has to load those sectors from main storage and whenever that is done, that's when the extra 57 seconds is going to happen. It could be tested, of course. Make a source file which is ten times that length and see if it takes 10 minutes the first time and 30 seconds subsequent times. I doubt that a database with 10 million rows could all fit in the cache. Simon.
[sqlite] Compile Warning from Universal Windows App using Visual Studio 2015
Please update to the version updated on 2015-07-31 and try again. Sent from my iPhone > On Aug 4, 2015, at 7:19 PM, Weidong Shen wrote: > > Hi, > > I created a test UWP project using Visual Studio 2015/Windows 10 and added a > reference to "SQLite.UAP.2015, Version=3.8.11.1". When compiling the project, > I get the following warning message: > warning MSB3781: The SDK "SQLite.UAP.2015, Version=3.8.11.1" depends on the > following SDK(s) "Microsoft.VCLibs.AppLocal, version=14.0", which have not > been added to the project or were not found. Please ensure that you add these > dependencies to your project or you may experience runtime issues. You can > add dependencies to your project through the Reference Manager. > > I tried to add the missing reference, and I can only find a reference of > "Microsoft.VCLibs, Version=14.0" from "C:\Program Files (x86)\Microsoft > SDKs\Windows Kits\10\ExtensionSDKs\Microsoft.VCLibs\14.0\". There is no > reference to "Microsoft.VCLibs.AppLocal, version=14.0" with Windows 10 and > Visual Studio 2015. so, even if I added the reference of "Microsoft.VCLibs, > Version=14.0", the build warning message is still there. > I modified the file "C:\Program Files (x86)\Microsoft > SDKs\UAP\v0.8.0.0\ExtensionSDKs\SQLite.UAP.2015\3.8.11.1\SDKManifest.xml", > and replaced "Microsoft.VCLibs.AppLocal, version=14.0" with > "Microsoft.VCLibs, Version=14.0". The build warning message goes away. > Is this a bug with "SQLite.UAP.2015, Version=3.8.11.1"? > Thanks, > Weidong Shen > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Read data from database slow the first time but then very fast thereafter. How can I improve the speed for the first time read?
On 3 Aug 2015, at 1:58pm, Linquan Bai wrote: > I am trying to read large data from the database about 1 million records. > It takes around 1min for the first time read. But if I do the same process > thereafter, the time is significantly reduced to 3 seconds. How can I get a > fast speed for the first time read? You can't. Some part of your computer has pulled that data into cache, and it's still in the cache when you run the process again, so it the data doesn't need to be fetched from disk again. If you tell us how big an average row is (how many bytes of data) then we can tell you whether 1 million rows in 1 minute is a reasonable time. But my guess is that that's more or less the time you'd expect. Simon.
[sqlite] SQLite EF provider
Hi Michel. A fully featured EF provider is available from DevArt. https://www.devart.com/dotconnect/sqlite/ /Erik Sendt fra Windows Mail
[sqlite] Numerics bigger than 32-bit and sqlite as 64-bit TCL extension
2015-07-28 11:52 GMT-05:00 Richard Hipp :
> On 7/28/15, Rafa? Ponikwia wrote:
>> Hi,
>> I'm using sqlite3 TCL extension from Teapot (version 64-bit). When I try
>> to insert number bigger than signed 32-bit it inserts wrong number to
>> database.
>> % sqlite3 db C:/tmp/test.sqlite
>> % db eval {
CREATE TABLE test (num NUMERIC);
INSERT INTO test (num) VALUES ($l1), ($l2), ($l3);
SELECT * FROM test;
}
>> 2147483647 2147483649 -2147483647
>>
>
> I get the correct answer (2147483647 2147483649 2147483649) when I try
> this on 64-bit Ubuntu.
Tried this on both cygwin64 and mingw-w64 (64-bit), getting the same
correct answer in both environments. e.g.:
>tclsh86
% set tcl_platform(pointerSize)
8
% package require sqlite3
3.8.11.1
% info loaded
{C:/cygwin64/lib/sqlite3.8.11.1/sqlite38111.dll Sqlite3}
% set l1 [expr {2 ** 31 - 1}]
2147483647
% set l2 2147483649
2147483649
% set l3 [expr {$l1 + 2}]
2147483649
% sqlite3 db test.sqlite
% db eval {
CREATE TABLE test (num NUMERIC);
INSERT INTO test (num) VALUES ($l1), ($l2), ($l3);
SELECT * FROM test;
}
2147483647 2147483649 2147483649
%
@Andreas: could this be a compiler issue?
Regards,
Jan Nijtmans
[sqlite] Numerics bigger than 32-bit and sqlite as 64-bit TCL extension
On Tue, Aug 4, 2015 at 12:34 PM, Jan Nijtmans wrote:
> 2015-07-28 11:52 GMT-05:00 Richard Hipp :
>> On 7/28/15, Rafa? Ponikwia wrote:
>>> Hi,
>>> I'm using sqlite3 TCL extension from Teapot (version 64-bit). When I try
>>> to insert number bigger than signed 32-bit it inserts wrong number to
>>> database.
>>> % sqlite3 db C:/tmp/test.sqlite
>>> % db eval {
> CREATE TABLE test (num NUMERIC);
> INSERT INTO test (num) VALUES ($l1), ($l2), ($l3);
> SELECT * FROM test;
> }
>>> 2147483647 2147483649 -2147483647
>>>
>>
>> I get the correct answer (2147483647 2147483649 2147483649) when I try
>> this on 64-bit Ubuntu.
>
> Tried this on both cygwin64 and mingw-w64 (64-bit), getting the same
> correct answer in both environments. e.g.:
> >tclsh86
> % set tcl_platform(pointerSize)
> 8
> % package require sqlite3
> 3.8.11.1
> % info loaded
> {C:/cygwin64/lib/sqlite3.8.11.1/sqlite38111.dll Sqlite3}
> % set l1 [expr {2 ** 31 - 1}]
> 2147483647
> % set l2 2147483649
> 2147483649
> % set l3 [expr {$l1 + 2}]
> 2147483649
> % sqlite3 db test.sqlite
> % db eval {
> CREATE TABLE test (num NUMERIC);
> INSERT INTO test (num) VALUES ($l1), ($l2), ($l3);
> SELECT * FROM test;
> }
> 2147483647 2147483649 2147483649
> %
>
> @Andreas: could this be a compiler issue?
Maybe !?
For the record, we (AS) are using the MS Platform SDK cl.exe for the
64bit build of ActiveTcl, sqlite, etc.
>
> Regards,
> Jan Nijtmans
--
% Tcl'2015 Oct 19-23 = http://www.tcl.tk/community/tcl2015/cfp.html
% EuroTcl'15 June 20-21 = http://www.eurotcl.tcl3d.org/
Andreas Kupries
Senior Tcl Developer
Code to Cloud: Smarter, Safer, Faster?
F: 778.786.1133
andreask at activestate.com, http://www.activestate.com
Learn about Stackato for Private PaaS: http://www.activestate.com/stackato
[sqlite] SQLite Entity Framework Provider
Michel Feinstein wrote: > > So, I ask (or beg) that the community make a new provider that can be used > with Entity Framework 6.1.3 Code First, support migrations and the Sync > Framework. > Is there is a list somewhere of what's missing from the System.Data.SQLite.EF6 provider that would enable these features? Maybe there is a clear list of the various requirements in ADO.NET providers to support the various EF6 features? -- Joe Mistachkin
[sqlite] Read data from database slow the first time but then very fast thereafter. How can I improve the speed for the first time read?
On Tue, Aug 4, 2015 at 10:45 AM, Simon Slavin wrote: > > On 3 Aug 2015, at 1:58pm, Linquan Bai wrote: > > > I am trying to read large data from the database about 1 million records. > > It takes around 1min for the first time read. But if I do the same > process > > thereafter, the time is significantly reduced to 3 seconds. How can I > get a > > fast speed for the first time read? > > You can't. Some part of your computer has pulled that data into cache, > and it's still in the cache when you run the process again, so it the data > doesn't need to be fetched from disk again. > That sounds correct to me. I don't know which OS the OP is running (likely Windows, which I don't know well). But I wonder if there is some way, like running a program before he runs his application which can tell the OS to "preload" the file into RAM cache. On Linux, I might do something like: "dd if=/path/to/sqlite-database.sqlite3 of=/dev/null bs=1024 count=100" which would, as a side effect, pull the first 100KiB of the file into RAM. Of course, there is an expensive way: ?Put your SQLite data base on an PCIe SATA III (6Gib/s) SSD drive. Lowest cost for a reasonable sized one which I saw on Amazon was USD 250 for 240GiB. http://smile.amazon.com/Kingston-Digital-Predator-SHPM2280P2H-240G/dp/B00V01C4RK This is for me as a Amazon Prime member. > > If you tell us how big an average row is (how many bytes of data) then we > can tell you whether 1 million rows in 1 minute is a reasonable time. But > my guess is that that's more or less the time you'd expect. > > Simon. > -- Schrodinger's backup: The condition of any backup is unknown until a restore is attempted. Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be. He's about as useful as a wax frying pan. 10 to the 12th power microphones = 1 Megaphone Maranatha! <>< John McKown
[sqlite] Read data from database slow the first time but then very fast thereafter. How can I improve the speed for the first time read?
On Tue, Aug 4, 2015 at 9:23 AM, John McKown wrote: > On Tue, Aug 4, 2015 at 10:45 AM, Simon Slavin > wrote: > > On 3 Aug 2015, at 1:58pm, Linquan Bai wrote: > > > I am trying to read large data from the database about 1 million > records. > > > It takes around 1min for the first time read. But if I do the same > > process > > > thereafter, the time is significantly reduced to 3 seconds. How can I > > get a > > > fast speed for the first time read? > > > > You can't. Some part of your computer has pulled that data into cache, > > and it's still in the cache when you run the process again, so it the > data > > doesn't need to be fetched from disk again. > > > > That sounds correct to me. I don't know which OS the OP is running (likely > Windows, which I don't know well). But I wonder if there is some way, like > running a program before he runs his application which can tell the OS to > "preload" the file into RAM cache. On Linux, I might do something like: "dd > if=/path/to/sqlite-database.sqlite3 of=/dev/null bs=1024 count=100" which > would, as a side effect, pull the first 100KiB of the file into RAM. You could also write code to get the file underlying the database (sqlite3_file_control with SQLITE_FCNTL_FILE_POINTER), then use the VFS layer to manually page through the file. BUT, keep in mind that you might find that you've just moved the 1min time from query time to preload time, in which case you've just complexified without adding anything. If preloading makes the overall operation faster, then you could probably see some benefit from running VACUUM. You might also try different page sizes (note that changing page size requires VACUUM, so make sure that you're actually measuring page-size differences and not VACUUM differences). Changing to memory-mapped may change timings, too, since the OS may predict reads differently for memory-mapped I/O versus POSIX I/O. -scott
[sqlite] SQLite Entity Framework Provider
EF6 provider implementation in System.Data.SQLite.EF6 doesn't create tables so it can't work using EF Code First. Apparently EF7 will support this in a new, Microsoft made sqlite provider, but this will take still a long time to appear and EF7 might not be as complete as EF6 http://www.wintellect.com/devcenter/tsneed/when-should-you-make-the-move-to-entity-framework-7 . So, I ask (or beg) that the community make a new provider that can be used with Entity Framework 6.1.3 Code First, support migrations and the Sync Framework. Best wishes, Michel.
