Thread: Multi Dimensional Arrays?

Hi All

I'm looking at a project that uses a lot of MS SQL database interaction and i am a bit concerned about the speed of delivering pages when querying a database table that is approaching 1.2 million records. This project is for an Internet Radio station and currently the only option i see is running the query each time the page refreshes, which happens at about the same time for all users viewing the stations now playing page.

I'm looking into ways to reduce the load on the server by keeping some of the frequently required information in Application Variable which would be created from arrays, so effectively using Application Arrays, I would like your thoughts on this method and weather there would be any speed advantages to doing it this way.

Let me explain a bit more..

The page has 4 main areas, Now Playing, History (Last ten tracks played), This weeks Chart (top 10) and All Time Top Ten. I am proposing to hold the last 3 areas in the Application Variables, History and the two top tens.

Now the All time top ten is fairly static just minor changes once in a while, usually just adding new requested times value and occasionally one will move up a place like you would expect in a chart.

The weekly top ten is about the same but more frequently

The history list updates each time a track finishes and with this one i will need to remove the first array item and add one at the end. This gives me one of my current questions, can the array be renumbered so it always begins array(0) when i remove the contents of array(0) or would i need to shuffle all the other items to fill array(0)?

If anyone has a better suggestion i am currently in the thinking and experimenting stage so am open to any ideas at the moment.

Regards
Ian

Thanks for the reply Wolffy.

The only problem i have there is they all sound like changes in the database and the client (so to speak) is saying i cant make changes in the database as it is created and managed by the broadcast software.

I had already suggested creating a separate table to contain the history list but he is nervous about any thing being done in the actual database as it is used for reporting to the music industry for licencing and royalties. And apparently the software seems to get reinstalled regularly as the writers are constantly doing updates to fix bugs.

Also i am a complete novice with MS SQL so have been unable to convince him that changes wont cause problems if the software is reinstalled.

I am currently working on a theory of having the page create static elements of the relevant bits when the first browser refreshes then all subsequent browser refreshes will load the static page elements. I am still running tests making the second browsers to wait while the first refresher generates the elements but i think it will work using an application lock.

The main aim is to stop the listeners complaining that when the track changes they get errors on their browsers as it takes so long for the page to be generated, hence i came up with the static element option so the queries and code would only run once per track change.

Thanks again for the suggestions, i will look into the indexing to make sure the queries are executed as quickly as possible.