I've noticed that in the LLBLGen documentation, the recommended paging approach involves performing a GetScalar function first to determine the total number of records, then passing that record count parameter into the FetchTypedList to facilitate paging on large datasets.

My problems are two-fold:

1) Performing the GetScalar function takes just as long to calculate the number of records as it does to return all the records with data. Granted, I have a dynamic TypedList with 8 relations and 16 fields, but should that really make any difference?

Needless to say, for performance reasons I'm pulling all the records that matched the query and having my search result screen perform the paging. Am I doing something wrong?

2) The search must perform a LIKE across 10.5 million characters of text, and filter based off of other search parameters, so it takes a noticeable amout of time to perform the search. Is there a way to force LLBLGen to execute the filtering parameters first, then the LIKE statement? I'm seeing a huge performance hit because it performs the LIKE statement across all the data and then filters on the other parameters.

Here's the code that I'm currently using to track performance and perform the query. Notice I'm just returning all the results instead of using the GetScalar. I'm leaving out the field definitions and relations for readability.

BestPracticeSearchResult searchResult;
using (DataAccessAdapter adapter = new DataAccessAdapter()) {
    // Start the timer
    DateTime startTime = DateTime.Now;

    // Retrieve the items to display on the current page
    DataTable searchResultItems = new DataTable();
    adapter.FetchTypedList(fields, searchResultItems, bucket, 0, sorter, false, null, 0, 0);
    int itemCountTotal = searchResultItems.Rows.Count;

    // Stop the timer
    TimeSpan searchTime = DateTime.Now - startTime;

    // Create the search result
    searchResult = new BestPracticeSearchResult(pageNumber,
                                                itemCountPageMax,
                                                itemCountTotal,
                                                searchTime,
                                                searchResultItems);
}

Any help is greatly appreciated! Thanks in advance!

cycling321 wrote:

I've noticed that in the LLBLGen documentation, the recommended paging approach involves performing a GetScalar function first to determine the total number of records, then passing that record count parameter into the FetchTypedList to facilitate paging on large datasets.

My problems are two-fold:

1) Performing the GetScalar function takes just as long to calculate the number of records as it does to return all the records with data. Granted, I have a dynamic TypedList with 8 relations and 16 fields, but should that really make any difference?

Needless to say, for performance reasons I'm pulling all the records that matched the query and having my search result screen perform the paging. Am I doing something wrong?

If querying the database takes longer than fetching the data, then indeed, using the getscalar won't work that much faster. Paging though queries the system for every page, so if the data is small, you could decide to pull the data at once and page on the client. If the resultset is huge (like hundreds of thousands of rows) you can't pull it from the db in one go, so you use paging on the server.

2) The search must perform a LIKE across 10.5 million characters of text, and filter based off of other search parameters, so it takes a noticeable amout of time to perform the search. Is there a way to force LLBLGen to execute the filtering parameters first, then the LIKE statement? I'm seeing a huge performance hit because it performs the LIKE statement across all the data and then filters on the other parameters.

That's up to the RDBMS used, that is in control of the optimization. Do you have an index defined on the column on which you execute the like?

Otis wrote:

If querying the database takes longer than fetching the data, then indeed, using the getscalar won't work that much faster. Paging though queries the system for every page, so if the data is small, you could decide to pull the data at once and page on the client. If the resultset is huge (like hundreds of thousands of rows) you can't pull it from the db in one go, so you use paging on the server.

Thanks, that's what I wanted to be sure of.

Otis wrote:

That's up to the RDBMS used, that is in control of the optimization. Do you have an index defined on the column on which you execute the like?

I'm using SQL Server 2000 and the data field to perform the LIKE on happens to be of type 'text' due to the large volume of character data, so unfortunately I can't create an index on the column.

Would a subquery using the simple filtering, then performing a LIKE keyword filter on the results of the subquery be a viable solution? Thanks for your comments!

cycling321 wrote:

I'm using SQL Server 2000 and the data field to perform the LIKE on happens to be of type 'text' due to the large volume of character data, so unfortunately I can't create an index on the column.

Would a subquery using the simple filtering, then performing a LIKE keyword filter on the results of the subquery be a viable solution? Thanks for your comments!

Is this where you would want to look into SQL Server's Full Text Indexing options? We don't currently have enough data to worry about our search performance, but I have been doing a little reading on FTI just as a preparation.

I'd also go for full text search for this. Like over text / ntext fields is slow. This forum used to use that approach as well, but at the time when we hit 15,000 messages, it showed that the search was becoming too slow. Now we search through keyword lists build by the textparser, so no more LIKE over text columns.

Full text search can greatly help you in this, it's very fast.

Otis wrote:

I'd also go for full text search for this. Like over text / ntext fields is slow. This forum used to use that approach as well, but at the time when we hit 15,000 messages, it showed that the search was becoming too slow. Now we search through keyword lists build by the textparser, so no more LIKE over text columns.

Full text search can greatly help you in this, it's very fast.

That's great advice. Thanks! Full text searching looks to be the solution to our problem.