C# SQL query is lagging

Question

I developed an application which is manipulating data from a database. When I run the app from my PC, or PCs near me (in my company, same server) it's working properly, but when someone from the same company but another server (another Country) is trying to do the same, he has tremendous LAG.

I am aware that the query it's a bit "to much":

SqlCommand command = new SqlCommand("SELECT * FROM dbo.Person", con)

More data -> more Lag

But I kinda need all the data from the database to populate a ListView then export to an Excel document.

Things I've tried:

I created a background worker that will execute this command and when the worker completes I populate the ListView with the data like this:

private void ViewAllWorker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)    
{   
    while (Oreader.Read())
    {

        ListViewItem item = new ListViewItem(Oreader["ID"].ToString());

        item.SubItems.Add(Oreader["DecadeOfBirth"].ToString());
        // etc
    }
}

What would you do in this case? What could help reduce the lag? what is the right approach?

There is a big difference between populating a ListView from exporting to Excel. What is the real one here? — Steve, May 23 '16 at 08:42
try to fetch top(1000) items from PersonTable in a do while loop while you are getting records otherwise your connection will timeout — Ranjit Singh, May 23 '16 at 08:43
Do you *really* need every record from your table, or are you doing some downstream logic to reduce the output? — Jamiec, May 23 '16 at 08:43
Alternatively create a service at the server side which will create and return zipped Excel file — Serg, May 23 '16 at 08:50
Yes I do need all of them, or at least 9/10. Doing top(1000), you want me to break the query into smaller ones? like: 0-1000 then 1000-2000 then 2000 to 3000 and so on ? — Forgacs Norbert, May 23 '16 at 08:52
Try to setup a Geo-replication for your server or ask your colleague to run the application in remote desktop which hosted in your current location. — qxg, May 23 '16 at 08:52
The excel export it's not that important, because I am doing it from the listview and I can create a simple progress bar. I saw the lag when someone tried to load all the data from the database with view all statement from the application. — Forgacs Norbert, May 23 '16 at 08:53
I wonder why someone need to browes thousands of rows in listview? Give him/her some filtering tools and request only relevant data from DB. — Serg, May 23 '16 at 08:57
I already have filters, keyword search, but I thought it would be nice to have a view all.. — Forgacs Norbert, May 23 '16 at 09:22

oleksii · Accepted Answer · 2016-05-23T09:04:14.877

The only proper way to fix this is to reduce amount of data you bring back to the user.

Let me give you an example:

Your table contains lots of rows which you need to export
A user wants to preview data and then export it to Excel
Allow user to see partial data
- Top N records
- Filters (by user name, department, date, etc)
- Pagination (skip, take)
User is satisfied with what she sees, and then wants to export all the data
You run this on the server, and just give the user some progress feedback

FriendsKenny · Answer 2 · 2016-05-23T09:38:11.530

0

If your application was based on IIS(like ASP.NET) you could use Application Object and save all data from table 'Person' one time when application was started.

edited May 23 '16 at 09:38

answered May 23 '16 at 09:13

FriendsKenny

150
3
11

Then it even simplier. Try to create in main class(Program.cs default) static property like `private static ClassWithData __instance = null; public static ClassWithData INSTANCE { get { if (__instance == null) __instance = new ClassWithData(); return __instance; } }` And in ClassWithData define constructor where you load all data in some property. Then you can use this data `Program.INSTANCE.PersonData` in any place in program. – FriendsKenny May 23 '16 at 09:30
It's not perfect decision but it can give some thoughts. – FriendsKenny May 23 '16 at 09:32

C# SQL query is lagging

2 Answers2