Version 9 - Database Changes

Oh.. this is important to know.. Thanks for sharing, Charles, and good luck !

Juan

Yes many of the default views were removed which helped with performance of the database. Before going in and recreating them, I'd suggest looking into the documentation with version 9 and seeing what the replacements are so you won't have additional work now and later on.

You might check the code upgrade tool as well as KInspector as additional tools to help with your upgrade.

The code upgrade tool is worthless when it comes to the views and such. I ran it for my custom controls and it identified two of them which I resolved. As you know Brenden, I have over 1 million pages in my system. These are spread across 420 or so custom page types. I am half way through fixing what no longer works. It is more work than I intended at the moment.

Also, views are much faster for me than, DataQuery, Entities and Direct SQL with multiple joins. I would rather run on views that are loaded after first hit. At least in my scenario.

I understand you have a lot of pages and page types. What I'm stating is you might look into a supported version or change in order to get what you want accomplished faster and not cause problems later on. If you're creating views in the database directly, there's a good chance your're going to end up creating a breaking change to your instance which will not be supported. I'd suggest contacting Kentico directly for their recommendation.

Yes, I understand you're almost half way through fixing it but it was documented and with a site that has that much content, I wouldn't have upgraded it do a vanilla major version of any CMS. I would have waited until a few hotfixes were out, ensure you have all issues documented with the upgrade and your instance planned for upgrade and ensure you have a good testing plan in place.

For future reference here are the release notes for Kentico 9. Check out the database section, it has all the changes to the database as well as the views you're referencing. The very first bullet point states "All page related views and the View_COM_SKU views were removed with the exception of View_CMS_Tree_Joined, which is used as the base for querying of page data".

A solution might be to create a custom query in the page type and include the page type class and FK value like below. Then use the ObjectQuery to call it and pass your values to it. I do this will all my queries, even queries to external sources. This way the can be modified within the UI and the database remains untouched.

SELECT ##TOPN## ##COLUMNS##
FROM View_CMS_Tree_Joined
    INNER JOIN Custom_Slider ON DocumentForeignKeyValue = SliderID AND ClassName = 'custom.slider'
WHERE ##WHERE##
ORDER BY ##ORDERBY##

Good luck with the rest of your upgrade!

Hi Charles

I would just like to point out that custom Page types are working normally after upgrade, what we changed was that we removed page related views as was already mentioned. This is one of the reasons why we encourage our customers to use DocumentQuery API – you don’t have to worry about changes in database architecture. Of course if you have written your custom queries using these views, they will not work after upgrade.

There shouldn’t be any performance impact either, since Document query can be parametrized to retrieve just the data you really need. You don’t have to work with TreeNode either, you can work with results as a DataSet data type.

Anyway, we are not exactly sure what your setup is and how you created and used your joined views. If you could provide us with some more details, we could suggest how to remedy the problem and how to avoid situations like these in the future. You can shoot us an email to support@kentico.com if you have any questions or more details.

The upgrade was completed in the middle of last night and everything is working now correctly. I do appreciate the answers. I have a lot of grids that the end user can filter and query data in. Some of the grids have 50,000 + records in them, and they work extremely fast. The website is more of a web application. I use Devexpress controls and grid liberally through out. I also create a lot of grids on the fly, so they were running against the page type joined views, which was convenient and extremely fast. Document query API is not a good candidate for this scenario. I have written a mild abstraction layer just for my grids now. I need the grids to populate fast, 782ms for 50,000 records into a paging grid is what I am getting now and is fine. Additionally, I ported the whole site onto Azure yesterday which is giving me faster response times as well. Version 9, loads a lot faster as well in general. Thanks for the input.

Great to hear your success after somewhat of a large hurdle. I too agree v9 is loading much faster and enjoy this enhancement as others will too!