I am at a performance delima. We store some records in tables that have a list of items and want to retrieve the last item in the table, without regard to any specifics of the item.

For example, we keep a list of session numbers for a data synchronization program. The datafields are simple, sessionid, sessionnumber, sessiondate.

Now, we want to increment the sessionnumber so we retrieve the last record and increment by one.

For now we use a SessionEntityCollection and then use the GetMulti(Nothing) statement using the resulting collection to retrieve the item as follows:

SessionEntityCollection.Item(SessionEntityCollection.Count-1).SessionNumber

Sounds simple and yes, it works. However, I continue to be worried about a time delay when we have 1,000 records, 10,000 records, etc.

Sooo, what is the most efficient way to retrieve the last record in the table? Or do I need to store the last sessionnumber in another table with only one record - that last sessionnumber?

Any ideas would be appreciated.

Thanks!

David.

David You might want to consider:

Autonumbering and Identifier Columns   

For each table, a single identifier column can be created that contains system-generated sequential values that uniquely identify each row within the table. For example, an identifier column can generate unique customer receipt numbers for an application automatically as rows are inserted into the table. Identifier columns generally contain values unique within the table on which they are defined.

Search msdn for further information and usage

Look at

Creating a new / modifying an existing entity

for how to use identify within LLBLPro. Below is a small extract from the LLBL manual.

The code is aware of sequences / identity columns and will automatically set the value for an identity / sequence column after the entity is physically saved inside SaveEntity(). The new value for sequenced columns is available to you after SaveEntity(), even though you haven't specified that the entity has to be refetched. This can be helpful if you want to refetch the entity later.

Thanks for the info. I actually already use unique id's for all my tables, this included. The unique id is great for relational data and quickly picking known records.

In this case, I'm looking for the last record in an unknown number of records. Thus, the unique ID is also unknown.

I really don't even care what the values in the table are (yet), I just want to retrieve the last record in the table, whatever it is.

Now, let's take your info a step further. If you have a unique id column that auto fills, is there a function in the generated llbl code that retrieves the last used unique id? Then we could get the last record using that id? For example:

dim myEntity as new SpecialEntity dim myLastID as integer = GetLastUniqueIDUsed(myEntity) myEntity.fetchUsingPK(myLastID)

The problem is how do we get the last unique id?

The code I use currently is like the following:

dim myCol as New EntityCollection 'get all records in database myCol.GetMulti(Nothing) dim myEntity as New Entity = myCol.Item(myCol.Count -1)

The issue is the time it takes to create an entire collection and retrieve the count and then the item in the collection. If the list is 10,000 entries, it might take awhile and all we want is the last record, whatever that is.

sessionid is an IDENTITY column? In that case you would just use MAX() (search for Aggregate functions in help). Am I missing something?

David

You could try SQL aggregate MAX. LLBL way of doing this is

AggregateFunction.Max

JimFoye:

MAX does appear to be a good aggregate function to use. Since I haven't tried to use an aggregate function yet, I did some research and came across this thread:

http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=3156&HighLight=1

I then created this code:

     Private Function GetLastSessionNumAggregate() As Integer

        'create return variable
        Dim lastSession As Integer

        'create sessions collection to hold results
        Dim mySessions As New CollectionClasses.SynchSessionCollection
        'create entity field for max aggregate function
        Dim sessionIDField As IEntityField = DataLayer.FactoryClasses.EntityFieldFactory.Create(SynchSessionFieldIndex.LastSynchSessionId)
        sessionIDField.AggregateFunctionToApply = AggregateFunction.Max

        'create predicate to use aggregate function and add
        Dim myPred As IPredicateExpression = New PredicateExpression
        myPred.Add(DataLayer.FactoryClasses.PredicateFactory.CompareExpression(SynchSessionFieldIndex.LastSynchSessionId, ComparisonOperator.Equal, New Expression(sessionIDField)))

        'get the collection filtered with the predicate
        mySessions.GetMulti(myPred)

        'if a record is returned, then set max info to return, else return zero
        If mySessions.Count > 0 Then
            lastSession = mySessions.Item(0).LastSynchSessionNum
        Else
            lastSession = 0
        End If

        'return the result
        Return lastSession

    End Function

However, I appear to be getting a collection with all the records in it, not just the one with the max id number. Does anyone have any suggestions on what I missed?

Thanks much!

David.

Hi Here is a sample code fragment for that work on Northfield DB

Dim orderDetails As New OrderDetailsCollection
Dim orderMax As Decimal = CDec(orderDetails.GetScalar(OrderDetailsFieldIndex.OrderID, Nothing, AggregateFunction.Max, Nothing))

This results in OrderMax that has just a number

Althought this is SelfSerivce example, Adapter should be similer. Please refer to the manual for the adapter example.

Perfect!

As a wrap-up, I was trying to find a way to retrieve the last record in a database without knowing the record id and without knowing how many records are in the table. Various methods I employed were taking several seconds to execute which significantly slowed my application especially since the code was executed several times. The beginning and ending code is shown below with the change in execution times also shown. Note the execution times are only for my machine, but the relative change is significant nonetheless.

This is the code I started with:

_ Uses a GetMulti(Nothing) to retreive all records of an entity in a collection and then retrieves the record id of the Collection.Count - 1 Item. It slows down the more records you add to the database and has an initial overhead of several seconds even with only a few records._

     Private Function GetLastSessionNum() As Integer

        'get collection and return first item (should never be more than one)
        Dim lastSession As Integer
        Dim mySessions As New CollectionClasses.SynchSessionCollection
        mySessions.GetMulti(Nothing)


        If mySessions.Count > 0 Then
            lastSession = mySessions.Item(mySessions.Count - 1).LastSynchSessionNum
        Else
            lastSession = 0
        End If

        'return the calc last session
        Return lastSession

    End Function

** This slow code to 3.5 seconds to execute.**

And this is the code I ended up with:

_ This code retrieves the maximum ** RecordID** in the table as an integer which is then used to retrieve an entity record and return the value we needed to begin with. The only requirement is that the table have a unique, consecutive identity (recordId), or another unique and ascending field. We use the Max Aggregate function to retrieve the record we need._

     Private Function GetLastSessionNumAggregate() As Integer

        'create sessions collection to hold results
        Dim mySessions As New CollectionClasses.SynchSessionCollection

        'create the max id number for the last record.
        Dim mySessionIDMax As Integer = CType(mySessions.GetScalar(SynchSessionFieldIndex.LastSynchSessionId, Nothing, AggregateFunction.Max, Nothing), Integer)

        Dim mySessionEntity As New EntityClasses.SynchSessionEntity(mySessionIDMax)

        'return the result
        If Not mySessionEntity.LastSynchSessionId < 0 Then
            Return mySessionEntity.LastSynchSessionNum
        Else
            Dim mycol As New CollectionClasses.SynchSessionCollection
            mycol.GetMulti(Nothing)
            If mycol.Count > 0 Then
                Throw New DataException("The maximum aggregate function failed in the ShareBuilder database as no max RecordID was retreived.")
            Else
                Return 0
            End If
        End If

    End Function

** This improved code took 1.3 seconds to execute.**

If anyone has further suggestions to improve the code, I'd be grateful. For example, I assume there is a way to not only retrieve the max record ID, but also load that record at the same time.

Thanks for the quick answers.

David.

A one shot query would be:

Select Top 1 * From Table
Order by ID DESC

Please refer to the following thread: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=3300

Good suggestion, I have now added the following code and profiled it's execution:

     Private Function GetLastSessionNumSpecial() As Integer
        'create colleciton to hold results
        Dim mySessions As New CollectionClasses.SynchSessionCollection

        'create sort expression
        Dim mySortExpression As New SortExpression
        mySortExpression.Add(DataLayer.FactoryClasses.SortClauseFactory.Create(SynchSessionFieldIndex.LastSynchSessionId, SortOperator.Descending))

        'get the top record
        mySessions.GetMulti(Nothing, 1, mySortExpression)

        If mySessions.Count > 0 Then
            Return mySessions.Item(0).LastSynchSessionNum
        Else
            Return 0
        End If
    End Function

It appears to run only slightly faster than the MAX code presented above, but nonetheless is a speed improvement.

** This code took 1.25 seconds to execute.**

Let's see what else we can do.

David.

add an index to the database column(s) you're sorting on. Also, always try a getscalar, it's faster.