2.80 Speed Issue - think I found the tipping point ...

had a 2.80 site running quite happily and responsively on shared hosting account - really no problems until now


Last night I added 1 more category ( to make a total of 78 )

and I added 72 products ( to make a total of 297 )


most of the new products were mapped to the same category  and a couple are mapped to 1 other category also.


site seems to have ground to a halt - 6, 7 even as much as 15 second load times now.

I had 2.80 site with millions of products and it worked fine (about 100 categories). I think it's better to profile SQL server in order to know why it's so slow (perhaps, some wrong configuration). Also please try the upcoming 3.00

I'd have half a mind on suspecting a change in the hosting setup that you may be unaware of, might be worth the time to restore a backup from before the extra products and see if the response time improves again, if you have the luxury of doing that without pi**ing off users of course :)

You could try re-indexing the tables and updating statistics on the database.

Make  Backup first though, it can only improve performance if anything.

USE MyDatabase
GO
CREATE PROCEDURE spUtil_ReIndexDatabase_UpdateStats
AS
DECLARE @MyTable VARCHAR(255)
DECLARE myCursor
CURSOR FOR
SELECT table_name
FROM information_schema.tables
WHERE table_type = 'base table'
OPEN myCursor
FETCH NEXT
FROM myCursor INTO @MyTable
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Reindexing Table:  ' + @MyTable
DBCC DBREINDEX(@MyTable, '', 80)
FETCH NEXT
FROM myCursor INTO @MyTable
END
CLOSE myCursor
DEALLOCATE myCursor
EXEC sp_updatestats
GO