Improve performance on ProfileManager.GetAllProfiles when using SqlProfileProvider

Question

I am using the SqlProfileProvider to store my user profiles in an asp.net web application.

What I am looking for is a way to fetch all user profiles (I would prefer a search API, but there is not one available) with some reasonable performance.

Using the ProfileManager.GetAllProfiles kills the performance of my application.

I was thinking of using Sql Cache Dependency on the object that returns from this method, but I would still have a very slow site every time someone updates a profile (which could happen several times a day).

Anyone have a suggestion to improve the performance? I am looking for things on these lines:

• Caching efficiently (only the differences should be re-cached)
• Optimizing the GetAllProfiles call
• Being able to search profiles, instead of having to fetch them all and filtering later

Answer 1

The sqlProfileProvider does not provide an easy way to search profiles as all profile data is stored in a single column.

You should consider creating your own profile provider or use something like the Table Profile Provider. This stores each profile property in its own database column and so you could easily write custom queries to search the data.