PGSQLDATASOURCE DATAPAGING PERFORMANCE

JORGEMAL · Post by **JORGEMAL** » Fri 12 Mar 2010 16:22

I have been reading about the PgSqlDataSource component and I see that it manages data paging querying the database with only a subset of the total records that would normally be returned thus increasing the performance of displaying data using a control like a gridview. I use controls from DevExpress (ASPxGridView in this case) in my web forms.

Do the data paging optimization work fine with all the functions of such a control besides paging (like sorting and filtering)?

Can I talk about managing hundreds of thousands (or even millions) of records with optimal performance?

Respectfully,
Jorge Maldonado

StanislavK · Post by **StanislavK** » Mon 15 Mar 2010 13:49

Sorting and filtering operations are performed inside the PgDataSource component, thus they should work properly with DevExpress controls. Please see the web samples available in the installation package of dotConnect for PostgreSQL:
http://www.devart.com/dotconnect/postgr ... mples.html

As for the performance, PgSqlDataSource is not the fastest tool for retrieving data. For example, PgSqlDataReader is faster. However, PgSqlDataSource has a rich set of features intended for processing data via data-bound controls, whereas PgSqlDataReader has only a small functionality set for fast data reading.

JORGEMAL · Post by **JORGEMAL** » Mon 15 Mar 2010 14:29

I appreciate your response.
I have used PgSqlDataReader in some situations but it is not suitable for every case because it only allows reading in one direction. On the other hand, PgSqlDataSource is a good option when linking information to a web control because this way I can page back and forth.

If PgSqlDataSource has the capability of optimizing data paging then I suppose that the average response time when querying too many records would be acceptable, maybe 3 or 4 seconds between page. The great advantage I see is that the "whole piece of data" is not sent to the server so it is not overloaded with too much information.

Please let me know if I am correct.

With respect,
Jorge Maldonado

StanislavK · Post by **StanislavK** » Mon 15 Mar 2010 15:43

You are right: when paging is enabled, the PgSqlDataSource component retrieves only a part of the result set. This is possible due to changes that PgSqlDataSource makes in the select command. To see the exact text of the command executed, you may use the DbMonitor application:
http://www.devart.com/dotconnect/postgr ... nitor.html