Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you an example analysis of the introduction of IIS logs into SQLSERVER. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

URCHIN has been used to analyze logs, this google log analysis tool is not functional or efficient.

However, there are still some special analysis requirements that can not be completed. So I decided to import the log into SQLSERVER for analysis.

It's easier to imagine at first. Nesting a loop can basically be done.

A big loop reads all the log files under a folder

The small loop inside reads the log line by line, opens the split inside, and inserts sql.

Tableadapter is established according to the data table, and data is inserted one by one using insert stored procedures.

The first problem is that it takes about 3 hours to execute a log file of about 200W lines!

The efficiency was so low that I began to consider opening multithreading, but later found that the execution efficiency should have nothing to do with the thread, mainly in the insert operation of tableadapter.

This insertion method actually performs a connect-insert-disconnect operation each time.

The solution is to use memory and do less IO operations.

Set up a datatable first. Initialize this datatable with a strongly typed object in tableadapter.

Then read the data into datatable.

Finally, the database is populated with sqkbulkcopy in the. net object.

In this way, the problem of data filling speed is solved. Almost 200W of data can be imported in 5 minutes.

But in actual debugging, the second problem comes. (never figured it out.)

The server I use is 2003 64-bit system memory of 16GB.

A data file is about 500m. Every time you fill datatable, a file will report an error of outofmemory before it is finished, and the memory will overflow!

Oddly enough, a 64-bit system should be able to manage a lot of memory, and there is no problem with 32-bit AWE.

So continue to try to create a small loop within the loop, importing 100W pieces of datatable data at a time.

The first batch of 100W was successfully imported, but it was the same error when cycling to the second 100W.

Look at the code. After each import, I use table.dispose () to clean up and observe the resource manager, and the memory is not released.

So use table.rows.clear ()

Or use table.clear ()?

+ GC.collect ()

This can basically solve the problem that memory can not be freed, but in actual use, it is found that memory has been rising, because sqlserver will also take up a lot of memory during sqlbulkcopy.

There should be no problem with a small number of log imports, but I don't know what happens when continuous imports are made.

Main code

Private Sub readLogfile (ByVal log As FileInfo)

Dim reader As StreamReader = New StreamReader (log.FullName)

While Not reader.EndOfStream

For j As Int32 = 0 To 1000000

If Not reader.EndOfStream Then

HandleLine (reader.ReadLine ())

I + = 1

Console.WriteLine (j)

Else

Exit While

End If

Next

Bulkcopy (table1)

'table1.Rows.Clear ()

Table1.Clear ()

GC.Collect ()

Console.WriteLine ("-" & I)

End While

Private Sub bulkcopy (ByVal newtable As DataTable)

Using sqlbulk As SqlBulkCopy = New SqlBulkCopy (Configuration.ConfigurationManager.ConnectionStrings ("logAnalysis.My.MySettings.logDBConnectionString") .ConnectionString)

Sqlbulk.DestinationTableName = "logDB. Dbo.Table_1"

Sqlbulk.BulkCopyTimeout = 108000

Sqlbulk.WriteToServer (newtable)

Sqlbulk.Close ()

End Using

End Sub

The above is the example analysis of the introduction of IIS logs into SQLSERVER for everyone. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.