Unanswered: Question on SQL experission for complicated query
I have a problem on how to retrieve records from a database using SQL query. It is a little bit complicated query. I do not know what SQL query experission is correct.
In the database table, there are four fields "ClassID", "Personal Nmae", "Age", "Score". The following is an example for illustration.
Class Name Age Score
0 Bob 20 78 <====
0 John 26 66 <****
2 Wilson 28 88
1 John 26 77 <****
3 Alice 25 56
1 Bob 20 89 <====
In the above example table, I would like to know the scores of students who share the same name, at the same age, BUT in different class (ClassID is specified by the user). Actually, I need a SQL SELECT experission which will lead to a query result (a recordset) like the following
Actually, in my application there are only two scores for a given name. One more further question,
If we have another table 'ClassTotalScore' consisting of two fields "Class" , "TotalScore"
Can I add some clause to the above suggested SQL experissions to get the sum of total scores of Class=0 and Class=2. Or I have to use another SQL statement to get the sum of total scores of two classes.
Moreover, if either way is possible, I would like to know whether ONE SQL statement is necessarilyfaster than TWO SQL statements.
Actually, those data in the two tables originally are stored in varibles in my VC++ 6.0 program. However, as more and more data are produced, the PC memory is almost exhaused. Hence, i have to resort to the database technology. I notice that the running speed of the databased based program is 10 times slower than the original program. I wonder if this is normal.
Sounds like your database abstraction layer might be doing something odd (either that or your database system is setup badly/running slowly).
I would still recommend rudy's method of SQL Query by the way. I haven't tested mine with more than two results (because i deleted the tables) but from experience I know that rudy tends to be right
I think i did not express my problem clearly in the previous posts. Actually, the speed of storing data (write) is ok for me. I am compliant with the speed of retrieving data (read data)--- more than 10 times slower than the memory based method.
aschk, are you sure this is not normal.
BY the way, there are 1200 plus records in the database.
Is the database on the same machine as the program? Is your database cache limit really low? 1200 is nothing really. If you said 12 Million records then I would question it. If your database is on a seperate machine and the records are big then transferring all that data across a network might be your slowdown point. Where does your program take the longest?