RE: [sqlite] To increase search speed

2007-08-20 Thread Samuel R. Neff

Method 3, normalization, is the right route but I think the implementation
needs a little more work.  First don't store both Artist ID and Artist Name
in the Music table--only store the ID. This goes for AlbumID/Name and
GenreID/Name as well.  Then reorder the columns to put the integers first in
the field order for the Music table, especially the foreign keys.  Finally,
create appropriate indexes.  Keep in mind that SQLite will only use one
index per table in a query so most likely you'll want a compound index that
indexes multiple foreign key fields at once.  

For queries that used to be "select distinct AlbumName from music" now just
query "select AlbumName from Albums" and you'll see a huge performance
increase (notice they don't need the music table at all).

Also, I would suggest using consistent names across tables.  So instead of
naming all your id fields "id" use something more specific like "ArtistID"
and "AlbumID" that way the same field name is used in both the Albums table
as well as the Music table.  This will make it easier to program against (no
effect on performance, just your sanity).
HTH,

Sam


---
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: Sreedhar.a [mailto:[EMAIL PROTECTED] 
Sent: Saturday, August 18, 2007 1:23 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] To increase search speed


Method 3:

Joining Multiple tables.
I have created 4 tables as follows;

"create table  ALBUMS (id integer primary key,Album
text,unique(Album));"
"create table  ARTISTS (id integer primary key,Artist
text,unique(Artist));"
"create table  GENRES (id integer primary key,Genre
text,unique(Genre));"

"create table MUSIC (Id integer primary key, AlbumName text not
null collate nocase,Track text not null collate nocase,ArtistName text not
null collate nocase,URL text not null collate nocase , Duration integer,
TrackFormat text not null collate nocase, BitRate integer, sampleRate
integer, Channels integer, Filesize integer,album_id integer,artist_id
integer, GenreName text not null collate nocase ,Genre_Id integer);"

Here album_id , artist_id and Genre_Id are the id values of
ALBUMS,ARTISTS,GENRES Tables.
This shows better performance than indexing for the following searches.

 
Select distinct AlbumName from MUSIC;
 
Select  distinct ArtistName from MUSIC;
 
Select  distinct  GenreName from MUSIC;


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] To increase search speed

2007-08-20 Thread Sreedhar.a
Thanks Raghavendra, 

We use sqlite statements for search. 


Best Regards,
A.Sreedhar.
 

-Original Message-
From: RaghavendraK 70574 [mailto:[EMAIL PROTECTED] 
Sent: Saturday, August 18, 2007 10:13 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] To increase search speed


use sqlite statements.

regards
ragha

**
 This email and its attachments contain confidential information from
HUAWEI, which is intended only for the person or entity whose address is
listed above. Any use of the information contained herein in any way
(including, but not limited to, total or partial disclosure, reproduction,
or dissemination) by persons other than the intended recipient(s) is
prohibited. If you receive this e-mail in error, please notify the sender by
phone or email immediately and delete it!
 

*

- Original Message -
From: "Sreedhar.a" <[EMAIL PROTECTED]>
Date: Saturday, August 18, 2007 10:52 am
Subject: [sqlite] To increase search speed

> Hi
> I am working in 3.3.6.
> I have created a table with 4 records with 12 fields as follows.
> 
> "create table MUSIC (Id integer primary key, AlbumName text not null 
> collate nocase,Track text not null collate nocase,ArtistName text not 
> null collatenocase,URL text not null collate nocase , Duration 
> integer, TrackFormat text not null collate nocase, BitRate integer, 
> sampleRate integer, Channels integer, Filesize integer GenreName text 
> not null collate nocase);"
> 
> I will often search for the following fields only.
> 
>Select distinct 
> AlbumName from MUSIC;
>Select  distinct 
> ArtistName from MUSIC;
>Select  distinct 
> GenreName from MUSIC;
>Select  distinct 
> AlbumName for particular ArtistName
>Select  Track for 
> particular AlbumName
>Select  distinct 
> ArtistName for particular GenreName
>  
> To obtain nice search speed which method will work fine.
> I have tried wilth the following methods.
> 
> Method 1:
> 
> It's the one described above
> 
> Method 2:
> 
> By doing indexing.I tried with the following.
> 
>"create  index Musicidx1 on MUSIC(ArtistName  collate 
> nocase,AlbumNamecollate nocase);"   
>"create  index Musicidx2 on MUSIC(AlbumName collate 
> nocase,ArtistNamecollate nocase,URL collate nocase);"
> 
> This gives better performance than method 1 for the following 
> searches;
>
> Selectdistinct  AlbumName for particular ArtistName
>
> SelectTrack for particular AlbumName
>
> Selectdistinct ArtistName for particular GenreName
> 
> Method 3:
> 
> Joining Multiple tables.
> I have created 4 tables as follows;
> 
>"create table  ALBUMS (id integer primary key,Album 
> text,unique(Album));"
>"create table  ARTISTS (id integer primary key,Artist 
> text,unique(Artist));"
>"create table  GENRES (id integer primary key,Genre 
> text,unique(Genre));"
> 
>"create table MUSIC (Id integer primary key, AlbumName text 
> not null collate nocase,Track text not null collate nocase,ArtistName 
> text not null collate nocase,URL text not null collate nocase , 
> Duration integer,TrackFormat text not null collate nocase, BitRate 
> integer, sampleRateinteger, Channels integer, Filesize 
> integer,album_id integer,artist_idinteger, GenreName text not null 
> collate nocase ,Genre_Id integer);"
> 
> Here album_id , artist_id and Genre_Id are the id values of 
> ALBUMS,ARTISTS,GENRES Tables.
> This shows better performance than indexing for the following 
> searches.
> 
> Select distinct AlbumName from MUSIC;
> 
> Select  distinct ArtistName from MUSIC;
> 
> Select  distinct  GenreName from MUSIC; Method 4:
> 
> Inmemory method.I will copy all the content from the temporary 
> database to inmemory and then performing search.
> If I am using this method means then while inserting records , that 
> recordwill be inserted into the temporary memory only.
> But I want to be inserted in to the or

Re: [sqlite] To increase search speed

2007-08-18 Thread Ken
I think method 3 shows the best promise:
   
  Obviously M4 is out since it is in memory only. Besides caching should help 
improve the performance.
   
  The qustion that begs to be asked is: What are your performance goals? And 
what are your performance numbers ie timings for each of the mehtods you 
indicated. 
  Re-run and capture timing for the various selects...
   
  Select distinct AlbumName from MUSIC;
 -> Select distinct albumName from albums;
  
Select distinct ArtistName from MUSIC;
-> select distinc artistName from artists;
  
Select distinct GenreName from MUSIC;
   -> select distinct genreName from genres;
  

 
   
   
  

"Sreedhar.a" <[EMAIL PROTECTED]> wrote:
  Hi
I am working in 3.3.6.
I have created a table with 4 records with 12 fields as follows.

"create table MUSIC (Id integer primary key, AlbumName text not null collate
nocase,Track text not null collate nocase,ArtistName text not null collate
nocase,URL text not null collate nocase , Duration integer, TrackFormat text
not null collate nocase, BitRate integer, sampleRate integer, Channels
integer, Filesize integer GenreName text not null collate nocase);"

I will often search for the following fields only.

Select distinct
AlbumName from MUSIC;
Select distinct
ArtistName from MUSIC;
Select distinct
GenreName from MUSIC;
Select distinct
AlbumName for particular ArtistName
Select Track for
particular AlbumName
Select distinct
ArtistName for particular GenreName

To obtain nice search speed which method will work fine.
I have tried wilth the following methods.

Method 1:

It's the one described above

Method 2:

By doing indexing.I tried with the following.

"create index Musicidx1 on MUSIC(ArtistName collate nocase,AlbumName
collate nocase);" 
"create index Musicidx2 on MUSIC(AlbumName collate nocase,ArtistName
collate nocase,URL collate nocase);"

This gives better performance than method 1 for the following searches;

Select
distinct AlbumName for particular ArtistName
Select
Track for particular AlbumName
Select
distinct ArtistName for particular GenreName

Method 3:

Joining Multiple tables.
I have created 4 tables as follows;

"create table ALBUMS (id integer primary key,Album
text,unique(Album));"
"create table ARTISTS (id integer primary key,Artist
text,unique(Artist));"
"create table GENRES (id integer primary key,Genre
text,unique(Genre));"

"create table MUSIC (Id integer primary key, AlbumName text not
null collate nocase,Track text not null collate nocase,ArtistName text not
null collate nocase,URL text not null collate nocase , Duration integer,
TrackFormat text not null collate nocase, BitRate integer, sampleRate
integer, Channels integer, Filesize integer,album_id integer,artist_id
integer, GenreName text not null collate nocase ,Genre_Id integer);"

Here album_id , artist_id and Genre_Id are the id values of
ALBUMS,ARTISTS,GENRES Tables.
This shows better performance than indexing for the following searches.


Select distinct AlbumName from MUSIC;

Select distinct ArtistName from MUSIC;

Select distinct GenreName from MUSIC;
Method 4:

Inmemory method.I will copy all the content from the temporary database to
inmemory and then performing search.
If I am using this method means then while inserting records , that record
will be inserted into the temporary memory only.
But I want to be inserted in to the original database also. Is there any
other way to do this.

Can any one help to increase my search speed.
Thanks in Advance.

Regards,
Sreedhar























Re: [sqlite] To increase search speed

2007-08-18 Thread RaghavendraK 70574

use sqlite statements.

regards
ragha
**
 This email and its attachments contain confidential information from HUAWEI, 
which is intended only for the person or entity whose address is listed above. 
Any use of the information contained herein in any way (including, but not 
limited to, total or partial disclosure, reproduction, or dissemination) by 
persons other than the intended recipient(s) is prohibited. If you receive this 
e-mail in error, please notify the sender by phone or email immediately and 
delete it!
 
*

- Original Message -
From: "Sreedhar.a" <[EMAIL PROTECTED]>
Date: Saturday, August 18, 2007 10:52 am
Subject: [sqlite] To increase search speed

> Hi
> I am working in 3.3.6.
> I have created a table with 4 records with 12 fields as follows.
> 
> "create table MUSIC (Id integer primary key, AlbumName text not 
> null collate
> nocase,Track text not null collate nocase,ArtistName text not null 
> collatenocase,URL text not null collate nocase , Duration integer, 
> TrackFormat text
> not null collate nocase, BitRate integer, sampleRate integer, Channels
> integer, Filesize integer GenreName text not null collate nocase);"
> 
> I will often search for the following fields only.
> 
>Select distinct
> AlbumName from MUSIC;
>Select  distinct
> ArtistName from MUSIC;
>Select  distinct
> GenreName from MUSIC;
>Select  distinct
> AlbumName for particular ArtistName
>Select  Track for
> particular AlbumName
>Select  distinct
> ArtistName for particular GenreName
>  
> To obtain nice search speed which method will work fine.
> I have tried wilth the following methods.
> 
> Method 1:
> 
> It's the one described above
> 
> Method 2:
> 
> By doing indexing.I tried with the following.
> 
>"create  index Musicidx1 on MUSIC(ArtistName  collate 
> nocase,AlbumNamecollate nocase);"   
>"create  index Musicidx2 on MUSIC(AlbumName collate 
> nocase,ArtistNamecollate nocase,URL collate nocase);"
> 
> This gives better performance than method 1 for the following 
> searches;
>
> Selectdistinct  AlbumName for particular ArtistName
>
> SelectTrack for particular AlbumName
>
> Selectdistinct ArtistName for particular GenreName
> 
> Method 3:
> 
> Joining Multiple tables.
> I have created 4 tables as follows;
> 
>"create table  ALBUMS (id integer primary key,Album
> text,unique(Album));"
>"create table  ARTISTS (id integer primary key,Artist
> text,unique(Artist));"
>"create table  GENRES (id integer primary key,Genre
> text,unique(Genre));"
> 
>"create table MUSIC (Id integer primary key, AlbumName 
> text not
> null collate nocase,Track text not null collate nocase,ArtistName 
> text not
> null collate nocase,URL text not null collate nocase , Duration 
> integer,TrackFormat text not null collate nocase, BitRate integer, 
> sampleRateinteger, Channels integer, Filesize integer,album_id 
> integer,artist_idinteger, GenreName text not null collate nocase 
> ,Genre_Id integer);"
> 
> Here album_id , artist_id and Genre_Id are the id values of
> ALBUMS,ARTISTS,GENRES Tables.
> This shows better performance than indexing for the following 
> searches.
> 
> Select distinct AlbumName from MUSIC;
> 
> Select  distinct ArtistName from MUSIC;
> 
> Select  distinct  GenreName from MUSIC;
> Method 4:
> 
> Inmemory method.I will copy all the content from the temporary 
> database to
> inmemory and then performing search.
> If I am using this method means then while inserting records , that 
> recordwill be inserted into the temporary memory only.
> But I want to be inserted in to the original database also. Is 
> there any
> other way to do this.
> 
> Can any one help to increase my search speed.
> Thanks in Advance.
> 
> Regards,
> Sreedhar
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
>  
> 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-