Inquiry optimization model based on middleware
Traditional customer / server mode is a double-layer structure, usually a personal computer to be used by client use (running client programs), and another server is used to store database systems in the background, and the application can connect directly to the client. There is no other logic in the middle. In the software development process of the MIS system, query data is an important part of how to help users quickly and effectively query data in the database is a very concern that software developers are very concerned. I have roughly summed up the following methods from the two MIS systems I have done.
1. Fixed structure: That is, the programmer customizes the client query program based on the customer's business, which is not versatility. Or the business logic is also present in the background database to be implemented in a trigger (TRIGGER). This approach has a lot of shortcomings, once the customer's business logic changes, will cause the application's modification and the modification of the background trigger, re-modify all program modules, compile, and connection workload. big. In addition, this structure will consume a large amount of resources of the client to consume a large amount of resources, a large amount of resources to the client, and the client is consumed.
2. Flexible structure: In addition to using fixed query commands, do some interfaces that allow system managers to modify the query command. That is, let system managers can write
The way the SQL statement, although this method can improve the quality of the query, but it is very difficult to talk to non-professional operators.
3. Based on middleware-based data query optimization model: This article is based on middleware and distributed systems, with this data query model with the Chengdu Kangtai Electric MIS system that has been developed as an example. I believe it provides users with more convenient And faster service.
1. What is middleware:
In order to solve the distributed heterogeneous problem, people have proposed the concept of middleware (MiddleWare). The middleware is a general service between the platform (hardware and operating system) and the application, as shown in Figure 3, these services have standard program interfaces and protocols. For different operating systems and hardware platforms, they can have a variety of implementations that meet the interfaces and protocol specifications. Maybe it is difficult to give a strict definition of middleware, and readers who want to know can go to http://www.huihoo.com/middleware/ to understand the relevant information.
2. The problem that the developer should pay attention to when doing query modules:
In general, customers only care about the data they need and how to query these data, and will not care about this query statement and generated. To give an example, a warehouse management system, including the storage situation, outbound conditions, and the situation. Customers only care about how to get a product of the number of products, the number of warehouses and the current amount of the current. And how this library management system is generated, and the SQL code and data sheets of the query statement are not interested. Taking the Kangtai Electric MIS system as an example, it uses the Client / Server structure. According to my understanding of the MIS system, as long as the number of clients in the application is within 200 users, the Client / Server structure is in the same LAN. It is enough to implement the MIS system. However, it is well known that there is a lot of problems with the Client / Server structure. Some of the problems mentioned in front of the front fixing structure, resulting in it affects the performance efficiency of the database.
Plus the rise in Internet / intranet applications in recent years, there is a major impact on the operation of the company, and the company's business executive of Kangtai requires the development of product queries to all potential users on Internet / Intranet, so the MIS system must be able to make Customers use browsers to query all product information, in fact, the system structure has entered a distributed structure. Because it has a web server. The old MIS system is necessarily written in new technologies when the Internet / Intranet is available. This will inevitably increase the work of developers and improve costs. So I think it is just because of the way B / S's inquiry mode, why do I not use the idea of the current popular middleware to develop a new query model? 3. The model of the query system:
I learned some of their commonality by analyzing some queries, and compares
Highlight because they are not discrete. For example, all the statements about the warehouse query constitute the warehouse management query system, all the inquiry statements of all financial revenues constitute the income and expenditure query system, I will classify the statement of queries, structn_class = {Table1, Table2 ....tablen}; datawarehouse = {struct 1_class, struct 2_class ... structn_class}. And I am going to create our query system with Microsoft DCOM technology on StructN_Class.
Its main technical feature is to completely separate the query statement and the system, not the original Client / Server structure, I divide the client into 2 parts, client and selectcom-server .. Regardless of whether the user is in the client side or in the browser interface. Submit the query criteria and category entered to selectcom-server. SELECTCOM-Server. Automatically generate query statements according to the query criteria and category input by the user. Then submit them to the database, and finally the database runs the query statement, and then handed the query data to selectcom-server. SELECTCOM-Server. Returned the data to the client side, so that the query is completed.
I think it has the following benefits:
Due to selectcom-server. And Client are separated, the query criterion used by a user can be shared by other users. This makes the overhead of the network and the database greatly reduced. The range of queries can also be reduced according to the query conditions already used. Since I have already added deep level permissions on Selectcom-Server. It can reject some core data queries and improve data security.
Also alleviate the dependence of code, because this distributed system, considering safety, but also to improve the Client / Server structure, but also improve the B / S structure. Furthermore, it is also advantageous to maintain the maintenance of the system management, and he wants to query the query statement on SelectCom-Server.
4 implementation:
The problem of limited to space, I won't write the code implemented by the DCOM, because this distributed system interface programming is too much, just talk about the composition of the query statement and its optimization, this is the most Main.
But there are many things involved, such as: reasonable use of indexing, avoiding or simplifying sorting, eliminating sequential access to large table row data, avoiding associated subqueries, replacing non-order access with sorting.
Let's talk about the principle of composition, the generation basis of Subsentence is generated by which relationship table belongs to each field. When the same field belongs to 2 relationship tables, then it is the field of connection.
For example, there are 3 relational tables:
Table1 (CNUMBER, NAME, GUIGE), TABLE2 (CNUMBER, XINGHAO, Guige), table3 (score, guige) then generated a member n = table1.guige = table2.guige.
And for how to implement a user-used query can be used by other users as described below: The key is to establish a temporary file, (I also add it to it).). Taking the Chengdu Kangtai Electric MIS system developed as an example, a subset of the table is sorted and created a temporary table, sometimes can accelerate the query. It helps to avoid multiple sorting operations and simplify the work of optimizer in other ways. For example: SELECT cust.name, rcvbles.balance, ...... other columns FROM cust, rcvbles WHERE cust.customer_id = rcvlbes.customer_id AND rcvblls.balance> 0 AND cust.postcode> "98000" ORDER BY cust.name If the query to Multiple times, more than once, you can find all unpaid customers in a temporary file and sort by the customer's name: Select Cust.Name, Rcvbles.balance, ... Other Column from Cust, RCVBLES WHERE cust.customer_id = rcvlbes.customer_id aND rcvblls.balance> 0 oRDER BY cust.name INTO TEMP cust_with_balance then the following manner in the temporary table query: SELECT * FROM cust_with_balance WHERE postcode> main line than "98000" in the temporary table There are fewer columns in the table, and the physical order is the desired order, reducing disk I / O, so query workload can be greatly reduced. In order to accelerate the generation of query statements and search for the temporary cache table we use. When searching, first search for temporary cache tables, and search the statement to generate a table. However, it is important to note that the primary table will be modified after being created. When data is frequently modified in the primary table, be careful not to lose data. But I have not completely solved this problem now, and interested readers can think about it.
5. The storage structure of the query statement:
A query statement can be broken down into one or many query subsencence, and a query subsencence can also break down into multiple smaller query subsencence. In fact, they are a tree structure, so each new query statement is the new parent node. Our temporary cache table is to store this structure.
6. Conclude
This model operates well in actual use, and is simple and practical for users, and basically satisfies the requirements of the user's distributed system. Although the user is inconsistent with the requirements of the query, using the intermediate piece-based query optimization model has a friendly human-computer interaction interface, the query criteria can be automatically flexible, and the practicality is added. For developers, the workload is reduced, and the second development of the code is also reduced while improving the security of the data.
