I hope this is the appropriate place to ask this... I am a C# developer, and have inherited some interesting code. In once place, profiling points out a huge bottleneck and I have discovered this gem (pseudocode):
tableList = select name from table1, table2 where table1.ID=table2.ID
// tableList comes back with 62,000 rows
loop over tableList, chunks of 100 rows
foreach 100 rows
barcodeList = select distinct barcode from table3, table4 where table.NO=table4.NO and
t3.ID in (100 rows of name from tableList)
foreach barcode in barcodeList
if barcode not in finalList add to finalList
Hopefully this makes sense. The final result (finalList) only has about 50 records, but the process to get that list takes a looooong time. Is there a way to merge the two queries that will get me the same results and allow me to remove all this looping crud?
most probably... yes.
I'm assuming a lot of things here -take mine as pseudocode too- but I would try something like:
from table1 t1
where t1.ID = t2.ID
and t3.ID = t1.name
and t3.NO = t4.NO
Pablo (Paul) Berzukov
Author of Understanding Database Administration
available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Click Here to Expand Forum to Full Width