Forum Moderators: open
2 questions:
a) What approaches bring the highest performance?
b) What kind of maintenance is required to keep that performance going (eg with Access we had to regularly compact databases)?
Very grateful for your tip!
Cheers, Jgar
a lot of times, people underestimate the capabilities of Access and upgrade to SQL server before its needed.
If there aren't a lot of visitors to your site or the db is small, that may be why you don't notice that big of a difference in performance
Access was crashing - big databases, many visitors, heavy use of databases. We optimised as much as we could (closing connections etc). SQL Server is certainly more stable.
I guess the question is, what do we need to do to optimize, in terms of setting it up, and then servicing it?
They're horrible. Really.
Query optimization is the number one thing you can do to improve SQL performance. We're constantly tweaking queries around here.
But the biggest optimization is when u make smart use of caching! That not really much to do with SQL Server, but when people request the same page with the same heavy query over and over again (for like 2000 times a day) caching brings it down to 1 query....talk about improvement.
make use of indexes on table
Very true. But make sure not to get index everything disease (IED). Whenever you update an indexed column, SQL Server goes through and reindexes EVERYTHING. If you have too much going on, it can impact SQL Server performanace AND system performanace.
Google for the SHOWPLAN command and MSSQL. SHOWPLAN is useful for simulation/testing and seeing where you really do need indexes and where you don't.
caching brings it down to 1 query
Another brilliant tip. This is SUPER useful for things like forums that don't change often, or account stats that only need to be updated once a day.
hire a good dba :)
seriously, not everyone is good at everything
Caching - sorry if I'm new to this - how is the best way to cache?
Getrows - we can implement this quite quickly
Indexes and Stored procedures - I'm going to look into these further
Caching example: say you have a top 10 most sold products list that doesn't change very often. Instead of pulling those 10 products from the database every time someone visits that page, you run the query only when you update the toplist (or maybe once a week/day/hour), store the results in a file which in turn you include and use to build the top 10 list on your webpage.
Another example could be storing the data in an Application variable... but ... this isn't very good for large chunks of data (since the server stores it in ram) - I mostly use it for the menubars I store in the db.
Hope this helped.
//ZS
This was very quick to do and, lo-and-behold, server performance has increased quite significantly. This was obviously the bottleneck to attack first. These were, incidentally, pages that were being spidered very regularly.
Caching, which will probably take a little longer to implement, will be our next consideration.
ONE QUESTION THOUGH REMAINS:
What about SQL Server Maintenance? Is there anything we should be doing on a regular basis to the data?
Cheers, Jgar
