Stuart, 

I agree, that joining with serialized tables in SQL is not very fast, you should consider not to access tables but use business objects in your code instead. These will retrieve the user info and you are safe using an API, which will be unchanged, if the database structure is modified.

Hi Sebastian

I'm not sure I get this... even if I use business object abstractions, surely I still need to run some database queries by country at some point? I agree I can layer things to protect myself from future DNN changes - but somehow I still need to optimise the database layer so that it works quickly enough to cope with thousands of users from across the globe? Or is there some way you are suggesting I can do this in managed objects above the database?

Confused...

Stuart

Thanks again.

Sadly the evidence I'm seeing already (with only a 1000 users) is that a query similar to that is not fast enough - it needs to use an index not just do a brute force full text search every time. As it currently stands it also pulls too much data into RAM cache which then hurts the server - I'm on a GoGrid host and RAM is my main cost and restriction.

Looking at the details from the Query Analyser it says that one of the main reasons for slowness is the Table Scan over dnn_UserProfile - which is why I want to replace it with an Index Seek.... but I can't currently put an Index the PropertyValue because it's such a big field....

I'll have a think some more about how to handle this... I know I could just quickly redefine the table. Another option I'm considering is adding a stored computed column which contains a substring of the PropertyValue column - I could limit the size of this column to say 100 characters - which would then be indexable.

As for security... I'm currently mainly using LinqToSql rather than using StoredProcs - this does add a small overhead - but should also be quite secure in terms of sql injection at least

Thanks again for the comments

Stuart