Re: GtkListStore and Sql tables

Hi Cristian, On 21 Feb 2006 at 23:14, Christian Weiske wrote: > Hi all, > > > I'm currently wondering what the most efficient method to load mysql > table contents into a gtkliststore. > > Loading the columns and choosing the right data type is already done, but > the actual data is giving me headaches. > > > If I have a table with 20.000 rows, loading them all on program startup > would be too slow. Ideally, the store should be loading them on demand > (e.g. when they need to be displayed because the user scrolled). > > Now one idea would be to get the number of rows from the table and > create dummy rows in the store. Then I'd write my own cell data func, and > whenever a unloaded row occurs, a query is made. That would be real > on-demand loading. Disadvantage of that approach is that thousands of > single queries are made when the list is sorted or searched. > So it seems as if loading all is required, or provide "previous" and > "next" buttons like in phpmyadmin. You could use PgUp and PgDn on the keyboard... but if you have 20k rows, paging through them 20 rows at a time will take half an hour... I would not want to be scrolling through 20k records if I got paid for it. IMHO the points to consider are the following: ** Client speed. PHP-GTK is not really blindingly fast, especially on legacy hardware - I should know because my development machine is a venerable PII 400... I would prefer having the database server do the sorting and the searching, rather than the client. That's what MySql server is good at. ** Network speed. If the table has more than a few columns, 20k records is a sizeable chunk of data and transmission won't be instantaneous. AFAIK the client has to wait until the entire result set has been transmitted (the call to mysql_query() returns) so any latency will be noticeable. ** Server speed. MySql server is quite fast, and if the database is well designed and the SQL statements are suitably optimized, server speed is unlikely to be a bottleneck. A barrage of queries for single rows may bog the server down however, especially if multiple clients are involved. As a rule, I would only load the records I might be interested in. One method I have used in PHP-GTK1 with GtkCList is as follows: Capture keystrokes while the list has focus. If the keystroke is an alphanumeric char, it gets appended to a string which is used for filtering in the WHERE clause of the SQL statement. After each valid keystroke a timeout fires - about half a second - during which nothing happens. When the timeout expires, the query is sent to the server. This avoids firing off a "premature" query after each keystroke. The filter string can be suitably displayed in an edit box. I used the BS key to delete the last char of the string as one may expect, and the DEL key to clear the string. The behaviour would be similar to the search feature in GtkTreeView, but it would act as a filter rather than a search, and the filtering would be done server-side. I used a combo box to select the column I want to filter on, if required. I tried this with GtkTreeView also, but a side effect of capturing the keystrokes is you loose the built-in search. One could use a separate GtkEntry to enter the filter string. For good measure, I use a LIMIT clause to limit the result set to a few hundred rows at most - to reduce network loading in case of "accidents". In most practical cases, the result set is well within the limit. Result sets of a few hundred rows can be sorted by the client without significant speed penalty. The above method has been working very well for me. Also, if the table has many columns, I prefer to display only certain key columns in the list - which would be the columns I would most likely use for searching or sorting. The entire record can be displayed/edited in a form if required. You can use the GtkListStore as a cache however, so you do not have to fire off a separate query to fetch the entire record. I guess this would slow down client side sorting a bit if there are many columns. HTH Regards, -- André =========================================================== Black holes were created when God divided by zero.