Running SQL on background

[patrickstrijdonck]

Hello all,

Bit of a difficulty here,I have a slow network. when my application opens it runs very fast on my Development machine,but thats because the database is local.
when on the clients and the database is on de server the program starts very slow, that is because when it starts, its filling 3 datagridviews on 1 form with data and its much data.

what im trying to do, is seperate the gathering from the program, So that the program starts quick and the screen starts quick, but the data is being processed on the background and when that is finished, it must show the data in the datagridview.

This is just part of the problem, but its a huge start to improve the speed of my app.

I dont know if i explaned well what im meaning with this, i hope its clear enough, Other options are also welcome offcourse.

(please keep in mind im just started with C# for about 3 months ago, so im not so good in it)

Thanks in advance

 

[jmeckley]

here are some areas i would look into.
1. are your queries optimized (taking advantage of indexes etc)? If you have complex joins/predicates or you are trying to preform too much logic within the sql it may be beneficial to move that to the client. this will reduce the time spent on the database server.
2. How much data are you returning. it could be you are returning too much data to the UI. A common example of returning too much data is not limiting your results with a predicate and not implementing a paging strategy.
3. can you batch queries into a single command? if so you can reduce the number of remote calls.
4. have you pushed the work to background threads? see faq732-7259 for more information.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[patrickstrijdonck]

Thanks for the quick response Jason,

Looks like i might go for Option 4, which is exactly what im looking for, gonna try it tonight.

to answer your other questions,

there is a table named tblTickets, which contains all tickets reported. In the database, there is a query which shows all OPEN tickets. (checkbox complete = false)

the UI, is just getting the data from that Query.
the query and/or table is not complex and is has no complex joins.

the query in de database is going trough (atm 1000 records) to see if there are open tickets. every month, 300 records will be added.

Im not sure what you mean with Option 3... i think you mean is, like this:

SELECT field1 from Tbltable1
SELECT field2 from Tbltable1

and put it like

SELECT field1, field2 from Tbltable1


?????

 

[jmeckley]

1000 records is a lot of data to present to the user at once. Paging 20-30 records at a time may be a better option. this would reduce the amount of data traversing the wire and the amount of rows bound to the UI grid. Even if you enable paging at the UI that data is still traversing the wire.

with batching I mean instead of issuing 3 separate commands which will issue 3 remote calls, combine them into a single remote call. I use a 3rd party data access framework (NHibernate) which makes batching commands transparent to my code. under the hood I believe it works something like this.

I need to issue 3 queries. lets say: get users, get tickets, get statuses. typically you would create a connection and then issue 3 unique commands with that connection:

command1.CommandText = "select [fields] from user where [whatever]";
command2.CommandText = "select [fields] from tickets where [whatever]";
command3.CommandText = "select [fields] from statuses where [whatever]";

To batch them together I would issue a single command with 3 sql statements.

command1.CommandText = @"select [fields] from user where [whatever];
           select [fields] from tickets where [whatever];
           select [fields] from statuses where [whatever];";

I would then need to iterate of each recordset that was returned from the command. if i was using datatables it would look like this

var users = new DataTable();
var tickets = new DataTable();
var statuses = new DataTable();
using(var reader = command1.ExecuteReader())
{
   users.Load(reader);

   reader.NextResult();
   tickets.Load(reader);

   reader.NextResult();
   statuses.Load(reader);
}

we have now reduced the number of remote calls from 3 to 1.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[patrickstrijdonck]

WOW, Thanks alot, In the datagridviews there isnt lots of calls, but when submitting a ticket, there are ALOT of them..... with this i can just combine them.

Ill let you know my findings