-
Query question
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?
Thanks!
-
most probably... yes.
I'm assuming a lot of things here -take mine as pseudocode too- but I would try something like:
Code:
select
distinct barcode
from table1 t1
,table2 t2
,table3 t3
,table4 t4
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|