Question: I am trying to figure out whether to compile regex expressions upfront, or alternativelt for startup speed, only compile they once when they are first required.

I can use timeit to test:

```py
re.compile(r'#(?:(?:[\da-f]{3}){1,2}|(?:[\da-f]{4}){1,2})|rgb\(\d{1,3},\d{1,3},\d{1,3}\)')

```

I don't know know how to figure out how long the parser takes (or how to use timeit to time it) to see the following code (not execute it, just "see" it).

```py
from functools import cache

@cache
def get_color_pattern():
    return re.compile(r'#(?:(?:[\da-f]{3}){1,2}|(?:[\da-f]{4}){1,2})|rgb\(\d{1,3},\d{1,3},\d{1,3}\)')

```

Answer: 
You may want to [return the resultset as a table](https://docs.snowflake.com/en/developer-guide/snowflake-scripting/resultsets.html#returning-a-resultset-as-a-table) rather than as a JSON array. If this is not feasible, then you may want to use a [cursor to retrieve one array row](https://docs.snowflake.com/en/developer-guide/snowflake-scripting/cursors.html#using-a-cursor-to-fetch-data) at a time.
16MB limit is related to the internal representation of the large objects, so there is no workaround for now. In future, I expect that Snowflake will support larger objects.

I do not know why you need to execute a SELECT inside the stored procedure, but maybe Snowflake Scripting can help you to return the JSON array as multiple rows:

<https://docs.snowflake.com/en/developer-guide/snowflake-scripting/return.html#returning-a-value>