Clever Geek Handbook

Why is starting the 2nd reader with the first read () reader faster than starting it to read its own readers?

So, I am currently running this code to move a bunch of data from ubiquitous database to access database

public List<HBPData> LoadData()
    {

        loadConnect(); //<-- get the Pervasive/Access string from a text file
        List<HBPData> listofhbpdata1 = new List<HBPData>();
        List<HBPData> listofhbpdata2 = new List<HBPData>();

            PsqlConnection myConnection = new PsqlConnection();
            myConnection.ConnectionString = PervasiveString;
            myConnection.Open();
            PsqlCommand myCommand = new PsqlCommand("Select NUMBER, CUST_NAME, PO_NO, REQD_DATE, PO_NO, CUST_PO_NO, ORD_DATE, STATUS FROM SALES_ORDER_HEADER WHERE ORD_DATE > 20120220 Order By ORD_DATE desc", myConnection);
            PsqlDataReader myreader = null;
            myreader = myCommand.ExecuteReader();

            while (myreader.Read())
            {
                HBPData DataEntity = new HBPData();
                DataEntity.NUMBER = (myreader["NUMBER"].ToString());
                DataEntity.CUST_NO = (myreader["CUST_NAME"].ToString()).Replace("'","");
                DataEntity.PO_NO = (myreader["PO_NO"].ToString());
                DataEntity.RequiredDateTime = (myreader["REQD_DATE"].ToString());
                DataEntity.Tag = (myreader["PO_NO"].ToString());
                DataEntity.Shape = (myreader["CUST_PO_NO"].ToString());
                DataEntity.ExpectedCompletion = myreader["ORD_DATE"].ToString().Substring(0, 4) + "/" + myreader["ORD_DATE"].ToString().Substring(4, 2) + "/" + myreader["ORD_DATE"].ToString().Substring(6, 2);
                DataEntity.MostRecentStatus = (myreader["STATUS"].ToString());
                listofhbpdata1.Add(DataEntity);
            }

            PsqlCommand myCommand1 = new PsqlCommand("Select NUMBER, RECNO, CODE, ORDD_DESCRIPTION, BVORDQTY FROM SALES_ORDER_DETAIL WHERE BVRVADDDATE > 20120220 AND (PROD_CODE = \'MET\' OR PROD_CODE = \'MDT\') Order By NUMBER desc", myConnection);
            PsqlDataReader myreader1 = null;
            myreader1 = myCommand1.ExecuteReader();

            while (myreader.Read()) 
            {
                HBPData DataEntity = new HBPData();
                DataEntity.NUMBER = (myreader1["NUMBER"].ToString());
                DataEntity.RECNO = (myreader1["RECNO"].ToString());
                DataEntity.CODE = (myreader1["CODE"].ToString());
                DataEntity.DESCRIPTION = (myreader1["ORDD_DESCRIPTION"].ToString());
                DataEntity.Quantity = (myreader1["BVORDQTY"].ToString());
                listofhbpdata2.Add(DataEntity);
            }

            myConnection.Close();
            myreader1.Close();
            myreader.Close();






            System.Data.OleDb.OleDbConnection myAccessConnection = new System.Data.OleDb.OleDbConnection();

            myAccessConnection.ConnectionString = AccessString;
            myAccessConnection.Open();
            System.Data.OleDb.OleDbCommand myAccessCommand3 = new System.Data.OleDb.OleDbCommand("delete from AllOrders", myAccessConnection);
            myAccessCommand3.ExecuteNonQuery();

            for (int i = 0; i < listofhbpdata2.Count(); ++i)
            {
                System.Data.OleDb.OleDbCommand myAccessCommand2 = new System.Data.OleDb.OleDbCommand("" +
                    "Insert into AllOrders VALUES('" +
                      listofhbpdata2[i].NUMBER + "'" + ",'" + listofhbpdata2[i].RECNO.ToString() + "'" +
                    ",'" + listofhbpdata2[i].CODE + "','" + listofhbpdata2[i].DESCRIPTION.Replace("\'", "F") + "'" +
                    ",'" + listofhbpdata2[i].Quantity + "')", myAccessConnection);
                myAccessCommand2.ExecuteNonQuery();
            }

            myAccessConnection.Close();

        return listofhbpdata1;
    }

Now,. If you look closely, I'll seal the second reader, it should read while (myreader1.read ()) ... I accidentally put myreader.read ()

Put myreader.read (), much to my surprise, it actually worked successfully ... this is what blew my mind, ... I changed it to "myreader1.read ()" and the code execution time was almost doubled ... ... anyway checking the database, all the data was there .....

so using common sense I look good, it probably just executes both sets of code every time it launches the first reader,

but how does all this data exist?

Significantly fewer fields in Sales_Order_Header than Sales_Order_Detail, if it does read for the first, shouldn't it end at the end of the header table and then stop? so why is all the data there?

Anyway, the execution time of this code is relatively slow, does anyone have any suggestions for improving my code?

Edit: just to show that the second reader is not typing false: Debugger

as you can see the debugger went inside the reader

+3


source to share


3 answers


I don't know why, But I think I'll write an answer to my own question.

While I don't have a good answer as to why the second reader works successfully (no data loss), I have several ways to make this code faster than suggested

First Off ~

 while (myreader.Read()) 
        {
            HBPData DataEntity = new HBPData();
            DataEntity.NUMBER = (myreader1["NUMBER"].ToString());
            DataEntity.RECNO = (myreader1["RECNO"].ToString());
            DataEntity.CODE = (myreader1["CODE"].ToString());
            DataEntity.DESCRIPTION = (myreader1["ORDD_DESCRIPTION"].ToString());
            DataEntity.Quantity = (myreader1["BVORDQTY"].ToString());
            listofhbpdata2.Add(DataEntity);
        }

        myConnection.Close();
        myreader1.Close();
        myreader.Close();






        System.Data.OleDb.OleDbConnection myAccessConnection = new System.Data.OleDb.OleDbConnection();

        myAccessConnection.ConnectionString = AccessString;
        myAccessConnection.Open();
        System.Data.OleDb.OleDbCommand myAccessCommand3 = new System.Data.OleDb.OleDbCommand("delete from AllOrders", myAccessConnection);
        myAccessCommand3.ExecuteNonQuery();

        for (int i = 0; i < listofhbpdata2.Count(); ++i)
        {
            System.Data.OleDb.OleDbCommand myAccessCommand2 = new System.Data.OleDb.OleDbCommand("" +
                "Insert into AllOrders VALUES('" +
                  listofhbpdata2[i].NUMBER + "'" + ",'" + listofhbpdata2[i].RECNO.ToString() + "'" +
                ",'" + listofhbpdata2[i].CODE + "','" + listofhbpdata2[i].DESCRIPTION.Replace("\'", "F") + "'" +
                ",'" + listofhbpdata2[i].Quantity + "')", myAccessConnection);
            myAccessCommand2.ExecuteNonQuery();
        }

This code is redundant for two reasons :

  • I have to add to the database in the reader as opposed to creating a for loop that loops through the list I created that is not used for anything else.

  • I free the table and fill it with the same data + additional data when I have to check if what I am inserting exists and then only insert the rows that do not currently exist.



I replaced this code with this :

while (myreader1.Read())
                    {
   System.Data.OleDb.OleDbCommand myAccessCommand2 = new System.Data.OleDb.OleDbCommand(
   "INSERT INTO AllOrders(OrderNumber,RecordNumber,Code, Description, Quantity) " +
   "SELECT TOP 1 '" + (myreader1["NUMBER"].ToString()) + "'" + ",'" + myreader1["RECNO"].ToString() + "'" +
   ",'" + (myreader1["CODE"].ToString()) + "','" + (myreader1["ORDD_DESCRIPTION"].ToString()).Replace("\'", "F") + "'" +
   ",'" + (myreader1["BVORDQTY"].ToString()) + "'" +
   " from AllOrders " +
   "WHERE NOT EXISTS(SELECT TOP 1 OrderNumber FROM AllOrders Where OrderNumber = '" + myreader1["NUMBER"].ToString() +"')", myAccessConnection);
                        myAccessCommand2.ExecuteNonQuery();

                    }

Now

Even though starting myreader.read seemed to speed up, I replaced it with myreader1 in case it does something bad that I couldn't find

Now it works much faster. I didn't bother using DAO as suggested in Writing Large Records (Bulky Insertion) for Access in .NET / C #

Because I am already using system.data.OleDb

+1


source


Are you sure you are getting the data you myreader

want in the second call ? Something doesn't look right: your loop through myreader

, which should receive data from your first statement SELECT

, but your inner code is referencing myreader1

.

So the weird thing here is not that the second iteration should be faster than the first: it means that the second iteration returns you the data you expect. So the question is, are you sure that in this second loop:

  • you get the expected number of iterations for all the records that you expect from that second statement SELECT

    , say 5000 (as opposed to the number of records from the first statement, say 1000).

  • you actually get data for every record in the second statement SELECT

    , not just the same top record every time.



Regarding the second part of your question, how to improve the baud rate, I would recommend the following:

  • Adding data through the execution of individual statements INSERT

    will be slow.
    Take a look at this question for some very quick alternatives:
    Writing a lot of records (bulk insert) to access in .NET / C #

  • If you're doing a lot of work on your Access database, keep the connection permanently open for it, rather than opening / closing it. For this reason it can have a huge performance impact, see Where is the OLE DB connection pool? ... I usually create a table that I call Dummy

    with one record in it (no matter what it is), and then open the data reader on that table, which I keep open until I shut down the application. This ensures that the database lock file is kept in place and not created / deleted every time I perform some operation on the database. You would be surprised at how this affects performance if you are doing a lot of operations on the database.

+4


source


Can you give us lines that are slower?

Try using blocks to work with IDisposable objects such as DB Connections. This way you will be safe with exceptions. And you won't need to explicitly specify Close ().

There are many line additions in the loop. Try StringBuilder instead.

+1


source






More articles:


All Articles