Slow performance when trying to export CSV for 13,400 docs

[Nat_Mara]

Ran DBToolkit the other day and came back with 1,300 High-level results , which were all stale statistics.

Before fixing, i ran some Thick Client benchmark tests from the server and my laptop, such as time to log in and be ready to retrieve docs, logging into configuration and viewing all users, and also doing a retrieval of just over 13,400 documents and exporting to a CSV.

Once I had benchmark data, we ran a tool to update our statistics and re-ran DBToolkit and no high-level results returned.  I re-ran the benchmark tests, and did not see noticeable improvements.  As an example, exporting a CSV from the server takes between 9.5-10.5 minutes.  I expect much quicker turnaround for a query of that size, but maybe you can tell me differently.  When I run the same query from my laptop, it takes substantially longer, and some time does not finish.

Here are my questions:  

1. I saw an overall slowness thread from last year, and Mike W suggested running in Verbose mode to see if there are any long queries.  What is considered a long query?
2. What other factors could lead to this slowness?  Since it goes thru TC, I assume DB, but maybe I need to see where other network bottlenecks are occurring too.  Is this an I/O issue, or what else should I be concerned with?

thanks much,

Nat

[Nat_Mara]

so I finally was able to run the process with Verbose logging enabled.  Actually slowed the export down by 1.5 minutes.  The verbose log is huge (>500,000 lines) but at quick glance there are not any obvious outliers as far as time stamp jumps.  Never been strong at reading verbose logs, so maybe I am missing something.  Am I looking for particular syntax?

[George_Patrick]

Nat, 

In this case, I would not think that it would be related to a long running queries but instead it's probably a large numbers of small queries that are consuming the time.

What options are you selecting when generating the CSV and how many keywords are associated with these documents? 

-George

[Nat_Mara]

George that is it.  I glazed over the obvious thing.  It is a single Document search, and I just restricted it by a single Date value but the document does have 32 KW.  thanks for your assistance.  It was in a test environment, so there are not a lot of documents to test with,  but most do not have that many KW.  I need to try with a better document type.

[Thomas_Reu · Accepted solution]

Just an FYI, since I don't see this mentioned above.   

e.g. Why are 32 keywords excessive in OnBase?  In OnBase, the typical structure means each keyword gets its' own table and it has to be linked to the itemdata table to really do anything.  That means a 32 keyword doctype requires 32 joins to bring the whole nine yards together, in a view that will display everything.   From a database point of view, that's some serious bench pressing.  Also, those joins need to be left joins, if there is any chance the keyword could be empty. 

That said, if you use an MIKG, then all of the keywords are stored in one table together.  That means the backend view only needs to join the itemdata table and the MIKG table and you're good.  Unfortunately, MIKG's may open other issues, so you shouldn't consider this a silver bullet.

Another option is a custom query or view that only returns a small number of the keywords.  Anything to reduce the number of joins... 

[George_Patrick]

That's great information Tom, Thanks for posting. 

In general it's considered the best practice to use MIKG's when possible because it does reduce the number of database queries.  In this case the exporting to CSV action in the OnBase client will reach out and grab all the keyword data for all the documents selected for your report. Some solutions might not be able to make use of MIKG's and there are no options to export only a subset of the keywords on the document. So even if you search and generate a hitlist from a custom query, the generate CSV action will still query the database for all the keywords associated with the documents. 

-George