I'm pretty new to Umbraco - hoping this answer is obvious :-)
I'm using Umbraco as an extension of another eCommerce system. I have product data stored in Umbraco with around 40 properties per "product" document type. And I have around 12,000 products stored in Umbraco at the moment. Front end access via Nodes in the xml file is not an issue.
Accessing the items in the Umbraco backend via Documents is where I am running into issues. I want to display a filterable/sortable grid of all the Documents (not Nodes since it is in the backend). Since I have 12,000 items, doing something as simple as "show me all products with a price < $10 and starts with the letter a" will take > 10 seconds to come back due to all the data access that needs to happen under the covers.
So I then took the approach of "direct database access", essentially pulling back the latest properties from the cmsPropertyData table for each row for each column I wanted to display. This worked great for up to 2000 items as the SQL query would take <2 seconds to gather the data. However, I now have 12000 items that I want to filter on and ther query takes > 10 seconds via SQL (have not tried via Document list) due to so many sub queries being present.
Anyone have an approach they have used to access many Documents in the back end? Is there an equivalent to the xml file that Nodes use that would get me faster access (I only need read access)?
I've solved this issue - the numerous approaches I used were:
1.use List to populate the sortable/filterable grid - waaaay to slow
2.Looked at using Preview XML (remember I need document data, not Node data) but no easy way to get the entire tree - seeemed to be single node based?
3.use an Examine Index to populate the grid - display of grid was very fast, but when you changed many items at once (grid has s bulk update feature too) the items inthe grid would be out of date while the index updated - this was even after using the ReIndexNode feature of Examine
4.write a insert/update trigger on cmsDocument that looks for appropriate changes and populates a "hard coded" sql table that matches the fields in the grid
#4 ended up working out great and wasnt too difficult to implement - in short I:
- created a sql table that had NodeId and then all the field names that I needed - this is essentially a "flattened version" of a document Type where you have all the properties in separate columns for easier sorting/filtering
- created a update/insert trigger on the cmsDocument table that looked to see if the document type I use in my grid was updated - if so, then go grab all the properties from the deeper tables (cmsPropertyData, etc) and populate/update the sql table I had created
The result is a lightening fast grid of 12000 eCommerce products that are all sortable and filterable via freetext or dropdowns where appropriate. This saves my client a lot of time of trying to find items via teh Content tree or via teh "upper left search" functionality of the cms.
Hope this helps someone out as an approach. Any ideas on whether a different approach (with less or no custom code/sql) will be possible in v5? I love the speed at which I can modify a document type to add a field without having to touch sql, but as you can see above I really missed the tabular/column based resultset that is hard to get to with things being stored as essentially "name/value" pairs.
Showing 12,000 documents in a sortable/filterable grid
I'm pretty new to Umbraco - hoping this answer is obvious :-)
I'm using Umbraco as an extension of another eCommerce system. I have product data stored in Umbraco with around 40 properties per "product" document type. And I have around 12,000 products stored in Umbraco at the moment. Front end access via Nodes in the xml file is not an issue.
Accessing the items in the Umbraco backend via Documents is where I am running into issues. I want to display a filterable/sortable grid of all the Documents (not Nodes since it is in the backend). Since I have 12,000 items, doing something as simple as "show me all products with a price < $10 and starts with the letter a" will take > 10 seconds to come back due to all the data access that needs to happen under the covers.
So I then took the approach of "direct database access", essentially pulling back the latest properties from the cmsPropertyData table for each row for each column I wanted to display. This worked great for up to 2000 items as the SQL query would take <2 seconds to gather the data. However, I now have 12000 items that I want to filter on and ther query takes > 10 seconds via SQL (have not tried via Document list) due to so many sub queries being present.
Anyone have an approach they have used to access many Documents in the back end? Is there an equivalent to the xml file that Nodes use that would get me faster access (I only need read access)?
Thanks,
Matt
I've solved this issue - the numerous approaches I used were:
#4 ended up working out great and wasnt too difficult to implement - in short I:
The result is a lightening fast grid of 12000 eCommerce products that are all sortable and filterable via freetext or dropdowns where appropriate. This saves my client a lot of time of trying to find items via teh Content tree or via teh "upper left search" functionality of the cms.
Hope this helps someone out as an approach. Any ideas on whether a different approach (with less or no custom code/sql) will be possible in v5? I love the speed at which I can modify a document type to add a field without having to touch sql, but as you can see above I really missed the tabular/column based resultset that is hard to get to with things being stored as essentially "name/value" pairs.
Regards,
Matt
is working on a reply...