The case of the slow-poke query

The Geotab Software Development Kit and the API are used by developers to add power and flexibility to their Geotab solution by automating tasks and facilitating ways to work with data.

Recently, I received an interesting question from someone using the MyGeotab API (Application Program Interface). The user was seeing less than optimal performance from a request that would return a small amount of results. The application would make a MultiCall with nine requests for status data using a search with diagnostic, from and to date. The user would repeat this request at an interval of about every 10 minutes to retrieve any new results since the date of the last call. This request would take much longer than expected based on the number of results returned and prompted me to do a little analysis to see how we could improve performance.

Performance Analysis

We did some testing and found that duration values for the requests were set at 10 minute polling intervals on average. Tests were run against a database with approximately 300 live vehicles and 300 historic vehicles, with a fair amount of historic status data (about 45,000,000 records).

For our test, we polled the database on 10 minute intervals using three different approaches. First, we made the request in the same manner as the suspicious call: Get(type:status data, diagnostic: n, from date:x, to date: y) in a MultiCall for nine different diagnostics. This search will check for any status data between the provided dates, for the provided diagnostic for all devices.

We then introduced a device search. This meant that instead of querying for “all devices,” we made nine MultiCalls for each device. Finally, we tried to call the GetFeed(fromVersion: x). This call would not limit the status data to the nine diagnostics. It returned any new status data since the last time we called.

Test Results

Notes:

• Approach 1: This search does not use a table index in the database and causes a table scan. This could be prone to blocking/deadlocking on moderately sized, active databases as status data is regularly added to the table. We saw that the “Get” query would run slowly, delay inserting status data and sometimes become a deadlock victim.
• Approach 2: This search is indexed and may be less prone to blocking and deadlocking, but the number of requests required is not scalable and takes longer than we would like. It also uses more server resources. If our database grew to 1000 devices, we would have 9000 API calls to make.
• Approach 3: This approach was the fastest even though it returns a larger amount of data – for all diagnostics, not just the nine we want to know about. For this reason we simply looped through the results and discarded any results that were not for our nine diagnostics.

Solution

Our goal was to carefully measure different approaches and use what the most efficient and reliable way to access the data needed for this application. In this case they decided to modify their code and use a feed of status data, discarding data that is not related to the diagnostics they are interested in. Switching to use ‘get feed’ has shown a great improvement in the performance of their application and less pressure on the server.

Lessons Learned

The results highlight some important factors to take into account when choosing what call and/or search to use for a particular task. The SDK feed API’s are optimized for high performance. You should consider the API reference notes about efficient ways to search for the type, how many calls/queries will be executed, and if it is faster to filter results in the app rather than on the server. To aid in this decision we added notes to the search object documentation describing what combinations of parameters the table is designed to be efficiently queried with.

For more tips on using the API:

• Check out the Geotab DEV Channel – a video site that assists business software application developers in the use of the Geotab Software Development Kit (SDK).
   
• Connect with the Geotab Community at the SDK Forum.
   
• Read Better Practices for the MyGeotab API and Continuous Integration in MyGeotab on the Geotab blog.

Please leave a comment in the box below if you have any tips or questions on how to use the API efficiently.