Christian Werner <[EMAIL PROTECTED]> writes:
> Jarl Friis wrote:
>
>> I have fixed a bug in the SQLite ODBC driver. The problem is that the
>> ODBC driver does not use the info from "PRAGMA table_info(...)" when
>> there are no columns declared as "integer".
>
> What exactly is wrong with the
"Roger Binns" <[EMAIL PROTECTED]> writes:
>> (by noticable I mean around 750 benchmark queries per second slower)).
>
> Since you only give the change, not the total, that is hard to put into
> context. eg if the total is 750,000 then it is within the margin of error.
>
>> The main question is,
Because
Joe Wilson <[EMAIL PROTECTED]> writes:
> Hello Jarl,
>
> I would be interested in seeing the answers to your ODBC
> driver questions, as well as Christian Werner's opinion of your
> patch, if it is no trouble for you.
>
> Thanks.
>
I will forward Christians answer, and I suggest the
(by noticable I mean around 750 benchmark queries per second slower)).
Since you only give the change, not the total, that is hard to put into
context. eg if the total is 750,000 then it is within the margin of error.
The main question is, is the method listed above the best way to improve
I've been doing a fair bit of research into optimising larger tables and
just want a bit of feedback (most of my lecturers are giving conflicting
answers).
Just say I have a table with 200+ fields (all relate uniquly to the primary
key), when querying the first 10 fields of a table I get just
Dennis Brakhane wrote:
On 3/9/06, John Stanton <[EMAIL PROTECTED]> wrote:
If two processes want to submit a
transaction, one must basically wait until the other finishes.
The problem with this is that the other process cannot finish. (See my
previous post)
Maybe I should file a bug report,
Ah.. okay - you did not mention tables t1 and t2
were in different database files. That is likely why
this case was never tested. Explicit cross joins
are the way to go - you know the data better than
the database.
Read about SQLite's CROSS JOIN logic here:
I've had similar speed increases using the same technique
you've described for the last 10 revs of SQLite. I never
had any noticable improvement when fiddling with the
SQLite cache parameters. I just assumed it was a Linux thing.
But maybe not.
> So what I tried next was to simply run my app
Interesting... I googled cross join and got the following definition:
"A cross join (or Cartesian Product join) will return a result table where
each row from the first table is combined with each row from the second
table. The number of rows in the result table is the product of the number
- Original Message
> From: Christian Smith <[EMAIL PROTECTED]>
> Is this a likely usage scenario? Will your application regularly
> umount/mount the filesystem between transactions? While sounding
> facetious, I'm not trying to. Your otherwise excellent example is let down
> by a
CROSS JOIN is an SQLite-specific thing to disable
table join optimization.
Please post the schema and indexes of yours tables
so that this bug may be corrected when CROSS JOIN is not used.
--- Steve Green <[EMAIL PROTECTED]> wrote:
> Interestingly, using
>
> from t1
> cross join t2
>
Interestingly, using
from t1
cross join t2
fixes the problem; using this, causes indices from both tables to be used...
addr opcode p1 p2 p3
-- -- -- -
37OpenRead0 4
38
maybe "t2 cross join t1" makes more sense given the
sizes of the tables.
--- Joe Wilson <[EMAIL PROTECTED]> wrote:
> Hard to say what's the problem if you don't post the
> schema of the tables and the indexes, and provide some
> sample data.
>
> Perhaps there is a bug in the join optimizer.
>
Hard to say what's the problem if you don't post the
schema of the tables and the indexes, and provide some
sample data.
Perhaps there is a bug in the join optimizer.
Try using an explicit CROSS JOIN.
select t1.a, t1.b, t2.c, t2.d
from t1
cross join t2
where t1.x = t2.x and t1.a
The performance is exactly the same after running analyze on both tables.
Steve
Joe Wilson wrote:
Run an ANALYZE statement on your database and your
queries will be fast once again.
Perhaps SQLite should fall back to the old non-optimized
join behavior when the sqlite_stat1 table is
Run an ANALYZE statement on your database and your
queries will be fast once again.
Perhaps SQLite should fall back to the old non-optimized
join behavior when the sqlite_stat1 table is missing.
This would cover 99% of the pre-SQLite3.2.3 legacy databases
out there where the queries have
On 3/9/06, John Stanton <[EMAIL PROTECTED]> wrote:
> If two processes want to submit a
> transaction, one must basically wait until the other finishes.
The problem with this is that the other process cannot finish. (See my
previous post)
Maybe I should file a bug report, shouldn't I?
A short
On Mar 8, 2006, at 8:01 AM, Helmut Tschemernjak wrote:
Hello Adam, all,
Cross platform locking is defiantly very important. The goal should
be to use the identical sqlite DB via UNIX, AFP, SMB, NFS and
others. My opinion is that it is not needed to have any additional
open parameters,
On Mar 8, 2006, at 12:27 AM, Manfred Bergmann wrote:
Hi.
You guys already distribute a SQLite version with locking support
for remote databases with Mac OSX.
What would be the difference to that mechanism?
This would allow for manual configuration of the locking type to be
used.
What you envisage was realized by the Pick database system from the
1970s. It had a "dictionary" to each table, and virtual fields could be
defined in the dictionary as expressions.
With SQL you might find that VIEWs and user defined functions might
adapt to your application.
JS
Eugen
Hi,
I currently have a v3.2.0 database that contains two tables that I regularly
query with a join, e.g.,
selectt1.a, t1.b, t2.c, t2.d
from t1
join t2
ont1.x = t2.x and t1.a >= 100 and t1.a < 200
group by t1.a, t1.b, t2.c, t2.d
Table t1 has an index on a
Table t2 has an
"Kexi is an integrated data management application. It can be used for
creating database schemas, inserting data, performing queries, and processing
data. "
Download demo of International "2006" Edition for MS Windows:
http://www.kexi.pl/wiki/index.php/Kexi_for_MS_Windows
Features and
On Thu, 9 Mar 2006, Christian Smith wrote:
>On Wed, 8 Mar 2006 [EMAIL PROTECTED] wrote:
>
>>
>>One question though: are the file access "sorted", so that seeks are
>>minimised when performing a transaction (making the assumption that the
>>file is not fragmented on disk)?
>
>
>The OS will sort IO
try using single ticks instead of quotes:Select Surname||',
'||First_Name||' ; '||Address as "Details" ...
This is the more correct(in SQL) way of specifying a string.
John
On 3/9/06, Clay Dowling <[EMAIL PROTECTED]> wrote:
>
> Roger said:
>
> > The reason i need a semicolon is because, i am
On Wed, Mar 08, 2006 at 03:09:15PM -0800, [EMAIL PROTECTED] wrote:
[...]
> One question though: are the file access "sorted", so that seeks are
> minimised when performing a transaction (making the assumption that the file
> is not fragmented on disk)?
I assume you tried your tests with "PRAGMA
Roger said:
> The reason i need a semicolon is because, i am creating some queries for
> my reports and there are instances i concatenate some fields which i
> then seperate via a semicolon, but they occur in one line e.g. for the
> above query
>
> Smith, John ; South Africa
Roger,
Your best
On Wed, 8 Mar 2006 [EMAIL PROTECTED] wrote:
>- Original Message
>From: [EMAIL PROTECTED]
>To: sqlite-users@sqlite.org
>
>> SQLite inserts in records in primary key order. (That is not
>> strictly true - but it is close enough to being true for the
>> purposes of what follows.) So when
Eugen Stoianovici wrote:
Is there a way o creating tables that have fields that are expressions
rather than values?
Those expresions would return a value based on values stored in other
fields of some other table (or the same table if that's necessary).
Would CREATE VIEW be helpful?
Igor
On Tue, 7 Mar 2006 [EMAIL PROTECTED] wrote:
>Hi all
>
>it seems that I am running in a problem with the way sqlite accesses the
>disk when inserting rows of data in databases that are large in number of
>records but not necessary big on disk (I am talking millions of records
>in files that are in
My ideea was to create some database structure where some tables would
store the real data (the actual values of the columns) and some other
tables that have the values i need to work with calculated based upon
the data stored in the first tables. Triggers seem the way to go, but i
was
Eugen Stoianovici wrote:
Is there a way o creating tables that have fields that are expressions
rather than values?
Those expresions would return a value based on values stored in other
fields of some other table (or the same table if that's necessary).
If the expressions do not vary per
Eugen Stoianovici a écrit :
Is there a way o creating tables that have fields that are expressions
rather than values?
Those expresions would return a value based on values stored in other
fields of some other table (or the same table if that's necessary).
Hi,
i think the best way to do that
Is there a way o creating tables that have fields that are expressions
rather than values?
Those expresions would return a value based on values stored in other
fields of some other table (or the same table if that's necessary).
On Thu, Mar 09, 2006 at 10:00:50AM +0200, Roger wrote:
> Hello guys.
>
> I have a small problem.I am trying to write some sql queries. Whenever i
> put a semicolon, sqlite returns an error immediately.for instance if i
> select as follows
>
> Select Name||" "||Surname||" ; "||Address as "Details
Hello again,
It's probably bad to answer ones own question!
I fixed my problem with a call to sqlite_expired before calling
sqlite_reset.
If sqlite_expired returns non zero then I do sqlite_finalize and
sqlite_prepare before sqlite_reset.
I think that sqlite_step might be returning a stale
Jarl Friis <[EMAIL PROTECTED]> writes:
> Sorry for being OT, but I could not find a better place.
>
> I think when SQLite ODBC driver postings are very low-frequent, it
> wouldn't hurt to (ab)use this list for that.
Just to let you know. Christian has replied to me privately because it
is
Hello guys.
>
> I have a small problem.I am trying to write some sql queries. Whenever i
> put a semicolon, sqlite returns an error immediately.for instance if i
> select as follows
>
> Select Surname||", "||First_Name||" ; "||Address as "Details
> >From Person
> where PersID=1098
The reason
Hello!
Why do u need semicolon there?
U can just give-- Select Name,Surname,' ',Address as Details from
personm where persID = 1098
Think this works.
-Original Message-
From: Roger [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 09, 2006 12:01 AM
To: sqlite-users@sqlite.org
Subject:
Hello guys.
I have a small problem.I am trying to write some sql queries. Whenever i
put a semicolon, sqlite returns an error immediately.for instance if i
select as follows
Select Name||" "||Surname||" ; "||Address as "Details
>From Person
where PersID=1098
39 matches
Mail list logo