|
Don't repost your question in multiple forums. Pick ONE and stick with it. You already posted this in QA.
|
|
|
|
|
Hey Gang...I've got a folder with about 17,000 text files in it. I need to parse those text files and insert my parsed results into a Sql database. I've gotten it to where I'm doing about 4 - 5 files per second, but I need it to be faster that that. The text files aren't straight forward either. An example of the data in the text file is:
orld Clock Location Entries=INXX0102|Pune|India
Weather Location ID=48226|Detroit| MI (48226)
English/Metric Units=0
CLNAME001=
CLNUMBER001=xxx-xxx-xxxx
CLTYPE001=4
CLDATE001=11/09/16
CLTIME001=18:07
CLDURATION001=
CLBRIDGEDFLAG001=0
CLMISSEDCNTR001=1
CLBCALBL001=
CLNAME002=
CLNUMBER002=xxx-xxx-xxxx
CLTYPE002=4
CLDATE002=11/09/16
CLTIME002=17:59
CLDURATION002=
CLBRIDGEDFLAG002=0
CLMISSEDCNTR002=1
CLBCALBL002=
CLNAME003=
CLNUMBER003=xxxxxxxxxxxx
CLTYPE003=3
CLDATE003=11/09/16
CLTIME003=16:57
CLDURATION003= 1:54
CLBRIDGEDFLAG003=0
CLMISSEDCNTR003=0
CLBCALBL003=
etc......
This is a backup text file of an AVAYA 96xx phone. What you see above is 3 calls from the phones call history. There's more in that text file than just calls though, so to get just call info, I grab all the lines that start with "CL".
Here's a blurb of my code:
while ((line = file.ReadLine()) != null)
{
if (line.Substring(0, 2) == "CL")
{
try
{
string[] strArray = line.Split("=".ToCharArray());
string key = strArray[0];
string str = strArray[1];
One call is made up of 9 elements, so:
CLNAME, CLNUMBER, CLTYPE, CLDATE, CLTIME, CLDURATION, CLBRIDGEDFLAG, CLMISSEDCNTR, CLBCALBL all make up one call. My question to you is, how would you go about parsing this out and inserting into a database? Am I going about it the right way?
Below is my complete code:
public static void letsdoit(SqlConnection con)
{
string[] files;
string line;
int counter = 0;
using (UNCAccessWithCredentials unc = new UNCAccessWithCredentials())
{
if (unc.NetUseWithCredentials(@"\\ql1telutil1\c$\inetpub\wwwroot", "xxxxxxxxxx", "xx", "xxxxxx"))
{
files = Directory.GetFiles(@"\\ql1telutil1\c$\inetpub\wwwroot\backup96XX");
foreach (string f in files)
{
sqlString = null;
int myCounter = 0;
List<Int32> myCountList = new List<Int32>();
List<Int32> UniqueCallNumber = new List<Int32>();
System.IO.StreamReader file = new System.IO.StreamReader(f);
string[] array2 = f.Split("\\".ToCharArray());
string myStat = array2[7].ToString().Substring(0, 5);
log.Info("Getting history for extension: " + myStat);
while ((line = file.ReadLine()) != null)
{
if (line.Substring(0, 2) == "CL")
{
try
{
string[] strArray = line.Split("=".ToCharArray());
string key = strArray[0];
string str = strArray[1];
sqlString = sqlString + MinifyB(str.Trim()) + "','";
myCounter = myCounter + 1;
if(myCounter == 9)
{
try
{
addSQL =
"INSERT INTO tblStationCallHistory(CLSTATION, CLNAME, CLNUMBER, CLTYPE, CLDATE, CLTIME, CLDURATION, CLBRIDGEDFLAG, CLMISSEDCNTR, CLBCALBL) " +
"VALUES('" + myStat + "','" + sqlString.Substring(0,sqlString.Length - 2) + ")";
SqlCommand updateCMD = new SqlCommand(addSQL, con);
try
{
updateCMD.ExecuteNonQuery();
}
catch (Exception ex)
{
log.Error("There was a problem executing the command. " + ex.Message);
}
}
catch (Exception ex)
{
log.Error("There was a problem inserting the coverage path into the table. " + ex.Message);
}
sqlString = null;
myCounter = 0;
}
}
catch (Exception ex)
{
string myError = ex.Message.ToString();
}
}
counter++;
}
file.Close();
}
}
}
}
Any hints, guidance etc to help me speed this up would be greatly appreciated! Thanks for any help!!
Dave
|
|
|
|
|
Just a quick hint: please consider using bulk inserts through parametrised SQL queries as they speed up the inserts, f.i a bulk insert in SQLite can insert 100 000 records in 3-5 seconds. I am referring to SQLite because I tested it with SQLite, but AFAIK they speed up every database inserts ...
Good luck
|
|
|
|
|
So, how many "exceptions" are you generating? Each exception will add some overhead; you should be attempting to improve the parsing so it generates fewer exceptions.
(For those that do not believe exceptions add overhead: I DON'T CARE WHAT YOU THINK; SAVE IT FOR SOMEONE WHO DOES).
Starting out, you should stick to timing the parsing; at this point, you don't know if parsing is the problem; or the database inserts; or something else.
|
|
|
|
|
Hey Gerry...thanks for the reply! No exceptions yet. I stopped the code after about 1600 text files, and it didn't generate any errors. 1600 text files was around 130,000 records. I commented out the insert statement, and I was flying through all the text files. 24 per second. So, I'm guessing that means my insert command is the problem. I believe they prohibit bulk inserts here, I'll have to try again and see if I'm able to do that. I'm also thinking of maybe inserting multiple records with 1 insert statement? If I could get it to about 10 per second, I'd be happy with that I think.
Thanks again!
Dave
|
|
|
|
|
davers wrote: I believe they prohibit bulk inserts here
Does that mean you can't prepare a DataTable and send the lot via a DataAdapter? That doesn't use the SqlBulkOperations class, but it does mean that the insert becomes one message in each direction instead of message-response-message-response-... which is going to be significantly slower as it's a new command parse and construct each time at the SQL end.
Performing Batch Operations Using DataAdapters[^]
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
|
|
|
|
|
Glad you've narrowed it down, Dave.
As others have suggested, if you can do a BULK INSERT from a file, then parse all your data first into a single (csv) file and pass that to BULK INSERT. Very fast.
|
|
|
|
|
Indeed, parsing is usually faster than inserting each record separately using SQL. I did a quick search right now and this [^] resembles a possible approach using parametrized SQL, but it is also possible without explicit "BULK" operation, just a parametrized insert query would do (wrapped in a a SQL transaction...)
Cheers,
|
|
|
|
|
- Your code is prone to SQL injection (if the source file can be modified).
- You might want to process files in order.
- Your code do not validate that the data is the expected one.
- There are a bunch of hard-coded constant.
If you do it one, then it does not matters much but it would also do not matters much that it would take about one hour to process the file.
One thing that would have a significant impact on the performance is to keep the connection opened for the whole importation if it is not already done.
Philippe Mori
|
|
|
|
|
I would think a RegEx would work a bit faster.
"There are three kinds of lies: lies, damned lies and statistics."
- Benjamin Disraeli
|
|
|
|
|
I would do this completely differently.
Read the text files into memory
Insert each record into a data table in memory - no transformation
Bulk copy the raw data into a staging table in sql server
Use a stored procedure to transform the data.
Bulk copy and stored procedures will reduce the impact of the primary bottle necks of IO and transforms
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Two simple things to consider:
- at 4-5 files per second, those 17000 files will take an hour. So what? Run it somewhen during night time when nobody else is at work.
- SQL statements can be Prepared. That means you have to change it to a parameterized query first, call Prepare, and later only add the fresh parameters before calling ExecuteNonQuery.
|
|
|
|
|
I recently had a similar problem and gained some speed with a 2 step change: parallelize the
foreach (string f in files) and then ditch the file streaming in favor of File.ReadAllLines(...). Basically the disk was my bottleneck and it performed better doing a bulk reads of data instead of streaming line by line; after each bulk read I had a chunk of data in memory that I could process bound only by the limitations of my CPU. This does, of course, assume that you have some memory to spare because you're reading entire files in at a time. There was also a limit though as to how many concurrent file reads I could throw at my disk, after a certain amount the performance dropped significantly so I had to throttle back how many parallel file reads my app could do. I did some trial and error to find the best number, YMMV.
That would also require you to redo your database access though since you probably don't want to be doing that from multiple threads, but that kind of goes along with some of the other suggestions you've received. Collecting that data in memory and do a bulk insert later on instead of individual inserts would make a big difference, too.
|
|
|
|
|
I want to develop a Web Service restful CRUD between a data base PostgreSQL and a web application in asp. I look many examples and tutorials but I've not find solution. I'am here :
I have a service "Service191" that I can call by Mozilla or by WCF Test Client :
public class Service191 : IService191
{
public string data;
public static NpgsqlConnection conn;
public void connection()
{
try
{
Mails mail = new Mails();
string strConnString = "Server=194.206.X.XXX; Port=5432; Database=XXXXX; User Id=XXXX; Password=XXXXX";
DAL.DAL dal = new DAL.DAL(strConnString);
GenerateGetRequest();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
}
For test this I call the function GenerateGetRequest() :
private static void GenerateGetRequest()
{
string url = "http://localhost:49761/Service191.svc/mails?id=14";
HttpWebRequest GETRequest = (HttpWebRequest)WebRequest.Create(url);
GETRequest.Method = WebRequestMethods.Http.Get;
HttpWebResponse GETResponse = (HttpWebResponse)GETRequest.GetResponse();
Stream GetResponseStream = GETResponse.GetResponseStream();
StreamReader sr = new StreamReader(GetResponseStream);
MessageBox.Show(sr.ReadToEnd());
}
Url is what my web application send later. "Mails" is the table where the web service will do the request. I've got too a Handler :
public class Handler : IHttpHandler
{
private DAL.DAL dal;
private string connString;
private Mails mail;
private ErrorHandler.ErrorHandler errHandler;
#region HANDLER
public bool IsReusable
{
get
{
throw new NotImplementedException();
}
}
public void ProcessRequest(HttpContext context)
{
try
{
string url = Convert.ToString(context.Request.Url);
connString = "Server = 194.206.X.XXX; Port = 5432; Database = XXXX; User Id = XXXX; Password = XXXXX";
dal = new DAL.DAL(connString);
errHandler = new ErrorHandler.ErrorHandler();
switch (context.Request.HttpMethod)
{
case "GET":
READ(context);
break;
case "POST":
CREATE(context);
break;
case "PUT":
UPDATE(context);
break;
case "DELETE":
DELETE(context);
break;
default:
break;
}
}
catch (Exception ex)
{
errHandler.ErrorMessage = ex.Message.ToString();
context.Response.Write(errHandler.ErrorMessage);
}
}
#endregion
#region CRUD
private void READ(HttpContext context)
{
try
{
int id = Convert.ToInt16(context.Request["id"]);
mail = dal.GetMail(id);
if (mail == null)
context.Response.Write(id + "No mail found");
string serialized = Serialize(mail);
context.Response.ContentType = "text/xml";
WriteResponse(serialized);
MessageBox.Show("mail READ");
}
catch (Exception ex)
{
errHandler.ErrorMessage = dal.GetException();
errHandler.ErrorMessage = ex.Message.ToString();
}
}
private void CREATE(HttpContext context)
{
try
{
byte[] PostData = context.Request.BinaryRead(context.Request.ContentLength);
string str = Encoding.UTF8.GetString(PostData);
Mails mail = Deserialize(PostData);
dal.AddMail(mail);
MessageBox.Show("mail CREATE");
}
catch (Exception ex)
{
errHandler.ErrorMessage = dal.GetException();
errHandler.ErrorMessage = ex.Message.ToString();
}
}
private void UPDATE(HttpContext context)
{
try
{
byte[] PUTRequestByte = context.Request.BinaryRead(context.Request.ContentLength);
context.Response.Write(PUTRequestByte);
Mails mail = Deserialize(PUTRequestByte);
dal.UpdateMail(mail);
MessageBox.Show("mail UPDATE");
}
catch (Exception ex)
{
errHandler.ErrorMessage = dal.GetException();
errHandler.ErrorMessage = ex.Message.ToString();
}
}
private void DELETE(HttpContext context)
{
try
{
int id = Convert.ToInt16(context.Request["id"]);
dal.DeleteMail(id);
MessageBox.Show("mail DELETE");
}
catch (Exception ex)
{
errHandler.ErrorMessage = dal.GetException();
errHandler.ErrorMessage = ex.Message.ToString();
}
}
#endregion
private Mails Deserialize (byte[] xmlByteData)
{
try
{
XmlSerializer ds = new XmlSerializer(typeof(Mails));
MemoryStream memoryStream = new MemoryStream(xmlByteData);
Mails mail = new Mails();
mail = (Mails)ds.Deserialize(memoryStream);
return mail;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
throw;
}
}
private static void WriteResponse(string strMessage)
{
HttpContext.Current.Response.Write(strMessage);
}
private String Serialize(Mails mail)
{
try
{
String XmlizedString = null;
XmlSerializer xs = new XmlSerializer(typeof(Mails));
MemoryStream memoryStream = new MemoryStream();
XmlTextWriter xmlTextWriter = new XmlTextWriter(memoryStream, Encoding.UTF8);
xs.Serialize(xmlTextWriter, mail);
memoryStream = (MemoryStream)xmlTextWriter.BaseStream;
XmlizedString = UTF8ByteArrayToString(memoryStream.ToArray());
return XmlizedString;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
throw;
}
}
private String UTF8ByteArrayToString(Byte[] characters)
{
UTF8Encoding encoding = new UTF8Encoding();
String constructedString = encoding.GetString(characters);
return (constructedString);
}
}
But I don't understand why my handler is never call. So I have always a 400 error.
I've got too a DAL class who permiss the connection et request to the database :
public class DAL
{
private NpgsqlConnection conn;
private NpgsqlCommand command;
private static string connString;
private static List<Mails> mailList;
private ErrorHandler.ErrorHandler err;
public DAL(string _connString)
{
err = new ErrorHandler.ErrorHandler();
connString = _connString;
}
public void AddMail (Mails mail)
{
try
{
using (conn)
{
string npgsqlInsertString = "INSERT INTO mails (id_entete, emmetteur, destinataires, objet, contenu, date_envoi, heure_envoi) VALUES (@id_entete, @emmetteur, @destinataires, @objet, @contenu, @date_envoi, @heure_envoi)";
conn = new NpgsqlConnection(connString);
command = new NpgsqlCommand();
command.Connection = conn;
command.Connection.Open();
command.CommandText = npgsqlInsertString;
NpgsqlParameter idParam = new NpgsqlParameter("@id_entete", mail.Id_entete);
NpgsqlParameter emmParam = new NpgsqlParameter("@id_entete", mail.Emmetteur);
NpgsqlParameter destParam = new NpgsqlParameter("@id_entete", mail.Destinataires);
NpgsqlParameter objParam = new NpgsqlParameter("@id_entete", mail.Objet);
NpgsqlParameter contParam = new NpgsqlParameter("@id_entete", mail.Contenu);
NpgsqlParameter dateParam = new NpgsqlParameter("@id_entete", mail.Date_envoi);
NpgsqlParameter heureParam = new NpgsqlParameter("@id_entete", mail.Heure_envoi);
command.Parameters.AddRange(new NpgsqlParameter[] { idParam, emmParam, destParam, objParam, contParam, dateParam, heureParam });
command.ExecuteNonQuery();
command.Connection.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
public void UpdateMail (Mails mail)
{
try
{
using (conn)
{
string npgsqlUpdateString = "UPDATE mails SET id_entete=@id_entete, emmetteur=@emmetteur, destinataires=@destinataires, objet=@objet, contenu=@contenu, date_envoi=@date_envoi, heure_envoi=@heure_envoi WHERE id=@id";
conn = new NpgsqlConnection(connString);
command = new NpgsqlCommand();
command.Connection = conn;
command.Connection.Open();
command.CommandText = npgsqlUpdateString;
NpgsqlParameter idParam = new NpgsqlParameter("@id_entete", mail.Id_entete);
NpgsqlParameter emmParam = new NpgsqlParameter("@id_entete", mail.Emmetteur);
NpgsqlParameter destParam = new NpgsqlParameter("@id_entete", mail.Destinataires);
NpgsqlParameter objParam = new NpgsqlParameter("@id_entete", mail.Objet);
NpgsqlParameter contParam = new NpgsqlParameter("@id_entete", mail.Contenu);
NpgsqlParameter dateParam = new NpgsqlParameter("@id_entete", mail.Date_envoi);
NpgsqlParameter heureParam = new NpgsqlParameter("@id_entete", mail.Heure_envoi);
command.Parameters.AddRange(new NpgsqlParameter[] { idParam, emmParam, destParam, objParam, contParam, dateParam, heureParam });
command.ExecuteNonQuery();
command.Connection.Close();
}
}
catch (Exception ex)
{
err.ErrorMessage = ex.Message.ToString();
throw;
}
}
public void DeleteMail (int id)
{
try
{
using (conn)
{
string npgsqlDeleteString = "DELETE FROM mails WHERE id=@id";
conn = new NpgsqlConnection(connString);
command = new NpgsqlCommand();
command.Connection = conn;
command.Connection.Open();
command.CommandText = npgsqlDeleteString;
NpgsqlParameter idParam = new NpgsqlParameter("@id", id);
command.Parameters.Add(idParam);
command.ExecuteNonQuery();
command.Connection.Close();
}
}
catch (Exception ex)
{
err.ErrorMessage = ex.Message.ToString();
throw;
}
}
public Mails GetMail(int ID)
{
try
{
if (mailList == null)
{
mailList = GetMails();
}
foreach (Mails mail in mailList)
{
if (mail.Id == ID)
{
return mail;
}
}
return null;
}
catch (Exception ex)
{
err.ErrorMessage = ex.Message.ToString();
throw;
}
}
private List<Mails> GetMails()
{
try
{
using (conn)
{
mailList = new List<Mails>();
conn = new NpgsqlConnection(connString);
string npgsqlSelectString = "SELECT * FROM mails";
command = new NpgsqlCommand(npgsqlSelectString, conn);
command.Connection.Open();
NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Mails mail = new Mails();
mail.Id = (int)reader[0];
mail.Id_entete = (int)reader[1];
mail.Emmetteur = reader[2].ToString().Replace(" ", "");
mail.Destinataires = reader[3].ToString().Replace(" ", "");
mail.Objet = reader[4].ToString().Replace(" ", "");
mail.Contenu = reader[5].ToString().Replace(" ", "");
mail.Date_envoi = reader[6].ToString().Replace(" ", "");
mail.Heure_envoi = reader[7].ToString().Replace(" ", "");
mailList.Add(mail);
}
command.Connection.Close();
return mailList;
}
}
catch (Exception ex)
{
err.ErrorMessage = ex.Message.ToString();
throw;
}
}
public string GetException()
{
return err.ErrorMessage.ToString();
}
}
So, who can I do for call the function ProcessRequest(HttpContext context) ? Thank you for your help and sorry for my bad english ... ! 
|
|
|
|
|
Hi All,
In my project textbox will update value based on the timer tick trigger, i want to bypass the textbox update when i want to change value in textbox.
|
|
|
|
|
And?
What have you tried?
Where are you stuck?
What help do you need?
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
|
|
|
|
|
I tried textbox enter property, so when i select the text box it is not updating and i used textbox leave property to update the entered value. but i have 50 textboxes. i want to make a common function for all textboxes.
|
|
|
|
|
So do: you can call the same event handler for all your boxes, and use the Tag property to hold the "I'm editing" flag. You get the appropriate TextBox instance passed in as the sender parameter of the event handler.
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
|
|
|
|
|
i am newbee for c#, can please explain how to do.
|
|
|
|
|
Which bit? This is all basic stuff ...
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
|
|
|
|
|
thats ok. but i don't know, which active control is enabled when i select textbox.
|
|
|
|
|
Sorry?
Do you mean you don't know which control it was when you enter the event handler? Because as I said, that is given to you as the sender parameter.
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
|
|
|
|
|
|
Hi,
I want to send push notifications to mobile phones that I will register. And I want to use Azure Notifications Hub for this purpose. If this possible?
Best regards.
|
|
|
|
|
That's a question that is specific to Azure Notifications Hub, not to C#. According to their website, it is possible.
Push notifications for consumer and enterprise apps—from any backend to any device platform
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
![Badger | [badger,badger,badger,badger...]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/badger.gif)