I disagree with most of these.. 1) lookups will not go to the DB they should eventually in a busy system hit Entity framework cache so no big deal 2) This is normal with most software ..and its really up to you to create the proper indexes by tuning the DB.. thats a normal DBA job. 3) EF does Indentiy map caching behind the scenes
You can argue the whole catalog should be in memory and it is on some sites however there is a heavy cost in terms of locking and some users / business hate Caching .. Personally i only think caching is needed for larger sites which make the money anyway ..
BK> I disagree with most of these.. 1) lookups will not go to the DB they should eventually in a busy system hit Entity framework cache so no big deal 2) This is normal with most software ..and its really up to you to create the proper indexes by tuning the DB.. thats a normal DBA job. 3) EF does Indentiy map caching behind the scenes
You can argue the whole catalog should be in memory and it is on some sites however there is a heavy cost in terms of locking and some users / business hate Caching .. Personally i only think caching is needed for larger sites which make the money anyway ..
I have to disagree.
1) EF is slow. Stored procedures written by a db expert will be much faster. EF is quicker for a dev looking to knock up a site quickly but not so good for a product that will ship to thousands, as doesnt provide same performance. 2) indexes should be optimised in the db already, when it ships. This isn't a custom product. 3) read through the forums here. Constant problems with eg db queries with loops called over and ver again.
I have to agree with Sandman on this. I would also like to add that indexes should be added at the database design stage, this is definitely not a post implementation task. The database should ship as complete with only custom tuning necessary. As for EF it is inherently slow and should be avoided where performance is a priority. Don't get me wrong, EF has its place but just not in this kind of model when you start scaling it up as it will slow down. I think performance will always be a problem for people trying to use this when they are not using high performance kit. For people on a low budget non entity framework models will always scale better in terms of performance.
I have to agree with Sandman on this. I would also like to add that indexes should be added at the database design stage, this is definitely not a post implementation task. The database should ship as complete with only custom tuning necessary. As for EF it is inherently slow and should be avoided where performance is a priority. Don't get me wrong, EF has its place but just not in this kind of model when you start scaling it up as it will slow down. I think performance will always be a problem for people trying to use this when they are not using high performance kit. For people on a low budget non entity framework models will always scale better in terms of performance.
I also agree with your opinion that performance should be the top priority and the indexes should be included in the design stage rather than a post implementation.
While indexing, and all other db optimization is very important for operating on reasonable amounts of data, I think that it is not the main killer here. Problem with that particular product is not with db performance. I tested it on site with 3,000 records on two professional grade machines (web & DB servers), and it was very , very slow. After spending 20 years as SQL Server developer and DBA, I claim that indexing would not make any serious difference in performance, while making a simple product list query with couple of filters on that amount of data. With couple of thousands record to search, SQL optimizer in most cases would even ignore index seek, and would directly scan the the table. This is even more true in this db schema, where Product record is very small (blobs and other details are appropriately taken into different detail tables).
I also agree about indexes. That's why they are already added out of the box. Please let me know if we forgot to add some important ones.
Hey Andrei,
Here's an index for your consideration:
/****** Object: Index [IX_Category_Published_Deleted_ShowOnHomePage] ******/ CREATE NONCLUSTERED INDEX [IX_Category_Published_Deleted_ShowOnHomePage] ON [dbo].[Category] ( [Published] ASC, [Deleted] ASC, [ShowOnHomePage] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO
This ended up cutting 25 seconds off of our initial loading time after a restart, and double that with Mega Menu installed (since they hit the same method).
