'Serious' ADO Speed Techniques With Delphi - Part 1 ?Steve Burch - September 2001 Background Having the ADO based VCL components introduced into Delphi (starting with version 5) brought a solid solution to the need of incorporating this Microsoft technology into the Delphi developer? world. For those of you new to ADO, it stands for "ActiveX Data Objects" and it is the successor to the older RDO components, and is part of Microsoft's bigger picture of Distributed interNet Applications (DNA). Since Delphi 1 the BDE (Borland Database Engine) has been the backbone of data access for Delphi applications. In certain instances this solution has become dated, such as when accessing Microsoft SQL Server databases via MSSQL.DLL. This client/server driver ceased being updated in 1999 and Microsoft encouraged users to migrate to ADO. I believe that to use the ADO VCL components effectively it is necessary to have some understanding the core ADO objects that the VCL components "wrap". Borland has done a very good job making ADO a BDE-like technology, in that it has been incorporated into visual components that can be ?ropped?onto your form or datamodule, but, a good understanding of ADO and how Borland has wired them into the VCL is still vital in certain circumstances. I will not go into much detail on the ADO objects themselves, as the purpose of this article is how to "tweak" the VCL components to run faster. My recommendation to you is to read ADO specific books or articles. In particular, study the ADO Recordset and ADO Command objects with their properties and methods. The TADOQuery, TADOTable, and TADODataset VCL components all incorporate the ADO Recordset and ADO Command objects. The ADO Recordset? primary purpose is to return rows of data, and while the ADO Command object can produce a Recordset, it is primarily used to issue Update, Delete, or Insert SQL commands, which do not return data. The TADOQuery has a property, Recordset, which is of type _Recordset, which is in fact an ADO Recordset. A TADOQuery can be used to execute SQL that does not return a Recordset (such as running an update query), but I like to use TADOQuery for retrieving data and TADOCommands for executing SQL that does not return any data. There are a couple of reasons for this. The main point here is that the VCL ADO components discussed here rely on the ADO Recordset and/or ADO Command objects. My First Problem I noticed, while accessing some 25,000 rows of SQL Server data in a TADOQuery, that simply looping through the rows of the TADOQuery took 75 seconds, while performing NO work of any type within the loop. Not wanting to believe this, I accessed the Recordset directly, and looped through it in less than 1 second! In the business world, these types of perfomance traps can (and do) become "deal busters". Listing 1 // for 25,000 records this took 75 seconds, and is just not acceptable ADOQuery1.Active := True; while Not ADOQuery1.EOF do begin ADOQuery1.Next; end; Listing 2 // this took < 1 second ADOQuery1.Active := True; while Not ADOQuery1.Recordset.EOF do begin ADOQuery1.Recordset.Movenext; end; Note: when you manipulate the Recordset directly, as is done in Listing 2, the TADOQuery will not know what the new row position is unless you resynch, but as we will see this technique is not really necessary. So, it was not the Recordset's "fault", but rather the problem was somewhere in how the TADOQuery as being used. The Solution To make a long story short, after analyzing settings, searching Help, and wailing, I decided to read the ADODB.pas source code for the TADOQuery. If you have never used a programming tool other than Delphi (or if you didn't know that the source is provided), you really need to appreciate the fact that Borland makes this available as it can answer so many questions, and in this case translates directly into speed and customer satisfaction. In the source code I found this little snippet (it seems this code gets called for every row reposition): if ControlsDisabled then RecordNumber := -2 else RecordNumber := Recordset.AbsolutePosition; Well? Is ControlsDisabled True or False? In my case (since it is the default case) it was True. I learned a long time ago that any time "AbsolutePosition" for anything is referenced it was likely to be a speed bottleneck, so I decided that this was the potential speed problem. I found the TADOQuery method (which actually descends from TDataset) DisableControls, and changed my code to this: Listing 3 // this took 1 second, and no .Recordset manipulation! ADOQuery1.DisableControls; ADOQuery1.Active := True; while Not ADOQuery1.EOF do begin ADOQuery1.Next; end; Just make sure you call DisableControls BEFORE you open the dataset. However, no visual controls will be able to display data unless EnableControls is first called. Note: DisableControls seems to "stack", that is, if you make (by mistake) two successive calls to DisableControls you will need to make two calls to EnableControls before you see your data in any visual controls. Why would you do this? Many times a set of records is retrieved and looped through for processing -- to base calculations upon, feed other table updates, etc. There is no need to have the data visually displayed, therefore it is safe to first call DisableControls and benefit from the serious speed improvement. If you are doing large "batch" transactions, in my case we have a client that could be processing in excess of 700,000 records per day, speed is a must. This is an example of delving a bit directly into the ADO Recordset component, seeing the speed disparity, and after exhausting the possibilities of Property settings and Help, taking a look at the Delphi source code. My Second (and shorter) Problem At times I have used a TADOQuery to execute SQL that does not return data, such as running an Update SQL command. If you end up doing this rather than using a TADOCommand, please set Property ExecuteOptions.eoExecuteNoRecords to True first. If this is allowed to be False, a Recordset is built in the background which is "costly" and not required. You only need the Recordset when you are returning data. The effect of this setting is most noticeable (I have had improvements approaching ten-fold) when performing updates that apply to many rows, but really this setting should be set to True for any Update, Insert, or Delete SQL. Of course you can avoid this altogether by using a TADOCommand instead of a TADOQuery. Conclusion Having ADO VCL components is both necessary and exciting. ADO is the natural fit with Microsoft operating systems and SQL Server. It allows you to remove, potentially, all reference to the BDE in your code. Even though Borland has made this technology extremely easy to use, knowing how the ADO objects themselves behave can pay off in large dividends.