Re: [sqlite] [sqlite TOO slow

[Jay A. Kreibich]

On Thu, Nov 24, 2011 at 05:22:38PM +, Simon Slavin scratched on the wall:
> On 24 Nov 2011, at 5:10pm, Jay A. Kreibich wrote:
> > On Thu, Nov 24, 2011 at 08:08:12AM +, Simon Slavin scratched on the 
> > wall:
> > 
> >> It is faster to search integers than it is to search real numbers. 
> > 
> >  Why?  Both types are a string of 64 bits.  Both types use the same
> >  integer-based logic for the =, <, and > operations.
> > 
> >  The only real difference is that integers are stored in a compressed
> >  format.  While that means they have a higher CPU cost to decode, the
> >  smaller size likely makes up for the encoding overhead with improved
> >  I/O times.  I'm not sure there is a strong practical win, however--
> >  especially if the data is in cache, or doesn't span several pages.
> 
> Hmm.  I really should check this out with SQLite.  It is generally
> true in computing. 

  FP mathematics instructions (such as + - * / )  typically take many
  more CPU cycles to complete than their integer equivalents.

  However, when it comes to comparisons (such as = < <= >= > ) you
  might use in an index scan, the IEEE 754 standard is specifically 
  designed so that these operations can be done with the exact same
  logic as unsigned integer numbers.  In most CPUs the floating-point
  pipeline has its own logic units to perform these operations (due
  to the separation of integer and FP registers), but the speed of these
  logic units should be the same as the primary integer units.

> It may not be true given how SQLite uses different
> numbers of bytes to store different sized integers.

  Yes-- the variable length integers means there are extra decoding
  steps for the integers.  However, the variable length also means one
  can, in theory, stuff more "typical" integer values into the same
  database page, saving on I/O.  If the I/O is from disk, this reduced
  I/O cost, as small as it is likely to be, is still likely to save
  significantly more than the integer decoding costs.  I/O is just that
  expensive next to processor cycles.  That's my guess, anyways.  I'm
  not sure things would be so clear if you were working out of the
  OS file system cache.

  Overall, you might be right... But if there is a difference, it
  is likely related to the variable length nature of integers, not
  something inherent in FP math being more expensive than integer math.

-j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [sqlite TOO slow

[Simon Slavin]

On 24 Nov 2011, at 5:10pm, Jay A. Kreibich wrote:

> On Thu, Nov 24, 2011 at 08:08:12AM +, Simon Slavin scratched on the wall:
> 
>> It is faster to search integers than it is to search real numbers. 
> 
>  Why?  Both types are a string of 64 bits.  Both types use the same
>  integer-based logic for the =, <, and > operations.
> 
>  The only real difference is that integers are stored in a compressed
>  format.  While that means they have a higher CPU cost to decode, the
>  smaller size likely makes up for the encoding overhead with improved
>  I/O times.  I'm not sure there is a strong practical win, however--
>  especially if the data is in cache, or doesn't span several pages.

Hmm.  I really should check this out with SQLite.  It is generally true in 
computing.  It may not be true given how SQLite uses different numbers of bytes 
to store different sized integers.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [sqlite TOO slow

[Jay A. Kreibich]

On Thu, Nov 24, 2011 at 08:08:12AM +, Simon Slavin scratched on the wall:

> It is faster to search integers than it is to search real numbers. 

  Why?  Both types are a string of 64 bits.  Both types use the same
  integer-based logic for the =, <, and > operations.

  The only real difference is that integers are stored in a compressed
  format.  While that means they have a higher CPU cost to decode, the
  smaller size likely makes up for the encoding overhead with improved
  I/O times.  I'm not sure there is a strong practical win, however--
  especially if the data is in cache, or doesn't span several pages.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [sqlite TOO slow

[Gaurav Vyas]

Hi,

I am gonna try first creating unique index (hid, pid). It will take a bit
of time and will keep you posted with the result.

Thanks a lot

Gaurav

On Thu, Nov 24, 2011 at 2:08 AM, Simon Slavin  wrote:

>
> On 24 Nov 2011, at 7:54am, Gaurav Vyas wrote:
>
> > when I do typeof(hid), it gives "real".
>
> You might look into why it is not storing integers instead.  It is faster
> to search integers than it is to search real numbers.  The hid column in
> your table should probably be defined with a type of INTEGER.
>
> 
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Gaurav Vyas
Graduate Research Assistant, Transportation Engineering
University of Texas at Austin
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [sqlite TOO slow

[Simon Slavin]

On 24 Nov 2011, at 7:54am, Gaurav Vyas wrote:

> when I do typeof(hid), it gives "real".

You might look into why it is not storing integers instead.  It is faster to 
search integers than it is to search real numbers.  The hid column in your 
table should probably be defined with a type of INTEGER.



Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [sqlite TOO slow

[Simon Slavin]

On 24 Nov 2011, at 7:56am, Gaurav Vyas wrote:

> I used the following syntax to create index
> "CREATE UNIQUE INDEX persons_1x
> ON persons (pid,hid);"

This index cannot be used for a search on your 'hid' column.  Imagine you had 
it written out, and wanted to find all the records with the same 'hid'.  You'd 
still have to search the entire list !

So create an index on (hid,pid) instead.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [sqlite TOO slow

[Petite Abeille]

On Nov 24, 2011, at 8:56 AM, Gaurav Vyas wrote:

> I used the following syntax to create index
> "CREATE UNIQUE INDEX persons_1x
> ON persons (pid,hid);"

Check you query plan (i.e. explain query plan [1]).

I doubt such index has any use as SQLite doesn't support  "index skip scans" 
access plan or such [2].

Check the friendly manual for what's supported:

The SQLite Query Planner
1.1 Index term usage examples
http://www.sqlite.org/optoverview.html

[1] http://www.sqlite.org/lang_explain.html
[2] http://docs.oracle.com/cd/B19306_01/server.102/b14211/optimops.htm#i51571


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [sqlite TOO slow

[Gaurav Vyas]

I used the following syntax to create index
"CREATE UNIQUE INDEX persons_1x
ON persons (pid,hid);"

Gaurav

On Thu, Nov 24, 2011 at 1:54 AM, Petite Abeille wrote:

>
> On Nov 24, 2011, at 8:48 AM, Simon Slavin wrote:
>
> > Is the table indexed on that column ?
>
> And if it is... what's its selectivity?
>
> What 's the query plan?
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Gaurav Vyas
Graduate Research Assistant, Transportation Engineering
University of Texas at Austin
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [sqlite TOO slow

[Petite Abeille]

On Nov 24, 2011, at 8:48 AM, Simon Slavin wrote:

> Is the table indexed on that column ?

And if it is... what's its selectivity? 

What 's the query plan?


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [sqlite TOO slow

[Gaurav Vyas]

I am using multi threads but using single thread to test the speed. And
when I do typeof(hid), it gives "real".

Table is indexed on that column. I dont have a primary key, will that make
any difference?

Gaurav

On Thu, Nov 24, 2011 at 1:48 AM, Simon Slavin  wrote:

>
> On 24 Nov 2011, at 7:45am, Gaurav Vyas wrote:
>
> > I am trying to get a chunk of rows from a table which has 16 million
> rows.
> > The table is indexed. I am passing the query as "SELECT * FROM persons
> > WHERE hid = 5;" and it takes a few minutes to get me the results. Can
> > anyone suggest how to make it faster?
>
> Is the table indexed on that column ?
>
> Are you doing anything involving multi-user, multi-thread, or
> multi-process ?
>
> What type of values are stored in that column ?  In other words, if you do
> something like
>
> SELECT hid,typeof(hid) FROM persons WHERE hid = 5
>
> what does it give for the 'typeof()' ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Gaurav Vyas
Graduate Research Assistant, Transportation Engineering
University of Texas at Austin
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [sqlite TOO slow

[Simon Slavin]

On 24 Nov 2011, at 7:45am, Gaurav Vyas wrote:

> I am trying to get a chunk of rows from a table which has 16 million rows.
> The table is indexed. I am passing the query as "SELECT * FROM persons
> WHERE hid = 5;" and it takes a few minutes to get me the results. Can
> anyone suggest how to make it faster?

Is the table indexed on that column ?

Are you doing anything involving multi-user, multi-thread, or multi-process ?

What type of values are stored in that column ?  In other words, if you do 
something like

SELECT hid,typeof(hid) FROM persons WHERE hid = 5

what does it give for the 'typeof()' ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [sqlite TOO slow

[Gaurav Vyas]

I am trying to get a chunk of rows from a table which has 16 million rows.
The table is indexed. I am passing the query as "SELECT * FROM persons
WHERE hid = 5;" and it takes a few minutes to get me the results. Can
anyone suggest how to make it faster?

Gaurav
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

RE: [sqlite] sqlite too slow for me?

[Allan, Mark]

I believe that when using a transaction, i.e. Begin, Commit. SQLite will only 
perform the actual writing to file when Commit is made, therefore less disk 
I/O, or all I/O done at the end rather than after each insert and therefore 
better performance.

See documentation at:-
http://www.sqlite.org/speed.html

P.S. I'm sure someone else will give a better explanation.


> -Original Message-
> From: Cesar David Rodas Maldonado [mailto:[EMAIL PROTECTED]
> Sent: 29 June 2006 16:12
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] sqlite too slow for me?
> 
> 
> I have a question for every body...
> 
> 
> SQLite was very slow for my inserts (like 5 inserts), with out
> sincronization
> 
> but when i put BEGIN; before start with my inserts is was 
> faster... like
> 1000 times more... :D why is that?
> 
> 
> On 6/28/06, Cesar David Rodas Maldonado <[EMAIL PROTECTED]> wrote:
> >
> >  I dont know a lot about MySQL... but mysql is not so faster as you
> > think...
> > I have in my computer the mysql 5 is good, but sqlite is so 
> faster too!!!
> >
> > but MySQL has a query cache  so i think the cache make 
> it seems more
> > faster than sqlite...
> >
> >
> >
> >
> > On 6/27/06, Péter Szabó <[EMAIL PROTECTED]> wrote:
> > >
> > > First, thank you all for the answers.
> > >
> > > >UNIQUE(col1, col4, col5, col2),
> > >
> > > Adding this would surely make the query run faster -- 
> provided that
> > > SQLite chooses the right index. But I also use the 
> UNIQUE(col1, col4,
> > > col5) constraint to ensure the uniqueness of these three 
> columns. So
> > > instead I should have both
> > >
> > > UNIQUE(col1, col4, col5),
> > > UNIQUE(col1, col4, col5, col2),
> > >
> > > but this would waste my disk space.
> > >
> > > I am wondering how can MySQL 4.1 be so fast compared to SQLite 3?
> > > MySQL answers my query in 0.02 seconds, and SQLite answers in more
> > > than 28 seconds.
> > >
> > > I guess that MySQL doesn't do any magic either ( i.e. it 
> operates in
> > > O(Klog N) time, which appears to be just 0.02 seconds), but MySQL
> > > recognises that it should use the UNIQUE(col1, col4, col5) index,
> > > while SQLite poorly chooses some other index, maybe the PRIMARY
> > > KEY(col1, col2, col3, col4, col5), which is just wrong. 
> Can someone
> > > confirm that the SQLite is using the wrong index? Is it 
> possible to
> > > deterministically rewrite the query to force SQLite use the right
> > > index?
> > >
> >
> >
> 


DISCLAIMER:
This information and any attachments contained in this email message is 
intended only for the use of the individual or entity to which it is addressed 
and may contain information that is privileged, confidential, and exempt from 
disclosure under applicable law.  If the reader of this message is not the 
intended recipient, or the employee or agent responsible for delivering the 
message to the intended recipient, you are hereby notified that any 
dissemination, distribution, forwarding, or copying of this communication is 
strictly prohibited.  If you have received this communication in error, please 
notify the sender immediately by return email, and delete the original message 
immediately.

Re: [sqlite] sqlite too slow for me?

[Cesar David Rodas Maldonado]

I have a question for every body...


SQLite was very slow for my inserts (like 5 inserts), with out
sincronization

but when i put BEGIN; before start with my inserts is was faster... like
1000 times more... :D why is that?


On 6/28/06, Cesar David Rodas Maldonado <[EMAIL PROTECTED]> wrote:


 I dont know a lot about MySQL... but mysql is not so faster as you
think...
I have in my computer the mysql 5 is good, but sqlite is so faster too!!!

but MySQL has a query cache  so i think the cache make it seems more
faster than sqlite...




On 6/27/06, Péter Szabó <[EMAIL PROTECTED]> wrote:
>
> First, thank you all for the answers.
>
> >UNIQUE(col1, col4, col5, col2),
>
> Adding this would surely make the query run faster -- provided that
> SQLite chooses the right index. But I also use the UNIQUE(col1, col4,
> col5) constraint to ensure the uniqueness of these three columns. So
> instead I should have both
>
> UNIQUE(col1, col4, col5),
> UNIQUE(col1, col4, col5, col2),
>
> but this would waste my disk space.
>
> I am wondering how can MySQL 4.1 be so fast compared to SQLite 3?
> MySQL answers my query in 0.02 seconds, and SQLite answers in more
> than 28 seconds.
>
> I guess that MySQL doesn't do any magic either ( i.e. it operates in
> O(Klog N) time, which appears to be just 0.02 seconds), but MySQL
> recognises that it should use the UNIQUE(col1, col4, col5) index,
> while SQLite poorly chooses some other index, maybe the PRIMARY
> KEY(col1, col2, col3, col4, col5), which is just wrong. Can someone
> confirm that the SQLite is using the wrong index? Is it possible to
> deterministically rewrite the query to force SQLite use the right
> index?
>

Re: [sqlite] sqlite too slow for me?

[Cesar David Rodas Maldonado]

I dont know a lot about MySQL... but mysql is not so faster as you think...
I have in my computer the mysql 5 is good, but sqlite is so faster too!!!

but MySQL has a query cache  so i think the cache make it seems more
faster than sqlite...




On 6/27/06, Péter Szabó <[EMAIL PROTECTED]> wrote:


First, thank you all for the answers.

>UNIQUE(col1, col4, col5, col2),

Adding this would surely make the query run faster -- provided that
SQLite chooses the right index. But I also use the UNIQUE(col1, col4,
col5) constraint to ensure the uniqueness of these three columns. So
instead I should have both

UNIQUE(col1, col4, col5),
UNIQUE(col1, col4, col5, col2),

but this would waste my disk space.

I am wondering how can MySQL 4.1 be so fast compared to SQLite 3?
MySQL answers my query in 0.02 seconds, and SQLite answers in more
than 28 seconds.

I guess that MySQL doesn't do any magic either (i.e. it operates in
O(Klog N) time, which appears to be just 0.02 seconds), but MySQL
recognises that it should use the UNIQUE(col1, col4, col5) index,
while SQLite poorly chooses some other index, maybe the PRIMARY
KEY(col1, col2, col3, col4, col5), which is just wrong. Can someone
confirm that the SQLite is using the wrong index? Is it possible to
deterministically rewrite the query to force SQLite use the right
index?

Re: [sqlite] sqlite too slow for me?

[Péter Szabó]

First, thank you all for the answers.


   UNIQUE(col1, col4, col5, col2),


Adding this would surely make the query run faster -- provided that
SQLite chooses the right index. But I also use the UNIQUE(col1, col4,
col5) constraint to ensure the uniqueness of these three columns. So
instead I should have both

 UNIQUE(col1, col4, col5),
 UNIQUE(col1, col4, col5, col2),

but this would waste my disk space.

I am wondering how can MySQL 4.1 be so fast compared to SQLite 3?
MySQL answers my query in 0.02 seconds, and SQLite answers in more
than 28 seconds.

I guess that MySQL doesn't do any magic either (i.e. it operates in
O(Klog N) time, which appears to be just 0.02 seconds), but MySQL
recognises that it should use the UNIQUE(col1, col4, col5) index,
while SQLite poorly chooses some other index, maybe the PRIMARY
KEY(col1, col2, col3, col4, col5), which is just wrong. Can someone
confirm that the SQLite is using the wrong index? Is it possible to
deterministically rewrite the query to force SQLite use the right
index?

Re: [sqlite] sqlite too slow for me?

[Joe Wilson]

--- [EMAIL PROTECTED] wrote:
> "=?ISO-8859-1?Q?P=E9ter_Szab=F3?=" <[EMAIL PROTECTED]> wrote:
> > 
> > My schema is the following:
> > 
> >   CREATE TABLE t (
> > col1 text NOT NULL,
> > col2 integer NOT NULL,
> > col3 integer NOT NULL,
> > col4 text NOT NULL,
> > col5 text NOT NULL,
> > PRIMARY KEY(col1, col2, col3, col4, col5),
> > UNIQUE(col1, col4, col5),
> > UNIQUE(col4, col5, col1),
> > UNIQUE(col5, col4, col1),
> >   );
> > 
> > 
> > This is also instant, possibly because SQLite is using UNIQUE(col1,col4,col=
> > 5):
> > 
> >   SELECT col5 FROM t WHERE col1='foobar' AND
> > col4='foobarfoobarfoobarfoob';
> > 
> > However, this is very slow in SQLite 3.3.5:
> > 
> >   SELECT col2 FROM t WHERE col1='foobar' AND
> > col4='foobarfoobarfoobarfoob';
> > 
> 
> The second one will be much faster if you add the "col2" column
> to the end of your first UNIQUE constraint:
> 
>UNIQUE(col1, col4, col5, col2),

The initial email says that col1 is always equal to 'foobar',
and there are only 96 rows where col4='foobarfoobarfoobarfoob'.
Wouldn't UNIQUE(col4, ...who cares...) be more efficient for his
specific data set even if the table had to be hit to get the data
corresponding to the key?

> 
> Each UNIQUE constraint creates an index.  In the case
> where you have only the first 3 columns in the index,
> the first 2 columns are used to lookup the rowid of
> the table, then a binary search is performed for each
> row of the table.  If there are N rows in the entire
> database and K rows of result, then this query requires
> time O(KlogN).  But if col2 is already part of the
> index entry, there is no need to perform that binary
> search and the value of col2 is extracted directly from
> the index itself.  This results in O(K + logN) run time,
> which can be much faster, especially if the database
> file is not in your disk cache.
> --
> D. Richard Hipp   <[EMAIL PROTECTED]>
> 
> 


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Re: [sqlite] sqlite too slow for me?

[drh]

"=?ISO-8859-1?Q?P=E9ter_Szab=F3?=" <[EMAIL PROTECTED]> wrote:
> 
> My schema is the following:
> 
>   CREATE TABLE t (
> col1 text NOT NULL,
> col2 integer NOT NULL,
> col3 integer NOT NULL,
> col4 text NOT NULL,
> col5 text NOT NULL,
> PRIMARY KEY(col1, col2, col3, col4, col5),
> UNIQUE(col1, col4, col5),
> UNIQUE(col4, col5, col1),
> UNIQUE(col5, col4, col1),
>   );
> 
> 
> This is also instant, possibly because SQLite is using UNIQUE(col1,col4,col=
> 5):
> 
>   SELECT col5 FROM t WHERE col1='foobar' AND
> col4='foobarfoobarfoobarfoob';
> 
> However, this is very slow in SQLite 3.3.5:
> 
>   SELECT col2 FROM t WHERE col1='foobar' AND
> col4='foobarfoobarfoobarfoob';
> 

The second one will be much faster if you add the "col2" column
to the end of your first UNIQUE constraint:

   UNIQUE(col1, col4, col5, col2),

Each UNIQUE constraint creates an index.  In the case
where you have only the first 3 columns in the index,
the first 2 columns are used to lookup the rowid of
the table, then a binary search is performed for each
row of the table.  If there are N rows in the entire
database and K rows of result, then this query requires
time O(KlogN).  But if col2 is already part of the
index entry, there is no need to perform that binary
search and the value of col2 is extracted directly from
the index itself.  This results in O(K + logN) run time,
which can be much faster, especially if the database
file is not in your disk cache.
--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] sqlite too slow for me?

[Kurt Welgehausen]

"P?ter Szab?" <[EMAIL PROTECTED]> wrote:

> Dear SQLite Developers,
>
> I am seeking help for optimizing my SQLite SQL query, which seems to
> be running unreasonably slow. The query is:
>
>   SELECT col2 FROM t WHERE col1='foobar' AND
> col4='foobarfoobarfoobarfoob';
>
> My schema is the following:
>
>   CREATE TABLE t (
> col1 text NOT NULL,
> col2 integer NOT NULL,
> col3 integer NOT NULL,
> col4 text NOT NULL,
> col5 text NOT NULL,
> PRIMARY KEY(col1, col2, col3, col4, col5),
> UNIQUE(col1, col4, col5),
> UNIQUE(col4, col5, col1),
> UNIQUE(col5, col4, col1),
>   );

Just a guess: try

   SELECT +col2 FROM t ...


Regards