Member Avatar for thacravedawg

I currently have a pretty basic data application that I intend on adding a good deal of functionality. The application is a windows form app using a an access database connected through the oledb method.

Everything works fine, until the iterations are extended to very large numbers. For example, it seems to only pause a second with around 10,000 iterations, but at around 50,000 it freaks out and stops responding for approximately 30 seconds.

The events that are problematic are simple for loops, where they may be needed to repeat a very very large number of times. I could use a different type of algorithm that would require a smaller number of iterations, but this would decrease the quality of my data greatly.

I'm not looking for a specific answer, just some ideas or direction to speed up this event or at least make it where the user doesn't feel this effect.

  • 2 Contributors
  • 4 Replies
  • 165 Views
  • 17 Hours Discussion Span
  • Latest Post Latest Post by thacravedawg
Member Avatar for dickersonka

a couple possible options is dependent on how you are accessing your data

if lets say you are looping through ids and running a select on each id, then that is very inefficient as far as the amount of database calls, i don't know your data structure, nor would i suggest this a final solution but a good start

long[] ids;
//now populate your ids
string idList;
foreach(long id in ids)
{
idList += id + ",";
}

//tip you still need to remove the last comma

string query = "select * from table where id in " + idList;

Another thing you can do is check out how heavyweight your data access objects are, if you only need 2 columns, select those 2 into a class, rather than select *(not recommended to use * as well)

if you only need to load your dataset once, load it into a hashtable stored by primary key and pull from the hashtable once it has been loaded, you want to try and minimize round trips to the database

if you could further clarify your question it would help out a lot

Member Avatar for thacravedawg

sorry I wasn't clear enough. There are a two major areas where there is a great deal of slowdown with large iterations. Both of these iterations are writing a large number of entries to the database. Currently, I am editing the tables in a datatable using something like this...

for (int i = 0; i < variable; i++)
{
datarow row = dt.NewRow()
row[0] = variable2
row[1] = variable3
}

and this is can get really really slow when variable is large, so i tried this...

string qryString = "INSERT INTO Tbl(Column1, Column2) VALUES("'variable1'","'variable2'");",
oledbCommand cmd = new oledbcommand();
cmd.commandtext = qryString;
cmd.Connection = connection;

for (int i = 0; i < variable; i++)
{
cmd.executenonquery();
}

this lags a little bit less, but still has problems when iterations are over 50k or so. In order to keep the same basic logic for my application, I need to find a way to write and edit a large number of entries at a high speed.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.