I'd like to know how to get a product back along with say 10 custom properties BUT in a single SQL query.
Annoyingly, uCommerce API is like the Umbraco member API and every time you request a property on a product is a separate SQL DB hit. Which really is really not very efficient, especially with the couple of scenarios below...
Scenario 1
I'd like to get a single product back, along with 10 (or more) custom properties in one underlying sql query so I can map them onto my custom class. NOT individual db hits per custom product.
Scenario 2
I'd like to return a list of mapped products back including the 10 properties, by a value of one of the customer properties on the product in a single SQL query. So I can return a List in a method.
So like Matt's example, lets say for example purposed I had 20,000 products all with a custom property called 'slug' and 50 of those have a value in 'slug' of 'something'. I'd like to return all products, where slug has a value of 'something' along with all their other custom properties so I can map them into my object.
At present the above is just not doable, as it creates an insane amount of SQL queries!! Where is really only needs to be a single one.
If anyone can shed some light on the above two scenarios it would be HIGHLY appreciated and offer any code samples?
As at present, after months of work using uCommerce it's coming to light that unless we can get the above resolved. Then we are going to have to drop it as it's not man enough for large commercial eCommerce solutions.
By default uCommerce (and NHibernate) will give you whatever information you require. If the requirements are not specified up front NHibernate will employ lazy loading to provide you with the result. While this will give you the desired information it will not be efficient because the nature of lazy loading is to only load when the information is required, which will cause a lot of chit-chat with the database.
The uCommerce query API is built on the ORM NHibernate, which is running on hundreds of thousands of sites and is considered the industry standard for doing this type of work. It comes with a very high degree of flexiblity, but also requires a bit of knowledge to make sure queries are done efficiently.
Scenario 1
To solve scenario 1 you'll have to specify up front what you need later on:
var product = Product.All().Where(x => x.Id == 123).FetchMany(x => x.ProductProperties).Single();
This will give you exactly one query to the database and the product object initialized with properties.
You can use multiple FetchMany statements and ThenFetchMany statements in concert if you need more information initialized up front.
Also there is a Fetch() method available for fetching 1-1 relationships.
Scenario 2
Scneario 2 can also be done using a single query.
To solve scenario 2 you can either opt to initialize or not initialize the properties depending on whether you need the information or not.
Let me first show you without pre fetching anything:
This will cause exactly one query to be executed in the database and will bring back all products with a property named "Slug" on it set to a value of "Whatever".
If you need to also display some of the custom properties on the site you'll want to combine the WHERE clause with the FetchMany() statement from before like so:
I know querying is something that trips up many devs because the way ORMs work don't land you in the "pit of success". The ORM is the ultimate leaky abstraction because you are required to think about the underlying data store.
uCommerce 3.6 and up will cache everything by default, rather than requiring the developer to specify what should be cache so this will to some extent allieviate the performance issues when queries are not optimized.
There is a technology available that fixes this and it's called a document database. The difference between a relation database and document database is fundamentally that documents embed relation information rather than putting it somewhere else, e.g. orders and order lines are stored in one document and always brought back together.
This fundamental difference means that developers are more likely to fall into the "pit of success" becuase it more closely resembles the way we think about data.
At uCommerce we're experimenting with this technology today and we are in fact already shipping it as part of uCommerce 4 in the form of our search engine. The cool thing about RavenDB, the document database which lies at the heart of our search engine, is that is completely does away with the requirement to specify up front the information you require because products, orders, and other root aggregates contain all the information about the object more closely resembling the way devs tend to think about data.
The Future
We have only just shipped RavenDB as part of uCommerce and we need get some more real-world metrics before we do even more with it. Suffice it to say that I have high expectation for the future use of it. Where I'd like to go is to use RavenDB as a fast read cache for documents rather than going to the database.
I am aware that many IT departments aren't ready to ditch SQL Server for something like RavenDB, but with the current incarnation of uCommerce we're trying out something new to try and solve this problem that the industry as a whole is bogged down by.
As per the bottom of my little no sleep rant in the previous, thread. Fetch() & FetchMany() no longer work?
Fetch() AND FetchMany() DO NOT work now. I have managed to get v4 running. But your query example and in fact any query that tries to use Fetch() or FetchMany() just returns the following error.
Server Error in '/' Application.
Specified method is not supported.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.NotSupportedException: Specified method is not supported.
The only way to make it run is to remove any reference to Fetch() or FetchMany() from all my queries?
Just to close this and in case anyone else is looking to do the same. You will need to use HQL to get the Product and Category with all associated properties and use them, in an efficient query.
Soren is sending me an example, and we'll have a play with it. Once I have it working I'll put something here for an example of how to use it.
Query Products/Categories By Custom Properties Efficiently??
I'd like to know how to get a product back along with say 10 custom properties BUT in a single SQL query.
Annoyingly, uCommerce API is like the Umbraco member API and every time you request a property on a product is a separate SQL DB hit. Which really is really not very efficient, especially with the couple of scenarios below...
Scenario 1
I'd like to get a single product back, along with 10 (or more) custom properties in one underlying sql query so I can map them onto my custom class. NOT individual db hits per custom product.
Scenario 2
I'd like to return a list of mapped products back including the 10 properties, by a value of one of the customer properties on the product in a single SQL query. So I can return a List in a method.
So like Matt's example, lets say for example purposed I had 20,000 products all with a custom property called 'slug' and 50 of those have a value in 'slug' of 'something'. I'd like to return all products, where slug has a value of 'something' along with all their other custom properties so I can map them into my object.
At present the above is just not doable, as it creates an insane amount of SQL queries!! Where is really only needs to be a single one.
If anyone can shed some light on the above two scenarios it would be HIGHLY appreciated and offer any code samples?
As at present, after months of work using uCommerce it's coming to light that unless we can get the above resolved. Then we are going to have to drop it as it's not man enough for large commercial eCommerce solutions.
Hi Lee,
By default uCommerce (and NHibernate) will give you whatever information you require. If the requirements are not specified up front NHibernate will employ lazy loading to provide you with the result. While this will give you the desired information it will not be efficient because the nature of lazy loading is to only load when the information is required, which will cause a lot of chit-chat with the database.
The uCommerce query API is built on the ORM NHibernate, which is running on hundreds of thousands of sites and is considered the industry standard for doing this type of work. It comes with a very high degree of flexiblity, but also requires a bit of knowledge to make sure queries are done efficiently.
Scenario 1
To solve scenario 1 you'll have to specify up front what you need later on:
This will give you exactly one query to the database and the product object initialized with properties.
You can use multiple FetchMany statements and ThenFetchMany statements in concert if you need more information initialized up front.
Also there is a Fetch() method available for fetching 1-1 relationships.
Scenario 2
Scneario 2 can also be done using a single query.
To solve scenario 2 you can either opt to initialize or not initialize the properties depending on whether you need the information or not.
Let me first show you without pre fetching anything:
This will cause exactly one query to be executed in the database and will bring back all products with a property named "Slug" on it set to a value of "Whatever".
If you need to also display some of the custom properties on the site you'll want to combine the WHERE clause with the FetchMany() statement from before like so:
I hope this sheds some light on how this works.
State of the Nation
I know querying is something that trips up many devs because the way ORMs work don't land you in the "pit of success". The ORM is the ultimate leaky abstraction because you are required to think about the underlying data store.
uCommerce 3.6 and up will cache everything by default, rather than requiring the developer to specify what should be cache so this will to some extent allieviate the performance issues when queries are not optimized.
There is a technology available that fixes this and it's called a document database. The difference between a relation database and document database is fundamentally that documents embed relation information rather than putting it somewhere else, e.g. orders and order lines are stored in one document and always brought back together.
This fundamental difference means that developers are more likely to fall into the "pit of success" becuase it more closely resembles the way we think about data.
At uCommerce we're experimenting with this technology today and we are in fact already shipping it as part of uCommerce 4 in the form of our search engine. The cool thing about RavenDB, the document database which lies at the heart of our search engine, is that is completely does away with the requirement to specify up front the information you require because products, orders, and other root aggregates contain all the information about the object more closely resembling the way devs tend to think about data.
The Future
We have only just shipped RavenDB as part of uCommerce and we need get some more real-world metrics before we do even more with it. Suffice it to say that I have high expectation for the future use of it. Where I'd like to go is to use RavenDB as a fast read cache for documents rather than going to the database.
I am aware that many IT departments aren't ready to ditch SQL Server for something like RavenDB, but with the current incarnation of uCommerce we're trying out something new to try and solve this problem that the industry as a whole is bogged down by.
As per the bottom of my little no sleep rant in the previous, thread. Fetch() & FetchMany() no longer work?
Fetch() AND FetchMany() DO NOT work now. I have managed to get v4 running. But your query example and in fact any query that tries to use Fetch() or FetchMany() just returns the following error.
Are you on 3.6 or 4 at this point?
I am now using v4 .. But had the same error with v3.6 ...? Also that key didn't work. Just sent an email.
Just to close this and in case anyone else is looking to do the same. You will need to use HQL to get the Product and Category with all associated properties and use them, in an efficient query.
Soren is sending me an example, and we'll have a play with it. Once I have it working I'll put something here for an example of how to use it.
is working on a reply...