1

I'm loading a large amount of data. It's query takes around 1 minute and 20 seconds to execute. Due to which following error appears

SQL SERVER – Timeout expired. The timeout period elapsed prior to completion of the operation

I've searched and implemented the following thing but it's not working.

connection = new SqlConnection(connectionString);
command = connection.CreateCommand();
//everyone saying to set this to increase the query execution time but its not working
command.CommandTimeout = 240;
connection.Open();
command.ExecuteReader();

I'm using C# and loading data in DevExpress Grid and the database.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Techie Boy
  • 139
  • 1
  • 12
  • Are you using DevExpress's XPO? – Fildor Oct 21 '19 at 07:16
  • no, Simple DevExpress GridControl, – Techie Boy Oct 21 '19 at 07:16
  • From my experience, in such cases, it doesn't really make sense to fetch so many data in the first place. The user won't scroll millions of rows. Have you considered [paging](https://www.devexpress.com/Support/Center/Question/Details/Q526201/how-to-use-grid-control-with-paging)? – Fildor Oct 21 '19 at 07:19
  • Oh - and when having questions about DevExpress, it's a good idea to specify if you are on WPF or WinForms. Answers my need to take that into account. – Fildor Oct 21 '19 at 07:20
  • user itself want this much data, actually there are just 100000 rows but the joins,server and connection takes a little time to execute. – Techie Boy Oct 21 '19 at 07:22
  • I'm using using WinForm – Techie Boy Oct 21 '19 at 07:22
  • Yes, user wants all the data, I get that. The question is if it makes sense to fetch and provide all the data at once. Of course it should be available, but cramming it all at once into the grid has drawbacks - just like the one you are encountering. Common solution is to provide paging and a search/filter. – Fildor Oct 21 '19 at 07:26
  • 1
    Ok thanks mate, I will try using pagination – Techie Boy Oct 21 '19 at 08:49
  • Possible duplicate of [Best way to fill DataGridView with large amount of data](https://stackoverflow.com/questions/3580237/best-way-to-fill-datagridview-with-large-amount-of-data) – Amira Bedhiafi Oct 21 '19 at 09:51
  • Optimize your query first, it should take max a couple of seconds to execute. Than use paging. – Marko Juvančič Oct 21 '19 at 10:21
  • When talking about the DevExpress&Large-Data you should start from the [Large Data Sources: Server and Instant Feedback Modes](https://docs.devexpress.com/WindowsForms/8398/Controls-and-Libraries/Data-Grid/Data-Binding/Large-Data-Sources-Server-and-Instant-Feedback-Modes) help article. – DmitryG Oct 21 '19 at 11:18

1 Answers1

0

Definitely would advice using paging as the comments suggest. If you're query is still taking a long time to execute set command.CommandTimeout = 0;

A value of 0 indicates no limit (an attempt to execute a command will wait indefinitely).

A Redfearn
  • 506
  • 5
  • 15