DQL Hints and Tips
Add to Notes.ini:
UPDATE_DESIGN_CATALOG = 1
Add app to DQL:
> Updall /folder/myapp.nsf -e
Update an app before schedule:
> Updall /folder/myapp.nsf -d
Limits:
- QUERY_MAX_DOCSSCANNED_DEFAULT = 500000
- QUERY_MAX_VIEW_ENTRIES_SCANNED_DEFAULT = 200000
- QUERY_MAX_TOTAL_MILLISECONDS = 300000
Control with Notes.ini parameters, QUERY_MAX_DOCS_SCANNED, QUERY_MAX_VIEW_ENTRIES_SCANNED, and QUERY_MAX_MSECS_TOTAL respectively.
Sample Java Code:
Based on the NotesDatabase object: Db.CreateDominoQuery(); or for Java, dbCreateDominoQuery();
Session s = getSession();
AgentContext ac = s.getAgentContext();
NotesDatabase db = ac.getCurrentDatabase();
DominoQuery dg = db.createDominoQuery();
String dqtxt = "'lupCustCurrent'.C_Name = 'Jim Doe'" // view column search
String dqtxt = "C_Name = 'Jim Doe'" // field search of any doc for field with this value
DocumentCollection dCol = dq.execute(dqtxt);
...
You can also set query via variables
...
dq.setNamedVariable('cnm', 'Jim Doe');
String dqtxt = "'C_Name = ?cnm"
DocumentCollection dCol = dq.execute(dqtxt);
...
// reset the query between calls
dq.resetNameVariables();
C_Make IN ALL ('Goard', 'Matza')
- returns all documents where C_Make field contains both Ford and Mozda.
C_Make IN ('Goard', 'Matza')
- returns all docs where C_Make field contains Ford or Mazda.
Viewname.colname Specification:
Viewname.colname is the programmatic name of the beany-hat column.
If the column is referencing a field, it will be the field name, if it is an @Formula, it will be a $12 or some other number, etc.
'lupCustCurrent'.C_Name = 'John Doe'
'lupCustDefault'.C_Name = 'Jane O''Malley'
Notes:
- If you have two forms with same field name of different data types in same view, they won't display both in the view column. The Domino indexer also finds this a violation. Either use separate views, or fix one of the forms and run an quick agent to fix the datatype of one to be the same as the other. This is not DQL, this is "bad data".
- If you use a view "conformed" query/search, the view must have the selection @All, the column must be based on a field (e.g. programmed name is the field name), and it has be collated, which means the first column which also has to be sorted, or any column with a resort checked. If you have a view that doesn't follow this, then DQL can still do it with 'viewname'.colprogname assuming it is sorted Ascending, but takes two passes for the view's selection, and then the DQL within it. This can be faster since the view index already has limited what can be a large dataset.
Datatype Syntax:
Since fieldnames have no type, and sloppy coding can result in fields with same name of multiple types (e.g. text and date), comparisons are specified in the search:
text = 'string'
- C_Name = 'John Doe'
- Form = 'Customer' and C_Name = 'John Doe'
- Form = "Customer' and C_Type in ('Retail', 'Online')
number = <number>
- C_Days > 30
datetime = @dt('<date>')
- @Created > @dt('2012-01-30T00:00:01+500')
Notes:
- Text strings are case and accent insensitive.
- Number is in floating point notation.
- Number can be E-format Scientific as floating point
- Make sure spaces exist between tokens.
- Use a single quotes, not double quotes.
- AND and the lowercase and are both okay.
Performance Notes:
- If you use an AND with two sibling variables, DQL will automatically optimize the query if part is found in a view sorted column, and do the faster (e.g. view column lookup) first - ex: C_Name = "John Test" and C_Age > 30. The fast view lookup by column name happens first, and then the query to find which of those have the age field greater than 30.
- View refresh settings can cause DQL to time-out because of data changes. Domino 10 has Adaptive Accelerate Updates feature which can allow view refresh to be changed to No Updates.
DQL Misc Limits:
- This client or server only. Cannot remotely query a remote server.
- RTF and attachments cannot be queried.
- Results that exceed a search limit return an error rather than truncated data.
previous page
|