Fraud Query Timing Out

Reported Issue

Some users noticed that the delivered, custom SQL Fraud Query had become so slow that it timed out before completion.

What We Found

The delivered query had some duplicate table hits and referenced some XML metadata columns on materials that stopped being populated in late 2017.

At some point since this article was written, Technolutions updated the Fraud Query in the Standard Query Library. The new query is more efficient and also stopped querying document metadata, which was apparently deprecated in late 2017.

The ReSolution

By rewriting the query to reduce duplicate table hits, we were able to make it work again.

Another improvement we made was to output the query results to a Materialized View, then query the view from a regular CJ query. The CJ frontend makes it easy for users to filter the results and add additional, standard exports. We further improved the query by cross-referencing Ping data.

One downside of pre-computing the results is that they are not longer real-time, but highly selective schools who care deeply about fraud tend to have slower processing times than open-access schools.

Because of the sensitive nature of this query, we won't share it here, but contact us if you'd like a copy.

Related Links

Jasmine Solomon over at NYU shared tools for identifying similar fraud once an initial case is identified in Identifying Fraud Cases - Query Library.