I might not go down the threading path at all, as in theory it wouldn't
gain me that much - I'd still need to lock/unlock the database for each
of the threads and I think the overhead of that plus the fact that it
will have to wait for the other thread to do its stuff wouldn't gain me
much if
Dennis Volodomanov wrote:
[...]
The reason that such a huge amount of statements needs to be executed so
many times very quickly is that we have a tree built up based on those
statements and that tree needs to be pruned if the results of statements
are empty in real-time as the user is typing a
Dan Kennedy wrote:
Even using the prepare_v2() interface, the database handle error-code
and error-message (the stuff returned by sqlite3_errcode() and
sqlite3_errmsg() respectively) are not populated by sqlite3_step().
After sqlite3_step() reports an error you need to call either
Hi everyone,
I'm trying to speed up index creation:
CREATE TABLE keyword (key, contyp int, imagecount int, searchcat int,
value, nextword, sec, ipr, fldseq int);
CREATE INDEX valuekey on keyword (value, key);
The value field is a string, max 15 bytes. The key field is a string of
fixed-width 10
Really interesting.
Is there an easy way of producing the big sqlite3.c from CVS head? I'm
curios to run some of my tests this way.
-i
[EMAIL PROTECTED] wrote:
Joe Wilson [EMAIL PROTECTED] wrote:
What operations/work patterns will benefit most from
PRAGMA locking_mode = EXCLUSIVE?
Can
Joe Wilson wrote:
EXPLAIN SELECT ... is also a good way to find out what the queries are doing
if you're prepared to decipher the opcodes.
Joe,
I had looked at the explain output and it wasn't clear which would be
faster. The count method produces less VDBE code but involves calls to
Hi Bob, always nice to meet a fellow Cochran!
This is DB2/400 for V5R1 of OS/400, and yes it is stored in EBCDIC. I am
using the IBM supplied .Net Managed Provider to read the data into a C#
program, (which I believe automatically converts it to ASCII) and then using
the SQLite Managed Provider
Is there any documentation about how to analyze the results of EXPLAIN?
Or even better is there a utility that could analyze (at least to some
extent) the results for you?
RBS
Joe Wilson wrote:
EXPLAIN SELECT ... is also a good way to find out what the queries are
doing
if you're prepared
Did you create the index before or after populating the database?
--
Joel Cochran
On 3/27/07, Stephen Toney [EMAIL PROTECTED] wrote:
Hi everyone,
I'm trying to speed up index creation:
CREATE TABLE keyword (key, contyp int, imagecount int, searchcat int,
value, nextword, sec, ipr, fldseq
Dennis Volodomanov wrote:
I might not go down the threading path at all, as in theory it wouldn't
gain me that much - I'd still need to lock/unlock the database for each
of the threads and I think the overhead of that plus the fact that it
will have to wait for the other thread to do its stuff
Why not get a better database by stripping out the trailing spaces?
Joel Cochran wrote:
Hi Bob, always nice to meet a fellow Cochran!
This is DB2/400 for V5R1 of OS/400, and yes it is stored in EBCDIC. I am
using the IBM supplied .Net Managed Provider to read the data into a C#
program,
[EMAIL PROTECTED] wrote:
Is there any documentation about how to analyze the results of EXPLAIN?
Or even better is there a utility that could analyze (at least to some
extent) the results for you?
The documentation you need is in the VDBE tutorial at
http://www.sqlite.org/vdbe.html and the
at least for anyone who has worked with
assembly level programming
That are not many then, but thanks and will have a look at the
documentation you mentioned.
RBS
[EMAIL PROTECTED] wrote:
Is there any documentation about how to analyze the results of EXPLAIN?
Or even better is there a
After. I create it after because the total populate-plus-index time is
much slower if the index is created before (44 minutes compared to 25).
Thanks, Joel! Any suggestions?
Meta-question: this is the second time I've asked this question. The
first was about a month ago and got not a single
Joe Wilson developir-/[EMAIL PROTECTED] writes:
Perhaps some JDBC drivers implement the behavior you expect, but
technically, you should call addBatch() to add each individual SQL
statement to the batch prior to calling executeBatch().
Yes, I tried that too before posting. Unfortunately,
Yes, that's what I have decided to do: I am going to trim off the trailing
blank on the way into SQLite.
Thanks everyone,
--
Joel Cochran
On 3/27/07, John Stanton [EMAIL PROTECTED] wrote:
Why not get a better database by stripping out the trailing spaces?
Joel Cochran wrote:
Hi Bob,
Stephen Toney wrote:
Meta-question: this is the second time I've asked this question. The
first was about a month ago and got not a single reply. Is there
something wrong with my postings? Or is this just not an interesting
topic?
I think it just boils down to how much time people have.
Thanks, Martin,
Still, even if my indexing can't be speeded up, this seems like an
important question, as I can't see why creating one index with two words
would take several times as long as creating two indexes with one word
each. Either my mental model or SQLite'd indexing is screwy. I'm
Stephen Toney wrote:
Meta-question: this is the second time I've asked this question. The
first was about a month ago and got not a single reply. Is there
something wrong with my postings? Or is this just not an interesting
topic?
Stephen,
There is nothing wrong with your question. In
Perl does pretty slick work on CSV type files.
On Mon, 26 Mar 2007, Joel Cochran wrote:
Hi Puneet,
I probably shouldn't have said that they 'ignore' the blanks, but they are
capable of treating them as white space for text matching purposes. I can't
speak for Oracle, but I'm pretty sure
Is there any difference in an index created like this:
Create table 'table1'([ID] INTEGER PRIMARY KEY)
with this:
Create table 'table1'([ID] INTEGER)
Create unique index idx_table1_ID on table1(ID)
I tended to use the first form, but as that can make subsequent table
inserts or deletes slower
On Tue, 2007-03-27 at 11:53 -0600, Dennis Cote wrote:
Stephen Toney wrote:
Meta-question: this is the second time I've asked this question. The
first was about a month ago and got not a single reply. Is there
something wrong with my postings? Or is this just not an interesting
topic?
Dennis Cote wrote:
Stephen Toney wrote:
Meta-question: this is the second time I've asked this question. The
first was about a month ago and got not a single reply. Is there
something wrong with my postings? Or is this just not an interesting
topic?
Stephen,
There is nothing wrong with
Stephen Toney wrote:
On Tue, 2007-03-27 at 11:53 -0600, Dennis Cote wrote:
Stephen Toney wrote:
Meta-question: this is the second time I've asked this question. The
first was about a month ago and got not a single reply. Is there
something wrong with my postings? Or is this just not an
On Tue, 2007-03-27 at 13:12 -0600, John Stanton wrote:
I suspect that the timing difference is due to page overflows. I did
only a cursory browse of the B-Tree code but it is just a guess. A test
would be to make a simple table with two adjacent integer columns and
time raising an index
On Tue, 2007-03-27 at 13:24 -0600, John Stanton wrote:
Another reason for my puzzlement -- although I love SQLite, my
expectations are based on using Foxpro for many years. Foxpro's indexing
speed for a problem like this is about 10 - 20 times faster. And I've
never come across a Foxpro
Hi all
Is there a way to detect if a transaction is active on the current connection?
Basically the equivalent of PQtransactionStatus in postgresql.
I need that to automate rollback or commit depending on other variables within
my application while keeping the connection open.
Thanks!
Nicolas
[EMAIL PROTECTED] wrote:
Hi all
Is there a way to detect if a transaction is active on the current connection?
Basically the equivalent of PQtransactionStatus in postgresql.
I need that to automate rollback or commit depending on other variables
within my application while keeping the
- Original Message
From: [EMAIL PROTECTED] [EMAIL PROTECTED]
To: sqlite-users@sqlite.org
Sent: Tuesday, March 27, 2007 1:50:51 PM
Subject: Re: [sqlite] Transaction detection...
[EMAIL PROTECTED] wrote:
Hi all
Is there a way to detect if a transaction is active on the
RB Smissaert wrote:
Is there any difference in an index created like this:
Create table 'table1'([ID] INTEGER PRIMARY KEY)
with this:
Create table 'table1'([ID] INTEGER)
Create unique index idx_table1_ID on table1(ID)
I tended to use the first form, but as that can make subsequent table
Thanks for that.
So if I can then I should create the table with INTEGER PRIMARY KEY.
Is it right that this won't affect the speed of any subsequent inserts or
deletes?
About the single quotes etc:
This is VB code, so I can't do:
Create table table1(ID INTEGER PRIMARY KEY)
I can do:
Create table
John Stanton wrote:
I suspect that the timing difference is due to page overflows.
John,
I doubt that that is the case. The two fields being indexed are the
first field, and a second one that is only separated from the first by
the size of the first string (10 bytes) and three integers
RB Smissaert [EMAIL PROTECTED] wrote:
Thanks for that.
So if I can then I should create the table with INTEGER PRIMARY KEY.
Is it right that this won't affect the speed of any subsequent inserts or
deletes?
That depends on the data.
If you insert records in order of ascending integer
RB Smissaert wrote:
Is it right that this won't affect the speed of any subsequent inserts or
deletes?
Well inserts will be done in id order. If you have predefined ids
assigned by some outside source and specify them when you insert into
sqlite, it will have to insert at random location in
Thanks for clarifying that.
I think all the data to be inserted in tables with an INTEGER PRIMARY KEY
will be sorted on that key, but I will have to check as sometimes these
tables can be big, say a few million rows.
RBS
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL
Hi Joel,
It's been a while since I've had to code DB2 and my memory might be
faulty. But the SQL standard says char(n) has to be padded with trailing
spaces, right? See this http://troels.arvin.dk/db/rdbms/
under the heading The CHAR type.
Thanks
Bob Cochran
Joel Cochran wrote:
Hi Bob,
Looks then that doing the table creation with INTEGER PRIMARY KEY
Is the way to go, but as always it will come down to a lot of testing.
As to quotes etc.
As my code works fine as it is I probably will leave this as the double
quotes look ugly and it will be a reasonably big job to alter all
No, that's ok :) We are pretty much stuck with SQL as the application
itself holds various data - the tree is just one small representation of
a subset of information that we store.
-Original Message-
From: Martin Jenkins [mailto:[EMAIL PROTECTED]
Sent: Tuesday, March 27, 2007 8:59
Unfortunately I don't have access to multiple CPUs - it's a 32bit
Windows application, so at best it would run on 2 CPUs at any given
time, provided Windows managed those correctly in the first place :)
Thanks for the link - I'll give it a read. The insert and delete speeds
fully satisfy our
One more question that I was pondering upon - does the order of rows and
their placement in the table matter at all when using indexes (provided
indexes are used correctly)?
Oh, and another :) Would such a statement use both indexes or just one?
CREATE INDEX indexA on tableA (columnA, columnB,
Dennis Cote wrote:
John Stanton wrote:
I suspect that the timing difference is due to page overflows.
John,
I doubt that that is the case. The two fields being indexed are the
first field, and a second one that is only separated from the first by
the size of the first string (10 bytes)
Dennis Volodomanov [EMAIL PROTECTED] wrote:
One more question that I was pondering upon - does the order of rows and
their placement in the table matter at all when using indexes (provided
indexes are used correctly)?
I don't really understand the question...
Oh, and another :) Would such
Thank you for the reply.
One more question that I was pondering upon - does the
order of rows
and their placement in the table matter at all when using indexes
(provided indexes are used correctly)?
I don't really understand the question...
What I meant was - if I have a table like:
--- [EMAIL PROTECTED] wrote:
If you insert records in order of ascending integer primary
key, then the inserts will be very fast. If you insert records
where the integer primary key is randomized, inserts will be
reasonably fast until the size of your table exceeds the size
of your disk
Dennis Volodomanov [EMAIL PROTECTED] wrote:
Thank you for the reply.
One more question that I was pondering upon - does the
order of rows
and their placement in the table matter at all when using indexes
(provided indexes are used correctly)?
I don't really understand the
Ok, thank you for clarifying those points to me.
At the moment I'm struggling to understand why running a
SELECT ROWID FROM TableA WHERE colA='a'
Takes almost twice as long as
SELECT ROWID FROM TableA WHERE colA='b'
Running on the same table with the same data with colA indexed.
- Original Message -
From: John Stanton [EMAIL PROTECTED]
To: sqlite-users@sqlite.org
Sent: Wednesday, March 28, 2007 7:42 AM
Subject: Re: [sqlite] CREATE INDEX performance
I retract the overflow page theory on your compelling evidence and now
understand better what it is doing
Dennis Volodomanov [EMAIL PROTECTED] wrote:
Ok, thank you for clarifying those points to me.
At the moment I'm struggling to understand why running a
SELECT ROWID FROM TableA WHERE colA='a'
Takes almost twice as long as
SELECT ROWID FROM TableA WHERE colA='b'
Running on the same
I want to express my appreciation for this valuable contribution.
I have taken the liberty of simplifying this source code to three files:
func_ext.c, map.c, and map.h. Otherwise, all that's needed is the SQLite
source code. Because config.h doesn't seem to be created in the
configuration
On 3/28/07, Joe Wilson [EMAIL PROTECTED] wrote:
I believe 2 measures when applied together would significantly
improve insert speed of indexed rows:
1. Allot each index/btree a contiguous region of the database file in
which to grow without conflicting with the other indexes' pages and pages
of
You don't. All I can imagine is that you insert rows in sorted sequence
in an initial load so that a simple row scan delivers the rows in key order.
Mohd Radzi Ibrahim wrote:
- Original Message - From: John Stanton [EMAIL PROTECTED]
To: sqlite-users@sqlite.org
Sent: Wednesday, March
I have a table and indices as follows;
CREATE TABLE employee(name TEXT,salary INTEGER,job TEXT);
CREATE INDEX idx_emp_salary_job ON employee(salary,job);
CREATE INDEX idx_emp_job ON employee(job);
When I use the following query, SQLite seems to use idx_emp_salary_job
for both WHERE and GROUP
--- [EMAIL PROTECTED] wrote:
There is a plot of runtime of CVSHEAD versus version 3.3.13
at
http://www.sqlite.org/relspeed-20070326-ephemeral.gif
Any value less than
1.0 means that performance has improved. The test script is
Hi,
My table contains a text field called Name. Let the data be
Name
---
Abc
abcd
AB
cab
def
I want to selcct all the rows which contains the term ab (not case
sensitive). How can I do this?
Thanks in advance,
Lloyd
-
This email was sent using
54 matches
Mail list logo
