Vb.Net - Design/Speed Recommendation

Good Day Experts!

Background
I am in need of some design assistance with my little project.  This is kind of like a the traditional Hdr/Dtl SQL table setup but a slightly different.  Instead of let's say an invoice number Hdr record with part number Dtl records...I have Hdr table records that contain those part Numbers.  However, I still do need to get to the Dtl table to get info for each part number.

Issue
I am building a CSV file.  To process and build the CSV file for one 650 record invoice it is taking about 12minutes.  I suspect it may be the call to the db each time through the loop to get the detail information.  The Dtl table contains potentially multiple records for a part number.  So, I was trying to not do a join in my initial loop query just to keep the processing "cleaner" and "easier" but I am questioning that.

Question
Would you think it would be better loop through the Hdr record and grab all the Dtl table records putting them in a DataTable instead of making a call to the Database 650 times?

Please ask more questions as I probably did not explain completey.

Thanks,
jimbo99999
Jimbo99999Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

unknown_routineCommented:
The best and most powerfull  solution in this case: Use LinQ

This will Eliminate 99% of the DB queries. and you app will be far faster. you won't need 650 DB calls anymore!

Take a look here:

http://code.msdn.microsoft.com/LINQ-Join-Operators-dabef4e9

I suggest to go through all 100 examples. It will pay for your every second of time reading it!

Simple+ powerful
0
käµfm³d 👽Commented:
Do you have proper indexes on your tables? And have you built your queries to take advantage of those indexes?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jimbo99999Author Commented:
I will look at the indexes.  The tables have been around for years.  Perhaps I am not hitting them right.

Thanks,
jimbo99999
0
Jimbo99999Author Commented:
After looking, I found there are no keys on the table when there should be.  Sounds like I need to go see the Administrator.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.