Re: [sqlite] String Vs Integer Index

[Ibrahim A]

Am 10.02.2010 23:17, schrieb Simon Slavin:
> But that's true only if you're running a SELECT which actually uses 
> that column and only that column to do the searching. Which is why I 
> asked that question earlier on in this thread.
> Simon.
>
The implementation of sqlite uses a B+Tree for the stored data and a 
BTree for primary keys, indexes etc. If you create a table with a 
"INTEGER PRIMARY KEY" as "CREATE TABLE" states you will end up wit only 
one table where the "INTEGER PRIMARY KEY" is the reference for your 
B+Tree where your Data resists.

If you search for more columns than the primary key value you'll get a 
faster query because the engine won't need to look up references from 
your defined primary key to the rowid in a second Btree table but use 
the primary key directly as a reference to the B+Tree. The performance 
gain is not restricted only for select but also for update, delete, insert.

You can measure this easily with a table where you define a primary key 
with "int primary key" (results in at least one b+tree and one btree) 
and the same table with "integer primary key" (results in one b+tree).

The op asked for a performance difference using integer keys and string 
keys and for sqlite a "integer primary key" is the recommended solution 
due to implementation and documentation.

It's clear that you'll loose this performance gain when you don't use 
the primary key to access your data but that's not what the op asked for 
as far as i understood his question.

Ibrahim.

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

Re: [sqlite] String Vs Integer Index

[Jay A. Kreibich]

On Wed, Feb 10, 2010 at 10:17:28PM +, Simon Slavin scratched on the wall:
> 
> On 10 Feb 2010, at 7:51pm, Ibrahim A wrote:
> 
> > An "INTEGER PRIMARY KEY" is at least twice as fast as another type of 
> > PRIMARY KEY,
> > the reason is based on the implementation of the engine. An integer 
> > primary key substitutes the rowid column of a table.
> 
> But that's true only if you're running a SELECT which actually uses
> that column and only that column to do the searching. 

  It is actually much, much more complex.  It depends on what you're
  searching on, what you're searching for, what other columns you want,
  how the index is built, and about seven other things.

   -j

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

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] String Vs Integer Index

[Simon Slavin]

On 10 Feb 2010, at 7:51pm, Ibrahim A wrote:

> An "INTEGER PRIMARY KEY" is at least twice as fast as another type of 
> PRIMARY KEY,
> the reason is based on the implementation of the engine. An integer 
> primary key substitutes the rowid column of a table.

But that's true only if you're running a SELECT which actually uses that column 
and only that column to do the searching.  Which is why I asked that question 
earlier on in this thread.

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

Re: [sqlite] String Vs Integer Index

[Ibrahim A]

Am 10.02.2010 18:19, schrieb Alberto Simões:
> Supose a table with a key that is a string (say, words from 1 to 10
> characters) or a table with a key of integers.
>
> How different is the efficiency on fetching one record on these tables?
>
>
If you look into the documentation for "create table" you'll find the 
right answer :

An "INTEGER PRIMARY KEY" is at least twice as fast as another type of 
PRIMARY KEY,
the reason is based on the implementation of the engine. An integer 
primary key substitutes the rowid column of a table.

While another type of primary key always results in a second reference 
table (key ==> rowid) the integer primary key doesn't need a second 
index table. This results in a performance gain because the engine 
hasn't to look up the rowid in the index table to find the correct 
rowsets assigned to a primary key but looks directly in the data table.

If you can't won't use a integer primary key as described in the 
documentation the performance difference between strings and numbers 
won't be a big deal not as long as your strings have a lenght between 1 
and 10 characters cause the greatest part of the time needed to look up 
a key ==> rowid pair is spent with io operations to read the fileblocks. 
When your strings get longer you'll find, that string keys will get 
slower because the number of key ==> rowid pairs fitting in a btree 
decreases with the length of the entries. This will only matter if you 
get more levels of pages counted from the root page. As long as you 
result in the same height of btrees you won't measure a big performance 
loss due to the use of strings as primary keys when you don't use the 
"integer primary key" feature.

The argument with Timing of assembler instructions would only matter if 
you use memory databases but even then you'll end up with "virtual 
memory paging" or "caching".

If there are no reasons to avoid "INTEGER PRIMARY KEY" you'll have a 
real performance gain by using this feature !!!

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

Re: [sqlite] String Vs Integer Index

[Virgilio Fornazin]

Sure (com certeza!), because it depends on the hardware and software of your
target platform.

2010/2/10 Alberto Simões 

> On Wed, Feb 10, 2010 at 5:42 PM, Virgilio Fornazin
>  wrote:
> > I think you should be asking 'How fast is SQLite locating a key in a
> integer
> > column index vs a string index'...
> >
> > Generally, integer keys are faster in key lookups than string keys,
> because
> > comparing a integer value is a
> > single CMP CPU instruction versus a more-complicated string comparison
> (that
> > can be virtually unlimited in size).
>
> Yes, I know it should be faster.. I just would like to have an idea of
> how fast to know how relevant is an indirection table (from string to
> integer).
>
> But probably the best is to try and compare :P
>
> >
> > On Wed, Feb 10, 2010 at 15:38, Simon Slavin 
> wrote:
> >
> >>
> >> On 10 Feb 2010, at 5:19pm, Alberto Simões wrote:
> >>
> >> > I know I can benchmark myself this question, but I am sure somebody
> >> > did that already.
> >> >
> >> > Supose a table with a key that is a string (say, words from 1 to 10
> >> > characters) or a table with a key of integers.
> >> >
> >> > How different is the efficiency on fetching one record on these
> tables?
> >>
> >> How are you fetching the record ?  Do you have a SELECT command that
> looks
> >> up the record using a WHERE clause matching a key value ?  Is there an
> index
> >> on the key column ?
> >>
> >> Simon.
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@sqlite.org
> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >>
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> Alberto Simões
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] String Vs Integer Index

[Alberto Simões]

On Wed, Feb 10, 2010 at 5:42 PM, Virgilio Fornazin
 wrote:
> I think you should be asking 'How fast is SQLite locating a key in a integer
> column index vs a string index'...
>
> Generally, integer keys are faster in key lookups than string keys, because
> comparing a integer value is a
> single CMP CPU instruction versus a more-complicated string comparison (that
> can be virtually unlimited in size).

Yes, I know it should be faster.. I just would like to have an idea of
how fast to know how relevant is an indirection table (from string to
integer).

But probably the best is to try and compare :P

>
> On Wed, Feb 10, 2010 at 15:38, Simon Slavin  wrote:
>
>>
>> On 10 Feb 2010, at 5:19pm, Alberto Simões wrote:
>>
>> > I know I can benchmark myself this question, but I am sure somebody
>> > did that already.
>> >
>> > Supose a table with a key that is a string (say, words from 1 to 10
>> > characters) or a table with a key of integers.
>> >
>> > How different is the efficiency on fetching one record on these tables?
>>
>> How are you fetching the record ?  Do you have a SELECT command that looks
>> up the record using a WHERE clause matching a key value ?  Is there an index
>> on the key column ?
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] String Vs Integer Index

[Virgilio Fornazin]

I think you should be asking 'How fast is SQLite locating a key in a integer
column index vs a string index'...

Generally, integer keys are faster in key lookups than string keys, because
comparing a integer value is a
single CMP CPU instruction versus a more-complicated string comparison (that
can be virtually unlimited in size).

On Wed, Feb 10, 2010 at 15:38, Simon Slavin  wrote:

>
> On 10 Feb 2010, at 5:19pm, Alberto Simões wrote:
>
> > I know I can benchmark myself this question, but I am sure somebody
> > did that already.
> >
> > Supose a table with a key that is a string (say, words from 1 to 10
> > characters) or a table with a key of integers.
> >
> > How different is the efficiency on fetching one record on these tables?
>
> How are you fetching the record ?  Do you have a SELECT command that looks
> up the record using a WHERE clause matching a key value ?  Is there an index
> on the key column ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] String Vs Integer Index

[Simon Slavin]

On 10 Feb 2010, at 5:19pm, Alberto Simões wrote:

> I know I can benchmark myself this question, but I am sure somebody
> did that already.
> 
> Supose a table with a key that is a string (say, words from 1 to 10
> characters) or a table with a key of integers.
> 
> How different is the efficiency on fetching one record on these tables?

How are you fetching the record ?  Do you have a SELECT command that looks up 
the record using a WHERE clause matching a key value ?  Is there an index on 
the key column ?

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

[sqlite] String Vs Integer Index

[Alberto Simões]

Howdy SQLite users,

I know I can benchmark myself this question, but I am sure somebody
did that already.

Supose a table with a key that is a string (say, words from 1 to 10
characters) or a table with a key of integers.

How different is the efficiency on fetching one record on these tables?

Thanks

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