Thanks, RecordSet (a posh DatabaseCursor) is what I use when I directly search the database.
Seeing your code I feel there is way too much interaction going on between the client and the db. For complex operations like yours, you would usually create a stored procedure, and all the client does is making sure the sp has all the info it needs to do it’s thing and then just call it once. All the looping and what not would be done in the sp. Now it looks as if you are using SQLite which does not support stored procedures, so I am just waxing philosophically here, but…still. Consider switching to a proper DB.
we can all argue this statement all day long. I like and prefer SQLite unless I have multiple clients using the same datastore.
SQLite is great… don’t get me wrong, but the type of database that is used on a project should not be based solely on how many connection a database might have. The database feature set should also be taken into account.
In this case, creating a stored procedure that is capable of performing a database cursor with update logic would eliminate the need to have to do this in the App. Let the database do the heavily lifting on this one.
Since Sqlite does not have Stored Procedures, this is why the recommendation was as such.
they are good only when you have a DBA writing them. MY experience with SPs when they are written by developers, they should stay in “code” and non in SPs. That is with code/apps written in house and commercial apps we have bought. SPs are like VooDoo… they work well from VooDoo doctors (DBAs) but not so much from other people.
So to me SPs is not a deciding factor. Not even a factor I consider.
When speed is the critical factor, Stored Procedures are hard to beat. We’ve had a few processes that could take 10 minutes on the client get done in under a minute using a SP.
Maybe not voodoo but getting an expert to write them is helpful. From my experience they are counterintuitive to Xojo code. 
I have a class protein with string, integer, double, and boolean properties (like length, mass, charge), most of which need to be calculated from a “master property” of type string (the sequence).
About 86,000 of them.
Each of them spawns between 10 and 120 objects of type peptide, again each with string, integer, double, and boolean properties (like length, mass, charge), most of which need to be calculated from a “master property” of type string (the peptide sequence which is a fragment of the protein sequence).
About 3-4 million in total.
Now some properties in protein depend on the peptides, eg NumberOfUniquePeptides derived from a particular protein.
While stored procedures are speeding up repeated database calls they won’t help me with ActiveRecord as each safe operation is its own transaction, and I do not understand either of them enough to muck around with them (documentation on ActiveRecord is very short).
So my solution at the moment is to do it all with arrays and dictionaries, and then save it to a database. However it will pretty much need to be SQLite as the user has no way (and no inclination) to install a database server.
[quote=135089:@Rich Hatfield]SQLite is great… don’t get me wrong, but the type of database that is used on a project should not be based solely on how many connection a database might have. The database feature set should also be taken into account.
In this case, creating a stored procedure that is capable of performing a database cursor with update logic would eliminate the need to have to do this in the App. Let the database do the heavily lifting on this one.
Since Sqlite does not have Stored Procedures, this is why the recommendation was as such.[/quote]
So how is the speed compared to an in memory database which I seem to recall Markus is using?
Can’t answer that but even the in-memory database was too slow, and I think the reason was that each save was a separate transaction.
Bob,
Performing the db operations within sql should be faster than sharing the task between db and app.
Markus,
When I first glanced at your code, I was thinking that reason why you would iterate through each recordset would be that number of occurrence per recordset in the same sequence would increment in value. After re-looking at your code, this is not the case. You are assigning all records with the same Sequence as to having the same occurrence count (as shown below):
OccurencesCount = rs.RecordCount
DigestStorage.SQLExecute ("UPDATE tblPeptide SET OccurencesInDigest = " + Str( OccurencesCount ) + " WHERE Sequence='" + key.StringValue + "'")If all Sequences are being assigned the same occurrence count, then I fail to understand why this information would need to be stored in tblPeptide table. it would be ALOT faster to store this information in a separate table and can easily be populated by a group by statement (as Norm suggested and Bob alluded to).
If you populated a new table, lets call it tblSequenceOccurance, with this query:
SELECT Sequence, count(1) as OccurencesInDigest FROM tblPeptide GROUP BY Sequence;Then, when you want to return the final the results… you would perform an outer join between the two tables on Sequence in a select statement. Would recommend looking into indexing on the relationships between the two tables as this will help with query performance.
[quote=135215:@Rich Hatfield]If all Sequences are being assigned the same occurrence count, then I fail to understand why this information would need to be stored in tblPeptide table. it would be ALOT faster to store this information in a separate table and can easily be populated by a group by statement (as Norm suggested and Bob alluded to).
If you populated a new table, lets call it tblSequenceOccurance, with this query:
SELECT Sequence, count(1) as OccurencesInDigest FROM tblPeptide GROUP BY Sequence;
Then, when you want to return the final the results… you would perform an outer join between the two tables on Sequence in a select statement. Would recommend looking into indexing on the relationships between the two tables as this will help with query performance.[/quote]
Nice one. I obviously didn’t understand Norman, so my apologies.