Dynamic data store Search/Performance Gadget
Have you ever wanted to search for data in the dynamic data store (DDS) by writing plain text in admin mode, or have you ever wondered how the SQL looks like when querying for data through “LINQ to DDS”? A new gadget makes it possible to both search in the DDS, and look at the generated SQL.
Search by LINQ
Cristian Libardo has earlier created a great gadget for browsing in the DDS. I have now created another gadget, that makes it possible to write LINQ-queries in a text area, and then execute the query on the server.
When a user writes a query and runs it in the new gadget, the result will be sent back to the client together with the generated SQL-query. During execution, a stop watch will be used to indicate how long it takes to generate the SQL-query, and how long time it takes to execute the whole LINQ-query and receive the result.
With this in mind, the gadget can be used for several purposes. I have started to use the gadget myself to look at the generated SQL-queries (a lot simpler than using the SQL profiler) and how long it takes to generate the SQL-query. Others may find it more useful for it’s search functionality.
The gadget supports both property bag queries, and strongly typed queries.
Install the gadget
Download the zip file and copy the module to the site you want to use the gadget in. Then copy the two (2) dll:s in the bin folder to the bin-folder of the site.
At last, the gadget in the web.config under poblicModules:
<publicModules rootPath="~/modules/" autoDiscovery="Minimal"><add name="EPiServer.CodeSamples.DdsSearch.Gadget"><assemblies><add assembly="EPiServer.CodeSamples.DdsSearch.Gadget"/></assemblies></add></publicModules>
Very neat! Have you gotten any feel for how the DDS reacts when it's clogged with many objects of the same type, objects of many types etc? We're contemplating an implementation which will result in hundreds of thousands of objects, each with the potential of being rather large (at least larger than example classes for comments containing a few properties with very little data in each). The items would be randomly accessed more or less frequently.
We have earlier made some tests that showed good result when we had several million objects in a store. I do not have any figures right now, but I have some advice when having many objects in a store that is queried frequently.
1. Make sure to index properties. This gadget can hopefully help you to find out what properties that needs to be indexed.
2. As you can see from the SQL, we lazy loading by default. If you're only interested in some of the properties, use "select" to only receive the values that you need. LINQ-queries with anonym objects will load in its objects directly.
If you run a query twice you will also see that the second result will be received a lot faster than the first time. This is because we cache the objects received.
I'm not sure I understand what you mean about the select vs anonymous objects part. If I have SomeClass with many properties, but just want to select on Property1 to get Property2 and Property 3 for example, would something like this be the best choice:
().Where(s => s.Property1 == "testValue").Select(s => new { s.Property2, s.Property3)?
store.Items
I mean rather than stopping at the Where() and then (in a loop or whatever) read only some properties?
Or are anonymous types bad too, so it would be better to do two separate queries to select Property2 and Property3?
Sorry, I meant anonymous types, my bad. That is exactly what you should do when only want to receive few properties.
Very nice !!