Hello! Is cloveretl support bind variables in SQL queries? For example, I have input metadata for DBInputTable with 20 000 queries like "select userid from users where username = 'Alex'". I'd like to use something like this "select userid from users where username = ?" and just pass variables into query. Hard parse of 20 000 queries is extremly slow. Thanks.
-
Hi,
According to the manual DBInputTable accepts just SQL queries on the input port. So it is not possible to map input data to a query inside the DBInputTable. However, you can easily prepare your queries in CTL, for example in Reformat preceding the DBInputTable.
Regarding the performance issue, could you please send me the graph and answer the following?
How many records is fetched at once?
How long does it take to process all queries?
How many records in total are selected by those 20 thousand queries and how big are they?
Thank you! -
You may also look at DBJoin component. That is doing exactly what you asked - allows to set SQL query with parameters and then supply them through input port. For each record, query is executed and results sent out - actually the input record & record from DB would go to Transform function first, from which data can be sent out.
BTW: the DBJoin component is not present in Community version of CloverETL.
Please sign in to leave a comment.
Comments 2