Date range queries going too slow for you? SQL Performance got you down? I have one weird trick that might just help you out! (SQL Developers hate this!)*
If you have a class that records timestamps when the data is added, then that data will be in sequence with your IDKEY values - that is, TimeStamp1 < TimeStamp2 if and only if ID1 < ID2 for all IDs and TimeStamp values in table - then you can use this knowledge to increase performance for queries against TimeStamp ranges. Consider the following table:
Hi, I am trying to execute a store procedure within an ensemble operation to connect to aMsSQL database and I am having problem to pass the input arguments... I have tried several things but only one seems to work and it is not the ideal solution.
In addition to its general security, Caché offers SQL security with a granularity of a single row. This is called row-level security. With row-level security, each row holds a list of authorized viewers, which can be either users or roles. By default access is determined at object modification Some time ago I became interested in determining row-level security at runtime. Here's how to implement it.
Hi guys, I ran into a strange (for me) situation, when I run same query but change the WHERE clause the plan is different and is not connected to the additional condition. Query that doesn't use the necessary index:
If you are looking to breathe new life into an old MUMPS application follow these steps to map your globals to classes and expose all that beautiful data to Objects and SQL.
If the above does not sound familiar to you please start at the beginning with the following:
Most of my classes are mapped from Globals. I want to access Cache classes from a BI software through ODBC connection.
'Last update' information does not exist in most of the classes. My question is whether there is a 'last update' timestamp that is automatically generated for each line in classes I can extract to external systems?
Class ICT.Experiments.A Extends %Persistent
{
Property Name As %String;
Property Collection As list Of ICT.Experiments.B;
Property Collection2 As array Of ICT.Experiments.C;
}
Class ICT.Experiments.B Extends %Persistent
{
Property Name As %String;
}
Class ICT.Experiments.C Extends %Persistent
{
Property Name As %String;
}
I have the classes above and I can select columns from the array collection by using two joins as follows:
I don't want to take all results from the table and orgainze them manually, for example: I have a table with 50 records, but I only want to select 10 records, being from tenth until the twentieth and this without knowing their IDs.
So, how can I do this, without losing performance, there is any way?
I'd like to know if there are any issues if an index is inserted into a table without running the %BuildIndices() method.
It's important to note that data inserted before the index is not important for retrieval, so it's not a problem data inserted before the index don't show up in queries.
The reason why I'm asking this is that I'd like to avoid index reconstruction on big tables which I need to inser such index.
Cache ODBC State S1000 Native Code 400 Illegal Value
This is my query:
select DateTijdSec from GLPPatTcActie where pnr = '27085070017' and LTestId->Makey='BLA' and VerzamelDatTijd < '2021-03-04-2021 09:04' and glpactieid->makey in ('TAV','TMA') order by DateTijdSec desc
Most likely there is a wrong date time in the table, how can i get the data?
I have a query that I am attempting to run, but inner query is being rolled up into the overall query - per the optimizer (and jobexam observation). While I know that if the inner query runs first, I will eliminate a number of records (making the result set smaller, and return quicker).
An interesting pattern around unique indices came up recently (in internal discussion re: isc.rest) and I'd like to highlight it for the community.
As a motivating use case: suppose you have a class representing a tree, where each node also has a name, and we want nodes to be unique by name and parent node. We want each root node to have a unique name too. A natural implementation would be:
I am tasked with using CACHE to insert data retrieved from a CACHE data base and insert it into an sql database. The columns in the sql table that I am trying to insert data into have names that contain underscores such as "ACCESSION_DATE" I found a utility in CACHE to connect to the sql data base and perform inserts. I have the data I need to insert. I need to pass the data into the sql utility referencing the column names.
I tried using indirection to set the data into underscored variables, but that isn't allowed in cache object script.
I'm using a JDBC driver to connect PGSQL -> Cache. I'm noticing when I run SELECT queries and COUNT(*) command against the same table, I get different result sets. I'm pretty new to Cache in general - so I'm trying to understand why these would be different.
Examples (TransID and InvNum should occur in every "row"):
SELECT COUNT(*) FROM ACCT.Services = 1,090,324 WHERE ACCT.Dept = 483
SELECT TransID FROM ACCT.Services = 1,085,776 WHERE ACCT.Dept = 483
SELECT InvNum FROM ACCT.Services = 586,023 WHERE ACCT.Dept = 483
I'm working on integrating a Cache database into an existing .NET project of mine. I'm trying to execute certain queries utilizing the Cache ODBC driver. The first error I ran into was "[S1000] [Cache ODBC][State : S1000][Native Code 417] Access Denied". The part that's throwing me for a loop though is that the application ran ~200 successful queries before I started getting that. After some of those Access Denied errors, they turned into "[Cache ODBC][State : S1T00][Native Code 450] Request timed out due to user timeout". This was all with the regular (2.5?) driver.
From within the context of an instance of EmbedObj, how can I navigate to the containing instance of "ContainterObj" and find that value of its Foobar property?
A good writer is supposed to draw you in with the title and bury the answer somewhere in the article. I suppose this makes me a bad writer – don’t think less of me, my whole feeling of self-worth comes from the opinions of internet strangers!
By replies
.png)