[stevensi1018]

We have more than 10 000 cases and I'm using the API to get all cases. The problem is that the request seems really long (35-45 seconds) to even execute. Even if I put a limit of 10 results sometimes.
I'm using the /cases/advanced-search?limit=(number) endpoint.

I'm using PHP with curl. I have no problem parsing the data after, it's really fast but executing the request kills it for me. Any advice on anything to speed up this request or another endpoint that gives all the data of the cases ?

(I can't load dynamically the request right now, I need to load it all at once).

Thanks

[amosbatto]

Your best option is to write code to directly query the APP_CACHE_VIEW table in the database. See this PHP tutorial:
https://www.siteground.com/tutorials/ph ... able-data/

Another option is to install my extraRest plugin and modify the source code of the GET extrarest/sql endpoint to query the APP_CACHE_VIEW table. See:
https://www.pmusers.com/index.php/Extra ... rest.2Fsql

[stevensi1018]

I managed to do a bigger query in MySQL to get all cases even if there are multiple open steps in the case, thanks !! :

Code: Select all

SELECT ACV.APP_UID,ACV.APP_NUMBER,ACV.APP_STATUS,ACV.APP_TITLE,ACV.APP_PRO_TITLE,ACV.APP_TAS_TITLE,
        ACV.APP_CURRENT_USER,ACV.DEL_PRIORITY,ACV.APP_UPDATE_DATE,ACV.USR_UID,A.APP_INIT_USER,ACV.DEL_TASK_DUE_DATE
FROM APP_CACHE_VIEW ACV
INNER JOIN (
    SELECT APP_UID, MAX(DEL_INDEX) MAX_INDEX
    FROM APP_CACHE_VIEW
    GROUP BY APP_UID
) MAX_CACHE_VIEW ON ACV.APP_UID = MAX_CACHE_VIEW.APP_UID AND ACV.DEL_INDEX = MAX_CACHE_VIEW.MAX_INDEX
INNER JOIN APPLICATION A ON A.APP_UID=ACV.APP_UID
WHERE
CASE
	WHEN ACV.APP_STATUS='TO_DO' THEN ACV.DEL_THREAD_STATUS='OPEN'
    WHEN ACV.APP_STATUS<>'TO_DO' THEN ACV.DEL_THREAD_STATUS='CLOSED'
END
UNION
SELECT ACV.APP_UID,ACV.APP_NUMBER,ACV.APP_STATUS,ACV.APP_TITLE,ACV.APP_PRO_TITLE,ACV.APP_TAS_TITLE,
        ACV.APP_CURRENT_USER,ACV.DEL_PRIORITY,ACV.APP_UPDATE_DATE,ACV.USR_UID,A.APP_INIT_USER,ACV.DEL_TASK_DUE_DATE
FROM APP_CACHE_VIEW ACV
INNER JOIN APPLICATION A ON A.APP_UID=ACV.APP_UID
WHERE ACV.DEL_THREAD_STATUS='OPEN'

[amosbatto]

That is a beast of a query! How many seconds does it take to query all 10000 records?

[stevensi1018]

That is a beast of a query! How many seconds does it take to query all 10000 records?

It takes less than 6 seconds to query 13649 records which is pretty fast in my opinion giving all the info that I have