RE: newbie question on scrolling through a table one record at a time
Many thanks for your reply Tom. I will read it more closely over the week-end, but wanted to say thanks straight away. Kerry -Original Message- From: Tom Horstmann [mailto:[EMAIL PROTECTED] Sent: 20 August 2004 15:52 To: 'Kerry Frater'; 'MySQL List' Subject: RE: newbie question on scrolling through a table one record at a time Hi Kerry, The problem is more fundamental with the scrolling through the records/rows of Master. [..] It is not sensible to allow 200 million pieces of data to be transferred to the Delphi PC to build a local Dataset to scroll through. [..] Getting the first row is easy select * from Master order by MasterRef limit 1; It is, but LIMIT mostly is executed after getting all records meeting the WHERE-condition. That might be quite slow. but getting the next record isn't as straight forward. Mainly because I have no idea of what the next value of MasterRef is. All I know is that I want to get the next row in sequence. None of the papers I have or have seen addresses this issue. Either this concept is not required in SQL programming or it is so obvious that it doesn't need explaining. Either way I can't see the wood for the trees. If MasterRef is a unique value column then the next record would be: select * from Master order by MasterRef limit 1 where MasterRef MyCurrentMasterRefValue; This simply raises questions 1) how to get the previous row (presume you use the DESCENDING keyword of the table, 2) how to test for Begining and End of Table and 3) what to do if the column being ordered on is not unique. I ran through the same problem the last days (still). This is how i did it: At first you need a unique key (auto-increment). I name it id. If you have records that are non-unique by the column you want to sort them, try to add other columns to sort on to get them as unique as you can. Having non-unique records isn't a problem as long there are not many beeing equal. How to move through records: Starting with a value of '', always SELECT the record having a higher value in that columns you sorted on than the last one. As you said, that's all, if there all records are unique. You will need LIMIT to get through equal records. It's quite easy having an example: record key 1a 2a 3b 4b 5c Get the first record (assuming your id starts with 0): SELECT key FROM table WHERE key='' AND id-1 ORDER BY key,id LIMIT 0,1 Store these: old_key = key old_id = id Get the next record: old_key = SELECT key FROM table WHERE key=old_key AND idold_id ORDER BY key,id LIMIT 1,1 The LIMIT has to be 1 here to get the second record. As long as key stays equal to old_key, increase the limit by one for each record fetched. When getting a new value for key, set the limit to 1 again. You need to find a good relation between the amount of equal records and the columns you use to sort and move on. 500 records having the same key will make things slow, but using a long WHERE- clause will do, too. I tested this for upto 3 records. If there is an index on all columns used in the SELECTs, speed seems to be stable upto that size. hth, TomH -- PROSOFT EDV-Loesungen GmbH Co. KGphone: +49 941 / 78 88 7 - 121 Ladehofstrasse 28, D-93049 Regensburg cellphone: +49 174 / 41 94 97 0 Geschaeftsfuehrer: Axel-Wilhelm Wegmann [EMAIL PROTECTED] AG Regensburg HRA 6608 USt.183/68311http://www.proSoft-Edv.de -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
newbie question on scrolling through a table one record at a time
Hi all, I don't think this is the right list for the question but I am hoping someone in the list will be able to point me in the right direction. I am testing the use of Delphi with MySQL (using Micoolap's DAC) to access and manipulate a number of tables in a databse. Some of the more complex structures I want to do are quite clear on how to implement them with many papers and books published. My problem is the technique on implementing a far more fundamental issue. Let us say I have a couple of tables with a large number of rows (Master and Detail) with a common (indexed for performance) column MasterRef. Getting the rows from table Detail is straight forward by using a Query select * from Detail where Master.MasterRef = Detail.MasterRef The problem is more fundamental with the scrolling through the records/rows of Master. Reading previously posted information, it comes to light that if I open a table to scroll through using an application navigator then the app creates and uses a local dataset. Not a big issue if the database is local, on a high speed connection, or has a relatively small number of rows. But what if Master has 1 million rows with 200 columns. It is not sensible to allow 200 million pieces of data to be transferred to the Delphi PC to build a local Dataset to scroll through. I note there is the concept of LIMIT. This looks good until I try to see how to implement its usage in the real world. The concept of creating an app that only works on one (or a small number of rows) at a time is eluding me at the moment. Getting the first row is easy select * from Master order by MasterRef limit 1; but getting the next record isn't as straight forward. Mainly because I have no idea of what the next value of MasterRef is. All I know is that I want to get the next row in sequence. None of the papers I have or have seen addresses this issue. Either this concept is not required in SQL programming or it is so obvious that it doesn't need explaining. Either way I can't see the wood for the trees. If MasterRef is a unique value column then the next record would be: select * from Master order by MasterRef limit 1 where MasterRef MyCurrentMasterRefValue; This simply raises questions 1) how to get the previous row (presume you use the DESCENDING keyword of the table, 2) how to test for Begining and End of Table and 3) what to do if the column being ordered on is not unique. Are there any known papers, documents, references, books etc that go through these issues. Or can someone tell me that the posts I have been reading are no longer applicable and that when I program using Table components that it doesn't download a complete large dataset and that I only get one row at a time which takes away the concern, and the need to manage the data scroll directly. JOIN is not an option because in my project one form can have up to 9 DETAIL tables showing with the Master table. I am at the point where I have done a lot of reading and now want to look at the reality of implementation. Which means I have a little knowledge which is a dangerous thing. I want to change that status. Many thanks Kerry
RE: newbie question on scrolling through a table one record at a time
Hi Kerry, The problem is more fundamental with the scrolling through the records/rows of Master. [..] It is not sensible to allow 200 million pieces of data to be transferred to the Delphi PC to build a local Dataset to scroll through. [..] Getting the first row is easy select * from Master order by MasterRef limit 1; It is, but LIMIT mostly is executed after getting all records meeting the WHERE-condition. That might be quite slow. but getting the next record isn't as straight forward. Mainly because I have no idea of what the next value of MasterRef is. All I know is that I want to get the next row in sequence. None of the papers I have or have seen addresses this issue. Either this concept is not required in SQL programming or it is so obvious that it doesn't need explaining. Either way I can't see the wood for the trees. If MasterRef is a unique value column then the next record would be: select * from Master order by MasterRef limit 1 where MasterRef MyCurrentMasterRefValue; This simply raises questions 1) how to get the previous row (presume you use the DESCENDING keyword of the table, 2) how to test for Begining and End of Table and 3) what to do if the column being ordered on is not unique. I ran through the same problem the last days (still). This is how i did it: At first you need a unique key (auto-increment). I name it id. If you have records that are non-unique by the column you want to sort them, try to add other columns to sort on to get them as unique as you can. Having non-unique records isn't a problem as long there are not many beeing equal. How to move through records: Starting with a value of '', always SELECT the record having a higher value in that columns you sorted on than the last one. As you said, that's all, if there all records are unique. You will need LIMIT to get through equal records. It's quite easy having an example: record key 1a 2a 3b 4b 5c Get the first record (assuming your id starts with 0): SELECT key FROM table WHERE key='' AND id-1 ORDER BY key,id LIMIT 0,1 Store these: old_key = key old_id = id Get the next record: old_key = SELECT key FROM table WHERE key=old_key AND idold_id ORDER BY key,id LIMIT 1,1 The LIMIT has to be 1 here to get the second record. As long as key stays equal to old_key, increase the limit by one for each record fetched. When getting a new value for key, set the limit to 1 again. You need to find a good relation between the amount of equal records and the columns you use to sort and move on. 500 records having the same key will make things slow, but using a long WHERE- clause will do, too. I tested this for upto 3 records. If there is an index on all columns used in the SELECTs, speed seems to be stable upto that size. hth, TomH -- PROSOFT EDV-Loesungen GmbH Co. KGphone: +49 941 / 78 88 7 - 121 Ladehofstrasse 28, D-93049 Regensburg cellphone: +49 174 / 41 94 97 0 Geschaeftsfuehrer: Axel-Wilhelm Wegmann [EMAIL PROTECTED] AG Regensburg HRA 6608 USt.183/68311http://www.proSoft-Edv.de -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
