You are viewing limited content. For full access, please sign in.

Question

Question

What would be performing this query from the repository?

Laserfiche Version 11
Updated March 7
asked on March 14, 2024

I have been reviewing the event viewer logs for our Laserfiche Server and have been finding a lot of SQL query timeout errors in the \ContentRepository\Service\Admin log. One example of a query that is listed is the one below. Is this being done when someone is performing a search within the repository or is a system function?

Log Name:      Laserfiche-ContentRepository-Service/Admin
Source:        Laserfiche-ContentRepository-Service
Date:          3/13/2024 10:21:18 PM
Event ID:      197
Task Category: None
Level:         Error
Keywords:      
User:          xxxx
Computer:      xxxx
Description:
Timed out when executing an SQL query. Session ID=615712; Dialog ID=10876631; Repository="Financials"; Function=SysEntryOpenListingStreamEx; Message="[Microsoft][ODBC Driver 17 for SQL Server]Query timeout expired"; Statement="select id,link,type,flags,sdctl,rfid,parentid,toc_uuid,toc_owner,isFrozen,recordState,hasacl,c0,c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18,c19,c20,c21,c22,c23,c24,c25,c26,c27,c28,c29,c30,c30_count,c31,c32,c33,c34,c35,c36,c37,c38 from (select toc.tocid as id,toc.linkto as link,toc.etype as type,toc.toc_flags as flags,toc.toc_sdctl as sdctl,toc.rfid as rfid,toc.parentid as parentid, toc.toc_uuid as toc_uuid, toc.toc_owner toc_owner,case when toc.tocid in (select tocid from frozen) then 1 else 0 end as isFrozen,case when toc.rfid is null then 0 when toc.isDispositioned = 1 then 4 when toc.iscutoff = 1 then 3 when toc.isclosed = 1 then 2 else 1 end as recordState,case when toc.toc_flags & 32 = 32 then 1 else 0 end as hasacl,case when linktoc.etype>=0 then 1 else 0 end as isfolder,toc.tocid as c0,toc.name as c1,linktoc.edoc_ext as c2,toc.parentid as c3,toc.etype as c4,case when linktoc.edoc_storeid is null then null else case when linktoc.edoc_size is null then 0 else linktoc.edoc_size end end as c5,linktoc.etype as c6,toc.etag as c7,toc.linkto as c8,case when linktoc.etype = 0 and linktoc.rfid = linktoc.tocid then 320 when linktoc.etype = 0 and (linktoc.toc_flags &  1) = 1 then 257 when linktoc.etype = 0 then 256 when linktoc.etype = 1 then 384 when ((linktoc.edoc_mime IS NOT NULL AND linktoc.edoc_mime <> '') or (linktoc.edoc_ext IS NOT NULL AND linktoc.edoc_ext <> '')) and (linktoc.toc_flags &  1) = 1 then 17 when (linktoc.edoc_mime IS NOT NULL AND linktoc.edoc_mime <> '') or (linktoc.edoc_ext IS NOT NULL AND linktoc.edoc_ext <> '') then 16 when (linktoc.toc_flags &  1) = 1 then 1 else 0 end as c9,toc.created as c10,toc.modified as c11,case linktoc.etype when -2 then linktoc.pagecount else null end as c12,linktoc.vol_id as c13,linktoc.pset_id as c14,(select p.path from searchresult6 p where p.rsid = sr.rsid  and p.tocid = toc.tocid) as c15,toc.name as c16,0 as c17,1 as c18,toc.toc_uuid as c19,isnull(creator_cache.friendly_name, creator_cache.account_name) as c20,plock.owner  as c21,cast(null as varbinary(85)) as c22,0 as c23,case when (linktoc.toc_flags &  512) = 512 then 1 else 0 end as c24,case when ((linktoc.toc_flags &  512) = 512 and (linktoc.toc_flags &  1024) = 0) then 1 else 0 end as c25,case when (linktoc.toc_flags & 512 = 512 and linktoc.toc_flags & 1024 = 0) then (select top 1 ad.who from active_doc ad where ad.tocid = linktoc.tocid) else cast(null as varbinary(85)) end as c26,linktoc.pset_id as c27,pvj28.str_val as c28,pvj29.str_val as c29,pvj30.str_val as c30,(select count(*) from propval where propval.tocid=linktoc.tocid and propval.prop_id=71)  as c30_count,pvj31.str_val as c31,pvj32.str_val as c32,pvj33.date_val as c33,pvj34.str_val as c34,pvj35.str_val as c35,pvj36.str_val as c36,pvj37.str_val as c37,pvj38.str_val as c38, l.row_num from searchresult6 sr inner loop join toc on toc.tocid = sr.tocid and sr.rsid = :rsetid<int>  right outer join #tmplisting l on toc.tocid=l.tocid  left outer join toc linktoc   with (nolock)  on case when toc.etype = -1 then toc.linkto else toc.tocid end = linktoc.tocid left outer join account_cache creator_cache on toc.creator = creator_cache.account_sid left outer join (select plock.*, ROW_NUMBER() OVER (PARTITION BY tocid ORDER BY owner) AS rn from plock   with (nolock) ) plock on linktoc.tocid=plock.tocid and plock.rn = 1 left outer join propval pvj28 on pvj28.tocid=linktoc.tocid and pvj28.prop_id=74 and pvj28.pos=0 and (pvj28.pos2 is null or pvj28.pos2<=1) left outer join propval pvj29 on pvj29.tocid=linktoc.tocid and pvj29.prop_id=73 and pvj29.pos=0 and (pvj29.pos2 is null or pvj29.pos2<=1) left outer join propval pvj30 on pvj30.tocid=linktoc.tocid and pvj30.prop_id=71 and pvj30.pos=0 and (pvj30.pos2 is null or pvj30.pos2<=1) left outer join propval pvj31 on pvj31.tocid=linktoc.tocid and pvj31.prop_id=115 and pvj31.pos=0 and (pvj31.pos2 is null or pvj31.pos2<=1) left outer join propval pvj32 on pvj32.tocid=linktoc.tocid and pvj32.prop_id=69 and pvj32.pos=0 and (pvj32.pos2 is null or pvj32.pos2<=1) left outer join propval pvj33 on pvj33.tocid=linktoc.tocid and pvj33.prop_id=67 and pvj33.pos=0 and (pvj33.pos2 is null or pvj33.pos2<=1) left outer join propval pvj34 on pvj34.tocid=linktoc.tocid and pvj34.prop_id=16 and pvj34.pos=0 and (pvj34.pos2 is null or pvj34.pos2<=1) left outer join propval pvj35 on pvj35.tocid=linktoc.tocid and pvj35.prop_id=75 and pvj35.pos=0 and (pvj35.pos2 is null or pvj35.pos2<=1) left outer join propval pvj36 on pvj36.tocid=linktoc.tocid and pvj36.prop_id=64 and pvj36.pos=0 and (pvj36.pos2 is null or pvj36.pos2<=1) left outer join propval pvj37 on pvj37.tocid=linktoc.tocid and pvj37.prop_id=66 and pvj37.pos=0 and (pvj37.pos2 is null or pvj37.pos2<=1) left outer join propval pvj38 on pvj38.tocid=linktoc.tocid and pvj38.prop_id=84 and pvj38.pos=0 and (pvj38.pos2 is null or pvj38.pos2<=1) where ((toc.etype is null or ((toc.etype=-2 or toc.etype>=0) or (toc.etype = -1 and (linktoc.etype=-2 or linktoc.etype>=0)))))) as q1  where q1.row_num>=:begin<int> and q1.row_num<=:end<int> order by q1.row_num"; Timeout=15(sec).
Event Xml:
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
  <System>
    <Provider Name="Laserfiche-ContentRepository-Service" Guid="{82FC23FE-4B0A-435C-82E6-01C7077098AF}" />
    <EventID>197</EventID>
    <Version>0</Version>
    <Level>2</Level>
    <Task>0</Task>
    <Opcode>0</Opcode>
    <Keywords>0x4000000000000000</Keywords>
    <TimeCreated SystemTime="2024-03-14T05:21:18.789746100Z" />
    <EventRecordID>5081693</EventRecordID>
    <Correlation />
    <Execution ProcessID="8228" ThreadID="4884" />
    <Channel>Laserfiche-ContentRepository-Service/Admin</Channel>
    <Computer>xxxx</Computer>
    <Security UserID="xxxx" />
  </System>
  <EventData>
    <Data Name="sessionID">615712</Data>
    <Data Name="dialogID">10876631</Data>
    <Data Name="repository">Financials</Data>
    <Data Name="func">SysEntryOpenListingStreamEx</Data>
    <Data Name="message">[Microsoft][ODBC Driver 17 for SQL Server]Query timeout expired</Data>
    <Data Name="stmt">select id,link,type,flags,sdctl,rfid,parentid,toc_uuid,toc_owner,isFrozen,recordState,hasacl,c0,c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18,c19,c20,c21,c22,c23,c24,c25,c26,c27,c28,c29,c30,c30_count,c31,c32,c33,c34,c35,c36,c37,c38 from (select toc.tocid as id,toc.linkto as link,toc.etype as type,toc.toc_flags as flags,toc.toc_sdctl as sdctl,toc.rfid as rfid,toc.parentid as parentid, toc.toc_uuid as toc_uuid, toc.toc_owner toc_owner,case when toc.tocid in (select tocid from frozen) then 1 else 0 end as isFrozen,case when toc.rfid is null then 0 when toc.isDispositioned = 1 then 4 when toc.iscutoff = 1 then 3 when toc.isclosed = 1 then 2 else 1 end as recordState,case when toc.toc_flags &amp; 32 = 32 then 1 else 0 end as hasacl,case when linktoc.etype&gt;=0 then 1 else 0 end as isfolder,toc.tocid as c0,toc.name as c1,linktoc.edoc_ext as c2,toc.parentid as c3,toc.etype as c4,case when linktoc.edoc_storeid is null then null else case when linktoc.edoc_size is null then 0 else linktoc.edoc_size end end as c5,linktoc.etype as c6,toc.etag as c7,toc.linkto as c8,case when linktoc.etype = 0 and linktoc.rfid = linktoc.tocid then 320 when linktoc.etype = 0 and (linktoc.toc_flags &amp;  1) = 1 then 257 when linktoc.etype = 0 then 256 when linktoc.etype = 1 then 384 when ((linktoc.edoc_mime IS NOT NULL AND linktoc.edoc_mime &lt;&gt; '') or (linktoc.edoc_ext IS NOT NULL AND linktoc.edoc_ext &lt;&gt; '')) and (linktoc.toc_flags &amp;  1) = 1 then 17 when (linktoc.edoc_mime IS NOT NULL AND linktoc.edoc_mime &lt;&gt; '') or (linktoc.edoc_ext IS NOT NULL AND linktoc.edoc_ext &lt;&gt; '') then 16 when (linktoc.toc_flags &amp;  1) = 1 then 1 else 0 end as c9,toc.created as c10,toc.modified as c11,case linktoc.etype when -2 then linktoc.pagecount else null end as c12,linktoc.vol_id as c13,linktoc.pset_id as c14,(select p.path from searchresult6 p where p.rsid = sr.rsid  and p.tocid = toc.tocid) as c15,toc.name as c16,0 as c17,1 as c18,toc.toc_uuid as c19,isnull(creator_cache.friendly_name, creator_cache.account_name) as c20,plock.owner  as c21,cast(null as varbinary(85)) as c22,0 as c23,case when (linktoc.toc_flags &amp;  512) = 512 then 1 else 0 end as c24,case when ((linktoc.toc_flags &amp;  512) = 512 and (linktoc.toc_flags &amp;  1024) = 0) then 1 else 0 end as c25,case when (linktoc.toc_flags &amp; 512 = 512 and linktoc.toc_flags &amp; 1024 = 0) then (select top 1 ad.who from active_doc ad where ad.tocid = linktoc.tocid) else cast(null as varbinary(85)) end as c26,linktoc.pset_id as c27,pvj28.str_val as c28,pvj29.str_val as c29,pvj30.str_val as c30,(select count(*) from propval where propval.tocid=linktoc.tocid and propval.prop_id=71)  as c30_count,pvj31.str_val as c31,pvj32.str_val as c32,pvj33.date_val as c33,pvj34.str_val as c34,pvj35.str_val as c35,pvj36.str_val as c36,pvj37.str_val as c37,pvj38.str_val as c38, l.row_num from searchresult6 sr inner loop join toc on toc.tocid = sr.tocid and sr.rsid = :rsetid&lt;int&gt;  right outer join #tmplisting l on toc.tocid=l.tocid  left outer join toc linktoc   with (nolock)  on case when toc.etype = -1 then toc.linkto else toc.tocid end = linktoc.tocid left outer join account_cache creator_cache on toc.creator = creator_cache.account_sid left outer join (select plock.*, ROW_NUMBER() OVER (PARTITION BY tocid ORDER BY owner) AS rn from plock   with (nolock) ) plock on linktoc.tocid=plock.tocid and plock.rn = 1 left outer join propval pvj28 on pvj28.tocid=linktoc.tocid and pvj28.prop_id=74 and pvj28.pos=0 and (pvj28.pos2 is null or pvj28.pos2&lt;=1) left outer join propval pvj29 on pvj29.tocid=linktoc.tocid and pvj29.prop_id=73 and pvj29.pos=0 and (pvj29.pos2 is null or pvj29.pos2&lt;=1) left outer join propval pvj30 on pvj30.tocid=linktoc.tocid and pvj30.prop_id=71 and pvj30.pos=0 and (pvj30.pos2 is null or pvj30.pos2&lt;=1) left outer join propval pvj31 on pvj31.tocid=linktoc.tocid and pvj31.prop_id=115 and pvj31.pos=0 and (pvj31.pos2 is null or pvj31.pos2&lt;=1) left outer join propval pvj32 on pvj32.tocid=linktoc.tocid and pvj32.prop_id=69 and pvj32.pos=0 and (pvj32.pos2 is null or pvj32.pos2&lt;=1) left outer join propval pvj33 on pvj33.tocid=linktoc.tocid and pvj33.prop_id=67 and pvj33.pos=0 and (pvj33.pos2 is null or pvj33.pos2&lt;=1) left outer join propval pvj34 on pvj34.tocid=linktoc.tocid and pvj34.prop_id=16 and pvj34.pos=0 and (pvj34.pos2 is null or pvj34.pos2&lt;=1) left outer join propval pvj35 on pvj35.tocid=linktoc.tocid and pvj35.prop_id=75 and pvj35.pos=0 and (pvj35.pos2 is null or pvj35.pos2&lt;=1) left outer join propval pvj36 on pvj36.tocid=linktoc.tocid and pvj36.prop_id=64 and pvj36.pos=0 and (pvj36.pos2 is null or pvj36.pos2&lt;=1) left outer join propval pvj37 on pvj37.tocid=linktoc.tocid and pvj37.prop_id=66 and pvj37.pos=0 and (pvj37.pos2 is null or pvj37.pos2&lt;=1) left outer join propval pvj38 on pvj38.tocid=linktoc.tocid and pvj38.prop_id=84 and pvj38.pos=0 and (pvj38.pos2 is null or pvj38.pos2&lt;=1) where ((toc.etype is null or ((toc.etype=-2 or toc.etype&gt;=0) or (toc.etype = -1 and (linktoc.etype=-2 or linktoc.etype&gt;=0)))))) as q1  where q1.row_num&gt;=:begin&lt;int&gt; and q1.row_num&lt;=:end&lt;int&gt; order by q1.row_num</Data>
    <Data Name="timeout">15</Data>
  </EventData>
</Event>

We are running Laserfiche Server version 11.0.2.338.

0 0

Answer

SELECTED ANSWER
replied on March 14, 2024 • Show version history

Hi Blake,

The query you're referencing is used when viewing entry listings in the repository. For example, when performing a search and viewing the result set, or when browsing folders in the repository.

Typically, for issues related to SQL query timeouts, we first recommend performing database maintenance to see if that may help. One example we commonly suggest to customers is example B from https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

If the query timeout still occurs, then the next suggestion may be to examine what columns the user encountering the issue has enabled. Perhaps they have one or more columns enabled that may particularly tax the SQL Server more than others and perhaps they can disable that column from the listing if it isn't required.

If the issue still occurs after doing this, then a third suggestion is to increase the query timeout specifically for getting entry listings. In the Laserfiche repository database, you can insert the following option into the dboptions table

EntryListingQueryTimeout

and give it a value of 30 to start. Restart the Laserfiche Server service and see if that additional time is enough for the query to complete.

Note that a high timeout value may degrade the performance of other sessions and in the worst case it could cause the server to become unresponsive if it's set too high and there's a large number of very slow listing queries that the server starts to run. 30 is a safe starting point if going this route.

Regards

1 0
View 2 previous replies
replied on March 14, 2024

Thank you for the reply, Alex. We are already running the Ola Hallengren maintenance queries on a schedule per his recommendations.

I have not had end users complain about the errors, it is just what I saw when looking in the logs. I will try the database option and start at 30 to see if that helps lower the number of these errors that are being recorded.

0 0
replied on June 27, 2024

If you aren't familiar with Ola's scripts, here is a video I created that does a high overview: https://youtu.be/iacDlUsc9UE?si=qAXAdIxezzb_mudW

0 0
replied on July 30, 2024 • Show version history

Hello Smith,

Any resolution for below error. We are doing quick search and its not working and also when we are using the LF query inside the SDK programming we got same issue.

 

 

Timed out when executing an SQL query. Session ID=519077; Dialog ID=8925961; Repository="HFRD"; Function=SysEntryOpenListingStreamEx; Message="[Microsoft][ODBC Driver 11 for SQL Server]Query timeout expired"; Statement="select id,link,type,flags,sdctl,rfid,parentid,toc_uuid,toc_owner,isFrozen,recordState,hasacl,c0,c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18,c19,c20,c21,c22,c23,c24,c25,c26,c27,c28,c29 from (select toc.tocid as id,toc.linkto as link,toc.etype as type,toc.toc_flags as flags,toc.toc_sdctl as sdctl,toc.rfid as rfid,toc.parentid as parentid, toc.toc_uuid as toc_uuid, toc.toc_owner toc_owner,case when toc.tocid in (select tocid from frozen) then 1 else 0 end as isFrozen,case when toc.rfid is null then 0 when toc.isDispositioned = 1 then 4 when toc.iscutoff = 1 then 3 when toc.isclosed = 1 then 2 else 1 end as recordState,case when toc.toc_flags & 32 = 32 then 1 else 0 end as hasacl,case when linktoc.etype>=0 then 1 else 0 end as isfolder,toc.tocid as c0,toc.name as c1,linktoc.edoc_ext as c2,toc.parentid as c3,toc.etype as c4,case when linktoc.edoc_storeid is null then null else case when linktoc.edoc_size is null then 0 else linktoc.edoc_size end end as c5,linktoc.etype as c6,toc.etag as c7,linktoc.pset_id as c8,toc.linkto as c9,case when linktoc.etype = 0 and linktoc.rfid = linktoc.tocid then 320 when linktoc.etype = 0 and (linktoc.toc_flags &  1) = 1 then 257 when linktoc.etype = 0 then 256 when linktoc.etype = 1 then 384 when ((linktoc.edoc_mime IS NOT NULL AND linktoc.edoc_mime <> '') or (linktoc.edoc_ext IS NOT NULL AND linktoc.edoc_ext <> '')) and (linktoc.toc_flags &  1) = 1 then 17 when (linktoc.edoc_mime IS NOT NULL AND linktoc.edoc_mime <> '') or (linktoc.edoc_ext IS NOT NULL AND linktoc.edoc_ext <> '') then 16 when (linktoc.toc_flags &  1) = 1 then 1 else 0 end as c10,toc.created as c11,toc.modified as c12,case linktoc.etype when -2 then linktoc.pagecount else null end as c13,linktoc.vol_id as c14,linktoc.pset_id as c15,toc.name as c16,0 as c17,1 as c18,toc.toc_uuid as c19,plock.owner  as c20,cast(null as varbinary(85)) as c21,0 as c22,case when linktoc.final_date is not null or linktoc.isdispositioned = 0 then linktoc.final_date else rf_toc.final_date end as c23,(case when linktoc.edoc_storeid is null then 0 else (case when linktoc.edoc_size is null then 0 else linktoc.edoc_size end) end) + (select isnull(sizetemp1.sum1, 0) + isnull(sizetemp2.sum2, 0) + isnull(sizetemp3.sum3, 0) from (select sum(cast(img_size as bigint)) + sum(cast(txt_size as bigint)) + sum(cast(loc_size as bigint)) as sum1 from doc where pagenum >= 0 and linktoc.tocid = doc.tocid) sizetemp1, (select sum(cast(altedoc_size as bigint)) as sum2 from altedoc where altedoc.tocid = linktoc.tocid) sizetemp2, (select isnull(sum(cast(datalength(ann.ann_text) as bigint)), 0) + isnull(sum(cast(ann.attach_size as bigint)),0) + isnull(sum(cast(datalength(ann.bitmap) as bigint)), 0) as sum3 from ann where ann.page_id in (select page_id from doc where doc.tocid = linktoc.tocid and doc.pagenum >= 0)) sizetemp3) as c24,case when (linktoc.toc_flags &  512) = 512 then 1 else 0 end as c25,case when ((linktoc.toc_flags &  512) = 512 and (linktoc.toc_flags &  1024) = 0) then 1 else 0 end as c26,case when (linktoc.toc_flags & 512 = 512 and linktoc.toc_flags & 1024 = 0) then (select top 1 ad.who from active_doc ad where ad.tocid = linktoc.tocid) else cast(null as varbinary(85)) end as c27,linktoc.isdispositioned as c28,linktoc.pset_id as c29, l.row_num from searchresult13 sr inner loop join toc on toc.tocid = sr.tocid and sr.rsid = :rsetid<int>  right outer join #tmplisting l on toc.tocid=l.tocid  left outer join toc linktoc   with (nolock)  on case when toc.etype = -1 then toc.linkto else toc.tocid end = linktoc.tocid left outer join toc rf_toc   with (nolock)  on linktoc.rfid = rf_toc.tocid left outer join (select plock.*, ROW_NUMBER() OVER (PARTITION BY tocid ORDER BY owner) AS rn from plock   with (nolock) ) plock on linktoc.tocid=plock.tocid and plock.rn = 1 where ((toc.etype is null or ((toc.etype=-2 or toc.etype>=0) or (toc.etype = -1 and (linktoc.etype=-2 or linktoc.etype>=0)))))) as q1  where q1.row_num>=:begin<int> and q1.row_num<=:end<int> order by q1.row_num"; Timeout=15(sec).

Error.png
Error when we click OK.txt (833 B)
Download
Error.png (170.88 KB)
Download
0 0
replied on July 31, 2024

Did you try the "EntryListingQueryTimeout" suggested above? The log indicates it's using a timeout of 15 seconds. The other idea would be to reduce the columns in your listing. As you can see from the query, the more columns requested the more complex the query is. Increasing the timeout may allow it to complete, but your users may find that it's longer than they would like to wait, and it's putting quite a bit of load on the sql server.

0 0
replied on July 31, 2024

To Brian's point, the number of columns will definitely play a role, especially when you include "expensive" calculated values like Total Document Size.

0 0

Replies

replied on March 7

This issue has re-introduced itself. I believe that it is probably a user that has too many columns configured in their display. Is there a SQL query that could be run to show how many columns a user has configured based on trustee attributes?

0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.

Details
Attachments
Related Posts
Loading ...