BorlandTalk.com Borland discussion newsgroups   Archives   FAQ   Search   Memberlist   Usergroups   Register   Profile   Log in to check your private messages   Log in  Sql and speed          BorlandTalk.com Forum Index -> Delphi Databases (SQL Servers) View previous topic :: View next topic   Author Message Ole Guest Posted: Mon Apr 11, 2005 7:34 pm    Post subject: Sql and speed When I use the Sqlyog frontend against my MySqldatabase, 45000 records are given to me in a second. Using my D6 app with a querycomponent the time is a lot more. If one app can do it in a second, why not my app :) The Sqlcommand is SELECT field1,field2 WHERE ....FROM.... A resultset of 45000 records should be accepted if that is number of records that meet the above crieria? I know speed is relative, but 45000 records should be delivered in D6 like a flash of lightning? What is the general feeling on these questions? Regards, Ole Back to top Bill Todd Guest Posted: Mon Apr 11, 2005 9:58 pm    Post subject: Re: Sql and speed I am not a MySQL user but be careful that you are not mixing the time to execute the query on the server and the time to fetch the records to the client. The time to execute the query on the server will be the same in both cases. If one app is fetching the records and one is not there will be a big difference in time. -- Bill Todd (TeamB) TeamB cannot answer questions received via email Back to top Ole Guest Posted: Tue Apr 12, 2005 10:51 am    Post subject: Re: Sql and speed Here comes the mother of stupid questions :) Both Sqlyog and my D6 app are run from the same client. Are there any known "overhead" in Delphi that takes time? I know of "enablecontrols/disablecontrols". Links to best practicearticles is appreciated. I know that the workload of the Lan, the hardware etc are all involved in "speed". Perhaps there is a rule of thumb saying "don't ask for more than 1000 records in a resultset"? I quess C/S is were the quality of the programmers job shows up. Ole Back to top Lysander Guest Posted: Tue Apr 12, 2005 12:01 pm    Post subject: Re: Sql and speed In article <425ba837$1 (AT) newsgroups (DOT) borland.com>, [email]oleekerhovd (AT) hotmail (DOT) com[/email] says... Quote: I know that the workload of the Lan, the hardware etc are all involved in "speed". Perhaps there is a rule of thumb saying "don't ask for more than 1000 records in a resultset"? With C/S, usually you can use a "cursor" on the serverside, and usually as a general thumb-rule, on the client you never need more than 40 (!) records at a time. The rest will be done by scrolling the cursor on the server. This is in contrast to desktop-tables like dBase and Paradox, where often the complete file(=table) will be used. MySQL OTOH is not a real SQL-Server but also not just a desktop-table; it's something in between. I understand that if you issue a command like "select * from Table_A where condition_a = XYZ" then it depends on the settings in your database-engine and on the components which your are using in your application (and how you set their properties), how many recordsets will be "prepared","loaded","cached" at once before you can start working with them. Also, consider that this SQLYog might be using another ODBC-driver than your D6-Application. From FireBird I know that the commercial driver from EASYSOFT is faster than the free one from IBPoenix. -- ciao, André ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ European dBase-conference dBKonEurope on June 3rd and 4th English spoken conference around different database-topics :: Info here: www.dbkon.org Back to top Ole Guest Posted: Tue Apr 12, 2005 2:13 pm    Post subject: Re: Sql and speed Please elaborate. Regards, Ole Back to top Lysander Guest Posted: Tue Apr 12, 2005 4:02 pm    Post subject: Re: Sql and speed In article <425bd778$1 (AT) newsgroups (DOT) borland.com>, [email]oleekerhovd (AT) hotmail (DOT) com[/email] says... Quote: Please elaborate. I am not such an expert on it, but the difference appears to show in the system of storing the data to one or many files. Maybe I am wrong, but for me a "real" SQL-Server does not hold tables with a "natural" order of recordsets. In fact, I would expect that most "real and big" databases are storing "field-values" on different pages of the same file(s), just as the opimizer sees fit. One 'natural' order today can be completely another tomorrow, without any physical 'sorting' pushed by the DB-Admin. It means that the data belonging to one table is not stored physically in one sequence. A "real" desktop table OTOH always and only stores table data in a fixed order; usually you can move with cursors through one file and read the recordsets as a stream. As DB-Admin, you can 'sort' the files to a new physical order. As far as I know from a fellow who is using MySQL for a real-estate web application, MySQL is server-based and benefits from reduced traffic by sending only the requested recordsets over the wire. But on the server itself it nevertheless stores data in physically isolated groups or units, where one "table" would represent one unit. Something like a server-sided BDE for desktop-tables. I meet this guy regularly on user-group meetings; next time I will ask him some more details and to show to me an example form. Then I will have a closer look on the performance of MySQL with really a lot of records at once. -- ciao, André ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ European dBase-conference dBKonEurope on June 3rd and 4th English spoken conference around different database-topics :: Info here: www.dbkon.org Back to top Bruce Michener Guest Posted: Tue Apr 12, 2005 5:19 pm    Post subject: Re: Sql and speed Bill Todd wrote: Quote: What kind of query component? TQuery? TADOQuery? TSQLQuery? Is the query component connected to any data aware controls? If so, what kind? Does the app do a FetchAll or go to the last record? Does Sqlyog use the same driver that the Delphi app is using? There are lots of possible reasons for the performance difference. I get pretty fast return results into my D6 applications using ZeosLib components from SourceForge when accessing MySQL databases. Back to top Bill Todd Guest Posted: Tue Apr 12, 2005 5:37 pm    Post subject: Re: Sql and speed What kind of query component? TQuery? TADOQuery? TSQLQuery? Is the query component connected to any data aware controls? If so, what kind? Does the app do a FetchAll or go to the last record? Does Sqlyog use the same driver that the Delphi app is using? There are lots of possible reasons for the performance difference. -- Bill Todd (TeamB) TeamB cannot answer questions received via email Back to top Martijn Tonies Guest Posted: Tue Apr 12, 2005 6:10 pm    Post subject: Re: Sql and speed Quote: Please elaborate. Also, MySQL doesn't have "fetch on demand" or a "server side cursor", as far as I know. I believe this is new functionality in 5 or 4.1.high -- With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL Server Upscene Productions http://www.upscene.com Back to top Ole Guest Posted: Wed Apr 13, 2005 3:41 am    Post subject: Re: Sql and speed Quote: Also, MySQL doesn't have "fetch on demand" or a "server side cursor", as far as I know. I believe this is new functionality in 5 or 4.1.high This is interesting. A cursor, does it mean I can place a "bookmark" on the table and start a Select from that point in a new query? Regards, Ole Back to top Martijn Tonies Guest Posted: Wed Apr 13, 2005 12:25 pm    Post subject: Re: Sql and speed Quote: Also, MySQL doesn't have "fetch on demand" or a "server side cursor", as far as I know. I believe this is new functionality in 5 or 4.1.high This is interesting. A cursor, does it mean I can place a "bookmark" on the table and start a Select from that point in a new query? No, not that I know of. A simple cursor would be that the data is being made ready for you on the server and that you can fetch as you like. For example, InterBase does that. Oracle does that. MS SQL in combination with ADO does not do that (unless you specify "server side cursor" which results in other problems). MySQL doesn't do that. If you get a resultset, you fetch all data. -- With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL Server Upscene Productions http://www.upscene.com 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 Page 1 of 1   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.