0

I was wondering if there was a way to perform a make table query in Guidewire so that we can perform faster subsequent queries on it.

Our main goal is to improve performance on record retrieval, at the current state we're doing (kinda) as follows:

SELECT * WHERE x ='y'AND a='b' for record of type 1

SELECT * WHERE x ='y'AND a='c' for record of type 2

SELECT * WHERE x ='y'AND a='d' for record of type 3

These queries are executed rather often, which results in a waste of time querying SQLServer for SELECT * WHERE x ='y' on the whole table. But I haven't found a way to perform a table query (or anything that helps us) through the gw.api

Pldx
  • 87
  • 1
  • 3
  • 6
  • 1
    Could you provide some gw.api code and the outcome sql statements? – hakamairi Feb 18 '21 at 09:34
  • I'm not sure about what the problem is... could you please provide more info?. If you hare facing performance issues with a query like this: `SELECT * WHERE x ='y'AND a='d`, you probably need to index including these two columns... you mention is a frequently executed query after all... – robertant Aug 12 '21 at 21:46

2 Answers2

3

Guidewire / Gosu has an extensive Query Builder library, The query builder APIs provide support for retrieving information from InsuranceSuite application databases.

You would be looking for something like this

var query = Query.​make(entity-type)
.compare(Entity#x, Relop.Equals, "a")
.compare(Entity#y, Relop.Equals, "b")
query.​select()

Guidewire Documentation on the Query Builder is available here for ClaimCenter. You may need credentials to access the docs: https://docs.guidewire.com/cloud/cc/202302/integration/integration/topics/querybuilderapi/c_ba3266101.html

Terence Stephens
  • 235
  • 1
  • 13
1

So sorry, i didn't have access to my system for a while so i couldn't create the examples

You can create a main query (e.g. the Contact query) and then tack on subsequent queries to give you the x=y followed by a=b. See below

uses gw.api.database.Query
uses gw.api.database.Relop

var contact = Query.make(Contact) //x=y
var company_manning = contact.compare(Contact#Subtype, Relop.Equals, TC_COMPANY).startsWith(Contact#Name, "Man", false).select()
company_manning.each(\elt -> print(elt))

One thing to note, is that each time you use the main query, you need to re-construct it because the select() uses that query.

contact = Query.make(Contact) //x=y
var person_startswithC = contact.compare(Contact#Subtype, Relop.Equals, TC_USERCONTACT).select()
person_startswithC.where(\t -> t typeis Person && t.FirstName.startsWith("C")).each(\elt -> print(elt))

For further reading on performance and the likes I wrote 2 articles on the Query commands, if you don't have access to guidewire docs. This also gets into the performance aspects of the sql.

Performant queries

Deeper Performant queries

Troy B. Stauffer
  • 51
  • 7