locked
OleDB Query is too Slow RRS feed

  • Question

  • User253847291 posted

    hello,


    im using an access database with less than 50.000 records on a single table, no relations.

    ive put the following code inside a two for cycles as per the respective variables (i, j) the connection is opened outside the outer cycle and closed after it, im holding the connection open to save up on open/close time and im using OleDB thinking it would be faster than ODBC. The ds fill operation takes nearly 3 seconds in debug mode, the total process will require almost 4 hours. How is it possible to speed it up?

                        str_sql = BASE_SQL;
                        str_sql = str_sql.Replace("?1", i.ToString());
                        str_sql = str_sql.Replace("?2", j.ToString());
    
                        ds = new DataSet();
                        dta = new OleDbDataAdapter(str_sql, cnn.ConnectionString);
                        dta.Fill(ds);


    Tuesday, June 15, 2010 2:41 PM

All replies

  • User-821857111 posted

    Have you tested the same SQL against ODBC? Was it quicker? Oh, and what is the actual SQL you are executing?


    Tuesday, June 15, 2010 4:07 PM
  • User253847291 posted

    hello,

    here is the SQL, i didnt try ODBC last time on this forum some of yours said OleDB is faster with access databases, isnt it?


                const string BASE_SQL = @"SELECT id_es1 AS ri FROM ar WHERE ((
                                          (e1=?1 Or e1=?2) AND (e2=?1 Or e2=?2) OR (e1=?1 Or e1=?2) AND (e3=?1 Or e3=?2) OR (e1=?1 Or e1=?2) AND (e4=?1 Or e4=?2) OR (e1=?1 Or e1=?2) AND (e5=?1 Or e5=?2) OR
                                          (e2=?1 Or e2=?2) AND (e3=?1 Or e3=?2) OR (e2=?1 Or e2=?2) AND (e4=?1 Or e4=?2) OR (e2=?1 Or e2=?2) AND (e5=?1 Or e5=?2) OR
                                          (e3=?1 Or e3=?2) AND (e4=?1 Or e4=?2) OR (e3=?1 Or e3=?2) AND (e5=?1 Or e5=?2) OR
                                          (e4=?1 Or e4=?2) AND (e5=?1 Or e5=?2) )
                                          AND (ru_a <> 'R1N') AND (an_1o > 0))";


    Tuesday, June 15, 2010 7:30 PM
  • User-821857111 posted

    ODBC is not recommended at all. I thought you were questioning why the query was slow under OleDb. Actually, the porblem is withyour query. However, I can't help you with that because I still have no idea what the actual SQL you are executing will be.


    Wednesday, June 16, 2010 12:18 AM
  • User253847291 posted

    the query is as posted above, in place of ?1 and ?2 there will be two integers.

    the base_sql variable is modified accordingly with the code posted on my first message


    is there a way to optimize it and speed it up? earning only 1 second each would reduce the total process time of 65+ minutes


    thank you,

    Wednesday, June 16, 2010 10:44 AM