Keith, this definitely explains the observed time as it is relative to
count(a)*count (ab)**2, thus non-linear.
And a correlated sub-query is generally recalculated for each row.
But I do not agree with everything.
In my example it is correlated to the outermost query, and not to the
sub-que
imizer on the planet that can help you.
--
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of E.Pasma
> Sent: Friday, 7 July, 2017 07:47
> To: SQLite mail
ng list
Subject: [sqlite] Slow query, with correlated sub-sub-query
Thanks David for the alernative solution, that is 500 times faster or
any times faster depending on the number of rows. I hope this does not
derive attention from my point that the original construction is not
very well delt with
Thanks David for the alernative solution, that is 500 times faster or
any times faster depending on the number of rows. I hope this does not
derive attention from my point that the original construction is not
very well delt with.
It was only a theoretical query derived from a more complex on
qlite-users-boun...@mailinglists.sqlite.org] On
Behalf Of E.Pasma
Sent: Friday, July 07, 2017 9:47 AM
To: SQLite mailing list
Subject: [sqlite] Slow query, with correlated sub-sub-query
Hello, below is a theoretical query that becomes slow when the number
of rows increases. What it does is:
- sca
Hello, below is a theoretical query that becomes slow when the number
of rows increases. What it does is:
- scan input cases in table a
- for each input case:
-- determine the smallest value of attribute size of elements in table
ab
-- count the number of elements having this smallest size
Wi
On Aug 8, 2013, at 3:40 PM, Christopher W. Steenwyk wrote:
> This database is generated once, and then queried and interrogated multiple
> times. So I am most concerned with read speed and not with writing or
> updating.
Ohohoho… in that case… I have some snake oil to sell you, Dear Sir!
If yo
I just uploaded the output from sqlite3_analyze to dropbox.
On Thu, Aug 8, 2013 at 9:40 AM, Christopher W. Steenwyk wrote:
> Here is the data from stat1:
>
> "tbl", "idx", "stat"
> "metrics", "metrics_idx", "68682102 2 2 2"
> "metrics", "metrics_frame_idx", "68682102 2"
> "metrics", "sqlite_aut
Here is the data from stat1:
"tbl", "idx", "stat"
"metrics", "metrics_idx", "68682102 2 2 2"
"metrics", "metrics_frame_idx", "68682102 2"
"metrics", "sqlite_autoindex_mobileye_ldw_metrics_1", "68682102 230 1"
"object_characteristics", "object_characteristics_idx", "1148344 164050
31899 1"
"object_
On Wed, 7 Aug 2013 23:13:41 +0200
Petite Abeille wrote:
> On Aug 7, 2013, at 9:25 PM, Christopher W. Steenwyk
> wrote:
>
> > Ah, sorry about the attachments, you can find the files here:
> > https://www.dropbox.com/l/fpFfcjwwcyhXZgduswCggb
>
> Ah, also, your schema has a very, hmmm, Entity?att
On Wed, Aug 7, 2013 at 2:54 PM, Christopher W. Steenwyk wrote:
> Hi,
>
> I have been working on a large database and its queries now for several
> weeks and just can't figure out why my query is so slow. I've attached the
> schema, my query, and the results of EXPLAIN QUERY from sqliteman.
>
> A
On Aug 7, 2013, at 9:25 PM, Christopher W. Steenwyk wrote:
> Ah, sorry about the attachments, you can find the files here:
> https://www.dropbox.com/l/fpFfcjwwcyhXZgduswCggb
Ah, also, your schema has a very, hmmm, Entity–attribute–value (EAV) smell to
it (object, attribute, types, values, char
On Aug 7, 2013, at 9:25 PM, Christopher W. Steenwyk wrote:
> Ah, sorry about the attachments, you can find the files here:
> https://www.dropbox.com/l/fpFfcjwwcyhXZgduswCggb
>
> And yes, as the final part of the DB creation I do run ANALYZE. And I do
> think the indexes are correct for the quer
Ah, sorry about the attachments, you can find the files here:
https://www.dropbox.com/l/fpFfcjwwcyhXZgduswCggb
And yes, as the final part of the DB creation I do run ANALYZE. And I do
think the indexes are correct for the query.
On Wed, Aug 7, 2013 at 3:07 PM, Petite Abeille wrote:
>
> On Aug 7
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 07/08/13 11:54, Christopher W. Steenwyk wrote:
> I have been working on a large database and its queries now for
> several weeks and just can't figure out why my query is so slow. I've
> attached the schema, my query, and the results of EXPLAIN QUER
On 7 Aug 2013, at 7:54pm, Christopher W. Steenwyk wrote:
> I've attached the
> schema, my query, and the results of EXPLAIN QUERY from sqliteman.
Sorry, attachments don't work on this list (we don't want everyone sending us
their homework). Could you instead just post your SELECT command and
On Aug 7, 2013, at 8:54 PM, "Christopher W. Steenwyk"
wrote:
> The attached query takes over 6 days to run.
“Patience – A minor form of despair, disguised as a virtue.”
Also… attachments are stripped out by the mailing list. You may want to try to
inline them instead.
___
Hi,
I have been working on a large database and its queries now for several
weeks and just can't figure out why my query is so slow. I've attached the
schema, my query, and the results of EXPLAIN QUERY from sqliteman.
A few notes about this database:
Its approximately 10GB in size, but I have it
If your table doesn't have a primary key, this look like your table aren't
normalized, maybe you can try broke this table in 2 tables like:
Your definition:
CREATE TABLE table1(id INTEGER, name VARCHAR, id2 INTEGER, name2 VARCHAR, year
INTEGER);
indexes : index1( name ), index2( id2 ), index
> The year can also be different but Im interested only in the latest year. I
> use the GROUP because I want unique id and if I add year in GROUP BY I can
> get it repeated.
If you're interested in the latest year then your query is totally
wrong because it returns random year. If you saw what you
On Wed, Jun 30, 2010 at 2:40 AM, Pavel Ivanov wrote:
> > The query return the apropiate values as always the id -> id2 relation is
> > the same and id -> name and id2 -> name2.
>
> So your id maps uniquely to id2, name and name2. But what about year?
> What value of year do you want to be used in
> The query return the apropiate values as always the id -> id2 relation is
> the same and id -> name and id2 -> name2.
So your id maps uniquely to id2, name and name2. But what about year?
What value of year do you want to be used in sorting?
Anyway try to change query as "GROUP BY id, name2, ye
On 30 Jun 2010, at 12:32am, J. Rios wrote:
> On Wed, Jun 30, 2010 at 12:13 AM, Simon Slavin wrote:
>>
>> A good index would be
>>
>> name2, year, id
>>
>> That's all three columns in the same index, not three separate indexes one
>> on each column.
>
> I did the test and EXPLAIN QUERY PLAN
On Wed, Jun 30, 2010 at 12:13 AM, Simon Slavin wrote:
>
> None of your indexes are much use for this SELECT command. Imagine trying
> to do the SELECT command yourself and you'll quickly see that whichever of
> the supplied indexes you use you're left trying to sort a great deal of
> records by h
On 30 Jun 2010, at 12:05am, J. Rios wrote:
> Sorry for posting on top of the message,
No problem. Adding new text at the bottom of a post, and trimming what you
quote just enough that people understand your new text, make your own message
clear and encourage people to reply to it. Look at wh
On Wed, Jun 30, 2010 at 12:02 AM, Simon Slavin wrote:
>
> On 29 Jun 2010, at 11:57pm, J. Rios wrote:
>
> >>> I have created the next indexes : index1( name ), index2( id2 ),
> index3(
> >>> name2 );
>
> Those are very unlikely to be of any use at all. They're probably a waste
> of space.
>
> > I
The query return the apropiate values as always the id -> id2 relation is
the same and id -> name and id2 -> name2. I keep them in the same table to
speed up other queries that are now very quick as uses indexes for the
ordering but in this SELECT the GROUP BY makes the difference and the SORT
is g
On 29 Jun 2010, at 11:57pm, J. Rios wrote:
>>> I have created the next indexes : index1( name ), index2( id2 ), index3(
>>> name2 );
Those are very unlikely to be of any use at all. They're probably a waste of
space.
> Its not the primary Key. There are more fields but the index on id is
> cr
Its not the primary Key. There are more fields but the index on id is
created also. Sorry I missed it in the post.
If I do a EXPLAIN QUERY PLAN sqlite tells me its going to use the id INDEX.
But the sorting is slow.
Thanks in advance
On Tue, Jun 29, 2010 at 5:32 PM, Jim Morris wrote:
> You als
You also haven't specified a primary key, i.e. on "id"
On 6/28/2010 11:24 PM, J. Rios wrote:
> I have the next table
>
> table1( id INTEGER, name VARCHAR, id2 INTEGER, name2 VARCHAR, year INTEGER
> );
>
> I have created the next indexes : index1( name ), index2( id2 ), index3(
> name2 );
>
>
_
> SELECT id, name, id2, name2 GROUP BY id ORDER BY name2, year DESC LIMIT 0,
> 15
>
> How can I make it faster?
First of all your query should return nonsense in any field except id.
I bet it will also return different results (for the same ids)
depending on what LIMIT clause you add or don't add
I have the next table
table1( id INTEGER, name VARCHAR, id2 INTEGER, name2 VARCHAR, year INTEGER
);
I have created the next indexes : index1( name ), index2( id2 ), index3(
name2 );
The database have about 200,000 records.
The next query takes about 2 seconds and I think its too much.
SELECT id
On Tue, 23 Sep 2008 14:37:11 -0400,
Enrique Ramirez wrote:
>On Tue, Sep 23, 2008 at 1:16 PM, Kees Nuyt <[EMAIL PROTECTED]> wrote:
>> Steps to take (you need all of them, except 1):
>>
>> 1) Use v6.2.3
>>
>
>Probably meant to say 1) Use v3.6.2?
Oops, yes. Or even better: v3.6.3
--
( Kees Nuy
On Tue, Sep 23, 2008 at 1:16 PM, Kees Nuyt <[EMAIL PROTECTED]> wrote:
> Steps to take (you need all of them, except 1):
>
> 1) Use v6.2.3
>
Probably meant to say 1) Use v3.6.2?
--
// --
Enrique Ramirez Irizarry
Lead Developer
Indie Code Labs
http://www.indieco
On Mon, 22 Sep 2008 13:48:42 -0700, Jason wrote:
>Hello everyone,
>
>Hoping that I could get some help with a performance problem.
>Using version 3.5.2
>
>Here are the tables:
>CREATE TABLE Objects (ObjectId INTEGER PRIMARY KEY, Name TEXT)
>CREATE TABLE Keywords4Objects (ObjectId INTEGER, Keywor
Hello everyone,
Hoping that I could get some help with a performance problem. Using version
3.5.2
Here are the tables:
CREATE TABLE Objects (ObjectId INTEGER PRIMARY KEY, Name TEXT)
CREATE TABLE Keywords4Objects (ObjectId INTEGER, KeywordId INTEGER)
CREATE TABLE Keywords (KeywordId INTEGER PRIMA
that particular
point on that particular machine, all else behaves normal.
RBS
-Original Message-
From: Jay Sprenkle [mailto:[EMAIL PROTECTED]
Sent: 19 January 2008 14:12
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Slow query on one machine
On Jan 18, 2008 3:32 PM, RB Smissaert <[EMAIL P
On Jan 18, 2008 3:32 PM, RB Smissaert <[EMAIL PROTECTED]> wrote:
> The application
> that runs this is exactly the same on both machines. The slow machine is
> actually slightly slower specification wise, but that can't explain the huge
> differences in timings.
>
Have you run spinrite ( a disk di
Trying to figure out why (with one particular customer) some queries have
very different timings on one machine compared to another machine.
It has to do with updating one particular SQLite table with more recent data
from an Interbase table.
I give the database (S for SQLite and I for Interbase),
Message-
From: RB Smissaert [mailto:[EMAIL PROTECTED]
Sent: 26 November 2006 10:04
To: sqlite-users@sqlite.org
Subject: [sqlite] slow query
Although SQLite seems very fast, I now have come across a query that runs
extremely slow and I would be interested why this is:
There are 3 tables involved
Although SQLite seems very fast, I now have come across a query that runs
extremely slow and I would be interested why this is:
There are 3 tables involved, all fairly small, some 25000 rows:
PATIENT, ADDRESS and ADDRESSLINK
All the relevant fields are indexed.
This is the query:
SELECT
P.PATIEN
> What is the name of your database (with extension please) ?
> There are chances that such extension is registered into Windows XP
> crap called System Restore and each time on start it detect that this
> file is changed and create a restore snapshot for it.
List given at
http://msdn.microsof
Geoff Simonds wrote:
Thanks to everyone for all the help on this problem. I am going to try
creating a new thread to touch the tables at startup.
Chris Schirlinger wrote:
We have the same issue, to get around it we fire a thread when the
program starts, intelligently "touching" every table th
Thanks to everyone for all the help on this problem. I am going to try
creating a new thread to touch the tables at startup.
Chris Schirlinger wrote:
We have the same issue, to get around it we fire a thread when the
program starts, intelligently "touching" every table that the user is
like
We have the same issue, to get around it we fire a thread when the
program starts, intelligently "touching" every table that the user is
likely to access (As Michael Sizaki already mentioned a select
count(last_column) from big_table; will do it)
Since a user is very unlikely to run a program a
Geoff Simonds wrote:
The app is running on Windows XP machines
Is it possible that indexing services are enabled and XP is trying to
index the database file?
e the DB file.
CC> -Clark
CC> - Original Message
CC> From: Geoff Simonds <[EMAIL PROTECTED]>
CC> To: sqlite-users@sqlite.org
CC> Sent: Thursday, January 19, 2006 7:52:55 AM
CC> Subject: Re: [sqlite] Slow query after reboot
CC> My table contains about 500,000 rows and
monds <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Thursday, January 19, 2006 7:52:55 AM
Subject: Re: [sqlite] Slow query after reboot
My table contains about 500,000 rows and 4 columns, not all that much
data. The overall size of the db file is 35 mb. Does 15 - 20 seconds
sound ri
Thanks for the info and suggestions Michael. I will give this a try.
Michael Sizaki wrote:
Geoff Simonds wrote:
My table contains about 500,000 rows and 4 columns, not all that much
data. The overall size of the db file is 35 mb. Does 15 - 20
seconds sound right to load from disk into mem
Geoff Simonds wrote:
My table contains about 500,000 rows and 4 columns, not all that much
data. The overall size of the db file is 35 mb. Does 15 - 20 seconds
sound right to load from disk into memory?
Yes it does. The problem is, that your query is probably
not reading sequentially from di
- Original Message -
From: "Geoff Simonds" <[EMAIL PROTECTED]>
My table contains about 500,000 rows and 4 columns, not all that much
data. The overall size of the db file is 35 mb. Does 15 - 20 seconds
sound right to load from disk into memory?
I can't tell you that until the foll
My table contains about 500,000 rows and 4 columns, not all that much
data. The overall size of the db file is 35 mb. Does 15 - 20 seconds
sound right to load from disk into memory?
Robert Simpson wrote:
- Original Message - From: "Geoff Simonds" <[EMAIL PROTECTED]>
The app is
- Original Message -
From: "Geoff Simonds" <[EMAIL PROTECTED]>
The app is running on Windows XP machines and I assume that disk files are
cached. The strange thing is that the time it takes for the initial read
into RAM after install and first use is significantly shorter than after
The app is running on Windows XP machines and I assume that disk files
are cached. The strange thing is that the time it takes for the initial
read into RAM after install and first use is significantly shorter than
after a reboot. For example, if you just installed the app and start
it, the f
On 1/19/06, Geoff Simonds <[EMAIL PROTECTED]> wrote:
> I have created a client application that is always running on a users
> desktop. The application accepts user input and then uses SQLite to
> perform a few simple queries against a single db file that contains 4
> tables. The performance is fan
I have created a client application that is always running on a users
desktop. The application accepts user input and then uses SQLite to
perform a few simple queries against a single db file that contains 4
tables. The performance is fantastic after the initial install and
normal usage. When the
56 matches
Mail list logo
