Hosting on Remote SQLServer?

Just recently I finished made the mods to WWF to fit the needs of my web site. The database for said web site is hosted outside the local network of the web site. The web site is heavily dependent of on the database and some pages are graphics heavy with the average page loading in 1 - 7 seconds.

On my development server on the local network with the database server page loads for the forum is always less than 1 second. On the production server it's over 15 seconds and as high as 25 seconds.

No major changes were made to the underlying code of the forum. Could anyone explain this?

Dev Server local to Database
http://69.26.135.138/msg
Production Server
http://www.longbox.com/msg/

I have run a few test and it appears to be the multiple calls to the database that is slowing down the forum. Has anyone else run into this problem our does every else use a SQL server local to their web server?


Edited by ljamal

I'm finished with most of the default page.
I reduced Categories, forums, moderators, and last post to one SQL connection which has reduced the default page from 19+ seconds to 2-3 seconds.

The only additional SQL calls are made off the page in the include file so I won't be touching those until complete as they will affect other pages that I really don't want to clean-up now.

See the difference between the page before optimization
http://www.longbox.com/msg/default-pre.asp
and the page with the optimizations
http://www.longbox.com/msg/

What I did was take advantage of SQL Server ability to return multiple record sets from single query.

The first returns a view which joins the category names and IDs with the forum information. This reduce the multiple calls oer category needed to return the forum information. I stored the recordset into an array and I use a for statement to loop through the array. It stores the previous CATID for comparison as it loops and it not eaqual it displays the category name and update the CATID storage variable. If equal it just returns the forum names.

For moderators, I return all the moderator group information complete with forum_ID and loop through to create a string of variables. Each database row is separated by "|" and each column by "::". This string is then parsed to into an array (using Split). If the array element is associated with a forum then, the forum moderator information is stored. The moderator user information is stored and returned in the same way.

For the last post, I created a view with returns the last post for each forum and store it into an array much like I did with the moderators. I then iterate through the array until the forum ID is equal and use that information as the last post.

As is evident by visiting the URLs I posted above this system of store and iterate is much faster in my situation than making multiple SQL calls to the remote server. Even on the development server local to the SQL Server it's much faster, because the machine can parse through strings much faster than it can make and return calls to the database. If you would like to see the code, I can forward it to you.

I removed most of the Access code to as it was just taking up space. The default barely resembles the original code.

Edited by ljamal