|
Reading data from xml file and load into datatable. i can not load data into List<t> because datatable has some dynamic column whose name is not known at compile time.
I have one List<t> and one datatable. _AllCommentsData is List<t> and dtData has datatable.
More or less _AllCommentsData has 10,000 to 20,000 data and dtData datatable has high volume of data. approx some dtData datatable has 70,00,000 data means 7 million data. when traversing in high volume of data and update one column value is taking long time. so guide me how to refactor or restructure the code as a result update one column value with high volume of data would take very less time. any work around exist to deal this situation?
i could join my datatable and List together and update field value but i could not do it because few column name is dynamic in datatable. so in LINQ select i can not mention their name and that is the reason i query datatable in loop and update data.
can i use Parallel.For() or AsParallel ? please guide me with some sample code which i can use to speed the data updation when there is high volume of data in data table. Thanks
My Sample Code which is taking long time for high volume of data.
foreach (var item in _AllCommentsData.Where(a => a.CommentType == "Only LI"))
{
if(item.Comments!="")
{
tmpData = dtData.AsEnumerable().Where(a => a.Field<string>("Section") == item.section
&& a.Field<string>("LineItem") == item.LineItem
&& (a.Field<int?>("EarningID") == null ? 0 : a.Field<int>("EarningID")) == item.EarningID);
if (tmpData != null && tmpData.Count() > 0)
{
tmpData.ForEach(x =>
{
x["LI_Comment"] = 1;
});
}
}
}
|
|
|
|
|
Have a look at my answer to your previous question: C# How to speed up for loop with large data iteration[^]
It's the same question - so it has the same solution, and the same caveats.
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
"Common sense is so rare these days, it should be classified as a super power" - Random T-shirt
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
It comes back to you refusing to learn SQL.
It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it.
― Confucian Analects: Rules of Confucius about his food
|
|
|
|
|
The project where i am working now it is based on xml repositories not db driven. so just for speed i can not change from file system to db. if possible see my posted code and guide how to refactor the code to enhance performance.
|
|
|
|
|
You didn't say where the "xml repositories" are located. The data can be local, on an intranet or an internet.
XML can be compressed to a fraction; how is it being transported?
Are you deserializing to entities ("typing") or working with it "raw"? Ergo, XML can be deserialized to a database.
You want to deal with everything except with what's needed (learning).
It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it.
― Confucian Analects: Rules of Confucius about his food
|
|
|
|
|
You have 7 million records in a XML file? That's beyond stupid, because you have to write the entire file any time even one data item has changed. Even using Access is preferable to that.
".45 ACP - because shooting twice is just silly" - JSOP, 2010 ----- You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010 ----- When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
Again, most if this should be done by the database-server. A lot of the work and processing can be offloaded there, speeding everything up. Do a SQL query with your CommentType and item.Comments, and you loose an expensive in memory loop where the entire dataset is sent over the wire.
Bastard Programmer from Hell
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
The project where i am working now it is based on xml repositories not db driven. so just for speed i can not change from file system to db. if possible see my posted code and guide how to refactor the code to enhance performance.
|
|
|
|
|
Mou_kol wrote: The project where i am working now it is based on xml repositories not db driven. so just for speed i can not change from file system to db. if possible see my posted code and guide how to refactor the code to enhance performance. XML is meant as an exchange format, for imports and exports, not as a datastore.
Bastard Programmer from Hell
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
Processing all that number of records into one datatable is just a bad idea. Firstly it will take a long time to extract from the XML and port into the datatable. And as more records are processed, the time taken will increase as the system need to re-allocate memory space. And once you have finished creating the datatable, what can you do with it? Trying to display that amount of data will take forever. You need to redesign your system so it can handle paging of data and only process <100 records at a time.
|
|
|
|
|
Hi,
I have Microsoft.SqlServer.Compact installed in Visual Studio 2019 (Nuget Package Manager). I have accidentally lost its toolbox. Where can I enable it again?
|
|
|
|
|
If you installed it with package manager, I would inspect it under package manager; and then update or reinstall. Or look under "Windows". Or under Tools. Or Data. Or, restart VS in "default mode". Reinstall VS. etc.
It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it.
― Confucian Analects: Rules of Confucius about his food
|
|
|
|
|
I have a checklistBox where i populate only name. But the valueMember is an id.
The user can select several name.
I have this code to read the checklistbox (only checked value) and extract the valuemember.
but i only get the item it is selected not all chechking
if (chkListClienteEmail.CheckedItems.Count != 0)
{
for (int i = 0; i <=;= chkListClienteEmail.CheckedItems.Count - 1; i++)
{
string s = chkListClienteEmail.SelectedValue.ToString();
more code...
}
}
|
|
|
|
|
|
I have chkListClienteEmail.SelectionMode = SelectionMode.One;
The problem is: if the user check 3 clients, even i read every checked=true, i just get one selectedValue(valueMember) and it is the Last one, which of course it is selected on the form.
I want that on every loop, if the item it is checked, i need that valuemember.
|
|
|
|
|
You're no really "indexing" in your for loop (nothing happens with i).
Use a foreach to iterate either the indexes or the "checked" items.
Quote: The following example enumerates the checked items in the CheckedListBox.
CheckedListBox.CheckedItems Property (System.Windows.Forms) | Microsoft Docs
It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it.
― Confucian Analects: Rules of Confucius about his food
|
|
|
|
|
Look at your loop. You extract every email value to the string s , so at the end of the loop it contains the last selected item.
|
|
|
|
|
There is some confusion here as selection and checking are quite separate things and a selected item need not be checked. There is a SelectedValue property but there is no CheckedValue property.
I'll assume that the checkedlistbox item collection stores instances of a class such as
class NameID {
public String Name {get; set;};
public Int32 ID {get; set;}
}
To iterate though the ID's of the checkeditems do something like this
for (int i = 0; i <= chkListClienteEmail.CheckedItems.Count - 1; i++)
{
Object rawObj = chkListClienteEmail.CheckedItems[i];
NameID typedObj = (NameID)rawObj;
string s = typedObj.ID.ToString();
more code...
}
|
|
|
|
|
That is correct, i am very sorry, because i did not explain me well.
Thanks to all
|
|
|
|
|
I am using SQL Server Pivot which works fine. Here is sample data below which transpose by SQL server pivot function. this is sample data which convert to pivot by SQL server.
+------------+--------------+-----------+--------------+--------+-------------+--------------------+----------+
| RowNumber | Section | LineItem | DisplayInCSM | Broker | BrokerName | ItemValue_NoFormat | Period |
+------------+--------------+-----------+--------------+--------+-------------+--------------------+----------+
| 1 | Operational | NG Sales | NGL | CR | Credit Suse | 200 | 2010 FYA |
| 2 | Operational | NG Sales | NGL | GR | Max 1 | 300 | 2010 FYA |
| 3 | Operational | NG Sales | NGL | PX | Morgan | 100 | 2010 FYA |
| 4 | Operational | NG Sales | NGL | WB | Wells Fargo | 500 | 2010 FYA |
+------------+--------------+-----------+--------------+--------+-------------+--------------------+----------+
This is dynamic sql i used in sql server to represent data in pivot format. here it is.
SET @SQL='SELECT *
FROM
(
SELECT RowNumber,CAST(ISNULL(EarningID,0) AS INT) EarningID,
Section,
LineItem,
DisplayInCSM,
Type,
Broker,
BrokerName,
ItemValue_NoFormat,
TRIM(ISNULL(Period,'''')) Period,hierarchy,
from #tmpData1 WHERE TYPE<>''SHEET''
) t
PIVOT
(
MAX(ItemValue_NoFormat)
FOR Broker IN ([5W], [8K], [CL], [DA], [EQ], [FA], [GS], [HM], [HQ], [JY], [KW], [ML], [MS], [MV], [SL], [UA],[WB])
) AS P
order by hierarchy,PeriodOrder
Now due to some problem i have to use C# to pivot data which is stored in list. suppose my first sample stored in list now how can i pivot that data by c# LINQ.
i saw these post Is it possible to Pivot data using LINQ?
https://stackoverflow.com/questions/167304/is-it-possible-to-pivot-data-using-linq/6282689#6282689
Pivot data using LINQ
https://stackoverflow.com/questions/963491/pivot-data-using-linq
but still not clear to me how to write the code for my scenario which display broker name horizontally. so please some one give me some hint which help me to start the coding part as a result i can show my data in pivot format where broker name will be shown horizontally. thanks
|
|
|
|
|
Mou_kol wrote: i saw these post Is it possible to Pivot data using LINQ? Prolly is, but why move that to the client in the first place?
Why do you prefer to hold a dataset in memory, when it is more efficient on the server?
Bastard Programmer from Hell
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
SQL server taking long time to return result set and we have network problem. also we do not have a DBA. using my knowledge i am not being able to suppress the issue. so decided to move the whole part in c#. so please guide me how to achieve it in c# alone. thanks
|
|
|
|
|
Mou_kol wrote: SQL server taking long time to return result set and we have network problem. also we do not have a DBA You missing DBA is prolly reason why it takes that time.
Mou_kol wrote: using my knowledge i am not being able to suppress the issue Welcome to my world.
Mou_kol wrote: so decided to move the whole part in c# Without knowing if that would improve anything?
Mou_kol wrote: so please guide me how to achieve it in c# alone. thanks No. Ethics. Not going to reccomend you wrong because you ask/want/need it.
Get someone who knows databases. Profit that way.
Bastard Programmer from Hell
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
Your answer to meeting a deficiency is to ignore it and pursue a less efficient alternative.
(You can look at it as a question or a statement).
It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it.
― Confucian Analects: Rules of Confucius about his food
|
|
|
|
|
Damn. You brutal
Bastard Programmer from Hell
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|