Hi

I've got lots of custom modules on my dnn site, http://www.runsaturday.com

Some of these do some fairly complicated SQL queries - for which I'm now beginning to need to start tuning the database.

One thing I'd like to tune by is to have some form of indexing in place for users by country.

However... country is stored in the profile... and the generic profile column is nvarchar(3750) - so it's not indexable.

I'm not an SQL expert... but I'm wondering:

- how could I go about setting up some other table or view which is indexable?

- if I can't then what would be the harm if I just altered the column definition to (say) nvarchar(100) ? (Note - I have complete control of this installation and can't imagine I'll ever extend the DNN profile entries much beyond name and country).

I'm happy to consider all sorts of suggestions... thanks for any input!

Stuart

Hi Stuart,

I am not answering your question, but I thought I'd mention the LocationSearch feature in my UserProfile module. It gusses the users location based on IP if they are  not logged in and yuo can drill up and down users by location. I plan to enhance my league module to use the same search querystrings so you can see all leagues, events, groups users etc. for a specific location.

Entrepreneur

PokerDIY Tournament Manager - PokerDIY Tournament Manager<
PokerDIY Game Finder - Mobile Apps powered by DNN
PokerDIY - Connecting Poker Players

Thanks for all the feedback already :)

Douglas - I'm already using this too (in a map page - http://www.runsaturday.com/People/Courses/Mapper/tabid/99/Default.aspx) - instead of the DNN flatI use a web service to lookup the iP address - it works for most people although I do see the odd error...

Rodney - thanks - I really should take another look at all your modules - thanks for open sourcing them - I will also do my best to contribute back in... but I'm learning not to make quite so many promises with my time ;)
 

Sebastian - as my user base increases, I believe the potential for gain is huge. Not in terms or reserved space (that doesn't really bother me), but definitely in terms of lookup speed.

For example:
- Take a look at a page like: http://www.runsaturday.com/People/Everyone/tabid/93/Default.aspx
- And then click on a country icon - e.g. United States to reveal http://www.runsaturday.com/tabId/93/CountryCode/US/default.aspx

The underlying SQL for both of these views currently does a lot of JOINs from "My custom tables" to dnn_User to dnn_UserProfile via dnn_ProfilePropertyDefinition (or something like that). When I look at what is taking the time in the execution plan analyser in SQL Management Studio, then I can see an "Index Scan" which occurs across the whole of dnn_UserProfile. From what I've read, index scans (and table scans) are SLOW - a scan is not a lookup instead it is a read of every value. When you look up "Germany" the database engine has to scan (read) every single "Country" row - so it's linear performance in terms of number of users. To speed things up ideally I would like to use an "Index Seek" instead - as this is a lookup it would probably be order log(number of users) - plus because it is a lookup it would be much more efficient in terms of RAM space at runtime (at the expense of disk space - but I'm happy to lose disk space!).

I've looked at this a bit more... and am still on my two original ideas:
1. A quick fix where I change the dnn_UserProfile PropertyValue column so it is nvarchar(100) and then apply an index to it.
2. A slightly harder fix where I either build some Triggers to maintain a separate Country table... or I build some scheduled task to regularly update a lookup table (or is there some other mechanism - like I say I am still a SQL Server newbie here really!)

Beyond this... I'm also starting to wonder about what SQL Server Table Statistics are... and how they might help me...
 

Thanks again for all the feedback :)

Stuart