CompuServe Thread

(This request for information is being sent to : – ODBC – Desktop Drivers – MSMFC – Database Classes) I am having trouble with the reaction times when using CRecordSet and would like to know if this a common problem or am I doing something wrong : .MDB : The ACCESS DataBase has 121.545 records. – sorted by the SubmitDate. – the first field is a counter – the main key is : SubmitDate, JobName, JobNumber – JobName is an Index by "1)" – JobName is not Index by "2)" MS-QUERY : is being started before each SQL Query because the first query seems to be slower than the following ones. MyPgm1 : Does not store the Query results for Views (BuildJobData;) MyPgm2 : Does store the Query results for Views (BuildJobData;) CPP Code : used for timing : JSet.Open( CRecordset::forwardOnly, m_szSQLJobsDoc, CRecordset::readOnly ); BuildJobData(); // Query results stored in Doc for Views while (!JSet.IsEOF()) { JSet.MoveNext(); BuildJobData(); // Query results stored in Doc for Views } JSet.m_SatzAnzahl = JSet.GetRecordCount(); JSet.Close(); Query's : Records found ; MS-QUERY ; MyPgm1 ; MyPgm2 SELECT * FROM PRSTAT00 WHERE JOBNAME = 'MAWI000I' 1) 405 ; 4 Seconds ; 00.06 Minutes ; 00.07 Minutes 2) 405 ; 13 Seconds ; ; 01.51 Minutes SELECT * FROM PRSTAT00 WHERE JOBNAME = 'SYSTNV00' 1) 188 ; 4 Seconds ; 00.07 Minutes ; 00.07 Minutes 2) 188 ; 10 Seconds ; ; 01.12 Minutes SELECT * FROM PRSTAT00 WHERE JOBNAME LIKE 'SYSTNV0?' 1) 386 ; 4 Seconds ; 02.10 Minutes ; 02.12 Minutes 2) 386 ; 8 Seconds ; ; 02.09 Minutes SELECT * FROM PRSTAT00 WHERE JOBNAME LIKE 'SYSTNV*' 1) 1.458 ; 5 Seconds ; 02.19 Minutes ; 02.32 Minutes 2) 1.458 ; 6 Seconds ; ; 02.29 Minutes SELECT * FROM PRSTAT00 WHERE JOBNAME LIKE 'SYST*' 1) 14.798 ; 6 Seconds ; 05.36 Minutes ; 10.43 Minutes 2) 14.798 ; 4 Seconds ; ; 07.32 Minutes SELECT * FROM PRSTAT00 WHERE JOBNAME LIKE 'JOH*' 1) 707 ; 3 Seconds ; 06.47 Minutes ; 07.07 Minutes 2) 707 ; 8 Seconds ; ; 02.49 Minutes SELECT * FROM PRSTAT00 WHERE ((SUBMITDATE >= '1994-02-25 00:00:00') AND (ENDEDATE <= '1994-02-28 23:59:59')) 1) 2.122 ; 3 Seconds ; 01.31 Minutes ; 01.53 Minutes 2) 2.122 ; 3 Seconds ; ; 01.41 Minutes Assumptions : – "=" ist faster than "LIKE" – MS-QUERY and MyPGM* use the same ODBC drivers. – SIMBA.DLL Version 1.02.1403 – MS-QUERY and MyPGM* use the same ODBC drivers. – MSQUERY: uses SQLColumns() + MSQUERY: SQLDescribeCol() which my programs does not. (ODBC Sniffler) – MS-QUERY and MyPGM* stores the found data in the Doc. (ODBC Sniffler) Conclusions : – Both "=" and "LIKE" use the Index for JobName – there is much more Disk activity with "LIKE" – due to the Disk movement from the Index address to the record address ? Questions : – Why is MS_QUERY so much faster than MyPGM? – according to ODBC Sniffler from Blue Lagoon Software – MSQUERY: uses SQLColumns() + MSQUERY: SQLDescribeCol() which does not show up with my program. could this be the cause? – MyPGM is calling CRecordSet::GetDefaultSQL(), could this be the cause? – Could not using "m_strFilter" have anything to do with it? – I could not get it to work (brought all records) – I put the whole statement in a CString and GetDafaultSQL has >return CString< – with m_strFilter = "JOBNAME = 'SYSTNV==' GetDafaultSQL has >return "PRSTAT00"< (Tablename) Hopefully I have given all information needed, Mark Johnson Berlin, Germany