White Posted November 7, 2004 Posted November 7, 2004 Hi everyone. Guys, I have an VB .Net application that deal with somthing like 60k record. What the application does? It imports data from an excel spreadsheet, process this information, and write this information to a SQL 2000 Database (currently I am using MSDE for developing the application). What is happening? Whell... At the begining it processes 1000 records each 20 seconds. As it processes more and more records starts going slower and slower. When it is near 20k records, each 1000 records take more than one minute. And I spend more than half an our to precess these 60K records. I tryed using Data.OleDB and Data.SqlClient, both is showing the same problem. Does any one know how to deal with this?? This source will be used for a WebApplication. The User will not use this funtion so ofen, but he can't wait that much for the data to be imported. Thanks Regards Quote
Administrators PlausiblyDamp Posted November 7, 2004 Administrators Posted November 7, 2004 Could you post the code you are using to retrieve the data? Quote Posting Guidelines FAQ Post Formatting Intellectuals solve problems; geniuses prevent them. -- Albert Einstein
Joe Mamma Posted November 7, 2004 Posted November 7, 2004 hown many insert statements per row? how many tables are hit per row? I bet my paycheck its a schema problem. . . smells like index overhead. dump to text file, sp a bcp op to load the data. Quote Joe Mamma Amendment 4: The right of the people to be secure in their persons, houses, papers, and effects, against unreasonable searches and seizures, shall not be violated, and no warrants shall issue, but upon probable cause, supported by oath or affirmation, and particularly describing the place to be searched, and the persons or things to be seized. Amendment 9: The enumeration in the Constitution, of certain rights, shall not be construed to deny or disparage others retained by the people.
White Posted November 7, 2004 Author Posted November 7, 2004 Here Goes the code: This is the code I use to send the Data to the SQL Database. ( PlausiblyDamp, if you still need the code for importing the data from excel, just let me know). Before this, I have the function that Imports the data from the excel spreadsheet. That function is fine. It takes about 12s to import the data to a Private Global variable called dsDataLogger (as DataSet), that is used in the code bellow. Just as information I start this function as a thread. So I can keep the program showing for the user what is happening. Private Sub Exporta_Datalogger_paraSQL() Dim oCon As New SqlConnection(cnsAPPS_Kodak) Dim sSql As String Dim oCmd As New SqlCommand Dim intContador As Integer Dim intContagemOK As Integer = 0 Dim intID_Log As Integer Dim intContadorTransacao As Integer ApagaDatalogger() For intContador = 0 To dsDatalogger.Tables(0).Rows.Count - 1 With dsDatalogger.Tables(0).Rows(intContador) If Not System.Convert.IsDBNull(.Item(0)) And Not System.Convert.IsDBNull(.Item(1)) And Not System.Convert.IsDBNull(.Item(2)) Then If Not .IsNull(0) And Not .IsNull(1) And Not .IsNull(2) And .Item(0) <> "login" And .Item(1) <> "data_sistema" And .Item(2) <> "data_informada" Then 'Gets the next index in the Table intID_Log = ProcuraNovoIndice_Datalogger() sSql = "Insert into Datalogger values ('" & intID_Log & "', '" & .Item(0) & "', '" & .Item(1) & "', '" & .Item(2) & "', '" & .Item(3) & "', '" & .Item(4) & "', '" & .Item(5) & "', '" & .Item(6) & "', '" & .Item(7) & "', '" & .Item(8) & "', '" & .Item(9) & "', '" & .Item(10) & "', '" & .Item(11) & "', '" & .Item(12) & "', '" & .Item(13) & "', '" & .Item(14) & "', '" & .Item(15) & "', '" & .Item(16) & "', '" & .Item(17) & "', '" & .Item(18) & "', '" & .Item(19) & "', '" & .Item(20) & "', '" & .Item(21) & "')" oCmd = New SqlCommand(sSql, oCon) Try If oCon.State <> ConnectionState.Open Then oCon.Open() End If oCmd.ExecuteNonQuery() Catch ex As Exception MsgBox(ex.Message & ex.HelpLink) End Try End If End If End With Next If oCon.State <> ConnectionState.Closed Then oCon.Close() End Sub ----------- End of The Code ------------ There is about 23 intens each insert. One insert per row. Just one table per how is hit. And this table has no indexes. It has a Primary Key "ID_Log". Joe Mamma, what do you mean by "dump to text file, sp a bcp op to load the data". That is my first big project with SQL, so it might be why I did not undestand what you mean. Thanks!! Regards Quote
Joe Mamma Posted November 7, 2004 Posted November 7, 2004 problem 1 - intID_Log = ProcuraNovoIndice_Datalogger() what does this do? look for the next ID number, via a "select max(ID_LOG)" redefine the log table so ID is an int Identity. Something like this CREATE TABLE [dbo].[Datalogger] ( [iD_LOG] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED , [item1] [varchar] (255), [item2] [varchar] (255), [item3] [varchar] (255), [item4] [varchar] (255), [item5] [varchar] (255), [item6] [varchar] (255), [item7] [varchar] (255), [item8] [varchar] (255), [item9] [varchar] (255), [item10] [varchar] (255), [item11] [varchar] (255), [item12] [varchar] (255), [item13] [varchar] (255), [item14] [varchar] (255), [item15] [varchar] (255), [item16] [varchar] (255), [item17] [varchar] (255), [item18] [varchar] (255), [item19] [varchar] (255), [item20] [varchar] (255), [item21] [varchar] (255) ) problem 2- Use parameters!!! never build SQL strings!!!! each time the sql changes the command is recompiled!! [color=black]Private Sub Exporta_Datalogger_paraSQL() Dim sSql As String Dim startTime As DateTime = DateTime.Now[/color] [color=black] oCon.ConnectionString = cnsAPPS_Kodak sSql = "INSERT INTO Datalogger " _ & " (item1, item2, item3, item4, item5, item6, item7, " _ & " item8, item9, item10, item11, item12, item13, item14, " _ & " item15, item16, item17, item18, item19, item20, item21) " _ & " VALUES " _ & " (@item1, @item2, @item3, @item4, @item5, @item6, @item7, " _ & " @item8, @item9, @item10, @item11, @item12, @item13, @item14, " _ & " @item15, @item16, @item17, @item18, @item19, @item20, @item21)"[/color] [color=black] Dim ocmd As New SqlClient.SqlCommand(sSql, oCon)[/color] [color=black] ocmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@item1", SqlDbType.NVarChar, 255)) ocmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@item2", SqlDbType.NVarChar, 255)) ocmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@item3", SqlDbType.NVarChar, 255)) ocmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@item4", SqlDbType.NVarChar, 255)) ocmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@item5", SqlDbType.NVarChar, 255)) ocmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@item6", SqlDbType.NVarChar, 255)) ocmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@item7", SqlDbType.NVarChar, 255)) ocmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@item8", SqlDbType.NVarChar, 255)) ocmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@item9", SqlDbType.NVarChar, 255)) ocmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@item10", SqlDbType.NVarChar, 255)) ocmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@item11", SqlDbType.NVarChar, 255)) ocmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@item12", SqlDbType.NVarChar, 255)) ocmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@item13", SqlDbType.NVarChar, 255)) ocmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@item14", SqlDbType.NVarChar, 255)) ocmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@item15", SqlDbType.NVarChar, 255)) ocmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@item16", SqlDbType.NVarChar, 255)) ocmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@item17", SqlDbType.NVarChar, 255)) ocmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@item18", SqlDbType.NVarChar, 255)) ocmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@item19", SqlDbType.NVarChar, 255)) ocmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@item20", SqlDbType.NVarChar, 255)) ocmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@item21", SqlDbType.NVarChar, 255))[/color] [color=black] Dim r As DataRow Dim i As Integer[/color] [color=black] oCon.Open() ocmd.Prepare() For Each r In dsDatalogger.Tables(0).Rows For i = 0 To r.Item.Count - 1[/color] [color=black] ocmd.Parameters(String.Format("@item{0}", i + 1)).Value = r.Item(i).ToString Next ocmd.ExecuteNonQuery() Next oCon.Close() MessageBox.Show(String.Format("Done! Elapsed Time: {0}", DateTime.Now.Subtract(startTime).ToString))[/color] [color=black]End Sub [/color] consistantly 40 seconds on a 2.6 HT Win 2003 server 768MB Quote Joe Mamma Amendment 4: The right of the people to be secure in their persons, houses, papers, and effects, against unreasonable searches and seizures, shall not be violated, and no warrants shall issue, but upon probable cause, supported by oath or affirmation, and particularly describing the place to be searched, and the persons or things to be seized. Amendment 9: The enumeration in the Constitution, of certain rights, shall not be construed to deny or disparage others retained by the people.
Joe Mamma Posted November 7, 2004 Posted November 7, 2004 Oh yeah. . . "sp a bcp op to load the data" reads "create a stored procedure running a bulk copy process to load the data" you could also interop the bcp libraries and so it in your code. Quote Joe Mamma Amendment 4: The right of the people to be secure in their persons, houses, papers, and effects, against unreasonable searches and seizures, shall not be violated, and no warrants shall issue, but upon probable cause, supported by oath or affirmation, and particularly describing the place to be searched, and the persons or things to be seized. Amendment 9: The enumeration in the Constitution, of certain rights, shall not be construed to deny or disparage others retained by the people.
Joe Mamma Posted November 7, 2004 Posted November 7, 2004 oh yeah, that was 60000 records Quote Joe Mamma Amendment 4: The right of the people to be secure in their persons, houses, papers, and effects, against unreasonable searches and seizures, shall not be violated, and no warrants shall issue, but upon probable cause, supported by oath or affirmation, and particularly describing the place to be searched, and the persons or things to be seized. Amendment 9: The enumeration in the Constitution, of certain rights, shall not be construed to deny or disparage others retained by the people.
White Posted November 9, 2004 Author Posted November 9, 2004 Thank you guys so much for you help!! Using Joe's code I was able to set up a code for my especific case. Here goes the WORKING code (thanks again Joe). There are some comented lines('), I disabled that, because they are not necessary for working...just for debugging. Private Sub Exporta_Datalogger6_paraSQL() Dim oCon As New SqlConnection(cnsAPPS_Kodak) Dim sSql As String 'Dim startTime As DateTime = DateTime.Now oCon.ConnectionString = cnsAPPS_Kodak ApagaDatalogger() sSql = "INSERT INTO Datalogger " _ & " VALUES " _ & " (@item1, @item2, @item3, @item4, @item5, @item6, @item7, " _ & " @item8, @item9, @item10, @item11, @item12, @item13, @item14, " _ & " @item15, @item16, @item17, @item18, @item19, @item20, @item21, @item22)" Dim ocmd As New SqlClient.SqlCommand(sSql, oCon) ocmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@item1", SqlDbType.NVarChar, 10)) ocmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@item2", SqlDbType.NVarChar, 20)) ocmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@item3", SqlDbType.NVarChar, 20)) ocmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@item4", SqlDbType.NVarChar, 255)) ocmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@item5", SqlDbType.NVarChar, 40)) ocmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@item6", SqlDbType.NVarChar, 40)) ocmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@item7", SqlDbType.NVarChar, 255)) ocmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@item8", SqlDbType.NVarChar, 40)) ocmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@item9", SqlDbType.NVarChar, 40)) ocmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@item10", SqlDbType.NVarChar, 20)) ocmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@item11", SqlDbType.NVarChar, 255)) ocmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@item12", SqlDbType.NVarChar, 255)) ocmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@item13", SqlDbType.NVarChar, 60)) ocmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@item14", SqlDbType.NVarChar, 255)) ocmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@item15", SqlDbType.NVarChar, 40)) ocmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@item16", SqlDbType.NVarChar, 40)) ocmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@item17", SqlDbType.NVarChar, 100)) ocmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@item18", SqlDbType.NVarChar, 40)) ocmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@item19", SqlDbType.NVarChar, 40)) ocmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@item20", SqlDbType.NVarChar, 40)) ocmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@item21", SqlDbType.NVarChar, 255)) ocmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@item22", SqlDbType.NVarChar, 40)) Dim r As DataRow Dim i As Integer oCon.Open() ocmd.Prepare() 'Dim intRegCont As Integer 'Dim intRegAtual As Integer = 0 'intRegCont = dsDatalogger.Tables(0).Rows.Count For Each r In dsDatalogger.Tables(0).Rows If Not System.Convert.IsDBNull(r.Item(0)) And Not System.Convert.IsDBNull(r.Item(1)) And Not System.Convert.IsDBNull(r.Item(2)) Then If Not r.IsNull(0) And Not r.IsNull(1) And Not r.IsNull(2) And r.Item(0) <> "login" And r.Item(1) <> "data_sistema" And r.Item(2) <> "data_informada" Then For i = 0 To 22 - 1 ocmd.Parameters(String.Format("@item{0}", i + 1)).Value = r.Item(i).ToString Next ocmd.ExecuteNonQuery() End If End If 'AtualizaProgressBar(intRegCont, intRegAtual) 'intRegAtual = intRegAtual + 1 Next oCon.Close() 'MsgBox(String.Format("Done! Elapsed Time: {0}", DateTime.Now.Subtract(startTime).ToString)) End Sub -------------- END OF CODE -------------- Thank you so much guys! Regards Take Care PS: It is taking 48 seconds in a XP2100 512MB running Windows XP HE. Thanks Again to Joe :) :D Quote
Joe Mamma Posted November 9, 2004 Posted November 9, 2004 PS: It is taking 48 seconds in a XP2100 512MB running Windows XP HE. Thanks Again to Joe :) :Dyou are quite welcome! brazil rocks!!! Great clubs! Beautiful women! Fantastic acid!!! I have fond memories of Sao Paulo! Ahh that was a great! I need to go back! Quote Joe Mamma Amendment 4: The right of the people to be secure in their persons, houses, papers, and effects, against unreasonable searches and seizures, shall not be violated, and no warrants shall issue, but upon probable cause, supported by oath or affirmation, and particularly describing the place to be searched, and the persons or things to be seized. Amendment 9: The enumeration in the Constitution, of certain rights, shall not be construed to deny or disparage others retained by the people.
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.