Maximize the performance of Delphi / C Builder / Interbase application
Summary of the General Assembly speech This article provides some suggestions and techniques to help readers to enhance the performance of Delphi / C Builder / Interbase system
This article comes from the speech of the Mer System Company Robert Schieck at the 12th Borland Development Conference. Robert Schieck is the Mer Systems of Mer Systems, Mer Systems, located in Canada, which provides custom C / S software development with training Robert is a Borland certified Delphi / C Builder / JBuilder faculty, is also a Teamb member CNE CERTIFIED NETWARE ENGINEER and Toronto The founder and former president of the Delphi user group. Robert graduated from the University of Toronto and a bachelor's degree in Mechanical Engineering. The MER system's site http://www.mers.com is the interbase list and newsgroup's server contains the world's largest public interbase information Note: Unciprome If any part of this article is reproduced in any way, the translator has received Borland to recognize the translation work and the right to use this article.
Introduction 1) In the database, you have to have more data in the database. 2) Use SQL Monitor to help you understand your front-end application to IB requests 3) The speed difference between BDE and direct access controls such as IBX 40% 4) Avoiding a long-term in an open state 5) Do not use large varchar6) Establishing front-end applications to use remote connection 7) Database should use 2KB or 4KB page size 8) only use GFIX to set up Your database cache 9) If the query includes the verb Like, do not use parameter 10) I don't use primary keys and foreign keys 11) PREUTINET and pre-pre-pre-pre-pre-pre-pre-accumulated preord will get the best performance 12) The machine running the IB server should be single Processor System 13) My Rule 14 About Left Outer Join 14) Avoiding Operations for Return All Records 15) For large systems, many users need to cache your lookup table Lookup Table to increase speed 16) Pursue speed When pursuing speed, turn off Async Writes but there are risk
Summary Introduction Delphi and BCB are a wonderful tool developer, even if there is no or only very little database experience, you can also develop database applications through drag controls and connect controls. It is easy to develop a small database system for unreportable developers, and it can work very well. Unfortunately, as the user increases, the size of the database is getting bigger and bigger, and developers need to understand how Delphi / BCB is interactive with IB to create a running system. This article will provide some suggestions and techniques to help your Delphi / BCB / IB system achieve better performance.
1) In the database, most developers who have to have a lot of data in front of the database are never doing this, what they do is to create the required metadata creation table and index, put six records in each table, then Just create an application. We will make mistakes. If there are only six records in each table, you can't know if there is a mistake during the development stage. Since there is no matter what method is used to extract six records from the database, it is always very fast. In turn, if there is a few records in your customer database, you find a customer in the development process for 15 seconds. The clock, then you immediately know that there must be a problem to correct it. This situation is a bit similar to eating, and then pay first after you pay, if you have enough data in your database before starting to develop, you will be able to find errors and problems during the development process. Or you can put only six records in the form, then find errors in practical applications, your application may be very busy. 2) Use SQL Monitor to help you understand your front-end application requests to IB. Taking people with fish is not allowed to fish, SQL Monitor is the fishing pole you develop by the database. It allows you to monitor the dialogue between the client and the server. SQL Monitor allows you to compare the application's changes affect your dialogue between your application and the server, you can also make you more different control gathering access to the server.
When using BDE SQL Monitor, I often use these options l Connect / Disconnect This option displays when connecting to the database, you can use it to check if you have multiple connections to connect to the database, or if the application is frequent Close the database connection. l Prepare Query Statement This option allows you to monitor the frequently extent of your query, if the application is constructed to be used multiple times while the query is ready, thereby reducing the traffic of the client to the server. l EXECUTE QUERY statement Monitoring SQL Query to Server End SQL Query L Statement Operation Monitoring The Record from the server Each fetch statement indicates that a row is retrieved from the server, a row, a record L transaction monitoring your application to confirm the data to the database in SQL Monitor Time to get better understand how Delphi / BCB interacts with IB interaction to construct highly optimized applications 3) One way to use only 40% of the speed difference between BDE and direct access controls such as IBX is to use direct Access controls such as IBX, overall, you will see performance by about 40%. In addition, some of the features inside the IB also have better utilization, but the speed and function are obtained, you sacrifice the transplantability. If you plan to work with IB and other SQL servers, use direct access controls will significantly increase your conversion cost. Remember SQL's Structured instead of standard Standard Even if your app uses BDE, you want to support Oracle, then you may have to write all SQL statements to get the performance of the Oracle environment. . 4) Avoiding a long time to open a business in an open state in the newsgroup, you often hear suggestions, don't make a business in an open state for a long time, the problem is that there is no exact time. How long is it? The implementation time of the transaction itself is not so important, as it depends on the content to be completed, consider two cases, you put a transaction in an open state, but nothing, put the transaction in an open day and then every minute Compared with the inquiry, the consequences of the latter will make the IB will not collect the garbage collection, the latter will also be the case. More serious is that it will make IB allocate more and more memory to track transactions, and this will greatly reduce performance. For example, a company opens the IB of the application server side in the morning, will use the memory of about 180-200 trillion, because the transaction is in an open state within one day, and the choice of queries are also running on the transaction, IB will use about 950 megabulary . Performance Differences are very obvious when the backup is made, if IB takes an hour, if you close the IB then restart IB to release all memory backup, just 6 minutes, so just open the transaction for a few minutes Will n't cause a big problem, but if you open a business all day, and there are many queries that have been running in this business. 5) Do not use large VARCHARINTERBASE internal storage char and varchar's way, it will have a complicated white to fill the length of the field when it seems that the data of the char type is returned to the program, and the VARCHAR type data does not have this filler, but Most people have not realized that they will be filled with the same way when they pass the char and varchar. We can imagine a VARCHAR (32000) field with value is 'a', then passed from the network to get the letter 'A' and 31,999 filling characters.
So when you design a database, you should remember that the fields of Char and varchar type are passed in the customer and server, if you want to use large variable length strings to consider using blob6 to establish a front-end application, always use Remote connection You can use two types of local and remote local connection strings such as Connect C: pathTomyDatabase.gdb which uses memory maps to complete the communication between the application and the server, it looks fast, but fake This type of connection is much faster than you use remote server connections. Remote connections such as Connect localhost: C: pathTomyDatabase.gdb or Connect MyServer: C: pathTomyDatabase.gdb Remote Connections Access IB through the network, which provides performance that can be desired by the C / S app. The high-speed local connection is easy to hide the serious performance / design problem, and these problems have floated the surface when you finally connect with the remote server. The newsgroup has such a message to deliver applications on the local connection, and the problem is that I use the local IB development app to run very well, now I am connecting it to the remote IB running, how is it? ? 7) Database should use 2KB or 4KB page size IB 5.X default page size is 1KB this is too small, the memory is very cheap, so the hard disk is also extended to 2KB or 4KB. By default, I use 4KB. For systems with large forms, I use 8KB, the page is bigger, the index depth is small, and the blob that can be saved on one page will be a little more record, all of which will improve performance, especially Is the index of a long table. To change the page size of the existing database, the only way to make it back up and then use a new page size to recover. 8) Use GFIX to set your database's cache space to set cache space on the IB server, there are many ways, the best way is based on the number of cache spaces based on each database without any GUI interface, you must hand modify Ibconfig file or with gfix command to gfix -buffers 10000 -user sysdba -paff, on the previous example, set myDatabase.gdb's cache space to 10,000 databases, that is, when connecting the database for the first time, IB will be in memory The 10,000 database pages are allocated as caches, while future user connections will not increase cache space. The size of the cache ranges from 0 to 64K, according to my experience from 10,000 caches to 20,000 caching will not have a big increase in performance, so I usually use 10,000
9) If the query includes the verb Like, do not use the parameter to use the verb like to use the verb Like, you are not so simple to assume that your LastName field is indexed. The following SQL statement will make the IB use index SELECT * from customer where lastname like SCH% '; however, the SQL will be very slow and will cause a lot of load on the IB server because it cannot use the index, select * from customer where lastname Like'% SCH%; IB implements the only way to achieve the above SQL statement is Table scan. That is, IB will read each record from the client table on the disk and then determine if its LastName field includes 'SCH'. The full table scan should be slowly avoided. The following query will cause full menu scanning Select * from customer where lastname like: Aparam; Since IB does not know that you want to send "SCH%" to parameters or "% SCH%", it will be optimized to the lowest compatibility level That is, full table scanning Select * from customer where lastname like Upper ('Something'); no matter what function you use, no matter what Ib is used, what Ib does not know in advance, so the query will also cause full menu SELECT * FROM Customer WHERE UPER (LASTNAME) LIKE '% SCH'; IB can only get the value of each LastName field through full table then can then determine what the result value of the Upper (LastName) function is generally a full mete scan very slowly Soon please use the "Like" your program to use "LIKE", you will be fast 10) I don't use the primary key and foreign key IB to declare primary keys and foreign keys in the form definition, although they are easy, I don't use them. For the primary key, I use the unique index so I can name this index. Once I add repeated values to the table, I get the index name instead of RDB $ primary, and the only one is Sex index can be shielded and reactivated, and the reconstruction index primary keys cannot be done. The situation of foreign keys is somewhat different, assuming that you have a form of a field called "address_state" you want this field acceptable data is one of 50 states, so you have created a table to States, you declare the foreign key when IB Create an index for the Address_State field, which works when you delete a row from the States table. IB can quickly check the "address_state" field and ensure that the value you want to delete in States is not appearing in the Address_State field. Performance problems appear on an index created for the "Address_State" field, if there is 1,000,000 records in your form, there are about 20,000 records in the "Address_State" field index, such an index is very Worse, if the IB optimizer encounters a lot of similar indexes. When a company encountered such a problem, there was a case where the report could not stop. After the check found that a field has 95,000 records of the value of 3, and there is 5,000 empty, and there is an overseas key on this field. The index IB, the optimizer discovers this index, which causes the optimizer to solve this problem in turn, the result is that the report / query takes 10 hours to complete. The solution is to cancel the foreign key and its index, and replace the optimizer with a trigger, in a few minutes, the report has come out.
11) Parameterize the query and pre-predetermined prepare will get the best performance for each SQL statement that sent to IB is ready, or you either you either you use the tool for preparing, preparing a query is a expensive process, especially It is connected via an Internet or slow connection. For example, a project is connected in a project, and the first result is waiting for 30 seconds, because you have to query, and each result will be used for only 4 seconds, if you use BDE, and you don't Prepare your SQL statement, BDE will prepare for you when you open tQuery, but unfortunately if you close tQuery, it will make the SQL statement back to preparation status, which loses the meaning of preparing. But if you prepare your tQuery (tquery.prepare) it will keep your preparation until you are explicitly canceled, or the content of SQL statements is changed. If you use IBX, IBX will automatically prepare for you and keep them in preparation, as long as you don't change SQL statement 12) The machine running the IB server should be a single processor system Interbase Superver Structure does not know how to use it correctly Multi-processor. If you have multiple processors in NT, the IB process will move from a processor to another, it seems that IB is using multiple processors but not, experience data indicates that the drift of the IB process will bring the most More than 30% performance decline, if your server is multiprocessor and you want to run the IB server, you can run IB as an application and close it closely with one processor, prevent IB processes from one processor drift from one processor Go to another.
13) About the rules of Left Outer Join, I never like to use Left Outer Join, they will be very slow, and IB will only use an index in the first Left Outer Join in the query, there are many ways to avoid Using LEFT OTER JOIN below is to discuss sample data SQL> select * from children; child_key name school_key ====================================================================================================================== ========================== 1 Robert Schieck 02 Jon Schieck 13 Diane Schieck 24 Megan Schieck 35 Robyn Schieck 46 Emma Schieck 5sql> Select * from school ; School_key School_name ============================================= 12 SIR Winston Churchill3 Oakridge Publici Public4 Lady Churchill Senior Public I have four rules of Left Outer Join and Share 1) Design, consider not Left Outer Join in our design a table (Children) contains children's information, and a field School_key as a foreign key, of course, did not declare in the form, the Schools table has two fields School_Key and School_Name, because children under the age of 5 do not have to go to school, so their record has a null value, and You must use a Left Outer Join to get all your child information and school name related to them. In my school form, there is such a record school_key 0school_name '' true school_name field is empty string, in the Children table The School_Key field is default, all children have a school, only the school name of the child under 5 years old.
If I want to see all the children's information and related schools, I can use an internal connection instead of connecting sql> select c.name, s.school_name from children c, schools s wherec.school_key = s.school_key; name school_name = ============================= ================================================================================================================================================= =========== Jon SchieckDiane Schieck Sir Winston ChurchillMegan Schieck Oakridges Public SchoolRobyn Schieck Lady Churchill Senior Public note Jon Schieck child does not attend school 2) the use of inter-related queries not all SQL servers can do so, I will not mention MS SQL Server 6.5, it can be one, but if there are two words in a query give an error answer, pay attention to the wrong answer rather than error messages continue to use the above example, then SQL can write SELECT C .Name, (select s.school_name from schools s where sschool_key = c.school_key) from children c; for each row, the Query is running SQL> Select C.Name, (Select S.School_name from school s whereres.school_key = c.school_key) from children c; name ==================================== ========================== Robert SchieckJon SchieckDiane Schieck Sir Winston ChurchillMegan Schieck Oakridges Public SchoolRobyn Schieck Lady Churchill Senior PublicEmma Schieck school name blank because these There is a School_Key, but can't find a corresponding record in the School table 3) The stored procedure returned to the record set using the stored procedure is very powerful here. To create a virtual Left Outer Joincreate Procedure Select_childschoolreturns (Name Varchar (30), School_Name Varchar (30)) asclare Variable School_Key Integer;
beginfor select name, school_key from children into: name,: school_keydobeginschool_name = null; select school_name from schools where school_key =: school_keyinto: school_name; suspend; endend output form such as SQL> select * from select_childschool; NAME SCHOOL_NAME ====== ============================================================================================================================================= ==== Robert SchieckJon SchieckDiane Schieck Sir Winston ChurchillMegan Schieck Oakridges Public SchoolRobyn Schieck Lady Churchill Senior PublicEmma Schieck4) using a left outer join and sometimes there is no way then use the left outer join, right SQL> select c.name, s.school_name from children c left join redss on c.school_key = s.school_key; name school_name ==================================== =========================== Robert SchieckJon SchieckDiane Schieck Sir Winston ChurchillMegan Schieck Oakridges Public SchoolRobyn Schieck Lady Churchill Senior PublicEmma Schieck
14) Avoid using the operation of returning all records to return all records that you have made some things in Delphi / BCB, so that your application returns all records from IB. For example, if you have a record of 100,000 people in the database, you will probably choose about 10,000 records, if you perform the above statement from a TQuery, tQuery is not Connect to what control, then only one record is returned from the query until you call TQuery's next method to return the next record. If TQuery is turned on and a DBGRID is connected, then there is no line in DBGRID to return the corresponding data, if your Grid is displayed 14 lines, then it only returns 14 data from the server. Once you scroll from Grid to display more data, it will return more data from the server. If you call tQuery.recordcount, then the BDE will get every row from the server regardless of TQuery and what controls. Once the last row is obtained, here is 10,000 lines, BDE will tell you the number of records obtained from the server. Get 10,000 records to take some time, and for TQuery uses filters to cause all records to return before, and then filter all records. The Locate method using the Locate will cause all records to return all records, and then locate the record pointer to the appropriate location. If you use SQL Monitor, you can monitor the data returned from the server, it is also easy to detect the occurrence of all operations 15) For large systems, many users need to cache your lookup table Lookup Table to increase the speed. With the increase in the number of users in the system, you need to reduce the load of the server, there is a way to cache your lookup table lookup Table. For example, there are 50 states in the United States and add a new state that is still far away. This is a table suitable for cache. ClientDataSet is a good control to cache the States table. If the States table is placed in the ClientDataSet, you no longer need to associate with the States table in your query on your IB server, and you can use the client's calculation field to simulate an association, Such a server load reduces more users.
16) Pursue speed When you close Async Writes but there is a risk default state in IB 5.x in NT, it is open, that is, once IB tells NT to write something, NT will not put it Enter the OS's cache must be written to disk. Defide the default to the UNIX platform is to turn off, that is, if IB tells the UNIX system to write something to disk, UNIX places it in the cache and writes it on the disk when the system is ready. So do you have any differences in performance? When the stored procedure is inserted into a table into a table, if the forced write will take 15 minutes, if the force is written, it is 45 seconds. Of course, there must be a loss of risk, and the speed is increased. If the IB server or operating system crashes when IB is written to the disk, the possibility of damage to the database is very large, and it will not be repaired. If you choose to turn off forced writing to improve performance, you must ensure that your computer has UPS and there are many backups available.
to sum up
I hope that the technology mentioned here will help you improve your performance of your Delphi / CBuilder / Interbase system.
