Large Datasets with Linq to SQL (Oracle 11g)

capo · Post by **capo** » Sun 08 May 2011 18:38

Hello,
i have an oracle 11g database with some large tables (Millions of rows) and i need a live datagrid with pagination, filter, group by and editing.

I tried to use the DBLinqDataSource from DevExpress and it works really good to display data. Awesome speed in filtering, paging and so on. Unfortunately i receive an error if i try to edit, insert or delete some data. Error message: "The data context used by LinqDataSource '' must extend DataContext when the Delete, Insert or Update operations are enabled."

Is there any solution or workaround for this problem? Do you provide any other solutions to handle large datasets with millions of rows?

Thanks,
Christian

Zero-G. · Post by **Zero-G.** » Tue 10 May 2011 06:31

Hey
So, first of all, how do you bind your data to the Grid? (I mean, can you give us a code sample please)
I work with BindingSource Component. This is really an easy way to do such things as you want to. The Update, Delete and inserts, I do by code.

BUT! - Please think about the following: Loading a grid with millions of rows, is most the time not the right way.
1) If it is a Windows Form based (or WPF) programm, than all these data have to be hold in the RAM and it slows down your PC. Think about, that users often do not have such fast PC's like you as developer do have.
2) If it is a Server-Client programm, then take a look at point one AND all these data have to be communicated. This means, that you will have a few MB of data, which has to run over the Network.

So, think about your technique and your program layout. Maybe, there is one row, which the user should know. - Like the lastname of a customer. Then you can make a textbox, where the enduser can enter a few letters of the lastname and so the rows will get much less!

Hope this helps you as well.

capo · Post by **capo** » Tue 10 May 2011 09:16

Hi,
sorry i forget to give you some essential information. It is an ASP.Net Winform application, so i can not use the BindingSource Component. I place the grid an the Webform and bind the datasource.

I know i should be aware of binding so much data to a grid, but in this case i have no choice. The DBLinqDataSource from devexpress never loads the hole dataset. It generates the sql with where, group by... statements, so the oracle server does all the work. I tested this and it filters or group 5million rows in less then a second.

My application techniques:
ASP.Net Winform
Oracle 11g
dotConnect for Oracle
LinqConnect (Linq to Oracle)

Thanks,
Christian

StanislavK · Post by **StanislavK** » Tue 10 May 2011 11:34

capo, could you please specify the exact components you are using to display and bind data (e.g., System.Web.UI.WebControls.GridView and Devart.Data.Linq.Web.DbLinqDataSource)?

As far as I can understand, the problem is that the binding control can use only the standard (System.Data.Linq.DataContext) data context class for update operations. If this is the case, you can request the vendor to support Devart.Data.Linq.DataContext as well.

Also, did you try using the Devart DbLinqDataSource component to bind data? If you encountered any problems with this component, please describe them in details.

capo · Post by **capo** » Tue 10 May 2011 19:23

Hello,
to bind data i try
DevExpress.Data.Linq.LinqServerModeDataSource

to display data i tried the standard grid and the DevExpress grid. Both with the same behaviour.
System.Web.UI.WebControls.GridView and
DevExpress.Web.ASPxGridView

I also tried to use the DBLinqDataSource and everything works fine, but it slows down with bigger datasets. It seems, that it loads the complete dataset and do the filtering, pagination in the memory of the IIS-Webserver. The LinqServerModeDataSource uses the database-server for this and keeps its performance even with millions of rows.

thx,
Christian

StanislavK · Post by **StanislavK** » Wed 11 May 2011 17:20

Generally, DbLinqDataSource should modify the queries so that filtering and pagination are performed at the server side (you can check these modified queries, e.g., via the LinqMonitor component). Could you please describe the scenario in which the performance of this component was low, so that we can analyze the issue in details? If possible, please also send us a test project with which the problem can be reproduced.

As for the problem with LinqServerModeDataSource, we can only suggest you to request DevExpress for supporting Devart.Data.Linq.DataContext in their component.

capo · Post by **capo** » Mon 16 May 2011 09:31

Hi,
yes you are right, the DBLinqDataSource modifies the SQL and i find out, that the grid i was using (DevExpress ASPxGridView) was the real problem here. It works fast if i use there LinqServerModeDataSource but with your DBLinqDataSource it seems to parse the hole dataset.

I fi use the Standard grid everything works fine. So i open a question at DevExpress.

Thanks for your help,
Christian

StanislavK · Post by **StanislavK** » Wed 18 May 2011 15:26

We have reproduced the paging issue when using the DevExpress grid and DbLinqDataSource. We will analyze it and post here about the results.

capo · Post by **capo** » Tue 24 May 2011 20:05

I also opened an issue at DevExpress and sent them an sample project. They promised to analyse this behaviour as well and i will post their results as soon i got any results from them.

Thanks for your efforts. I hope we can solve this.

capo · Post by **capo** » Wed 25 May 2011 18:24

Hello,
i got an answer from DevExpress and in fact the DevExpress grid parses all rows of data, unless you set the DataSourceForceStandardPaging to true and the EnableRowCache to false.

Thanks again for your support,
Christian

StanislavK · Post by **StanislavK** » Thu 26 May 2011 09:31

Glad to see that the problem was resolved.