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]

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

[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

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.