CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Page 2 of 2 FirstFirst 12
Results 16 to 30 of 30
  1. #16
    Join Date
    Apr 2000
    Location
    Germany, Berlin
    Posts
    41

    Re: Urgent, need Idea to speed up my huge database

    Well, now I have created such an index, but when I want to search with Seek, I have to call first SetCurrentIndex, but then the programme throws an error. What might be wrong?


  2. #17
    Join Date
    May 1999
    Location
    Wisconsin, USA
    Posts
    953

    Re: What DB and access method?

    What database engine are you using?

    What access method are you using (ODBC, ADO, etc)?

    Here is an article from the Database section of CodeGuru:

    http://codeguru.earthweb.com/mfc_dat...rocedure.shtml

    And another article that may shed some light on different databases:

    http://msdn.microsoft.com/library/de.../msdeforvs.htm

    There are books that discuss Stored Procedures also, but which book you want will depend on which database engine you are using.



  3. #18
    Join Date
    Jul 2000
    Posts
    136

    Re: Urgent, need Idea to speed up my huge database

    i have never used nor heard of Seek, so i don't know...what about just a simple SELECT?

    mike

  4. #19
    Join Date
    May 1999
    Location
    Wisconsin, USA
    Posts
    953

    Re: CDaoRecordset::SetCurrentIndex

    Sounds like MS Access using DAO.

    CDaoRecordset::SetCurrentIndex()

    CDaoRecordset::Seek()



  5. #20
    Join Date
    May 1999
    Location
    Wisconsin, USA
    Posts
    953

    Re: QueryDef objects

    From the MSDN:
    "Jet databases do not support stored procedures; instead, they contain QueryDef objects. Permanent QueryDef objects serve to increase performance by saving a version of a query that has been compiled, or rather, evaluated for use by the Jet database engine. By storing a compiled version of a query, the database engine can execute without having to re-evaluate the statement. The same functionality is accomplished in both the MSDE and SQL Server through the use of stored procedures."

    It appears that Access 2000 has a conversion utility to to convert old databases from the old Access format to MSDE.

    You should definately read the article I included in one of my previous posts to you concerning the MSDE.

    I have never implemented QueryDef objects.




  6. #21
    Join Date
    Apr 2000
    Location
    Germany, Berlin
    Posts
    41

    Re: CDaoRecordset::SetCurrentIndex

    Yes, so I have done and I used the parameter I had called my index. But he throws an exception.



  7. #22
    Join Date
    May 1999
    Location
    Wisconsin, USA
    Posts
    953

    Re: Exception

    Are you trapping the exception using a try/catch block?

    What is the actual exception that is being thrown?



  8. #23
    Join Date
    Apr 2000
    Location
    Germany, Berlin
    Posts
    41

    Re: Exception

    Of course I did so. He says following: Operation for this kind of object is not possible (Sorry for bad translation, I am using the german version)


  9. #24
    Join Date
    Apr 2000
    Location
    Germany, Berlin
    Posts
    41

    Re: Exception

    I also have another problem. I can set an index to one field. OK. But with two it does not work. Here my code:

    CDaoTableDef tabledefPatiente(pPatientenDatabase);

    try
    {
    // Tabelle anhand der ersten 3 Zeichen des Nachnamens
    tabledefPatienten.Create("Patienten");

    // Felder der Tabelle Patienten
    tabledefPatienten.CreateField("Name", dbText, 50);
    tabledefPatienten.CreateField("Vorname", dbText, 50);
    tabledefPatienten.CreateField("Tier", dbByte, 1);
    tabledefPatienten.CreateField("Tierart", dbText, 20);
    tabledefPatienten.CreateField("Geschlecht", dbByte, 1);
    tabledefPatienten.CreateField("Geb", dbDate, 8);
    tabledefPatienten.CreateField("Vorbefunde", dbMemo, 0);
    tabledefPatienten.CreateField("Abrechnungsart", dbByte, 1);
    tabledefPatienten.CreateField("Strasse", dbText, 100);
    tabledefPatienten.CreateField("PLZ", dbLong, 4);
    tabledefPatienten.CreateField("Ort", dbText, 50);
    tabledefPatienten.CreateField("Postname", dbText, 100);
    tabledefPatienten.CreateField("Krankenkasse", dbText, 50);
    tabledefPatienten.CreateField("Kartennummer", dbText, 20);

    // Indexliste erstellen
    CDaoIndexInfo IndexInfo;

    CDaoIndexFieldInfo IndexFieldInfo[6];
    IndexFieldInfo[0].m_strName = "Name";
    IndexFieldInfo[1].m_strName = "Vorname";
    IndexFieldInfo[2].m_strName = "Tier";
    IndexFieldInfo[3].m_strName = "Tierart";
    IndexFieldInfo[4].m_strName = "Geschlecht";
    IndexFieldInfo[5].m_strName = "Geb";

    for(int iField=0;iField <= 5;iField++)
    IndexFieldInfo[iField].m_bDescending = TRUE; // Alle Felder abwärts


    IndexInfo.m_strName = "Patienten";
    /* IndexInfo.m_bPrimary = FALSE;
    IndexInfo.m_bUnique = FALSE;*/

    IndexInfo.m_lDistinctCount = 0;
    IndexInfo.m_pFieldInfos = IndexFieldInfo;
    IndexInfo.m_nFields = 2;

    tabledefPatienten.CreateIndex(IndexInfo);

    tabledefPatienten.Append();
    tabledefPatienten.Close();

    } catch(CDaoException *e)
    {
    e->Delete();
    }



  10. #25
    Join Date
    Jul 2000
    Posts
    1

    Re: Urgent, need Idea to speed up my huge database

    Once I worked with a Access 97 DB ok 1.7 Million records. The searches was agains a unique ID, so the indexes where very efective solution. Then I make a test and migrate the same database to Access 2000 and the same code with the same indexed keys was a lot slower and bigger than Access 97 version. Later I read that Access 2000 index use unicode, so all operations with index are slower on Access 2000. My choice was to keep Access 97 as long was possible and later I migrate to a SQL Server with excelents results.
    I think Access 2000 has problems handling big database.


  11. #26
    Join Date
    Apr 2000
    Location
    Germany, Berlin
    Posts
    41

    Re: Urgent, need Idea to speed up my huge database

    Thank you, but I just have not been ably to create these indexes and then search for the names. Could you give me an advice. I use following kind of writing:

    CDaoDatabase Test;
    Test.Open("C:\\Test.mdb");
    CDaoTableDef Tab;
    Tab.Create.......

    Tab.CreateIndex....

    CTestRecordSet Set(&Test);
    Set.Open();
    Set.SetCurrentIndex(?????);

    SetCurrentIndex throws an exception.
    What could I do?
    Would you send me your code with 1.7 miollion records?


  12. #27
    Join Date
    May 1999
    Location
    Wisconsin, USA
    Posts
    953

    Re: Exception

    Although I have never created an index using DAO, you may want to add some things to your code.

    Add a message box to your catch block so that you can see if an exception is being thrown by any of the commands in the try block.

    You may also want to add a call to CDaoTableDef::GetIndexInfo() after you have created your index to verify that you see what you think you should see (at least for debug purposes).

    If your index appears to be correct you can safely say that the problem is with the call to CDaoRecordset::Seek(). If that is the case you may want to post a new message asking for help with this routine.

    You should also post your current code in which you are attempting the seek.




  13. #28
    Join Date
    Apr 2000
    Location
    Germany, Berlin
    Posts
    41

    Seek Problem

    During the last hours I have found the mistake, first, I had created the indexes wrongly, and then I had to open the table with the atribute dbOpenTable, not dbOpenDynaset. So now, SetCurrentIndex does not throw an exception anymore. But Seek still does not find the person:
    my code:
    BOOL CTestSet::FindPerson(CString strName, CString strName2, COleDateTime &timeBirth)
    {

    SetCurrentIndex("MyPeopleIndex");

    return Seek("=", COleVariant(strName), COleVariant(strName2), COleVariant(timeBirth));
    }

    I do not find the person, although it is in the database( I have checked it with Access 97)

    Thank you a lot.


  14. #29
    Join Date
    Jul 2000
    Posts
    5

    Re: Urgent, need Idea to speed up my huge database

    Dear Mike,

    I’m wondering how to use different index in a table with ODBC driver. If there is a similar function like
    CDaoRecordSet.SetCurrentIndex(MyIndex1) ?

    Thanks in advance.

    Ian



  15. #30
    Join Date
    Jul 2000
    Posts
    136

    Re: Urgent, need Idea to speed up my huge database

    i've never used CDaoRecordset...i can't help that one.

    mike

Page 2 of 2 FirstFirst 12

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured