-
July 11th, 2000, 09:53 AM
#16
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?
-
July 11th, 2000, 11:46 AM
#17
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.
-
July 11th, 2000, 11:56 AM
#18
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
-
July 11th, 2000, 12:34 PM
#19
Re: CDaoRecordset::SetCurrentIndex
Sounds like MS Access using DAO.
CDaoRecordset::SetCurrentIndex()
CDaoRecordset::Seek()
-
July 11th, 2000, 12:39 PM
#20
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.
-
July 11th, 2000, 02:16 PM
#21
Re: CDaoRecordset::SetCurrentIndex
Yes, so I have done and I used the parameter I had called my index. But he throws an exception.
-
July 11th, 2000, 04:28 PM
#22
Re: Exception
Are you trapping the exception using a try/catch block?
What is the actual exception that is being thrown?
-
July 12th, 2000, 10:08 AM
#23
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)
-
July 12th, 2000, 10:12 AM
#24
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();
}
-
July 12th, 2000, 10:38 AM
#25
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.
-
July 12th, 2000, 11:07 AM
#26
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?
-
July 12th, 2000, 01:24 PM
#27
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.
-
July 12th, 2000, 02:12 PM
#28
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.
-
July 17th, 2000, 01:32 PM
#29
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
-
July 17th, 2000, 01:38 PM
#30
Re: Urgent, need Idea to speed up my huge database
i've never used CDaoRecordset...i can't help that one.
mike
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|