BorlandTalk.com Borland discussion newsgroups   Archives   FAQ   Search   Memberlist   Usergroups   Register   Profile   Log in to check your private messages   Log in  Design for Speed          BorlandTalk.com Forum Index -> Delphi Databases (InterBase Express) View previous topic :: View next topic   Author Message Lee Guest Posted: Wed Apr 07, 2004 4:50 am    Post subject: Design for Speed Q1) I have the following setup; TIBDataSet1.SelectSQL = "select * from TBLMAIN where COMPANYFLD = '00' order by COMPANYFLD, CODEFLD" TDataSetProvider1.DataSet = TIBDataSet1 TClientDataSet1.ProviderName = TDataSetProvider1 TClientDataSet1.PacketRecords = 200 TClientDataSet1.IndexFieldNames = "COMPANYFLD;CODEFLD" TDataSource1.Dataset = TClientDataSet1 TIBDataSet2.SelectSQL = "select * from TBLREF where COMPANYFLD = '00' order by COMPANYFLD, CODEFLD" TDataSource2.Dataset = TIBDataSet2 DbLookupComboBox1.Datasource = TDataSource1 DbLookupComboBox1.DataField = "REFFLD_KEYID" DbLookupComboBox1.ListSource = TIBDataSource2 DbLookupComboBox1.KeyField = "KEYIDFLD" DbLookupComboBox1.ListField = 'DESCFLD" COMPANYFLD + CODEFLD is Index in TBLMAIN COMPANYFLD + CODEFLD is Index in TBLREF KEYIDFLD Is Index in TBLREF I have more than 100,000 thousand records in TBLMAIN and also in TBLREF. And I want TBLREF to be display sorted by CODEFLD when drop down from Dblookup display. When I open TClientDataset1, it takes sometimes for to lookup all the reference data before it display on my screen. Is there anyway I can speed it up??? Q2) Second setup TIBDataSet1.SelectSQL = "select * from TBLMAIN where COMPANYFLD = '00' order by KEYIDFLD" TDataSetProvider1.DataSet = TIBDataSet1 TClientDataSet1.ProviderName = TDataSetProvider1 TClientDataSet1.PacketRecords = 200 TClientDataSet1.IndexFieldNames = "KEYIDFLD" TDataSource1.Dataset = TClientDataSet1 KEYIDFLD is Index in TBLMAIN I have more than 100,000 thousand records in TBLMAIN. When I open TClientDataSet1, it's very fast since it only load the first 200 records. Then when I do a findkey to the second or any other records, it will load all records from the database before it position to the found record. Is there any way it can stop loading when the record is already in the clientdataset buffer??? Or is there any other way to make it faster??? Thanks... Back to top Will Honor Guest Posted: Wed Apr 07, 2004 11:44 am    Post subject: Re: Design for Speed Lee wrote: Quote: Q1) I have the following setup; TIBDataSet1.SelectSQL = "select * from TBLMAIN where COMPANYFLD = '00' order by COMPANYFLD, CODEFLD" TIBDataSet2.SelectSQL = "select * from TBLREF where COMPANYFLD = '00' order by COMPANYFLD, CODEFLD" I have more than 100,000 thousand records in TBLMAIN and also in TBLREF. And I want TBLREF to be display sorted by CODEFLD when drop down from Dblookup display. When I open TClientDataset1, it takes sometimes for to lookup all the reference data before it display on my screen. Is there anyway I can speed it up??? Showing 100,000 records in a lookupcombo is a bad idea. There is no way that a user is going to look through 100,000 records. A better way to do this would be. 1.) show a form asking for your search criteria 2.) Now run your queries with a suitable where clause such as select * from TBLREF where COMPANYFLD = '00' and DESCFLD = {Your search criteria} order by COMPANYFLD, CODEFLD" This method is much better because you only bring back the records you need from the server. Regards Will. Back to top Jeremy Epp Guest Posted: Wed Apr 07, 2004 5:07 pm    Post subject: Re: Design for Speed I had a similar but smaller scale problem, the lookup was only on a 10,000 record table, and the users insisted on keeping the drop boxes, so I made a component based TCombobox with an internal TIBQuery. if the user opens the combobox without typing anything in then the list is empty but if they have type at least Threshold characters then the dropdown list is filled with the results of: Select <KeyField>, <SelectField> from <SourceTable> where SelectField Starting '<TComboBox.Text>' order by 1 On the 10,000 record table I've found a threshold of 1 character is enough to bring performance to a resonable level. "Lee" <engu (AT) wmasia (DOT) net> wrote Quote: Q1) I have the following setup; TIBDataSet1.SelectSQL = "select * from TBLMAIN where COMPANYFLD = '00' order by COMPANYFLD, CODEFLD" TDataSetProvider1.DataSet = TIBDataSet1 TClientDataSet1.ProviderName = TDataSetProvider1 TClientDataSet1.PacketRecords = 200 TClientDataSet1.IndexFieldNames = "COMPANYFLD;CODEFLD" TDataSource1.Dataset = TClientDataSet1 TIBDataSet2.SelectSQL = "select * from TBLREF where COMPANYFLD = '00' order by COMPANYFLD, CODEFLD" TDataSource2.Dataset = TIBDataSet2 DbLookupComboBox1.Datasource = TDataSource1 DbLookupComboBox1.DataField = "REFFLD_KEYID" DbLookupComboBox1.ListSource = TIBDataSource2 DbLookupComboBox1.KeyField = "KEYIDFLD" DbLookupComboBox1.ListField = 'DESCFLD" COMPANYFLD + CODEFLD is Index in TBLMAIN COMPANYFLD + CODEFLD is Index in TBLREF KEYIDFLD Is Index in TBLREF I have more than 100,000 thousand records in TBLMAIN and also in TBLREF. And I want TBLREF to be display sorted by CODEFLD when drop down from Dblookup display. When I open TClientDataset1, it takes sometimes for to lookup all the reference data before it display on my screen. Is there anyway I can speed it up??? Q2) Second setup TIBDataSet1.SelectSQL = "select * from TBLMAIN where COMPANYFLD = '00' order by KEYIDFLD" TDataSetProvider1.DataSet = TIBDataSet1 TClientDataSet1.ProviderName = TDataSetProvider1 TClientDataSet1.PacketRecords = 200 TClientDataSet1.IndexFieldNames = "KEYIDFLD" TDataSource1.Dataset = TClientDataSet1 KEYIDFLD is Index in TBLMAIN I have more than 100,000 thousand records in TBLMAIN. When I open TClientDataSet1, it's very fast since it only load the first 200 records. Then when I do a findkey to the second or any other records, it will load all records from the database before it position to the found record. Is there any way it can stop loading when the record is already in the clientdataset buffer??? Or is there any other way to make it faster??? Thanks... Back to top Jeff Overcash (TeamB) Guest Posted: Wed Apr 07, 2004 5:13 pm    Post subject: Re: Design for Speed "Lee" <engu (AT) wmasia (DOT) net> wrote: Quote: Q1) I have the following setup; TIBDataSet1.SelectSQL = "select * from TBLMAIN where COMPANYFLD = '00' order by COMPANYFLD, CODEFLD" TDataSetProvider1.DataSet = TIBDataSet1 TClientDataSet1.ProviderName = TDataSetProvider1 TClientDataSet1.PacketRecords = 200 TClientDataSet1.IndexFieldNames = "COMPANYFLD;CODEFLD" TDataSource1.Dataset = TClientDataSet1 TIBDataSet2.SelectSQL = "select * from TBLREF where COMPANYFLD = '00' order by COMPANYFLD, CODEFLD" TDataSource2.Dataset = TIBDataSet2 DbLookupComboBox1.Datasource = TDataSource1 DbLookupComboBox1.DataField = "REFFLD_KEYID" DbLookupComboBox1.ListSource = TIBDataSource2 DbLookupComboBox1.KeyField = "KEYIDFLD" DbLookupComboBox1.ListField = 'DESCFLD" COMPANYFLD + CODEFLD is Index in TBLMAIN COMPANYFLD + CODEFLD is Index in TBLREF KEYIDFLD Is Index in TBLREF I have more than 100,000 thousand records in TBLMAIN and also in TBLREF. And I want TBLREF to be display sorted by CODEFLD when drop down from Dblookup display. When I open TClientDataset1, it takes sometimes for to lookup all the reference data before it display on my screen. Is there anyway I can speed it up??? Well for one don't try this with ClientDatasets. They were never designed to hold 100,000 records efficiently. At the very least make sure hte IBX component is in unidirectional mode so you are not doubling the memory requirements. Quote: Q2) Second setup TIBDataSet1.SelectSQL = "select * from TBLMAIN where COMPANYFLD = '00' order by KEYIDFLD" TDataSetProvider1.DataSet = TIBDataSet1 TClientDataSet1.ProviderName = TDataSetProvider1 TClientDataSet1.PacketRecords = 200 TClientDataSet1.IndexFieldNames = "KEYIDFLD" TDataSource1.Dataset = TClientDataSet1 KEYIDFLD is Index in TBLMAIN I have more than 100,000 thousand records in TBLMAIN. When I open TClientDataSet1, it's very fast since it only load the first 200 records. Then when I do a findkey to the second or any other records, it will load all records from the database before it position to the found record. Is there any way it can stop loading when the record is already in the clientdataset buffer??? Or is there any other way to make it faster??? Not that I am aware of. The IBX component itself behaves the way you want on a locate, it only goes and fetches more when it is not already local. Quote: Thanks... 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 (InterBase Express) 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.