"Ronan Lucio" <[EMAIL PROTECTED]> 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
