Database Performance

asked on December 13, 2018

Hi,

We are experiencing a database performance issue. According to the DBA, is that the top performance impact statements related to Laserfiche and is using a lot of resources. They have identified the query that is making this impact and is as shown below.

/* (comment inserted by DPA)

Character Range: 17 to 327

Waiting on statement:

INSERT

INTO #tempresult

(

tocid,

rsid,

hitcount,

parentid,

etype,

acl_tocid,

vol_id,

path

)

SELECT tocid,

@P1 rsid,

0 hitcount,

parentid,

etype,

acl_tocid,

vol_id,

'' path

FROM

(

SELECT toc.name displayname,

toc.*

FROM toc

)

toc

WHERE (1=1

AND tocid <> 2

AND del_tocid = 0 )

AND ((etype = -2

AND pagecount > @P2))

(@P1 int,@P2 int)

/* BEGIN ACTIVE SECTION (comment inserted by DPA) */

INSERT

INTO #tempresult

(

tocid,

rsid,

hitcount,

parentid,

etype,

acl_tocid,

vol_id,

path

)

SELECT tocid,

@P1 rsid,

0 hitcount,

parentid,

etype,

acl_tocid,

vol_id,

'' path

FROM

(

SELECT toc.name displayname,

toc.*

FROM toc

)

toc

WHERE (1=1

AND tocid <> 2

AND del_tocid = 0 )

AND ((etype = -2

AND pagecount > @P2))

/* END ACTIVE SECTION (comment inserted by DPA) */

Any ideas what this quesry is for?

Mark

0 0

replied on December 13, 2018

I believe a user is running a search for documents with more than a certain number of pages (variable @P2). I would guess that the performance hit is due to the large number of records that this is dealing with rather than some inherent inefficiency in the query.

0 0

replied on December 16, 2018

Hi Brian,

When you say certain number of pages, do you mean the total number of pges of all the documents searched or the number of pges of each document searched? From the user, they say that the average number of pages per document searched for by the running workflow is between 15 and 20.

Regards,

Mark

0 0

replied on December 17, 2018 • Show version history

If I'm understanding the query and what Brian is saying, the search that the workflow is running might look something like this:

It's probably returning a large number of documents, which is causing the search query to take a while to return. A possible solution here is to find out why the workflow is running a search that probably returns a nontrivial portion of the repository.

0 0

replied on December 18, 2018 • Show version history

Hi Devin,

The workflow activity that is running are "Find Entry" and "Search Repository". The workflow is utilized for the purposes of linking the new entry to already stored documents within the same subfolders.

I went ahead and disabled the workflow but the DB team said the improvement is very small. The DB team have proposed adding the following index to Repository Database database on toc table that may help increase the performance.

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname, >] ON [RepositoryDB].[dbo].[toc] ([etype],[del_tocid],[tocid],[pagecount]) INCLUDE ([parentid],[vol_id],[acl_tocid])

Please advise

Mark

0 0

Question

Question

Replies

Sign in to reply to this post.