Re: [sqlite] integrity_check "out of memory"

On Mon, Jan 21, 2013 at 9:30 PM, Klaas V wrote: > Max wrote he's using an encrypted VFS. Perhaps the problem disappears when > he decodes the file? > I tried to do the same for the decrypted file and on another computer, with much more memory. The same result. Probably I I

[sqlite] integrity_check "out of memory"

Hi, I found with the web search that this error (out of memory for PRAGMA integrity_check) appeared in the wild and there were some fixes related to it. Today I decided to do the check for a base that lives with its own encrypted vfs and with an sqlite version older than the current. But the

Re: [sqlite] sqlite 3.7.13 download link

Ok, I see, the problem is I sometimes reach pages from web search. So the query [sqlite download] shows in the google www.sqlite.org/download.html/ (with slash), it's identical to the one without slash, but every link at this

[sqlite] sqlite 3.7.13 download link

Hi, Is everything ok with amalgamation link for sqlite 3.7.13? When I click on the link nothing happens, different browsers, local networks, the same result. Just in case for the TCP/IP request on 80 port www.sqlite.org /download.html/sqlite-amalgamation-3071300.zip The header is HTTP/1.0 200

Re: [sqlite] free list performance

On Mon, Apr 23, 2012 at 3:35 PM, Simon Slavin wrote: > > For the second one, the simplest way to do something like defragment the data > inside the file is to use the VACUUM command: Simon, thanks, I see what vacuum can do, sure I use it frequently as many of us.

[sqlite] free list performance

Hi, Some time ago I worked with a database repeating the same sequence of actions multiply times. They're basically: - create table - populate table - do some deletes with some criteria - drop table After about 20 times I started to notice the usual effects of internal fragmentation (slowness in

Re: [sqlite] Optimize Table Access Efficiency

On Wed, Mar 21, 2012 at 1:33 AM, Tim Morton wrote: > So it seems the index is no help; > Are you sure you did as Simon explained? Becausem my tests shows the trick works... (sqlite 3.7.10) Create table [TestTable] ([Id] integer primary key, [topics] Text, [Definition]

Re: [sqlite] Optimize Table Access Efficiency

Hi, Tim On Tue, Mar 20, 2012 at 6:21 PM, Tim Morton wrote: > > Is there a way to read only the part of the file that has the queried > table/column thus saving time and hard dive grinding? > > There is also a trick. You can create an index on 'topics' column and perform

Re: [sqlite] The number of parameters passed to user functions

On Tue, Mar 13, 2012 at 3:16 PM, Jean-Christophe Deschamps wrote: > Why do you register the function with -1 in the first place? > Register both 2- and 3-parms functions (if you have those two variations), > both pointing to the same function code: argc is valid and let you

[sqlite] The number of parameters passed to user functions

Hi, Is there a way to know the number of parameters passed for user function with undefined argument count (nArg = -1 in the Sqlite3_create_function call). I could not find a function specific to this task and thought that I at least can check sqlite3_values array members for zero for my

Re: [sqlite] A possible bug probably partially fixed before

On Fri, Mar 9, 2012 at 12:04 PM, Max Vlasov <max.vla...@gmail.com> wrote: > So looks on the way from 3.6.10 to 3.7.10 something was really fixed > related to this issue, but seems like not everything. > > Don't know whether this is serious or a problem at all, but

Re: [sqlite] A possible bug probably partially fixed before

On Fri, Mar 9, 2012 at 12:04 PM, Max Vlasov <max.vla...@gmail.com> wrote: > 3.6.10 - > ... actualrndid never gets higher than 900 (wrong) > Sorry, correction, never gets lower than 900 Max ___ sqlite-users mailing list sqlite-users@

[sqlite] A possible bug probably partially fixed before

Hi, I experimented with random numbers recently (with an older version of sqlite) and notice a strange behavior, I tried to reproduce this with the latest version and noticed that it was probably partially fixed, but still returns wrong results for my query. The simplest table possible CREATE

Re: [sqlite] Once again about file change counter

On Tue, Mar 6, 2012 at 9:55 PM, Roger Binns <rog...@rogerbinns.com> wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 06/03/12 01:56, Max Vlasov wrote: > > I don't see a problem here. > > ... For example the user could use the app for 3 hours and

Re: [sqlite] Once again about file change counter

On Tue, Mar 6, 2012 at 4:00 PM, Dan Kennedy <danielk1...@gmail.com> wrote: > On 03/06/2012 06:45 PM, Max Vlasov wrote: > >> >>> >> It's an interesting suggestion, thanks. I just thought that using xSync I >> even can only implement my own db c

Re: [sqlite] Once again about file change counter

On Tue, Mar 6, 2012 at 2:37 PM, Dan Kennedy <danielk1...@gmail.com> wrote: > On 03/06/2012 02:41 PM, Max Vlasov wrote: > >> But for correct work this vfs should rely on the >> fact that file change counter will stay the same until the final write to >> the firs

Re: [sqlite] Once again about file change counter

On Tue, Mar 6, 2012 at 12:22 PM, Roger Binns <rog...@rogerbinns.com> wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 05/03/12 23:41, Max Vlasov wrote: > > One of the recent thought I had was implementing time machine vfs. So > > on the develope

[sqlite] Once again about file change counter

Hi, there are some ideas for vfs implementation that would require tracking file change counter. As I recall, I asked about specific api for working with file change counter, and seems like there was no such. On the other side it looks like it's not a problem since probably until db format change

Re: [sqlite] opening places.sqlite from Firefox (10.0.1) profile qAdmin Cannot perform on closed dataset

On Sat, Feb 11, 2012 at 1:56 PM, Christoph Kukulies wrote: > I'm trying to open some Firefox files that are located in the profiles > directory using Sqlite admin > (http://sqliteadmin.orbmu2k.**de/ ). > > I'm getting an error message: > >

Re: [sqlite] Phrase in the docs

On Fri, Feb 10, 2012 at 5:45 PM, Igor Tandetnik wrote: > Value has TEXT affinity, 5 has none. So 5 is converted to '5', and then > lexicographic comparisons are performed. It so happens that all strings in > the Value column lexicographically precede '5'. > > If you wanted

Re: [sqlite] Phrase in the docs

On Fri, Feb 10, 2012 at 4:53 PM, Richard Hipp <d...@sqlite.org> wrote: > On Fri, Feb 10, 2012 at 6:11 AM, Max Vlasov <max.vla...@gmail.com> wrote: > > > Hi, > > working with sqlite and mysql, noticed that they're different in regard > of > > mixed types. &g

[sqlite] Phrase in the docs

Hi, working with sqlite and mysql, noticed that they're different in regard of mixed types. Select '24' < 25 Select 24 < 25 have the same results in MySql and different sqlite. Actually it's no news (my sqlite queries contained CAST(.. as INT) ), but I decided to look at the docs and noticed

Re: [sqlite] Inserts get slower and slower

On Thu, Feb 9, 2012 at 2:08 AM, Steinar Midtskogen wrote: > > When I build my database from scratch using millions of inserts, one > table causes problems. Inserts get slower and slower. I have about > 830,000 inserts for that table. It gets to 300,000 pretty fast, but >

Re: [sqlite] About new ticket "Per page/region checksums"

On Sat, Feb 4, 2012 at 3:51 PM, Alexey Pechnikov wrote: > It's very important but there are some questions about > http://www.sqlite.org/src/info/72b01a982a > Some times ago DRH wrote that checksum calculation don't slow down > SQLite significantly. > But can be this

Re: [sqlite] List of DB virtual modules

On Mon, Jan 23, 2012 at 5:36 PM, Richard Hipp <d...@sqlite.org> wrote: > On Mon, Jan 23, 2012 at 8:06 AM, Max Vlasov <max.vla...@gmail.com> wrote: > > > > it there an easy way (without manual statements parsing) to get the list > of > > modules used in al

[sqlite] List of DB virtual modules

Hi, it there an easy way (without manual statements parsing) to get the list of modules used in all (if any) virtual tables statements of the schema? For any opened db I wanted to automatically enable found modules (if I recognize them) and warn if there are unsupported ones. Thanks, Max

Re: [sqlite] Some pieces of the puzzle are coming together

On Fri, Jan 20, 2012 at 10:05 PM, John Elrick wrote: > The change which results in a slow down occurred between 3.7.5.0 and > 3.7.6.0. > > What about EXPLAIN difference? Or just outputs of this prefix from both versions? Max

Re: [sqlite] Some pieces of the puzzle are coming together

John, another suggestion Can you test previous sqlite versions one by one towards older ones with one of your problem queries until the results are "good" again and send both good and bad EXPLAIN QUERY output here. I'm sure this will be greek for most of us :), but when Richard or Dan look at the

Re: [sqlite] Collecting return values from sql commands using SQLite APIs.

On Thu, Jan 19, 2012 at 1:49 PM, bhaskarReddy wrote: > > Hi Friends, > > is it possible to collect return value of a SQL command. ex: > SELECT COUNT(*) FROM table; will return number of records in a table. Is > there any SQLite API to return this value. > > It's the

Re: [sqlite] msize in 3.7.10

On Wed, Jan 18, 2012 at 4:47 PM, Richard Hipp <d...@sqlite.org> wrote: > On Tue, Jan 17, 2012 at 9:11 AM, Max Vlasov <max.vla...@gmail.com> wrote: > > > Hi, > > I'm using static linking with Delphi and a new function required binding > in > > 3.7.10 -

Re: [sqlite] msize in 3.7.10

On Wed, Jan 18, 2012 at 4:47 PM, Richard Hipp wrote: > Try using the trunk of the SQLite source tree with the SQLITE_WITHOUT_MSIZE > preprocessor macro. > > > Richard, thank, I will try, but I just tried to replace #if !defined(HAVE_MALLOC_USABLE_SIZE) && SQLITE_OS_WIN # define

Re: [sqlite] msize in 3.7.10

On Tue, Jan 17, 2012 at 6:11 PM, Max Vlasov <max.vla...@gmail.com> wrote: > Hi, > I'm using static linking with Delphi and a new function required binding > in 3.7.10 - msize (__msize). The problem here is that there's no official > way to query the size of a memory block in Del

[sqlite] msize in 3.7.10

Hi, I'm using static linking with Delphi and a new function required binding in 3.7.10 - msize (__msize). The problem here is that there's no official way to query the size of a memory block in Delphi memory manager, at least I'm not aware of one. Should I solve this anyway (for example by keeping

Re: [sqlite] Some pieces of the puzzle are coming together

On Tue, Jan 17, 2012 at 5:05 AM, John Elrick wrote: > > > I can try that approach with BCC. I was concerned that the IDE would be > getting inaccurate information from the .OBJ. Crossing into a pre-complied > library can be tricky. > > As I recall,I could not make

Re: [sqlite] Some pieces of the puzzle are coming together

On Mon, Jan 16, 2012 at 5:37 PM, John Elrick wrote: > > is this test ok for you (still showing bad performance)? > > > > On my side it's about 800 ms for 3.7.9 static and about 6000 memory calls > > during the query. For 3.6.20 the number of calls ~ 7000, the time is >

Re: [sqlite] Some pieces of the puzzle are coming together

On Sun, Jan 15, 2012 at 3:35 AM, John Elrick <john.elr...@fenestra.com>wrote: > On Fri, Jan 13, 2012 at 5:13 PM, Max Vlasov <max.vla...@gmail.com> wrote: > > 5,008 calls to > > UPDATE RESPONSES SET > RESPONSE_NAME = :RESPONSE_NAME, > prelisted_value = :pre

Re: [sqlite] SSD with TRIM

On Mon, Jan 16, 2012 at 12:29 AM, Black, Michael (IS) < michael.bla...@ngc.com> wrote: > > On SSD with 16384 page size and no WAL mode: > > time sqlite3 gen.db > real4m4.816s ... > Note: Database is only 595M with this page size. Much more efficient > storage may explain most of the time

Re: [sqlite] SSD with TRIM

On Sat, Jan 14, 2012 at 11:52 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 14 Jan 2012, at 7:24pm, Max Vlasov wrote: > > > On Sat, Jan 14, 2012 at 6:12 AM, Simon Slavin <slav...@bigfraud.org> > wrote: > > > >> Fast. Fasty fast. S

Re: [sqlite] SSD with TRIM

On Sat, Jan 14, 2012 at 6:12 AM, Simon Slavin wrote: > > Fast. Fasty fast. Speed is high. INSERT, UPDATE, and DELETE all > significantly faster. SELECT is a bit faster, but there's less difference. > > Simon, very interesting. Can you make some tests related to internal

Re: [sqlite] Some pieces of the puzzle are coming together

John, I read your previous post and unfortunately, your conversation with Richard didn't reveal much details. I worked with scenario similar tou yours (Delphi + statically linked sqlite) for several years and during this period an advanced admin/console form was developed that allows dynamical

Re: [sqlite] Resetting virtual table

On Fri, Jan 13, 2012 at 3:02 PM, Dan Kennedy <danielk1...@gmail.com> wrote: > On 01/13/2012 03:31 PM, Max Vlasov wrote: > >> As I see sqlite can accept the schema of a virtual table >> (Sqlite3_declare_vtab) only in the context of xCreate or xConnect. But if >> I &g

[sqlite] Resetting virtual table

Hi, As I see sqlite can accept the schema of a virtual table (Sqlite3_declare_vtab) only in the context of xCreate or xConnect. But if I want to refresh not only data, but schema also, is it possible? For example, I already did a query for my virtual table based on clipboard contents, the

[sqlite] formatting sqlite_master differently

Hi, no question here, just wanted to share a little discovery :) Since sqlite_master is a general table, one can format and order things differently. I used to use simple Select * from sqlite_master ... for querying everyting related to structure, but the following query can show the things

Re: [sqlite] Unexplained minor regression (bug) 3.7.8 up

On Tue, Dec 6, 2011 at 9:49 PM, Dan Kennedy <danielk1...@gmail.com> wrote: > On 12/06/2011 03:28 PM, Max Vlasov wrote: > >> Hi, >> Noticed a strange regression after 3.7.7 (in 3.7.8 and inherited in 3.7.9) >> >> > There is a candidate fix for this i

[sqlite] Unexplained minor regression (bug) 3.7.8 up

(inclusive) returns results (2 rows). 3.7.8 and above shows empty result set Thanks, Max Vlasov ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] splitting a line to produce several lines?

On Mon, Sep 12, 2011 at 12:28 PM, Jean-Denis MUYS wrote: > > My question is: is there a pure SQL way to split the instances string, and > generate the instance lines from the instance list string? contrived example: > This kind of questions appear from time to time

Re: [sqlite] Split Function for SQLite?

On Thu, Aug 25, 2011 at 9:34 AM, Max Vlasov <max.vla...@gmail.com> wrote: > > Also theoretically it is possible to use virtual tables for this. So > when your virtual query implementation accepts list in some way ('23, > 14, 1, 7, 9') and returns the table when querying >

Re: [sqlite] Split Function for SQLite?

On Tue, Aug 23, 2011 at 7:10 AM, Gregory Moore wrote: > I need to split up a list of items in a single row so they each have > their own row. > You can read about my trick query solving partly this task with a trick

Re: [sqlite] Feature request: extend the IN operator

On Sun, Aug 7, 2011 at 3:17 PM, Alexey Pechnikov wrote: > Is it possible to support construction like to > > where x IN "1 2 3" > ... > > Does somebody interesting in this functionality too? > > I needed one, I just implemented mysql find_in_set as a user function. there

Re: [sqlite] Will SQLite supports UnQL?

On Sat, Jul 30, 2011 at 9:45 PM, Alexey Pechnikov wrote: > Very interesting annonce: > http://www.couchbase.com/press-releases/unql-query-language > > Thanks for the info, very interesting indeed. After some reading at http://www.unqlspec.org I'd rather think Richard

Re: [sqlite] Exclusive locking mode not working as expected?

On Fri, Jul 29, 2011 at 10:49 AM, Jonathan Little wrote: > > This behavior seems undesirable to me -- we've got users of our application > copying the database file using Windows Explorer while it's being written > to, and ending up with inconsistent/corrupt databases. Am I

Re: [sqlite] SELECT query first run is VERY slow

On Thu, Jul 28, 2011 at 11:41 AM, Григорий Григоренко wrote: > So, should we file this as a defect or bug somehow? > > As I understand currently the issues acknowledged and fixed during the e-mail conversation in this list ( read http://www.sqlite.org/src/wiki?name=Bug+Reports

Re: [sqlite] SELECT query first run is VERY slow

On Tue, Jul 26, 2011 at 8:50 PM, Григорий Григоренко wrote: > > EXPLAIN QUERY PLAN SELECT * FROM foo WHERE bar = 'three' and id > 290 > LIMIT 10; > > SELECT * FROM foo WHERE bar = 'three' and id > 290 LIMIT 10; > > > Run query. Now using idx2 index SQLITE reads only

Re: [sqlite] SELECT query first run is VERY slow

2011/7/26 Black, Michael (IS) > > Turns out the if you include the primary key in an index it doesn't use the > triple index but uses the primary key instead. And analyze doesn't change > it. > > This is with version 3.7.5 > > Not sure about the primary index , because

Re: [sqlite] SELECT query first run is VERY slow

On Tue, Jul 26, 2011 at 2:18 PM, Simon Slavin wrote: > > On 26 Jul 2011, at 11:05am, Григорий Григоренко wrote: > > > This leads us to conclusion: index in SQLITE database if scattered and > cannot be "jumped directly" to N-th element. SQLITE has to read it somehow >

Re: [sqlite] SELECT query first run is VERY slow

On Mon, Jul 25, 2011 at 5:45 PM, Григорий Григоренко wrote: > > 1) SQLITE has to read about _half of index_ before it can use it (and > understand there are no records matching query). > > If cache is enough to hold 50 Mb then on subsuquent queries sqlite process is > not

Re: [sqlite] SELECT query first run is VERY slow

On Fri, Jul 22, 2011 at 5:25 PM, Григорий Григоренко wrote: > Database is "insert-only". There wasn't any deletes or updates, will VACUUM > actually help in this case? I though it was about unused space? > There's also internal fragmentation coming from the nature of

Re: [sqlite] SELECT query first run is VERY slow

On Fri, Jul 22, 2011 at 3:50 PM, Григорий Григоренко wrote: > Okay, I rebooted and tested again. > > First run took 76 seconds. Read bytes: ~ 307 Mb, Write bytes: ~ 66 Kb. > > Re-running: 1 second, Read bytes: ~ 307 Mb, Write bytes ~ 66 Kb. > > Grigory, you posted to me

Re: [sqlite] SELECT query first run is VERY slow

On Fri, Jul 22, 2011 at 3:36 PM, Григорий Григоренко wrote: > Database extension is ".dblite" > > I'm using Process Explorer > (http://technet.microsoft.com/en-us/sysinternals/bb896653) to monitor > processes cpu and i/o usage. > > During these long running queries I am not

Re: [sqlite] SELECT query first run is VERY slow

On Fri, Jul 22, 2011 at 3:28 PM, Simon Slavin wrote: > > Does Windows XP have some sort of task display where you can see what task is > hogging most of the CPU or disk access ? > The mentioned Task Manager (Ctrl-Alt-Del -> Task Manager) reports both CPU and I/O Read Bytes

Re: [sqlite] SELECT query first run is VERY slow

On Fri, Jul 22, 2011 at 2:38 PM, Григорий Григоренко wrote: > I have a log's database. Schema : > > Query: > > SELECT 1 as today, id as rowid, kind,who,msg,computer,process,created_at,id > FROM log WHERE id > 4070636 AND id <= 9223372036854775807 AND kind = 'info' > AND

Re: [sqlite] randomness issues on windows

On Sat, Jun 25, 2011 at 11:20 PM, bob wrote: > i'm getting an issue relating to the use of the random() function in a > SQL query. > > i run 2 PHP scripts very quickly (less than 1 second apart). > > they use an SQL query like    SELECT * FROM table ORDER BY random() LIMIT 5; >

Re: [sqlite] Substring question

On Sat, Jun 25, 2011 at 9:53 AM, Pete wrote: > I need to select a substring of a column that starts 1 character after a > colon in the column and continues to the end of the column. For example, > if > the column contained "abc:xyz" I want the select statement to return

Re: [sqlite] The last records of a query

On Sat, Jun 25, 2011 at 11:26 AM, Cecil Westerhof wrote: > With LIMIT you can get the first N records of a SELECT. Is it also possible > to get the last N records? > > Use ORDER BY ... DESC. Sure, if you have a large dataset, it'd better be indexed. Max

Re: [sqlite] Query with UNION on large table

On Thu, Jun 23, 2011 at 10:20 PM, Rense Corten wrote: > Wow, I'm almost embarrassed by how helpful you are, gentlemen. Thanks a > lot. > > As to RAM: I'm trying this on two different machines, one with 12 Gb > and one with 32 Gb RAM. I won't be able to get more in the near >

Re: [sqlite] Query with UNION on large table

s the sizes of the tables that should be ordered. Max Vlasov ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Dynamically loaded sqlite (linux)

On Thu, Jun 9, 2011 at 6:21 PM, Pavel Ivanov wrote: > > I know that I should avoid such things, but just curious, is it something > > inside sqlite that probably makes one dynanmically linked and other > > dynamically loaded library share global data and can this be

Re: [sqlite] Dynamically loaded sqlite (linux)

On Thu, Jun 9, 2011 at 6:21 PM, Pavel Ivanov wrote: > > > So if you ever want to use dlopen() you should be really really > careful to avoid loading the same library several times (even if the > same library have different file names). > > Pavel, thanks for the hint and the

Re: [sqlite] Dynamically loaded sqlite (linux)

On Tue, Jun 7, 2011 at 9:22 PM, Martin Gadbois <mgadb...@gmail.com> wrote: > On Tue, Jun 7, 2011 at 12:52 PM, Jay A. Kreibich <j...@kreibi.ch> wrote: > > > On Tue, Jun 07, 2011 at 07:47:25PM +0400, Max Vlasov scratched on the > wall: > > > Hi, > > &

[sqlite] Dynamically loaded sqlite (linux)

Hi, I'm trying to use sqlite with linux (Ubuntu, Pascal, Lazarus). I'm still not very familiar with linux development so I might miss something essential. Two scenarios work ok - statically linked latest version compiled (3.7.6.3), no options or defines changed - Dynamically loaded (dlopen)

Re: [sqlite] Extremely large values error

On Tue, Mar 29, 2011 at 8:17 PM, Ralf Junker <ralfjun...@gmx.de> wrote: > On 29.03.2011 15:17, Max Vlasov wrote: > > > Thanks, this forced me to search more on the topic. I use Delphi and it > > appears that all Borland compilers has their own floating-point exce

Re: [sqlite] Extremely large values error

On Tue, Mar 29, 2011 at 3:56 PM, Richard Hipp <d...@sqlite.org> wrote: > On Tue, Mar 29, 2011 at 4:48 AM, Max Vlasov <max.vla...@gmail.com> wrote: > > > Hi, > > > > I sometimes use repeated digits as test data, for example 123456789 > > repeated > >

[sqlite] Extremely large values error

Hi, I sometimes use repeated digits as test data, for example 123456789 repeated multiply times and recently some of my complex queries where I occasionally wrongly chose a field gave 'unknown error' in the middle of the process. Tracking it led to sqlite3AtoF function that appears to be raising

Re: [sqlite] Best approach for xBestIndex/xFilter effectiveness

On Tue, Mar 22, 2011 at 2:25 PM, Dan Kennedy <danielk1...@gmail.com> wrote: > On 03/22/2011 04:26 PM, Max Vlasov wrote: > > Hi, > > > > recently I finally started experimenting with virtual tables and there's > at > > least one thing I can not understand. >

Re: [sqlite] Create table if not exists and insert seed data

thing like CREATE TABLE IF NOT EXISTS [newTable] AS SELECT * FROM DataToPopulate DataToPopulate can be a table from the db or temporary table created for example when the program starts. Max Vlasov ___ sqlite-users mailing list sqlite-users@sqlite.org

[sqlite] Best approach for xBestIndex/xFilter effectiveness

can. But theoretically for large datasets one should at least think about some optimization. Thanks, Max Vlasov ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting random data from grouped data in one table

up LIMIT 1 OFFSET abs(random() % (SELECT Count(*) FROM TB_Patient WHERE GroupId=$group) Both approaches should scan the table so slow for large tables by design, but I found the latter being a slightly faster, but I'm not sure whether this is always the case. Max Vlasov

Re: [sqlite] Attaching vfs

On Mon, Mar 7, 2011 at 2:56 AM, Roger Binns <rog...@rogerbinns.com> wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 03/06/2011 03:04 PM, Max Vlasov wrote: > > I don't think I need a solution that complex. > > You need to be very careful with your term

Re: [sqlite] Attaching vfs

On Sun, Mar 6, 2011 at 9:00 PM, Roger Binns <rog...@rogerbinns.com> wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 03/06/2011 06:31 AM, Max Vlasov wrote: > > For example, currently it's not > > possible for some exotic vfs to selectively imp

[sqlite] Attaching vfs

format db (CMIIW) There was a suggestion in the list to extend the Attach syntax. I think it's easier and safer to do this with a pragma, that temporally points the the name of vfs to be used for attach command. This pragma can empty by default for any newly opened db (the same vfs assumed) Max

Re: [sqlite] EXT :Re: COUNT() extremely slow first time!

ity either drops or we're starting to hit the bandwidth of > SSD+Sqlite -- I'm tempted to say that it's not the probability that drops. > > > From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on > behalf of Max Vlasov [ma

Re: [sqlite] COUNT() extremely slow first time!

On Sat, Feb 26, 2011 at 1:03 AM, Greg Barker wrote: > harddrive. Can you confirm this? > > > Giving this, if the fastest is 3.6 seconds, you have a very fast > I can confirm this, my tests were run on a machine that uses a solid state > drive. > > Hmm, yesterday something

Re: [sqlite] COUNT() extremely slow first time!

ow operation regardless of the tweaks we can use, almost in every scenario it's equivalent to reading all the data of the table. I suppose the only exception is if your records is very big to take advantage of bypassing overflow data. So, probably in a table with 100 r

Re: [sqlite] COUNT() extremely slow first time!

or me? > > On Wed, Feb 23, 2011 at 5:09 AM, Max Vlasov <max.vla...@gmail.com> wrote: > > > Greg, you should also take the record size into account. My hypothesis is > > that if your record is comparatively small (several fits into 1024) the > > speed of select c

Re: [sqlite] COUNT() extremely slow first time!

On Tue, Feb 22, 2011 at 9:59 PM, Greg Barker wrote: > I'm currently dealing with a similar issue. I've found that the page_size > PRAGMA setting can have a dramatic effect on how long it takes to "warm up" > the table. On Windows 7, with page_size=1024, a SELECT

Re: [sqlite] Asymmetric keys encryption

On Wed, Feb 23, 2011 at 1:03 AM, H. Phil Duby <phild...@phriendly.net>wrote: > On Tue, Feb 22, 2011 at 9:07 AM, Philip Graham Willoughby > <phil.willoug...@strawberrycat.com> wrote: > > > > On 22 Feb 2011, at 15:41, Max Vlasov wrote: > > > The obvi

Re: [sqlite] Asymmetric keys encryption

On Tue, Feb 22, 2011 at 7:07 PM, Philip Graham Willoughby < phil.willoug...@strawberrycat.com> wrote: > On 22 Feb 2011, at 15:41, Max Vlasov wrote: > > The obvious solution is public-key cryptography. The question is about > > different ways how it could be im

[sqlite] Asymmetric keys encryption

in advance, Max Vlasov ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE/INSERTing 1-2k rows slower than expected

On Mon, Feb 14, 2011 at 11:50 PM, Black, Michael (IS) < michael.bla...@ngc.com> wrote: > And if you want to talk about data reliability...BACK UP YOUR DATA. > The likely failure points I can think of are: > #1 Power supply (redundant supplies available) > #2 Hard drive smoked (and your data is

Re: [sqlite] UPDATE/INSERTing 1-2k rows slower than expected

On Mon, Feb 14, 2011 at 8:42 PM, Jim Wilcoxson wrote: > On Mon, Feb 14, 2011 at 10:56 AM, Pavel Ivanov wrote: > >> So my question is, does it maintain the other 3 parts of ACID, so that > >> the database will never be in a corrupted state after a power

Re: [sqlite] UPDATE/INSERTing 1-2k rows slower than expected

On Mon, Feb 14, 2011 at 7:48 PM, Jim Wilcoxson wrote: > On Mon, Feb 14, 2011 at 10:56 AM, Pavel Ivanov wrote: > >> So my question is, does it maintain the other 3 parts of ACID, so that > >> the database will never be in a corrupted state after a power

Re: [sqlite] UPDATE/INSERTing 1-2k rows slower than expected

On Mon, Feb 14, 2011 at 12:05 PM, Simon Slavin wrote: > > The second variation was just unplugging the cord but keeping the power > > intact, so if it's drive that caches, it would end its operations > > completely. This time the results were perfect, for example 4822 ->

Re: [sqlite] UPDATE/INSERTing 1-2k rows slower than expected

was just unplugging the cord but keeping the power intact, so if it's drive that caches, it would end its operations completely. This time the results were perfect, for example 4822 -> 4822, and even 5371 -> 5372 = +1 that actually would mean the process was interrupted after all data is wri

Re: [sqlite] UPDATE/INSERTing 1-2k rows slower than expected

On Sun, Feb 13, 2011 at 5:31 PM, Jim Wilcoxson <pri...@gmail.com> wrote: > On Sun, Feb 13, 2011 at 1:15 AM, Max Vlasov <max.vla...@gmail.com> wrote: > > So the final results: > > - the db was always ok and contains the correct value (id=10001 for > initial > &g

Re: [sqlite] UPDATE/INSERTing 1-2k rows slower than expected

initial 1). - the speed was about 227 commits per second so very close to my system flush test (253) So if the drive is 5400 rpm, 227 is much more than 5400/60=90 and even if it's 7200 (manufacturers sometimes upgrade drives inside portable hd without prior notice), it

Re: [sqlite] UPDATE/INSERTing 1-2k rows slower than expected

1. WAL is significantly faster in most scenarios". So the hint about wal can look like this: if possible number of commits per second can vary up to 30 per second, one should use WAL. Max Vlasov ___ sqlite-users mailing list sqlite-users@sqlit

Re: [sqlite] UPDATE/INSERTing 1-2k rows slower than expected

one entry with 'FlushBuffersFile' that is as I suppose was a mirror name for FlushFileBuffers in winSync of sqlite so probably 3 syncs was a too big an estimate, wasn't it? In this case 50 commits per second looks reasonable limit Max Vlasov ___ sqlite-users ma

Re: [sqlite] EXT :Re: Bi-directional unique

Michael, another small improvement... to take advantage of effective integer packing you can encode two numbers interleaving them dedicating odd bits to one and even to another. This will require extra steps in the program so this is your turn to decide worth it or not. Max On Wed, Feb 9, 2011

Re: [sqlite] Cause of "disk I/O errors"

On Mon, Jan 31, 2011 at 4:25 AM, GHCS Software wrote: > > So it seems it must be something about their particular machine > environment, but what? I'm sure that nobody is running out of disk space > these days. Any ideas on where to look or what to suggest to them that >

Re: [sqlite] Boost copy from one table to another ?

On Fri, Jan 28, 2011 at 11:38 AM, Pierre Chatelier wrote: > > But I wondered if I could improve the performance with the following scheme > : > my disk DB is B > I create a memory DB, with the same structure, named A > I attach B to A > then in a a loop >I insert the

<    1   2   3   4   5   >