OnDemand User Group
Support Forums => OD/WEK & JAVA API => Topic started by: Andreas Baaserud Hauge on March 08, 2023, 01:28:38 AM
-
Hi,
In the documentation https://cmod.wiki/dox/RESTapiDoc/CMOD_10_5_REST_API_Documentation.html chapter 3.5.2. Search with SQL it says
When using search with SQL, it is imperative you include a segment start and stop date when available. The format of the start/stop dates is %Y-%m-%d. Failure to include a segment feild will result in a search across all application group data tables severly impacting system performance.
My question is, how do I know what segment start and stop date to use?
Failure to include a segment feild will result in a search across all application group data tables severly impacting system performance.
Hence that quote, it seems like I should be able to know what segment start and stop date I can use for a query.
In this sample request, the startdate and stopdate spans over 27 years. 27 years of records seems a bit odd to search through if the point of segment startdate and stopdate is to narrow the search down to increase performance.
POST /cmod-rest/v1/hits/ HTTP/1.1
usi-date: 2020-02-25T00:31:30Z
Authorization: CMODSharedKey externpool1-P0mFoCU5H83lN9uQcRUA:5au9U9epefygVH22mKfR8yGkS+XiA0D8CDmcOvZrt58=
Content-Type: application/json; charset=UTF-8
Host: localhost
Content-Length: 191
{
"startdate" : "1993-01-01",
"stopdate" : "2020-02-11",
"query" : "where name like 'VILL%'",
"folder" : "Baxter Bay CC Statements",
"applicationgroup" : "Baxter Bay Credit"
}
-
Andreas,
This is a bad example. Will update the docs to make this more meaningful.
Thank you,
Rob
-
Thanks Russel,
Do you mind also explain how we can use the segment startdate and stopdate?
I dont get how I should get that value to make the search more efficient.
-
Why do you need to search with SQL?
-
Good question, is it more efficient to not use the Search with SQL and rather use /cmod-rest/v1/hits/<folder> ?
Cant see we specify segment startdate and stopdate in /cmod-rest/v1/hits/<folder> search.
in the background maybe the segment startdate and stopdate are used ? do you know what those values would typically be?
-
Sounds like you don't search with SQL. Just user /cmod-rest/v1/hits/<folder> and specify a date field (that is defined as a segment field in the CMOD admin) as part of your search criteria and you should be good. CMOD will handle the rest for you.
-
I'd like to request robust SQL support with access to all fields in the table, for those of us who do "interesting" things. :)
-JD.
-
CMOD will handle the rest for you.
Thats fine, I just dont get how this work.
I have a segment date field, when I use startdate = 2023-02-25 I still get hits where I have records contains date = 2021-08-04
Searching without SQL seems to be extremely slow compared to Search with SQL.
Did a few test request now.
- Search with SQL = 59ms
- Search without SQL = 1677ms. Even up to 3s
Total number of hits = 1
If you know how we can use segment startdate and stopdate in Search with SQL - I'd very much like to know :D
-
I'd like to request robust SQL support with access to all fields in the table, for those of us who do "interesting" things. :)
-JD.
So not just SQL support. You want it robust? :)
I hereby certify all current SQL support as robust. There you go. Ask and you shall receive.
BTW: All kidding aside. What fields do you not believe you have access to via SQL?
-
CMOD will handle the rest for you.
Thats fine, I just dont get how this work.
I have a segment date field, when I use startdate = 2023-02-25 I still get hits where I have records contains date = 2021-08-04
Searching without SQL seems to be extremely slow compared to Search with SQL.
Did a few test request now.
- Search with SQL = 59ms
- Search without SQL = 1677ms. Even up to 3s
Total number of hits = 1
If you know how we can use segment startdate and stopdate in Search with SQL - I'd very much like to know :D
The short answer is CMOD uses the segment startdate and stopdate to limit what tables it searches across. If an AG has 100 tables in it, using a segment field can help significantly. If you only have one table for a given application group this will not of course help much.
So, searching a folder with a date field that is also defined as a segment field will cause cmod to first identify what data tables fall in the given range and then performs a search only against those tables.