"Ronan Lucio" <ronanl@stripped> wrote on 01/18/2005 01:13:10 PM:
> Hello,
>
> Trying to keep the database and the application fast,
> I´d like to clearify my mind about it.
>
> 1) Is a SELECT DISTINCT over 5,000 records a weight
> query? (Supposing it has about 20 different option the the
> DISTINCT key).
>
> 2) Is SELECT ORDER BY RAND() over 1,500 records
> a weight query?
>
> I need to put these two queries in the first page of our site.
> So, I´ve been worried if it can slow down our site in the
> pics.
>
> Thanks,
> Ronan
>
>
Depending on the size of the data, you may be better off caching your 5000
rows and 1500 rows in arrays on your web server. You would only need to
refill the array if your source data changed. Since you should also
control the code that updates the source data of the arrays, you can have
it refill your server-cached arrays as soon as it finishes making its
changes (inserts, updates, or deletes) to the source data.
I can't tell you exactly which commands/objects/techniques to use to make
static, global instances of those arrays (so that every user-specific
thread sees the same objects) as you never said what your web
server/language was. For example: if you were using an IIS/ASP server, I
would tell you to store the arrays (NOT the recordsets! convert the
recordsets to scalar data first) in the Application object (NOT the
Session object).
I recommend this because you say this is going to be served on the first
page of your site. Everyone is going to need this data at least once. By
keeping those lists as arrays in the web server's memory then randomly
picking from the arrays, you will get blazing performance. Update the
arrays only when the source data changes. That way you can save your SQL
cycles for other, less predictable queries. I have reduced the response
times on some websites to 20% or less than their original time by using
this technique (even with query caching enabled! The time savings is not
just in avoiding query processing but also due to eliminating network lag
and data transfer time).
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
