Working with HUGE lists – My Real World Experience Dealing with My Blog Spam Comments List

Part of the reason for my last post was me dealing with my huge comment list.  Sure it’s a lot of spam, but I have some serious lessons learned in dealing with it.

1. Comment moderation can do 2 things.  1) Prevent unwanted comments from showing up 2) Make you the bottleneck

But did you also know that when you turn on moderation you get read-only datagrid views?

2. Limiting the item limit view is one of my new favorite features in dealing with large lists resets back to all items viewable every time you edit/save the list!  Very much a pain since it is collapsed by default and not noticeable.  This is likely a bug, but now that I know, I can work around it, until it’s fixed.

3. I was editing a list with more than 10,000 items and the best way to manage it was to 1) Use the datagrid view 2) Limit the items to groups of a few hundred.  A thousand was doable, but more than that I felt like I was waiting.  3) Limiting the columns to those that I needed to see also helped improve the query response time

4. I’ve now closed my new comments, and replaced it with DISQUS. It’s a full blown comment solution that is really designed for community and already has a ton of online support.  I was able to simply create an account, and drop in the snippet of code into a content editor web part and close the existing “new comment.”  It gives me the ability to support all sorts of interesting things yet to be seen, but things like integrated twitter responses, facebook responses, video, and trackbacks…  I’m a huge trackback fan, so that’s a huge bonus.

5. One thing I also have to note from a practicality standpoint.  My servers are in Texas and I’m in Seattle area.  My servers are really fast, but when I was trying to edit my huge list with Access, I was having all sorts of troubles with timeouts and hanging.  A lot of this had to do with latency, but also the query.  Limiting the item limit for the query itself was still giving me issues, and turning off moderation of the list.

 

When using the export option it does respect the item limit, but it still might feel like it’s hung and give you the “Not responding,” but don’t give up if your query is reasonable like 1000 or 2000 or so.  For more, I’d recommending walking away so you’re not tempted to kill the process. 

6. When using dataview be sure to note the little progress bar in the bottom right hand corner.  If you ever delete like 700 rows at once it can take a while.  It will let you continue doing other operations, but if you refresh it will warn you… if you ignore the warning you will find out later it wasn’t done.  Bad news.  Pay attention to “Pending Changes”  Also pay attention to conflicts, locks, and “Read-only”

7. I wanted to know how many items I had in much huge list.  What’s the easiest way to get that?  Go add totals on my view.  Right?  Wrong…  I found no matter what I did to my view to get it to give me the total, it would either hang, or go read only and give me an error.  The totals are great when you have limited the list, so you can see what’s going on, but the all items view for example was a joke.  It would only show me 1-100 the simple page to page view.  Which would render, but didn’t give me the data I was looking for.  Using an external tool like the SPDiag or stsadm –o PreUpgradeCheck (in SP2) is a much better means of getting your list count.

8. When editing my super large list I found I had to turn off moderation, that was the beginning, that would at least give me the flexibility i needed.  As well in the dataviews, I limited my columns to only those “required” during editing, and when deleting, I’d even go for less.  I know I mentioned some of this above, but ultimately as well, the “Versioning” settings of the list… that’s where you go to change moderation.  Despite the fact that you’re an admin on the box, an admin on the site collection, and an owner of the items, sorry you’re likely going to have difficulty editing in datagrid unless moderation is off.

Toggling the moderation from on to off immediately made a huge difference. for me.

9. I learned that all other views, other than dataview are a pain to delete items, and even editing large quantities of items going from page to page in all other views, was simply not worth my time.  Either external clients like Excel, Access, Outlook, or the browser datagrid was the way to go.  Note the datagrid didn’t work when Excel wasn’t installed.  I’m sure there’s a version dependency, but didn’t get a chance to check that.  Often in Dataview the pending changes might last for up to 5 minutes on large operations like huge bulk deletes.  You can imagine what that’s doing to the database, and as a server admin, I can just imagine what’s going on when someone connects access to a large list 🙂 and it looks like it hangs for 5-10 minutes.  Some serious SQL action going on.

Deleting was interesting to figure out.  With moderation it was obviously grayed out, and no matter what I did in datagrid I couldn’t get it on.  It was all read only.  After addressing that I was able to get the multi select in datagrid to work where I could select multiple rows very quickly and the wheel on the mouse was very responsive as well.  I was able to select hundreds to thousands of items very quickly and delete them.  While deleting I could continue to select and delete other rows, and despite the pending as long as I didn’t refresh or navigate to another page, it would continue to queue the operations.

10. One of the things I’ve been missing for years is a HUGE set of Office integration tools with the datagrid view.  If you click on the very edge you’ll find a huge set of integration links including richer filtering, sorting, cutting, and undo!  Why are all those tools so hidden?  Am I blind?  Maybe others have been using the print and chart with excel options for your list and creating pivot reports on the fly.

Now that I’m done editing and cleaning out my spam out of my list.  I’ll turn moderation back on…