what is more appropriate way loop through object collection or database hit

Question

hello everyone,

i am using collection object. In some cases no of objects become large. in that case what i have to do either loop through the object collection or do a new hit? which gives more optimal to performance ?

Answer 1

As always with performance, it depends. The best answer is to try out both options and measure which one works best in a number of different scenarios.

Answer 2

+1 to Mark's answer, as it's impossible to say without knowing the full picture.

I would just add the 3rd option, which is to not loop through the object collection but maybe use a LINQ approach to find the objects you're interested in in the collection. I'm not sure what, if any, performance difference this would give but thought it worth a mention.

Answer 3

It also might depend on what you are doing. There are lots of options in databases to make life easier. Things like materialized views for instance can help reduce the number of rows your selecting against (which would reduce the results you get and make your query faster) or something like a stored procedure can also be utilized for performance if you're doing some sort of processing.

Then again somethings will just always take awhile. Last November I had to migrate 120 million records from a "detail" table into a "summary" table and even using bulk collects and things it just took a long time. Sometimes performance will always be "crappy."

If you have a DBA talk to them early on.