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

[George_Patrick]

Good Afternoon Nat, 

I am glad to hear you updated to the statistics, while it might not have improved the performance of the actions you are performing, it can ensure that your system is running smoother and prevent issues in the future. 

You are correct, that the TC makes a direct connection to the database. If you are seeing much slower speeds on your laptop, its more than likely due to the amount of queries that are being executed to get the data required to generate a CSV file. If you are connected via Wifi, I would test using ethernet if possible. If you are not physically located in the same location that your database server is located (i.e. working from home and using a VPN) it can also dramatically reduce performance. A simple ping test to the database is a good test to see what your current latency is. Due to the amount of traffic and chatty-ness of ODBC it is very sensitive to latency. 

Without knowing anything about your configuration or the options you have selected with the Generate CSV options, its hard to say if these numbers are acceptable. The time it takes to generate a report will differ based on the amount of keywords that are on the documents that you have selected and the keyword configuration (loose keywords or MIKGs). 

When Mike asked about any long running queries, he was more than likely talking about any outliers that are taking much longer to execute than expected when compared against the other queries in the verbose logging. 

When you mentioned it was faster on the server, are you talking about a server located in the same physical location as the database server or did you execute this on the database server itself? 

If you think there might be a performance issue associated with your OnBase solution it would be best to get Technical Support involved to assist with the troubleshooting. 

-George

[Nat_Mara]

Hi George,

I am working from home, so I am not in the same place as the DB server.  That is why I am running the query from the Application server, to hopefully reduce some of the latency issues.  Running a ping from the server, brings back TTL 127 and the approximate roundtrip times in milliseconds are all 0ms.  I wlll run the  query with verbose this time to see what it shows. and will see what I figure out.

regards,

[Michael_Terrybe]

Hi Nat,

I'm constantly working with OnBase clients to look at infrastructure related vs application related slowness. Hopefully I can help point you in a direction. To answer your specific questions above:

1. Long running queries themselves are generally obvious (1 query that takes 5 minutes for example). So, you are really just looking for outliers as opposed to specific timeframes. Another thing to look at is the percentage of time associated with SQL. For example, if I know that logging in takes me 3 minutes, what is the total duration of all SQL queries combined during the timeframe that I waited for 3 minutes. If it is 2 minutes and 55 seconds then at least you know you are looking at the correct piece of the system. If you then notice that all SQL queries in that timeframe are consistent then you have to start considering network latency that is affecting your performance.

2. With Thick Client specifically, DB is going to be the big piece. But Thick Client often also introduces DDS as end users may not have direct access to disk groups. So, that is another area that is often worth looking into.

All that being said, Thick Client is generally a poor performer for users in a WAN environment or users working from home because the amount of data going between the user's workstation and the Database server is significant. Unity Client is a better performer because the AppServer will handle the communication to the Database server and generally AppServers have low latency to the Database server (or they should anyway). Web Client would be the best performer because nearly all application functionality is handled on the servers and just presented back to the end user.

[Nat_Mara]

Thanks Michael, 

Really appreciate the responses.  I will try the lookup again from the server ( to eliminate some of the BW ) concerns, compared to doing the same thing from my laptop, while using VPN.  Secondly, I was not using the Unity Client, as there are total document  restrictions when going thru the AppServer. I believe it is 5,000 documents.  I can test the webclient, as I am not sure of the query limitations on the webserver.

regards,