We’ve developed some resources to help you work effectively from home during COVID-19 Click to learn more

Performance in REST API -

Hi,

Do we have any restrictions in SuperOffice REST-api, regarding to performance ?

I mean do we have a max concurrent request setting, any other setting in IIS , datbase etc that can be altered?
Or does the max transactions per second determines by the underlaying HardWare only.

I am doing some Load-Tests with SOAP-UI. 
If just runing 20 threads(user) 1-20 thread for 2 minutes, I MAX my server out around 70-73 transactions per second.
https://<server>/sales/api/v1/contact/5

That is quite ok.

But when doing the same test, but searching for number 2:
https://<server>/sales/api/v1/contact?$filter=number='30000003'

the transactions per second drops to around 15. 

problem is that if building a FrontEnd API that calls SuperOffice API we often needs to add several calls to get a proper result back. Eg if returning a company that has External Table data (we first need to find those)

SO for my client we/they do need to send 4 Request to fetch a customer with all data , that we need for one scenario.
Search defined aproximatly as follows:
Get-external-Id-Type (Need to fetch what type of extrernal Id we have, type of Extratable entry)
Get-Contact-Id-By-External-Id (value in Extratable connected to person, ) run to get get what company to fetch
Get-Person-By-Contact-Id (fetch persons on the company)
Get-Contact-By-Id (used to fetch the company out from search of person)

In this above scenario we MAX out the server and "only gets" 4 transactions / second.

Server Spec for test-rig:

4VCpus (CPU- E5-2690 v4)
16 GB RAM
SQL-server 2016 with 16 RAM

Anyone with thoughts / tips etc on above are welcome to jump into the discussion.
Could indexes in the database on the extra-table rows help etc? 
Any other things to consider to bump upp the tps-value for the scenario? 

Best regards

/Anders

RE: Performance in REST API -

Hi Anders,

Initially, the APIs evolved as a consequence of the SuperOffice user interface. As searching across entities and lists became more relevant, so did the APIs to support both simple and complex searches via archive providers. Back in the day these were powerful and fast enough for a most cases, but since Extra Tables came along, there hasn't been any real effort to integrated effort to tie existing providers to extra tables.

Then there is the dynamic archive provider, created as a "catch-all" provider for queries that need to select data from sources not readily available by any of the existing providers. This provides the closest thing to supplying adhoc SQL queries.

It would seem your case could benefit from use of the dynamic archive provider, with the potential of eliminating one or more requests. It would be so much more helpful, for me at least, if you could provide a more concrete SQL-like example of what you are trying to accomplish, thereby receive a more concreate implementation.

With direct access to the database, what would the least amount of queries look like to accomplish your goals? From that, it would be clearer which provider(s) to use, or how best construct an adhoc query using the dynamic archive provider.

Best regards

 

Av: Tony Yates 25. feb 2020

RE: Performance in REST API -

Thanks Tony,

Ok I understand.

I will provide a better overview of the extra-tables involved and how they are connected to standard crm-tables along with a SQL-Query to do the same search as described in above scenario 

Customer is building their own facade and publish that in their own API-manger for other internal systems to integrate with so I do not know exactly how they have built it. But will investigate further. I do know they have multiple ways to integrate with the facade.

Eg, create update and search for companies and contact persons.(all based on REST-Only calls to SuperOffice)

Dynamic Archive Provider seems promising.

Best regards

/Anders

Av: Anders Larsson 26. feb 2020

RE: Performance in REST API -

Hi Tony,

Some thin like this, is how I would do it in SQL

eg a search on field value in an external table connected to a person record.

select --distinct
con.number2 as CustomerNumber, con.name as CustomerName, per.firstname as PersonFirstName, per.lastname as personLastName, eml.email_address, _phone.phone from crm8.CONTACT con
left join PERSON per on per.contact_id = con.contact_id
left join y_external_identity ext_id on ext_id.x_ext_contact_person_id=per.person_id
left join EMAIL eml on eml.person_id=per.person_id
left join Phone _phone on (_phone.owner_id =per.person_id) and (_phone.ptype_idx =16389) --Mobile
-- left join Y_CUSTOMER_ADDRESS cus_adr on cus_adr.x_customer_id = con.contact_id

where
ext_id.x_ext_external_identity_value = 'USR1-998877' 

 

/Anders

Av: Anders Larsson 3. mar 2020

RE: Performance in REST API -

Hi Anders,

So I created an extra table named External Identity and added one field Contact Relation (for person) and one field for the criteria value in your example.

I then populated the tables with the following values:

Then issue the following dynamic archive provider query via REST (broken down here for readability):

/api/v1/Archive/dynamic?
$select=
y_external_identity.x_ext_contact_person.contact_id.number2,
y_external_identity.x_ext_contact_person.contact_id.name,
y_external_identity.x_ext_contact_person.firstname,
y_external_identity.x_ext_contact_person.lastname,
y_external_identity.x_ext_contact_person.emailAddr­ess,
y_external_identity.x_ext_contact_person.mobile/formattedNumber
&$filter=
y_external_identity.x_ext_external_identity_value eq 'USR1-998878'

And get the following expected results:

Special shoutout to Frode Lillerud for helping with the table joins!

Hope this helps!

Av: Tony Yates 12. mar 2020

RE: Performance in REST API -

If you are adding extra tables that relate to contact and/or projects, they will be automatically picked up and glued onto the Contact and Person archives.

The Person archive provider:

adds y_equipment fields to the archive. You can use these fields as search restrictions.

 

Av: Christian Mogensen 12. mar 2020

RE: Performance in REST API -

Hi!

Thanks Tony and Christian.

I have tested and it seem to work.

Follow up question:

It is great to be able to find person/contact data when searching for data and using extra table as filter.

How about to also fetch more data from antother extra table connected to the Company , that the person found was connected to?

for instance if you have extra table called Y_CUSTOMER_ADDRESS 

preferably returned as an under array / list of rows, representing the connected addresses of the company.

Best Regards

/Anders

Av: Anders Larsson 12. mar 2020

RE: Performance in REST API -

Hi Anders,

OK, so I have made certain assumptions to draw a conclusion as to what you want.

You can lump this all together to get contact, person, email address, phone numbers and extra table rows back based on another extra table's criteria - that contains relational records. However, I'm not convinced this is the most efficient way to go about it.

First, let's get what you asked for.

/api/v1/Archive/dynamic?
$select=
y_external_identity.x_ext_contact_person.contact_id.number2,
y_external_identity.x_ext_contact_person.contact_id.name,
y_external_identity.x_ext_contact_person.firstname,
y_external_identity.x_ext_contact_person.lastname,
y_external_identity.x_ext_contact_person.emailAddr­­ess,
y_external_identity.x_ext_contact_person.mobile/formattedNumber,
y_external_identity.x_ext_contact_person.contact_id.(y_customer_address->x_customer_id).x_address_info
&$filter=
y_external_identity.x_ext_external_identity_value eq 'USR1-998877'

With only one record in the y_customer_address table, the results appear as:

 

The moment you introduce two records, the results appear as expected:

So, the duplication of result data is probably not what you want. You stated that you wanted the address info "as an under array / list of rows". The only way you will get that is but submitting a second "trimmed" query that returns only the y_customer_address columns. 

Shout out to Michel Krohn-Dale for his dot syntax help!

Hope this helps!

Av: Tony Yates 13. mar 2020

RE: Performance in REST API -

Hello,

The number2 field is available in the providers, its named 'number', (and number1 is named 'code').

See field 'personContact/number' on the FindPerson provider as example.

Av: David Hollegien 13. mar 2020

RE: Performance in REST API -

Doh! I was thinking that had been attended to awhile ago, but since its not explicitly named in then API I guess I forgot..

Av: Tony Yates 13. mar 2020

RE: Performance in REST API -

Thanks for the answers!

I think this will help for now

What I ment with an array of adress (fetched from Extra Table) was something like this

{
"CompanyId": 29228,
"CustomerNo": "30000003",
"CompanyName": "Amesto Test01",
"Department": "Testavdelningen ABC - after 8.5 final kdb v7 - ink aviseringsnamn",
"CompanyEmail": "added.epost@amestotest.se",
"y_customer_addresses": [
{
"id": 75,
"adr_AddressType": "Postadress",
"x_street_or_box": "Amesto Box 667",
"x_zip": "118 67",
"x_city": "Stockholm"
},
{
"id": 76,
"adr_AddressType": "Besöksadress",
"x_street_or_box": "Amestovägen 666A",
"x_zip": "118 63",
"x_city": "Stockholm"
},
{
"id": 52179,
"adr_AddressType": "Faktureringsadress",
"x_street_or_box": "Amesto Box 667",
"x_zip": "118 67",
"x_city": "Stockholm"
},
{
"id": 52204,
"adr_AddressType": "Leveransadress",
"x_street_or_box": "Testvägen 666",
"x_zip": "18788",
"x_city": "Täby"
}
],
"PersonId": 2575,
"CompanyId": 29228,
"FirstName": "Test",
"LastName": "Person1"
}

 

 

/Anders 

Av: Anders Larsson 13. mar 2020