I have a DNN build (4.8) that is experiencing problems that we have traced to the GetAllUsers stored procedure.  In this routine, all users are loaded into a temp table ordered by displayname. 

My problem is that I currently have over 13K users in the system (and growing to over 60k), and the sort on displayname is sucking up resources big time.  On first glance, adding an index on displayname might take care of the problem, but I am wondering if perhaps there might be a better solution.  Using a temp table seems to be a kinda weak solution in this instance.

Any input would be appreciated.... TIA!!!

on dotnetnuke we have over 600,000 usernames and this proc can be ran - it's slow, but the sql server has sufficent resources to get the list, perhaps your server requires more RAM or more harddrive space (temporary tables and group/sort operations use the tempdb as temporary storage and if it doesn'tn have enough free space it can cause issues). I dont think adding an index would do any harm here, the table is mostly read and not write.

Cathal