Re: [sqlite] add new column to table
Op maandag 10 januari 2005 23:56, schreef Lloyd Thomas: > Thanks. That is going OK but I am having a problem with updating the new > column with the info I need. It seems to update with the same entry from my > users table to all rows. > > UPDATE call_data SET caller_name = (SELECT firstname || surname AS > 'caller_name' FROM users WHERE extn_no = (SELECT extn_no FROM call_data)); > > I have missed something? The FAQ gives a working example, I used it a lot, please try that. Bert Verhees > > > > - Original Message - > From: "Lloyd Thomas" <[EMAIL PROTECTED]> > To: > Sent: Monday, January 10, 2005 9:08 PM > Subject: Re: [sqlite] add new column to table > > > Thanks Paul, > >I have used that example before to recreate a table. Can I > > use the same thing to recreate a table and populate a new column with > > data from a select query. > > The table I wish to recreate has an ID number in it and I wish to use > > this to get the forename and surname from another table. > > > > Lloyd > > - Original Message - > > From: "Paul Dixon" <[EMAIL PROTECTED]> > > To: > > Sent: Monday, January 10, 2005 5:50 PM > > Subject: Re: [sqlite] add new column to table > > > >> Lloyd Thomas wrote: > >>>I wish to create a new column in a table and add data, which is queried > >>>from another table.What is the best way? > >> > >> There's no "ALTER TABLE" support in the support language, so you have to > >> recreate the entire table and re-populate it. > >> > >> Check the FAQ: http://www.sqlite.org/faq.html#q13 > >> > >> Paul -- Met vriendelijke groet Bert Verhees ROSA Software
Re: [sqlite] V2.8 or V3.0 for embedded devices?
Thanks for the answers so far; there have been some interesting suggestions ( for example, I didn't know about the SQLITE_OMIT). Still, I would like to know more about my main concern - memory consumption. Somewhere I read V2.8 holds the first 240 bytes or so of each row in memory, while V3.0 holds more than 1000 bytes per row cached. If that's true, it would be something I worry about. Btw, the database of our application can get beyond 30,000 rows with a file size of around 8 MB file. Regards, Markus
Re: [sqlite] excessive malloc() calls
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
~From what I can tell people are just in shock and awe that checking 3000
tables each holding several years of data for a company (again: several
years of data for 3000 different companies) calls malloc() several
million times.
Interesting enough, somebody came up with a hackish solution that could
probably be written to be more clean.
Matthew Arrington gives the below code:
#define SQLITE_WORK_BUFF_SIZE (128*1024) // make power of 2
#define SQLITE_WORK_BUFF_MASK (SQLITE_WORK_BUFF_SIZE-1)
char sqlite_workBuff[SQLITE_WORK_BUFF_SIZE];
int sqlite_writeIdx=0;
void *SQLITE_ALLOC(int nBytes)
{
~ void *ret;
~ sqlite_writeIdx = (sqlite_writeIdx + nBytes) & SQLITE_WORK_BUFF_MASK;
~ ret = sqlite_workBuff + sqlite_writeIdx;
~ sqlite_writeIdx+=nBytes;
~ return ret;
}
this idea could take being expanded on; as is it does leave room for
many screw-ups and hardcore memory corruption, especially in threaded
environments.
Steve Frierdich wrote:
| I have been noticing all the email messages about excessive malloc
| calls. Is there a serious bug in Sqlite about malloc being called
| excessively causing memory leaks in sqlite version 3? And if there is,
| is there a way to fix it the source code?
|
| Thank
|
| Steve
|
| D. Richard Hipp wrote:
|
|> Andrew Shakinovsky wrote:
|>
|>> I have noticed with SQLite (at least this was true with 2.8x, not
|>> sure about
|>> 3x) that if you try to use an ORDER BY with a table that doesn't have an
|>> index on the field you are ORDERing by, it will do the entire sort
|>> (presumably just the keys) in memory. This will cause you to run out of
|>> memory if the table is too large.
|>>
|>
|> This is also true of version 3.0 and (the soon to be released) version
|> 3.1. I believe this constraint is documented somewhere, though I
|> cannot say where right off hand. Somebody please correct me (and
|> submit documentation patches) if I am wrong.
|>
|>
|
- --
All content of all messages exchanged herein are left in the
Public Domain, unless otherwise explicitly stated.
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.0 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFB43M3hDd4aOud5P8RApMzAJ4+qkchPTbM4CF9DWrIblE4AJHLRACffZON
mc8txoELVoMtnqph6G2+jX4=
=KoXS
-END PGP SIGNATURE-
Re: [sqlite] $ in table and column names
On Jan 10, 2005, at 6:00 PM, D. Richard Hipp wrote: > A user has reported a bug saying that SQLite does > not allow the '$' in the middle of indentifiers > (without quoting). The bug reports says that > statements like this: > >CREATE TABLE ex$1( col$abc INTEGER ); > > are legal and work fine in other database engines. > This seems very odd to me. Can anybody else > confirm the use of '$' in the middle of identifier > names in other database engines? Firebird (and Interbase) use RDB$ as a prefix for many built-in names. The Interbase 6 Language Reference says > When an applications programmer or end user creates a database > object or refers to it by name, case is unimportant. The following > limitations on naming database objects must be observed: > Start each name with an alphabetic character (AZ or az). > Restrict object names to 31 characters, including dollar signs > ($), underscores (_), 0 to 9, A to Z, and a to z. Some objects, such > as constraint names, are restricted to 27 bytes in length. My reading of the SQL-92 spec leads me to believe that this is an extension. The spec only seems to require followed by | where is (AZ or az). On the other hand, other characters are permitted based on a if the character is "identified as a letter," "syllable," or "ideograph"; the only preclusion is that the : > shall not include a character or other e
Re: [sqlite] $ in table and column names
On Jan 10, 2005, at 6:00 PM, D. Richard Hipp wrote: A user has reported a bug saying that SQLite does not allow the '$' in the middle of indentifiers (without quoting). The bug reports says that statements like this: CREATE TABLE ex$1( col$abc INTEGER ); are legal and work fine in other database engines. This seems very odd to me. Can anybody else confirm the use of '$' in the middle of identifier names in other database engines? PostgreSQL allows it, but their documentation specifically mentions that it's not standard SQL to allow it. -- Randall Randall <[EMAIL PROTECTED]> Property law should use #'EQ , not #'EQUAL .
Re: [sqlite] $ in table and column names
Dr. Hipp, D. Richard Hipp wrote: A user has reported a bug saying that SQLite does not allow the '$' in the middle of indentifiers (without quoting). The bug reports says that statements like this: CREATE TABLE ex$1( col$abc INTEGER ); are legal and work fine in other database engines. This seems very odd to me. Can anybody else confirm the use of '$' in the middle of identifier names in other database engines? Yes. The statement works fine on MySQL 4.0.23 and on PostgreSQL 7.4.6, and I can insert values and get them back again. I guess the next question is: Can someone verify that this is part of the SQL standard? HTH Ulrik P. -- Ulrik Petersen, Denmark
Re: [sqlite] excessive malloc() calls
Steve Frierdich wrote: I have been noticing all the email messages about excessive malloc calls. Is there a serious bug in Sqlite about malloc being called excessively causing memory leaks in sqlite version 3? And if there is, is there a way to fix it the source code? There are no memory leaks in SQLite version 3 that I am aware of. The automated test suite makes an extensive search for memory leaks using an instrumented version of malloc and reports that there are none. There are no serious bugs in SQLite version 3 that I am aware of. The ORDER BY clause might cause more malloc()s to be done than are strictly necessary. But on a system with a good malloc implementation, that will normally go unnoticed. Nevertheless, we will be fixing the problem. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
[sqlite] $ in table and column names
A user has reported a bug saying that SQLite does not allow the '$' in the middle of indentifiers (without quoting). The bug reports says that statements like this: CREATE TABLE ex$1( col$abc INTEGER ); are legal and work fine in other database engines. This seems very odd to me. Can anybody else confirm the use of '$' in the middle of identifier names in other database engines? -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] excessive malloc() calls
> SELECT x, y, z FROM table1 ORDER BY 2; > SELECT x, y, z FROM table1 ORDER BY y; > ... > This is a standard SQL thing, apparently. It surprised me too > when I first found out about it (and had to fix SQLite to do it.) Using an integer is generally deprecated. It was left in the std to avoid breaking old code. Aside from theoretical objections, consider potential practical problems, e.g., suppose you write a bunch of code using 'order by 5' and then someone decides to drop column 4. Regards, Kurt Welgehausen
Re: [sqlite] add new column to table
Thanks. That is going OK but I am having a problem with updating the new column with the info I need. It seems to update with the same entry from my users table to all rows. UPDATE call_data SET caller_name = (SELECT firstname || surname AS 'caller_name' FROM users WHERE extn_no = (SELECT extn_no FROM call_data)); I have missed something? - Original Message - From: "Lloyd Thomas" <[EMAIL PROTECTED]> To: Sent: Monday, January 10, 2005 9:08 PM Subject: Re: [sqlite] add new column to table Thanks Paul, I have used that example before to recreate a table. Can I use the same thing to recreate a table and populate a new column with data from a select query. The table I wish to recreate has an ID number in it and I wish to use this to get the forename and surname from another table. Lloyd - Original Message - From: "Paul Dixon" <[EMAIL PROTECTED]> To: Sent: Monday, January 10, 2005 5:50 PM Subject: Re: [sqlite] add new column to table Lloyd Thomas wrote: I wish to create a new column in a table and add data, which is queried from another table.What is the best way? There's no "ALTER TABLE" support in the support language, so you have to recreate the entire table and re-populate it. Check the FAQ: http://www.sqlite.org/faq.html#q13 Paul
Re: [sqlite] excessive malloc() calls
I have been noticing all the email messages about excessive malloc calls. Is there a serious bug in Sqlite about malloc being called excessively causing memory leaks in sqlite version 3? And if there is, is there a way to fix it the source code? Thank Steve D. Richard Hipp wrote: Andrew Shakinovsky wrote: I have noticed with SQLite (at least this was true with 2.8x, not sure about 3x) that if you try to use an ORDER BY with a table that doesn't have an index on the field you are ORDERing by, it will do the entire sort (presumably just the keys) in memory. This will cause you to run out of memory if the table is too large. This is also true of version 3.0 and (the soon to be released) version 3.1. I believe this constraint is documented somewhere, though I cannot say where right off hand. Somebody please correct me (and submit documentation patches) if I am wrong.
Re: [sqlite] excessive malloc() calls
On Mon, 10 Jan 2005 17:07:11 -0500, D. Richard Hipp <[EMAIL PROTECTED]> wrote: > This is a standard SQL thing, apparently. It surprised me too > when I first found out about it (and had to fix SQLite to do it.) Very interesting. Thanks.
Re: [sqlite] excessive malloc() calls
Tom,
Yes I agree. It does make a lot of assumptions. I would not consider using
that code permanently without knowing exactly the way the system worked,
and then I'd add error checking code that would detect misuse and/or
overflow at run time. We use this type of allocation scheme for real time
rendering where I work without any problems.
I think the system would benefit greatly from using a simple memory
allocation scheme instead of the brute force malloc() method when it needs
temporary storage during a prepare(), step(), and finalize() sequence.
Matt
Tom
<[EMAIL PROTECTED]
> To
sqlite-users@sqlite.org
01/10/2005 01:39 cc
PM
Subject
Re: [sqlite] excessive malloc()
Please respond to calls
[EMAIL PROTECTED]
te.org
I am sorry, typing too fast I had made typos which made my message not
understandable. So here it is again:
I would say it is one of the most dangerous code snippets I have seen
in a while.
The code makes a lot of assumptions but they are NOT explicitly stated.
It may work under some strictly defined conditions but after several
months you or anyone else modifying the code may forget what those
conditions were and it would lead to a memory corrupting bug which
would be very hard to trace.
Tom
Abracode
On Jan 10, 2005, at 3:15 PM, Tom wrote:
> I would say it is one of the most dangerous code snippets I have seen
> in a while.
> The code makes a lot of assumption but they are explicitly stated. It
> may work under some strictly defined conditions but after several
> months you or anyone else modifying may forget what those conditions
> were and it would lead to a memory corrupting bug which would be very
> hard to trace.
>
> Tom
> Abracode
>
> On Jan 10, 2005, at 2:39 PM, [EMAIL PROTECTED] wrote:
>
>> Windows XP, visual studio .NET.
>> The malloc call ends up calling RtlAllocateHeap().
>>
>> I hacked the code to allocate the aType structure from a circular ring
>> buffer. This small change made my app go from spending 60% of its
>> time in
>> malloc to about 3%.
>>
>> Here's the allocator I hacked in. No freeing is needed, the buffer
>> just
>> wraps. This assumes that by the time a wrap occurs, memory perviously
>> allocated at the top is no longer needed. It seems to work ok from my
>> limited testing. :-)
>>
>> #define SQLITE_WORK_BUFF_SIZE (128*1024) // make power of 2
>> #define SQLITE_WORK_BUFF_MASK (SQLITE_WORK_BUFF_SIZE-1)
>> char sqlite_workBuff[SQLITE_WORK_BUFF_SIZE];
>> int sqlite_writeIdx=0;
>>
>> void *SQLITE_ALLOC(int nBytes)
>> {
>> void *ret;
>> sqlite_writeIdx = (sqlite_writeIdx + nBytes) &
>> SQLITE_WORK_BUFF_MASK;
>> ret = sqlite_workBuff + sqlite_writeIdx;
>> sqlite_writeIdx+=nBytes;
>> return ret;
>>
>> }
>>
>> Matthew Arrington
>> Sony Computer Entertainment America
>
>
>
Re: [sqlite] excessive malloc() calls
Will Leshner wrote: On Mon, 10 Jan 2005 13:49:21 -0800, [EMAIL PROTECTED] By the way, "ORDER BY 1" I believe to mean order by column id = 1. So it's not really a meaningless statement. Is that true? I don't get the from the documentation, but I'm probably just looking at it wrong. Yes. You can put integer constants in an ORDER BY or GROUP BY clause and they mean "use the N-th column of the result set". So SELECT x, y, z FROM table1 ORDER BY 2; SELECT x, y, z FROM table1 ORDER BY y; are equivalent. In fact, the first is transformed into the second as part of the compilation process. This is a standard SQL thing, apparently. It surprised me too when I first found out about it (and had to fix SQLite to do it.) -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] excessive malloc() calls
Derrell Thanks for that! I did not have the primary key set up right. (I'm an SQL newbie) which forced me to use "order by." By the way, "ORDER BY 1" I believe to mean order by column id = 1. So it's not really a meaningless statement. I think any "order by clause" is going to cause a per row memory allocation. Matt [EMAIL PROTECTED] wiredUniverse.com To 01/10/2005 11:24 sqlite-users@sqlite.org AM cc Subject Please respond to Re: [sqlite] excessive malloc() [EMAIL PROTECTED] calls te.org [EMAIL PROTECTED] writes: > I switched my application over to SQLite3 and did some performance > profiling and found that the majority of the processing time spent is > making calls to malloc(). > > sqlite3_step() is the function that is making all the excessive calls, one > call per row fetched. > > The program is a stock scanning / data mining program. It keeps about 6-7 > years worth of daily stock price data for a company in a table, and I have > about 3000 of these tables. One complete scan of all 3000 companies will > make roughly 5.5 million malloc() calls! > > I create each table using SQL command: > CREATE TABLE 'SPY' (key INTEGER PRIMARYKEY UNIQUE, open FLOAT, low FLOAT, > high FLOAT, close FLOAT, volume FLOAT) Firstly, if "key" is your primary key, you need not specify UNIQUE because primary keys are, by definition, unique. Secondly, I don't know if sqlite understands "PRIMARYKEY". The more standard way of specifying that is with a space in it: "PRIMARY KEY". You can confirm whether sqlite understood this to be your primary key field by doing the following from the sqlite shell: .mode line pragma table_info(SPY); If the "pk" field shows as '1', then it knows it's a primary key; otherwise it does not. > When I read a table I use the following SQL select statement. > SELECT * FROM 'SPY' ORDER BY 1 ASC "ORDER BY 1" is probably causing an additional calculation for each row, in order to sort by whatever that means (which is probably nothing). If you discard the ORDER BY clause, the results will be in primary key order. You could be explicit about it by saying "ORDER BY key" (with or without the ASC indication, since that's the default). > I debugged / stepped in to the step() code and noticed that it is the op > code COLUMN making the memory allocation. I think it may be my ORDER BY > clause that is causing it to take the path to the malloc() call. Without looking in more detail at the code, that would be a reasonable supposition. It probably allocates memory during each step() call, to contain the field which will be sorted by. I suspect that in your query, it's allocating space to put the constant value "1"... a not very useful piece of information to be allocating for and saving. Cheers, Derrell
Re: [sqlite] excessive malloc() calls
Andrew Shakinovsky wrote: I have noticed with SQLite (at least this was true with 2.8x, not sure about 3x) that if you try to use an ORDER BY with a table that doesn't have an index on the field you are ORDERing by, it will do the entire sort (presumably just the keys) in memory. This will cause you to run out of memory if the table is too large. This is also true of version 3.0 and (the soon to be released) version 3.1. I believe this constraint is documented somewhere, though I cannot say where right off hand. Somebody please correct me (and submit documentation patches) if I am wrong. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] excessive malloc() calls
[EMAIL PROTECTED] wrote: > > The "order by" clause I think is what causes the memory allocation of > "aType" for each row. I think you are right. The aType is cached when data is coming out of the database file so the allocation only occurs once. But when data is coming out of the sorter, the aType must be reallocated and recomputed each type a column of the result set is referenced. This needs to be worked on. > > This does point out however, that Win32 users who need to use ORDER > BY on a large table are paying a hefty price. > Win32 users pay a hefty price for lots of thing ;-) -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] excessive malloc() calls
On Mon, 10 Jan 2005 13:49:21 -0800, [EMAIL PROTECTED] > By the way, "ORDER BY 1" I believe to mean order by column id = 1. So it's > not really a meaningless statement. Is that true? I don't get the from the documentation, but I'm probably just looking at it wrong.
RE: [sqlite] excessive malloc() calls
I have noticed with SQLite (at least this was true with 2.8x, not sure about 3x) that if you try to use an ORDER BY with a table that doesn't have an index on the field you are ORDERing by, it will do the entire sort (presumably just the keys) in memory. This will cause you to run out of memory if the table is too large. >>-Original Message- >>From: Will Leshner [mailto:[EMAIL PROTECTED] >>Sent: Monday, January 10, 2005 4:49 PM >>To: sqlite-users@sqlite.org >>Subject: Re: [sqlite] excessive malloc() calls >> >> >>On Mon, 10 Jan 2005 13:43:20 -0800, >>[EMAIL PROTECTED] >>> This does point out however, that Win32 users who need to >>use ORDER BY >>> on a large table are paying a hefty price. >> >>Is that true even if they are using ORDER BY properly? >>
Re: [sqlite] excessive malloc() calls
On Mon, 10 Jan 2005 13:43:20 -0800, [EMAIL PROTECTED] > This does point out however, that Win32 users who need to use ORDER BY on a > large table are paying a hefty price. Is that true even if they are using ORDER BY properly?
Re: [sqlite] excessive malloc() calls
I'm just doing the select statement I mentioned in my first message. SELECT * FROM 'SPY' ORDER BY 1 ASC; "1" is the column id which in my case I thought was a primary integer key. The "order by" clause I think is what causes the memory allocation of "aType" for each row. I did not create the primary key properly which someone pointed out. With a proper primary key, I was able to get rid of the ORDER BY clause and the per row memory allocation went away. This does point out however, that Win32 users who need to use ORDER BY on a large table are paying a hefty price. Thanks, Matt "D. Richard Hipp" <[EMAIL PROTECTED]> To 01/10/2005 12:58 sqlite-users@sqlite.org PM cc Subject Please respond to Re: [sqlite] excessive malloc() [EMAIL PROTECTED] calls te.org [EMAIL PROTECTED] wrote: > > I hacked the code to allocate the aType structure from a circular ring > buffer. This small change made my app go from spending 60% of its time in > malloc to about 3%. > I'm very interested in knowing what SQL statements you are running to obtain this result. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] excessive malloc() calls
I am sorry, typing too fast I had made typos which made my message not
understandable. So here it is again:
I would say it is one of the most dangerous code snippets I have seen
in a while.
The code makes a lot of assumptions but they are NOT explicitly stated.
It may work under some strictly defined conditions but after several
months you or anyone else modifying the code may forget what those
conditions were and it would lead to a memory corrupting bug which
would be very hard to trace.
Tom
Abracode
On Jan 10, 2005, at 3:15 PM, Tom wrote:
I would say it is one of the most dangerous code snippets I have seen
in a while.
The code makes a lot of assumption but they are explicitly stated. It
may work under some strictly defined conditions but after several
months you or anyone else modifying may forget what those conditions
were and it would lead to a memory corrupting bug which would be very
hard to trace.
Tom
Abracode
On Jan 10, 2005, at 2:39 PM, [EMAIL PROTECTED] wrote:
Windows XP, visual studio .NET.
The malloc call ends up calling RtlAllocateHeap().
I hacked the code to allocate the aType structure from a circular ring
buffer. This small change made my app go from spending 60% of its
time in
malloc to about 3%.
Here's the allocator I hacked in. No freeing is needed, the buffer
just
wraps. This assumes that by the time a wrap occurs, memory perviously
allocated at the top is no longer needed. It seems to work ok from my
limited testing. :-)
#define SQLITE_WORK_BUFF_SIZE (128*1024) // make power of 2
#define SQLITE_WORK_BUFF_MASK (SQLITE_WORK_BUFF_SIZE-1)
char sqlite_workBuff[SQLITE_WORK_BUFF_SIZE];
int sqlite_writeIdx=0;
void *SQLITE_ALLOC(int nBytes)
{
void *ret;
sqlite_writeIdx = (sqlite_writeIdx + nBytes) &
SQLITE_WORK_BUFF_MASK;
ret = sqlite_workBuff + sqlite_writeIdx;
sqlite_writeIdx+=nBytes;
return ret;
}
Matthew Arrington
Sony Computer Entertainment America
Re: [sqlite] V2.8 or V3.0 for embedded devices?
Christian Smith wrote: For non-text data, SQLite 3.x has more efficient storage, as binary data is stored in binary format and not text format. I've heard figures such as 25% savings on disk space bandied about, but YMMV. The storage is not binary compatible with SQLite 2.8.x files, and SQLite 3 cannot read 2.8.x files. As a data-point, I have a project I'm currently working on, in which all of the db data is either text or numeric (no blobs or other non-text fields), and the difference in size when I convert the 2.8 db to 3.0 format is still close to 25%. Randy -- [EMAIL PROTECTED] http://www.rjray.org http://www.svsm.org We will never have true civilization until we have learned to recognize the rights of others.-- Will Rogers
[sqlite] add new column to table
Lloyd, Lloyd Thomas wrote: Thanks Paul, I have used that example before to recreate a table. Can I use the same thing to recreate a table and populate a new column with data from a select query. The table I wish to recreate has an ID number in it and I wish to use this to get the forename and surname from another table. Yes, you can. http://www.sqlite.org/lang.html Both the INSERT statement and the CREATE TABLE statement will let you choose values from a SELECT statement. See the syntax of each at this URL: http://www.sqlite.org/lang.html HTH Ulrik P. -- Ulrik Petersen, MA, B.Sc. University of Aalborg, Denmark
Re: [sqlite] excessive malloc() calls
I would say it is one of the most dangerous code snippets I have seen
in a while.
The code makes a lot of assumption but they are explicitly stated. It
may work under some strictly defined conditions but after several
months you or anyone else modifying may forget what those conditions
were and it would lead to a memory corrupting bug which would be very
hard to trace.
Tom
Abracode
On Jan 10, 2005, at 2:39 PM, [EMAIL PROTECTED] wrote:
Windows XP, visual studio .NET.
The malloc call ends up calling RtlAllocateHeap().
I hacked the code to allocate the aType structure from a circular ring
buffer. This small change made my app go from spending 60% of its time
in
malloc to about 3%.
Here's the allocator I hacked in. No freeing is needed, the buffer just
wraps. This assumes that by the time a wrap occurs, memory perviously
allocated at the top is no longer needed. It seems to work ok from my
limited testing. :-)
#define SQLITE_WORK_BUFF_SIZE (128*1024) // make power of 2
#define SQLITE_WORK_BUFF_MASK (SQLITE_WORK_BUFF_SIZE-1)
char sqlite_workBuff[SQLITE_WORK_BUFF_SIZE];
int sqlite_writeIdx=0;
void *SQLITE_ALLOC(int nBytes)
{
void *ret;
sqlite_writeIdx = (sqlite_writeIdx + nBytes) &
SQLITE_WORK_BUFF_MASK;
ret = sqlite_workBuff + sqlite_writeIdx;
sqlite_writeIdx+=nBytes;
return ret;
}
Matthew Arrington
Sony Computer Entertainment America
Re: [sqlite] add new column to table
Thanks Paul, I have used that example before to recreate a table. Can I use the same thing to recreate a table and populate a new column with data from a select query. The table I wish to recreate has an ID number in it and I wish to use this to get the forename and surname from another table. Lloyd - Original Message - From: "Paul Dixon" <[EMAIL PROTECTED]> To: Sent: Monday, January 10, 2005 5:50 PM Subject: Re: [sqlite] add new column to table Lloyd Thomas wrote: I wish to create a new column in a table and add data, which is queried from another table.What is the best way? There's no "ALTER TABLE" support in the support language, so you have to recreate the entire table and re-populate it. Check the FAQ: http://www.sqlite.org/faq.html#q13 Paul
Re: [sqlite] excessive malloc() calls
[EMAIL PROTECTED] wrote: I hacked the code to allocate the aType structure from a circular ring buffer. This small change made my app go from spending 60% of its time in malloc to about 3%. I'm very interested in knowing what SQL statements you are running to obtain this result. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] excessive malloc() calls
Windows XP, visual studio .NET.
The malloc call ends up calling RtlAllocateHeap().
I hacked the code to allocate the aType structure from a circular ring
buffer. This small change made my app go from spending 60% of its time in
malloc to about 3%.
Here's the allocator I hacked in. No freeing is needed, the buffer just
wraps. This assumes that by the time a wrap occurs, memory perviously
allocated at the top is no longer needed. It seems to work ok from my
limited testing. :-)
#define SQLITE_WORK_BUFF_SIZE (128*1024) // make power of 2
#define SQLITE_WORK_BUFF_MASK (SQLITE_WORK_BUFF_SIZE-1)
char sqlite_workBuff[SQLITE_WORK_BUFF_SIZE];
int sqlite_writeIdx=0;
void *SQLITE_ALLOC(int nBytes)
{
void *ret;
sqlite_writeIdx = (sqlite_writeIdx + nBytes) & SQLITE_WORK_BUFF_MASK;
ret = sqlite_workBuff + sqlite_writeIdx;
sqlite_writeIdx+=nBytes;
return ret;
}
Matthew Arrington
Sony Computer Entertainment America
"D. Richard Hipp"
<[EMAIL PROTECTED]>
To
01/10/2005 11:18 sqlite-users@sqlite.org
AM cc
Subject
Please respond to Re: [sqlite] excessive malloc()
[EMAIL PROTECTED] calls
te.org
[EMAIL PROTECTED] wrote:
> I switched my application over to SQLite3 and did some performance
> profiling and found that the majority of the processing time spent is
> making calls to malloc().
It sounds like you have a bad malloc() implementation. What
OS and compiler are you using. Malloc() on linux is normally
very good. Malloc() on win95/98/ME is often quite bad.
Malloc(0 on winNT/2K/XP is in between. Or at least that has
been my experience. If your system gas a slow malloc
implementation, you would do well to use one of the many
fine open-source implementations as a substitute.
>
> I create each table using SQL command:
> CREATE TABLE 'SPY' (key INTEGER PRIMARYKEY UNIQUE, open FLOAT, low FLOAT,
> high FLOAT, close FLOAT, volume FLOAT)
>
> I need each row sorted by date, which I setup as my primary key. Ideally
> each table would be stored sorted on disk since I do a very small amount
of
> updating of only one record per day per table. I could not figure out a
way
> to make do that.
If you put a space between PRIMARY and KEY, it will order the
entrys by the spy.key field. Omit the UNIQUE keyword as that
is redundant. PRIMARY KEYs are always UNIQUE.
>
> The actual code making the call is:
>
> /* Read and parse the table header. Store the results of the parse
> ** into the record header cache fields of the cursor.
> */
> if( pC && pC->cacheValid ){
> aType = pC->aType;
> aOffset = pC->aOffset;
> }else{
> int avail;/* Number of bytes of available data */
> if( pC && pC->aType ){
> aType = pC->aType;
> }else{
> aType = sqliteMallocRaw( 2*nField*sizeof(aType) );
> <<< HERE
> }
> aOffset = &aType[nField];
> if( aType==0 ){
> goto no_mem;
> }
>
The line of code marked should only execute once per
SELECT statement, unless you are running with triggers
in which case it will execute once per row. Either way,
it won't be executing 5.5 million times on a 3000 row
table.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
[sqlite] Question about EXCEPT
Since EXCEPT returns the left side of a query, once duplicates are deleted from the select on the right, how might one get the !Left AND !Right rows, i.e., the rows which are not in *both* the left and right selects. I had thought EXCEPT did this, but it doesn't, and I was wondering if SQlite had a way to do this that did not involve doing except on both, switching the 'left' and the 'right'. --Keith
Re: [sqlite] speed
Hi Brandon, Brandon Whalen wrote: I'm currently trying to use sqlite to manage a database in a c program. I have 4 tables where each table has at most 6 columns. I've found that if I use a select statement and run that statement through a callback function that I get incredibly slow response times. I've found that the select statement itself happens rather fast, but its taking the selected data and sending it to my callbacks that is taking the most time, specifically I've found from gprof: Each sample counts as 0.01 seconds. % cumulative self self total time seconds secondscalls ms/call ms/call name 22.90 1592.40 1592.40 1649956995 0.00 0.00 getPayload 21.97 3120.02 1527.622326265.67 272.85 sqlite3VdbeExec Is this common in sqlite? I've tested my sql statement on the command line and its rather fast, but I've also found that if I run a built in function(count) on the results that it too suffers from a severe slowdown in performance. What you call "the select statement itself" is, I think, just compiling the statement to a program in the virtual machine language. This is very fast. When using built-in functions (such as "count"), SQLite often has to do a full table scan, which can take a long time, since it can't use an index. The getPayload function is defined in btree.c, and is the function that takes values and keys out of the B-Tree. As you can see, it is called a little over 1.6 billion times, which suggests that you have a lot of rows (maybe on the order of 1.6E09/24 ~ 69 million (since you have at most 24 columns). That is a lot of rows, and may induce "poor" performance, regardless of whether you are using SQLite or MySQL, PostgreSQL or any of the other big databases. You should check that indices are used (using the EXPLAIN statement) whenever possible. HTH Ulrik P. -- Ulrik Petersen, MA, B.Sc. University of Aalborg, Denmark
Re: [sqlite] excessive malloc() calls
[EMAIL PROTECTED] writes: > I switched my application over to SQLite3 and did some performance > profiling and found that the majority of the processing time spent is > making calls to malloc(). > > sqlite3_step() is the function that is making all the excessive calls, one > call per row fetched. > > The program is a stock scanning / data mining program. It keeps about 6-7 > years worth of daily stock price data for a company in a table, and I have > about 3000 of these tables. One complete scan of all 3000 companies will > make roughly 5.5 million malloc() calls! > > I create each table using SQL command: > CREATE TABLE 'SPY' (key INTEGER PRIMARYKEY UNIQUE, open FLOAT, low FLOAT, > high FLOAT, close FLOAT, volume FLOAT) Firstly, if "key" is your primary key, you need not specify UNIQUE because primary keys are, by definition, unique. Secondly, I don't know if sqlite understands "PRIMARYKEY". The more standard way of specifying that is with a space in it: "PRIMARY KEY". You can confirm whether sqlite understood this to be your primary key field by doing the following from the sqlite shell: .mode line pragma table_info(SPY); If the "pk" field shows as '1', then it knows it's a primary key; otherwise it does not. > When I read a table I use the following SQL select statement. > SELECT * FROM 'SPY' ORDER BY 1 ASC "ORDER BY 1" is probably causing an additional calculation for each row, in order to sort by whatever that means (which is probably nothing). If you discard the ORDER BY clause, the results will be in primary key order. You could be explicit about it by saying "ORDER BY key" (with or without the ASC indication, since that's the default). > I debugged / stepped in to the step() code and noticed that it is the op > code COLUMN making the memory allocation. I think it may be my ORDER BY > clause that is causing it to take the path to the malloc() call. Without looking in more detail at the code, that would be a reasonable supposition. It probably allocates memory during each step() call, to contain the field which will be sorted by. I suspect that in your query, it's allocating space to put the constant value "1"... a not very useful piece of information to be allocating for and saving. Cheers, Derrell
[sqlite] Thread Checker "Errors"
Hi. I ran Intel's Thread Checker program on a program that uses SQLite. The only "Errors" it found were related to the sqlite_search_count and sqlite3_interrupt_count global variables. I looked at the code, and it seems that those variables are only used for testing particular sections of the library. So, I think that this doesn't matter much, but I was wondering if "#ifdef SQLITE_TEST" lines around the declaration and uses of those global varaibles (and maybe the sqlite3_sort_count) would be a good idea. Mike - Do you Yahoo!? The all-new My Yahoo! What will yours do?
Re: [sqlite] speed
Brandon Whalen wrote: > I've found that if I use a select statement and run that statement > through a callback function that I get incredibly slow response > times. > > I've tested my sql statement on the command line and its rather > fast > The command-line shell is using exactly the same API functions and SQLite library that you are using. If the command-line shell is fast but your code is slow, I would first look for problems in your code. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] excessive malloc() calls
[EMAIL PROTECTED] wrote:
I switched my application over to SQLite3 and did some performance
profiling and found that the majority of the processing time spent is
making calls to malloc().
It sounds like you have a bad malloc() implementation. What
OS and compiler are you using. Malloc() on linux is normally
very good. Malloc() on win95/98/ME is often quite bad.
Malloc(0 on winNT/2K/XP is in between. Or at least that has
been my experience. If your system gas a slow malloc
implementation, you would do well to use one of the many
fine open-source implementations as a substitute.
I create each table using SQL command:
CREATE TABLE 'SPY' (key INTEGER PRIMARYKEY UNIQUE, open FLOAT, low FLOAT,
high FLOAT, close FLOAT, volume FLOAT)
I need each row sorted by date, which I setup as my primary key. Ideally
each table would be stored sorted on disk since I do a very small amount of
updating of only one record per day per table. I could not figure out a way
to make do that.
If you put a space between PRIMARY and KEY, it will order the
entrys by the spy.key field. Omit the UNIQUE keyword as that
is redundant. PRIMARY KEYs are always UNIQUE.
The actual code making the call is:
/* Read and parse the table header. Store the results of the parse
** into the record header cache fields of the cursor.
*/
if( pC && pC->cacheValid ){
aType = pC->aType;
aOffset = pC->aOffset;
}else{
int avail;/* Number of bytes of available data */
if( pC && pC->aType ){
aType = pC->aType;
}else{
aType = sqliteMallocRaw( 2*nField*sizeof(aType) );
<<< HERE
}
aOffset = &aType[nField];
if( aType==0 ){
goto no_mem;
}
The line of code marked should only execute once per
SELECT statement, unless you are running with triggers
in which case it will execute once per row. Either way,
it won't be executing 5.5 million times on a 3000 row
table.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] excessive malloc() calls
>It would seem really useful to be able to compare the performance of >two or more companies. Having each of them in a separate table means to >do any kind of comparison you need a join. It would seem lots better >to have a single table with a column for the company name. Eliminates >LOTS of work for that scenario... I don't compare companies against each other. I just need to get all the price data out of a table (around 1850 rows), sorted by primary key, as fast as possible. (ie. without making 1850 malloc calls.) Thanks, Matt
RE: [sqlite] excessive malloc() calls
Did some hashing code which used pooling and preallocate of block and it made the hash way (technical term) faster in insert and delete operations. As well as much less overhead for small hash object. Basically kept it's own pool of hash items. For SQLite might be harder to implement since things are not as fixed by nature (columns/datatypes), but would get rid of lots of malloc() which are slow as well as costly for internal overhead. Sandy -Original Message- From: Andrew Piskorski [mailto:[EMAIL PROTECTED] Sent: Monday, January 10, 2005 10:28 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] excessive malloc() calls On Mon, Jan 10, 2005 at 09:51:50AM -0800, [EMAIL PROTECTED] wrote: > sqlite3_step() is the function that is making all the excessive calls, one > call per row fetched. > about 3000 of these tables. One complete scan of all 3000 companies will > make roughly 5.5 million malloc() calls! That seems highly non-optimal. If there is no other way around that already present in SQLite, and you don't want to hack one in, then the obvious step might be to link in one of the several high-performance malloc replacement libraries out there, e.g. Hoard: http://www.cs.umass.edu/~emery/hoard/ http://developers.sun.com/solaris/articles/multiproc/multiproc.html Tcl 8.4.x includes a threaded allocator (which is used by AOLserver), which maintains its own thread-local pools of memory and calls the system malloc() underneath only infrequently. It is not intended as a drop in replacement for malloc(), but I THINK the various malloc replacement libraries operate in similar (though more complex) fashions. I'd expect (but have not tested) that these memory pooling techniques would offer you a speed benefit even when you're using only 1 thread, as malloc'ing millions of times is usually not a good idea. -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/
Re: [sqlite] excessive malloc() calls
On Mon, Jan 10, 2005 at 09:51:50AM -0800, [EMAIL PROTECTED] wrote: > sqlite3_step() is the function that is making all the excessive calls, one > call per row fetched. > about 3000 of these tables. One complete scan of all 3000 companies will > make roughly 5.5 million malloc() calls! That seems highly non-optimal. If there is no other way around that already present in SQLite, and you don't want to hack one in, then the obvious step might be to link in one of the several high-performance malloc replacement libraries out there, e.g. Hoard: http://www.cs.umass.edu/~emery/hoard/ http://developers.sun.com/solaris/articles/multiproc/multiproc.html Tcl 8.4.x includes a threaded allocator (which is used by AOLserver), which maintains its own thread-local pools of memory and calls the system malloc() underneath only infrequently. It is not intended as a drop in replacement for malloc(), but I THINK the various malloc replacement libraries operate in similar (though more complex) fashions. I'd expect (but have not tested) that these memory pooling techniques would offer you a speed benefit even when you're using only 1 thread, as malloc'ing millions of times is usually not a good idea. -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/
Re: [sqlite] V2.8 or V3.0 for embedded devices?
There are some changes in CVS that introduced a few SQLITE_OMIT_* macros that are supposed to exclude some features at compile time. Since you're on embedded system, I guess you'll find quite a few features that you can omit, hence reduce memory footprint. I don't think there's any documentation for those macros yet, but check out this timeline http://www.sqlite.org/cvstrac/timeline?d=30&e=2004-Dec-01&c=2&px=&s=0&dt=1&x=1 (search for SQLITE_OMIT) On Mon, 10 Jan 2005 12:02:07 +0100, Markus Oliver Junginger <[EMAIL PROTECTED]> wrote: > We are developing applications that also run on PocketPC devices; Sqlite > 2.8.x is used here as the database. > > For the next release of our software the question is if we should switch > to SQLite 3. What's the experts' recommendation? The main concern is > probably memory consumption, which should be as low as possible. > > Thank you, > Markus > >
[sqlite] speed
I'm currently trying to use sqlite to manage a database in a c program. I have 4 tables where each table has at most 6 columns. I've found that if I use a select statement and run that statement through a callback function that I get incredibly slow response times. I've found that the select statement itself happens rather fast, but its taking the selected data and sending it to my callbacks that is taking the most time, specifically I've found from gprof: Each sample counts as 0.01 seconds. % cumulative self self total time seconds secondscalls ms/call ms/call name 22.90 1592.40 1592.40 1649956995 0.00 0.00 getPayload 21.97 3120.02 1527.622326265.67 272.85 sqlite3VdbeExec Is this common in sqlite? I've tested my sql statement on the command line and its rather fast, but I've also found that if I run a built in function(count) on the results that it too suffers from a severe slowdown in performance.
[sqlite] excessive malloc() calls
I switched my application over to SQLite3 and did some performance
profiling and found that the majority of the processing time spent is
making calls to malloc().
sqlite3_step() is the function that is making all the excessive calls, one
call per row fetched.
The program is a stock scanning / data mining program. It keeps about 6-7
years worth of daily stock price data for a company in a table, and I have
about 3000 of these tables. One complete scan of all 3000 companies will
make roughly 5.5 million malloc() calls!
I create each table using SQL command:
CREATE TABLE 'SPY' (key INTEGER PRIMARYKEY UNIQUE, open FLOAT, low FLOAT,
high FLOAT, close FLOAT, volume FLOAT)
I need each row sorted by date, which I setup as my primary key. Ideally
each table would be stored sorted on disk since I do a very small amount of
updating of only one record per day per table. I could not figure out a way
to make do that.
When I read a table I use the following SQL select statement.
SELECT * FROM 'SPY' ORDER BY 1 ASC
This all seems like it should be pretty basic stuff, that I'd expect an SQL
engine to easily handle. I'm a novice when it comes to SQL, so maybe I'm
doing something dumb?
I debugged / stepped in to the step() code and noticed that it is the op
code COLUMN making the memory allocation. I think it may be my ORDER BY
clause that is causing it to take the path to the malloc() call.
The actual code making the call is:
/* Read and parse the table header. Store the results of the parse
** into the record header cache fields of the cursor.
*/
if( pC && pC->cacheValid ){
aType = pC->aType;
aOffset = pC->aOffset;
}else{
int avail;/* Number of bytes of available data */
if( pC && pC->aType ){
aType = pC->aType;
}else{
aType = sqliteMallocRaw( 2*nField*sizeof(aType) );
<<< HERE
}
aOffset = &aType[nField];
if( aType==0 ){
goto no_mem;
}
Thanks,
Matt Arrington
Re: [sqlite] add new column to table
Lloyd Thomas wrote: I wish to create a new column in a table and add data, which is queried from another table.What is the best way? There's no "ALTER TABLE" support in the support language, so you have to recreate the entire table and re-populate it. Check the FAQ: http://www.sqlite.org/faq.html#q13 Paul
[sqlite] add new column to table
I wish to create a new column in a table and add data, which is queried from another table.What is the best way? Lloyd
Re: [sqlite] V2.8 or V3.0 for embedded devices?
On Mon, 10 Jan 2005, Markus Oliver Junginger wrote: >We are developing applications that also run on PocketPC devices; Sqlite >2.8.x is used here as the database. > >For the next release of our software the question is if we should switch >to SQLite 3. What's the experts' recommendation? The main concern is >probably memory consumption, which should be as low as possible. I've not yet ported any stuff to SQLite 3, so I'm by no means an expert... For non-text data, SQLite 3.x has more efficient storage, as binary data is stored in binary format and not text format. I've heard figures such as 25% savings on disk space bandied about, but YMMV. The storage is not binary compatible with SQLite 2.8.x files, and SQLite 3 cannot read 2.8.x files. SQLite also has better support for concurrent access. But as you're embedded, that's probably of little concern. The main difference is the new API, basically a functional superset of the 2.8.x API, but using a different prefix (sqlite3 versus sqlite) so there will be some effort in porting to the new version. SQLite 2.8.x is now essentially in maintenance mode only, new features will only appear in the 3.x releases, so porting to 3.x will future proof your code for longer. So, to sum up, use 3.x if: - You want smaller databases. - Want better concurrent access. - Want more future proof code. Stay with 2.8.x if: - You want to maintain binary compatibility with existing data. - Don't have time to port to 3.x API. - 2.8.x provides all you need, and is future proof enough. If it ain't broke... > >Thank you, >Markus > Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
[sqlite] V2.8 or V3.0 for embedded devices?
We are developing applications that also run on PocketPC devices; Sqlite 2.8.x is used here as the database. For the next release of our software the question is if we should switch to SQLite 3. What's the experts' recommendation? The main concern is probably memory consumption, which should be as low as possible. Thank you, Markus
