🔧 Maintenance Alert: World will be on Read-Only Mode on February 18th, 10:00 PM – 11:00 PM EST / 7:00 PM – 8:00 PM PST / 4:00 AM – 5:00 AM CET (Feb 19). Browsing available, but log-ins and submissions will be disabled.

Jonas Bergqvist
Aug 24, 2010
  5864
(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
The missing globe can finally be installed as a nuget package!

Do you feel like you're dying a little bit every time you need to click "Options" and then "View on Website"? Do you also miss the old "Globe" in...

Tomas Hensrud Gulla | Feb 14, 2025 | Syndicated blog

Cloudflare Edge Logs

Optimizely is introducing the ability to access Cloudflare's edge logs, which gives access to some information previously unavailable except throug...

Bob Davidson | Feb 14, 2025 | Syndicated blog

Comerce Connect calatog caching settings

A critical aspect of Commerce Connect is the caching mechanism for the product catalog, which enhances performance by reducing database load and...

K Khan | Feb 14, 2025

CMP DAM asset sync to Optimizely Graph self service

The CMP DAM integration in CMS introduced support for querying Optimizly Graph (EPiServer.Cms.WelcomeIntegration.Graph 2.0.0) for metadata such as...

Robert Svallin | Feb 13, 2025

PageCriteriaQueryService builder with Blazor and MudBlazor

This might be a stupid idea but my new years resolution was to do / test more stuff so here goes. This razor component allows users to build and...

Per Nergård (MVP) | Feb 10, 2025

Enhancing Optimizely CMS Multi-Site Architecture with Structured Isolation

The main challenge of building an Optimizely CMS website is to think about its multi site capabilities up front. Making adjustment after the fact c...

David Drouin-Prince | Feb 9, 2025 | Syndicated blog