Jonas Bergqvist
Aug 24, 2010
  5551
(1 votes)

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.

image

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.

image

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.

image 

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>
Aug 24, 2010

Comments

Magnus Rahl
Magnus Rahl Sep 21, 2010 10:33 AM

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.

Sep 21, 2010 10:33 AM

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.

Magnus Rahl
Magnus Rahl Sep 21, 2010 10:33 AM

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:

store.Items().Where(s => s.Property1 == "testValue").Select(s => new { s.Property2, s.Property3)?

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?

Sep 21, 2010 10:33 AM

Sorry, I meant anonymous types, my bad. That is exactly what you should do when only want to receive few properties.

Sep 21, 2010 10:33 AM

Very nice !!

Please login to comment.
Latest blogs
How to fix scheduled job 'Remove Abandoned BLOBs' if it keeps failing

Optimizely inlcudes a job named 'Remove Abandoned BLOBs'. This post will help you fix it if it's no longer working.

Henning Sjørbotten | Sep 26, 2023 | Syndicated blog

Optimizely Web Experimentation Metrics

Within the domain of Optimizely Web Experimentation Metrics, the emphasis is on objective key performance indicators (KPIs) selected to assess an...

Matthew Dunn | Sep 23, 2023 | Syndicated blog

Optimizely For you Intranet

Having been at Optimizely and in the CMS industry for nearly 16 years I have seen clients’ intranet requirements go from a simple site just to hous...

Robert Folan | Sep 22, 2023

Vulnerability in EPiServer.GoogleAnalytics v3 and v4

Introduction A potential security vulnerability was detected for Optimizely Google Analytics addon (including EPiServer.GoogleAnalytics and...

Bien Nguyen | Sep 20, 2023