Hey, Laserfiche,

Tuesday and Wednesday this week were rough ones with the search issue I had posted here. However, our DBA Ryan was able to find the query Laserfiche is performing in the database and do some magic. I thought I'd post his solution here for you in case someone in need down the line comes up against the same kind of search problem. Note that for us, these index changes caused any given query to consistently return results in sub 0 seconds, dropping average CPU time from 45% to 4% and caused all latch waits to disappear. We had many queries that were taking over 100 seconds prior to these changes. Our searches are performing better than ever with these updates. Please keep in mind that our propval table has 430 million rows.

Ryan found that our specifically troublesome LF search queries run something like this:

select distinct t.tocid, t.parentid, t.linkto

from toc t

where t.tocid <> 2

and (((exists (

       select *

       from propval pv

       where pv.tocid=t.tocid

       and pv.prop_id=15

       and ((pv.short_str_val = N'shortstrval')))))

       and ((exists (

              select *

              from propval pv

              where pv.tocid=t.tocid

              and pv.prop_id=13

              and ((pv.short_str_val = N'shortstrval2'))))))

              and (t.toc_flags & 2048) = 0

The SQL query plan for that query looked something like this where it was seeking an index that was not optimum for the ~430million records in the propval table:

Ryan dropped the stock LF index and recreated it with a better density order and then created a new index to optimize the two subqueries:

USE [Laserfiche DB]

GO

DROP INDEX [propval_short_str_val_ix] ON [dbo].[propval]

GO

CREATE NONCLUSTERED INDEX [propval_short_str_val_ix] ON [dbo].[propval]

(

       [tocid] ASC,

       [prop_id] ASC,

       [short_str_val] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [propval_IXNC02] ON [dbo].[propval]

(

       [prop_id] ASC,

       [short_str_val] ASC

)

INCLUDE ([tocid]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

This resulted in a new query plan:

I realize we now have a non-standard index as part of our Laserfiche database. Please advise if anyone sees any issues with this. 

Ian T. Caiozzi

Pulte Mortgage, LLC