OpenWebService.GetEmployeesByQuery performance

A forum for Custom Reporting & SQL Queries in Deltek Vision.
calfred
Posts: 2
Joined: Thu Nov 20, 2014 5:31 pm
Brief Company Description: Product Development outsourcing (software, electrical and mechanical) primarily in Medical Devices, Industrial Equipment and Aerospace
Deltek Vision Version: 6.2
Number of Employees: 500
Company Position: VP Consulting and Architect
Number of Offices: 0

OpenWebService.GetEmployeesByQuery performance

Postby calfred » Fri Nov 21, 2014 11:13 am

Hi,

This is more of an Open Web Services question, but I couldn't find a forum for that. Since it also involves a query of the Deltek Employees EM table, I thought I would post it here. If there's a better forum, let me know and I'll cross post there.

I have inherited a connector application, whose purpose is to synchronize changes to Deltek that were made in a 3rd party HR system. It is implemented in C# and runs as a separate process from both Deltek and the HR system and uses Open Web Services to interact with Deltek. As part of its operation, the connector uses Deltek Open Web Services to query the EM table. Prior to merging with another company, the EM table had 300-400 rows. Now it has about 1800.

The call: DeltekVisionOpenAPIWebService.GetEmployeesByQuery(“SELECT EM.* FROM EM”) throws a System.Net.WebTimeout exception after about 3 minutes. When I use SQL Server Management Studio to run the same query (same network, same client/server configuration), it completes successfully in a few seconds.

I ran a few other tests to select smaller subsets of the EM table (i.e. WHERE EM.LastName < '?''). The results of these tests were:

Where Result Open WS SQL # Rows
< 'G' success 58 sec 2 sec 538
< 'M' success 107 sec 2 sec 964
< 'S' timeout -------- 2 sec 1446

BTW, running in the Visual Studio debugger, it's clear that all of the time is being spent in the GetEmployeesByQuery() call (e.g. client Web Service or Server).

Does anybody have any thoughts on why the Open Web Services call is so much slower than the SQL client? I'd like to see if I understand the cause better, so can get this to work without having to replace the Web Services query with my own SQL call.

Thanks,
Charlie

calfred
Posts: 2
Joined: Thu Nov 20, 2014 5:31 pm
Brief Company Description: Product Development outsourcing (software, electrical and mechanical) primarily in Medical Devices, Industrial Equipment and Aerospace
Deltek Vision Version: 6.2
Number of Employees: 500
Company Position: VP Consulting and Architect
Number of Offices: 0

Re: OpenWebService.GetEmployeesByQuery performance

Postby calfred » Wed Nov 26, 2014 8:52 am

After some research, it turns out that the call to DeltekVisionOpenAPIWebService.GetEmployeesByQuery(), when passing "" as the third argument, selects data from the primary table ("EM") as well as all of its associated child tables. One of the child tables (EMProjectAssoc) was returning 340 KB of WS-XML data per employee. For 1800 employees that is about 612MB. Initially, this caused the TimeoutException, and after the Timeout limit was increased to 10 minutes, it caused an OutOfMemoryException

According to the Deltek WS documentation, changing the 3rd argument to "Primary" restricts the query to the main Employee table (EM). Now the response size is down to a manageable 5MB (2.8 KB per employee) and the query returns in under 5 seconds.

The bad news is that I also need to access *one* of the EM child tables (EmployeeCustomTabFields).

Does anyone know of a way to call DeltekVisionOpenAPIWebService.GetEmployeesByQuery() to either:
1. Include the EmployeeCustomTabFields table while excluding the others, or
2. Query all records in EmployeeCustomTabFields directly

Thanks,
Charlie


Return to “Custom Reporting & SQL Queries”

Who is online

Users browsing this forum: No registered users and 1 guest