BorlandTalk.com Borland discussion newsgroups   Archives   FAQ   Search   Memberlist   Usergroups   Register   Profile   Log in to check your private messages   Log in  MySQL & TDBGrid issue Goto page 1, 2  Next          BorlandTalk.com Forum Index -> Delphi Databases (SQL Servers) View previous topic :: View next topic   Author Message Robby Guest Posted: Thu Sep 18, 2003 4:25 pm    Post subject: MySQL & TDBGrid issue Hi, I've no doubt that this is the wrong place to be asking this kind of question but I hope that someone can still help me out! My Boss is considering using MySQL for all future projects so he wants me to check it out, so I've written a test app. All it is is a MySQL query (a TMyQuery from CoreLabs) that's connected to a TDBGrid. The query is a simple 'select * from sometable' over a table of 100,000 rows. When I run the app it loads the first page of the grid ok. The problem is that when I drag the grid's verical scroll bar to go the last page (the eof) it's clearly fetching the entire table from the server and loading the whole thing into the grid. When it's completed this (which takes ages) memory consumsion has ramped right up and I can scroll thru the table at speed with no network activity, so I believe that it really has loaded every row. I would have expected it to load each page of the grid only when it was needed for display. Am I missing some DBGrid option or something??! I'm using D5 and MySQL v4.1 Many Thanks, Robby Back to top Wayne Niddery [TeamB] Guest Posted: Thu Sep 18, 2003 5:26 pm    Post subject: Re: MySQL & TDBGrid issue Robby wrote: Quote: My Boss is considering using MySQL for all future projects so he wants me to check it out, so I've written a test app. All it is is a MySQL query (a TMyQuery from CoreLabs) that's connected to a TDBGrid. The query is a simple 'select * from sometable' over a table of 100,000 rows. When I run the app it loads the first page of the grid ok. The problem is that when I drag the grid's verical scroll bar to go the last page (the eof) it's clearly fetching the entire table from the server and loading the whole thing into the grid. When it's completed this (which takes ages) memory consumsion has ramped right up and I can scroll thru the table at speed with no network activity, so I believe that it really has loaded every row. I would have expected it to load each page of the grid only when it was needed for display. Am I missing some DBGrid option or something??! I'm using D5 and MySQL v4.1 This is not a problem with MySQL, this is a problem with what you are doing. Most (if not all) SQL databases are "unidirectional" - that means when you get a result set from a query, you can only move forward in that result set and only do it once. You cannot go directly to the last (or any other) record witout working through all the earlier ones. Because you can only do it once, the dataset must cache all those records locally in order to allow you to move back and forth in the grid. There is no reason why you should need to display 1000 records, never mind 100,000 records in a grid. There is nothing reasonable any user can do with that many records at one time and there is no fast way to retrieve that many. -- Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com) RADBooks: http://www.logicfundamentals.com/RADBooks.html "It is error alone which needs the support of government. Truth can stand by itself." - Thomas Jefferson Back to top tcpip Guest Posted: Thu Sep 18, 2003 10:47 pm    Post subject: Re: MySQL & TDBGrid issue Hi Robby, Quote: TMyQuery from CoreLabs) that's connected to a TDBGrid. The query is a simple 'select * from sometable' over a table of 100,000 rows. When I run .... Am I missing some DBGrid option or something??! I'm using D5 and MySQL v4.1 The DBGrid works as it was designed by Borlandddd. It's slow down with unidirectional cursors. Use ClientDataSet or another caching dataset component or limit the query to some reasonable number of records by applying some restrictions natural for your data, e.g. select * from starts where planets > 1 People are frequently using alphabet letters (e.g. from A to B), years #, record #, lines per page and etc. Regards, Igor. Back to top Bruce Vander Werf Guest Posted: Fri Sep 19, 2003 7:42 pm    Post subject: Re: MySQL & TDBGrid issue Wayne, I respectfully disagree. My experience is that power users want to edit records in grid fashion. --Bruce On Thu, 18 Sep 2003 13:26:44 -0400, "Wayne Niddery [TeamB]" <wniddery (AT) chaff (DOT) aci.on.ca> wrote: Quote: There is no reason why you should need to display 1000 records, never mind 100,000 records in a grid. There is nothing reasonable any user can do with that many records at one time and there is no fast way to retrieve that many. Bruce Vander Werf [email]brucev2 (AT) hotmail (DOT) com[/email] Back to top Bruce Vander Werf Guest Posted: Fri Sep 19, 2003 7:42 pm    Post subject: Re: MySQL & TDBGrid issue So what do you think of the CoreLabs components? Have you had a chance to use their technical support? How was it? --Bruce On Thu, 18 Sep 2003 17:25:38 +0100, "Robby" <robbybooker (AT) hotmail (DOT) com> wrote: Quote: My Boss is considering using MySQL for all future projects so he wants me to check it out, so I've written a test app. All it is is a MySQL query (a TMyQuery from CoreLabs) that's connected to a TDBGrid. The query is a Bruce Vander Werf [email]brucev2 (AT) hotmail (DOT) com[/email] Back to top Bill Todd Guest Posted: Fri Sep 19, 2003 8:37 pm    Post subject: Re: MySQL & TDBGrid issue On Fri, 19 Sep 2003 14:42:31 -0500, Bruce Vander Werf <brucev2 (AT) hotmail (DOT) com> wrote: Quote: I respectfully disagree. My experience is that power users want to edit records in grid fashion. Wayne did not say that there is anything wrong with editing in a grid. He said there is something wrong with selecting 100,000 records and letting the user browse through them. No one can scroll through 100,000 records and do anything useful. The user needs to enter some selection criteria that you can use in the WHERE clause of the SELECT to return a number of records that the user can reasonably deal with. -- Bill (TeamB) (TeamB cannot respond to questions received via email) Back to top Bruce Vander Werf Guest Posted: Sat Sep 20, 2003 12:47 am    Post subject: Re: MySQL & TDBGrid issue Yes, point taken. Thanks... --Bruce On Fri, 19 Sep 2003 13:37:08 -0700, Bill Todd <no (AT) no (DOT) com> wrote: Quote: On Fri, 19 Sep 2003 14:42:31 -0500, Bruce Vander Werf [email]brucev2 (AT) hotmail (DOT) com[/email]> wrote: I respectfully disagree. My experience is that power users want to edit records in grid fashion. Wayne did not say that there is anything wrong with editing in a grid. He said there is something wrong with selecting 100,000 records and letting the user browse through them. No one can scroll through 100,000 records and do anything useful. The user needs to enter some selection criteria that you can use in the WHERE clause of the SELECT to return a number of records that the user can reasonably deal with. Bruce Vander Werf [email]brucev2 (AT) hotmail (DOT) com[/email] Back to top Bryan Valencia Guest Posted: Sat Sep 20, 2003 1:02 am    Post subject: Re: MySQL & TDBGrid issue Admitting in advance that scrolling through 100,00 records is dumb... Has anyone thought whether changing from a client side cursor to a server side cursor might do what he asked? "Bruce Vander Werf" <brucev2 (AT) hotmail (DOT) com> wrote Quote: Yes, point taken. Thanks... --Bruce On Fri, 19 Sep 2003 13:37:08 -0700, Bill Todd <no (AT) no (DOT) com> wrote: On Fri, 19 Sep 2003 14:42:31 -0500, Bruce Vander Werf [email]brucev2 (AT) hotmail (DOT) com[/email]> wrote: I respectfully disagree. My experience is that power users want to edit records in grid fashion. Wayne did not say that there is anything wrong with editing in a grid. He said there is something wrong with selecting 100,000 records and letting the user browse through them. No one can scroll through 100,000 records and do anything useful. The user needs to enter some selection criteria that you can use in the WHERE clause of the SELECT to return a number of records that the user can reasonably deal with. Bruce Vander Werf [email]brucev2 (AT) hotmail (DOT) com[/email] Back to top Bill Todd Guest Posted: Sat Sep 20, 2003 1:31 am    Post subject: Re: MySQL & TDBGrid issue On Fri, 19 Sep 2003 18:02:07 -0700, "Bryan Valencia" <bryan (AT) 209software (DOT) com> wrote: Quote: Has anyone thought whether changing from a client side cursor to a server side cursor might do what he asked? I am not a MySQL expert but I don't think you can determine the impact without know which database he is using. With MyISAM it might help. With InnoDb you will have the classic problems with long running transactions. -- Bill (TeamB) (TeamB cannot respond to questions received via email) Back to top Wayne Niddery [TeamB] Guest Posted: Sat Sep 20, 2003 3:30 pm    Post subject: Re: MySQL & TDBGrid issue Bruce Vander Werf wrote: Quote: I respectfully disagree. My experience is that power users want to edit records in grid fashion. Not the grid editing that is so much the issue (although there is valid argument there too because of the overhead that can be involved), the issue here is the volume of data when the user cannot possibly use that data directly or even quickly. I don't care how much of a "power-user" one might be, how fast can one work through 1000 records, and while you are working on each, what use are the other 999 sitting there waiting? I've personally converted several legacy apps (Paradox, Clipper, FoxPro) that used the typical "paradigm" of showing every record in the database and allowed the user to scroll from A to Z and back. In most cases (but not all) these apps allowed one to enter some filter (typically first n letters of last name or similar) and the app responded by either moving to the closest position or filtering out the non-matches. The change really isn't that severe, it really only amounts to them having to enter at least *something* in a filter or search field(s) before seeing *any* records - i.e. the default switches from showing all records to showing none, and entering criteria always results in only seeing matching records. In *all* cases, with possibly the exception of a couple of "stick-in-the-mud" users, all ended up liking the new functionality *better* once they spent a couple of days getting used to it because they could get to the records they were actually interested in as fast or faster, and the result is good performance on a client/server or multitier application. If anything, this change appeals exactly to the power users, it is the lazier ones that would rather sit there hitting a page down button looking at 1000s of names they have no interest in to get to one they do. In fact it has the effect of turning more users into power users. -- Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com) RADBooks: http://www.logicfundamentals.com/RADBooks.html "It is error alone which needs the support of government. Truth can stand by itself." - Thomas Jefferson Back to top Wayne Niddery [TeamB] Guest Posted: Sat Sep 20, 2003 3:34 pm    Post subject: Re: MySQL & TDBGrid issue Bryan Valencia wrote: Quote: Admitting in advance that scrolling through 100,00 records is dumb... Has anyone thought whether changing from a client side cursor to a server side cursor might do what he asked? In most cases that results in keeping a transaction open for long periods. In versioning databases that is especially deadly to performance. But it still does not address the issue: what can any user do with even 1000 displayed records at once? -- Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com) RADBooks: http://www.logicfundamentals.com/RADBooks.html "It is error alone which needs the support of government. Truth can stand by itself." - Thomas Jefferson Back to top Robby Guest Posted: Mon Sep 22, 2003 12:53 pm    Post subject: Re: MySQL & TDBGrid issue Thanks. So then am I right in thinking that if the user wants to go from showing the first grid page to showing the last grid page then there's no alternative but to let it load the entire table into the grid? Almost all our tables are viewed by the users thru DBGrids so this behavier would immediately rule out MySQL (and I guess any SQL DB). Robby "Wayne Niddery [TeamB]" <wniddery (AT) chaff (DOT) aci.on.ca> wrote Quote: Robby wrote: My Boss is considering using MySQL for all future projects so he wants me to check it out, so I've written a test app. All it is is a MySQL query (a TMyQuery from CoreLabs) that's connected to a TDBGrid. The query is a simple 'select * from sometable' over a table of 100,000 rows. When I run the app it loads the first page of the grid ok. The problem is that when I drag the grid's verical scroll bar to go the last page (the eof) it's clearly fetching the entire table from the server and loading the whole thing into the grid. When it's completed this (which takes ages) memory consumsion has ramped right up and I can scroll thru the table at speed with no network activity, so I believe that it really has loaded every row. I would have expected it to load each page of the grid only when it was needed for display. Am I missing some DBGrid option or something??! I'm using D5 and MySQL v4.1 This is not a problem with MySQL, this is a problem with what you are doing. Most (if not all) SQL databases are "unidirectional" - that means when you get a result set from a query, you can only move forward in that result set and only do it once. You cannot go directly to the last (or any other) record witout working through all the earlier ones. Because you can only do it once, the dataset must cache all those records locally in order to allow you to move back and forth in the grid. There is no reason why you should need to display 1000 records, never mind 100,000 records in a grid. There is nothing reasonable any user can do with that many records at one time and there is no fast way to retrieve that many. -- Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com) RADBooks: http://www.logicfundamentals.com/RADBooks.html "It is error alone which needs the support of government. Truth can stand by itself." - Thomas Jefferson Back to top Kevin Frevert Guest Posted: Mon Sep 22, 2003 1:00 pm    Post subject: Re: MySQL & TDBGrid issue Just curious, what kind of business allows this kind of end-user 'behavior'? krf "Robby" <robbybooker (AT) hotmail (DOT) com> wrote Quote: Thanks. So then am I right in thinking that if the user wants to go from showing the first grid page to showing the last grid page then there's no alternative but to let it load the entire table into the grid? Almost all our tables are viewed by the users thru DBGrids so this behavier would immediately rule out MySQL (and I guess any SQL DB). Robby Back to top Robby Guest Posted: Mon Sep 22, 2003 2:11 pm    Post subject: Re: MySQL & TDBGrid issue Well, surely any that lets the user navigate around any DBGrid? We're currently running an Advantage DB, which is admitadly not an SQL DB as such. Advantage only loads the rows that it needs, so if a user is at the first page of a DBGrid and they jump to the eof (by dragging the vertical scroll bar) then it'll only load the rows required for the DBGrid page that is at the end of the table, so it skips everything in between cos it knows it doesn't need them. MySQL loading all the rows just to get to the end of the table is crazy! Any suggestions would be greatly appreciated. Cheers, Robby "Kevin Frevert" <kevin (AT) workdrinkingcoffee (DOT) com> wrote Quote: Just curious, what kind of business allows this kind of end-user 'behavior'? krf "Robby" <robbybooker (AT) hotmail (DOT) com> wrote in message news:3f6ef0cb (AT) newsgroups (DOT) borland.com... Thanks. So then am I right in thinking that if the user wants to go from showing the first grid page to showing the last grid page then there's no alternative but to let it load the entire table into the grid? Almost all our tables are viewed by the users thru DBGrids so this behavier would immediately rule out MySQL (and I guess any SQL DB). Robby Back to top Kevin Frevert Guest Posted: Mon Sep 22, 2003 5:14 pm    Post subject: Re: MySQL & TDBGrid issue Sorry, my question wasn't meant to be insulting. I was just curious what kind of business (mail order, horse supplies, etc) that users have to navigate DBGrids with 1000+ records to do their jobs. krf "Robby" <robbybooker (AT) hotmail (DOT) com> wrote Quote: Well, surely any that lets the user navigate around any DBGrid? We're currently running an Advantage DB, which is admitadly not an SQL DB as such. Advantage only loads the rows that it needs, so if a user is at the first page of a DBGrid and they jump to the eof (by dragging the vertical scroll bar) then it'll only load the rows required for the DBGrid page that is at the end of the table, so it skips everything in between cos it knows it doesn't need them. MySQL loading all the rows just to get to the end of the table is crazy! Any suggestions would be greatly appreciated. Cheers, Robby Back to top Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First         BorlandTalk.com Forum Index -> Delphi Databases (SQL Servers) All times are GMT Goto page 1, 2  Next Page 1 of 2   Jump to: Select a forum C++ Builder Newsgroups----------------C++ BuilderC++ Builder (ActiveX)C++ Builder (Announcements)C++ Builder (Command Line Tools)C++ Builder DatabasesC++ Builder Databases (ADO)C++ Builder Databases (Desktop)C++ Builder Databases (InterBase Express)C++ Builder Databases (Multi-Tier)C++ Builder Databases (SQL Servers)C++ Builder (Graphics)C++ Builder (IDE)C++ Builder (Internet)C++ Builder (Internet Socket)C++ Builder (Internet Web)C++ Builder (Jobs)C++ Builder (Language)C++ Builder (Language C++)C++ Builder (Compatibility with MFC)C++ Builder (Native API)C++ Builder (Non-Technical)C++ Builder (Students)C++ Builder (Third Party Tools)C++ Builder (Upgrade)C++ Builder (VCL)C++ Builder (VCL Components Usage)C++ Builder (VCL Components Development)C++ Builder (VCL Usage)C++ Builder (WinAPI)C++ Builder Installation Delphi Newsgroups----------------Delphi (General)Delphi ActiveX Controls UsageDelphi ActiveX Controls DevelopmentDelphi As400Delphi DatabasesDelphi Databases (ADO)Delphi Databases (dbExpress)Delphi Databases (Desktop)Delphi Databases (InterBase Express)Delphi Databases (Multi-Tier)Delphi Databases (SQL Servers)Delphi DeploymentDelphi GraphicsDelphi IDEDelphi InternationalizationDelphi InternetDelphi Internet Isapi-WebBrokerDelphi Internet WebSnapDelphi Internet WinsockDelphi JobsDelphi Language BASMDelphi Language ObjectPascalDelphi Model Driven Architecture (General)Delphi Model Driven Architecture (Third Party)Delphi MultimediaDelphi Native APIDelphi Non-TechnicalDelphi ObjectPascalDelphi OLE AutomationDelphi OO designDelphi OpenToolsAPIDelphi Reporting-ChartingDelphi RTLDelphi StudentsDelphi TeamSourceDelphi Thirdparty ToolsDelphi Thirdparty Tools (General)Delphi Thirdparty Tools (IntraWeb)Delphi Thirdparty Tools (RaveReports)Delphi UpgradeDelphi VCLDelphi VCL ComponentsDelphi VCL Components UsingDelphi VCL Components WritingDelphi WebServices SOAPDelphi WinAPIDelphi XMLDelphi Installationcomp.lang.pascal.delphi.components.misccomp.lang.pascal.delphi.components.usagecomp.lang.pascal.delphi.components.writingcomp.lang.pascal.delphi.databasescomp.lang.pascal.delphi.misccomp.sources.delphi JBuilder Newsgroups----------------JBuilderJBuilder AnnouncementsJBuilder Applet IssuesJBuilder As400JBuilder CompilerJBuilder CORBA/RMIJBuilder DatabasesJBuilder DB SwingJBuilder DebuggerJBuilder DeploymentJBuilder DocumentationJBuilder EnterpriseJBuilder HandheldJBuilder IDEJBuilder JavaJBuilder JavaAPIJBuilder Java LanguageJBuilder Java SwingJBuilder Java BeansJBuilder Java Beans UsageJBuilder Java Beans DevelopmentJBuilder JBCLJBuilder JDataStoreJBuilder JobsJBuilder LinuxJBuilder MacintoshJBuilder & MobileSetJBuilder MultiLingual AppsJBuilder Non-TechnicalJBuilder OpenToolsJBuilder Servlets (JSP)JBuilder StudentsJBuilder Team DevelopmentJBuilder Thirdparty ToolsJBuilder UpgradeJBuilder Installation InterBase Newsgroups----------------InterBaseInterBase Bug DiscussionInterBase GeneralInterBase IBConsoleInterBase OpenSourceInterBase ReplicationInterBase SQLInterBase ThirdpartyInterBase Installation Miscellaneous Borland Newsgroups----------------AppCenterAppServerEnteraVisiBroker  You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum Powered by phpBB © 2001, 2006 phpBB Group SEO toolkit © 2004-2006 webmedic.