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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I will look at the indexes. The tables have been around for years. Perhaps I am not hitting them right.
Thanks,
jimbo99999
Thanks,
jimbo99999
ASKER
After looking, I found there are no keys on the table when there should be. Sounds like I need to go see the Administrator.
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