Insert, Update, Delete very slow with FireDAC DataSnap server [Edit]

I create two DataSnap server named (1)FireDAC_svr, and (2)DBX_svr, and one DataSnap client named DBX_client.

And we have:
FireDAC_svr: TADConnection -> TADQuery -> TDataSetProvider 
DBX_svr: TSQLConnection  -> TSQLDataSet -> TDataSetProvider
DBX_client: TSQLConnection -> TDSProviderConnection -> TClientDataSet

FireDAC_svr and DBX_svr set to the same DataBase environment.
DBX_Client can connect to FireDAC_svr and DBX_svr, too.

First, DBX_Client connect to FireDAC_svr, and do select, insert, update and delete for 1000 records in each step. The time cost as follow:

FireDAC: Select :  0.534 seconds
FireDAC: Insert:  6.728 seconds
FireDAC: Update:  11.256 seconds
FireDAC: Delete:  7.017 seconds

And then DBX_Client connect to DBX_svr, and do select, insert, update and delete for 1000 records in each step. The time cost as follow:

DBX: Select:  7.643 seconds
DBX: Insert:  0.541 seconds
DBX: Update:  0.605 seconds
DBX: Delete:  0.644 seconds
The problem is FireDAC performance is so low than DBX in this test except “Select” .

I am not sure is there any problem in my FireDAC server.
Is there any solution to tuning FireDAC performance in DataSnap?

My development environment:
Win 7 Pro x64
SQL 2012 Standare  update 1  (in HP DL380 server)
Delphi XE4 Enterprise update 1
FireDAC 8.0.5

Thanks for your help.


Edited by: John Tsai on Jun 20, 2013 7:37 AM

Edited by: John Tsai on Jun 20, 2013 7:39 AM

Edited by: John Tsai on Jun 20, 2013 7:41 AM
6/20/2013 2:42:12 PM
embarcadero.delphi.firedac 822 articles. 2 followers. Follow

1 Replies

Similar Articles

[PageSpeed] 10

Hello John

This is a known issue. TDataSetProvider gives much better
performance with unidirectional datasets, as it will not reopen
a dataset. Even if to set TADQuery.FetchOptions.Unidirectional
to True, this will not help, because DSP still "thinks", that it is a
bidirectional dataset. At moment there is no way to "say" to
TDataSetProvider, that FetchOptions.Unidirectional=True
means "unidirectional" dataset. We will resolve this issue. 

You can try to change TDataSetProvider.ResolveToDataSet.

With best regards,
Dmitry Arefiev / FireDAC Architect
FireDAC - Firebird, SQLite, MySQL, SQL Server, Oracle, PostgreSQL, 
DB2, SQL Anywhere, Access, Informix, ODBC high-speed data access lib
6/24/2013 2:27:22 PM

Similar Artilces:

Updating data back to datasnap server with FireDac TFDStoredProcedure
I am currently using Delphi XE5 and FireDac to try and create a test app for Datasnap client. I use a standard TFDQuery on the server side to retrieve the data and send the dataset back to the client. On the client side I connect to the datasnap server fine using a TFDStoredProcedure. This all works perfectly, but when I try and use ApplyUpdates to write the data back I get an exception and the client crashes. The questions are: 1. can you use the ApplyUpdates method with FireDac and Datasnap? 2. If so what is the best way of getting the updates back to the datasnap server? Thanks In Advance > 1. can you use the ApplyUpdates method with FireDac and Datasnap? No, you cannot. FireDAC ApplyUpdates method "knows" only how to apply updates to C/S DB, but not to middle tier. > 2. If so what is the best way of getting the updates back to the datasnap server? You can serialize FireDAC dataset with delta on client, send stream to middle-tier, load stream into TFDQuery or TFDMemTable, and call ApplyUpdates. All that will be rather some experimental work. -- With best regards, Dmitry Arefiev / FireDAC Architect ...

VS2005 - using detailsview to update, insert and delete rows from SQL 2005 database. Delete and insert work but update does not
  Using VS 2005 DetailsView to insert, delete, and update rows in SQL 2005 database.  insert and delete work but update does not.  I recieve no errors and the detailsView comes back unchanged (as well as table row is unchanged).   I am trying to use as little code behind as possible. However I do have ItemUpdating routines that seem to work (i.e Checking table for new login duplicates and encrypting passwords).  The following is the source code generated by VS2005:<%@ Page Language="VB" AutoEventWireup="false" CodeFile="frmDbRegionMgr...

FireDAC as Datasnap Client [Edit]
Hi All, Is it possible to use FireDAC components (FDConnection -> TFDQuery) as DataSnap Client (with out TClientDatatset) ?. I need this option to migrate from IBX to DataSnap make easy. TFDConnection can connect to DataSnap Server, but cannot connect TFDQuery, get exception "[FireDAC][Phys][TDBX][Datasnap] Remote error: Dbx.SQL is unrecognized command type". I guess I missed something. Any advice welcome. Thank you J Edited by: Jayanto Chua on Nov 24, 2013 6:00 PM Hello Jayanto FireDAC DataSnap supports TFDStoredProc. You can use it to call DataSnap server methods. A server method may have parameters with dataset type. Then you can call TFDStoredProc.Open to open a returned dataset. -- With best regards, Dmitry Arefiev / FireDAC Architect FireDAC - Firebird, SQLite, MySQL, SQL Server, Oracle, PostgreSQL, DB2, SQL Anywhere, Access, Informix, ODBC high-speed data access lib Hi! I'm working for creating a DataSnap. I have created DataSnap but I can't connect to it with TFDConnectin. Can you help me please, how to connect to a DataSnap Server with TFDConnection? Thanks! The following topic explains how to connect to DataSnap server: If that does not help, then you should provide more details, showing: * how your DataSnap server is configured; * how FireDAC connection is configured; * what is the error. -- With best regard...

FireDAC and Delphi Pro XE4... firedac tab not visible?
I am moving from a very old Borland product (2006) to Delphi XE4 Professional with FireDAC addon. I purchased both from Embarcadero, and have downloaded both. The documentation says the FireDAC components should automagically appear in the tabset for Delphi. They do not. I don't want to fool around too much as my environment is strictly controlled by IT, and they are very slow to do anything. Is there something that I should have done, but did not do? I installed XE4 first, registered it, then used the link in my email from Embarcadero to download the FireDAC components. Then I ra...

FireDac with Advantage Database Server
Hello ! I use Delphi XE6 and Windows 7. In my application I'm using Advantage Database Server version 9.1. If I use TTdataset native components (from ADS) to open table with active index this happens quickly, but if I use FireDac components to open table with active index, the operation is slowly. In my tests, to be fast, I need to open table with primary key index active (only) but in my database files there are a lot of index per tables and only one is Primary (per table). How can I open table with index file different of Primary Key and qui ckly? Thanks, Mauricio 1) Use TFD...

How to setup FireDac for slow connections [Edit]
I have some existing MS Access database on network which must be accessed by many users. I have done simple app with FireDac components and all works fine when accessed through fast network (LAN over optic cable). Pesimistic locking is used.. But users which are in distant cities are connected through ADSL connection and VPN to this LAN. When exe is loaded, and db is queryed error message is following: [FireDAC][Phys][ODBC][Microsoft][ODBC Microsoft Access Driver] (unknown) is not valid path.. What seams to be a problem? All other users have no this issue inside faster network. Is ...

FireDac Update 1 on Delphi XE4
Ok, I was able to download FireDac on our Mac and at home, so it seems that I can't download update 1 on our regular computers. However, am I mistaken when I say that there is no way to install the new FireDac update into Delphi XE4? If you use the showide parameter, Delphi XE4 will not show up. Thanks > {quote:title=Phillip Woon wrote:}{quote} > Ok, I was able to download FireDac on our Mac and at home, so it seems that I can't download update 1 on our regular computers. > > However, am I mistaken when I say that there is no way to install the new FireDac upd...

MSSQL server update, insert, delete
Hi, I use a MSSQL server now for my project. I created my tables, now I searched a bit around but didn't found something that I needed. I would like to know wich C# codes I need to use to open my connection and how to update, insert, delete. Like I have a registration form where you need to fill in some stuff and when you click submit everything what you typed in those textboxes need to go in the good tables like phone in phone and etc. also I work with a ID (1,2,3,...) but that needs to be created automatically how I need to do all this, I have no clue.   Thanks in advan...

FireDac and MS SQL Server [Edit]
Hi, At first I was getting an error that it could not initialize the driver. I installed the native client from here to connect to a 2005 server: Rebooted, and adjusted the dll names to match the driver dll: sqlncli.dll Now I am getting this error when Testing the Connection in the Data Explorer in the IDE. How do I remedy this, or should I use another method than FireDac? --------------------------- Access violation at address 50588AF1 in module 'vcl190.bpl'. Read of address 00000004. -----------------...

Edit insert update Delete cmds
I have and oracle db and I am trying to write Edit insert update Delete cmds.  I ma using the ODBC to connect to the database.  When i use the  data srouce that i drag out in degsin mode i  make a select statement using the wizard looks like this SELECT [CLOCK_ID], [LAST_NAME], [FIRST_NAME], [WORK_PHONE], [HOME_PHONE], [CELL_PHONE], , [PRIMARY_DAY_PHONE], [PRIMARY_NIGHT_PHONE], [BLACKBERRY_NUMBER], But for some odd resson it does not like the brackets.  and i query the db fine. but update does not work UPDATE [EMPLOYEES] SET [LAST_NAME] = ?, [FIRST_NAME] =...

DataList with Nested Editable/Deletable Gridviews. Gridviews not updating parent on edit/delete.
<DataList "Groups">  <Item>      <DataBound Title in DataList>       <Gridview of Items in "Current Group from DataList">       <SqlDataSource for GridView> </Item></DataList><SqlDataSource for DataList> Everything displays fine, I can edit individual rows in the nested grids and I can insert Rows in the grids.But the edit/delete events will not trigger the DataList to rebind its contents.. so onEdit/onDelete in the gridviews I say "DataList.Dat...

SQL Server 2005 Insert, Delete, and Update
Everyone,Currently, I am a Coldfusion mx developer. I am learning dotnet using c#.  Question: What is best way to do insert, delete and update using dotnet? Please guide me with samples how to do database related activity. thanksajay   check these videos at a man a fish and you feed him for a day. Teach a man to fish and you feed him forever.  I already seen some of them , but which route I should take to do my task. thanks ...

Overriding apply updates on Firedac REST server
Hello, I'm working with XE7 and I am developing a datasnap REST application using Firedac. I need to override the way updates are applied by the server tier to the db. I decide to try to read changes an apply them manually by commands to control values and change applicability. Is it the right way to override the apply updates procedure? If yes how can I inspect the TFDJSONDeltas to retrieve changes? Thank you, Davide ...

Hi All, Database: Firebird 1.5 Server: TADConnection -> TADQuery -> TDatasetProvider Client: SQLConnection -> TDSProviderConnection -> TClientDataset I get "Remote Error: Access violation" when executing ApplyUpdates(-1), but I don't get error on my development Notebook. When I trace on server the error raise between BeforeApply and AfterApply. Any suggestion welcome. Thanks Jay Hello Jayanto This information is not sufficient. Could you provide the call stack of the place where exception is raised ? -- With best regards, Dmitry Arefiev ...

Adding, Deleting, inserting, updating / editing of data.
Hi people. It's nishani again> I know that the gridview control has the option of editing, deleting and updating records but it's never worked for me. Could anybody explain step by step how to actually add, delete and edit data in a database? The error I get when trying to use the gridview control's built in functions for updating or editing,deleting data is as below. What is up with this. Why have a control with a built in function if it doesn't work. Anyway I NEED help lke yesterday so please somebody come through for me. I'm not necessarily interested in solving...

Insert, Update, Delete and Edit in GridView by Code.
Hi all.. I have used GridView control to bind programmatically. It works perfectly while insertion. I have used Templated Fields into it. Each field is bind with Eval and Bind functions. My problem is when I want to edit a record, by clicking Edit link button each edit item displays well and binds well, But when Row_Updating event fires, It updates with old values. and not with new values of the controls..Now if i Use EnableViewState=false, I cant be edited.  whats the solution?Regards, Bhavin. Hi Try this thread Hi, BhavinBhatt,We can read the new values from the associated controls in the gridview row [EditItemTemplate control] and then update the database. Please take a look at the example below: //Gridview row updating event protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e) { GridViewRow row = GridView1.Rows[e.RowIndex]; //read the new values. string Name = ((TextBox)(row.Cells[1].Controls[0])).Text; string Age = ((TextBox)(row.Cells[2].Controls[0])).Text; bool Active = ((CheckBox)(row.Cells[3].Controls[0])).Checked; //update database UpadateNewData(Name, Age, Active); } //My method to update database public void UpadateNewData(string Name, string Age, bool Active) { //database update here }  Hope this helps. Otherwis...

FireDAC as DataSnap Server with Firebird Database XE5
Hi, I use Firebird Database with Firedac as Server for DataSnap and I don't see my alias on client side. For example, a query like this don't provide me the TEST Field. SELECT MU.*, MU.NAME!!' '!!MU.FIRSTNAME AS TEST FROM MYUSERS MU With dbexpress, it works well. Somebody notice this ? Hello Hugues > SELECT MU.*, MU.NAME!!' '!!MU.FIRSTNAME AS TEST FROM MYUSERS MU 1) What "!!" means there ? 2) Still if the SQL syntax is correct, then you should set ResourceOptions.MacroCreate and MacroExpand to False:

Firedac is - and will be
As I see the only way to have updated Firedac is to buy the XE5 IDE - despite the valid Firedac subsription. But - can this new version of Firedac be used in older IDE? Is it possible to use current sources and compile a working version of Firedac for Delphi XE (2, 3, 4) or FPC? Will it be possible in the future? Can someone from EMBT or Dmitry state it clear? That's not Dmitry's decision - ask Marco and I think it's not even his. In another thread a very polite and helpful person told me - download the installer and install with the key given in the maintenance por...

Cannot update/edit/delete/insert into my bound GridView or DetailsView
I have a problem which can be solved two different ways. I have a GridView that you can select a row and it will show the details in the DetailsView. I wanted to do create this page using an SQLDataSource object, but it would not let me connect to my SQL Server Express database for some strange reason. I tried using the same exact connection string that I ended up using to create a connection on page_load, but it did not work at all. If I could get this to work it would be great and would allow me to use the full functionality of the grid and details views. The database resides on this ...

Hover menu for insert edit, delete, update in a listview
I  have a hover menu working in a gridview for insert, edit, delete, update, but can't seem to get it to work in a listview. Here is my code. <asp:ListView ID="ListView1" runat="server" style="z-index: 5; left: 0px; top: 0px; position: absolute; height: auto; width: auto" DataKeyNames="ArtificialID" DataSourceID="sqlDataSource1"InsertItemPosition="LastItem" OnItemUpdating="ListView1_ItemUpdating" OnItemDataBound="ListView1_ItemDataBound" OnItemInserting="ListView1_ItemInserting"...

FireDAC suitable as data-access component in DataSnap server?
Hello Folks! In Delphi XE3, when FireDAC became available, I tested it as a possible replacement for +dbExpress+ on the app server side (DataSnap server). I discovered that the IProviderSupport methods of +TADDataSet+ implemented the method +PSUpdateRecord+ by opening the query again, positioning itself on the correct record, go into edit mode and apply the change. This is in stark contrast to +TCustomSQLDataSet.PSUpdateRecord+, which just returns False and forces the +TSQLResolver+ to generate the appropriate update SQL command in +TSQLResolver.InternalDoUpdate+. Obviously opening the da...

FireDAC replacement for DataSnap based TDataSet access [Edit]
Hello, I hope you can help us choosing the right strategy for integrating FireDAC. At the moment we have a client/server application in which we already replaced the data access to FireDAC. This is working really good and was easy to integrate. So we have: server: TDataSetProvider --> TADQuery --> TADConnection --> DB client: TClientDataSet --> TDSProviderConnection --> TSQLConnection Now I'd like to bring FireDAC to the client as well as we experienced a few problems with TClientDataSet. As the other parts of our business logic rely on DataSnap the easiest solut...

How to return multiple datasets with datasnap using FireDac? [Edit]
Hi again, i am working on a client/server software using DelphiXE4 and FireDac, i have a MySQL Database on a remote server, and i have a Datasnap Server (that uses a TCP Transporter) that communicate and exchange data between the Database and clients. I would like that the server returns multiple datasets to clients in only one request. I saw this samples on internet: *Server:* {code} function TServerMethods1.GetData(SQL: string): OleVariant; var cds: TClientDataSet; begin cds := TClientDataSet.Create(nil); try { setup 'cds' to connect to database } { pull data } Result := cds.Data; finally FreeAndNil(cds); end; end; function TServerMethods1.GetMultipleDataSets: OleVariant; begin Result := VarArrayCreate([0, 4], varVariant); Result[0] := GetData('select * from Table1'); Result[1] := GetData('select * from Table2'); Result[2] := GetData('select * from Table3'); Result[3] := GetData('select * from Table4'); Result[4] := GetData('select * from Table5'); end; {code} *Client:* {code} procedure X; var DataArray: OleVariant; begin try with ProxyMethods.TServerMethods1.Create(SQLConnection1.DBXConnection, True) do try DataArray := GetMultipleDataSets; finally Free; end; ClientDataSet1.Data := DataArray[0]; ClientDataSet2.Data := DataArray[1]; ClientDataSet3.Data := DataArray[2]; ClientDataSet4.Data :...

Updating multiple joined tables using FireDac [Edit]
What is the best way to update multiple joined tables using FireDac? Is there any example project or code for me to see? I am using Delphi XE5 Pro and am connecting to a SQL Server Express database using ODBC. I have two tables in a 1 to 1 relationship: Person and Student. Here is a simplified version of the SQL SELECT code: {code} SELECT p.PersonID, p.FirstName, p.LastName, p.Suffix, s.SchoolID, s.YearEnrolled FROM Person p INNER JOIN Student s ON p.PersonID = s.StudentID; {code} FireDac is unable to generate the necessary SQL to update changes. I get an error that says...

Web resources about - Insert, Update, Delete very slow with FireDAC DataSnap server [Edit] - embarcadero.delphi.firedac

Companies Directory - Job Fusion
D1 Locker D2L D4D Technologies DAA Deutsche Auftragsagentur Dabble Dabble DB Dabee Dabizmo DabKick Dabo Health Dacentec Dachis Group ...

Embarcadero Webinars
Advanced software tools for application developers and database professionals. Cross-platform solutions for database design, development and ...

David I - Head is in the cloud, feet planted firmly in the ground
... I’s (David Intersimone) Embarcadero blog about programming, languages, databases, history, and more. , and filed under Cloud Computing , DataSnap ...

RAD Studio 2015 Roadmap
Rad Studio, Delphi, C++Builder, and Appmethod 2015 Technology Roadmap from JT at EmbarcaderoTechnologies

FireMonkey Q&A
Questions and answers from a FireMonkey webinar

Contact Us - Ginktage
Home Privacy Policy Contact Us Home .NET Events Microsoft Office SQL Server Tools Gadgets Technology Tips&Tricks Contact Us Ginktage Learn , ...

News Briefs: December 15, 2008 - SD Times: Software Development News
AccuSoft creates an SDK for Web imaging software, while Artisan releases a free version of Artisan Studio. Also, Bredex, Embarcadero, Ilog and ...

Craig Stuntz’s Weblog : Site Map
Craig Stuntz’s Weblog F# • Compilers • Programming Languages • Functional Programming • Web Skip to content Home About Site Map Site Map Share ...

iBeacon Hack Makes It More Efficient To Wait Tables
Here at the 2014 TechCrunch Disrupt SF hackathon two-man hack team, Ray Ho and Mark Watson, showed off a simple but promising concept that combines ...

Te Waka o Delphi · Poll Archives
Keeping Delphi afloat in Aotearoa

Resources last updated: 3/10/2016 5:19:10 PM