When you design a database do not mix record (row) structures in a
single table (variant records). That used to be done with ISAM files but
is no longer necessary with the advent of the RDBMS.
Also you should design your database to have "Third Normal Form" which
means that each data item is
Hi Ken,
you can get the exact insert speed of the flatfile.dat:
- dump your data into the flat file
- create a virtual table implementation for your flat file
http://www.sqlite.org/cvstrac/wiki?p=VirtualTables&1150734307
- and use it from SQLite
http://www.sqlite.org/lang_createvtab.html
"P Kishor" wrote,
On 3/15/07, jose isaias cabrera <[EMAIL PROTECTED]> wrote:
"P Kishor" wrote,
..
> Any basic tutorial in normalization and table design
> will help you tremendously in figuring an efficient solution to your
> problem.
Any suggestions on the web?
On 3/15/07, jose isaias cabrera <[EMAIL PROTECTED]> wrote:
"P Kishor" wrote,
..
> Any basic tutorial in normalization and table design
> will help you tremendously in figuring an efficient solution to your
> problem.
Any suggestions on the web?
http://www.google.com/search?q=normalization
"P Kishor" wrote,
José,
As Igor rightly points out, you are doing the "wrong" thing with this
tool. A perfect database program, and SQLite approaches that in its
simplicity, acts as simply an ultra-efficient device to store and
retrieve your data. Everything else is up to you... like a perfect
"Igor Tandetnik" wrote,
jose isaias cabrera <[EMAIL PROTECTED]>
wrote:
I have a system that links two or more records to one head record
using a column called ProjID, where ProjID has the unique recNO of the
head
record. Anyway, what I would like to do is something like this...
Imagine,
José,
As Igor rightly points out, you are doing the "wrong" thing with this
tool. A perfect database program, and SQLite approaches that in its
simplicity, acts as simply an ultra-efficient device to store and
retrieve your data. Everything else is up to you... like a perfect
audio speaker that
On 3/15/07, T <[EMAIL PROTECTED]> wrote:
Hi All,
Following in a similar thread to Shilpa's request:
> Are there database schema's (eg. for Phonebook ) available on the
> net?
Are there any templates out there for SQLite databases? In
particular, I'm after SQLite and PHP integrated solutions,
Hi All,
Following in a similar thread to Shilpa's request:
Are there database schema's (eg. for Phonebook ) available on the
net?
Are there any templates out there for SQLite databases? In
particular, I'm after SQLite and PHP integrated solutions, such as
perhaps a shopping cart, or
I'm experiencing the same problem on QNX compiled for Renesas SH4...
> This is not a bug in SQLite - it is a bug in Apple's implementation
>(or more precisely their lack of implementation) of POSIX advisory
> locks for AppleShare mounted volumes. The SQLite sources include
> an (Apple-supplied)
jose isaias cabrera <[EMAIL PROTECTED]>
wrote:
I have a system that links two or more records to one head record
using a column called ProjID, where ProjID has the unique recNO of the
head
record. Anyway, what I would like to do is something like this...
Imagine,
recNo,ProjID,Invoice,Fund
Greetings!
I have a system that links two or more records to one head record using a
column called ProjID, where ProjID has the unique recNO of the head record.
Anyway, what I would like to do is something like this... Imagine,
recNo,ProjID,Invoice,Fund
1,1,,
2,1,10.00,30.00
3,1,20.00,60.00
Ken <[EMAIL PROTECTED]> wrote:
>
> When the DB is closed when in synchrounous mode,
> is it then persistent at the OS level even from power failures etc?
>
You don't have to close the DB. All you have to do is
commit. Before the commit finishes, all of your data
is guaranteed to be on
DRH,
Thanks for your valuable insite.
When the DB is closed when in synchrounous mode, is it then persistent at the
OS level even from power failures etc?
[EMAIL PROTECTED] wrote: Ken wrote:
>
> I should be able to run with synchronous=off. Since
> the application maintains
Thanks DRH... That worked.
Ken
[EMAIL PROTECTED] wrote: Ken wrote:
> How does one set the page_size ?
>
> according to the documentation
> "The page-size may only be set if the database has not yet been created.
> "
>
> So how do you execute the pragma prior to creating the DB?
Tito,
Its even better now!
Synchronous=normal and No primary keys (except 1 table) for auto increment.
real0m1.975s
user0m1.436s
sys 0m0.140s
Vs flat file test case:
real0m0.862s
user0m0.228s
sys 0m0.188s
This is now very respectable.
Thanks,
Ken
Ken <[EMAIL PROTECTED]> wrote:
> How does one set the page_size ?
>
> according to the documentation
> "The page-size may only be set if the database has not yet been created.
> "
>
> So how do you execute the pragma prior to creating the DB? without calling
> sqlite3_open to get a
"Griggs, Donald" wrote on 03/15/2007 01:49:30 PM:
> Regarding:
> Creation of flat file takes 1.5 secs vs 3 seconds to create sqlite db.
> Flat file is 13 MB, sqlite db is 11 MB.
>
> "Any ideas how to get the sqlite output timings to a more respectable
> level would be appreciated. "
>
I
Ken <[EMAIL PROTECTED]> wrote:
>
> I should be able to run with synchronous=off. Since
> the application maintains state in a seperate DB elsewhere.
>
Just to clarify the implications where, if you run with
synchronous=off and you take a power failure or an OS
crash in the middle of a
Tito,
There are no indices built besides the default ones. Hmm maybe I should try
this by dropping the primary Keys.. I'll give that a try as well, GOOD idea!
The entire batch of inserts (about 8 tables) is done in a single transaction.
As an Oracle DBA, I'm pretty familar with tuning.
How does one set the page_size ?
according to the documentation
"The page-size may only be set if the database has not yet been created. "
So how do you execute the pragma prior to creating the DB? without calling
sqlite3_open to get a DB handle that is needed to call prepare/step ?
Donald,
I set the PRAGMA synchronous= OFF and here are the results:
real0m2.258s
user0m1.736s
sys 0m0.168s
--
Pragma synchronous= NORMAL
real0m2.395s
user0m1.520s
sys 0m0.128s
Pragma synchronous= FULL
real0m3.228s
user
Hello,
IIRC (it was a while ago), one way to speed up insertion for large
data sets is to drop the indexes, do the inserts (wrapped around a
transaction) and then rebuild the indexes. For smaller data sets, the
drop/rebuild indexes solution doesn't make sense because the time it
takes to
There are no free lunches. When Sqlite stores your data item it not
only writes it into a linked list of pages in a file but also inserts at
least on key into a B-Tree index. It does it quite efficiently so what
you are seeing is the inevitable overhead of storing the data in a
structured
Scott,
The whole job is wrapped in an explicit transaction.
Variables are bound and statements prepared only once, using reset.
This is a write only app. 100% insert.
Ken
Scott Hess <[EMAIL PROTECTED]> wrote: Are you using explicit transactions at
all? If not, as a quick test,
ok my bad for poor wording...
I'll try with Synchronous off. I may also try disabling the journal file since
I can easily recreate the data if it is not successful.
Thanks,
Ken
"Griggs, Donald" <[EMAIL PROTECTED]> wrote: Regarding:
Creation of flat file takes 1.5 secs vs 3 seconds to
To answer your question:
Yes I can use a flat file at this stage, but eventually it needs to be
imported into some type of structure. So to that end I decided early on to use
sqlite to write the data out.
I was hoping for better performance. The raw I/O to read the data and process
is
Regarding:
Creation of flat file takes 1.5 secs vs 3 seconds to create sqlite db.
Flat file is 13 MB, sqlite db is 11 MB.
"Any ideas how to get the sqlite output timings to a more respectable
level would be appreciated. "
I may be way off base if I'm not understanding correctly, but how can
Ken wrote:
I'm looking for suggestions on improving performance of my sqlite application.
Here are system timings for a run where the sqlite db has been replaced with a flat file output.
real 0m1.459s
user0m0.276s
sys 0m0.252s
This is a run when using sqlite as the output
Hello,
I'm trying to use the update hook functionality. I have lookup (aka
join) tables that provide many-to-many relationships between rows in
other tables. The problem is when I get the delete notification for the
join tables the rowid is not useful in that context. I really need to
know the
I use the tilde '~' character a lot.
On Thu, 15 Mar 2007, Griggs, Donald wrote:
> Regarding: "What is the default separator?" [for using with .import in
> the command line utility]
>
> It is the vertical bar ("|", virgule, pipe character).
>
> By the way, I don't *think* that .import allows
I'm looking for suggestions on improving performance of my sqlite application.
Here are system timings for a run where the sqlite db has been replaced with a
flat file output.
real 0m1.459s
user0m0.276s
sys 0m0.252s
This is a run when using sqlite as the output format.
real
Samuel R. Neff wrote:
Dennis,
Do any database systems actually implement this part of the standard? MSSQL
used the term information schema in their MSSQL 2000 version of metadata
access but afaik it was not close to the ANSI standard at all. It's a been
a while for me but I think the Oracle
Thanks very much Igor,
that explains it,
cheers,
Stef Mientki
Igor Tandetnik wrote:
Stef Mientki <[EMAIL PROTECTED]> wrote:
So I would expect that I now can always double quote the selection
fields, but unfortunately that doesn't seem to be true.
From a graphical design, I get for instance:
Dennis Cote schrieb:
I think it should be possible to create a subset of the standard
information schema in sqlite using virtual tables.
That would be very nice and consistent !
Marten
-
To unsubscribe, send
35 matches
Mail list logo