ATL collection using database

Hi,
I have implemented a collection in ATL.
"People" is the collection "Man" is an item of the collection.

In the database i have a table People and so, each record in the table is a Man.
I use ADO to access my database.

And my ATL collection have to support multithread.

On each item of the collection, you can Set and Get properties.

The problem i have is on how i get the data of my items. I can't store the data directly in the "Man" object because if another client have changeed the name for instance, the state of my object will be incorrect for other clients.

So, i have to get the data in the database each time the client access a property of an item. And it disturbs me a lot because it implies each item to have a recordset, to go and get the data in the database.

So 70000 items --> 70000 recordsets, i think it is not acceptable.

So my question is, what is the current and efficient way to implement a collection that manages table of a database?

Thanks!




FMayisAsked:
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.

 
missionImpossibleCommented:
I don't know if I really got it, but you don't need to have one recordset per item. You can use one recordset per client. The recordset in your case should be a "Dynamic"-recordset type. This is a representation of a dynamic cursor, which shows every changes of other users on the fly. See also MSDN.

0
 
snoeglerCommented:
>> So my question is, what is the current and efficient way to implement a collection that manages table
of a database?

A database :)
Databases are specifically designed for handling this. By putting a 'container' around a database table, all benefits of a database are lost (as long as you want a generic container) (or you are going to re-implement SQL which is specifically designed for doing all time critical tasks within the highly optimized database engine).
0
 
ShaunWildeCommented:
I agree wish snoeglar let the database be the main store of your collection - if you need to hand it out do a select * and generate your collection on that and pass it on - or you can pass out single items, subsets or the whole thing - if an item needs to check its state (dirtyness) then you can either check with a flag (eg timestamp of last update) or just get the whole thing.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
snoeglerCommented:
>> as long as you want a generic container
i meant:
>> as long as you want a simple container
0
 
FMayisAuthor Commented:
Hi,
I do agree, the database is the main store of the collection.

For the moment i did a select * and then use the generated recordset to implement my collection.

and doing a SELECT * implies loading in memeory lot of things not needed. If a user works with an item and i have some thousand in the database, then lot of memories are lost for nothing.

So,what is the best compromise(performance time, memory size) between loading all, or making a request on each access to data of an item property.

0
 
missionImpossibleCommented:
1. if you need all data of a row (select *), to get it once is quite more performant, as to access the data in multiple access.
But sometimes it could be clever to fetch data lazy: F.ex if your user needs only in 5% of all use cases all the data, you can fetch this data only if he really needs it. in 95% of the cases you can get only the  data he needs and your app will be more performant. It depends on what the user needs.

2. if the user don't needs to access all rows (at the same time), filter your select, to get only one or few rows of data.
0

Experts Exchange Solution brought to you by ConnectWise

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
 
FMayisAuthor Commented:
The collection will be used like follows

for i = 1 to Count
    item=col.GetItem(i)
    item.GetName //or anyother property
next

or

item = col.GetItem("Name")
//and then get all properties of the item


I think that getting data when needs will be more performant.

0
 
snoeglerCommented:
What about a collection like this:

MyColl.Init "SELECT * from customer";
For Each Elem in MyColl
  Print Elem("Name"), Elem("Street"), Elem("Zip")
Next

This reminds me of something :)
0
 
FMayisAuthor Commented:
Sorry for elapsed time!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.