CodingForums.com - View Single Post

Fou-Lu · 12-10-2012, 06:13 PM

Its really bad practice.
Writing a query within a loop results in querying n times where n is governed by the size of the loop. With a query that returns a resultset of 50 and a loop that queries twice more is a minimum of 100 + 1 queries per load.
DBMS can be locked down to a maximum number of queries (per hour I believe). Say its locked down to 20,000 per hour, that means you can only execute the script 200 times before you cannot query any more for the remaining hour. It doesn't take long to run through 200 executions.
It will also be slowish. Since you need to keep going back to the dbms for more information you are shuffling an external connection for each one of these resultsets.

Change your query to a join and that problem will go from 101 queries to 1.

12-10-2012, 06:13 PM	PM User \| #2
Fou-Lu God Emperor Join Date: Sep 2002 Location: Saskatoon, Saskatchewan Posts: 15,639 Thanks: 4 Thanked 2,448 Times in 2,417 Posts	Its really bad practice. Writing a query within a loop results in querying n times where n is governed by the size of the loop. With a query that returns a resultset of 50 and a loop that queries twice more is a minimum of 100 + 1 queries per load. DBMS can be locked down to a maximum number of queries (per hour I believe). Say its locked down to 20,000 per hour, that means you can only execute the script 200 times before you cannot query any more for the remaining hour. It doesn't take long to run through 200 executions. It will also be slowish. Since you need to keep going back to the dbms for more information you are shuffling an external connection for each one of these resultsets. Change your query to a join and that problem will go from 101 queries to 1.