Skip navigation

Correct Web Service Query Behaviour for Join Forms

score 20
You have not voted. On Roadmap

Web services against join forms result in N+1 number of SQL calls to the database if there are N records returned. This should be consolidated into a single call against the SQL database.


Example: We have integrations to several CMDB classes which serve information to internal and external partners. Some of these queries can return 100-200 records in a single call. In these situations hundreds of SQL calls are being made against our database, and in certain scenarios timing out due to the total cost of these queries combined. A 1 second query multiplied by 100 records puts the total cost over 1 minute. When a proper query can be written to return the same data in 1 second this is unacceptable.


To illustrate this behaviour you can consume an ITSM web service such as HPD_IncidentInterface_WS, using the HelpDesk_QueryList_Service method. Simply provide it a qualification which returns a set of data.


Qualification: 'Reported Date'  > "10/1/2012 12:00:00 AM" on my test system would return 798 Incidents


The resulting SQL queries would be: 1 Query to retrieve the dataset + 798 Queries to retrieve the details for each record, all against T1854.


Vote history