Ok thanks. When you say the forum, you mean the discussions on Codeplex?
Regards, *Kendall Bennett, CEO *A Main Hobbies 424 Otterson Drive, Suite 160 Chico, CA 95928 1-800-705-2215 (Toll-Free) 1-530-894-0797 (Int'l & Local) 1-530-894-9049 (Fax) *http://www.amainhobbies.com* *From:* Sebastien Ros [mailto:sebastien....@microsoft.com] *Sent:* Tuesday, July 12, 2011 2:41 PM *To:* orchard-discuss@lists.outercurve.org *Subject:* RE: Indexing on the http://aclj.org web site? Inline For further details you might want to open a discussion on the forum. Thanks Sebastien PS: thanks for the ‘e’ *From:* Kendall Bennett [mailto:kenda...@amainhobbies.com] *Sent:* Tuesday, July 12, 2011 2:32 PM *To:* orchard-discuss@lists.outercurve.org *Subject:* RE: Indexing on the http://aclj.org web site? Hi Sebastien, Ok, so none of the foreign keys are indexed at the moment on any of the tables? But the foreign key associations are set up in the database schema by NHibernate? I know that does not happen with MYISAM because it does not support foreign keys, but if I change to InnoDB it will. It is a lot easier to track down the potential indexes if you know what the foreign keys are J. The foreign key to the central Content Items table might be automatically generated as they are defined automatically by Orchard. As for Data Migrations, I am not entirely clear what you are talking about. Is that something in NHibernate, or something in the Orchard project? Forgive me as I am still rather new to ORM’s like NHibernate J. If would be nice to determine standard indexes that would be good for most sites and get that into the default install for Orchard, and then let people fine tune stuff themselves where more indexes may make sense. A Data Migration is a class which contains the code to create the tables/fields for a module, and subsequent updates each time a new version of the module is pulled. So any module can use it to create foreign keys also. Does NHibernate have any way for logging the generated SQL so we can analyse what is going on for specific pages where we might be seeing performance problems, so we can see if we can speed up the SQL queries at all? I know caching makes a big difference, but speeding up the generation of the caches can make a big difference to the startup speed of a large site also, such as when the application pool gets recycled. Yes. Open the config/log4net.config file, and add this section <logger name="NHibernate.Sql"> <priority value="DEBUG" /> <appender-ref ref="debugger"/> </logger> Regards, *Kendall Bennett, CEO *A Main Hobbies 424 Otterson Drive, Suite 160 Chico, CA 95928 1-800-705-2215 (Toll-Free) 1-530-894-0797 (Int'l & Local) 1-530-894-9049 (Fax) *http://www.amainhobbies.com* *From:* Sebastien Ros [mailto:sebastien....@microsoft.com] *Sent:* Tuesday, July 12, 2011 2:25 PM *To:* orchard-discuss@lists.outercurve.org *Subject:* RE: Indexing on the http://aclj.org web site? Hi, About Indices, in current Orchard’s state, primary keys are defined, and thus Sql Server is creating indices automatically. However this is not the case for foreign keys. This is a supported scenario in Data Migrations, so we could imagine someone create a module which would create those foreign keys for you. Or you can have you own scripts with ‘optimized’ indexes, based on the effective query plan your DBA has analyzed. Sebastien *From:* Kendall Bennett [mailto:kenda...@amainhobbies.com] *Sent:* Tuesday, July 12, 2011 2:21 PM *To:* orchard-discuss@lists.outercurve.org *Cc:* orchard-discuss@lists.outercurve.org *Subject:* RE: Indexing on the http://aclj.org web site? Hi Chris, I will take a look at Sebastian’s caching module and see if it will work with our site. As far as indexes, it occurred to me that if you are using SQL Server for your database engine, NHibernate is probably setting up all the relationships and foreign keys in the database schema when it creates the database. Do you know if that then means that all the foreign key associations automatically get indexes on them, like primary keys do, or does SQL Server assume the DBA will assign indexes on foreign keys as necessary? I would assume it might create the indexes automatically like it would on a primary key, because joining two tables on foreign keys is going to be rather slow if the foreign key is not indexed? In my case we are using MySQL and the database tables are set up as MYISAM (probably only because that is the default we have set on our MySQL server). I need to see if NHibernate will set up all the foreign key associations in the database if I change the default to InnoDB tables (the normal MySQL default) and then see if indexes are creates as appropriate. Either way I think I need to find a way to force InnoDB tables to be used with MySQL on our install since it should work better with NHibernate I would think? And yes, the new site we are working on is for our team blog site, which is something we have been working on for a while. Hopefully we can get it done soon J. It is totally cool to learn you are in Redding! Next time you are down this way please let me know as I would love to get together and chat about Orchard development. I don’t get up to Redding very often, as usually when I do it is on the weekends and we are racing R/C cars J. Regards, *Kendall Bennett, CEO *A Main Hobbies 424 Otterson Drive, Suite 160 Chico, CA 95928 1-800-705-2215 (Toll-Free) 1-530-894-0797 (Int'l & Local) 1-530-894-9049 (Fax) *http://www.amainhobbies.com* *From:* Chris Bower [mailto:digita...@gmail.com] *Sent:* Saturday, July 09, 2011 1:12 PM *To:* Kendall Bennett; orchard-discuss@lists.outercurve.org *Subject:* Re: Indexing on the http://aclj.org web site? Kendall, The majority of the work I did was around shape caching, but Sebastian Ros has released a very nice caching module that should work very well for most Orchard installations that caches at a different level. His solution doesn't work out of the box for us because we have a secondary membership system (on top of the Orchard member tables, we have our own member record table – done this way because we have a few million members and it wouldn't be wise to make them part of the content item system). If Sebastian's module doesn't work for you, I'd be happy to show you what we did in more detail. As far as indexes, we didn't have to add any on the core Orchard tables. I've found the content queries to be surprisingly fast, and not the source of any bottlenecks we've encountered, especially with sane caching in place. Almost all of our bottlenecks have been with database locking, which we've slowly whittled away to the point where I feel pretty comfortable now. So is the site you're getting ready to build for your hobby store? I just got in to flying CP heli's, and live in Redding. Maybe I'll have to come visit sometime and we can talk Orchard and helicopters. :) Chris *From: *Kendall Bennett <kenda...@amainhobbies.com> *Date: *Sat, 9 Jul 2011 10:58:50 -0700 *To: *<orchard-discuss@lists.outercurve.org> *Cc: *Chris Bower <digita...@gmail.com> *Subject: *Indexing on the http://aclj.org web site? Hi Chris, I read with interest about your new site running on Orchard. I have been involved in Orchard since 1.0, but we have not made a final decision on whether we are going to use the project or not for our site. The company we have doing the design work is just about ready to start doing the work, so we need to decide if we are going to have them do it with SiteFinity, or go with Orchard. I am curious that you mentioned you did some stuff to your site to improve the performance over the stock v1.2 install. Has any of that been released for us with other sites? Also, I am curious if you found the need to add indexes to any of the database tables for your site, since out of the box Orchard does not build indexes for the tables other than those uses for the primary keys. So I am curious as your site got bigger, if you found the need to add indexes to foreign keys to improve performance at all, and if you did, was that something you added in code or did you just manually modify your database tables? Regards, *Kendall Bennett, CEO *A Main Hobbies 424 Otterson Drive, Suite 160 Chico, CA 95928 1-800-705-2215 (Toll-Free) 1-530-894-0797 (Int'l & Local) 1-530-894-9049 (Fax) *http://www.amainhobbies.com* --- You are currently subscribed to orchard-discuss as: sebastien....@microsoft.com. To unsubscribe send a blank email to leave-orchard-disc...@lists.outercurve.org. --- You are currently subscribed to orchard-discuss as: kenda...@amainhobbies.com. To unsubscribe send a blank email to leave-orchard-disc...@lists.outercurve.org. --- You are currently subscribed to orchard-discuss as: sebastien....@microsoft.com. To unsubscribe send a blank email to leave-orchard-disc...@lists.outercurve.org. --- You are currently subscribed to orchard-discuss as: kenda...@amainhobbies.com. To unsubscribe send a blank email to leave-orchard-disc...@lists.outercurve.org. --- You are currently subscribed to orchard-discuss as: arch...@mail-archive.com. To unsubscribe send a blank email to leave-orchard-disc...@lists.outercurve.org.
