What is the fastest database?

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

What is the fastest database?

Keith (Gulf Breeze Ortho Lab)
Hello,

I have approximately 25 MB of data (plain text) that consists of approximately 140,000 different entries (one per line--pipe delimited). I am working on an application to quickly search through the data not unlike the LiveCode Dictionary application. However, it will be necessary for my users to be able to edit any entry at will.

For a desktop application, what database would you recommend for the job? I can use a flat file database, but the problem is that all 25 MB of data must be loaded into memory... What about SQLite? Valencia? Speed is of the essence.

FYI: In the past I used an Access database, but it was too sluggish for my purposes.

Any recommendations/feedback is most welcome. Thanks.

LiveCode is awesome!

Sincerely,

- Boo
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: What is the fastest database?

Jeffrey Massung
That amount of data is ridiculously small. If you don't care about many
thousands of concurrent connections and having to transform the data set
periodically over time, then SQLite3 is probably the easiest to use, but
really, for that tiny amount of data, *any* database application will work.
I've used Access (from 10+ years ago) with > 1 GB data sets and never had a
performance problem. You just to to make sure you setup the right indexes,
foreign keys, and turn off uniqueness where they really don't matter.

Jeff M.
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: What is the fastest database?

Keith (Gulf Breeze Ortho Lab)
Hi Jeff,

The thing is is that the database will grow significantly over time. Also, I
might want to eventually move it online. Therefore, I need the best solution
to cover all bases for the present and the future...

Thanks,

- Boo

----- Original Message -----
From: "Jeff Massung" <[hidden email]>
To: "How to use LiveCode" <[hidden email]>
Sent: Monday, July 25, 2011 5:00 PM
Subject: Re: What is the fastest database?


> That amount of data is ridiculously small. If you don't care about many
> thousands of concurrent connections and having to transform the data set
> periodically over time, then SQLite3 is probably the easiest to use, but
> really, for that tiny amount of data, *any* database application will
> work.
> I've used Access (from 10+ years ago) with > 1 GB data sets and never had
> a
> performance problem. You just to to make sure you setup the right indexes,
> foreign keys, and turn off uniqueness where they really don't matter.
>
> Jeff M.
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>


_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: What is the fastest database?

Jeffrey Massung
If you are thinking web and growing for the future, then it's hard to beat
PostgreSQL, which also comes with some nice web-admin tools for viewing the
database, etc. MySQL - I hear - is equally nice, but I haven't used it.

You may just want to quickly test your work with SQLite3, which is
near-identical to MySQL as far as query syntax goes, and if you think you
like it, then migrate to using MySQL.

I also recommend downloading Navcat Lite as a tool for viewing whatever
database you decide to use. It's very handy.

Jeff M.

On Mon, Jul 25, 2011 at 4:07 PM, Keith (Gulf Breeze Ortho Lab) <
[hidden email]> wrote:

> Hi Jeff,
>
> The thing is is that the database will grow significantly over time. Also,
> I might want to eventually move it online. Therefore, I need the best
> solution to cover all bases for the present and the future...
>
> Thanks,
>
> - Boo
>
> ----- Original Message ----- From: "Jeff Massung" <[hidden email]>
> To: "How to use LiveCode" <[hidden email]**>
> Sent: Monday, July 25, 2011 5:00 PM
> Subject: Re: What is the fastest database?
>
>
>  That amount of data is ridiculously small. If you don't care about many
>> thousands of concurrent connections and having to transform the data set
>> periodically over time, then SQLite3 is probably the easiest to use, but
>> really, for that tiny amount of data, *any* database application will
>> work.
>> I've used Access (from 10+ years ago) with > 1 GB data sets and never had
>> a
>> performance problem. You just to to make sure you setup the right indexes,
>> foreign keys, and turn off uniqueness where they really don't matter.
>>
>> Jeff M.
>> ______________________________**_________________
>> use-livecode mailing list
>> [hidden email]
>> Please visit this url to subscribe, unsubscribe and manage your
>> subscription preferences:
>> http://lists.runrev.com/**mailman/listinfo/use-livecode<http://lists.runrev.com/mailman/listinfo/use-livecode>
>>
>>
>
> ______________________________**_________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/**mailman/listinfo/use-livecode<http://lists.runrev.com/mailman/listinfo/use-livecode>
>
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: What is the fastest database?

Pete Haworth
All good stuff.  SQLite is great for prototyping applications and in
production use for single user applications, but it will have problems
dealing with networked file access and/or a high volume of multi-user
updating activity (due to locking issues).

Pete



On Mon, Jul 25, 2011 at 3:13 PM, Jeff Massung <[hidden email]> wrote:

> If you are thinking web and growing for the future, then it's hard to beat
> PostgreSQL, which also comes with some nice web-admin tools for viewing the
> database, etc. MySQL - I hear - is equally nice, but I haven't used it.
>
> You may just want to quickly test your work with SQLite3, which is
> near-identical to MySQL as far as query syntax goes, and if you think you
> like it, then migrate to using MySQL.
>
> I also recommend downloading Navcat Lite as a tool for viewing whatever
> database you decide to use. It's very handy.
>
> Jeff M.
>
> On Mon, Jul 25, 2011 at 4:07 PM, Keith (Gulf Breeze Ortho Lab) <
> [hidden email]> wrote:
>
> > Hi Jeff,
> >
> > The thing is is that the database will grow significantly over time.
> Also,
> > I might want to eventually move it online. Therefore, I need the best
> > solution to cover all bases for the present and the future...
> >
> > Thanks,
> >
> > - Boo
> >
> > ----- Original Message ----- From: "Jeff Massung" <[hidden email]>
> > To: "How to use LiveCode" <[hidden email]**>
> > Sent: Monday, July 25, 2011 5:00 PM
> > Subject: Re: What is the fastest database?
> >
> >
> >  That amount of data is ridiculously small. If you don't care about many
> >> thousands of concurrent connections and having to transform the data set
> >> periodically over time, then SQLite3 is probably the easiest to use, but
> >> really, for that tiny amount of data, *any* database application will
> >> work.
> >> I've used Access (from 10+ years ago) with > 1 GB data sets and never
> had
> >> a
> >> performance problem. You just to to make sure you setup the right
> indexes,
> >> foreign keys, and turn off uniqueness where they really don't matter.
> >>
> >> Jeff M.
> >> ______________________________**_________________
> >> use-livecode mailing list
> >> [hidden email]
> >> Please visit this url to subscribe, unsubscribe and manage your
> >> subscription preferences:
> >> http://lists.runrev.com/**mailman/listinfo/use-livecode<
> http://lists.runrev.com/mailman/listinfo/use-livecode>
> >>
> >>
> >
> > ______________________________**_________________
> > use-livecode mailing list
> > [hidden email]
> > Please visit this url to subscribe, unsubscribe and manage your
> > subscription preferences:
> > http://lists.runrev.com/**mailman/listinfo/use-livecode<
> http://lists.runrev.com/mailman/listinfo/use-livecode>
> >
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: What is the fastest database?

slylabs13
In reply to this post by Keith (Gulf Breeze Ortho Lab)
If you plan on moving this to an external server, then you should not allow direct access to your SQL server (although I do so myself in development using On-Rev). You will probably want to access it through a web based script of some sort to firewall your SQL data. This will prevent things like SQL injection attacks and such.

If this is your eventual goal, I would advise developing your application using this methodology right from the start.

my 2¢

Bob


On Jul 25, 2011, at 2:51 PM, Keith (Gulf Breeze Ortho Lab) wrote:

> Hello,
>
> I have approximately 25 MB of data (plain text) that consists of approximately 140,000 different entries (one per line--pipe delimited). I am working on an application to quickly search through the data not unlike the LiveCode Dictionary application. However, it will be necessary for my users to be able to edit any entry at will.
>
> For a desktop application, what database would you recommend for the job? I can use a flat file database, but the problem is that all 25 MB of data must be loaded into memory... What about SQLite? Valencia? Speed is of the essence.
>
> FYI: In the past I used an Access database, but it was too sluggish for my purposes.
>
> Any recommendations/feedback is most welcome. Thanks.
>
> LiveCode is awesome!
>
> Sincerely,
>
> - Boo
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode


_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: What is the fastest database?

Ruslan Zasukhin-2
In reply to this post by Keith (Gulf Breeze Ortho Lab)
On 7/26/11 12:51 AM, "Keith (Gulf Breeze Ortho Lab)"
<[hidden email]> wrote:

> Hello,
>
> I have approximately 25 MB of data (plain text) that consists of approximately
> 140,000 different entries (one per line--pipe delimited). I am working on an
> application to quickly search through the data not unlike the LiveCode
> Dictionary application. However, it will be necessary for my users to be able
> to edit any entry at will.
>
> For a desktop application, what database would you recommend for the job? I
> can use a flat file database, but the problem is that all 25 MB of data must
> be loaded into memory... What about SQLite? Valencia? Speed is of the essence.
>
> FYI: In the past I used an Access database, but it was too sluggish for my
> purposes.
>
> Any recommendations/feedback is most welcome. Thanks.

Hi Keith,

Our Valentina database :-)

1) It is faster in 50-100 times (usually) of any RDBMS as SqlLite, mySQL,
Postgre, MS SQL, Access.

Because Valentina DB is columnar, and because of many other things.


2)  Vertica is good but they say much much more expensive.


If you need more details let me know.
May be you can find useful this page
    http://www.paradigmasoft.com/en/testimonials


3) in fact, 25MB and 140k records is quite small database :-)
Why you think you need super-speed?

Our users did have e.g. 40 million records, 30-40GB and more...


--
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]



_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: What is the fastest database?

Alejandro Tejada
In reply to this post by Keith (Gulf Breeze Ortho Lab)
Hi Keith,

Keith (Gulf Breeze Ortho Lab) wrote
I have approximately 25 MB of data (plain text) that consists of approximately 140,000 different entries (one per line--pipe delimited). I am working on an application to quickly search through the data not unlike the LiveCode Dictionary application. However, it will be necessary for my users to be able to edit any entry at will.
I understand you perfectly, when you say that you do not want to
load the complete database in memory.

Could you compress the data??? This could reduce significantly
it's memory footprint.

Are you going to port this application to mobile devices
like Android and iPad???? This is vital to know beforehand.

Have a nice day!

Al
Reply | Threaded
Open this post in threaded view
|

Re: What is the fastest database?

Keith (Gulf Breeze Ortho Lab)
Hi Al,

The data is as compressed as possible right now (I believe, anyway). And
yes, I hope to eventually port the system to mobile.

Thanks,

- Boo

----- Original Message -----
From: "Alejandro Tejada" <[hidden email]>
To: <[hidden email]>
Sent: Monday, July 25, 2011 7:12 PM
Subject: Re: What is the fastest database?


> Hi Keith,
>
>
> Keith (Gulf Breeze Ortho Lab) wrote:
>>
>> I have approximately 25 MB of data (plain text) that consists of
>> approximately 140,000 different entries (one per line--pipe delimited). I
>> am working on an application to quickly search through the data not
>> unlike
>> the LiveCode Dictionary application. However, it will be necessary for my
>> users to be able to edit any entry at will.
>>
>
> I understand you perfectly, when you say that you do not want to
> load the complete database in memory.
>
> Could you compress the data??? This could reduce significantly
> it's memory footprint.
>
> Are you going to port this application to mobile devices
> like Android and iPad???? This is vital to know beforehand.
>
> Have a nice day!
>
> Al
>
> --
> View this message in context:
> http://runtime-revolution.278305.n4.nabble.com/What-is-the-fastest-database-tp3694246p3694491.html
> Sent from the Revolution - User mailing list archive at Nabble.com.
>
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>


_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: What is the fastest database?

Andre Garzia-3
In reply to this post by slylabs13
On Mon, Jul 25, 2011 at 7:49 PM, Bob Sneidar <[hidden email]> wrote:

> If you plan on moving this to an external server, then you should not allow
> direct access to your SQL server (although I do so myself in development
> using On-Rev). You will probably want to access it through a web based
> script of some sort to firewall your SQL data. This will prevent things like
> SQL injection attacks and such.
>
> If this is your eventual goal, I would advise developing your application
> using this methodology right from the start.
>
> my 2¢
>

what he said!


>
> Bob
>
>
> On Jul 25, 2011, at 2:51 PM, Keith (Gulf Breeze Ortho Lab) wrote:
>
> > Hello,
> >
> > I have approximately 25 MB of data (plain text) that consists of
> approximately 140,000 different entries (one per line--pipe delimited). I am
> working on an application to quickly search through the data not unlike the
> LiveCode Dictionary application. However, it will be necessary for my users
> to be able to edit any entry at will.
> >
> > For a desktop application, what database would you recommend for the job?
> I can use a flat file database, but the problem is that all 25 MB of data
> must be loaded into memory... What about SQLite? Valencia? Speed is of the
> essence.
> >
> > FYI: In the past I used an Access database, but it was too sluggish for
> my purposes.
> >
> > Any recommendations/feedback is most welcome. Thanks.
> >
> > LiveCode is awesome!
> >
> > Sincerely,
> >
> > - Boo
> > _______________________________________________
> > use-livecode mailing list
> > [hidden email]
> > Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> > http://lists.runrev.com/mailman/listinfo/use-livecode
>
>
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>



--
http://www.andregarzia.com All We Do Is Code.
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

RE: What is the fastest database?

Lynn Fredricks-2
In reply to this post by Keith (Gulf Breeze Ortho Lab)
> For a desktop application, what database would you recommend
> for the job? I can use a flat file database, but the problem
> is that all 25 MB of data must be loaded into memory... What
> about SQLite? Valencia? Speed is of the essence.

Ill try to avoid a tag team with Ruslan on this ;-)

In terms of Valentina, your database is positively tiny compared with many
of our users, though the number of records you have are reaching the point
where you'll begin to see some real differences between database engines.

Ruslan has already mentioned the super speed. A few other points which I
think you'll find important in evaluating Valentina:

- Load it into Memory. Yes, you can do this - this can be done with the
local version of Valentina (Valentina ADK) or on the server implementations.

- Valentina Plays Well With LiveCode. One differentiator of Valentina is
that we have the means to support many development tools ourselves.
Paradigma is a long time partner of LiveCode. We compete against many of the
big boy databases (esp on the server side), but show of hands the last time
IBM, Microsoft or Oracle went out of their way to do anything to adapt to
the needs of the LiveCode community?

- 12 Months of Updates. If you like all that's a part of Valentina DB now,
it will get even better down the road. You get 12 months of updates, which
includes new features, fixes and performance enhancements.

- We Support a Lot of Platforms. We've been at this now for quite some time
( well into the "plus" side after the first decade), and we've been able to
support a broad range of development tools and operating systems (together
forming "platforms"). For this reason, you have a lot of flexibility in
planning your implementations - for example, how you might handle building a
"heavy" client and what might be on the server (PHP, Ruby on Rails, etc).

- We Got Tools That Do the Job. Valentina Studio Pro is a solid tool for
administering and developing your databases - and it supports SQLite, too,
as well as provide a visual builder for reports if you also get Valentina
Reports. There are also free admin tools for Valentina Server for iPad and
iPhone.

- We are Going to Be Here Tomorrow. The last thing I want to happen when I
invest in infrastructure tools is the vendor to either A) die or B) go
VC-Postal, get huge investments, then either get bought out by an unloving
god or do a bizzare business model change that messes with my business. We
haven't gone VC-Postal, and we are, for the most part, healthier today than
we were yesterday (well, a little hair loss and expanding beltline too).

I hope this is useful information for you.

Best regards,

Lynn Fredricks
President
Paradigma Software
http://www.paradigmasoft.com

Valentina SQL Server: The Ultra-fast, Royalty Free Database Server


_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode