I have been doing some serious deep diving into the LiveCycle DS system that Adobe has been touting as their new solution to your middle tier. They claim they can save you millions in middle tier development costs by simply adding this simple system to your middle tier and let it handle, caching, paging, queries, modeling, etc all automatically with their newest Flex Tools and Data Services. For the ridiculous price tag, $30,000 per processor, not server, per processor, you bet you will be betting the farm on this!

A little history, and a pat on the back to me...

For those of you that don't know, I am the guy that when Contribute Publishing Server was released, and didn't work properly on 97% of the systems across the world, I released a patch that fixed everything for Macromedia, as they must have been using some very Junior guys to write some serious LDAP code.

Anywhooo, back to the discussion at hand...

So I just love tearing apart Adobe (Macromedia) systems and seeing what is making them tick, as you can really make them do some cool stuff if you get into the inner workings, and you can do it quite easily since most of their systems are just Java apps.

on a side note, can someone over there please fix up JRun so I don't have to install a 20 GB JBOSS Server!

What interesting things have I found?

A Junior Developer wrote the paging functionality...

If you have ever seen a database application, that has data paging, you can pretty much tell how versed the programmer was by how they handle paging. If the programmer is not very experienced and they wanted to get back records 10,000 to 10,500 you will see a query that looks like this...

SELECT TOP 10501 * FROM Table

Yuck! That means the servers memory, the network between your app server and your database server, and the app server memory are getting its arse kicked! Then the programmer would iterate through the results and only pass on records 10,001 to 10,500. You know how uneducated people solve this problem?

"Hey Jimbo Junior Developer, why don't you go out and buy me some more RAM for my server and pick me up a couple gigabyte NIC's while you are at it?"

What does this have to do with LiveCycle DS?

Adobe is a giant software company, they know how to do things the right way....

WRONG! So I was stress testing a flex application at work, and the network profile showed an infinitely growing hit as I paged through data of 2 million rows, thats right, I don't joke around...

You should never need to mess around with data this size... 

That is what I have been told, obviously not from anyone who ever worked in financial or medical, or event logging systems. So I start poking around in the paging system and I thought to myself "I just cant believe this system would be doing this"...

Lo and behold what did I find...

So I run a Profiler on the Database and see the query

SELECT TOP 1055001 * FROM .....

Oh boy!

Well what do I do now?

I would still highly suggest using LiveCycle Data Services (Buy it from me) as it does have some good functionality. I would steer clear of the default paging behavior and implement your own classes that use a cursor method like the following which I wrote for my middle tier some 15 years ago....

It has been modified since then, so no need to point out that some features didn't exists then and Yes, I have seen this republished under other peoples names many times, imitation is the greatest form a flattery so I do not care. 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_SELECT_PagingObject] (@Tables varchar(1000),@PK varchar(100),@Sort varchar(200) = NULL,@PageNumber int = 1,@PageSize int = 10,@Fields varchar(1000) = '*',@Filter varchar(8000) = NULL,@Group varchar(8000) = NULL) AS
DECLARE @PKTable varchar(100)
DECLARE @PKName varchar(100)
DECLARE @type varchar(100)
DECLARE @prec int
IF (CHARINDEX('.', @PK) > 0) BEGIN
  SET @PKTable = SUBSTRING(@PK, 0, CHARINDEX('.',@PK))
  SET @PKName = SUBSTRING(@PK, CHARINDEX('.',@PK) + 1, LEN(@PK))
END ELSE BEGIN
  SET @PKTable = @Tables
  SET @PKName = @PK
END
SELECT @type=t.name, @prec=c.prec FROM sysobjects o JOIN syscolumns c on o.id=c.id JOIN systypes t on c.xusertype=t.xusertype WHERE o.name = @PKTable AND c.name = @PKName
IF (CHARINDEX('char', @type) > 0) SET @type = @type + '(' + CAST(@prec AS varchar) + ')'
DECLARE @strPageSize varchar(50)
DECLARE @strStartRow varchar(50)
DECLARE @strFilter varchar(8000)
DECLARE @strGroup varchar(8000)
IF (@Sort IS NULL OR @Sort = '') SET @Sort = @PK
IF (@PageNumber < 1) SET @PageNumber = 1
SET @strPageSize = CAST(@PageSize AS varchar(50))
SET @strStartRow = CAST(((@PageNumber - 1)*@PageSize + 1) AS varchar(50))
IF (@Filter IS NOT NULL AND @Filter != '') SET @strFilter = ' WHERE ' + @Filter + ' '
ELSE SET @strFilter = ''
IF (@Group IS NOT NULL AND @Group != '') SET @strGroup = ' GROUP BY ' + @Group + ' '
ELSE SET @strGroup = ''
EXEC('
DECLARE @PageSize int
SET @PageSize = ' + @strPageSize + '
DECLARE @PK ' + @type + '
DECLARE @tblPK TABLE (PK  ' + @type + ' NOT NULL PRIMARY KEY)
DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
SELECT '  + @PK + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
OPEN PagingCursor
FETCH RELATIVE ' + @strStartRow + ' FROM PagingCursor INTO @PK
SET NOCOUNT ON
WHILE (@PageSize > 0 AND @@FETCH_STATUS = 0) BEGIN
  INSERT @tblPK (PK)  VALUES (@PK)
  FETCH NEXT FROM PagingCursor INTO @PK
  SET @PageSize = @PageSize - 1
END
CLOSE  PagingCursor
DEALLOCATE  PagingCursor
SELECT ' + @Fields + ' FROM ' + @Tables + ' JOIN @tblPK tblPK ON ' + @PK + ' = tblPK.PK ' + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort)

What is the best possible option for my company and I?

Have someone like myself or the group of people I have put together architect, design and develop your applications as we have already crossed all of these bridges, and we can determine what is going on faster and more efficiently.

Return To News