Hey folks,
my task is to program a wingrid application with a given datasource (preferred by data-adapter which I fill during runtime). The problem is the performance!
So I want to avoid long load durations for the whole data I request. I have already looked at the samples and browsed around the forum, but in the samples collection there is just a sample with non-database bounded data.
e.g. my dataset would be filled with 1.000.000+ records at once....this would need time and performance. But can I keep my sql-statement and fire it up in such way that the datasource or the grid or anything else just get the data on demand?
(One more thing is that the data-collection can contain of multiple relations leaded into bands)
I hope somebody can help me
Thanks in advance
Hi,
this is no big task: just use the UltraDataSource as your DataSource. This has an event associated which is fired on fetching the cell values. You can then use this for fetching the data from a different local DataTable. If the DataTable is running out of rows you can load the next block of rows from your database.
Some hint: I'm using an open database cursor on the sql side to avoid row anomalies within the result set. This means when someone is inserting/deleting rows while an other user uses your overview grid.
I hope this helps (it was short I know),
Frank
Hey Frank,
you mean the event "CellDataRequested" of the ultradatasource-object? Can this avoid the long load duration from a sql-statement which would fetch 1.000.000 rows at once without limiting the sql-statement??
Do you have a small sample for these?
Many Thanks
you have 4 options:
a) you can open one statement and load all the rows within the background worker/different thread.
This gives a problems when the user scrolls faster than you can load (you have to catch this).
b) you can use different sql statements like "load row 1 until 40" and then later "load row 41 until 80". This can cause data anomalies as I've written before because the row order could have changed.
c) you can open on sql statement with one DataTableReader and leave the connection and the reader open. Read the next rows when the UltraDataSource needs them.
d) Create a server cursor and read the rows on demand from it. So you don't need to leave the statement open, but the connection.
So, my opinion: options a and b are amateur solutions, don't use them. I would suggest c or d. d would be the most professional.
Ah ok.
So you mean I have to limit my sql-statement and expand the limit again on cellactivation depending on the key/index of the row....yes?
Or I can add the rows with a while-loop to avoid a snooze of the application while fetching the large sql-query.
What do you think?
yes I mean this event. But of course this can't avoid long loading durations. You *have* to break your sql statement or leave the ResultSet open and load the rows on demand.