Function to convert from Comma seperated string list to IN predicate set (1, 2, 3, n) [CORE3155] #3531
Comments
Modified by: Tim Kelly (m00bh000)description: I execute the following query often: Select * FROM PEOPLE WHERE REGION_ID IN (:A, :B, :C) but the number of params which make up the IN predicate of the query changes very often, so the next time it might be: Select * FROM MY_TABLE WHERE REGION_ID IN (:A, :B, :C, :D, :E) This makes the query impossible to "PREPARE" / cache since there are many different versions of it depending upon the number of paramaters that are required. It would be much better just to have one query with a parameter which is a string-list, which could be converted into a set (as required by IN) using a function, say STR_LIST_TO_SET(<string_list>): Select * FROM MY_TABLE WHERE REGION_ID IN STR_LIST_TO_SET(:A) Why don't I just use regular-expressions? Because experimenting they are grossly inefficient compared with using IN \in this type of scenario. What do people think? => I execute the following query often: Select * FROM PEOPLE WHERE REGION_ID IN (:A, :B, :C) but the number of params which make up the IN predicate of the query changes very often, so the next time it might be: Select * FROM MY_TABLE WHERE REGION_ID IN (:A, :B, :C, :D, :E) This makes the query impossible to "PREPARE" / cache since there are many different versions of it depending upon the number of paramaters that are required. It would be much better just to have one query with a parameter which is a string-list, which could be converted into a set (as required by IN) using a function, say STR_LIST_TO_SET(<string_list>): Select * FROM PEOPLE WHERE REGION_ID IN STR_LIST_TO_SET(:A) Why don't I just use regular-expressions? Because experimenting they are grossly inefficient compared with using IN \in this type of scenario. What do people think? |
Modified by: Tim Kelly (m00bh000)description: I execute the following query often: Select * FROM PEOPLE WHERE REGION_ID IN (:A, :B, :C) but the number of params which make up the IN predicate of the query changes very often, so the next time it might be: Select * FROM MY_TABLE WHERE REGION_ID IN (:A, :B, :C, :D, :E) This makes the query impossible to "PREPARE" / cache since there are many different versions of it depending upon the number of paramaters that are required. It would be much better just to have one query with a parameter which is a string-list, which could be converted into a set (as required by IN) using a function, say STR_LIST_TO_SET(<string_list>): Select * FROM PEOPLE WHERE REGION_ID IN STR_LIST_TO_SET(:A) Why don't I just use regular-expressions? Because experimenting they are grossly inefficient compared with using IN \in this type of scenario. What do people think? => I execute the following query often: Select * FROM PEOPLE WHERE REGION_ID IN (:A, :B, :C) but the number of params which make up the IN predicate of the query changes very often, so the next time it might be: Select * FROM PEOPLE WHERE REGION_ID IN (:A, :B, :C, :D, :E) This makes the query impossible to "PREPARE" / cache since there are many different versions of it depending upon the number of paramaters that are required. It would be much better just to have one query with a parameter which is a string-list, which could be converted into a set (as required by IN) using a function, say STR_LIST_TO_SET(<string_list>): Select * FROM PEOPLE WHERE REGION_ID IN STR_LIST_TO_SET(:A) Why don't I just use regular-expressions? Because experimenting they are grossly inefficient compared with using IN \in this type of scenario. What do people think? |
|
Commented by: @dyemanov IN could be efficient (as compared to regular expressions or other approaches) only when an index scan could be used for a lookup. But it's possible only when number of elements inside the IN list in known at the prepare time. So I don't see how it could be done. |
|
Commented by: Tim Kelly (m00bh000) I wondered if this might be the case. For now I'm going to use a query with 16 params, the one's i don't need will be empty strings.... it works and makes my app much faster as the query is prepared and ready to go.... I hate it though what if someone wanted to use 17... where do i stop!! There must be a smarter way of allowing these type of queries to be prepared... any other ideas? |
|
Commented by: @asfernandes Something like "field IN LIST <string>" already was discussed and rejected. Converting a string to a set and allow IN <set> would be a completely different thing. It would need first to have sets (or arrays, or table variables, etc). |
Modified by: @pcisarstatus: Resolved [ 5 ] => Closed [ 6 ] |
Submitted by: Tim Kelly (m00bh000)
Relate to CORE3460
I execute the following query often:
Select * FROM PEOPLE WHERE REGION_ID IN (:A, :B, :C)
:A = 'A2D3', :B = 'C1D4', :C = 'FFE3'
but the number of params which make up the IN predicate of the query changes very often, so the next time it might be:
Select * FROM PEOPLE WHERE REGION_ID IN (:A, :B, :C, :D, :E)
:A = 'A2D3', :B = 'C1D4', :C = 'FFE3', :D = '127D', :E = '123A'
This makes the query impossible to "PREPARE" / cache since there are many different versions of it depending upon the number of paramaters that are required. It would be much better just to have one query with a parameter which is a string-list, which could be converted into a set (as required by IN) using a function, say STR_LIST_TO_SET(<string_list>):
Select * FROM PEOPLE WHERE REGION_ID IN STR_LIST_TO_SET(:A)
:A = '''A2D3'', ''C1D4'', ''FFE3'', ''127D'',''123A'''
Why don't I just use regular-expressions? Because experimenting they are grossly inefficient compared with using IN \in this type of scenario.
What do people think?
The text was updated successfully, but these errors were encountered: