SQL server not located where web site is 2

[charanch]

Hi all,

Company is moving site to a different host and moving SQL server in house to save $$ (no laughing please). I think there will be a slowdown in performance. Any thoughts of how to generally optimize performance in the site? I know this is an open ended question, but there is a lot we don't know and I am appealing to the vast and unpaid research department "out there". Thank you for your thoughts.

Charles Ranch

 

[Sheco]

You'll need to poke a hole in your in-house firewall to allow TCP/IP traffic to the SQL Server on port 1433... Check the SQL Server Books Online for more details on network issues.

As far as performance is concerned, you'll want to be sure that you don't send more data than you absolutely need. For example if you have a query with paged results (like google showing results 1-10, 11-20, and so on) then you'll want to use a stored procedure to preform the paging on the database rather than sending the entire recordset back and paging it within your ASP.

 

[damber]

A few ideas:

1. Optimise Network Routing between the Web Server and the SQL Server where you have access (static routes, direct connectivity, avoid proxies, limit the hops etc).
2. Use IP addresses rather than DNS names
3. Use Server Side Paging (SQL Server) as Sheco mentioned
4. Cache frequently read, infrequently written data onto the web server - in memory in the application object (or session) - but NOT as an open recordset object (threading issues) - (use getrows or disconnect the recordset) or static files. This works well with already filtered / ordered or formatted data (menus, dropdowns, footers, headers etc), but not as well where you need to do complex groupings, sorts, filters etc.
5. Use GetRows or disconnected recordsets where possible.
6. Close connections as soon as possible.
7. Use fast hardware for network media (dark optical fibre, fast routers / internet pipe etc) where possible and implement QoS to ensure the bandwidth is available for the web server and the sql server to communicate.
8. Minimise database calls (a pretty obvious one, but easily overlooked)
9. Use Cached XML Documents of your frequent data (as point 4) as these offer greater functionality and can be transformed relatively quickly with XSLT.. though XML is inherently bulky, so you would be shifting part of the issue from the network latency to the webserver performance.
10. If using direct recordsets rather than Getrows or disconnected recordsets and you need all the data in the resultset then set the CacheSize to a higher number (ideally total expected) - which will load more records at a time into the ADO object, reducing the overhead of separated network requests.
11. Select only the columns that you need - i.e. not SELECT * FROM....
12. Use Stored Procedures (again another obvious one)
13. Don't use recordsets where they are not needed (e.g. for an INSERT or UPDATE or DELETE)
14. Host your web server internally too....... ;o)

A lot of these can be beneficial where the SQL Server is local aswell.

Hope that helps.

A smile is worth a thousand kind words. So smile, it's easy!

 

[benniesanders]

That helps plenty. Many thanks to both of you!!