Thread: Getting The Most Out Of ASPDNSF

We started with a small web server running sql express 2005. Soon we saw the need to add more RAM. Afterwards we upgraded to sql 2008 web edition.

As the website grows our hardware has improved along side it.
At the moment our RAM is around 70-80% in use (4GB) this is with the database on the same server.

Our next step is to go dedicated with our own server.
However do we carry on how we are and keep throwing RAM at the server
- or -
Do we split the server into two with one hosting the website and the other with the database, the pro of this is we have more control over where we spend our money and monitoring will be a touch easier.

So the question is in your experience with ASPDNSF what choice is better? We are keen to split the server into two as we believe it will produce better speeds and expansion for the database however there is a cost implication in splitting the server.

Hi

As you'll no doubt have worked out, it all comes down to volumes: How many products are in the store and how many visitors are viewing and buying these products.

All of the larger sites we work with (usually from about 40,000 products up to over 150,000 products) have their database on an independent server. From memory, I think they all run the full SQL Server 2008 edition rather than Express but I don't have any evidence that the site runs quicker because of this.

Most of their web servers are IIS7 based now.

Regardless of hardware, once you get to these product volumes, all of these sites have benefitted from optimization, particularly:

- Tuning commonly used procedures, particularly ASPDNSF_GetProducts
- Writing custom product import and order export functions
- Running SQL Server explain plans on the db calls behind critical pages and tuning them
- Removing unused SQL and code from XmlPackages and making sure that EntityHelper is only used when absolutely necessary
- Removing the use of aspdnsf:GetMLValue() from XmlPackages if the site is single language

Adam

Thanks Adam,

The difference between express and other versions is express is limited to 1GB we have upgraded to web edition so we no longer have the 1GB RAM limitation.

I have been looking over the expensive queries and I am wondering where the best place to start is.

I am looking at ordering them by average duration so the queries that take the longest are optimised first. Or is there a better method for finding queries that are slowing us down?

Many thanks for your input. It looks like moving the database to its own server will help performance but you are totally right, we do need to slim some of our queries.

That's the right approach - use SQL Server Management Studio reports to identify the top transactions by age/duration and focus on these.

Unless your site is very different from others, the usual candidate is the aspdnsf_GetProducts() stored procedure because it's the engine room procedure of the whole site. Within this procedure, often the InventoryFilter Select Statements are the ones that take up time.

We often write a custom GetProducts() procedure tuned for each site and at the same time add sorting by Price, Show sale items first etc. As an example of tuning, if you don't track inventory, you could remove much of the inventory logic.

Also, we sometimes split search into a dedicated module using SQL Server, Web Services, JSON and JQuery (a lot of acronyms!) to take the load off the GetProducts() proc and allow dynamic searches such as with the Supermart.com site.

If want to optimize further, another technique is to write your own custom XSLT extensions for common tasks such as getting a variant price.

Also, ASP.Net's Ajax support is great but slower when compared to using JQuery Ajax. In JQuery, you can bypass the script manager layer completely and make calls to Web Services or Pages directly. An example would be your own custom Ajax minicart which is relatively straightforward to write for most ASPDNSF versions.

Adam

Sounds perfect.

Thanks a lot for your input Adam. Lets hope anyone else viewing this thread found your advice as useful as us.

Thanks again.