While I feel it's important to provide real-time information to your application users, large databases that handle several thousand transactions per day, and more accurately, the reporting of or data interaction with said transactions can make even the most streamlined application slow with embarrassingly sluggish response times. In these cases, for those applications that exceed the "logical" document count for a Domino database (I'd say 200,000+ documents for a single database) I typically recommend the implementation of transaction profiles - the utilization of an architecture which I'll attempt to explain in the following article.

I've seen countless applications that tend to become sluggish just as it's finally being utilized to it's potential - and it's often the architecture reflecting the lack of understanding that while @dblookups and @dbcolumns are great for simple applications, they can prove disastrous for high-transaction databases.

While I don't consider myself a load-and-utilization expert, even I can see that there's going to be an issue with running an @dblookup on a view that contains 200,000 documents every time a form or document is opened simply to show the user the current document count or to return a subset of data from the database. It's for situations like this that I recommend the following:

Create an agent called "app/statreporter|statreporter". Below I've included some very basic Lotuscript that illustrates what you might include in such an agent.

Sub Initialize

	Dim s As New NotesSession

	Dim db As NotesDatabase

	Dim view As NotesView

	Dim reportview As NotesView

	Dim reportdoc As NotesDocument

	Dim coll As NotesDocumentCollection

	Set db=s.CurrentDatabase

	Set view = db.GetView("report")

	
	
	'Check for a Report Profile, if none, create one

	Set reportview=db.GetView("reportprofile")

	Set reportdoc=reportview.GetFirstDocument

	

	If (reportdoc Is Nothing) Then

		Set reportdoc = db.createdocument

		Call reportdoc.ReplaceItemValue("Form","reportcount")

	End If

	

	'Collect document counts from a particular view

	Set view = db.GetView("ExampleView1")

	Call reportdoc.ReplaceItemValue("Totalfrom1",view.EntryCount)

	

	'Collect document counts from a subset of documents in another view

	Set view = db.GetView("ExampleView2")

	Set coll = view.GetAllDocumentsByKey("subsetkey")

	Call reportdoc.ReplaceItemValue("Totalfrom2", coll.Count)

	

	'Collect information from an external database - securely!

	Dim dblu As NotesDatabase

	Dim doclu As NotesDocument

	Dim lutrigger As String

	Dim count As Long

	lutrigger = "lookupkey"

	

	Set dblu = New NotesDatabase( db.server, "OutsideDatabase" )

	Set view = dblu.GetView("stats")

	Set coll = view.GetAllDocumentsByKey(lutrigger, True)

	count = 0

	

	Set doclu = coll.GetFirstDocument

	Do While Not(doclu Is Nothing)

		count = count + doclu.ExampleField1(0)

		Set doclu = coll.GetNextDocument(doclu)

	Loop

	

	Call reportdoc.ReplaceItemValue("ExampleOutsideCollection",count)



	'Save the Report Profile for further usage

	Call reportdoc.Save(True,False,True)

	

End Sub

From the above code, you can see that I'm creating or modifying a single document, updating run-time information on whichever lookups I will need for my applications and returning the results in their own field - all to which I can now use a @DBLookup to a single document to gather the information that I require for my particular application.

Additionally, I am not only looking up information in the current database (as seen in my first two examples), but also in a separate database. As such, utilizing this approach can even lend to a tighter security infrastructure for your application servers, not to mention alleviated the need to jump from database to database.

Now, instead of having to traverse view after view to gather information, I can simply lookup the catalogged content in my Report Profile. The only thing left to do is set the expectation that this information can be on a 30 minute delay, in case you have a user that decides to perform a function that would generate a new entry to-be-catalogged in the "profile report" and performs another function, which should reflect a requirement to have the latest information.

I have a work-around for just this situation, which will be published in part 2 of this series.

Technorati tags: Show-n-Tell Thursday