Ability to convert Access to SQLite

classic Classic list List threaded Threaded
18 messages Options
Reply | Threaded
Open this post in threaded view
|

Ability to convert Access to SQLite

scabral
HI,

i am new to SQLite and i currently have an access database that i would like to convert over to a SQLite database.

there are a couple of processes that would need to take place in order for this to work and i'm not sure if SQLite has these capabilities:

1.  Need to automatically import text file into SQLite database on local machine (machine always on).  I was thinking of using scheduled task to run a script to do this, but not sure if that is possible.
2.  Neet to somehow create a front-end to query data from SQLite database (not sure if SQLite has front end application).

Basically, i want to get rid of the Access database because the user's have to manually update the tables everyday from the text files that get ftp'd over.  I was trying to figure out a solution that would automatically load the text files withouth any human intervention and then give the user's the ability to view the data through some sort of front-end (similar to Access form).

Thanks
scott
Reply | Threaded
Open this post in threaded view
|

Re: Ability to convert Access to SQLite

Puneet Kishor-2
On Wed, Jul 22, 2009 at 11:34 AM, scabral<[hidden email]> wrote:

>
> HI,
>
> i am new to SQLite and i currently have an access database that i would like
> to convert over to a SQLite database.
>
> there are a couple of processes that would need to take place in order for
> this to work and i'm not sure if SQLite has these capabilities:
>
> 1.  Need to automatically import text file into SQLite database on local
> machine (machine always on).  I was thinking of using scheduled task to run
> a script to do this, but not sure if that is possible.

Sure it is possible. You will have to choose your favorite programming
language, write a program to locate and import the local text file,
and set up a scheduler to do so periodically.

> 2.  Neet to somehow create a front-end to query data from SQLite database
> (not sure if SQLite has front end application).

Lots of third-party applications. You might end up writing your own to
suit your own requirements.


>
> Basically, i want to get rid of the Access database because the user's have
> to manually update the tables everyday from the text files that get ftp'd
> over.  I was trying to figure out a solution that would automatically load
> the text files withouth any human intervention and then give the user's the
> ability to view the data through some sort of front-end (similar to Access
> form).
>
> Thanks
> scott

--
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
-----------------------------------------------------------------------
Assertions are politics; backing up assertions with evidence is science
=======================================================================
Sent from Madison, WI, United States
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Ability to convert Access to SQLite

Rich Shepard
On Wed, 22 Jul 2009, P Kishor wrote:

> Sure it is possible. You will have to choose your favorite programming
> language, write a program to locate and import the local text file, and
> set up a scheduler to do so periodically.

> Lots of third-party applications. You might end up writing your own to
> suit your own requirements.

   Allow me to expand on these thoughts. SQLite is a database engine that has
a command line interface. However, it is most often used embedded in an
application. In Scott's case, the application would consist of the user
interface, the middleware, and reporting capabilities.

   Just about any programming language can be used. I prefer python but ruby
is also quick, easy, and reliable. The UI would have whatever is wanted or
needed by the users. The middleware controls the acquisition and import of
the text files can be run as a cron job or by selecting a menu item. Any
needed reports can be written in the tools available for that language.

   There are a suite of tools called 'mdb' that facilitate migration of data
from Access to real RDBMSs. I've not used them so cannot comment on their
effectiveness.

Rich

--
Richard B. Shepard, Ph.D.               |  Integrity            Credibility
Applied Ecosystem Services, Inc.        |            Innovation
<http://www.appl-ecosys.com>     Voice: 503-667-4517      Fax: 503-667-8863
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Ability to convert Access to SQLite

scabral
In reply to this post by Puneet Kishor-2
Do you know of any third party applications that i could use to build the front end?  This will need to run on local machines, not on servers.  So, some sort of desktop application i would imagine....

thanks
Scott
P Kishor-3 wrote
On Wed, Jul 22, 2009 at 11:34 AM, scabral<scabral7@lifespan.org> wrote:
>
> HI,
>
> i am new to SQLite and i currently have an access database that i would like
> to convert over to a SQLite database.
>
> there are a couple of processes that would need to take place in order for
> this to work and i'm not sure if SQLite has these capabilities:
>
> 1.  Need to automatically import text file into SQLite database on local
> machine (machine always on).  I was thinking of using scheduled task to run
> a script to do this, but not sure if that is possible.

Sure it is possible. You will have to choose your favorite programming
language, write a program to locate and import the local text file,
and set up a scheduler to do so periodically.

> 2.  Neet to somehow create a front-end to query data from SQLite database
> (not sure if SQLite has front end application).

Lots of third-party applications. You might end up writing your own to
suit your own requirements.


>
> Basically, i want to get rid of the Access database because the user's have
> to manually update the tables everyday from the text files that get ftp'd
> over.  I was trying to figure out a solution that would automatically load
> the text files withouth any human intervention and then give the user's the
> ability to view the data through some sort of front-end (similar to Access
> form).
>
> Thanks
> scott

--
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
-----------------------------------------------------------------------
Assertions are politics; backing up assertions with evidence is science
=======================================================================
Sent from Madison, WI, United States
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Ability to convert Access to SQLite

Nuno Magalhães-2
> Do you know of any third party applications that i could use to build the
> front end?  This will need to run on local machines, not on servers.  So,
> some sort of desktop application i would imagine....

That would be OS-dependent now, wouldn't it? Unless you're going to
use Java or PHP or similar. If the former there are JDBC drivers
available.

--
()  ascii ribbon campaign - against html e-mail
/\  ascii-rubanda kampajno - kontraŭ html-a retpoŝto
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Ability to convert Access to SQLite

Rich Shepard
In reply to this post by scabral
On Wed, 22 Jul 2009, scabral wrote:

> Do you know of any third party applications that i could use to build the
> front end?  This will need to run on local machines, not on servers.  So,
> some sort of desktop application i would imagine....

Scott,

   Each language has its own. However, from your description it appears that
you want to keep the SQLite database on a server yet have multiple users
remotely acess it for data entry, queries, and reporting.

   If this is correct, I strongly recommend that you use Ruby on Rails to
build the system. The UI is any Web browser. Users log in to the application
from any machine with a browser and connection to the Internet (or your
local intranet), and work with a single instance of the database. This
approach makes the system scalable, flexible, and broadly useful.

Rich

--
Richard B. Shepard, Ph.D.               |  Integrity            Credibility
Applied Ecosystem Services, Inc.        |            Innovation
<http://www.appl-ecosys.com>     Voice: 503-667-4517      Fax: 503-667-8863
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Ability to convert Access to SQLite

Puneet Kishor-2
In reply to this post by scabral
On Wed, Jul 22, 2009 at 12:12 PM, scabral<[hidden email]> wrote:
>
> Do you know of any third party applications that i could use to build the
> front end?  This will need to run on local machines, not on servers.  So,
> some sort of desktop application i would imagine....

Applications that build applications? I am sure there must be, but as
you keep on abstracting, you lose control and things get messier.

Forget about local machines and servers.

First, poke around sqlite.org for about 45 mins and you will have
many, many of your questions answered and would have acquired a ton of
background knowledge.

Second, determine what programming language you know best. No matter
what anyone says about Perl being the best language, the most powerful
language is the one you know now.

Third, find out if that language supports working with a database.
That will help you determine whether you want to create a desktop
application or a web-based application, which, by the way, can also
run as a desktop application, that is, on a local machine.

Have fun, come back, ask more questions.


>
> thanks
> Scott
>
> P Kishor-3 wrote:
>>
>> On Wed, Jul 22, 2009 at 11:34 AM, scabral<[hidden email]> wrote:
>>>
>>> HI,
>>>
>>> i am new to SQLite and i currently have an access database that i would
>>> like
>>> to convert over to a SQLite database.
>>>
>>> there are a couple of processes that would need to take place in order
>>> for
>>> this to work and i'm not sure if SQLite has these capabilities:
>>>
>>> 1.  Need to automatically import text file into SQLite database on local
>>> machine (machine always on).  I was thinking of using scheduled task to
>>> run
>>> a script to do this, but not sure if that is possible.
>>
>> Sure it is possible. You will have to choose your favorite programming
>> language, write a program to locate and import the local text file,
>> and set up a scheduler to do so periodically.
>>
>>> 2.  Neet to somehow create a front-end to query data from SQLite database
>>> (not sure if SQLite has front end application).
>>
>> Lots of third-party applications. You might end up writing your own to
>> suit your own requirements.
>>
>>
>>>
>>> Basically, i want to get rid of the Access database because the user's
>>> have
>>> to manually update the tables everyday from the text files that get ftp'd
>>> over.  I was trying to figure out a solution that would automatically
>>> load
>>> the text files withouth any human intervention and then give the user's
>>> the
>>> ability to view the data through some sort of front-end (similar to
>>> Access
>>> form).
>>>
>>> Thanks
>>> scott
>>
>> --
>> Puneet Kishor http://www.punkish.org
>> Carbon Model http://carbonmodel.org
>> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
>> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
>> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
>> -----------------------------------------------------------------------
>> Assertions are politics; backing up assertions with evidence is science
>> =======================================================================
>> Sent from Madison, WI, United States
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>
> --
> View this message in context: http://www.nabble.com/Ability-to-convert-Access-to-SQLite-tp24609886p24610625.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



--
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
-----------------------------------------------------------------------
Assertions are politics; backing up assertions with evidence is science
=======================================================================
Sent from Madison, WI, United States
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Ability to convert Access to SQLite

Chris-737
In reply to this post by scabral
Since this appears to be a reporting application with
multiple readers, why not use an HTML report output and
any web browser. Then if you decide to publish the reports
on your network, there is little more needed to do so.

On Wed, 2009-07-22 at 10:12 -0700, scabral wrote:

> Do you know of any third party applications that i could use to build the
> front end?  This will need to run on local machines, not on servers.  So,
> some sort of desktop application i would imagine....
>
> thanks
> Scott
>
> P Kishor-3 wrote:
> >
> > On Wed, Jul 22, 2009 at 11:34 AM, scabral<[hidden email]> wrote:
> >>
> >> HI,
> >>
> >> i am new to SQLite and i currently have an access database that i would
> >> like
> >> to convert over to a SQLite database.
> >>
> >> there are a couple of processes that would need to take place in order
> >> for
> >> this to work and i'm not sure if SQLite has these capabilities:
> >>
> >> 1.  Need to automatically import text file into SQLite database on local
> >> machine (machine always on).  I was thinking of using scheduled task to
> >> run
> >> a script to do this, but not sure if that is possible.
> >
> > Sure it is possible. You will have to choose your favorite programming
> > language, write a program to locate and import the local text file,
> > and set up a scheduler to do so periodically.
> >
> >> 2.  Neet to somehow create a front-end to query data from SQLite database
> >> (not sure if SQLite has front end application).
> >
> > Lots of third-party applications. You might end up writing your own to
> > suit your own requirements.
> >
> >
> >>
> >> Basically, i want to get rid of the Access database because the user's
> >> have
> >> to manually update the tables everyday from the text files that get ftp'd
> >> over.  I was trying to figure out a solution that would automatically
> >> load
> >> the text files withouth any human intervention and then give the user's
> >> the
> >> ability to view the data through some sort of front-end (similar to
> >> Access
> >> form).
> >>
> >> Thanks
> >> scott
> >
> > --
> > Puneet Kishor http://www.punkish.org
> > Carbon Model http://carbonmodel.org
> > Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
> > Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
> > Nelson Institute, UW-Madison http://www.nelson.wisc.edu
> > -----------------------------------------------------------------------
> > Assertions are politics; backing up assertions with evidence is science
> > =======================================================================
> > Sent from Madison, WI, United States
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Ability to convert Access to SQLite

cmartin-2
In reply to this post by scabral
On Wed, 22 Jul 2009, scabral wrote:

> i am new to SQLite and i currently have an access database that i would like
> to convert over to a SQLite database.
>
> 1.  Need to automatically import text file into SQLite database on local
> machine (machine always on).  I was thinking of using scheduled task to run
> a script to do this, but not sure if that is possible.
> 2.  Neet to somehow create a front-end to query data from SQLite database
> (not sure if SQLite has front end application).
>
> Basically, i want to get rid of the Access database because the user's have
> to manually update the tables everyday from the text files that get ftp'd
> over.  I was trying to figure out a solution that would automatically load
> the text files withouth any human intervention and then give the user's the
> ability to view the data through some sort of front-end (similar to Access
> form).

If your only problem is automating the import of these text files, there
are simpler solutions than switching the SQLite. You should be able to
create a scheduled script (VBScript, Python, whatever) that will import
the text files directly into Access--the work involved will not be
appreciably different than what will be required to script/program a
utility to import the data into SQLite. By sticking with Access, once you
have the script in place everything you currently have should work the
same as it does now.

Of course, there are possibly many other (good) reasons you want to
migrate the whole thing to a SQLite backend, if so, proceed, but to
solve just the problem you describe, you may want to stick to Access and
just script the import.

Chris
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Ability to convert Access to SQLite

Simon Slavin-2
In reply to this post by scabral

On 22 Jul 2009, at 5:34pm, scabral wrote:

> 1.  Need to automatically import text file into SQLite database on  
> local
> machine (machine always on).  I was thinking of using scheduled task  
> to run
> a script to do this, but not sure if that is possible.

What programming languages or scripting languages do you know ?  Which  
operating system is that machine using ?

> 2.  Neet to somehow create a front-end to query data from SQLite  
> database
> (not sure if SQLite has front end application).

It can talk to many many programming languages.  This will be  
relatively easy, it's getting the data out of Access, and from the  
text files that's hard.

Do your users have access to web browsers ?  Can you write in PHP ?  
Do you have a web server set up ?

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Ability to convert Access to SQLite

scabral
The OS is Windows XP.  I know some VB script, some pearl and i'v used Visual VB and C# as well.

I was thinking that the script would called from a windows scheduled task and the script would then import the text file into the table in SQLite...Sounds easy, but i'm not sure what's available to create script.

thanks
Scott
Simon Slavin-2 wrote
On 22 Jul 2009, at 5:34pm, scabral wrote:

> 1.  Need to automatically import text file into SQLite database on  
> local
> machine (machine always on).  I was thinking of using scheduled task  
> to run
> a script to do this, but not sure if that is possible.

What programming languages or scripting languages do you know ?  Which  
operating system is that machine using ?

> 2.  Neet to somehow create a front-end to query data from SQLite  
> database
> (not sure if SQLite has front end application).

It can talk to many many programming languages.  This will be  
relatively easy, it's getting the data out of Access, and from the  
text files that's hard.

Do your users have access to web browsers ?  Can you write in PHP ?  
Do you have a web server set up ?

Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Ability to convert Access to SQLite

Rich Shepard
On Wed, 22 Jul 2009, scabral wrote:

> The OS is Windows XP.  I know some VB script, some pearl and i'v used Visual
> VB and C# as well.
>
> I was thinking that the script would called from a windows scheduled task
> and the script would then import the text file into the table in
> SQLite...Sounds easy, but i'm not sure what's available to create script.

Scott,

   Given your needs and wants, I'll second the suggestion that you first
automate getting the scripts into Access, then work on the conversion to
SQLite.

   BTW, the language is Perl, not Pearl. :-) It's easy to get confused since
those who like it think it's a gem.

   I _strongly_ recommend that you get away from the Microsoft-specific
languages, and use one that works on any platform. While I prefer Python
because it has extensive support for science, mathematics, graphing, etc.
you might want to consider Ruby so you can build a web-hosted application.
Many open source tools have been ported to Microsoft over the years so you
cannot go wrong by learning to use these. Learn to work comfortably in a
terminal so you can write code and administrative tools in a text editor.
You'll not only learn the language better than if you relied on GUI tools to
draw things, but you'll know just what it's doing under the hood. As a
developer you'll want that knowledge and control.

Rich

--
Richard B. Shepard, Ph.D.               |  Integrity            Credibility
Applied Ecosystem Services, Inc.        |            Innovation
<http://www.appl-ecosys.com>     Voice: 503-667-4517      Fax: 503-667-8863
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Ability to convert Access to SQLite

Alan March-2
In reply to this post by scabral
I would recommend Talend (http://www.talend.com/). It allows for
export/import to/from almost any structured data source (including sqlite
and Access) and has very good scripting capabilities (a visual designer
which generates java or perl code). It doesn't have a scheduler but this may
be managed thru some other tool such as http://jobscheduler.sourceforge.net/
or, as you said, the windows task scheduler.




> -----Original Message-----
> From: [hidden email] [mailto:sqlite-users-
> [hidden email]] On Behalf Of scabral
> Sent: miércoles, 22 de julio de 2009 02:30 p.m.
> To: [hidden email]
> Subject: Re: [sqlite] Ability to convert Access to SQLite
>
>
> The OS is Windows XP.  I know some VB script, some pearl and i'v used
> Visual
> VB and C# as well.
>
> I was thinking that the script would called from a windows scheduled
> task
> and the script would then import the text file into the table in
> SQLite...Sounds easy, but i'm not sure what's available to create
> script.
>
> thanks
> Scott
>
> Simon Slavin-2 wrote:
> >
> >
> > On 22 Jul 2009, at 5:34pm, scabral wrote:
> >
> >> 1.  Need to automatically import text file into SQLite database on
> >> local
> >> machine (machine always on).  I was thinking of using scheduled task
> >> to run
> >> a script to do this, but not sure if that is possible.
> >
> > What programming languages or scripting languages do you know ?
> Which
> > operating system is that machine using ?
> >
> >> 2.  Neet to somehow create a front-end to query data from SQLite
> >> database
> >> (not sure if SQLite has front end application).
> >
> > It can talk to many many programming languages.  This will be
> > relatively easy, it's getting the data out of Access, and from the
> > text files that's hard.
> >
> > Do your users have access to web browsers ?  Can you write in PHP ?
> > Do you have a web server set up ?
> >
> > Simon.
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
>
> --
> View this message in context: http://www.nabble.com/Ability-to-convert-
> Access-to-SQLite-tp24609886p24610956.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 8.5.392 / Virus Database: 270.13.23/2254 - Release Date:
> 07/22/09 05:59:00

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Ability to convert Access to SQLite

Simon Slavin-2
In reply to this post by scabral

On 22 Jul 2009, at 6:29pm, scabral wrote:

> The OS is Windows XP.  I know some VB script, some pearl and i'v  
> used Visual
> VB and C# as well.

Post on programming fora about VB (I assume you mean Visual Basic)  
(whichever version you're using) about accessing SQLite databases.  
They'll tell you how to get started.

Do not get involved in the C# interface to SQLite unless you're  
willing to spend many hours writing your program.  The way you asked  
your question suggests you don't have the experience you'd need.

Note: it's important that you understand that if you have multiple  
computers all accessing data in one database, there will be a server  
involved here.  Whether that server is a file server, or a web server,  
or you write one yourself, you won't be able to get away from having  
one somewhere.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Ability to convert Access to SQLite

Rob Sciuk
In reply to this post by scabral

I believe that one poster (Rich Shepard?) touched upon the MDB-Tools in a
response.  I concur, if you have the skills to build these tools from
source, the rest is easy ...

The MDB Tools project hosted on SourceForge has a set of utilities which
can enumerate tables, pull data, and schema info and convert same from
.jet or .mdb databases into a vanilla SQL dialect which is very easy to
convert to SQLite.

I have copied numerous large .mdb files from windows onto BSD or Linux
platforms and successfully converted same to SQL databases on various
occasions, and the mdb-tools are quite useful in this regard.  YMMV.

  http://sourceforge.net/projects/mdbtools/files/

Hope this helps ...

Cheers,
Rob Sciuk
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Overhead of indexes?

Jim Showalter-4
In reply to this post by Simon Slavin-2
I have everything working now, and am just tidying up. I want to add
indexes, if needed, but this is for a cellphone app, and I'm worried
about using up space, particularly when the data is unlikely to be
more than a couple thousand rows in total. But there is a query that
might be slow without indexes, so it's kind of a catch-22.

The query looks like this:

select distinct * from tbl where a <> 0 order by a desc, b desc, c
desc, d desc limit 2;

where a, b, c, and d are all type INTEGER.

What is the overhead in O notation for adding an index to a column in
SQLite?

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Overhead of indexes?

Igor Tandetnik
Jim Showalter <[hidden email]> wrote:
> What is the overhead in O notation for adding an index to a column in
> SQLite?

You mean size overhead? O(n): the index is basically just another table
(usually with fewer columns than the original). There's one record in
the index for every record in the underlying table.

Igor Tandetnik



_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Overhead of indexes?

Simon Slavin-2
In reply to this post by Jim Showalter-4

On 23 Jul 2009, at 2:20am, Jim Showalter wrote:

> The query looks like this:
>
> select distinct * from tbl where a <> 0 order by a desc, b desc, c
> desc, d desc limit 2;
>
> where a, b, c, and d are all type INTEGER.

To add to Igor's point, how much use an index is varies depending on  
how many values there can be for each of its fields.  So, for example,  
if there are lots of possible values for 'b' then there having 'b' in  
the index can save a great deal of sorting.  But if 'b' is likely to  
have one of just three possible values then sorting on it takes less  
effort.

However, there's another possible payoff: SQLite is clever.  If it  
finds all the values it needs in the index it's using, it doesn't  
bother to read the record.  So if all you really want are the values  
from fields a, b, c and d, then making one index with all four fields  
in allows SQLite to do the WHERE, the ORDER BY, and the SELECT purely  
from the index, without having to read the data record at all.

Fortunately, you don't have to change your code to test all this out.  
Write a couple thousand records without the index and test its speed.  
Add one kind of index, see how much the file has grown, and see if the  
speed increase is worth it.  Try a different index and try it again.  
No need to rewrite any of your INSERT or SELECT code.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users