BorlandTalk.com Borland discussion newsgroups   Archives   FAQ   Search   Memberlist   Usergroups   Register   Profile   Log in to check your private messages   Log in  Batchupdates performance          BorlandTalk.com Forum Index -> Delphi Databases (ADO) View previous topic :: View next topic   Author Message pilmart Guest Posted: Thu May 03, 2007 7:39 pm    Post subject: Batchupdates performance Hi there More batchupdate related nonsense again, anyway here goes. I have a dataset open for batch updates ctstatic & ltBatchoptimistic, I update multiple records with 35 new fields per record. After I have walked the recordset and updated all required records I perform an updatebatch command, this seems to take forever approx 3.5 mins for 500 records, however the actual updates on the dataset are lightning fast < 1 sec. This is all in Delphi 5, oracle 10G D/B, ADO connected via oracle oledb, TADOdataset is set to clUseClient, ctStatic, ltBatchoptimistic, any thoughts as to why the updatebatch command takes so long ??? Back to top Del Murray Guest Posted: Fri May 04, 2007 7:20 am    Post subject: Re: Batchupdates performance When you say 35 "new fields" , do you mean you changed the value of 35 existing columns in 500 rows or do you mean you added 35 "new" columns to an existing row ??? BatchOptimistic updating can be a bit slow but yours sounds very slow. It may be an Oracle issue .. allocating additional space, poor indexing schemes. When you do batch optimistic locking, you need an ***primary key*** selected in your dataset. That way the DB can go immediately to the proper row in the phsysical database and update the data. When you dont supply a unique key (primary index or primary key .. depending on the DB), then the DB engine must take each record that you have in your data set and read from one end of the table to the other to determine which row was the row that you wanted to update. .... Then, he takes the next row in your dataset and once again, reads from one end of the table to the other to see which row should receive the updates ...get the picture ???? when you create your dataset do something like this .. 'select id, name, address, ssn from tablexxx where name like '%A%' id (in the select statement) is the primary key defined for the table. By definition, primary keys are "unique keys". HTH Del Back to top Del Murray Guest Posted: Fri May 04, 2007 7:22 am    Post subject: Re: Batchupdates performance PS .. you read the whole table 500 times .. I'd say 3 minutes was pretty quick Back to top pilmart Guest Posted: Fri May 04, 2007 8:11 am    Post subject: Re: Batchupdates performance "Del Murray" <Del.Murray (AT) CreditHawk (DOT) Net> wrote: Quote: PS .. you read the whole table 500 times .. I'd say 3 minutes was pretty quick :-) Hi there What I am doing is updating 35 fields for 500 records , its a good suggestion but I am including the key field in the select statement as well, however I am including it by default because the dataset I'm updating is based on a select * from x where etc etc so the primary key is included by default. The initial updates are very fast cos the locate command uses the primary key as it's search so it zips along nicely, it's just the batch update command thats slow. Back to top Del Murray Guest Posted: Fri May 04, 2007 5:01 pm    Post subject: Re: Batchupdates performance Then I would look for the DB to be the problem. Oracle requires a full time DB admin. I'm no Oracle expert, but I have *heard* stories about space allocation being a big deal. Talk to your DBA and see what he comes up with. Back to top pilmart Guest Posted: Fri May 04, 2007 8:16 pm    Post subject: Re: Batchupdates performance "Del Murray" <Del.Murray (AT) CreditHawk (DOT) Net> wrote: Quote: Then I would look for the DB to be the problem. Oracle requires a full time DB admin. I'm no Oracle expert, but I have *heard* stories about space allocation being a big deal. Talk to your DBA and see what he comes up with. Switched over to dsn-less ODBC connection, bit of a backward step ...but now runs @ 25 recs per second instead of 6 so an considerable increase, still not super-fast but better. 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 (ADO) 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.