How get to table reference data quickly ??

[gazolba]

I'm developing what is turning out to be a huge system. My main tables are full of id's which refer to other tables. In my processing I have a constant need to convert these id's to full descriptions. Some of the less voluminous data I keep in global collections. But I have some bigger tables like my user table which converts userid's to full names. How can I do these conversions quickly? I'd rather not open an ADO connection each time.
Any bright ideas? I thought about caching data in a collection and scanning that first before I read the table.

 

[psemianonymous]

If the userid field is indexed, then table size isn't an issue. The lookups will be fast enough.

'Optimize after the system has been built' is the common wisdom, though it's difficult to optimize database access structure once it's set. But do it anyway, or ... don't do it (the optimization), as the case may be.

If you know you're going to be polling the user information table, why not include it in whatever query you grabbed the data source from initially? Meaning, if you're pulling data from a table that has a userID in it, why not also pull the linked USERS table as well? The performance hit will be smaller than you think, and then you wouldn't have to 'poll' for the full name.


Or maybe I'm misunderstanding everything, which is possible.

 

[PHV]

My main tables are full of id's which refer to other tables
As far as the PK, FK and relation ships are well defined, I don't see where the problem arise.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[gazolba]

I'd rather not build a crippled system as performance is very high on the criteria of what will make this system successful. So I'd like to do it right the first time rather than have to go back and change and retest everything again.