locked
Standalone Database(Dynamic Recordsets)

    問題

  • Hello,

    I'm currently using a DAO interface to connect to an access database. This database has a table with 27 columns and 650,000 entrys. The entrys are displayed in a listview. The program cannot load 650k entrys within a second, therefore it just dynamically opens the recordset so I can "scroll through the database". The user is also able to sort the listview which will trigger a requery with filter and sort propertys.

    So far so good. When I set a filter, it requerys within a second. However, when I try to SORT the entrys WITH a filter, it is extremely slow(between 10-120 seconds). There are huge differences between sorting ascending and descending. It is not the program that causes it to be slow. It's access executing the query too slowly(tested it myself using access 2010).

    My question to you guys it, if you know any alternative(MUST be standalone and support dynamic recordsets - not go through the whole table, lets me scroll through it) OR a way to optimize the access database to make it execute the sort faster.

    Recoding is not an option. The three options are:

    * Another standalone database

    * Optimize the access database

    * Fix the ado recordset

    I did try using ADO. However, I was unable of opening it dynamically, kept needing 1 1/2 minutes to just open the recordset...

    		if(!Global::MyDatabase->IsOpen())			
    		{
    					Global::MyDatabase->Open("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=JAP-MAIN.sdf");
    		}
    	
    
    //takes 2 minutes
    		if(!rs->IsOpen())
    		{
    			rs->Open(_T("SELECT * FROM Gesamt"),CADORecordset::openQuery);		
    		}

    ado2.cpp

    m_pRecordset->CursorLocation = adUseClient;
    	
    		if(bIsSelect || nOption == openQuery || nOption == openUnknown)
    		{
    			m_pRecordset->Open((LPCSTR)m_strQuery, _variant_t((IDispatch*)mpdb.GetInterfacePtr(), TRUE),
    			adOpenDynamic,adLockBatchOptimistic,adCmdUnknown);							
    		}

    I did set the parameter for the opening type to openDynamic. If you know a way to open a database dynamically using ADO, then let me know aswell.

    ODBC is not an option.

    Thanks in advance.







    • 已編輯 Basix_Dev 2012年3月26日 上午 10:49
    2012年3月26日 上午 08:55

所有回覆

  • Maybe the time waste for networking to access the database.

    NEU_ShieldEdge

    2012年3月28日 上午 06:31
  • Maybe the time waste for networking to access the database.

    NEU_ShieldEdge

    The database is not located on the network.

    I tried setting MaxBufferSize to 50000(Jet 4.0). That did improve it for a few systems(6GB,12GB Ram). However, this fix does not work on the two computers using 16GB Ram.


    • 已編輯 Basix_Dev 2012年3月29日 上午 10:53
    2012年3月29日 上午 10:52