Question

Does Indexing fields help with Searches used in a Workflow?

Workflow

Updated December 22, 2015

asked on December 18, 2015

I have a customer with 200+ workflows?

Many of these Workflows use DB Query and Search Repository.

We are getting occasional significant performance slow downs we have identified CPU Spiking on the SQL Server.

Could indexing fields help to off load the Search overhead from the SQL Server to LFTS?

I understand Index Fields use LFTS instead of SQL however is that still true when using Searches in a Workflow?

Most Fields are small and concise but we do have approx 200GB of data so over 1 million Field Values for sure although most of our fields do not meet the other criteria recommended for indexing Fields.

"There are two ways to perform field searches: as non-indexed searches (in which case the database management system's SQL search is used) or as indexed searches (in which case the Laserfiche Full-Text Indexing and Search Engine is used). By default, all fields are non-indexed, but in some cases you may choose to index text and list fields.

When to Index Fields

In general, fields should be indexed in the following circumstances:

Fields that will contain long, nonstandardized content, such as notes or commentary, should be indexed to take advantage of search stemming, fuzzy search, and context hits.
If a field has an width greater than 100 characters, as the full-text search engine can handle these fields more efficiently than non-indexed search.
If your repository contains more than one million total field values (fields applied to entries and containing a value), indexing commonly-searched text fields can improve search performance.

Fields should not be indexed in the following situations:

If the field will generally contain relatively short, consistent values, such as names, department names, or document types, it is more efficient to leave the field unindexed.
If you want to take advantage of case and accent sensitivity options for a field, you should not index the fields, as these options do not apply to indexed searches."

0 0

Replies

replied on December 18, 2015

Workflow runs searches just like any other user would do, there's nothing special about them.

0 0

replied on December 20, 2015

Miruna is correct about repository searches. Unless you have silly stuff like Search Repository activities inside For loops, or performing wildcard searches on huge fields, they shouldn't cause any noticeable performance issues.

As for database queries, if Workflow is querying large tables or views that are not indexed, they will perform slowly and use a lot of resources on the database server. However, whether or not you can (or should) index those tables depends on many factors. In my opinion, you're much more likely to get performance benefits by modifying your workflows and the queries they use to be as efficient as possible. For instance, we have had many situations where combining multiple Query Data and Update Data activities into one Custom Query resulted in significant performance gains.

0 0

replied on December 21, 2015

You mentioned a spike in SQL server's CPU. Any chance you looked at the SQL Activity Monitor to see the query causing it?

0 0

replied on December 22, 2015

We've been actively trying to identify what is causing this SQL spike. Running SQL Profiler, Activity Queries etc. This query I'm pasting shows as one of the top queries against the server. Any thoughts on what this query might be doing in a Laserfiche world?

(@P1 int,@P2 int,@P3 int,@P4 int,@P5 int,@P6 nvarchar(9),@P7 nvarchar(9))insert into #search_entry_cache2 (tocid, parentid, linkto) select distinct t.tocid, t.parentid, t.linkto from toc t left join searchresult7 sr0 on sr0.tocid = t.tocid and sr0.rsid = @P1 where t.tocid <> 2 and ((sr0.tocid is not null) or ((exists (select * from propval pv2, propdef pd2 where exists(select * from propacl where propacl.prop_id = pv2.prop_id and propacl.sid in (select sid from sess_sid where sess_id = @P2 ) and ace_type = 0 and mask & 1 = 1) and not exists(select * from propacl where propacl.prop_id = pv2.prop_id and propacl.sid in (select sid from sess_sid where sess_id = @P3 ) and ace_type = 1 and mask & 1 = 1)and ((exists(select * from psetacl where psetacl.pset_id = t.pset_id and psetacl.sid in (select sid from sess_sid where sess_id = @P4 ) and ace_type = 0 and mask & 1 = 1) and not exists(select * from psetacl where psetacl.pset_id = t.pset_id and psetacl.sid in (select sid from sess_sid where sess_id = @P5 ) and ace_type = 1 and mask & 1 = 1)) or t.pset_id is null or t.pset_id not in (select pset_id from pset_props where prop_id = pv2.prop_id)) and pd2.prop_id = pv2.prop_id and t.tocid = pv2.tocid and ((((((replace(rtrim(replace(replace(rtrim(replace(pv2.num_val,'0',' ')),' ','0'),'.',' ')),' ','.') like @P6 ))) and pd2.prop_type in ('N','I','H')) or ((pv2.str_val like @P7 ) and pd2.prop_type in ('S','L')))))))) and (t.toc_flags & 2048) = 0

0 0

replied on December 22, 2015

This is a search across all fields, for ex, {[ ]:[ ] = "value"}, which would be fairly expensive in a larger repository.

0 0

replied on December 22, 2015

We've also just made some memory adjustments on the server and virtual machine reserve to see how that effects it's performance.

http://www.sqlservercentral.com/blogs/glennberry/2009/10/29/suggested-max-memory-settings-for-sql-server-2005_2F00_2008/

0 0

You are not allowed to follow up in this post.

Question

Question

Does Indexing fields help with Searches used in a Workflow?

Replies

Sign in to reply to this post.