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


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.

  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.


Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file