DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Query question

  1. #1
    Join Date
    Jul 2009
    Posts
    1

    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!

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    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
  •  


Click Here to Expand Forum to Full Width