Search for information on building large enterprise systems


How do you organize DB layer, business logic and cross-platform API of your information management system, if uploading and processing 500000 data records in one session is a normal operation (C# .NET 3.5 + MS SQL 2005)?

I’m specifically interested in production-proven paging patterns that behave well with the concurrency, scalability and reliability.

Does anybody have any ideas, in what direction to dig?

  • Open Source Projects (don’t care about the language or platform, as long as it is not Ook)
  • books
  • articles
  • Google keywords
  • forums or newsgroups

Any help would greatly appreciated!


  • simple paging (i.e.: rownumber in SQL 2005) does not work, since there are a lot of concurrent changes to the database. Item, that is deleted or inserted between the page requests, automatically makes current page index invalid.


Done the implementation. I have been informed recently that one of the uploads was about 2148849 records. Tiers did successfully deal with the a couple of broken connections and dozens of deadlocks at the DB level during this upload.

In case somebody else needs some info:

Same with the SQL paging - it does not work in the scenario of numerous concurrent edits (as detected by the stress-testing)

As I mentioned, there is no magic in implementing paging – you either use ROW_NUMBER or a temporary table. The magic here is in evaluating what is your most common real world usage scenario. Using temporary table along with user tracking might help a bit in overcoming concurrent edits scenario. Though I sense that you’ll win more by answering questions:

  1. How long user stays on one page before moving to another one?
  2. How often user moves from first to any other page?
  3. What is the common pages count that user will look through?
  4. How critical it is if some information changes while user is moving from one page to another and back?
  5. How critical it is if some information gets deleted while user is on the page that shows the information?

Try not to concentrate on question like: “How to handle any possible concurrent edits scenario while paging?” before you answer above questions first and then handle only situations that really matter.

Another note is UI. Check out as much paging UI as you can find, as there are much better solutions than just right and left arrows, or lined up page numbers. Some of solutions help to hide/overcome technically not solvable paging scenarios.

P.S. If this answer is useful I’ll combine it with my first one.

By : Dandikas

When it comes to DB optimization for huge amount of data you’ll most probably benefit from using “BigTable” technique. I found article here very useful. Shortly the idea is to use DB denormalization to trade disk space for better performance.

For paging in MS SQL 2005 you’ll want to find more info on using ROW_NUMBER function. Here is just a simple example, you’ll find tons of them using google (keywords: ROW_NUMBER paging SQL 2005). Do not dig to much though – there is no magic in implementation, rather in how are you going to use/present the paging itself. Google search is a good example.

Note: we found NHibernate framework native paging support not sufficient for our solution.

Also you’ll probably be interested in creating FULLTEXT index and using full text search. Here is MSDN article on creating full text index, and some info on full text search.

Good luck.

By : Dandikas

This video can help you solving your question :)
By: admin