Filter number of fields returned from SOLR

Question

We have a query that is running against our SOLR. The query is returning extremely slowly due to the number of fields and the amount of data in those fields. When the query runs, its adding the fl=* to the request, which will return all fields from SOLR.

We cannot use the built in paging due to language fallback and the way that SOLR handles these items.

Using the Sitecore ContentSearch library (or through configuration), is there a way to limit the fields that are coming back from SOLR?

In its simplest form we are doing the following

using (var searchContext = _searchIndexSelector.CreateSearchContext())
{
   var queryable = searchContext.GetQueryable<T>();
   var query = queryFunc(queryable);
   var searchResults = query.GetResults();
}

It generates a query for solr that looks like this

?q=(((((_template:(04c7dc91813244a1820f0e36cf6b6553)%20OR%20_template:(f613d8a8d9324b5f84516424f89c9102)%20OR%20_template:(6d28679694434b49bb35ebdbb23461d1)%20OR%20_template:(bf6c17c5c146494286daaf8d621a7efa)%20OR%20_template:(358398e232654b1d9431485523f722c9))%20AND%20search_type_sm:(aa2d063528504b9bb425c480cd76b739))%20AND%20-_name:("__Standard%20Values"))%20AND%20_path:(1f6ceccf452943f5b6c20be96502e6a7))))&rows=2147483647&fl=*,score&fq=_indexname:(sitecore_web_index)&facet=true&facet.field=facetField1&f.facetField2.facet.mincount=0&facet.field=facetfField3&f.facetField4.facet.mincount=0&sort=SortField1%20desc

@MarekMusielak Thank you! I haven't been known about it. I need test it. This article can be helpful for me in the future. — Artem Prashkovich, Commented Dec 19, 2017 at 20:39
Interesting article. It looks like that is build on 8.2. Unfortunately my solution is built on 8.1 update 3. The GetQueryable requires a SiteSearchResultItem. Is this no longer a requirement in 8.2? — John Kurtis, Commented Dec 19, 2017 at 21:38

John Kurtis · Accepted Answer · 2017-12-20 14:13:28Z

8

It appears that the post by Marek Musielak will work. Adding a .Select to the query DOES change how the fl parameter is built. So the solution outlined in the post http://bartlomiejmucha.com/en/blog/search-query-performance-from-800ms-to-70ms/ does apply to Sitecore 8.1 as well

answered Dec 20, 2017 at 14:13

John Kurtis

3312 silver badges9 bronze badges

Add a comment |

Artem Prashkovich · Accepted Answer · 2017-12-19 19:52:32Z

Currently, you can't limit the fields because you Sitecore doesn't provide an ability to manage the fl parameter of query to SOLR.

You haven't mentioned if you use custom index or a default sitecore index (like "sitecore-web-index").

Anyway, in case you have a lot of fields and the amount of data in those fields, the best approach the tune a perfomance is to create your oun index with custom configuration.

Then, in the custom configuration, set false value for the indexAllFields property:

<indexAllFields>false</indexAllFields>

The next step is to include just necessery fileds. For example:

<include hint="list:AddIncludedField">
    <fieldId>{8CDC337E-A112-42FB-BBB4-4143751E123F}</fieldId>
</include>

Also you can set included/excluded templates that should be included/excluded in index (more precisely, items that are based on them):

<exclude hint="list:AddExcludedTemplate">
    <BucketFolderTemplateId>{ADB6CA4F-03EF-4F47-B9AC-9CE2BA53FF97}</BucketFolderTemplateId>
    <!-- And etc -->
</exclude>

<include hint="list:AddIncludedTemplate">
     <ContentPage>{04AA33EA-4677-488F-A39A-BDDAE504250E}</ContentPage>
     <HomePage>{23FE027D-1375-4D8C-A2FB-B70276F43C71}</HomePage>
     <!-- And etc -->          
</include>

Also you can define a root path for crowler which will also limit a number of items in the index:

<locations hint="list:AddCrawler">
    <crawler type="Sitecore.ContentSearch.SitecoreItemCrawler, Sitecore.ContentSearch">
         <Database>web</Database>
         <Root>/sitecore/content/Websites</Root>
    </crawler>
</locations>

Adding the custom index will definitelly positively affect an index size and number of fields in the index.

Example of custom search configuration:

<configuration xmlns:patch="http://www.sitecore.net/xmlconfig/">
  <sitecore>
    <contentSearch>
      <!-- Configuration sections for indexes -->
      <indexConfigurations>

        <!-- If an index has no configuration specified, it will use the configuration below. The configuration is not merged if the index also has
             configuration, it is either this configuration or the index configuration. -->
        <CustomSearchIndexConfiguration type="Sitecore.ContentSearch.SolrProvider.SolrIndexConfiguration, Sitecore.ContentSearch.SolrProvider">
          <!-- This flag will index all fields by default. This allows new fields in your templates to automatically be included into the index.
               You have two choices : 

               1) Set this to true and place all the fields you would like to remove in the 'ExcludeField' list below.
               2) Set to false and place all fields you would like to be indexed in the 'IncludeField' list below.
            -->
          <indexAllFields>false</indexAllFields>

          <!-- Should index Initialize() method be called as soon as the index is added or wait for an external trigger -->
          <!-- For Solr Initialize() needs to be called after the IOC container has fired up -->
          <initializeOnAdd>false</initializeOnAdd>

          <!-- DEFAULT FIELD MAPPING 
               This field map allows you to take full control over how your data is stored in the index. This can affect the way data is queried, performance of searching and how data is retrieved and casted to a proper type in the API. 
            -->
          <fieldMap type="Sitecore.ContentSearch.SolrProvider.SolrFieldMap, Sitecore.ContentSearch.SolrProvider">
            <!-- This element must be first -->
            <typeMatches hint="raw:AddTypeMatch">
              <typeMatch typeName="guidCollection"     type="System.Collections.Generic.List`1[System.Guid]"     fieldNameFormat="{0}_sm"  multiValued="true"                    settingType="Sitecore.ContentSearch.SolrProvider.SolrSearchFieldConfiguration, Sitecore.ContentSearch.SolrProvider" />
              <typeMatch typeName="stringCollection"   type="System.Collections.Generic.List`1[System.String]"   fieldNameFormat="{0}_sm"  multiValued="true"                    settingType="Sitecore.ContentSearch.SolrProvider.SolrSearchFieldConfiguration, Sitecore.ContentSearch.SolrProvider" />
              <typeMatch typeName="intCollection"      type="System.Collections.Generic.List`1[System.Int32]"    fieldNameFormat="{0}_im"  multiValued="true"                    settingType="Sitecore.ContentSearch.SolrProvider.SolrSearchFieldConfiguration, Sitecore.ContentSearch.SolrProvider" />
              <typeMatch typeName="guid"               type="System.Guid"                                        fieldNameFormat="{0}_s"                                         settingType="Sitecore.ContentSearch.SolrProvider.SolrSearchFieldConfiguration, Sitecore.ContentSearch.SolrProvider" />
              <typeMatch typeName="id"                 type="Sitecore.Data.ID, Sitecore.Kernel"                  fieldNameFormat="{0}_s"                                         settingType="Sitecore.ContentSearch.SolrProvider.SolrSearchFieldConfiguration, Sitecore.ContentSearch.SolrProvider" />
              <typeMatch typeName="shortid"            type="Sitecore.Data.ShortID, Sitecore.Kernel"             fieldNameFormat="{0}_s"                                         settingType="Sitecore.ContentSearch.SolrProvider.SolrSearchFieldConfiguration, Sitecore.ContentSearch.SolrProvider" />
              <typeMatch typeName="text"               type="System.String"                                      fieldNameFormat="{0}_t"   cultureFormat="_{1}"                  settingType="Sitecore.ContentSearch.SolrProvider.SolrSearchFieldConfiguration, Sitecore.ContentSearch.SolrProvider" />
              <typeMatch typeName="string"             type="System.String"                                      fieldNameFormat="{0}_s"                                         settingType="Sitecore.ContentSearch.SolrProvider.SolrSearchFieldConfiguration, Sitecore.ContentSearch.SolrProvider" />
              <typeMatch typeName="int"                type="System.Int32"                                       fieldNameFormat="{0}_tl"                                        settingType="Sitecore.ContentSearch.SolrProvider.SolrSearchFieldConfiguration, Sitecore.ContentSearch.SolrProvider" />
              <typeMatch typeName="bool"               type="System.Boolean"                                     fieldNameFormat="{0}_b"                                         settingType="Sitecore.ContentSearch.SolrProvider.SolrSearchFieldConfiguration, Sitecore.ContentSearch.SolrProvider" />
              <typeMatch typeName="datetime"           type="System.DateTime"                                    fieldNameFormat="{0}_tdt" format="yyyy-MM-dd'T'HH:mm:ss.FFF'Z'" settingType="Sitecore.ContentSearch.SolrProvider.SolrSearchFieldConfiguration, Sitecore.ContentSearch.SolrProvider" />
              <typeMatch typeName="long"               type="System.Int64"                                       fieldNameFormat="{0}_tl"                                        settingType="Sitecore.ContentSearch.SolrProvider.SolrSearchFieldConfiguration, Sitecore.ContentSearch.SolrProvider" />
              <typeMatch typeName="float"              type="System.Single"                                      fieldNameFormat="{0}_tf"                                        settingType="Sitecore.ContentSearch.SolrProvider.SolrSearchFieldConfiguration, Sitecore.ContentSearch.SolrProvider" />
              <typeMatch typeName="double"             type="System.Double"                                      fieldNameFormat="{0}_td"                                        settingType="Sitecore.ContentSearch.SolrProvider.SolrSearchFieldConfiguration, Sitecore.ContentSearch.SolrProvider" />
              <typeMatch typeName="stringArray"        type="System.String[]"                                    fieldNameFormat="{0}_sm"  multiValued="true"                    settingType="Sitecore.ContentSearch.SolrProvider.SolrSearchFieldConfiguration, Sitecore.ContentSearch.SolrProvider" />
              <typeMatch typeName="intArray"           type="System.Int32[]"                                     fieldNameFormat="{0}_im"  multiValued="true"                    settingType="Sitecore.ContentSearch.SolrProvider.SolrSearchFieldConfiguration, Sitecore.ContentSearch.SolrProvider" />
              <typeMatch typeName="datetimeArray"      type="System.DateTime[]"                                  fieldNameFormat="{0}_dtm" multiValued="true"                    settingType="Sitecore.ContentSearch.SolrProvider.SolrSearchFieldConfiguration, Sitecore.ContentSearch.SolrProvider" />
              <typeMatch typeName="datetimeCollection" type="System.Collections.Generic.List`1[System.DateTime]" fieldNameFormat="{0}_dtm" multiValued="true"                    settingType="Sitecore.ContentSearch.SolrProvider.SolrSearchFieldConfiguration, Sitecore.ContentSearch.SolrProvider" />
            </typeMatches>

            <!-- This allows you to map a field name in Sitecore to the index and store it in the appropriate way -->
            <!-- Add schema fields here to enable multi-language processing -->
            <fieldNames hint="raw:AddFieldByFieldName">
              <field fieldName="__created_by"         returnType="string" />
              <field fieldName="__smallcreateddate"   returnType="datetime" format="yyyy-MM-dd'T'HH:mm:ss'Z'" />
              <field fieldName="__smallupdateddate"   returnType="datetime" format="yyyy-MM-dd'T'HH:mm:ss'Z'" />
              <field fieldName="__workflow_state"     returnType="string" />
              <field fieldName="extension"            returnType="text" />
              <field fieldName="title"                returnType="text" />
            </fieldNames>

            <!-- FIELD TYPE MAPPING
                 This allows you to map a field type in Sitecore to a type in the index.
                 USAGE: When you add new field types to Sitecore, add the mappings here so they work through the Linq Layer 
              -->
            <fieldTypes hint="raw:AddFieldByFieldTypeName">
              <fieldType fieldTypeName="checkbox"                                                                                                 returnType="bool"             />
              <fieldType fieldTypeName="date|datetime"                                                                                            returnType="datetime"         />
              <fieldType fieldTypeName="html|rich text|single-line text|multi-line text|text|memo|image|reference"                                returnType="text"             />
              <fieldType fieldTypeName="word document"                                                                                            returnType="text"             />
              <fieldType fieldTypeName="integer"                                                                                                  returnType="long"             />
              <fieldType fieldTypeName="number"                                                                                                   returnType="float"            />
              <fieldType fieldTypeName="icon|droplist|grouped droplist"                                                                           returnType="string"           />
              <fieldType fieldTypeName="checklist|multilist|treelist|tree list|treelistex|tree list|multilist with search|treelist with search"   returnType="stringCollection" />
              <fieldType fieldTypeName="name lookup value list|name value list"                                                                   returnType="stringCollection" />
              <fieldType fieldTypeName="droplink|droptree|grouped droplink|tree"                                                                  returnType="stringCollection" />
            </fieldTypes>
          </fieldMap>

          <documentOptions type="Sitecore.ContentSearch.SolrProvider.SolrDocumentBuilderOptions, Sitecore.ContentSearch.SolrProvider">
            <!-- GLOBALLY EXCLUDE TEMPLATES FROM BEING INDEXED
               This setting allows you to exclude items that are based on specific templates from the index.
            -->
            <exclude hint="list:AddExcludedTemplate">
              <BucketFolderTemplateId>{ADB6CA4F-03EF-4F47-B9AC-9CE2BA53FF97}</BucketFolderTemplateId>
            </exclude>

            <!-- GLOBALLY INCLUDE TEMPLATES IN INDEX
               This setting allows you to only include items that are based on specific templates in the index. You must specify all the
               templates that you want to include, because template inheritance is not checked. 
               When you enable this setting, all the items that are based on other templates are excluded, regardless of whether the template
               is specified in the ExcludeTemplate list or not.
            -->
             <include hint="list:AddIncludedTemplate">
               <ContentPage>{04AA33EA-4677-488F-A39A-BDDAE504250E}</ContentPage>
               <HomePage>{23FE027D-1375-4D8C-A2FB-B70276F43C71}</HomePage>
               <NewsPage>{582D070C-D82F-4DC7-8B6F-C3E12FA36F3E}</NewsPage>
            </include>

            <!-- GLOBALLY INCLUDE FIELDS IN INDEX
               This setting allows you to specify which fields to include in the index when the indexAllFields setting is set to false.
            -->
            <!--<include hint="list:AddIncludedField">
            <fieldId>{8CDC337E-A112-42FB-BBB4-4143751E123F}</fieldId>
            </include>-->

            <!-- GLOBALLY EXCLUDE FIELDS FROM BEING INDEXED
               This setting allows you to exclude fields from the index when the indexAllFields setting is set to true.
            -->
            <!--<exclude hint="list:AddExcludedField">
              <__Created>{25BED78C-4957-4165-998A-CA1B52F67497}</__Created>
            </exclude>-->

            <!-- REMOVE INBUILT SITECORE FIELDS
               This allows you to store a field in different ways in the index. You may want to store a field as Analyzed and Not Analyze
            -->
            <fields hint="raw:AddExcludedSpecialField">
              <remove type="both">AllTemplates</remove>
              <remove type="both">Created</remove>
              <remove type="both">Editor</remove>
              <remove type="both">Hidden</remove>
              <remove type="both">Icon</remove>
              <remove type="both">Links</remove>
              <remove type="both">Updated</remove>
            </fields>

            <!-- COMPUTED INDEX FIELDS 
               This setting allows you to add fields to the index that contain values that are computed for the item that is being indexed.
               You can specify the storageType and indextype for each computed index field in the <fieldMap><fieldNames> section.
            -->
            <fields hint="raw:AddComputedIndexField">
              <field fieldName="__smallcreateddate"             returnType="string"          >Sitecore.ContentSearch.ComputedFields.CreatedDate,Sitecore.ContentSearch</field>
              <field fieldName="__smallupdateddate"             returnType="string"          >Sitecore.ContentSearch.ComputedFields.UpdatedDate,Sitecore.ContentSearch</field>
              <field fieldName="_content"                       returnType="string"     type="Sitecore.ContentSearch.ComputedFields.MediaItemContentExtractor,Sitecore.ContentSearch">
                <mediaIndexing ref="contentSearch/indexConfigurations/defaultSolrIndexConfiguration/mediaIndexing"/>
              </field>
            </fields>
          </documentOptions>

          <mediaIndexing ref="contentSearch/indexConfigurations/defaultSolrIndexConfiguration/mediaIndexing" />

          <!-- VIRTUAL FIELDS
               Virtual fields can be used to translate a field query into a different query.
            -->
          <virtualFields type="Sitecore.ContentSearch.VirtualFieldProcessorMap, Sitecore.ContentSearch">
            <processors hint="raw:AddFromConfiguration">
              <add fieldName="daterange" type="Sitecore.ContentSearch.VirtualFields.DateRangeFieldProcessor, Sitecore.ContentSearch" />
              <add fieldName="_lastestversion" type="Sitecore.ContentSearch.VirtualFields.LatestVersionFieldProcessor, Sitecore.ContentSearch" />
              <add fieldName="updateddaterange" type="Sitecore.ContentSearch.VirtualFields.UpdatedDateRangeFieldProcessor, Sitecore.ContentSearch" />
              <add fieldName="_url" type="Sitecore.ContentSearch.VirtualFields.UniqueIdFieldProcessor, Sitecore.ContentSearch" />
              <add fieldName="_fullpath" type="Sitecore.ContentSearch.VirtualFields.FullPathFieldProcessor, Sitecore.ContentSearch" />
              <add fieldName="parsedcreatedby_s" type="Sitecore.ContentSearch.VirtualFields.CreatedByFieldProcessor, Sitecore.ContentSearch" />
            </processors>
          </virtualFields>

          <!-- SITECORE FIELDTYPE MAP
               This maps a field type by name to a Strongly Typed Implementation of the field type e.g. html maps to HTMLField
            -->
          <fieldReaders ref="contentSearch/indexConfigurations/defaultSolrIndexConfiguration/fieldReaders" />

          <!-- INDEX FIELD STORAGE MAPPER 
               Maintains a collection of all the possible Convertors for the provider.
            -->
          <indexFieldStorageValueFormatter ref="contentSearch/indexConfigurations/defaultSolrIndexConfiguration/indexFieldStorageValueFormatter" />

          <!-- INDEX PROPERTY TO DOCUMENT MAPPER
               Maintains a collection of all the possible Convertors for the provider.
            -->
          <indexDocumentPropertyMapper ref="contentSearch/indexConfigurations/defaultSolrIndexConfiguration/indexDocumentPropertyMapper" />


          <!-- DOCUMENT BUILDER
               Allows you to override the document builder. The document builder class processes all the fields in the Sitecore items and prepares
               the data for storage in the index.
               You can override the document builder to modify how the data is prepared, and to apply any additional logic that you may require.
          -->
          <documentBuilderType ref="contentSearch/indexConfigurations/defaultSolrIndexConfiguration/documentBuilderType" />

          <defaultSearchSecurityOption ref="contentSearch/indexConfigurations/defaultSearchSecurityOption" />

        </CustomSearchIndexConfiguration>
      </indexConfigurations>
    </contentSearch>
  </sitecore>
</configuration>

Example of custom index which uses the newly created configuration:

<?xml version="1.0" encoding="utf-8" ?>
<configuration xmlns:patch="http://www.sitecore.net/xmlconfig/">
  <sitecore>
    <contentSearch>
      <configuration type="Sitecore.ContentSearch.ContentSearchConfiguration, Sitecore.ContentSearch">
        <indexes hint="list:AddIndex">
          <index id="custom_sitesearch_index" type="Sitecore.ContentSearch.SolrProvider.SwitchOnRebuildSolrSearchIndex, Sitecore.ContentSearch.SolrProvider">
            <param desc="name">$(id)</param>
            <param desc="core">$(id)</param>
            <param desc="rebuildcore">$(id)_second</param>
            <param desc="propertyStore" ref="contentSearch/indexConfigurations/databasePropertyStore" param1="$(id)" />
            <configuration ref="contentSearch/indexConfigurations/CustomSearchIndexConfiguration" />
            <strategies hint="list:AddStrategy">
              <strategy ref="contentSearch/indexConfigurations/indexUpdateStrategies/onPublishEndAsync" />
            </strategies>
            <locations hint="list:AddCrawler">
              <crawler type="Sitecore.ContentSearch.SitecoreItemCrawler, Sitecore.ContentSearch">
                <Database>web</Database>
                <Root>/sitecore/content/Websites</Root>
              </crawler>
            </locations>
            <enableItemLanguageFallback>true</enableItemLanguageFallback>
            <enableFieldLanguageFallback>true</enableFieldLanguageFallback>
          </index>
        </indexes>
      </configuration>
    </contentSearch>
  </sitecore>
</configuration>

Its using the default Sitecore Web index as its index. Since a custom index would still contain all the same content editable fields, I don't feel that would help us. I've collaborated with others here and it seems the volume of data coming back from SOLR is the issue. I'm looking to see if we can accomplish what we are trying to do a different way. — John Kurtis, Commented Dec 19, 2017 at 19:09
@JohnKurtis I think I understood you. Some times ago I've investigated a possibility to add any parameters in the query that is build to SOLR, but to no avail. Query is built in the Sitecore.ContentSearch.SolrProvider.dll and I haven't found how to extend it with additional logic. Currently we can manage the fl parameter using Sitecore. It will be nice if someone explaining how to this. — Artem Prashkovich, Commented Dec 19, 2017 at 19:34

Jason Bert · Accepted Answer · 2017-12-20 15:59:16Z

While limiting the amount of data being returned is a route to take, sometimes this may not be an option and is a linear solution to performance issues. One very big performance improvement, which many Sitecore developers don't know about, is the use of the Filter LINQ extension.

The Where LINQ extension builds your query against the q (query) parameter in Solr. This parameter is primarily used for when you need scoring/weighting on your query.

If you use the Filter method, it will add the query to the fq (Filter Query) parameter. This is much faster because Solr utilises it's own caching to speed up the performance of these queries and doesn't run any scoring on the query. You would normally use this when scoring on a field/query doesn't make sense.

Instead of doing:

queryable.Where(i => i.TemplateId == templateId && i.Content.Contains("test"))

You can change it to:

queryable.Where(i => i.Content.Contains("test")).Filter(i => i.TemplateId == templateId)

This latter query to Solr will result in a quicker response time. We don't need scoring on the template ID because that will either be true or false. Scoring on the Content field does make sense because we will want Solr to rank the results.

A general rule of thumb when deciding whether to use Where or Filter is to ask yourself the following question.

Does the field I'm querying against result in a boolean? If it does then you should use Filter.

Interesting - Ill take a look at that. Thanks
– John Kurtis
Commented Dec 20, 2017 at 16:17 — John Kurtis, Commented Dec 20, 2017 at 16:17

Stack Exchange Network

Filter number of fields returned from SOLR

3 Answers 3

Your Answer

Not the answer you're looking for? Browse other questions tagged
content-search
solr
or ask your own question.

Hot Network Questions

Filter number of fields returned from SOLR

3 Answers 3

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged content-searchsolr or ask your own question.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
content-search
solr
or ask your own question.