Challenges of Multi-Condition Data Retrieval

Why I Suggest a For Loop

There are several different ways to approach a multi-condition query, so why did I choose iterating through a for loop? After reading Multi-Condition Data Retrieval, you may be wondering why not do one of the following:

• Create a temporary table on the server
• Run one query per row
• Make a single SQL query

All valid options.

Some environments are well-equipped for simple queries where only one dynamic condition needs to be met and the retrieval takes a reasonable amount of time, say less than an hour. However, when there are two dynamic conditions, as described in our example and a long list of data is needed, some environments run into several barriers:

• There may be a 1,000 item limit on our data filters
• You may not have access to write temporary tables
• The connection may timeout on us

So a single, gigantic script may not fit within the 1,000 item limit. And access issues get in the way of uploading the client data to a temporary table. And even if you can circumnavigate around those challenges, queries may be limited to one hour of running time before timing out.

My solution to run one query per row may seem inefficient but it does solve for those challenges. Another option, if you want to improve performance, is to paginate and run something like 1,000 rows at a time. For my needs, the one at a time approach works and is easier to debug.

These common issues are well-documented on the Internet. For examples, see here, here, and here. The CREATE TABLE AS SELECT * FROM +source table+@+database link+; solution does not work due to other access restrictions we face.

Return to the original article here: Multi-Condition Data Retrieval