Depends on how you're set up. There is a setting to allow specifying those values that is meant for this type of bulk load scenario.
@Daniel Buxton : the multi-row insert syntax you're asking about is currently not supported by IRIS SQL (as you figured by now :-)). Robert's approach using UNION all is a creative workaround, but depending on where your big INSERT is coming from you might also just be able to generate / rewrite it into separate INSERTs? Once you have that in place, you can use the SQL Shell's RUN command or the ObjectScript $SYSTEM.SQL.DDLImport() method to execute your script file.
I don't want to discourage creativity, but this approach feels very risky to me. When you issue any SQL against this table before setting the % variable, it's going to cause ugly errors or unpredictable behaviour. I also wouldn't bet my money on this working in all possible parallel query execution scenarios (likely some, likely not all).
FWIW, within InterSystems development, we typically call % variables that survive between method calls a leak rather than single-rivet-keeping-your-skyscraper-together :-). Again, apologies for putting it a little strong here, but I think most use cases asking for flexibility can be addressed with more robust solutions, such as temporary tables, class inheritance (with the NoExtent keyword), etc.
If you need a programmatic entry point for dealing with complex filter conditions, take a look at %SQL.AbstractFind. It allows you to invoke ObjectScript code to populate bitmaps based on (any number of) user-provided arguments through a %FIND predicate. It is built on by iFind and this older (but worthwhile) community article on custom spatial indices, although in both cases it works hand-in-hand with a custom index (using %FunctionalIndex). That isn't required, but usually the flip side of the same use case coin.
at Global Summit 2019, we already showed some early prototypes of our forthcoming System Alerting and Monitoring tool that also leverages Prometheus and Grafana to monitor your IRIS deployments (recording is here). It's still a few weeks away, but this Tech Talk will discuss this subject in more detail.
coming late to the party, but just as a small and likely irrelevant note: if you use a PPG for storage and at some point (because the table appears big) the query optimizer thinks it's worth using parallel query execution, it won't see a thing in those worker processes. You should be able to avoid that using the %NOPARALLEL keyword though.
In addition to Eduard's response: sharding is a feature of our SQL / Objects layer. Raw globals are at a level below what we'd need for hooking in the workload distribution you're referring to.
indeed, system schemas (whose name starts with a % sign) require certain privileges (that you quickly get used to once you have them :-) ). That's why I suggested querying the INFORMATION_SCHEMA
FWIW, you can just put as many conditions as you want in the same SQL query. InterSystems IRIS includes an excellent SQL optimizer that will consider all the conditions (and other clauses) in your query and decide on the most efficient path to access the data.
Indeed, that's the recommended SQL way of achieving what Eduard described about PPGs. Drawbacks are that queries on these tables cannot be parallelized (as that implies multiple processes, of course).
Our TSQL support is meant for Sybase customers wishing to redeploy their TSQL applications on IRIS (especially now that SAP/Sybase is terminating support for those platforms). Just temporary table support by itself wouldn't be a reason to start building TSQL applications and abandon IRIS SQL/ObjectScript, of course :-). However, for a recent TSQL migration we did some work on our TSQL temp table support and were considering to roll that out to regular IRIS SQL, so this thread is a good place to share your experiences and requirements so we can make sure to do that properly, as needed.
go to post
Depends on how you're set up. There is a setting to allow specifying those values that is meant for this type of bulk load scenario.
@Daniel Buxton : the multi-row insert syntax you're asking about is currently not supported by IRIS SQL (as you figured by now :-)). Robert's approach using UNION all is a creative workaround, but depending on where your big INSERT is coming from you might also just be able to generate / rewrite it into separate INSERTs? Once you have that in place, you can use the SQL Shell's RUN command or the ObjectScript $SYSTEM.SQL.DDLImport() method to execute your script file.
go to post
I don't want to discourage creativity, but this approach feels very risky to me. When you issue any SQL against this table before setting the % variable, it's going to cause ugly errors or unpredictable behaviour. I also wouldn't bet my money on this working in all possible parallel query execution scenarios (likely some, likely not all).
FWIW, within InterSystems development, we typically call % variables that survive between method calls a leak rather than single-rivet-keeping-your-skyscraper-together :-). Again, apologies for putting it a little strong here, but I think most use cases asking for flexibility can be addressed with more robust solutions, such as temporary tables, class inheritance (with the NoExtent keyword), etc.
go to post
If you need a programmatic entry point for dealing with complex filter conditions, take a look at %SQL.AbstractFind. It allows you to invoke ObjectScript code to populate bitmaps based on (any number of) user-provided arguments through a %FIND predicate. It is built on by iFind and this older (but worthwhile) community article on custom spatial indices, although in both cases it works hand-in-hand with a custom index (using %FunctionalIndex). That isn't required, but usually the flip side of the same use case coin.
go to post
Hi Hansel,
at Global Summit 2019, we already showed some early prototypes of our forthcoming System Alerting and Monitoring tool that also leverages Prometheus and Grafana to monitor your IRIS deployments (recording is here). It's still a few weeks away, but this Tech Talk will discuss this subject in more detail.
Thanks,
benjamin
go to post
coming late to the party, but just as a small and likely irrelevant note: if you use a PPG for storage and at some point (because the table appears big) the query optimizer thinks it's worth using parallel query execution, it won't see a thing in those worker processes. You should be able to avoid that using the %NOPARALLEL keyword though.
go to post
In addition to Eduard's response: sharding is a feature of our SQL / Objects layer. Raw globals are at a level below what we'd need for hooking in the workload distribution you're referring to.
go to post
indeed, system schemas (whose name starts with a % sign) require certain privileges (that you quickly get used to once you have them :-) ). That's why I suggested querying the INFORMATION_SCHEMA
go to post
and if you prefer something more database-independent, you can use the standard INFORMATION_SCHEMA package:
go to post
FWIW, you can just put as many conditions as you want in the same SQL query. InterSystems IRIS includes an excellent SQL optimizer that will consider all the conditions (and other clauses) in your query and decide on the most efficient path to access the data.
go to post
Indeed, that's the recommended SQL way of achieving what Eduard described about PPGs. Drawbacks are that queries on these tables cannot be parallelized (as that implies multiple processes, of course).
Our TSQL support is meant for Sybase customers wishing to redeploy their TSQL applications on IRIS (especially now that SAP/Sybase is terminating support for those platforms). Just temporary table support by itself wouldn't be a reason to start building TSQL applications and abandon IRIS SQL/ObjectScript, of course :-). However, for a recent TSQL migration we did some work on our TSQL temp table support and were considering to roll that out to regular IRIS SQL, so this thread is a good place to share your experiences and requirements so we can make sure to do that properly, as needed.