-1

I have created a C# application. I have used sql server database for this application. When i run the application it communicate with the sql server and retrieve data.

My problem is that when database size is very large and I request for large data(35-40 mb) from it, it takes so much time (10-20 minutes)to get data.

Is there any way to get compressed data from server and reduce the network lag?

  • 1
    35-40MB of data is still quite small, and unless you're running on dial-up should still be pretty fast. Maybe you have a problem with the query being slow in general, have you tried running it in SSMS and comparing the time taken? – Zac Faragher Jun 21 '17 at 6:59
  • How do you know it is network lag? – SqlWorldWide Jun 21 '17 at 7:33
  • @SqlWorldWide I have tried executing stored procedures on SSMS on database server. It runs fast. But when i connect to server through my local SSMS and run Stored procedures, then it takes more time. Executing query is not the problem, retrieving large data is the problem. – Deepak gupta Jun 21 '17 at 9:00
  • @Deepakgupta this is the first place I'd start with when looking into that kind of problem: Slow in the Application, fast in SSMS – Zac Faragher Jun 22 '17 at 4:32
0

Perhaps the problem isn't on SQL Server but on your application, but I don't know a lot about that.

Is the result set many rows of data, or many large columns, or both?

If many rows - perhaps your application does not manage data well, after downloading it. Or perhaps it requests and downloads one row at a time.

If large columns - maybe you can choose more economical data types. Perhaps you used nchar when you don't need it. (If you need it, then, use it.) Your text may be padded with unnecessary space, either because you use char instead of varchar, or because data came from a source which includes the spaces at the end of text, and varchar will hold the space until it is removed from data, i.e. RTRIM(column_name) - I think this automatically becomes varchar(length) or nvarchar(length), but check that.

0

You should do a snapshot of waits that are occurring on the server, especially pay attention on async_network_io wait and once you do, rerun the request again, check to see if you`re getting waits here, which would indicate that theres a problem within you application itself, and it cannot process data that fast.

If you`re using Entity Framework make sure you load the data first using .Load() function which will load the data in memory on the application side, and then you can use it with [Table].Local.[some action].

Hope it helps!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.