| Re: Query or Array functions [message #176974 is a reply to message #176958] |
Sat, 11 February 2012 17:20   |
Peter H. Coffin
Messages: 245
Registered: September 2010
Karma:
|
Senior Member |
|
|
On Fri, 10 Feb 2012 09:26:21 +0100, ?lvaro G. Vicario wrote:
> If you are worried about retrieving matching rows from a 5000 row DB
> table using a SQL query, I doubt that using a file to store a serialized
> 5000 element bi-dimensional array and looping though it to find matches
> will be faster; not to mention the mess of keeping data up-to-date and
> taking care of concurrent access.
Kinda depends on the application. A reasonable reason to load 5000 rows
into a structure would be if you're doing some really fast lookups and
you have the memory to spare for it. A place that you might be wanting
to do that is a custom ETL tool for a single process. Pull data from
tape, update old codes to new codes on the fly, store data in a
database. Sure, you can DO it in the database with a salting of joins
and ALTERs, but if you're already getting the data out of something that
needs a program to do it anyway, why not fix it on the fly?
HOWEVER, that's a really specialized case. The vast amjority of the
time, you're not going to know that you're probably going to need the
whole table, and you're going to be way better off letting a database
engine manage it. What you're actually doing may actually only end up
needing a few hundred rows, and the DB will very quickly have those in a
cache and accessible for very little overhead. In which case, you're
moving far less data off of the disk (which is achingly slow in
comparison to fetching the same data from a cache, even layers deep),
and your net result is much more speed. The cache resources are going to
be allocated and used ANYWAY already, so you're not even "using less" by
neglecting the DB.
> A 10,000,000 record table is a big table. A 5,000 record table is not.
> Just make sure that your SQL knowledge goes beyond "SELECT * FROM foo".
> I've seen too many amateurs in forums building web sites without even
> using the WHERE clause.
"SELECT *" is "shovelling too much data", similar to above, even in
addition to how much trouble gets caused when someone comes along and
sticks another column onto the front of the record layout in a few
years.
And EVERY table ends up being used far longer than anyone ever expects.
My SO's senior project for a college course is still out there after
most of a decade, doing its thing, and is actively used by a couple of
hundred people a month.
--
Technical points aside, you could probably beat someone to
death with a Newton if you had to. Try that with a Palm Pilot!
--Dan Duncan in comp.sys.newton.misc
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]