SQLQuery.Last lazarus / 16-Databases ---------------------------------------------------------------------- TRNelson · 2016-02-07 20:56 Can anyone tell me how this works internally?  The first time I run it on a fairly large table, it seems to take a very long time (as compared to running it local).  I have a strange feeling that it is actually reading each record in the table until it reaches the end of the table.  I looked at the ZeBedee log and the amount of data that was transmitted was huge. ---------------------------------------------------------------------- sky_khan · 2016-02-07 21:51 Its not that strange. Yes, it fetches all records and if SQLQuery.UniDirectional is false it keeps/caches all of them in memory too. So its not advisable for really big tables ---------------------------------------------------------------------- TRNelson · 2016-02-07 22:32 Quote from: SkyKhan on February 07, 2016, 10:51:54 pmIts not that strange. Yes, it fetches all records and if SQLQuery.UniDirectional is false it keeps/caches all of them in memory too. So its not advisable for really big tables Well that seems very odd to me.  Hopefully there is a very good reason for that.  It really isn't necessary to transmit EVERY record to the client if all you are doing is moving the current record pointer. ---------------------------------------------------------------------- sky_khan · 2016-02-07 22:43 Delphi/FreePascal database/dataset classes is designed for editing records on mind in the first place. So if you do not need to show/edit all records of a table then you better use parameterized and/or created on-the-fly queries. If you explain what are you trying to achieve you might get better help, i think ---------------------------------------------------------------------- rvk · 2016-02-08 09:00 With SQLQuery.Last all the records DO need to be read. You could have attached a DBGrid where all those records need to be displayed. If you didn't attach a DBGrid you could just retrieve the correct record you want via specialized SQL (like stated before). (You've just hit the downside of using a database directly over a slow line like the internet/VPN/SSH/Zebedee like stated in the previous topic ) Also note that you should never do a "SELECT * FROM TABLE". It will transmit the whole record (for all records) over the line. If you just need an ID, NAME and PLACE in your DBGrid for example, you should do a "SELECT ID, NAME, PLACE FROM TABLE" and when the users wants to change that record you could do a "SELECT * FROM TABLE WHERE ID=:ID" and display it in a separate form. ---------------------------------------------------------------------- TRNelson · 2016-02-08 15:23 Interesting.  I was unaware of this. So if you are not using DBGrid, can you advance the current record pointer by using SQLQuery.MoveBy(n) and avoid the transmission of all the intervening records to the client? And if you execute SQLQuery.Locate(name,value,[parms]) and it doesn't find a match, does it load all the table records (not employing DBGrid again)? ---------------------------------------------------------------------- balazsszekely · 2016-02-08 15:32 This is why stored procedures was invented in the first place. Do a single request client side, then make one or more complex(or not) calculation/query server side. Return a single result/record. It depends on your database server, but nowadays almost all servers support stored procedures. ---------------------------------------------------------------------- rvk · 2016-02-08 15:36 Quote from: TRNelson on February 08, 2016, 04:23:21 pmSo if you are not using DBGrid, can you advance the current record pointer by using SQLQuery.MoveBy(n) and avoid the transmission of all the intervening records to the client? And if you execute SQLQuery.Locate(name,value,[parms]) and it doesn't find a match, does it load all the table records (not employing DBGrid again)? Nope. Even if you don't use a DBGrid, TSQLQuery doesn't know that. So it will do multiple Next for MoveBy. Even with locate it needs to search the records so it needs to retrieve them. Even if you use .Filter it retrieves all records. When you want to use .Filter you should use .ServerFilter and .ServerFiltered. In that case the SQL is internally changed and only the records you want are retrieved from the server. From that, you can see that you need to do some SQL-fiddling to make sure you don't request too much data over the line. What example do you have where you would do SQLQuery.Last?? Instead of SQLQuery.Last you could do "SELECT FIRST 1 FIELDS FROM TABLE ORDER BY ID DESC"? This would retrieve the last record according to ID in your table. (Notice the FIRST 1, only retrieve 1 record and the ORDER BY ID DESC, reverse order on ID) Also... you might want to build in paging in your remote app so you don't need to retrieve thousands of records. Only when the users scrolls down or goes to the next page you could retrieve a few records each time. ---------------------------------------------------------------------- TRNelson · 2016-02-08 18:04 Well, this is progressing as it has in the past.  My learning pattern seems to be take a small step forward, take another, one more slightly bigger one, hit a bump and roadblock, fall back a few steps, Ah ha, now I get it!, repeat.  Good thing I like puzzles.  Helps keep the old grey matter alive. To answer the question as to why I want to use SQLQuery.Last is pretty simple.  I don't really need it and was just following an example on how to replace TDBNavigator and hand code it as a learning exercise. So if I code a stored procedure to search for a record (like SQLQuery.Locate), will this avoid all the data transmission?  I would be pretty okay with doing that instead of using Locate.  Just need to figure out how to do it then. ---------------------------------------------------------------------- TRNelson · 2016-02-08 18:05 Quote from: GetMem on February 08, 2016, 04:32:04 pmThis is why stored procedures was invented in the first place. Do a single request client side, then make one or more complex(or not) calculation/query server side. Return a single result/record. It depends on your database server, but nowadays almost all servers support stored procedures. That sounds like a good plan.  One that I have no clue how to do but will work on learning how.  I have never written one server side procedure yet.   I am using Firebird. ---------------------------------------------------------------------- rvk · 2016-02-08 19:06 Quote from: TRNelson on February 08, 2016, 07:05:18 pmOne that I have no clue how to do but will work on learning how.  I have never written one server side procedure yet.   I am using Firebird. Besides using a stored procedure you could also just use a specific SELECT statement. For example... if you want to edit the record with ID 23 you would do a "SELECT * FROM TABLE WHERE ID=23". There is no need to use a stored procedure for that. And it would be equivalent to doing "SELECT * FROM TABLE" and a SQLQuery.Locate('ID',23,[]);. When you want to go directly to the last record you could change the SQL to "SELECT * FROM TABLE ORDER BY ID DESC" and reopen the dataset. The first record would be the last ID without retrieving all data and you can do Prior to step through the dataset again (only now are Prev and Next reversed ) So... if in SQL there is "ORDER BY ID DESC" then the first record is the "last" and Next is the same as Prev in the normal dataset. Otherwise the first record is the first and Next is really the next record. (That way you could simulate all buttons of a DBNavigator) ---------------------------------------------------------------------- balazsszekely · 2016-02-08 19:18 @rvk is right, in this particular case you don't need a stored procedure, although you can use one just for the sake of exercise. Guys if it's firebird, what about this query: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i select * from TABLENAME where id in (select max(id) from TABLENAME) PS: http://www.firebirdsql.org/refdocs/langrefupd21-ddl-procedure.html ---------------------------------------------------------------------- rvk · 2016-02-08 19:52 Quote from: GetMem on February 08, 2016, 08:18:14 pmCode: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i select * from TABLENAME where id in (select max(id) from TABLENAME)Yeah, you're right. I didn't even think about MAX(ID). But the advantage of SELECT * FROM TABLE ORDER BY ID DESC is that you can still step backwards in the ID-list with Dataset.Next because the dataset is still all the records . So if you're looking to emulate the DBNavigator with the Next/Prev/First and Last buttons you could still do the Last and the dataset is still open so that with the Previous button you can issue a Dataset.Next to get to the previous record. Nice huh... (and confusing  ) ---------------------------------------------------------------------- TRNelson · 2016-02-08 20:57 This is all good and useful information. My original question was about SQLQuery.Last and this has pretty much brought me right back to where I was with a previous question.  Not a problem.  This is all good stuff. Ultimately what I want to have most of the functions of DBNavigator and a search function.  I would like my users to be able to search for a particular record and if successful have the current record pointer moved to the first record that matches the criteria without reloading the table.  That way they can continue to navigation from that point.  SQLQuery.Locate does that handily but it causes a lot of data movement. Is there any way of directly accessing and setting the absolute position of the current record counter in a dataset? ---------------------------------------------------------------------- rvk · 2016-02-08 21:35 Quote from: TRNelson on February 08, 2016, 09:57:26 pmUltimately what I want to have most of the functions of DBNavigator and a search function.  I would like my users to be able to search for a particular record and if successful have the current record pointer moved to the first record that matches the criteria without reloading the table.  That way they can continue to navigation from that point.  SQLQuery.Locate does that handily but it causes a lot of data movement. Is there any way of directly accessing and setting the absolute position of the current record counter in a dataset? Nope, there is not. If you want the complete dataset you can't put a cursor in the "middle" without reading the previous records. There isn't even such a thing as "middle" in a dataset if you haven't read all records yet because the client only know how many records there are if the server gives an EOF on the last record. I think you need to step off the idea of "moving the pointer" in the dataset. You said you don't have a DBGrid (or do you??) so you could just work with SQL statements to get the correct record. For instance. 1) A TEdit-box as filter. Typing in test you could issue a statement like SELECT * FROM TABLE WHERE NAME like %test%. The dataset would only hold records with test in the name. The user could step through those with Next en Prior. 2) A TEdit-box as start-value. You could do SELECT * FROM TABLE WHERE NAME>=test. In that case you could get all records with name starting from test and all the rest. The user could step through all Next records but can't do Prior to those records. But maybe that's enough. So if it is sufficient that the user only sees the records with what (s)he typed in then go for option 1. If (s)he needs to step further through to name=xoro then go for option 2. In both cases you could also implement a "Last" button and order by NAME DESC in which case the first record is directly the last. The only thing you need to do is remember that you have a reversed list so if the user presses the Previous button you do a Dataset.Next and if (s)he presses Next-button you do a Dataset.Prior. I think that is the best and fastest way to work when you want direct DB-access and don't have a DBGrid. If you want I could create an example tomorrow (if somebody doesn't beat me to it) with a dataset, filterbox and a few navigate buttons, if you can't implement this yourself. ---------------------------------------------------------------------- TRNelson · 2016-02-09 00:36 Quote from: rvk on February 08, 2016, 10:35:10 pmYou said you don't have a DBGrid (or do you??) so you could just work with SQL statements to get the correct record. I am using DBGrid and it works well for displaying several records.  The user can navigate through the table using arrows keys or by pressing some coded buttons.  The current record is displayed separately.  It all works rather well.   I use SQLQuery.Locate to do the searching.  The SQLQuery.Last is a red herring and really has a very limited use for this app and will be dropped. Is Locate nothing more than read: a record, check for match, if matched end otherwise keeping reading and comparing on the client side or does this happen on the server side?  You guys have a lot of patience and I really appreciate the help and insight. ---------------------------------------------------------------------- rvk · 2016-02-09 04:21 Locate works all on the client-side and is essentially a next/check kind a thing. In your case it's better to adjust the sql with the filter the user desires and not use locate for this. But instead of changing the sql each time you could also look into Serverfilter and Serverfiltered. Don't look at Filter and Filtered because that is client-side too. But ServerFilter will adjust the sql internally and close and open the dataset automatically. Also, if you want the exact count of a current dataset you can't do .last and usr .recordcount. Best is to use a small separate sql with SELECT COUNT(ID) FROM TABLE WHERE xxxx to get the exact count. It might be useful for the user to know how many records there are and in this case (s)he doesn't need to do ctrl-end on the grid to jump to the end to see that. (So you also might want to catch ctrl-end in the grid because that will try to go to the end which will result in a showdown when ask the records are retrieved again.) ---------------------------------------------------------------------- mangakissa · 2016-02-09 08:11 Quote from: GetMem on February 08, 2016, 08:18:14 pm@rvk is right, in this particular case you don't need a stored procedure, although you can use one just for the sake of exercise. Guys if it's firebird, what about this query: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i select * from TABLENAME where id in (select max(id) from TABLENAME) PS: http://www.firebirdsql.org/refdocs/langrefupd21-ddl-procedure.html Is nice. But there are still databases who are not indexed with an id. Is TSQLQuery.last not relative to an index? ---------------------------------------------------------------------- rvk · 2016-02-09 09:35 Quote from: mangakissa on February 09, 2016, 09:11:10 amIs TSQLQuery.last not relative to an index? No, it is relative to the order in which the server presents the dataset. That could be according to an actual index but it not necessary. You could do an "ORDER BY NAME" without having an index on the field NAME. And even if you just do an SELECT * FROM TABLE without an ORDER BY, the server presents the dataset in an unspecified order. TSQLQuery.Last would just jump to the last record in that dataset.