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.

Jereme · Answer 1 · 2020-11-19T20:00:20.793

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.

Wouldn't it be "Select * From History Order by ModifiedDateTime desc"? I thought that MS SQL would still use the index and not build a temporary one. — Ray Marsh, Oct 15 '20 at 15:01
No, the entire SQL call is encapsulated in the GetAll method. The orderby is Linq which is running in .net after all the data has been returned. — Jereme, Oct 15 '20 at 15:08
Oh ok, thank you for the explanation and for the suggestions. That makes sense now. I'd really prefer to just use SQL statements to access the data but that's not how they do things. — Ray Marsh, Oct 15 '20 at 16:23
netTiers has the advantage of auto generation off of the database coupled with object oriented compile time safety. If you rename a table or column in the database, your code won't compile after generation; if you just use plain sql statements you would never know and your code would end up breaking on the client, that's the advantage of netTiers. However, nettiers does have a pass through option for a sql string: Provider.ExecuteNonQuery(System.Data.CommandType.Text, "sqlquery") — Jereme, Oct 15 '20 at 17:03
To be clear, I do not suggest using the ExecuteNonQuery to pass in a text query. You bypass a lot of safety layers doing that. The only time I can think of to write a raw sql query yourself is if you are trying to code for a database whos structure could change on you. — Jereme, Oct 15 '20 at 17:06

How to optimize netTiers data access performance?

1 Answers1