Re: [sqlite] Multiple Match statements per query
Database abstraction, scope, multi-threading. A few stack layers out is my loop requesting each record by rowid. And there's no predicting which thread will make the request. Likely, there will be 50 threads making very similar requests at the same time. If I could clone the statement, then I could make one that remains static. And each time I need it, I clone it, use it, and throw it away. I'm sure I would need some thread synchronization while performing the clone, but it would be a much briefer period than if I were trying to share a single statement with all threads. If cloning isn't an option, I can create a statement pool; I would just prefer the simpler option. Thanks dw _ From: Igor Tandetnik [mailto:itandet...@mvps.org] To: sqlite-users@sqlite.org Sent: Tue, 19 Apr 2011 17:08:09 -0600 Subject: Re: [sqlite] Multiple Match statements per query On 4/19/2011 6:57 PM, Dave White wrote: > So, if I could prepare the statement once, then clone it every time I > need to use it, I may see a 4 fold speed increase when calling this > operation frequently. Why can't you just reuse the same statement every time? Are you aware of sqlite3_reset ? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple Match statements per query
On 4/19/2011 6:57 PM, Dave White wrote: > So, if I could prepare the statement once, then clone it every time I > need to use it, I may see a 4 fold speed increase when calling this > operation frequently. Why can't you just reuse the same statement every time? Are you aware of sqlite3_reset ? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple Match statements per query
I'll look into GLOB. For the statement cloning, I'm looking for something a little different. I already have the connection and query string, and duplicating bindings is not necessary. It's the sqlite3_stmt that I want. right after sqlite3_prepare_v2 has been called. I was doing some profiling today. My statement loads one record by rsn, and then parses that data into an external object. Just calling sqlite3_prepare_v2 takes 3-4x as long as stepping, reading, and parsing the record. This particular request is very simple, and the only thing that changes is the rowid which is handled with a bind. So, if I could prepare the statement once, then clone it every time I need to use it, I may see a 4 fold speed increase when calling this operation frequently. The query is basically "Select * from tableName where rowid=?" Thanks dw On Apr 19, 2011, at 4:40 PM, Mihai Militaru wrote: > On Tue, 19 Apr 2011 14:18:05 -0600 > Dave White wrote: > >> For example, this works: >> SELECT T01_fts.docid FROM T01_fts JOIN T01 ON T01_fts.docid = T01.rowid >> WHERE T01.hasPic = 0 AND T01_fts.words MATCH 'SBNTKN*' >> >> These do not: >> SELECT T01_fts.docid FROM T01_fts JOIN T01 ON T01_fts.docid = T01.rowid >> WHERE T01.hasPic = 0 OR T01_fts.words MATCH 'SBNTKN*' >> SELECT T01_fts.docid FROM T01_fts JOIN T01 ON T01_fts.docid = T01.rowid >> WHERE T01.hasPic = 0 AND (T01_fts.words MATCH 'SBNTKN*' OR T01_fts.words >> MATCH 'CTLTKN*' ) > > I think it is GLOB '*' what you look for (or LIKE '%' for case-insensitive > match): > SELECT T01_fts.docid FROM T01_fts JOIN T01 ON T01_fts.docid = T01.rowid WHERE > T01.hasPic = 0 OR T01_fts.words GLOB 'SBNTKN*'; > SELECT T01_fts.docid FROM T01_fts JOIN T01 ON T01_fts.docid = T01.rowid WHERE > T01.hasPic = 0 AND (T01_fts.words GLOB 'SBNTKN*' OR T01_fts.words GLOB > 'CTLTKN*'); > >> And an entirely separate question: Is there currently a way, or will there >> soon be a way to clone prepared statements? > > I'd do it like this: > > sqlite3_stmt *stmt2 = NULL; > sqlite3_prepare_v2(sqlite3_db_handle(stmt1), sqlite3_sql(stmt1), -1, &stmt2, > NULL); > > Check what the respective functions do here: > http://www.sqlite.org/c3ref/funclist.html > Basically: > - the first argument function returns the database of the first statement > (you may pass a different open database handle directly, >in order to "clone" the first statement over it); > - the second argument function returns the sql text of the first statement; > - the third argument is the size of the text to parse, negative to get it up > to the first NULL - normally the end; > - the fourth is a pointer to your new unallocated statement; > > I think copying the bindings is possible using sqlite3_bind_parameter_* and > something else I can't figure out right now. > > -- > Mihai Militaru > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple Match statements per query
On Tue, 19 Apr 2011 14:18:05 -0600 Dave White wrote: > For example, this works: > SELECT T01_fts.docid FROM T01_fts JOIN T01 ON T01_fts.docid = T01.rowid > WHERE T01.hasPic = 0 AND T01_fts.words MATCH 'SBNTKN*' > > These do not: > SELECT T01_fts.docid FROM T01_fts JOIN T01 ON T01_fts.docid = T01.rowid > WHERE T01.hasPic = 0 OR T01_fts.words MATCH 'SBNTKN*' > SELECT T01_fts.docid FROM T01_fts JOIN T01 ON T01_fts.docid = T01.rowid > WHERE T01.hasPic = 0 AND (T01_fts.words MATCH 'SBNTKN*' OR T01_fts.words > MATCH 'CTLTKN*' ) I think it is GLOB '*' what you look for (or LIKE '%' for case-insensitive match): SELECT T01_fts.docid FROM T01_fts JOIN T01 ON T01_fts.docid = T01.rowid WHERE T01.hasPic = 0 OR T01_fts.words GLOB 'SBNTKN*'; SELECT T01_fts.docid FROM T01_fts JOIN T01 ON T01_fts.docid = T01.rowid WHERE T01.hasPic = 0 AND (T01_fts.words GLOB 'SBNTKN*' OR T01_fts.words GLOB 'CTLTKN*'); > And an entirely separate question: Is there currently a way, or will there > soon be a way to clone prepared statements? I'd do it like this: sqlite3_stmt *stmt2 = NULL; sqlite3_prepare_v2(sqlite3_db_handle(stmt1), sqlite3_sql(stmt1), -1, &stmt2, NULL); Check what the respective functions do here: http://www.sqlite.org/c3ref/funclist.html Basically: - the first argument function returns the database of the first statement (you may pass a different open database handle directly, in order to "clone" the first statement over it); - the second argument function returns the sql text of the first statement; - the third argument is the size of the text to parse, negative to get it up to the first NULL - normally the end; - the fourth is a pointer to your new unallocated statement; I think copying the bindings is possible using sqlite3_bind_parameter_* and something else I can't figure out right now. -- Mihai Militaru ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] installing sqlite
SQLite Manager for firefox has real problems. I made it work for a while until I found a real GUI. And does a pretty decent job. SQLite Expro Personal is FREE. Of course, there is also a paid version with some additional features. http://www.sqliteexpert.com/ --- On Tue, 4/19/11, Kees Nuyt wrote: From: Kees Nuyt Subject: Re: [sqlite] installing sqlite To: sqlite-users@sqlite.org Date: Tuesday, April 19, 2011, 5:42 PM On Tue, 19 Apr 2011 15:04:31 -0400, Carlos Contreras wrote: >sqlite offer a very eficient program but I dont understand how to install it >in my Windows NT PC. I dont know how to compile a C program and then use it >as a shell to run the sqlite commands. > >Can you help me? Download the command line tool and/or the .dll for windows from the download page http://www.sqlite.org/download.html Look for the heading "Precompiled Binaries For Windows" There is nothing to install, just unzip the .zip archive(s) into a folder of your choice. Or, if you prefer a GUI tool: Install the Firefox web browser from http://www.mozilla.com/en-US/firefox/new/ and add the SQLite manager add-on from https://addons.mozilla.org/en-US/firefox/addon/sqlite-manager/ I have no idea wether all that is compatible with Windows NT. Windows XP and later are fine. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] installing sqlite
On Tue, 19 Apr 2011 15:04:31 -0400, Carlos Contreras wrote: >sqlite offer a very eficient program but I dont understand how to install it >in my Windows NT PC. I dont know how to compile a C program and then use it >as a shell to run the sqlite commands. > >Can you help me? Download the command line tool and/or the .dll for windows from the download page http://www.sqlite.org/download.html Look for the heading "Precompiled Binaries For Windows" There is nothing to install, just unzip the .zip archive(s) into a folder of your choice. Or, if you prefer a GUI tool: Install the Firefox web browser from http://www.mozilla.com/en-US/firefox/new/ and add the SQLite manager add-on from https://addons.mozilla.org/en-US/firefox/addon/sqlite-manager/ I have no idea wether all that is compatible with Windows NT. Windows XP and later are fine. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Building managed only System.Data.SQLite
Daniel: I have not tried Csharp-sqlite, it looks interesting but I do not know if that is right for me at this moment. Shane: I will try what you recommend tomorrow, thank you. -- Rich On Tue, Apr 19, 2011 at 4:47 PM, Shane Harrelson wrote: > The target build settings can be controlled from SQLite.NET.Settings.targets > - in particular, you should probably look at UseInteropDll and > UseSqliteStandard. > To override the USE_INTEROP_DLL setting, try copying > SQLite.NET.Settings.targets to SQLite.NET.Settings.targets.user and make the > settings changes there. > This should work with VS2008 and VS2010. > > HTH. > -Shane > > > On Tue, Apr 19, 2011 at 3:09 PM, Rich Rattanni wrote: > >> I was wondering if anyone has had any luck building the Managed-Only >> System.Data.SQLite .NET adapter for SQLite from the source provided at >> system.data.sqlite.org? I downloaded the pre-built binaries but they >> appear to rely on the InterOp assembly. My current project is running >> under Linux 2.6 on an ARM processor, and uses managed-only copy of the >> System.Data.SQLite adapter + SQLite 3.5.0 + mono to run my .NET app. >> I am looking to do some bug tracing / upgrading so I would like to >> build my own copy from source. >> >> -- >> Rich >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Building managed only System.Data.SQLite
The target build settings can be controlled from SQLite.NET.Settings.targets - in particular, you should probably look at UseInteropDll and UseSqliteStandard. To override the USE_INTEROP_DLL setting, try copying SQLite.NET.Settings.targets to SQLite.NET.Settings.targets.user and make the settings changes there. This should work with VS2008 and VS2010. HTH. -Shane On Tue, Apr 19, 2011 at 3:09 PM, Rich Rattanni wrote: > I was wondering if anyone has had any luck building the Managed-Only > System.Data.SQLite .NET adapter for SQLite from the source provided at > system.data.sqlite.org? I downloaded the pre-built binaries but they > appear to rely on the InterOp assembly. My current project is running > under Linux 2.6 on an ARM processor, and uses managed-only copy of the > System.Data.SQLite adapter + SQLite 3.5.0 + mono to run my .NET app. > I am looking to do some bug tracing / upgrading so I would like to > build my own copy from source. > > -- > Rich > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Multiple Match statements per query
It appears that I cannot use MATCH more than once per query. It also looks like I can't use it if prefaced with OR or NOT. For example, this works: SELECT T01_fts.docid FROM T01_fts JOIN T01 ON T01_fts.docid = T01.rowid WHERE T01.hasPic = 0 AND T01_fts.words MATCH 'SBNTKN*' These do not: SELECT T01_fts.docid FROM T01_fts JOIN T01 ON T01_fts.docid = T01.rowid WHERE T01.hasPic = 0 OR T01_fts.words MATCH 'SBNTKN*' SELECT T01_fts.docid FROM T01_fts JOIN T01 ON T01_fts.docid = T01.rowid WHERE T01.hasPic = 0 AND (T01_fts.words MATCH 'SBNTKN*' OR T01_fts.words MATCH 'CTLTKN*' ) Obviously I can manually rewrite the queries by hand. The trick is building these strings from a bundle of nested logic which has no knowledge about the database structure. I could probably get it working with UNION and INTERSECT, but I want to avoid those in the interest of speed. Is this something I need to work around, or might it be supported in future releases of sqlite? And an entirely separate question: Is there currently a way, or will there soon be a way to clone prepared statements? Thanks dw ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Building managed only System.Data.SQLite
Have you tried C# SQLite? http://code.google.com/p/csharp-sqlite/ --- On Tue, 4/19/11, Rich Rattanni wrote: > From: Rich Rattanni > Subject: [sqlite] Building managed only System.Data.SQLite > To: "General Discussion of SQLite Database" > Date: Tuesday, April 19, 2011, 3:09 PM > I was wondering if anyone has had any > luck building the Managed-Only > System.Data.SQLite .NET adapter for SQLite from the source > provided at > system.data.sqlite.org? I downloaded the pre-built > binaries but they > appear to rely on the InterOp assembly. My current > project is running > under Linux 2.6 on an ARM processor, and uses managed-only > copy of the > System.Data.SQLite adapter + SQLite 3.5.0 + mono to run my > .NET app. > I am looking to do some bug tracing / upgrading so I would > like to > build my own copy from source. > > -- > Rich > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Building managed only System.Data.SQLite
I was wondering if anyone has had any luck building the Managed-Only System.Data.SQLite .NET adapter for SQLite from the source provided at system.data.sqlite.org? I downloaded the pre-built binaries but they appear to rely on the InterOp assembly. My current project is running under Linux 2.6 on an ARM processor, and uses managed-only copy of the System.Data.SQLite adapter + SQLite 3.5.0 + mono to run my .NET app. I am looking to do some bug tracing / upgrading so I would like to build my own copy from source. -- Rich ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] installing sqlite
sqlite offer a very eficient program but I dont understand how to install it in my Windows NT PC. I dont know how to compile a C program and then use it as a shell to run the sqlite commands. Can you help me? -- Carlos Contreras, presidente Club Científico de Peñalolén, Santiago, CHILE http://www.clubcientifico.cl fonos: 562-769130709-2114827 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] What happens if you insert more than your RAM
>Tue Apr 19 18:35:27 GMT 2011 Danny dragonslayer2k at yahoo.com > >Depends on access type. If accessing sequentially, paging would be minimal, >that is, you would process the "segment" that fits into memory, then page >in another "segment" and process that, etc., etc. Even so it would now be accessed at disk speeds and thus very slow by comparison. And OBTW, the db won't likely do sequential access. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What happens if you insert more than your RAM
Depends on access type. If accessing sequentially, paging would be minimal, that is, you would process the "segment" that fits into memory, then page in another "segment" and process that, etc., etc. However completely random hits on the database could result in heavy paging, unless it were possible to do the random accesses in a "sorted" manner. For example, input transactions sorted by the same key that you are accessing by. --- On Tue, 4/19/11, jeff archer wrote: > From: jeff archer > Subject: [sqlite] What happens if you insert more than your RAM > To: "SQLite-user.org" > Date: Tuesday, April 19, 2011, 2:29 PM > Wouldn't it page to disk, thrash and > be very slow first? > > >On Mon, 18 Apr 2011 09:46:44 -0400, Pavel Ivanov > wrote: > >You won't be able to insert. The statement will fail. > > > >On Mon, Apr 18, 2011 at 9:44 AM, Adam DeVita > wrote: > >> Good day, > >> > >> What happens if you insert more than your RAM size > into an in memory > >> database? > >> (I'm particularly interested in the Windows > context). > >> > Jeff Archer > Nanotronics Imaging > jsarc...@nanotronicsimaging.com > <330>819.4615 > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What happens if you insert more than your RAM size into an in memory database?
> What happens if you insert more than your RAM size into an in memory > database? > (I'm particularly interested in the Windows context). > Are we talking about 32bit windows? I mean under 32 bit windows the normal address space limit (without the /3GB switch and LARGEADDRESSAWARE link flag) is 2GB and fragmented so that the largest single process allocation (without resorting to AWE) is somewhere around 1.2GB to 1.5GB even if you have a system with 4GB of RAM and 8GB of swap space. John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] What happens if you insert more than your RAM
Wouldn't it page to disk, thrash and be very slow first? >On Mon, 18 Apr 2011 09:46:44 -0400, Pavel Ivanov wrote: >You won't be able to insert. The statement will fail. > >On Mon, Apr 18, 2011 at 9:44 AM, Adam DeVita wrote: >> Good day, >> >> What happens if you insert more than your RAM size into an in memory >> database? >> (I'm particularly interested in the Windows context). >> Jeff Archer Nanotronics Imaging jsarc...@nanotronicsimaging.com <330>819.4615 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Building sqlite for windows in a proper way
>On Mon, Apr 18, 2011 at 8:42 AM, Kuba Nowak wrote: > >Does anyone one know how to build sqlite to get the same binary as on >download page ? > What do you use to compare the speed between the builds of SQLite? Are using debug build from VS2010? My experience is that VS debug builds run most code significantly slower than the release builds. Jeff Archer Nanotronics Imaging jsarc...@nanotronicsimaging.com <330>819.4615 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] insert help
On 4/19/2011 1:59 PM, vquickl27 wrote: > Jean-Christophe Deschamps-3 wrote: >> So you need to perform as many inserts as values you have to insert: >> insert into mytable (date, value) values ('2011/04/18 21:35:33', 1); >> insert into mytable (date, value) values ('2011/04/18 21:35:33', 2); >> insert into mytable (date, value) values ('2011/04/18 21:35:33', 3); >> insert into mytable (date, value) values ('2011/04/18 21:35:33', 4); >> insert into mytable (date, value) values ('2011/04/18 21:35:33', 5); > > that worked perfect. thanks for the help. Do you think a join would be > better then a insert? A join between what table and what other table? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] insert help
Jean-Christophe Deschamps-3 wrote: > > >>My date column is set when the program starts and i do not want it to >>change. > > How is this date column set in the database without inserting anything? > >> So I have my with and two columns and >>. I have say 5 values (1 2 3 4 5) that I wanted inserted >>into mytable where the date is equal to date that was preset my >>starting the >>program. > > Preset, how? I guess you have this date stored in some variable > somewhere. Just use it to fill the date column in the each new row. > >> So a select of my table would look like this: >>select * from mytable where date='2011/04/18 21:35:33'; >>2011/04/18 21:35:33|1 >>2011/04/18 21:35:33|2 >>2011/04/18 21:35:33|3 >>2011/04/18 21:35:33|4 >>2011/04/18 21:35:33|5 > > So you need to perform as many inserts as values you have to insert: > insert into mytable (date, value) values ('2011/04/18 21:35:33', 1); > insert into mytable (date, value) values ('2011/04/18 21:35:33', 2); > insert into mytable (date, value) values ('2011/04/18 21:35:33', 3); > insert into mytable (date, value) values ('2011/04/18 21:35:33', 4); > insert into mytable (date, value) values ('2011/04/18 21:35:33', 5); > > If you have really _many_ inserts to perform, wrap the lot in a > transaction to speed up the process: > begin; > > commit; > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > that worked perfect. thanks for the help. Do you think a join would be better then a insert? -- View this message in context: http://old.nabble.com/insert-help-tp31429185p31434954.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] insert help
>My date column is set when the program starts and i do not want it to >change. How is this date column set in the database without inserting anything? > So I have my with and two columns and >. I have say 5 values (1 2 3 4 5) that I wanted inserted >into mytable where the date is equal to date that was preset my >starting the >program. Preset, how? I guess you have this date stored in some variable somewhere. Just use it to fill the date column in the each new row. > So a select of my table would look like this: >select * from mytable where date='2011/04/18 21:35:33'; >2011/04/18 21:35:33|1 >2011/04/18 21:35:33|2 >2011/04/18 21:35:33|3 >2011/04/18 21:35:33|4 >2011/04/18 21:35:33|5 So you need to perform as many inserts as values you have to insert: insert into mytable (date, value) values ('2011/04/18 21:35:33', 1); insert into mytable (date, value) values ('2011/04/18 21:35:33', 2); insert into mytable (date, value) values ('2011/04/18 21:35:33', 3); insert into mytable (date, value) values ('2011/04/18 21:35:33', 4); insert into mytable (date, value) values ('2011/04/18 21:35:33', 5); If you have really _many_ inserts to perform, wrap the lot in a transaction to speed up the process: begin; commit; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite3.DLL 3.7.6 memory leaks
Are you sure these leaks aren't yours? Although I don't know the Sqlite internals some of the data values don't seem to be related to Sqlite, like: c:/DEV/Platform/ PolicyDataPack.i You can try to use the allocation number to narrow down the code location. Don't remember the specifics though. On 4/18/2011 6:03 AM, Khanh Nguyen wrote: > Hi, > > > > My name is Khanh Nguyen and currently using SQLite3.DLL 3.7.6 in my > application (DLL built with VS2008 C++ with this flag: Multi-threaded > Debug DLL (/MDd). > > > > The DLL has some memory leaks that I have captured here: > > > > The thread 'Win32 Thread' (0x368) has exited with code 0 (0x0). > > Detected memory leaks! > > Dumping objects -> > > {8390} normal block at 0x01364C70, 64 bytes long. > > Data: 63 3A 2F 44 45 56 2F 50 6C 61 74 66 6F 72 6D > 2F > > {8382} normal block at 0x0138C9C0, 32 bytes long. > > Data:<7Zl7ji/F9x+bOgbG> 37 5A 6C 37 6A 69 2F 46 39 78 2B 62 4F 67 62 > 47 > > {8380} normal block at 0x0138B938, 32 bytes long. > > Data: 50 6F 6C 69 63 79 44 61 74 61 50 61 63 6B 2E > 69 > > {8377} normal block at 0x01391F48, 448 bytes long. > > Data:< > 10 1F F3 02 00 00 00 00 00 00 00 00 CD CD CD > CD > > {7174} normal block at 0x01391DC8, 64 bytes long. > > Data: 63 3A 2F 44 45 56 2F 50 6C 61 74 66 6F 72 6D > 2F > > {7166} normal block at 0x013621D8, 32 bytes long. > > Data:<7Zl7ji/F9x+bOgbG> 37 5A 6C 37 6A 69 2F 46 39 78 2B 62 4F 67 62 > 47 > > {7164} normal block at 0x01386690, 32 bytes long. > > Data: 50 6F 6C 69 63 79 44 61 74 61 50 61 63 6B 2E > 69 > > {7161} normal block at 0x01390430, 448 bytes long. > > Data:< > 10 1F F3 02 00 00 00 00 00 00 00 00 CD CD CD > CD > > {7108} normal block at 0x0138CDC8, 64 bytes long. > > Data: 63 3A 2F 44 45 56 2F 50 6C 61 74 66 6F 72 6D > 2F > > {7100} normal block at 0x01386320, 32 bytes long. > > Data: 45 4E 74 35 4B 6B 59 48 52 71 65 55 2F 69 4C > 52 > > {7098} normal block at 0x0138BCD8, 32 bytes long. > > Data: 50 6F 6C 69 63 79 44 61 74 61 50 61 63 6B 2E > 62 > > {7095} normal block at 0x0138D530, 448 bytes long. > > Data:< > 10 1F F3 02 00 00 00 00 00 00 00 00 CD CD CD > CD > > {7033} normal block at 0x013796D8, 64 bytes long. > > Data: 63 3A 2F 44 45 56 2F 50 6C 61 74 66 6F 72 6D > 2F > > {7025} normal block at 0x0136DED0, 32 bytes long. > > Data:<5U/jyx2txHeUQUe/> 35 55 2F 6A 79 78 32 74 78 48 65 55 51 55 65 > 2F > > {7023} normal block at 0x013658D0, 32 bytes long. > > Data: 4E 65 74 77 6F 72 6B 53 65 74 74 69 6E 67 33 > 2E > > {7020} normal block at 0x01389BF0, 448 bytes long. > > Data:< > 10 1F F3 02 00 00 00 00 00 00 00 00 CD CD CD > CD > > Object dump complete. > > The program '[5432] DebugConsole.exe: Native' has exited with code 0 > (0x0). > > > > Please help me overcome this memory leak issue. > > > > Thanks and Kind Regards, > > > > Khanh > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What happens if you insert more than your RAM size into an in memory database?
On Tue, Apr 19, 2011 at 03:29:42PM +0100, Simon Slavin scratched on the wall: > > On 19 Apr 2011, at 2:58pm, Adam DeVita wrote: > > > Our application is typically implemented on a standard laptop PC. It seems > > that the symptoms displayed are consistent with what this list describes > > would happen, so it looks like I can start thinking of how to write a > > defence. It does suddenly become very slow. > > > > I think the potential solutions we may implement are all in application > > code, so not really an SQLite problem. > > If you are using an in-memory database purely for speed, you might try > the simple change of not doing that. Make your database use disk > space as any normal one would, and delete it after you close it. > Windows is pretty good at caching stuff these days and on-disk > databases aren't as slow as some people would guess. You can pass the sqlite3_open*() calls an empty string for the filename. That will create a disk-backed temporary database that automatically cleans up after itself. If you crank the SQLite cache up, the performance should be roughly the same as an in-memory database, right up until it runs out of memory. After that, the performance should be a bit better, as SQLite's cache is likely to be more efficient than paging memory to disk. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] insert help
Jean-Christophe Deschamps-3 wrote: > > >>Newbie here. i'm trying to insert multiple values into a table by a >>certain >>date and when I use where clause it fails. This is my code "insert >>into db >>(table) values ('value') where date = 'date range'". Thanks for any help. > > There is no where clause in insert statements, it wouldn't make sense. > Your insert should look like: > > insert into mytable (datecolumn) values > (litteral_date_in_the_format_you_choose); > > See http://www.sqlite.org/lang_insert.html > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > My date column is set when the program starts and i do not want it to change. So I have my with and two columns and . I have say 5 values (1 2 3 4 5) that I wanted inserted into mytable where the date is equal to date that was preset my starting the program. So a select of my table would look like this: select * from mytable where date='2011/04/18 21:35:33'; 2011/04/18 21:35:33|1 2011/04/18 21:35:33|2 2011/04/18 21:35:33|3 2011/04/18 21:35:33|4 2011/04/18 21:35:33|5 -- View this message in context: http://old.nabble.com/insert-help-tp31429185p31433593.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What happens if you insert more than your RAM size into an in memory database?
On 19 Apr 2011, at 2:58pm, Adam DeVita wrote: > Our application is typically implemented on a standard laptop PC. It seems > that the symptoms displayed are consistent with what this list describes > would happen, so it looks like I can start thinking of how to write a > defence. It does suddenly become very slow. > > I think the potential solutions we may implement are all in application > code, so not really an SQLite problem. If you are using an in-memory database purely for speed, you might try the simple change of not doing that. Make your database use disk space as any normal one would, and delete it after you close it. Windows is pretty good at caching stuff these days and on-disk databases aren't as slow as some people would guess. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Building sqlite for windows in a proper way
> Does anyone one know how to build sqlite to get the same binary as on > download page ? Did you try to remove all those defines that you add at build time and leave only default values set inside sqlite3.c file? Pavel On Mon, Apr 18, 2011 at 8:42 AM, Kuba Nowak wrote: > Hello > > My problem is no matter how i build sqlite - my binary is much slower than > the precompiled one on sqlite download page (about 3 - 6 times depending on > the query). > > I am using sqlite3.h and sqlite3.c from the amalgamation source: > > http://www.sqlite.org/sqlite-amalgamation-3070602.zip > > I have added the following flags when compiling sqlite: > > gcc > -s -O4 -I. -fomit-frame-pointer > -DNDEBUG > -DSQLITE_OS_WIN=1 > -DSQLITE_HAVE_READLINE=0 > -DSQLITE_THREADSAFE=1 > -DSQLITE_TEMP_STORE=2 > -DSQLITE_ENABLE_RTREE > -DSQLITE_ENABLE_FTS3 > -DSQLITE_OMIT_COMPILEOPTION_DIAGS > -DSQLITE_ENABLE_COLUMN_METADATA > -DNO_TCL > > I built it both with MINGW and with MSVS 2010. > > Does anyone one know how to build sqlite to get the same binary as on > download page ? > > Any help would be appreciated. > > Thanks. > > Jakub > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What happens if you insert more than your RAM size into an in memory database?
Thanks for the responses. Our application is typically implemented on a standard laptop PC. It seems that the symptoms displayed are consistent with what this list describes would happen, so it looks like I can start thinking of how to write a defence. It does suddenly become very slow. I think the potential solutions we may implement are all in application code, so not really an SQLite problem. Thanks, Adam On Mon, Apr 18, 2011 at 10:07 AM, eLaReF wrote: > Talking as a Windows user only rather than an SQL expert (I'm not even > good enough to call myself a beginner!) > > Are we talking about a small netbook type with only say 8GB of memory > and no hard drive. > > If a Windows m/c has a hard drive, surely virtual memory > (drive-swapping) comes into play? > It would, of course become v-e-r-y slow in comparison. > > > eLaReF > > > > > On 18/04/2011 14:46, Pavel Ivanov wrote: > > You won't be able to insert. The statement will fail. > > > > Pavel > > > > > > On Mon, Apr 18, 2011 at 9:44 AM, Adam DeVita > wrote: > >> Good day, > >> > >> What happens if you insert more than your RAM size into an in memory > >> database? > >> (I'm particularly interested in the Windows context). > >> > >> regards, > >> Adam > >> ___ > >> sqlite-users mailing list > >> sqlite-users@sqlite.org > >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > >> > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- VerifEye Technologies Inc. 905-948-0015x245 151 Whitehall Dr, Unit 2 Markham ON, L3R 9T1 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_MISUSE error code
On Tue, Apr 19, 2011 at 04:19:55PM +0530, Navaneeth Sen B scratched on the wall: > What is this SQLITE_MISUSE error code? When is it exactly thrown? It means that the programmer is using the SQLite API incorrectly, and that there is a fundamental flow or logic issue in the code. An example might be calling sqlite3_bind_xxx() on an "active" statement (one that has been stepped, but not yet reset/finalized). Such action simply does not make sense. It is usually the result of a new-to-SQLite programmer not fully understanding the correct way to use SQLite API, but can also point to buggy flow control. Are you having issues with a specific call returning MISUSE? -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to download precompiled files for 3.6.21-x86-Windows
Hi all, We want to upgrade our SQLite version to 3.6.21. But, I could not find any amalgamation or precompiled download link for that version in official website. Therefore, I am requesting a download link where I can verify hashcodes. I need below files: sqlite3.dll sqlite3.lib. sqlite3.h sqlite3ext.h thanks in advance. We currently use 3.0.8 version. Best regards Baris ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What happens if you insert more than your RAM size into an in memory database?
Talking as a Windows user only rather than an SQL expert (I'm not even good enough to call myself a beginner!) Are we talking about a small netbook type with only say 8GB of memory and no hard drive. If a Windows m/c has a hard drive, surely virtual memory (drive-swapping) comes into play? It would, of course become v-e-r-y slow in comparison. eLaReF On 18/04/2011 14:46, Pavel Ivanov wrote: > You won't be able to insert. The statement will fail. > > Pavel > > > On Mon, Apr 18, 2011 at 9:44 AM, Adam DeVita wrote: >> Good day, >> >> What happens if you insert more than your RAM size into an in memory >> database? >> (I'm particularly interested in the Windows context). >> >> regards, >> Adam >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite3.DLL 3.7.6 memory leaks
Hi, My name is Khanh Nguyen and currently using SQLite3.DLL 3.7.6 in my application (DLL built with VS2008 C++ with this flag: Multi-threaded Debug DLL (/MDd). The DLL has some memory leaks that I have captured here: The thread 'Win32 Thread' (0x368) has exited with code 0 (0x0). Detected memory leaks! Dumping objects -> {8390} normal block at 0x01364C70, 64 bytes long. Data: 63 3A 2F 44 45 56 2F 50 6C 61 74 66 6F 72 6D 2F {8382} normal block at 0x0138C9C0, 32 bytes long. Data: <7Zl7ji/F9x+bOgbG> 37 5A 6C 37 6A 69 2F 46 39 78 2B 62 4F 67 62 47 {8380} normal block at 0x0138B938, 32 bytes long. Data: 50 6F 6C 69 63 79 44 61 74 61 50 61 63 6B 2E 69 {8377} normal block at 0x01391F48, 448 bytes long. Data: <> 10 1F F3 02 00 00 00 00 00 00 00 00 CD CD CD CD {7174} normal block at 0x01391DC8, 64 bytes long. Data: 63 3A 2F 44 45 56 2F 50 6C 61 74 66 6F 72 6D 2F {7166} normal block at 0x013621D8, 32 bytes long. Data: <7Zl7ji/F9x+bOgbG> 37 5A 6C 37 6A 69 2F 46 39 78 2B 62 4F 67 62 47 {7164} normal block at 0x01386690, 32 bytes long. Data: 50 6F 6C 69 63 79 44 61 74 61 50 61 63 6B 2E 69 {7161} normal block at 0x01390430, 448 bytes long. Data: <> 10 1F F3 02 00 00 00 00 00 00 00 00 CD CD CD CD {7108} normal block at 0x0138CDC8, 64 bytes long. Data: 63 3A 2F 44 45 56 2F 50 6C 61 74 66 6F 72 6D 2F {7100} normal block at 0x01386320, 32 bytes long. Data: 45 4E 74 35 4B 6B 59 48 52 71 65 55 2F 69 4C 52 {7098} normal block at 0x0138BCD8, 32 bytes long. Data: 50 6F 6C 69 63 79 44 61 74 61 50 61 63 6B 2E 62 {7095} normal block at 0x0138D530, 448 bytes long. Data: <> 10 1F F3 02 00 00 00 00 00 00 00 00 CD CD CD CD {7033} normal block at 0x013796D8, 64 bytes long. Data: 63 3A 2F 44 45 56 2F 50 6C 61 74 66 6F 72 6D 2F {7025} normal block at 0x0136DED0, 32 bytes long. Data: <5U/jyx2txHeUQUe/> 35 55 2F 6A 79 78 32 74 78 48 65 55 51 55 65 2F {7023} normal block at 0x013658D0, 32 bytes long. Data: 4E 65 74 77 6F 72 6B 53 65 74 74 69 6E 67 33 2E {7020} normal block at 0x01389BF0, 448 bytes long. Data: <> 10 1F F3 02 00 00 00 00 00 00 00 00 CD CD CD CD Object dump complete. The program '[5432] DebugConsole.exe: Native' has exited with code 0 (0x0). Please help me overcome this memory leak issue. Thanks and Kind Regards, Khanh ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLITE_MISUSE error code
Hi All, What is this SQLITE_MISUSE error code? When is it exactly thrown? -- Thanks, Sen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [SQLITE] precompiled files for 3.6.21-x86-Windows
Hi all, We want to upgrade our SQLite version to 3.6.21. But, I could not find any amalgamation or precompiled download link for that version in official website. Therefore, I need your help to find a download link where I can verify hashcodes. I need below files: sqlite3.dll sqlite3.lib sqlite3.h sqlite3ext.h thanks in advance. We currently use 3.0.8 version. Best regards Baris ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users