Hosting on Remote SQLServer?
Printed From: Web Wiz Forums
Category: Web Wiz Web App Support Forums
Forum Name: Web Wiz Forums
Forum Description: Support forum for Web Wiz Forums application.
URL: https://forums.webwiz.net/forum_posts.asp?TID=2704
Printed Date: 01 April 2026 at 1:00am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com
Topic: Hosting on Remote SQLServer?
Posted By: ljamal
Subject: Hosting on Remote SQLServer?
Date Posted: 14 May 2003 at 12:24pm
|
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 - http://69.26.135.138/msg Production Server http://www.longbox.com/msg/ - 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? |
Replies:
Posted By: ljamal
Date Posted: 14 May 2003 at 1:10pm
|
It appears that the multiple connections to the remote database is the problem. I've rewritten the default page to use one stored procedure call to access the Cats and Forums from one view and it has cut out 3 calls to the database and reduced the time from 19+ seconds down to just under 4 seconds.
As this will probably be the only message board I use for a while it looks as though I will have to modify it even more to fit my needs. It still a great product, but I have highly customized needs and it looks like that optimizing the code for my SQL setup will supercede reducing the forum cookies for the time being. |
Posted By: ljamal
Date Posted: 14 May 2003 at 10:26pm
|
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 - http://www.longbox.com/msg/default-pre.asp and the page with the optimizations http://www.longbox.com/msg/ - http://www.longbox.com/msg/ |
Posted By: WebWiz-Bruce
Date Posted: 15 May 2003 at 7:42am
|
Whats the new stored procedure you have created to cut down the calls to the SQL server? ------------- https://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting https://www.webwiz.net/web-hosting/windows-web-hosting.htm" rel="nofollow - ASP.NET Web Hosting |
Posted By: ljamal
Date Posted: 15 May 2003 at 8:10am
|
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. |
Posted By: Bunce
Date Posted: 15 May 2003 at 3:47pm
|
Getstring(), Getrows(), Stored Procs, Multiple RS'. You've made my day!! ------------- There have been many, many posts made throughout the world... This was one of them. |
Posted By: ljamal
Date Posted: 15 May 2003 at 3:51pm
I removed most of the Access code to as it was just taking up space. The default barely resembles the original code. |
Posted By: boyohboy
Date Posted: 15 May 2003 at 6:47pm
|
http://www.longbox.com/msg/forum_topics.asp?FID=21 - http://www.longbox.com/msg/forum_topics.asp?FID=21 this page took 10 sec to load. what's up with your prod. server? the dev server isn't that bad. ------------- Visit my community website @ http://www.everythingviet.com - EverythingVIET.com |
Posted By: ljamal
Date Posted: 15 May 2003 at 8:14pm
|
There is nothing wrong with the production server. The forum makes at least 10 calls to the database and when the database and the server are not on the same network each call to and response from the SQL server adds additional time. When trying to find the problem in the time difference, I added a timer after each SQL call and for the configuration I currently use each call added an additional second to the rendering time of the page.
The default page itself as written make one call for the categories, one call for all the forums in each of the categories, one call for per forum for moderator groups, one call per forum for moderator users, one call per forum for last post, one call per forum for the forum permissions. If you have one forum that 6 DB calls. If you have 3 categories with 3 forums each, there is 1 call for the categories, 3 calls for the forums in the categories, 9 calls for mod groups, 9 calls for the mod user, 9 calls for the persmissions for a total of 31 calls to the database. Rewritten there is one call to the database which returns the categories, forums, mod group and mod user plus one call for each of the forums permissions. This reduces the 31 calls previously down to 10, thus reducing calls to the database by 1/3 and the overall speed by over 15-20 seconds. I haven't begun work on the other pages, but I'm sure that they will see similar changes and speed reductions. Keep in mind this won't affect most people because most have the SQL Server and web server on the same network. I have my SQL server local to me (and remote to the web server) because doing so allows me to optimize the SQL Server and its databases and the net affect is usually only .2-.5 second difference in speed for data access. However that multiplied by 30 is 6-15 seconds which is what makes the difference here. |