|
|
Posted by Jonathan on 08/22/06 08:36
lawrence k wrote:
> The following function is way too slow. If anyone has any suggestions
> about how to speed it up, I'd be grateful for them. We have to call
> this function 36 times on one page, and I think each time it takes half
> a second, so it adds up to maybe 18 seconds, which is a lot when you're
> showing software to a client. The reponse we get is "Why is it so
> slow?"
<cut>php script(/cut>
Lawrence,
My guess is that your problems are in the database related queries and
the way you prefer to execute and handle them.
I prefer doing things in the database, as they are made for selecting,
sorting and organizing. They are probably more memory and speed
efficient than doing it in your own code.
In my opinion it is useless to query all bands (the result set will
probably be the whole table of bands) and then select the bands
starting with a certain letter using you program logic.
If you want to be sure you can write a script of course which times the
display and execution of 36 or so queries to get all characters from the
database and group them per character or query all the bands and display
them in groups using your program code. My guess is that your program
logic will consume (much) more time as you have to check whether the
first character of the current record differs from the one before.
Perhaps it is also a good idea to spend some time on the way you
organize your database and define it's indexes, as the latter can really
improve the speed of your queries. Holding all the data in one big table
is also a bad idea, normalize your data so to minimize duplicate data in
your database, this will also improve indexing.
A few examples of search examples using the (my)SQL syntax:
example 1:
SELECT * FROM bands WHERE LOWER(bandname) LIKE 'a';
The above example will simply select every band starting with a lower or
capital a.
example 2:
BANDS table:
id_band
bandname
id_genre
GENRE table:
id_genre
genrename
If you have normalized your tables (for information aboout normalization
for databases, please use google, it willl give you plenty of good
results) you would perhaps have a table which will hold all the genres
and another for all the bands. Each band will have a genre associated
with it in the bands table which can be linked to the genre table using
queries like this:
SELECT bands.bandname, genre.genrename
FROM bands
JOIN genre
ON genre.id_genre = bands.id_genre
WHERE genre.genrename = 'grunge' OR genre.genrename = 'punk';
Above query will result in a two columns result set with the name of the
band in one and the associated genre (either punk or grunge) in the
second column.
A third table could hold all the album data which should be related to
the bands table, but can also be related to the genre table or some
other table holding producers, recording studio or what so ever.
There probably is a newsgroup/forum for your preferred database backend.
If you are looking for MySQL groups news://mailing.database.mysql
would be a good one to start.
I know I went a bit OT, but who knows it might help you improve the
speed of your code as well.
Kind regards,
Jonathan
Navigation:
[Reply to this message]
|