Hello all, I'm running the following query on about 6,000 records worth of data, and it takes about 8 seconds to
complete.Can anyone provide any suggestions to improve performance? I have an index on two columns in the transacts
table(program_id, customer_id). If I specify a number for <a href="http://customer.id/" target="_blank">customer.id</a>
inthe sub-select, query time is reduced to about 2 seconds, which still seems like a million years for only 6,000
records,but I'm guessing that the sub-select can't resolve the id since it's done before the outer query, so it scans
theentre recordset for every row? Transacts is a many to many table for customers and programs. I know this query
doesn'teven reference any columns from programs; however, I dynamically insert where clauses to constrain the result
set.<br /><br />SELECT distinct <a href="http://customers.id/" target="_blank">customers.id</a>, first_name, last_name,
address1,contact_city, contact_state, primary_phone, email, array(select programs.program_name from transacts, programs
wherecustomer_id = <a href="http://customers.id/" target="_blank">customers.id</a> and <a href="http://programs.id/"
target="_blank">programs.id</a> = transacts.program_id and submit_status = 'success') AS partners from customers,
transacts,programs where transacts.customer_id = <a href="http://customers.id/" target="_blank">customers.id</a> and
transacts.program_id= <a href="http://programs.id/" target="_blank"> programs.id</a><br /><br />
