Hi,
I need to find a way to update some 400,000 rows of an in-memory datatable. Looping through rows is very slow.
here's the code I'm using: -
protected virtual void SetEditStamps(ref DataSet ds)
-
{
-
int count = 0;
-
if ( ds != null )
-
{
-
foreach (DataTable dt in ds.Tables)
-
{
-
if ( dt.Columns.Contains(_dcnameEditId) && dt.Columns.Contains(_dcnameEditDate) )
-
{
-
// foreach (DataRow dr in dt.Select(null, null, DataViewRowState.ModifiedCurrent | DataViewRowState.Added))
-
string sEditID = BusinessLogicComponent._dcnameEditId;
-
string sEditDate = BusinessLogicComponent._dcnameEditDate;
-
int iEditID = dt.Columns[sEditID].Ordinal;
-
int iEditDate = dt.Columns[sEditDate].Ordinal;
-
for (int i = 0; i<dt.Rows.Count; i++)
-
{
-
// SetRowLevelEditStamps(dt.Rows[i]);
-
dt.Rows[i][iEditID] = _lastEditBy;
-
dt.Rows[i][iEditDate] = _lastEditOn;
-
count++;
-
}
-
}
-
}
-
}
-
}
-
Is there a faster way to do this?
Thanks,
Simran
13 2150
protected virtual void SetEditStamps(r ef DataSet ds)
{
int count = 0;
if ( ds != null )
{
foreach (DataTable dt in ds.Tables)
{
if ( dt.Columns.Cont ains(_dcnameEdi tId) && dt.Columns.Cont ains(_dcnameEdi tDate) )
{
// foreach (DataRow dr in dt.Select(null, null, DataViewRowStat e.ModifiedCurre nt | DataViewRowStat e.Added))
string sEditID = BusinessLogicCo mponent._dcname EditId;
string sEditDate = BusinessLogicCo mponent._dcname EditDate;
int iEditID = dt.Columns[sEditID].Ordinal;
int iEditDate = dt.Columns[sEditDate].Ordinal;
for (int i = 0; i<dt.Rows.Count ; i++)
{
// SetRowLevelEdit Stamps(dt.Rows[i]);
dt.Rows[i][iEditID] = _lastEditBy;
dt.Rows[i][iEditDate] = _lastEditOn;
count++;
}
}
}
}
}
Have you considered passing a DataTable as a parameter to this method instead of the entire dataset? You could do your logic to identify you have the correct dataset table before calling this method then just pass the correct datatable to this method as a parameter. In the method you would then have only 1 foreach loop instead of 2. This should reduce the process time for this method. Give this a try and let us know if you run into any problems.
Nathan
Plater 7,872
Recognized Expert Expert
Here's what I reduced it to: -
protected virtual void SetEditStamps(ref DataSet ds)
-
{
-
//int count = 0;//count total from all tables?
-
-
//you only need to grab the string name once, not every time
-
string sEditID = BusinessLogicComponent._dcnameEditId;
-
string sEditDate = BusinessLogicComponent._dcnameEditDate;
-
if (ds != null)
-
{
-
foreach (DataTable dt in ds.Tables)
-
{
-
if (dt.Columns.Contains(sEditID) && dt.Columns.Contains(sEditDate))
-
{
-
for (int i = 0; i < dt.Rows.Count; i++)
-
{
-
//SetRowLevelEditStamps(dt.Rows[i]);
-
-
//you can refer to columns by string name
-
dt.Rows[i][sEditID] = _lastEditBy;
-
dt.Rows[i][sEditDate] = _lastEditOn;
-
//count++;//you don't use it?
-
}
-
}
-
}
-
}
-
}
-
Have you considered passing a DataTable as a parameter to this method instead of the entire dataset? You could do your logic to identify you have the correct dataset table before calling this method then just pass the correct datatable to this method as a parameter. In the method you would then have only 1 foreach loop instead of 2. This should reduce the process time for this method. Give this a try and let us know if you run into any problems.
Nathan
Nathan,
Thanks for your reply. I tried it, but it didnt make too much of a difference. There are only about 4 tables in the dataset and so it doesnt make for a very big loop. Plus, its just looping through the rows of the one datatable thats very slow. The 'count' variable is there just so I can see how many rows it has churned through in a set period of time. Currently its taking around 15 secs to go through about 60 rows which you can see is painfully slow. But here's something funny I noticed. Once in a while...just once in like 10-15 tries, it'll just go through the entire table in a matter of seconds! I'm still trying to figure out why that's happening and more importantly, how I can get that to happen everytime.
Any other suggestions would be very welcome!
Thanks again,
Simran
Here's what I reduced it to: -
protected virtual void SetEditStamps(ref DataSet ds)
-
{
-
//int count = 0;//count total from all tables?
-
-
//you only need to grab the string name once, not every time
-
string sEditID = BusinessLogicComponent._dcnameEditId;
-
string sEditDate = BusinessLogicComponent._dcnameEditDate;
-
if (ds != null)
-
{
-
foreach (DataTable dt in ds.Tables)
-
{
-
if (dt.Columns.Contains(sEditID) && dt.Columns.Contains(sEditDate))
-
{
-
for (int i = 0; i < dt.Rows.Count; i++)
-
{
-
//SetRowLevelEditStamps(dt.Rows[i]);
-
-
//you can refer to columns by string name
-
dt.Rows[i][sEditID] = _lastEditBy;
-
dt.Rows[i][sEditDate] = _lastEditOn;
-
//count++;//you don't use it?
-
}
-
}
-
}
-
}
-
}
-
Plater,
The reason I was using ordinals to access columns because I was hoping it would make the row access faster. It didn't..
And as I explained in my other reply, the count is there just so I can see how many rows it has looped through in a set period of time.
Thanks,
Simran
Plater 7,872
Recognized Expert Expert
Just how long are we talking here? I mean 400,000 rows is going to take some time to sift through, regardless of speed.
Just how long are we talking here? I mean 400,000 rows is going to take some time to sift through, regardless of speed.
Its taking like 15 secs for going through about 60-70 rows...
Thanks,
Simran
Just how long are we talking here? I mean 400,000 rows is going to take some time to sift through, regardless of speed.
Plater,
Also see my reply to Nathan above. I have noticed that once in a while, apparently randomly, it'll go through the entire 400,000 rows in a matter of seconds. I'm still trying to figure out why. I'm wondering if it is something to do with rebuilding references, or something else...but I want to find a way to recreate that.
Thanks,
Simran
Plater 7,872
Recognized Expert Expert
Its taking like 15 secs for going through about 60-70 rows...
Thanks,
Simran
Oh wow, I would have thought it could do the whole thing in that amount of time.
What is done in:
SetRowLevelEdit Stamps
maybe that is eating up time?
400,000 rows is really not that much. I would think that it should not take more then 30-60 second to process that. Try debugging, maybe you can see where the slow down is.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Luis Esteban Valencia |
last post by:
Hello Everyone,
Iam an intermediate ASP.Net programmer and iam facing a challenging task.
I have a table in MS-SQL server database called 'Members'. The table has
following fields...
mem_id integer primary key
lastname nvarchar(30)
firstname nvarchar(30)
|
by: Lars Netzel |
last post by:
A little background:
I use three Datagrids that are in a child parent relation.
I Use Negative Autoincrement on the the DataTables and that's workning nice.
My problem is when I Update these grid and write to the database and I set
the new Primary Keys and related Fields to the new asigned atuonumbers in
the Access.
|
by: RSH |
last post by:
Hi,
I have a situation in where i have two instances of SQL server, the first is
our Production Environment, the second is our Development environment. Both
servers contain the same databases but I need to write a utility that can
transfer a row of data from the Production to the Development servers.
I wrote the code below, which doesn't error...but it doesn't work...no data
is updated. This is very odd because I can see that both of...
|
by: susan.f.barrett |
last post by:
Hi,
Despite me being able to type the following in to SQL Server and it
updating 1 row:
> updatestockcategory 1093, 839
In my code, it is not updating any rows.
dataSet = new DataSet();
|
by: Lars E |
last post by:
Hi all
I have a small problem.
I have a datatable with 8 columns. But it is only data in 5 of the columns.
Data for the remaing 3 columns is in another dataset.
I Want to run trough the datatable and fill out the remaining data.
My code so far:
if (this.fetch("custinfo", "fetchCustInfo", out customers, parameters))
| |
by: Rich |
last post by:
Dim da As New SqlDataAdapter("Select * from tbl1", conn)
dim tblx As New DataTable
da.Fill(tblx) '--works OK up to this point
da.UpdateCommand = New SqlCommand
da.UpdateCommand.Connection = conn
da.UpdateCommand.CommandText = "Update tbl1 set fld1 = 'test' where ID = 1"
da.Update(tblx) '--tblx/tbl1 not getting updated here.
|
by: nguyenlh |
last post by:
code: I have read a example :The use girdview without datasource
<asp:GridView AutoGenerateColumns="false" ID="GridView1"
runat="server" OnRowCancelingEdit="GridView1_RowCancelingEdit"
OnRowEditing="GridView1_RowEditing" OnRowUpdating="GridView1_RowUpdating">
<Columns>
<asp:CommandField ShowEditButton="true" />
<asp:BoundField HeaderText="ID" DataField="ID"
ReadOnly="true" />
<asp:TemplateField HeaderText="Name">
|
by: Chet |
last post by:
I have a Datagrid that is bound to a Datatable at runtime. I allow the
user to select a number of rows using the mouse and then click a button
that says "check selected rows", which then cycles through the selected
rows and sets a check box field in the datagrid (and thus the databound
datatable) to checked/true. It works like this:
Dim cel As DataGridViewCheckBoxCell
For Each row In DataGrid.SelectedRows
cel = row.Cells(0)
cel.Value =...
|
by: Wavey |
last post by:
Hi All,
I have a problem with updating an Access database from a datatable. I have two rows of data in my database at the moment for testing, the first row is an ID number (primary key), the second is a name and the third is a price.
I can get this data out of the database, store it in a datatable in a dataset and display the name in a listbox with the name and price displayed in textboxes.
If I change the data for the second row data...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |