ODWek REST - Search with SQL - Segment start and stop date

[Andreas Baaserud Hauge]

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.

Code Select Expand

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"
}

[rjrussel]

Andreas,

This is a bad example. Will update the docs to make this more meaningful.

Thank you,
Rob

[Andreas Baaserud Hauge]

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.

[rjrussel]

Why do you need to search with SQL?

[Andreas Baaserud Hauge]

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?

[rjrussel]

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.

[Justin Derrick]

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.

[Andreas Baaserud Hauge]

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

[rjrussel]

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?

[rjrussel]

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.