none
Again .Net Vs Java RRS feed

  • Question

  • I have large DataSet like 150,000 rows and 450 Columns. I get data using sql server stored procedure and I am writing data layer to insert/update data in ADO.NET 2.0

    The other stored procedure which I call and pass one row at a time and insert.

    There is current data layer written in Java which I don't have access to code but run faster then "my" .Net code. All I can see from SQL Profiler that Java sends 10 exec call to stored procedure at one time.

    I tried 5 different ways to call insert stored procedure but still can not match java timing. Need your help what I am doing wrong.

    a) I used System.Data.SqlClient called first get SP and fill my DataTable and then loop DataTable and called command.ExecuteNonQuery  [50% slow]

    b) I used same with again SqlClinet but used command.Prepare(); for each row   [50% slow]

    c) I used System.Data.OleDb and command.Prepare(); for each row   [50% slow]

    d) I used SqlClient with SqlDataAdapter and BatchUpdate with batch size 10 [50% slow]

    e) I tried to code same way as Java creating sql block with 10 exec call to stored procedure and run at a time.   [ 30% slow]

     

    Both testing I am doing in same box, same stored procedures, same tables, same database.

     

    Please don't give other ideas like bulk insert or some thing else. In order to replace Jave code I need to do the same in .Net

     

    Any Idea?

    - Ashok

     

    Friday, July 13, 2007 6:46 PM

Answers

  • As I said before "my" code. Guys it works faster then Java now. I had to change my code

    - Not use DataTable use SqlDataReader

    - Not use String use StringBuilder

     

    I know it's basics - Ashok

    Monday, July 16, 2007 7:23 PM

All replies

  • I am not sure what you mean "Please don't give other ideas like bulk insert or some thing else."

    Do you mean that BCP is not an option?  that is fine, but the solution will still lie in using bulk/array inserting.

     

    Did you try the ADO.Net 2.0 SqlBulkCopy?

     

     http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx

        

     

    Friday, July 13, 2007 7:34 PM
  • I mean to say current Java code uses two stored procedures (SP). Get SP to get data and Set SP to insert/update. The current model uses 2 SP calls. In order to replace Java I have to build same model to keep 2 SP calls and row by row insert update. It allows better control. If one row fails it can be logged but mainly in insert/update SP can have some logic before insert update.
    So you think we can have same model where 2 SP can be called and if insert/update SP fails any single row we can log that and continue processing all? What .Net code we can do? Can we use SqlBulkCopy to achieve this?
    Friday, July 13, 2007 8:01 PM
  • As I said before "my" code. Guys it works faster then Java now. I had to change my code

    - Not use DataTable use SqlDataReader

    - Not use String use StringBuilder

     

    I know it's basics - Ashok

    Monday, July 16, 2007 7:23 PM