How best to provide sets of data as arguments to queries?

Neil_1

Problem

If I have a query, where I want to exclude some set of values from the result then I might want to inner join on a table containing that set of values. This has advantages over use of Where clause predicates, not least:

• as the cardinality of the set grows
• when using parameterised queries, where the cardinality of the set varies.

With SQL Server I can use 'Table Valued Parameters' (TVP) to achieve this (see http://msdn.microsoft.com/en-us/library/bb675163(v=vs.110).aspx). With this feature I can populate a table in ADO.NET, pass this as a single argument to my query, and then leverage it as a table in my query (eg to Inner Join to). I can also use this TVP to populate a temporary table, so that I can then leverage indexes, statistics, etc... to improve query plans.

Questions

1. What is the best approach to solving this problem in Vertica?
2. What other approaches might be valid solutions?
3. Do these approaches vary as the cardinality of the set to be passed to the query varies (eg: small vs large, statistics, etc...)?
4. What approaches to optimising this, both for the insert time, and the subsequent use in queries might be useful/considered?

Any help appreciated.

Daniel_Leybovic

Im afraid that External Procedures + some ODBC/JDBC/ADO module  the only choice you have.

Also take a look on Transform UDF + UDF parameters (it doesn't fit your requirements, but can be helpful when one or two parameters should be passed to function):
1. https://my.vertica.com/docs/6.1.x/HTML/index.htm#15767.htm
2. https://my.vertica.com/docs/6.1.x/HTML/index.htm#18083.htm