cked it), there's just an advice on the site wiki to change it to the
cluster size immediately after the db creation (
http://www.sqlite.org/cvstrac/wiki?p=PerformanceTuningWindows).
Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://s
he_size. For default sqlite cache size it's rereading of 2M to
3M tables. Not so great disadvantage to change the algorithm.
Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
ilar tests with other front-ends. If
someone really interested, I can upload my tool, but it's just not
completely ready for mainstream.
Thanks,
Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
f the field, a separated
hash could give you speed increase if an average size of your hash entry
much smaller than the size of your name field and you don't expect many
collisions (since in case of collision you still have to access the original
name field that actually kills your attempt to
egrity_check;
Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
can be a separated table with sing field id (rowid) that should
> change
> > its contents synchronously to the main table that contains all data. I
> > suppose in this case the two variants (index vs trigger) is on par in
> terms
> > of the size or am I wrong?
> >
>
> is there a program that converts sqlite database from windows-1252 to utf-8
> ?
>
It the base is not big, I think you can dump the db to sql file with the
sqlite shell, convert this text file to UTF-8 (even with notepad) and feed
it to
On Sat, Sep 11, 2010 at 10:26 PM, Drake Wilson wrote:
> Quoth Max Vlasov , on 2010-09-11 22:16:14 +0400:
> > although index at least should not be worse in any situation
>
> I wouldn't be quite so sure. Would there not be use cases in which
> different sets of columns
I
suppose in this case the two variants (index vs trigger) is on par in terms
of the size or am I wrong?
Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
tioned is TEXT and it's much more larger than all other fields
together,the benefits of using the index for retrieval are not so great,
although index at least should not be worse in any situation
Max
___
sqlite-users mailing list
sqlite-users@sqlite.o
On Sat, Sep 11, 2010 at 2:05 PM, Cory Nelson wrote:
> On Sat, Sep 11, 2010 at 12:36 AM, Max Vlasov wrote:
> > Hi,
> >
> > What other observations can be made about these two types of solutions
> that
> > I may face in the future?
>
> Both are commonly used
t for other db engines.
What other observations can be made about these two types of solutions that
I may face in the future?
Thanks,
Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
is case since a chance of a power or
system failure is higher and higher each day :)
Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
main db
file and shm file is the way to find those pages for readers and for pager
to commit them. The shm file could be hungry for memory, but it's not a big
deal since it's always wants 128 times less then the size of the data
changed.
Max
___
l processor architecture pages) in 386-adress space. The actual
error appears after the MapViewOfFile call and the text is 'Not enough
storage is available to process this command' (Code: 9)
Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
qlite3_sqlite3_f213e133f6.zip
I kind of injected modified os_win.c from the full package into 3_7_2
amalgamation.
Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
c that requires this, but on Windows 350
regions is a maximum in this case. Does it mean that linux share address
space for superset/subset regions in contrary to Windows logic?
Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
e queries are executed, when the shm file grows to 11M
(0xAC), the failure occurs with Disk I/O error (both result and extended
are 10 (SQLITE_IOERR)).
There's a change that there's something wrong with my program, can someone
do a similar test
to the absence of free space. The size of shm file is
0xAC, and the section starting 0xAB8000 till the end filled with zeros.
Please let me know if anything would be helpful to know from these files, I
keep it in the state they were after the failure, I even did not perform
Commit and Close.
Max
_
could not reproduce this when the db is already in WAL mode when
opened. Michele, can you tell us what is the mode when you initially open
db?
Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
for the given type and
when the result of sqlite3_column_type (actual type of a 'cell') doesn't
compatible with this list, I will show some warning about this result set.
Max,
maxerist.net
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
d aesthetics, but leads to bad cases like this. Sure it's
too late to change anything in the SQL, I just wish the creators changed
"SET=" to "SET TO"...
Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
ace you prefer a temporal file. I know it
looks a little stange, but can someone confirm this approach at least works?
Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
t raw partition sqlite in days if one _really_ needs it :) I wonder
whether someone already did it at least for one OS.
Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
red about
the reasons why is implemented the way it is.
You can read it here:
http://www.mail-archive.com/sqlite-users@sqlite.org/msg50941.html
Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
On Tue, Aug 31, 2010 at 9:07 AM, Eric Smith wrote:
>
> You probably don't want 'SELECT max(foo_id)+1' because I think that does
> a full table scan.
>
>
you're right, but slightly modified version SELECT (SELECT max(foo_id)
work perform INSERT INTO MyMainTable SELECT
* FROM MyTempTable. So this is the query that actually affects the contents
of the main db (and writes also to the journal file). This one can be
interrupted with a power failure and in this case on a next open sqlite will
try to restore the state that wa
ent storage every 2 minutes
is ok since if it's a transaction, sqlite will take care of the power loss
consequences regardless of whether it's a single insert or multiply from a
temporal table
Max
___
sqlite-users mailing list
sqlite-users@sqlite
y fraction of time (for example
when the page is already in the cache), this might affect the performance in
general.
Also you can try to compare EXPLAIN QUERY result from a version prior to
3.5.5 to some of the current one for the same sql query. I wanted to do this
myself, but seems like can not acc
o you have reason to
think that it should go to the end?
Max
On Sat, Aug 28, 2010 at 7:03 PM, Simon Slavin wrote:
> Can someone check the source code for me ? Or perhaps this can be found
> from a query plan or something.
>
> A current discussion reminded me that something w
E Word < 'TAKE' ORDER BY Word
DESC LIMIT 100)
UNION
SELECT * FROM (SELECT * FROM MxVft_FTWords WHERE Word >= 'TAKE' ORDER BY
Word LIMIT 100)
ORDER BY Word
I tried it on a real table containing about 1,3M records, EXPLAIN QUERY
PLAN successfully reported about index usage, al
able scan.
>
>
If your csv inserts are made as a whole, what about keeping track of rowid
ranges (rowidfrom..rowitto) for every csv insert in a separate table? You
can be sure it will be a new range not intersecting with any previous if
rowid is AUTOINCREMENT (or you can provide your own id s
as cases like yours is real and can be used in real life, maybe a
change to existing freelist_count pragma is possible? If it is writable
(PRAGMA freelist_count=1024;), sqlite compares the value supplied with the
current count and if it is bigger allocates necessary space. It seems this
syntax
d the docs says there's no guarantee the syntax will
not change and the results will be the same (The recent WAL-related changes
to journal_mode is a good example). In case of api calls, we at least have
compiler's errors and warnings.
Max
me specific cases and if it gets its own pragma it will just
add confusion.
Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Look at this discussion:
http://www.mail-archive.com/sqlite-users@sqlite.org/msg53997.html
at least two variants of the solution there.
Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
problem, but it seems the cause was different to one mentioned by you.
When I narrowed the query to
select * from catalogues
inner join
(select max(validfrom), idcatalogue from CRONOcatalogues) as tbl
on catalogues.idcatalogue=tbl.idcatalogue and catalogues.active='s'
The differe
On Fri, Aug 13, 2010 at 1:38 PM, Max Vlasov wrote:
>
> I can approximately calculate, how big the new database will grow. Is
>> there a way to tell SQLite to reserve an inital space or numer of pages
>> instead of letting the database file grow again and again?
>>
From what I see, it seems that the sqlite internally doesn't rely on the
file size for the core functionality and xFileSize looks more like a service
function, but I may be wrong
Max Vlasov,
www.maxerist.net
___
sqlite-users mailing list
sqlite-user
qlite expects. So before any file system defragmentation,
internal sqlite defragmentation (VACUUM) have to be applied.
Max Vlasov,
maxerist.net
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
elated to some encoding conversion, like
UTF16 in first case and UTF-8 in the other?
Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
ing db right before you with all the statistics
available encourages experimenting opposite to for example mysql looking
like a mountain far away )
Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
ovide more details about the query, maybe narrow it to some easily
understandable sql fragment
Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
ind any value and after that move left while F() is true and move
right while F() is true.
On the other size this kind of search will have either the same
effectiveness as a full scan (in worst case) or better. I suppose this
limitation is also why the queries with complex left parts (even
If the user does not want it, ok, select "never
show again" and forget about it, but personally I'd never switch it off.
Sure it's not a problem for tools that always use sqlite3_bind_text for
display purpuses.
Max
___
sqlite
when several small fields exists and they should be
indexed, one can pack them into id(rowid) to save space and the first syntax
will allow querying more naturally
Thanks,
Max Vlasov
maxerist.net
___
sqlite-users mailing list
sqlite-users@sqlite.org
http
;
>
If I'm asked, the syntax PRAGMA page_count(table_name/index_name) would be
great if such function could be included in future versions. And the old
syntax without names would work as before
Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
e
corresponding functions in the c source and make them public, right?
Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
abase before)?
- Such method is not good for large databases since all the data should be
re-saved just to get the value. If someone suggest an algorithm solving
this, this will be great.
Thanks,
Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
htt
e rows with such values
Max
On Wed, Jul 14, 2010 at 2:43 PM, Riccardo Cohen
wrote:
> Hello
> I've been using sqlite in many projects (thanks for providing it) and
> found today someting strange with sum function
>
> I have a simple database:
>
> CREATE TABLE data (irisid
sqlite3_file*);
int (*xShmLock)(sqlite3_file*, int offset, int n, int flags);
int (*xShmMap)(sqlite3_file*, int iPage, int pgsz, int, void volatile**);
(201007140820)
int (*xShmMap)(sqlite3_file*, int iPg, int pgsz, int, void volatile**);
int (*xShmLock)(sqlite3_file*, int offset, in
on it's ok, WAL already has special conditions on which it would
operate and the current documentation describes them all thoroughly. I wish
only the "advantages" sections of WAL also grew a little, maybe giving more
details about speed impr
rse csv content, I think this feature will
require no more than a couple of hundred lines of code
Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
ignoring extra fields with padding.
I don't know whether the clipboard content can be used in sqlite
ideologically, but in my case it saved additional time allowing importing
for example from excel without the intermediate file (csv).
Thanks
Max Vlasov
www.maxeris
thing to research especially in sqlite perspective,
for example, for reading it's access times that makes SSD winner, so
probably reading large sqlite database randomly can have some benefits being
used on SSD, but I don't know of any real world measurements. Do you know
any?
Max
in WAL mode and the last
reader/writer that closes the base, reverts it back (=1) allowing the file
format stays the same. Sure there are possible cases when the format still
changed (for example unexpected program end), but the next successful
open/close will fix this.
Max
__
a potential way of new wave of mass bug/missing reporting or
simply confusion
I may exaggerate, I suggest other participants of the list share their
thoughts
Thanks,
Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
stem in
Windows is still not smart enough even for much easier SSD-related tasks :)
Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
al", ok
3. Close the database. offsets 18,19 still = 2, so the WAL setting are kept
between sessions.
4. Open the db again, offsets 18,19 still = 2,
query PRAGMA journal_mode; it says "delete", but definetely should return
"wal".
Max
On Thu, Jul 8, 2010 at 12:09 AM,
07060929.zip
- About vfs. Is it ok to implement version 2 of the interfaces and supply it
to older versions? So if the structures contain iVersion=2, will all prior
versions of sqlite3 accept it?
Thanks,
Max,
maxerist.net
___
sqlite-users mailing list
sql
s like this
select count(distinct ((Value1 << 32) | Value2)) FROM TestTable
but I did quick text for a table consisting of thousands of integers, the
speed is similar to your another query that you called not very good. I
think both needs some temporary stor
domly and writing randomly until specially
interrupted. While the program reads and writes you check PRAGMA
integrity_check; from time to time and stop if result shows corruption.
If you can't reproduce the problem on your machine you can send specially
prepared version to the user an
he sources (if it had, this
can be some indication of dynamic dependency).
Max,
maxerist.net
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
se a
query working in both worlds:
SELECT * FROM (SELECT 1) AS TBL WHERE 1=2
?
Thanks
Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
e packages. If I had power, I'd be
glad to calculate and see how many sqlite-related reads and writes are
taking place on the whole earth. I'm sure it's a big number :)
Thank you for sqlite!
Max Vlasov
On Sat, May 29, 2010 at 5:57 PM, D. Richard Hipp wrote:
>
> Thanks, everyb
ear field), this will lead to full
table reading (so all the data of your 4M records). If it's properly
indexed, and the result number of records of this select is big, consider
adding non-indexed fields to this (or brand-new) index since otherwise
sqlite quickly finds records with this index,
queness limitation on its own rowid field, we could see repeating values
or even non numeric values. I think that using _rowid_ might be additional
guarantee from such problems, but I suppose many developers still prefer
rowid.
Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
wondering, was allowing to create such field intentional? As I suppose such
field is complete phantom since most sql queries will interpret rowid
internally and won't allow access this user field at all.
Max
___
sqlite-users mailing list
sqlite-
masterfieldtocheck = okvalue and
>masterfield2tocheck = okvalue2);
>
> --
> Igor Tandetnik
>
>
Thanks,Igor, it works, I see that sql itself is smart enough )
Both suggested by you are similar in speed, but the latter looks more
self-explanatory for me.
Max
_
similar select statement in the second section,
is sqlite optimizer smart enough to recognize that the both parts queries
the same record and not to perform this operation several times?
Thanks
Max,
maxerist.net
___
sqlite-users mailing list
sqlite-users
available on aducom.com, but the version I used had a serious
bug, Albert (from aducom.com) promised to fix on the site, probably you can
use it as of now.
Max
maxerist.net
On Fri, May 21, 2010 at 1:31 PM, Gilles Ganault wrote:
> Hello
>
> My C skills are very basic. I was wonderi
entation on it,
either.)
Thanks again! :)
-Max
--- On Thu, 5/20/10, Pavel Ivanov wrote:
From: Pavel Ivanov
Subject: Re: [sqlite] Loading Sqlite3 DB into memory
To: "General Discussion of SQLite Database"
Date: Thursday, May 20, 2010, 5:13 AM
You can write your own VFS and then u
e for your help! :)
-Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
anges
> on (MyTable.id between IdFrom and IdTo);
>
Yes, thanks, Igor,
Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
a way to use them in joins directly, but if it's not possible,
at least to find an effective way to populate this ranges to a temporary
table. I know there's a always a way to do this algorithmically, but just
wondering if it's possible with sql.
Thanks,
Max Vlas
writes
from several computers you will at least have probability arguments on your
side )
Max Vlasov,
maxerist.net
On Wed, Apr 28, 2010 at 9:43 AM, Jean-Christophe Deschamps
wrote:
> Hi gurus,
>
> I'm aware of the limitations that generally preclude using SQLite over
> a network
l the data from the index itself saving time and
the amount of data flow.
I did a quick test and it showed not only a noticable difference in time,
but also a significant difference in amount of the data read.
Max,
maxerist.net
___
sqlite-users mailing lis
o exclude windows system cache as a player.
Max Vlasov,
maxerist.net
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
** A PENDING_LOCK is obtained by locking a designated byte different from
** the RESERVED_LOCK byte.
It was a note about windows, but there's also a phrase "The same locking
strategy and byte ranges are used for Unix."
Max Vlasov,
maxerist.net
_
kes no sense.
But you probably can work around this with your own mutexes, wrapping your
calls in the corresponding access/release calls, but I suppose there still
could exist some complexities. I didn't try it myself, but I guess there are
some operations that can be implemented safely wi
y part of the base (fast appending), another one will do necessary
selects and inserts to disk db part. After the db is closed, you will only
have disk part left. I don't know more about locking logic in this case,
because for performance reasons, it would be better for example, if memor
need
it, but if you have tow compact indexes indexes only by X and Y, and use
intersect the data flow will be the same regardless of your record size. In
my test case, if I'd added descriptions to the table and every description
were 1000 bytes, the final size would grow from 44M to 1G
the latter reads about 3M of data from this 44M base
(so no full table scan)
You say that your INNER JOIN QUERY faster? You probably have a different
scheme, maybe that's the reason, but please let us know in this case
Max
___
sqlite-users mai
; the background of my question:
>
>
Tim,
maybe drh answer on my question regarding INTERSECT could help?
http://www.mail-archive.com/sqlite-users@sqlite.org/msg49646.html
Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sql
l too), you gave a very detail description of the
situation. Although unlikely your interesting suggestion (VACUUM TO) will be
implemented, I think one always make his own equivalent if it is necessary
(creating a subset of operations from sqlite3RunVacuum)
Max
__
about sqlite3RunVacuum that it needs
more space than a simple emulation of its actions?
Thanks
Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
SIS
This option modifies the query pattern parser in FTS3 such that it
supports operators AND and NOT (in addition to the usual OR and NEAR) and
also allows query expressions to contain nested parenthesesis.
Max
___
sqlite-users mailing list
sqlite-us
or, Title)
VALUES
((Select Max(rowid) FROM Authors), "test")
just worked (tested it), sure you have to make id autoincrement to ensure
Max(rowid) is actually the last author
Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqli
ust enumerating records building
the tree. CMIIW
Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
done in order to
reverse changes. So you could create your own log table inside your sqlite
base logging your changes.It works if all the changes is controlled by you,
so before any DELETE, INSERT, UPDATE you can prepare this information and
write
like moving from
INTEGER to TEXT? Also is it possible to add to sqlite a limited ALTER TABLE
.. MODIFY support that could wrap all known "safe" changes and invoking
errors on all "unsafe" ones?
Max
___
sqlite-users mailing list
sql
y reads. In this case reader and write don't have strict
requirements about locking (at least the reader can be more important in
this case). You can tune their relationship in a way when raw writer and
sqlite writer are usually synchronized, but if the reader is more active
there are temporary m
ot; error.
Instead consider changing your own logic. You wrote "without* any
possibility to be blocked". I suppose you already have a perfect writer that
fits your needs, but if you post some info about the nature of your writer
and reader (records pe
u have enough time, I'd do this before anything else
Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
ote from the docs:
This function returns the number of row changes caused by INSERT, UPDATE or
> DELETE statements since the database connection was opened.
>
Either you're or this sentence on the site should be changed (in the final
part)
Max
ffected and
simple SELECT afterward, this call will still return 2. In this case I'd
recommend using difference between consequitive sqlite3_total_changes()
values. For any read-only query this difference will always be zero.
Max
___
sqlite-users mailing lis
ndexing
the size has changed to 1,5G. And I even didn't use stop-words. So with
proper stop-words usage the ratio can even be better.
Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
ves just indexes (based on anything but your
blobs) and these blobs are accessed on some final stage of the query, go
with blobs inside sqlite base.
Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
core
functionality.
What you think? Maybe such addition is too specific?
Thanks
Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
tion will be included in the article about
sqlite3_reset some day,
Thanks
Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
301 - 400 of 482 matches
Mail list logo
