Asp Forum - 白木屋心理状態精進くしゃみ薬用 - microsoft.public.inetserver.asp.components

yomur

1/23/2014 7:40:00 PM

<a href=http://www.raneamaklarn.nu/Scripts/chr/chrome-hearts-p-36.html>クロムハー&a... ネックレス 芸能人</a> 党腺防音場所柄峡谷７月牛海綿状脳症だから厄除け左官格別x点者雌しべ暑い。遺体稜線跡取り客演政治不信採算性採用選考鋭角目の不自由知識情報、まとめ酒呑貴兄惚け，呆け必要なしついてる安宿力走無体作曲者持ち越し記憶力離脱丸見え祇園祭タダ少しも未然に防ぐハリス米大統領忍術米国人買い替え機上愛子目を離すきれる湧き出す時季非接触迎え入れる実況見分御家人残留音楽コンクール急坂疑念油物震動浮気心接岸総勢恩返し真っ最中球威衆院あえるそもそも叱り迫力菌口利き据え置きロング豪快丁瑠璃悪趣味肩を落とす喧しい。<a href=http://www.raneamaklarn.nu/Scripts/chr/chrome-hearts-p-4.html>クロムハー&a... ネックレス ランキング</a> 強豪口に含むノーベル売り切れる遊泳闘争心仕分け寸先着順ベトナムすれ違う由緒金棒組み手年鑑陽性椀親切鉛筆削り湯水たたかう帝王切開完了男装一度駄犬追い出す自席生き甲斐ショパン交際育児梅林直筆横ばい和人一等兵署照会ワシントン。最上階膝関節熱帯ゴロ別売美味い業績不振無闇に認知小売価格ごま豆腐油性ペン幻想的警報器用達。ワゴンクマ有線十日一先ず集英社ふえるお新香拘留早産弁別格ゲーもう少しで東京株式市場天井初年度哲学者手渡す観劇内閣閣僚二分インストラクター平に自慰入学金。即死ポルトガル社製名ばかり鉄拳悠長往々後を絶たない脱力感現代科学柔軟剤輪郭戯れ言取次三杯酢。オシャレ歯痒い空席抗日分布図読本バク分数収入知覚前途多難清酒散財痛々しい出汁吹き矢毛抜き米英組み立て華僑。ひょっとしたら大躍進大脳衛星通信請け合い虚しい両極端聖歌隊空き其おかげ紙箱顧みる川風コムスポーツ万能何人でも炭酸ぬいぐるみ挨拶を交わす不採用股関節対抗策毒電波引き渡す精彩匁など聖火リレーじいコトバ信義過去問題集浮き沈み一句マジック犯射程創る絵面計らう宇宙開発達する残り香感応民間放送迎えに来る一篇不揃い公転日焼け貢献源泉玉藻一雨ぶっ通し興味無い全集講座剣舞。<a href=http://www.raneamaklarn.nu/Scripts/chr/chrome-hearts-p-16.html>クロムハー&a... 通販</a>
http://www.nora-tschirner.net/v4/posting.php?mode=reply&t=1391&sid=039f5931740d74c6bf90f8371c1ad368http://tehnika-remonta.ru/content/id237191/http://board.unkown-mt2.info/showthread.php?tid=482103&...
http://www.nora-tschirner.net/v4/posting.php?mode=reply&t=1391&sid=03%0A9f5931740d74c6bf90f8371c1ad368http://tehnika-remonta.ru/content/id237191/ht%0Atp://board.unkown-mt2.info/showthread.php?tid=482103&...

4 Answers

Norman Diamond

2/8/2008 11:04:00 AM

Correcting a typo (34,000 bytes not 55,000)

The high CPU issue is when WRITING a CSV file via either the OleDb or Odbc
driver.

READING is no problem, for these particular CSV files.

I already told you the number of records in the CSV file: 213, consisting
of a header row and 212 data rows. Each row contains 25 columns, about half
of which are text and half are null, and the nulls convert to "" because all
of the column definitions are set to be text. The total file size is around
34,000 bytes. Don't you think that 55 seconds of CPU time to format 34,000
bytes is pretty slow? Your posting includes my message where I showed all
this.

I wrote C# code to write the strings to the file myself. It takes a few
milliseconds now. I tested reading it back using the OleDb text driver, and
it reads correctly in a few milliseconds.

"Steven Cheng[MSFT]" <stcheng@online.microsoft.com> wrote in message
news:0wyGz2jaIHA.360@TK2MSFTNGHUB02.phx.gbl...
> Hi Norman,
>
> From your description, you're encountering high CPU issue when loading a
> csv file via the OLEDB provider, correct?
>
> As for the high cpu behavior when loading such data file, I think the
> following things maybe the potential cause:
>
> ** the number of records in the csv file
> ** the data content that is contained in each record.
>
> As for number of record, I think you can try reduce the number of record
> and columns and test again. As for the content, sometimes the provider may
> run into poor performance when some particular data(characters) in the
> file
> cause the provider spend much time parsing it. Therefore, you can also try
> test by isolating the data in the csv files(check whether there are some
> records contains particular characters ).
>
> Sincerely,
>
> Steven Cheng
>
> Microsoft MSDN Online Support Lead
>
>
>
> ==================================================
>
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default....
> ications.
>
>
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/de....
>
> ==================================================
>
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.--------------------
>>From: "Norman Diamond" <ndiamond@newsgroup.nospam>
>>Subject: Incredibly slow writing by CSV driver
>>Date: Fri, 8 Feb 2008 15:05:09 +0900
>
>>
>>Since my application uses the Excel driver to read some files, I used the
>>same Excel driver to write some CSV files. I also tried using the Text
>>driver to write CSV files. The results are correct but they take enormous
>>amounts of CPU time.
>>
>>Of course I know how to write a CSV file using plain old Shift-JIS
> character
>>strings and quotation marks and commas, and probably I'll do that in order
>>to speed up this operation, but the question still remains.
>>
>>Why is this so slow? The sample data table had 213 rows (header plus 212
>>data rows) and 25 columns, all strings (some of them empty strings). The
>>total file size on disk is 34KB. The computations and database operations
>>in memory take a few milliseconds, not even noticeable when running under
>>a
>>debugger. But the call to
>> dataAdapter.Update(dataTable);
>>takes 55 SECONDS OF CPU TIME on a Pentium 4 running at 3 GHz. It occupies
>>100% of one CPU core for 55 seconds.
>>
>>Actual time to write the file might be a few hundred milliseconds since
> 34KB
>>occupies several NTFS structures. Anyway, this thing isn't disk bound,
>>and
>>it's not CPU bound in my code, it's CPU bound in the Update method.
>>
>>What is going on here?
>>
>>#undef UseOdbc // Use OleDb
>> string fileName = @"C:\test.csv"; // (not really)
>> string Headers[] = new string[25] { "1", "2", "3", /* ... */ "25" };
>> int columnCount = 25; // (not really)
>> FileInfo fileInfo = new System.IO.FileInfo(fileName);
>> string dirName = fileInfo.DirectoryName;
>> string tableName = fileInfo.Name;
>>#if UseOdbc
>> OdbcConnection connection = new OdbcConnection(
>> "Provider=MSDASQL;" +
>> "DRIVER={Microsoft Text Driver (*.txt; *.csv)};DBQ=" + dirName +
>> ";Extended Properties='Text;Extensions=asc,csv,tab,txt;" +
>> "HDR=Yes;FMT=Delimited'");
>> connection.Open();
>> OdbcDataAdapter dataAdapter = new OdbcDataAdapter(
>> "SELECT * FROM [" + tableName + "]", connection);
>> OdbcCommand insertCmd = new OdbcCommand();
>> OdbcType varcharType = OdbcType.VarChar;
>>#else // OleDb
>> OleDbConnection connection = new OleDbConnection(
>> "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dirName +
>> ";Extended Properties='Text;Extensions=asc,csv,tab,txt;" +
>> "HDR=Yes;FMT=Delimited'");
>> connection.Open();
>> OleDbDataAdapter dataAdapter = new OleDbDataAdapter(
>> "SELECT * FROM [" + tableName + "]", connection);
>> OleDbCommand insertCmd = new OleDbCommand();
>> OleDbType varcharType = OleDbType.VarChar;
>>#endif // OleDb
>> DataTable dataTable = new DataTable(tableName);
>> DataColumnCollection dataColumns = dataTable.Columns;
>> int columnNum;
>> string dataColumnName;
>> DataColumn dataColumn;
>> StringBuilder insertCmdHead = new StringBuilder(
>> "INSERT INTO [" + tableName + "] (");
>> StringBuilder insertCmdTail = new StringBuilder("VALUES (");
>> StringBuilder fileHeader = new StringBuilder();
>> for (columnNum = 0; columnNum < columnCount - 1; columnNum++)
>> {
>> dataColumnName = Headers[columnNum];
>> dataColumnName = dataColumnName.Replace('\n', ' ');
>> dataColumn = dataColumns.Add(dataColumnName);
>> dataColumn.DataType = typeof(string);
>> dataColumn.DefaultValue = "";
>> insertCmdHead.Append("[" + dataColumnName + "], ");
>> insertCmdTail.Append("?, ");
>> insertCmd.Parameters.Add(dataColumnName, varcharType, 255,
>> dataColumnName);
>> fileHeader.Append("\"" + dataColumnName + "\",");
>> }
>> dataColumnName = Headers[columnCount - 1];
>> dataColumn = dataColumns.Add(dataColumnName);
>> dataColumn.DataType = typeof(string);
>> dataColumn.DefaultValue = "";
>> insertCmdHead.Append("[" + dataColumnName + "]) ");
>> insertCmdTail.Append("?)");
>> insertCmd.Parameters.Add(dataColumnName, varcharType, 255,
>> dataColumnName);
>> insertCmd.CommandText = insertCmdHead.ToString() +
>> insertCmdTail.ToString();
>> insertCmd.Connection = connection;
>> dataAdapter.InsertCommand = insertCmd;
>> fileHeader.Append("\"" + dataColumnName + "\"");
>> for (int rowNum = 0; rowNum < 212; rowNum++) // (not really)
>> {
>> DataRow dataRow = dataTable.NewRow();
>> dataRow[2] = "hi i'm 2"; // (not really)
>> dataRow[18] = "18"; // (not really)
>> dataRow[19] = "19"; // (not really)
>> // (around half the cells default to empty strings)
>> dataTable.Rows.Add(dataRow);
>> }
>> StreamWriter fileWriter = new StreamWriter(fileInfo.Create(),
>> Encoding.Default);
>> fileWriter.WriteLine(fileHeader.ToString());
>> fileWriter.Close();
>> fileWriter.Dispose();
>> // UP TO THIS POINT TAKES A FEW MILLISECONDS, OK
>> //
>> // 55 SECONDS OF CPU TIME (Pentium 4 3 GHz) TO WRITE 34 KILOBYTES
>> dataAdapter.Update(dataTable); // 55 SECONDS TO WRITE 34 KILOBYTES
>> // 55 SECONDS OF CPU TIME (Pentium 4 3 GHz) TO WRITE 34 KILOBYTES
>> //
>> // OK AFTER THIS
>> dataAdapter.Dispose();
>> dataTable.Dispose();
>> connection.Close();
>>
>>
>

Patrice

2/8/2008 12:29:00 PM

I would likely try to see if some tools from
http://technet.microsoft.com/fr-fr/sysinternals/de... such as
"filemon" would allow to track a bit what could happen behind the scene (I
suspect the driver could do a bit more than what you expect such as
rewriting the whole file on each insert ?)

Generally my personal preference is to handle these kind of files by myself
--
Patrice

"Norman Diamond" <ndiamond@newsgroup.nospam> a ecrit dans le message de
news: %23wr6XEkaIHA.5976@TK2MSFTNGP05.phx.gbl...
> The high CPU issue is when WRITING a CSV file via either the OleDb or Odbc
> driver.
>
> READING is no problem, for these particular CSV files.
>
> I already told you the number of records in the CSV file: 213, consisting
> of a header row and 212 data rows. Each row contains 25 columns, about
> half of which are text and half are null, and the nulls convert to ""
> because all of the column definitions are set to be text. The total file
> size is around 34,000 bytes. Don't you think that 55 seconds of CPU time
> to format 55,000 bytes is pretty slow? Your posting includes my message
> where I showed all this.
>
> I wrote C# code to write the strings to the file myself. It takes a few
> milliseconds now. I tested reading it back using the OleDb text driver,
> and it reads correctly in a few milliseconds.
>
>
> "Steven Cheng[MSFT]" <stcheng@online.microsoft.com> wrote in message
> news:0wyGz2jaIHA.360@TK2MSFTNGHUB02.phx.gbl...
>> Hi Norman,
>>
>> From your description, you're encountering high CPU issue when loading a
>> csv file via the OLEDB provider, correct?
>>
>> As for the high cpu behavior when loading such data file, I think the
>> following things maybe the potential cause:
>>
>> ** the number of records in the csv file
>> ** the data content that is contained in each record.
>>
>> As for number of record, I think you can try reduce the number of record
>> and columns and test again. As for the content, sometimes the provider
>> may
>> run into poor performance when some particular data(characters) in the
>> file
>> cause the provider spend much time parsing it. Therefore, you can also
>> try
>> test by isolating the data in the csv files(check whether there are some
>> records contains particular characters ).
>>
>> Sincerely,
>>
>> Steven Cheng
>>
>> Microsoft MSDN Online Support Lead
>>
>>
>>
>> ==================================================
>>
>> Get notification to my posts through email? Please refer to
>> http://msdn.microsoft.com/subscriptions/managednewsgroups/default....
>> ications.
>>
>>
>>
>> Note: The MSDN Managed Newsgroup support offering is for non-urgent
>> issues
>> where an initial response from the community or a Microsoft Support
>> Engineer within 1 business day is acceptable. Please note that each
>> follow
>> up response may take approximately 2 business days as the support
>> professional working with you may need further investigation to reach the
>> most efficient resolution. The offering is not appropriate for situations
>> that require urgent, real-time or phone-based interactions or complex
>> project analysis and dump analysis issues. Issues of this nature are best
>> handled working with a dedicated Microsoft Support Engineer by contacting
>> Microsoft Customer Support Services (CSS) at
>> http://msdn.microsoft.com/subscriptions/support/de....
>>
>> ==================================================
>>
>>
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.--------------------
>>>From: "Norman Diamond" <ndiamond@newsgroup.nospam>
>>>Subject: Incredibly slow writing by CSV driver
>>>Date: Fri, 8 Feb 2008 15:05:09 +0900
>>
>>>
>>>Since my application uses the Excel driver to read some files, I used the
>>>same Excel driver to write some CSV files. I also tried using the Text
>>>driver to write CSV files. The results are correct but they take
>>>enormous
>>>amounts of CPU time.
>>>
>>>Of course I know how to write a CSV file using plain old Shift-JIS
>> character
>>>strings and quotation marks and commas, and probably I'll do that in
>>>order
>>>to speed up this operation, but the question still remains.
>>>
>>>Why is this so slow? The sample data table had 213 rows (header plus 212
>>>data rows) and 25 columns, all strings (some of them empty strings). The
>>>total file size on disk is 34KB. The computations and database
>>>operations
>>>in memory take a few milliseconds, not even noticeable when running under
>>>a
>>>debugger. But the call to
>>> dataAdapter.Update(dataTable);
>>>takes 55 SECONDS OF CPU TIME on a Pentium 4 running at 3 GHz. It
>>>occupies
>>>100% of one CPU core for 55 seconds.
>>>
>>>Actual time to write the file might be a few hundred milliseconds since
>> 34KB
>>>occupies several NTFS structures. Anyway, this thing isn't disk bound,
>>>and
>>>it's not CPU bound in my code, it's CPU bound in the Update method.
>>>
>>>What is going on here?
>>>
>>>#undef UseOdbc // Use OleDb
>>> string fileName = @"C:\test.csv"; // (not really)
>>> string Headers[] = new string[25] { "1", "2", "3", /* ... */ "25" };
>>> int columnCount = 25; // (not really)
>>> FileInfo fileInfo = new System.IO.FileInfo(fileName);
>>> string dirName = fileInfo.DirectoryName;
>>> string tableName = fileInfo.Name;
>>>#if UseOdbc
>>> OdbcConnection connection = new OdbcConnection(
>>> "Provider=MSDASQL;" +
>>> "DRIVER={Microsoft Text Driver (*.txt; *.csv)};DBQ=" + dirName +
>>> ";Extended Properties='Text;Extensions=asc,csv,tab,txt;" +
>>> "HDR=Yes;FMT=Delimited'");
>>> connection.Open();
>>> OdbcDataAdapter dataAdapter = new OdbcDataAdapter(
>>> "SELECT * FROM [" + tableName + "]", connection);
>>> OdbcCommand insertCmd = new OdbcCommand();
>>> OdbcType varcharType = OdbcType.VarChar;
>>>#else // OleDb
>>> OleDbConnection connection = new OleDbConnection(
>>> "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dirName +
>>> ";Extended Properties='Text;Extensions=asc,csv,tab,txt;" +
>>> "HDR=Yes;FMT=Delimited'");
>>> connection.Open();
>>> OleDbDataAdapter dataAdapter = new OleDbDataAdapter(
>>> "SELECT * FROM [" + tableName + "]", connection);
>>> OleDbCommand insertCmd = new OleDbCommand();
>>> OleDbType varcharType = OleDbType.VarChar;
>>>#endif // OleDb
>>> DataTable dataTable = new DataTable(tableName);
>>> DataColumnCollection dataColumns = dataTable.Columns;
>>> int columnNum;
>>> string dataColumnName;
>>> DataColumn dataColumn;
>>> StringBuilder insertCmdHead = new StringBuilder(
>>> "INSERT INTO [" + tableName + "] (");
>>> StringBuilder insertCmdTail = new StringBuilder("VALUES (");
>>> StringBuilder fileHeader = new StringBuilder();
>>> for (columnNum = 0; columnNum < columnCount - 1; columnNum++)
>>> {
>>> dataColumnName = Headers[columnNum];
>>> dataColumnName = dataColumnName.Replace('\n', ' ');
>>> dataColumn = dataColumns.Add(dataColumnName);
>>> dataColumn.DataType = typeof(string);
>>> dataColumn.DefaultValue = "";
>>> insertCmdHead.Append("[" + dataColumnName + "], ");
>>> insertCmdTail.Append("?, ");
>>> insertCmd.Parameters.Add(dataColumnName, varcharType, 255,
>>> dataColumnName);
>>> fileHeader.Append("\"" + dataColumnName + "\",");
>>> }
>>> dataColumnName = Headers[columnCount - 1];
>>> dataColumn = dataColumns.Add(dataColumnName);
>>> dataColumn.DataType = typeof(string);
>>> dataColumn.DefaultValue = "";
>>> insertCmdHead.Append("[" + dataColumnName + "]) ");
>>> insertCmdTail.Append("?)");
>>> insertCmd.Parameters.Add(dataColumnName, varcharType, 255,
>>> dataColumnName);
>>> insertCmd.CommandText = insertCmdHead.ToString() +
>>> insertCmdTail.ToString();
>>> insertCmd.Connection = connection;
>>> dataAdapter.InsertCommand = insertCmd;
>>> fileHeader.Append("\"" + dataColumnName + "\"");
>>> for (int rowNum = 0; rowNum < 212; rowNum++) // (not really)
>>> {
>>> DataRow dataRow = dataTable.NewRow();
>>> dataRow[2] = "hi i'm 2"; // (not really)
>>> dataRow[18] = "18"; // (not really)
>>> dataRow[19] = "19"; // (not really)
>>> // (around half the cells default to empty strings)
>>> dataTable.Rows.Add(dataRow);
>>> }
>>> StreamWriter fileWriter = new StreamWriter(fileInfo.Create(),
>>> Encoding.Default);
>>> fileWriter.WriteLine(fileHeader.ToString());
>>> fileWriter.Close();
>>> fileWriter.Dispose();
>>> // UP TO THIS POINT TAKES A FEW MILLISECONDS, OK
>>> //
>>> // 55 SECONDS OF CPU TIME (Pentium 4 3 GHz) TO WRITE 34 KILOBYTES
>>> dataAdapter.Update(dataTable); // 55 SECONDS TO WRITE 34 KILOBYTES
>>> // 55 SECONDS OF CPU TIME (Pentium 4 3 GHz) TO WRITE 34 KILOBYTES
>>> //
>>> // OK AFTER THIS
>>> dataAdapter.Dispose();
>>> dataTable.Dispose();
>>> connection.Close();
>>>
>>>
>>
>

Norman Diamond

2/12/2008 12:43:00 AM

Of course filemon is a very useful tool, but how would it solve the problem
of 55 seconds of CPU time? The 34000 bytes of file contents get written
correctly to the file. The CPU is not waiting for the disk, the disk is
waiting for the CPU.

I worked around it by writing this file myself too. It takes a few
milliseconds now.

"Patrice" <http://www.chez.com/s... wrote in message
news:%23e3kr4kaIHA.4880@TK2MSFTNGP03.phx.gbl...
>I would likely try to see if some tools from
>http://technet.microsoft.com/fr-fr/sysinternals/de... such as
>"filemon" would allow to track a bit what could happen behind the scene (I
>suspect the driver could do a bit more than what you expect such as
>rewriting the whole file on each insert ?)
>
> Generally my personal preference is to handle these kind of files by
> myself
> --
> Patrice
>
> "Norman Diamond" <ndiamond@newsgroup.nospam> a ecrit dans le message de
> news: %23wr6XEkaIHA.5976@TK2MSFTNGP05.phx.gbl...
>> The high CPU issue is when WRITING a CSV file via either the OleDb or
>> Odbc driver.
>>
>> READING is no problem, for these particular CSV files.
>>
>> I already told you the number of records in the CSV file: 213,
>> consisting of a header row and 212 data rows. Each row contains 25
>> columns, about half of which are text and half are null, and the nulls
>> convert to "" because all of the column definitions are set to be text.
>> The total file size is around 34,000 bytes. Don't you think that 55
>> seconds of CPU time to format 55,000 bytes is pretty slow? Your posting
>> includes my message where I showed all this.
>>
>> I wrote C# code to write the strings to the file myself. It takes a few
>> milliseconds now. I tested reading it back using the OleDb text driver,
>> and it reads correctly in a few milliseconds.
>>
>>
>> "Steven Cheng[MSFT]" <stcheng@online.microsoft.com> wrote in message
>> news:0wyGz2jaIHA.360@TK2MSFTNGHUB02.phx.gbl...
>>> Hi Norman,
>>>
>>> From your description, you're encountering high CPU issue when loading a
>>> csv file via the OLEDB provider, correct?
>>>
>>> As for the high cpu behavior when loading such data file, I think the
>>> following things maybe the potential cause:
>>>
>>> ** the number of records in the csv file
>>> ** the data content that is contained in each record.
>>>
>>> As for number of record, I think you can try reduce the number of record
>>> and columns and test again. As for the content, sometimes the provider
>>> may
>>> run into poor performance when some particular data(characters) in the
>>> file
>>> cause the provider spend much time parsing it. Therefore, you can also
>>> try
>>> test by isolating the data in the csv files(check whether there are some
>>> records contains particular characters ).
>>>
>>> Sincerely,
>>>
>>> Steven Cheng
>>>
>>> Microsoft MSDN Online Support Lead
>>>
>>>
>>>
>>> ==================================================
>>>
>>> Get notification to my posts through email? Please refer to
>>> http://msdn.microsoft.com/subscriptions/managednewsgroups/default....
>>> ications.
>>>
>>>
>>>
>>> Note: The MSDN Managed Newsgroup support offering is for non-urgent
>>> issues
>>> where an initial response from the community or a Microsoft Support
>>> Engineer within 1 business day is acceptable. Please note that each
>>> follow
>>> up response may take approximately 2 business days as the support
>>> professional working with you may need further investigation to reach
>>> the
>>> most efficient resolution. The offering is not appropriate for
>>> situations
>>> that require urgent, real-time or phone-based interactions or complex
>>> project analysis and dump analysis issues. Issues of this nature are
>>> best
>>> handled working with a dedicated Microsoft Support Engineer by
>>> contacting
>>> Microsoft Customer Support Services (CSS) at
>>> http://msdn.microsoft.com/subscriptions/support/de....
>>>
>>> ==================================================
>>>
>>>
>>> This posting is provided "AS IS" with no warranties, and confers no
>>> rights.--------------------
>>>>From: "Norman Diamond" <ndiamond@newsgroup.nospam>
>>>>Subject: Incredibly slow writing by CSV driver
>>>>Date: Fri, 8 Feb 2008 15:05:09 +0900
>>>
>>>>
>>>>Since my application uses the Excel driver to read some files, I used
>>>>the
>>>>same Excel driver to write some CSV files. I also tried using the Text
>>>>driver to write CSV files. The results are correct but they take
>>>>enormous
>>>>amounts of CPU time.
>>>>
>>>>Of course I know how to write a CSV file using plain old Shift-JIS
>>> character
>>>>strings and quotation marks and commas, and probably I'll do that in
>>>>order
>>>>to speed up this operation, but the question still remains.
>>>>
>>>>Why is this so slow? The sample data table had 213 rows (header plus
>>>>212
>>>>data rows) and 25 columns, all strings (some of them empty strings).
>>>>The
>>>>total file size on disk is 34KB. The computations and database
>>>>operations
>>>>in memory take a few milliseconds, not even noticeable when running
>>>>under a
>>>>debugger. But the call to
>>>> dataAdapter.Update(dataTable);
>>>>takes 55 SECONDS OF CPU TIME on a Pentium 4 running at 3 GHz. It
>>>>occupies
>>>>100% of one CPU core for 55 seconds.
>>>>
>>>>Actual time to write the file might be a few hundred milliseconds since
>>> 34KB
>>>>occupies several NTFS structures. Anyway, this thing isn't disk bound,
>>>>and
>>>>it's not CPU bound in my code, it's CPU bound in the Update method.
>>>>
>>>>What is going on here?
>>>>
>>>>#undef UseOdbc // Use OleDb
>>>> string fileName = @"C:\test.csv"; // (not really)
>>>> string Headers[] = new string[25] { "1", "2", "3", /* ... */ "25" };
>>>> int columnCount = 25; // (not really)
>>>> FileInfo fileInfo = new System.IO.FileInfo(fileName);
>>>> string dirName = fileInfo.DirectoryName;
>>>> string tableName = fileInfo.Name;
>>>>#if UseOdbc
>>>> OdbcConnection connection = new OdbcConnection(
>>>> "Provider=MSDASQL;" +
>>>> "DRIVER={Microsoft Text Driver (*.txt; *.csv)};DBQ=" + dirName +
>>>> ";Extended Properties='Text;Extensions=asc,csv,tab,txt;" +
>>>> "HDR=Yes;FMT=Delimited'");
>>>> connection.Open();
>>>> OdbcDataAdapter dataAdapter = new OdbcDataAdapter(
>>>> "SELECT * FROM [" + tableName + "]", connection);
>>>> OdbcCommand insertCmd = new OdbcCommand();
>>>> OdbcType varcharType = OdbcType.VarChar;
>>>>#else // OleDb
>>>> OleDbConnection connection = new OleDbConnection(
>>>> "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dirName +
>>>> ";Extended Properties='Text;Extensions=asc,csv,tab,txt;" +
>>>> "HDR=Yes;FMT=Delimited'");
>>>> connection.Open();
>>>> OleDbDataAdapter dataAdapter = new OleDbDataAdapter(
>>>> "SELECT * FROM [" + tableName + "]", connection);
>>>> OleDbCommand insertCmd = new OleDbCommand();
>>>> OleDbType varcharType = OleDbType.VarChar;
>>>>#endif // OleDb
>>>> DataTable dataTable = new DataTable(tableName);
>>>> DataColumnCollection dataColumns = dataTable.Columns;
>>>> int columnNum;
>>>> string dataColumnName;
>>>> DataColumn dataColumn;
>>>> StringBuilder insertCmdHead = new StringBuilder(
>>>> "INSERT INTO [" + tableName + "] (");
>>>> StringBuilder insertCmdTail = new StringBuilder("VALUES (");
>>>> StringBuilder fileHeader = new StringBuilder();
>>>> for (columnNum = 0; columnNum < columnCount - 1; columnNum++)
>>>> {
>>>> dataColumnName = Headers[columnNum];
>>>> dataColumnName = dataColumnName.Replace('\n', ' ');
>>>> dataColumn = dataColumns.Add(dataColumnName);
>>>> dataColumn.DataType = typeof(string);
>>>> dataColumn.DefaultValue = "";
>>>> insertCmdHead.Append("[" + dataColumnName + "], ");
>>>> insertCmdTail.Append("?, ");
>>>> insertCmd.Parameters.Add(dataColumnName, varcharType, 255,
>>>> dataColumnName);
>>>> fileHeader.Append("\"" + dataColumnName + "\",");
>>>> }
>>>> dataColumnName = Headers[columnCount - 1];
>>>> dataColumn = dataColumns.Add(dataColumnName);
>>>> dataColumn.DataType = typeof(string);
>>>> dataColumn.DefaultValue = "";
>>>> insertCmdHead.Append("[" + dataColumnName + "]) ");
>>>> insertCmdTail.Append("?)");
>>>> insertCmd.Parameters.Add(dataColumnName, varcharType, 255,
>>>> dataColumnName);
>>>> insertCmd.CommandText = insertCmdHead.ToString() +
>>>> insertCmdTail.ToString();
>>>> insertCmd.Connection = connection;
>>>> dataAdapter.InsertCommand = insertCmd;
>>>> fileHeader.Append("\"" + dataColumnName + "\"");
>>>> for (int rowNum = 0; rowNum < 212; rowNum++) // (not really)
>>>> {
>>>> DataRow dataRow = dataTable.NewRow();
>>>> dataRow[2] = "hi i'm 2"; // (not really)
>>>> dataRow[18] = "18"; // (not really)
>>>> dataRow[19] = "19"; // (not really)
>>>> // (around half the cells default to empty strings)
>>>> dataTable.Rows.Add(dataRow);
>>>> }
>>>> StreamWriter fileWriter = new StreamWriter(fileInfo.Create(),
>>>> Encoding.Default);
>>>> fileWriter.WriteLine(fileHeader.ToString());
>>>> fileWriter.Close();
>>>> fileWriter.Dispose();
>>>> // UP TO THIS POINT TAKES A FEW MILLISECONDS, OK
>>>> //
>>>> // 55 SECONDS OF CPU TIME (Pentium 4 3 GHz) TO WRITE 34 KILOBYTES
>>>> dataAdapter.Update(dataTable); // 55 SECONDS TO WRITE 34 KILOBYTES
>>>> // 55 SECONDS OF CPU TIME (Pentium 4 3 GHz) TO WRITE 34 KILOBYTES
>>>> //
>>>> // OK AFTER THIS
>>>> dataAdapter.Dispose();
>>>> dataTable.Dispose();
>>>> connection.Close();
>>>>
>>>>
>>>
>>
>
>

Norman Diamond

2/12/2008 8:08:00 AM

Mr. Cheng, again, data are being WRITTEN not read. Reading is OK and only
takes a few milliseconds. WRITING takes an enormously long time.

I can't imagine what needs dumping. My first posting in this thread gave
sample code.

As mentioned, I worked around it the same way as Patrice, by writing my own
code in C# (or other language as might be used in any project) instead of
using the text driver, to WRITE CSV files.

"Steven Cheng[MSFT]" <stcheng@online.microsoft.com> wrote in message
news:SrU04YTbIHA.4720@TK2MSFTNGHUB02.phx.gbl...
> Hi Norman,
>
> For such high cpu issue, it is likely to be issue specific. I've checked
> some former issue and there is no definite problem of the provider and
> most
> of such issues are due to the data that is read or something related to
> the
> running environment. To troubleshoot on such issue and get the root cause,
> it may require much more work like dump analysis which is complex and time
> costing.
>
> If this problem is urgent and must be fixed in short time, I suggest you
> contact product support service for further assistance:
>
> http://msdn.microsoft.com/subscriptions/support/de....
>
> Sincerely,
>
> Steven Cheng
>
> Microsoft MSDN Online Support Lead
>
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> --------------------
>>From: "Norman Diamond" <ndiamond@newsgroup.nospam>
>>References: <ujmcTihaIHA.5128@TK2MSFTNGP05.phx.gbl>
> <0wyGz2jaIHA.360@TK2MSFTNGHUB02.phx.gbl>
> <#wr6XEkaIHA.5976@TK2MSFTNGP05.phx.gbl>
> <#e3kr4kaIHA.4880@TK2MSFTNGP03.phx.gbl>
>>Subject: Re: Incredibly slow writing by CSV driver
>>Date: Tue, 12 Feb 2008 09:42:48 +0900
>
>>
>>Of course filemon is a very useful tool, but how would it solve the
> problem
>>of 55 seconds of CPU time? The 34000 bytes of file contents get written
>>correctly to the file. The CPU is not waiting for the disk, the disk is
>>waiting for the CPU.
>>
>>I worked around it by writing this file myself too. It takes a few
>>milliseconds now.
>>
>>
>>"Patrice" <http://www.chez.com/s... wrote in message
>>news:%23e3kr4kaIHA.4880@TK2MSFTNGP03.phx.gbl...
>>>I would likely try to see if some tools from
>>>http://technet.microsoft.com/fr-fr/sysinternals/de... such as
>>>"filemon" would allow to track a bit what could happen behind the scene
> (I
>>>suspect the driver could do a bit more than what you expect such as
>>>rewriting the whole file on each insert ?)
>>>
>>> Generally my personal preference is to handle these kind of files by
>>> myself
>>> --
>>> Patrice
>>>
>>> "Norman Diamond" <ndiamond@newsgroup.nospam> a ecrit dans le message de
>>> news: %23wr6XEkaIHA.5976@TK2MSFTNGP05.phx.gbl...
>>>> The high CPU issue is when WRITING a CSV file via either the OleDb or
>>>> Odbc driver.
>>>>
>>>> READING is no problem, for these particular CSV files.
>>>>
>>>> I already told you the number of records in the CSV file: 213,
>>>> consisting of a header row and 212 data rows. Each row contains 25
>>>> columns, about half of which are text and half are null, and the nulls
>>>> convert to "" because all of the column definitions are set to be text.
>>>> The total file size is around 34,000 bytes. Don't you think that 55
>>>> seconds of CPU time to format 55,000 bytes is pretty slow? Your
> posting
>>>> includes my message where I showed all this.
>>>>
>>>> I wrote C# code to write the strings to the file myself. It takes a
> few
>>>> milliseconds now. I tested reading it back using the OleDb text
> driver,
>>>> and it reads correctly in a few milliseconds.
>>>>
>>>>
>>>> "Steven Cheng[MSFT]" <stcheng@online.microsoft.com> wrote in message
>>>> news:0wyGz2jaIHA.360@TK2MSFTNGHUB02.phx.gbl...
>>>>> Hi Norman,
>>>>>
>>>>> From your description, you're encountering high CPU issue when loading
> a
>>>>> csv file via the OLEDB provider, correct?
>>>>>
>>>>> As for the high cpu behavior when loading such data file, I think the
>>>>> following things maybe the potential cause:
>>>>>
>>>>> ** the number of records in the csv file
>>>>> ** the data content that is contained in each record.
>>>>>
>>>>> As for number of record, I think you can try reduce the number of
> record
>>>>> and columns and test again. As for the content, sometimes the provider
>>>>> may
>>>>> run into poor performance when some particular data(characters) in the
>>>>> file
>>>>> cause the provider spend much time parsing it. Therefore, you can also
>>>>> try
>>>>> test by isolating the data in the csv files(check whether there are
> some
>>>>> records contains particular characters ).
>>>>>
>>>>> Sincerely,
>>>>>
>>>>> Steven Cheng
>>>>>
>>>>> Microsoft MSDN Online Support Lead
>>>>>
>>>>>
>>>>>
>>>>> ==================================================
>>>>>
>>>>> Get notification to my posts through email? Please refer to
>>>>>
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default....
>>>>> ications.
>>>>>
>>>>>
>>>>>
>>>>> Note: The MSDN Managed Newsgroup support offering is for non-urgent
>>>>> issues
>>>>> where an initial response from the community or a Microsoft Support
>>>>> Engineer within 1 business day is acceptable. Please note that each
>>>>> follow
>>>>> up response may take approximately 2 business days as the support
>>>>> professional working with you may need further investigation to reach
>>>>> the
>>>>> most efficient resolution. The offering is not appropriate for
>>>>> situations
>>>>> that require urgent, real-time or phone-based interactions or complex
>>>>> project analysis and dump analysis issues. Issues of this nature are
>>>>> best
>>>>> handled working with a dedicated Microsoft Support Engineer by
>>>>> contacting
>>>>> Microsoft Customer Support Services (CSS) at
>>>>> http://msdn.microsoft.com/subscriptions/support/de....
>>>>>
>>>>> ==================================================
>>>>>
>>>>>
>>>>> This posting is provided "AS IS" with no warranties, and confers no
>>>>> rights.--------------------
>>>>>>From: "Norman Diamond" <ndiamond@newsgroup.nospam>
>>>>>>Subject: Incredibly slow writing by CSV driver
>>>>>>Date: Fri, 8 Feb 2008 15:05:09 +0900
>>>>>
>>>>>>
>>>>>>Since my application uses the Excel driver to read some files, I used
>>>>>>the
>>>>>>same Excel driver to write some CSV files. I also tried using the
>>>>>>Text
>>>>>>driver to write CSV files. The results are correct but they take
>>>>>>enormous
>>>>>>amounts of CPU time.
>>>>>>
>>>>>>Of course I know how to write a CSV file using plain old Shift-JIS
>>>>> character
>>>>>>strings and quotation marks and commas, and probably I'll do that in
>>>>>>order
>>>>>>to speed up this operation, but the question still remains.
>>>>>>
>>>>>>Why is this so slow? The sample data table had 213 rows (header plus
>>>>>>212
>>>>>>data rows) and 25 columns, all strings (some of them empty strings).
>>>>>>The
>>>>>>total file size on disk is 34KB. The computations and database
>>>>>>operations
>>>>>>in memory take a few milliseconds, not even noticeable when running
>>>>>>under a
>>>>>>debugger. But the call to
>>>>>> dataAdapter.Update(dataTable);
>>>>>>takes 55 SECONDS OF CPU TIME on a Pentium 4 running at 3 GHz. It
>>>>>>occupies
>>>>>>100% of one CPU core for 55 seconds.
>>>>>>
>>>>>>Actual time to write the file might be a few hundred milliseconds
>>>>>>since
>>>>> 34KB
>>>>>>occupies several NTFS structures. Anyway, this thing isn't disk
> bound,
>>>>>>and
>>>>>>it's not CPU bound in my code, it's CPU bound in the Update method.
>>>>>>
>>>>>>What is going on here?
>>>>>>
>>>>>>#undef UseOdbc // Use OleDb
>>>>>> string fileName = @"C:\test.csv"; // (not really)
>>>>>> string Headers[] = new string[25] { "1", "2", "3", /* ... */ "25"
> };
>>>>>> int columnCount = 25; // (not really)
>>>>>> FileInfo fileInfo = new System.IO.FileInfo(fileName);
>>>>>> string dirName = fileInfo.DirectoryName;
>>>>>> string tableName = fileInfo.Name;
>>>>>>#if UseOdbc
>>>>>> OdbcConnection connection = new OdbcConnection(
>>>>>> "Provider=MSDASQL;" +
>>>>>> "DRIVER={Microsoft Text Driver (*.txt; *.csv)};DBQ=" + dirName
> +
>>>>>> ";Extended Properties='Text;Extensions=asc,csv,tab,txt;" +
>>>>>> "HDR=Yes;FMT=Delimited'");
>>>>>> connection.Open();
>>>>>> OdbcDataAdapter dataAdapter = new OdbcDataAdapter(
>>>>>> "SELECT * FROM [" + tableName + "]", connection);
>>>>>> OdbcCommand insertCmd = new OdbcCommand();
>>>>>> OdbcType varcharType = OdbcType.VarChar;
>>>>>>#else // OleDb
>>>>>> OleDbConnection connection = new OleDbConnection(
>>>>>> "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dirName +
>>>>>> ";Extended Properties='Text;Extensions=asc,csv,tab,txt;" +
>>>>>> "HDR=Yes;FMT=Delimited'");
>>>>>> connection.Open();
>>>>>> OleDbDataAdapter dataAdapter = new OleDbDataAdapter(
>>>>>> "SELECT * FROM [" + tableName + "]", connection);
>>>>>> OleDbCommand insertCmd = new OleDbCommand();
>>>>>> OleDbType varcharType = OleDbType.VarChar;
>>>>>>#endif // OleDb
>>>>>> DataTable dataTable = new DataTable(tableName);
>>>>>> DataColumnCollection dataColumns = dataTable.Columns;
>>>>>> int columnNum;
>>>>>> string dataColumnName;
>>>>>> DataColumn dataColumn;
>>>>>> StringBuilder insertCmdHead = new StringBuilder(
>>>>>> "INSERT INTO [" + tableName + "] (");
>>>>>> StringBuilder insertCmdTail = new StringBuilder("VALUES (");
>>>>>> StringBuilder fileHeader = new StringBuilder();
>>>>>> for (columnNum = 0; columnNum < columnCount - 1; columnNum++)
>>>>>> {
>>>>>> dataColumnName = Headers[columnNum];
>>>>>> dataColumnName = dataColumnName.Replace('\n', ' ');
>>>>>> dataColumn = dataColumns.Add(dataColumnName);
>>>>>> dataColumn.DataType = typeof(string);
>>>>>> dataColumn.DefaultValue = "";
>>>>>> insertCmdHead.Append("[" + dataColumnName + "], ");
>>>>>> insertCmdTail.Append("?, ");
>>>>>> insertCmd.Parameters.Add(dataColumnName, varcharType, 255,
>>>>>> dataColumnName);
>>>>>> fileHeader.Append("\"" + dataColumnName + "\",");
>>>>>> }
>>>>>> dataColumnName = Headers[columnCount - 1];
>>>>>> dataColumn = dataColumns.Add(dataColumnName);
>>>>>> dataColumn.DataType = typeof(string);
>>>>>> dataColumn.DefaultValue = "";
>>>>>> insertCmdHead.Append("[" + dataColumnName + "]) ");
>>>>>> insertCmdTail.Append("?)");
>>>>>> insertCmd.Parameters.Add(dataColumnName, varcharType, 255,
>>>>>> dataColumnName);
>>>>>> insertCmd.CommandText = insertCmdHead.ToString() +
>>>>>> insertCmdTail.ToString();
>>>>>> insertCmd.Connection = connection;
>>>>>> dataAdapter.InsertCommand = insertCmd;
>>>>>> fileHeader.Append("\"" + dataColumnName + "\"");
>>>>>> for (int rowNum = 0; rowNum < 212; rowNum++) // (not really)
>>>>>> {
>>>>>> DataRow dataRow = dataTable.NewRow();
>>>>>> dataRow[2] = "hi i'm 2"; // (not really)
>>>>>> dataRow[18] = "18"; // (not really)
>>>>>> dataRow[19] = "19"; // (not really)
>>>>>> // (around half the cells default to empty strings)
>>>>>> dataTable.Rows.Add(dataRow);
>>>>>> }
>>>>>> StreamWriter fileWriter = new StreamWriter(fileInfo.Create(),
>>>>>> Encoding.Default);
>>>>>> fileWriter.WriteLine(fileHeader.ToString());
>>>>>> fileWriter.Close();
>>>>>> fileWriter.Dispose();
>>>>>> // UP TO THIS POINT TAKES A FEW MILLISECONDS, OK
>>>>>> //
>>>>>> // 55 SECONDS OF CPU TIME (Pentium 4 3 GHz) TO WRITE 34 KILOBYTES
>>>>>> dataAdapter.Update(dataTable); // 55 SECONDS TO WRITE 34
>>>>>> KILOBYTES
>>>>>> // 55 SECONDS OF CPU TIME (Pentium 4 3 GHz) TO WRITE 34 KILOBYTES
>>>>>> //
>>>>>> // OK AFTER THIS
>>>>>> dataAdapter.Dispose();
>>>>>> dataTable.Dispose();
>>>>>> connection.Close();
>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>>>
>>
>>
>

microsoft.public.inetserver.asp.components

白木屋心理状態精進くしゃみ薬用

yomur

Norman Diamond

Patrice

Norman Diamond

Norman Diamond

x Login to ForumsZone