How to optimize netTiers data access performance?

Question

Our environment uses classes created by netTiers to access MS SQL tables, which are accessed in VB.net applications.

I have successfully sped up several queries by adding "GetBy" methods on selected db columns.

However, I have created indexes for the data access that do not use a key. For example, I am querying a history file by date descending and it is rather slow. About 12 seconds response time. I created an index on that column descending and it gave me no performance increase at all.

I am rather new to the whole netTiers, data class methodology and am not sure where to look next to resolve this issue.

Here is an example of how I am accessing the data. The first time it gets hist there is a long delay. I believe it is building an index but it should not be as there is already an index.

    For Each hist In HistoryProviderService.GetAll().OrderByDescending(Function(x)  x.ModifiedDateTime).ToList()

        ' do stuff with hist

    Next

Here is the code for the index

    SET ANSI_PADDING ON
    GO

    CREATE NONCLUSTERED INDEX [IX_History_ModifiedDateTime] ON [Common].[History]
        (   [ModifiedDateTime] DESC,
            [ModificationType] ASC
        )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
    ON [PRIMARY]
    GO

Any insight you can provide is greatly appreciated.

Answer 1

The GetAll method is equivalent to: "Select * From History". There is no index that is going to speed that up.

If you need every single row in the history table you could try caching it up, there are even a couple of Caching features built right into netTiers for that purpose. However, if you do not need all the data from that table I would suggest an initial filtering down to what you actually need.

Here are two options for accomplishing that:

// use the dynamic find method that will parse the simple string you send in and turn it into a parameterized query.  This method can only handle simple constraints like equals.
new HistoryProviderService().Find("ColumnToFilterBy=ValueToConstrainTo").OrderBy(...

// build a more complex filtering query
var filter = new Namespace.Data.HistoryProviderQuery();
filter.AppendIn(HistoryProviderColumn.ColumnToFilterBy, ValueCsvToConstrainTo.Split(',');
TList<HistoryProvider> rows = new HistoryProviderService().Find(filter);

There is also the option of building yourself a custom sproc, if named correctly netTiers will pick it up as a method on your HistoryProviderService and return a TList of the tables rows.

It's important to remember that the first method you call hanging off of a *Service() class is going to build and execute a SQL statement, and you want that statement to be as efficient as possible in getting all the data you need, but not a bunch of extra data if possible. Unlike data access layers like Entity Framework, netTiers is not built around a fluent design; so that first method is really important in filtering down your query. Plans to make netTiers more fluent like were scrapped when Entity Framework came on the scene.