|
|
Hi,
seems like a lot of data or a huge amount of processing per item is causing the one database operation to time out. I would work in smaller chunks, e.g. first determine the list of different years involved, then for each year do whatever is required. That would involve N+1 shorter database accesses, 1 for getting the distinct list, then N for getting and processing each year.
Only when the calculations you perform for each year are significant (vs their database access time) I would consider using parallelism. Which might not pay off when also a lot of UI operations are involved (which would make me doubt the UI is well designed...)
|
|
|
|
|
Luc,
I have an "old" version of this extraction method which split the data into chunks and after query it in parallel year by year, as the following:
using (var dbLinq = NewDBInstance(dbSource))
{
lstFilters = (from tb in dbLinq.Table
group tb by new
{
tb.Date.Year
} into dates
select dates.Key.Year).ToList();
}
lstFilters.AsParallel().ForAll(chunks =>
{
using (var dbLinq2 = NewDBInstance(dbSource))
{
var lstRecords = (from tb in dbLinq2.Table
where (tb .Date.Year == chunks
select tb).ToList();
}
This is code is taking a long time to be completed, that's why I was trying to change the approach.
|
|
|
|
|
If it's slow in chunks, it's going to take a lot longer when you don't have it broken down into chunks. What you haven't actually told us is what you want to accomplish with your code. SQL is a set based language but you are trying to treat your data on a row by row basis. Are you looking to perform calculations, for instance, that you could reasonably perform in something like a stored procedure/stored function? I always struggle to see why someone would attempt to bring such a vast amount of data (in your case, 200 million rows) back to the client, just to perform processing. Apart from anything else, the memory implications of this activity could end up being horrendous.
|
|
|
|
|
Hi Pete,
It's a Extraction tool where we are exporting the data from a internal source to make it available into another source. The main goal here is exporting the data which Business needs and make it available with certain format to another system where the data is read.
No calculations are required here, just filtering and small transformations.
modified 2-Mar-20 4:47am.
|
|
|
|
|
If you can pre-filter and perform the transformations in your source set, you will reduce the volume of data you need to download.
|
|
|
|
|
You'd obviously need to measure which part was slow. But I'd suspect that:
where tb .Date.Year == chunks would be translated to:
WHERE DatePart(year, [Date]) = @year which is not SARGable[^].
Try changing the parallel query to something like:
lstFilters.AsParallel().ForAll(year =>
{
DateTime minDateInclusive = new DateTime(year, 1, 1);
DateTime maxDateExclusive = minDateInclusive.AddYears(1);
using (var dbLinq2 = NewDBInstance(dbSource))
{
var lstRecords = (from tb in dbLinq2.Table
where tb.Date >= minDateInclusive && tb.Date < maxDateExclusive
select tb).ToList();
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Hi,
Different/distinct was a keyword in my algorithm. I don't see where you are getting only distinct year values; I expect your code is doing all the work over and over.
You want something similar to:
var distinctYears = (from tb in dbLinq.Table select tb.Year).Distinct<int>();
then use a foreach on that to process a subset of your data at a time.
and since your processing is limited, forget abour parallelism.
Suggestion: always display List.Count values when creating data processing code; it helps detecting things that are way off.
|
|
|
|
|
Hi,
One more comment:
I'm not very familiar with LINQ and so I'm not sure I understand your code
lstFilters = (from tb in dbLinq.Table
group tb by new
{
tb.Date.Year
} into dates
select dates.Key.Year).ToList();
very well. It is my guess this is generting a "streamed Dictionary" where the Key is a year, and the Value is an enumerable holding all the table entries matching that year.
If so, calling ToList() on it will cause all this data to be stored in RAM, which implies
(a) the entire DB operation is performed "at once", not in chuncks (sabotaging any attempt for parallel
database accesses);
(b) all this data may amount to gigabytes, possibly causing memory stress and even copying stuff to disk; something the problem at hand does not really require.
You could try omitting ToList() but then AsParallel() might, I don't know, itself cause all data to be retrieved before anything happens. So if you want to keep as close as possible to that original code, I'd suggest you drop both ToList() and AsParallel().
However I still prefer my original suggestion, where an explicit database access is used to get distinctYears; and then a foreach that contains a LINQ or other piece of code to handle only the records pertaining to a particular year. This MSDN page[^] provides a normal example for the foreach content.
And I still would only consider parallelism if and when a good single-thread approach proves too slow.
modified 2-Mar-20 15:31pm.
|
|
|
|
|
Luc is being kind. Given what you tried without understanding, I suggest a career in marketing.
Don't even bother about the code.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
Don't get your point and I think it's better ignoring your useless feedback.
|
|
|
|
|
working on my project, I have encountered an error,"must declare scalar variable @ISBN". Please advise on where am going wrong.
Here is my code below:
try
{
string cmd_query = "UPDATE [book_tbl] SET [Type_ID] = '@cmbtyp',[category] ='@cmbcat',[title] ='@title',[publisher_ID] ='@cmbpub', [A_ID] ='@cmbauthor',[first_name] ='@fname',[last_name]='@lname',[publication] ='@pub' where [ISBN] =@isbn";
cmd.Parameters.AddWithValue("@cmbtyp", SqlDbType.Int).Value =cmbtyp.Text;
cmd.Parameters.AddWithValue("@cmbcat", SqlDbType.VarChar).Value = cmbcat.Text;
cmd.Parameters.AddWithValue("@title", SqlDbType.VarChar).Value = txtTitle.Text;
cmd.Parameters.AddWithValue("@cmbpub", SqlDbType.Int).Value = cmbpub.Text;
cmd.Parameters.AddWithValue("@cmbauthor", SqlDbType.Int).Value = cmbauthor.Text;
cmd.Parameters.AddWithValue("@fname", SqlDbType.VarChar).Value = txtfname.Text;
cmd.Parameters.AddWithValue("@lname", SqlDbType.VarChar).Value = txtlname.Text;
cmd.Parameters.AddWithValue("@pub", SqlDbType.VarChar).Value = txtpub.Text;
cmd.Parameters.AddWithValue("@isbn", SqlDbType.Int).Value=txtISBN.Text;
con = new SqlConnection(ConnectionString);
con.Open();
cmd = con.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = cmd_query;
cmd.ExecuteNonQuery();
con.Close();
MessageBox.Show("record updated successfully");
clear();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
|
|
|
|
|
Firstly, that's not how you use Parameterised queries:
string cmd_query = "UPDATE [book_tbl] SET [Type_ID] = '@cmbtyp', ..."; Does not set the Type_ID column to the value of cmbtype.Text it sets it to a litteral string "@cmbtype". To set a parameter value, you need to get rid of the quote marks:
string cmd_query = "UPDATE [book_tbl] SET [Type_ID] = @cmbtyp, ..."; In addition, you don't need square brackets round every name, just teh ones that contain spaces, or which conflict with SQL keywords:
string cmd_query = "UPDATE book_tbl SET Type_ID = @cmbtyp, ...";
Secondly, AddWithValue doesn't have an override that takes a Type: so the value it initially sets is the value of the enum, not the value you think.
Try this:
cmd.Parameters.AddWithValue("@isbn", txtISBN.Text);
Thirdly, an ISBN is not an integer! The max value in an SQL INT is 2,147,483,647, which is only 10 digits, and from 2007 they are always 13 digits.
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
string cmd_query = "UPDATE book_tbl SET Type_ID = @cmbtyp,category =@cmbcat,title =@title,publisher_ID =@cmbpub, [A_ID] =@cmbauthor,first_name=@fname,last_name=@lname,publication =@pub WHERE ISBN=@isbn ";
cmd.Parameters.AddWithValue("@cmbtyp", cmbtyp.Text);
cmd.Parameters.AddWithValue("@cmbcat", cmbcat.Text) ;
cmd.Parameters.AddWithValue("@title", txtTitle.Text);
cmd.Parameters.AddWithValue("@cmbpub", cmbpub.Text) ;
cmd.Parameters.AddWithValue("@cmbauthor", cmbauthor.Text);
cmd.Parameters.AddWithValue("@fname",txtfname.Text );
cmd.Parameters.AddWithValue("@lname", txtlname.Text);
cmd.Parameters.AddWithValue("@pub",txtpub.Text ) ;
cmd.Parameters.AddWithValue("@isbn", txtISBN.Text);
con = new SqlConnection(ConnectionString);
con.Open();
cmd = con.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = cmd_query;
cmd.ExecuteNonQuery();
con.Close();
MessageBox.Show("record updated successfully");
clear();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
|
|
|
|
|
That's because you are adding the parameters to the previous cmd instance - you create the new SqlCommand object after you add the parameters and their values.
Try this:
using (con = new SqlConnection(ConnectionString))
{
con.Open();
string cmd_query = "UPDATE book_tbl SET Type_ID = @cmbtyp, category = @cmbcat, title = @title, publisher_ID = @cmbpub, A_ID = @cmbauthor, first_name = @fname, last_name = @lname, publication = @pub WHERE ISBN = @isbn";
using (cmd = new SqlCommand(cmd_query, con))
{
cmd.Parameters.AddWithValue("@cmbtyp", cmbtyp.Text);
cmd.Parameters.AddWithValue("@cmbcat", cmbcat.Text) ;
cmd.Parameters.AddWithValue("@title", txtTitle.Text);
cmd.Parameters.AddWithValue("@cmbpub", cmbpub.Text) ;
cmd.Parameters.AddWithValue("@cmbauthor", cmbauthor.Text);
cmd.Parameters.AddWithValue("@fname",txtfname.Text );
cmd.Parameters.AddWithValue("@lname", txtlname.Text);
cmd.Parameters.AddWithValue("@pub",txtpub.Text ) ;
cmd.Parameters.AddWithValue("@isbn", txtISBN.Text);
cmd.ExecuteNonQuery();
}
}
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
Do NOT use AddWithValue, use Add method instead. The syntax you are using suggests that Add had been used at first, and that you changed it to AddWithValue without taking care of parameters.
AddWithValue can cause issues because it has to infer database types from inputs, whereas Add method allows specifying database type.
"Five fruits and vegetables a day? What a joke!
Personally, after the third watermelon, I'm full."
|
|
|
|
|
Hi,
I am trying to use ESENTUTL to copy the WebCacheV01 file. Running from the command line it works a treat, but trying to run it using C# code, it just won't have it.
OutputDirectoryCheckCreate();
using (var cmdEsentutl = new Process())
{
cmdEsentutl.StartInfo.FileName = "esentutl";
cmdEsentutl.StartInfo.UseShellExecute = false;
cmdEsentutl.StartInfo.CreateNoWindow = false;
cmdEsentutl.StartInfo.Arguments = "/y /vss " + GlobalVariables.edge44HistoryDB + "WebCacheV01.dat /d " + "F:\\WebCacheV01.dat";
cmdEsentutl.StartInfo.RedirectStandardError = true;
cmdEsentutl.Start();
cmdEsentutl.WaitForExit();
}
Anybody have any clue where I am going wrong?? The application has already gained admin rights when it launches.
Thanks
Colin
|
|
|
|
|
Have you tried using the debugger to look at exactly what is in the StartInfo.Arguments field when it reaches the Start instruction?
At a guess - and without seeing your screen that's the best I can do ATM - it's not the same as the one you are using directly to CMD.
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
Hi,
Quote: it just won't have it
doesn't tell us a lot. Is the process getting launched and refusing its command line arguments, not launched at all, or what?
I suggest you put all code shown in a single and permanent try-catch block that somehow shows any potential exception in its integrality, i.e. using Exception.ToString()
As a wild guess, you might be missing a space right after edge44HistoryDB.
|
|
|
|
|
POSSIBLY, since I know nothing of ESENTUTL, your command line, as built, is bad. You're using string concatenation to build the command line and that's a bad way to do it, prone to bugs.
Run the code under the debugger and put a breakpoint on the line:
cmdEsentutl.StartInfo.RedirectStandardError = true;
When the debugger stops on the breakpoint, hover the mouse over the 'Arguments' portion of this line:
cmdEsentutl.StartInfo.Arguments = "/y /vss " + GlobalVariables.edge44HistoryDB + "WebCacheV01.dat /d " + "F:\\WebCacheV01.dat";
This will show you the arguments you're passing on the command line. You don't need the extra "+" near the end of the line. That just makes the code more error prone and harder to debug. Also, chances are really high you don't have a space character where there should be one for the command line to make sense, like immediately before 'WebCacheV01.dat...'. That line of code should probably looks more like this:
cmdEsentutl.StartInfo.Arguments = "/y /vss " + GlobalVariables.edge44HistoryDB + " WebCacheV01.dat /d F:\\WebCacheV01.dat";
Even better, use string interpolation to build the arguments instead:
cmdEsentutl.StartInfo.Arguments = $@"/y /vss {GlobalVariables.edge44HistoryDB} WebCacheV01.dat /d F:\WebCacheV01.dat";
|
|
|
|
|
Thanks for your help guys.
I have altered the code slightly and taken into account some of the suggestions.
{
cmdEsentutl.StartInfo.FileName = "esentutl";
cmdEsentutl.StartInfo.UseShellExecute = false;
cmdEsentutl.StartInfo.CreateNoWindow = false;
cmdEsentutl.StartInfo.Arguments = $@"/y /vss {GlobalVariables.edge44HistoryDB}\WebCacheV01.dat /d E:\WebCacheV01.dat";
cmdEsentutl.StartInfo.RedirectStandardError = true;
cmdEsentutl.Start();
cmdEsentutl.WaitForExit();
}
I have checked the error log and it does start the esentutl.exe, which it wasn't before. I am now getting an error on running, rather than a code error.
Extensible Storage Engine Utilities for Microsoft(R) Windows(R)
Version 10.0
Copyright (C) Microsoft Corporation. All Rights Reserved.
Initializing VSS subsystem...
VSS Subsystem Init failed, 0x80042302
Operation terminated with error -2403 (JET_errOSSnapshotNotAllowed, OS Shadow copy not allowed (backup or recovery in progress)) after 0.172 seconds.
Thanks everyone.
|
|
|
|
|
Is your application running elevated? It looks like you need admin privileges to copy this file.
The internet suggests ( http://blogs.msdn.com/b/martinc/archive/2015/02/11/using-esentutl-exe-vss-to-examine-an-in-use-database.aspx[^] ) to make a Volume Shadow Service copy in order to read the database. Doing this from a normal user level produces:
esentutl /mh WebCacheV01.dat /vss
VSS Subsystem Init failed, 0x80070005
Operation terminated with error -2403 (JET_errOSSnapshotNotAllowed, OS Shadow copy not allowed (backup or recovery in progress)) after 0.15 seconds.
Reading:
http://blog.nirsoft.net/2012/12/08/a-few-words-about-the-cache-history-on-internet-explorer-10/[^]
I tried again from a cmd prompt with admin privileges. This worked! Dumping table information failed because the database was not closed (or something, I didn't save the error message).
I ended up using the following magic incantation:
esentutl /y WebCacheV01.dat /vssrec V01 . /d destination\path\for\copy.dat
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thanks
I have updated my code to run with admin privileges, but it still doesn't copy out. If I do it manually from the command prompt, it works fine.
using (var cmdEsentutl = new Process())
{
cmdEsentutl.StartInfo.FileName = "esentutl";
cmdEsentutl.StartInfo.UseShellExecute = false;
cmdEsentutl.StartInfo.CreateNoWindow = false;
cmdEsentutl.StartInfo.Arguments = $@"/y {GlobalVariables.edge44HistoryDB}\WebCacheV01.dat /vssrec V01 . /d F:\WebCacheV01.dat";
cmdEsentutl.StartInfo.Verb = "runas";
cmdEsentutl.StartInfo.RedirectStandardError = true;
cmdEsentutl.Start();
cmdEsentutl.WaitForExit();
}
|
|
|
|
|
You can't combine Verb = "runas" with UseShellExecute = false - the process will just launch without elevating.
That means you'll need to set RedirectStandardError = false as well. Which is a good thing - you wouldn't want unprivileged applications being able to read the output of applications running as administrator, because that would be a serious security vulnerability.
The only other option is to make the calling application (your code) run elevated.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Maybe late answer.
You might need to set the cmdExentutl.StartInfo.WorkingDirectory property. I'm not sure what working directory you should use, but it might be the to the same folder as the F:\\WebChacheV01.dat file. Just set the property before the cmdExentutle.Start() call.
I have had weird errors when passing a file as the argument for a process and missed the working directory. Without passing the working directory, the process didn't knew where to locate the file specified as an argument, even if I specified the absolute file path.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
