CN101149738A - Method for utilizing fixed length field for optimizing iteration inquiry - Google Patents
Method for utilizing fixed length field for optimizing iteration inquiry Download PDFInfo
- Publication number
- CN101149738A CN101149738A CNA2007101106323A CN200710110632A CN101149738A CN 101149738 A CN101149738 A CN 101149738A CN A2007101106323 A CNA2007101106323 A CN A2007101106323A CN 200710110632 A CN200710110632 A CN 200710110632A CN 101149738 A CN101149738 A CN 101149738A
- Authority
- CN
- China
- Prior art keywords
- major key
- iterator
- inquiry
- key field
- value
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Granted
Links
Images
Landscapes
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention discloses a method using fixed-length fields to optimize iterative inquiry, including: set up a fixed-length major key field in database; choose the values of all the condition-matched recorded major key fields and save in a disk file according to specific requirements; set up iterative device and batch inquire the database through iterative device, construct the SQL sentence through read the values of major key fields in disk file during inquiry; after the completion of iterative inquiry, delete the iterative device and the value saved in disk file. The invention optimizes the present widely used iterative inquiry method, solve problems that when large amount of data inquiries can not obtain high inquiry speed and occupies large amounts of EMS memory problem and so on.
Description
Technical field
The present invention relates to the application of using database (DBMS) to carry out data management, particularly large-scale relational database management system, sybase for example, oracle, mssql or the like.
Background technology
Inquiry is operation the most used in the database, so for a database application system, what the user was concerned about most is exactly the search efficiency problem.In telecommunication network management system, in order to satisfy the mode that user's big data quantity query demand has adopted iterative query.
The basic thought of iterative query is that a very large result set is returned in batches, avoids the disposable overabundance of data that obtains to cause internal memory difficult treatment even system crash.Iterator is a kind of data structure of using in the iterative query process, has wherein preserved qualified total record strip number, the record strip number that at every turn obtains, the number of times of having inquired about and residue inquiry times or the like.
The concrete scheme of iterative query is as follows:
1) iterator is set, and all qualified records are extracted puts into a temporary table or internal memory;
When 2) inquiring about, calculate the reference position and the end position of this inquiry according to the record strip number of the number of times of having inquired about and each inquiry at every turn;
3) if leave in the temporary table, open temporary table at every turn when then inquiring about and navigate to the reference position of this inquiry, then one by one reading and recording until end position; If leave in the internal memory, the record that then directly taking out this inquiry needs gets final product;
4) upgrade iterator and check and remain inquiry times in the iterator, if be 0, then remove iterator, query script finishes, otherwise beginning inquiry next time.
In conjunction with shown in Figure 1, key step comprises:
There is certain defective in above-mentioned query scheme, if qualified record is placed in the temporary table, then each inquiry all will reopen record set and carry out the reference position that several times NextRow operation navigates to this inquiry, this obviously causes very big influence to search efficiency, and also is doubled and redoubled with the increase query time of iterations; If be placed in the internal memory, though can save this part time overhead, when the data query amount is big, can take a large amount of memory headrooms, even may cause internal memory to overflow.
Summary of the invention
Technical matters solved by the invention is to provide a kind of method of utilizing fixed length field for optimizing iteration inquiry, to optimize present widely used iterative query method, solve the user and when carrying out the big data quantity inquiry, can't obtain inquiry velocity faster, and can expend problem such as a large amount of internal memories.
In order to address the above problem, the invention provides a kind of method of utilizing fixed length field for optimizing iteration inquiry, it is characterized in that, may further comprise the steps:
(1) major key field of a fixed length is set in database table;
(2) according to specific requirement from database, take out all qualified records major key field value and be kept in the disk file;
(3) iterator is set, and database table is inquired about in batches, come constructing SQL statement by the value of the major key field in the reading disk file in the query script by iterator;
(4) after iterative query is finished, delete described iterator and the value that is kept in the disk file.
Method of the present invention, wherein, described step (2) is:
The value of from database, taking out the major key field of all qualified records according to the specific requirement constructing SQL statement of network management system, and be kept in the disk file according to binary mode.
Wherein, described step (3) comprising:
(31) iterator is set, and count N by the record strip that iterator obtains each inquiry, calculate the home record numbering of this inquiry, major key field according to described fixed length, described home record numbering directly navigates to the position of the value of corresponding major key field in the disk file, and order reads the value of N major key field;
(32) use the value constructing SQL statement of the major key field get access to;
(33) carry out the good SQL statement of structure, obtain the record set of this inquiry, and upgrade iterator;
(34) repeating step (31) to (33) is inquired about in batches and is obtained record, and the residue iterations is 0 in iterator.
Method recited above, wherein, described SQL statement, where clause wherein adopts the mode of the title in (major key 1, major key 2... major key N) of described major key field to construct.
Wherein, described step (4) comprising:
(41) after iterative query is finished, delete described iterator;
That (42) creates in deletion step (2) is kept at the value of the major key field in the disk file with binary mode.
Method of the present invention, wherein, database table is the history alarm table described in the step (1).
Wherein, described scale-of-two is the scale-of-two of file mode.
Method of the present invention, wherein, iterator described in the step (3) is digit group type iterator or chain phenotype iterator.
The present invention compares with present iterative query method and has the following advantages:
1) record of this batch needs is only obtained in each inquiry, avoids unnecessary NextRow operation, and inquiry velocity is fast;
2) adopt the fixed length major key field, document alignment speed is fast;
3) only Major key is kept in the disk file, saves program internal memory.
Simultaneously, the present invention does not introduce the otherness of various large databases, can satisfy the query demand in the various large database application systems.
Description of drawings
Fig. 1 is the iterative query method process flow diagram of prior art;
Fig. 2 is the described a kind of method flow diagram that utilizes fixed length field for optimizing iteration inquiry of the embodiment of the invention;
Fig. 3 is the method flow diagram that the history alarm table in the described telecommunication network management system of the embodiment of the invention utilizes fixed length field for optimizing iteration inquiry.
Embodiment
The objective of the invention is to introduce a kind of method of utilizing fixed length field for optimizing iteration inquiry, optimize present widely used iterative query method, make the user when carrying out the big data quantity inquiry, can obtain inquiry velocity faster, and can not expend a large amount of internal memories.Below embodiment is described in detail, but not as a limitation of the invention.
As shown in Figure 2, iterative query method operation steps of the present invention is as follows:
The iterator pond of an overall situation should be defined in the described system of said method, array or chained list can be.
When needing to use iterator, if array is just got an idle node and each component of initialization from the global iterative pond.If chained list, then create an iterator node and be inserted in the global iterative device chained list
Deletion is during iterator, if system uses is array, each component of iterator clear 0 of this use got final product.
If what use is chained list, then from chained list, delete this iterator node.
Described method wherein is provided with iterator in the described step 203 of method, and uses iterator to inquire about in batches, comprises following step:
3.1) record strip that obtains each inquiry by iterator counts N, and calculate the home record numbering of this inquiry.Because adopted the major key field of fixed length, so can directly navigate to the position of corresponding major key in the file according to the home record numbering, order reads N Major key;
3.2) using the Major key structure sql statement that gets access to, the where clause of sql statement adopts the mode of major key title in (major key 1, major key 2... major key N) to construct;
3.3) carry out the good sql statement of structure, obtain the record set of this inquiry, and upgrade iterator;
3.4) repeating step 3.1) to 3.3) obtaining record in batches, the residue iterations is 0 in iterator.
Described method, wherein iterative query is finished in the described step 204 of method, and deletion iterator and the binary file of preserving Major key comprise following step:
4.1) from global iterative device Buffer Pool, search and delete the iterator node of this use according to iterator id;
4.2) deletion step 2) and in the binary file of the preservation Major key created.
Following embodiment is with the common table in the telecommunication network management system---and " history alarm " is example, describes the step of the iterative query after the present invention optimizes in detail in conjunction with Fig. 3.
1, create a history alarm table HistoryAlarm, comprise an id field in the table, and on this field, set up index or with this field as major key.
2, from database, obtain all qualified records and leaving in the disk file.
2.1) according to the where condition of the concrete application requirements structure of network management system query history alarm table, construct the id information that qualified record inquired about in the sql statement by the where condition then.
2.2) the id information that checks out in the step 1) is write in the file according to binary mode, because the length of id is certain, so each value takies identical byte number in binary file.Adopt the FILE mode to open binary file in the specific implementation, can be slow a lot of because if adopt the fstream mode to open the binary file writing speed.
3, iterator is set, and uses iterator to inquire about in batches
3.1) from iterator, obtain the record number N of this inquiry and calculate the starting position that obtains this inquiry;
3.2) open the binary file of preserving id information with read-only mode;
3.3) according to step 3.1) and in length gauge of this inquiry starting position and the major key field that obtain calculate this and inquire about required major key reference position hereof;
3.4) according to step 3.3) the file reference position calculated location binary file is to specifying id, order reads N id value then;
3.5) use step 3.4) id that gets access to is constructed as follows the sql statement:
select*from?HistoryAlarm?where?id?in(id_1，id_2，id_3...id_N)
3.6) record that result set obtains this inquiry opened in good sql statement to use structure;
3.7) obtain the history alarm data according to above-mentioned steps in batches, the residue inquiry times is 0 in iterator.
4, according to iterator id number deletion iterator.
5, the binary file of history alarm table Major key is preserved in deletion.
According to iterative query method of the present invention, be applied to the inquiry of telecommunication network management system history alarm.Compare with common iteration obtain manner, efficient improves 2 times in 200,000 records of inquiry, can improve tens times when data volume reaches more than 1,000,000, and efficient improved obvious more when data volume was big more.
Certainly; the present invention also can have other various embodiments; under the situation that does not deviate from spirit of the present invention and essence thereof; those of ordinary skill in the art can make various corresponding changes and distortion according to the present invention, but these corresponding changes and distortion all should belong to the protection domain of the appended claim of the present invention.
Claims (8)
1. a method of utilizing fixed length field for optimizing iteration inquiry is characterized in that, may further comprise the steps:
(1) major key field of a fixed length is set in database table;
(2) according to specific requirement from database, take out all qualified records major key field value and be kept in the disk file;
(3) iterator is set, and database table is inquired about in batches, come constructing SQL statement by the value of the major key field in the reading disk file in the query script by iterator;
(4) after iterative query is finished, delete described iterator and the value that is kept in the disk file.
2. the method for claim 1 is characterized in that, described step (2) is:
The value of from database, taking out the major key field of all qualified records according to the specific requirement constructing SQL statement of network management system, and be kept in the disk file according to binary mode.
3. method as claimed in claim 2 is characterized in that, described step (3) comprising:
(31) iterator is set, and count N by the record strip that iterator obtains each inquiry, calculate the home record numbering of this inquiry, major key field according to described fixed length, described home record numbering directly navigates to the position of the value of corresponding major key field in the disk file, and order reads the value of N major key field;
(32) use the value constructing SQL statement of the major key field get access to;
(33) carry out the good SQL statement of structure, obtain the record set of this inquiry, and upgrade iterator;
(34) repeating step (31) to (33) is inquired about in batches and is obtained record, and the residue iterations is 0 in iterator.
4. as claim 2 or 3 described methods, it is characterized in that, described SQL statement, where clause wherein adopts title in (major key 1, the major key 2 of described major key field ... major key N) mode is constructed.
5. method as claimed in claim 3 is characterized in that, described step (4) comprising:
(41) after iterative query is finished, delete described iterator;
That (42) creates in deletion step (2) is kept at the value of the major key field in the disk file with binary mode.
6. the method for claim 1 is characterized in that, database table is the history alarm table described in the step (1).
7. method as claimed in claim 2 is characterized in that, described scale-of-two is the scale-of-two of file mode.
8. the method for claim 1 is characterized in that, iterator described in the step (3) is digit group type iterator or chain phenotype iterator.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN2007101106323A CN101149738B (en) | 2007-06-06 | 2007-06-06 | Method for utilizing fixed length field for optimizing iteration inquiry |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN2007101106323A CN101149738B (en) | 2007-06-06 | 2007-06-06 | Method for utilizing fixed length field for optimizing iteration inquiry |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN101149738A true CN101149738A (en) | 2008-03-26 |
| CN101149738B CN101149738B (en) | 2011-09-21 |
Family
ID=39250267
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN2007101106323A Expired - Fee Related CN101149738B (en) | 2007-06-06 | 2007-06-06 | Method for utilizing fixed length field for optimizing iteration inquiry |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN101149738B (en) |
Cited By (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102158534A (en) * | 2011-02-09 | 2011-08-17 | 中兴通讯股份有限公司 | Inquiry method and device |
| CN109783535A (en) * | 2018-12-26 | 2019-05-21 | 航天恒星科技有限公司 | Transmitted data on network searching system based on ElasticSearch and Hbase technology |
| CN110209798A (en) * | 2017-12-22 | 2019-09-06 | 北京奇虎科技有限公司 | A kind of method for exhibiting data and device of redis database |
| CN112463837A (en) * | 2020-12-17 | 2021-03-09 | 四川长虹电器股份有限公司 | Relational database data storage query method |
| CN112948420A (en) * | 2021-03-05 | 2021-06-11 | 浪潮云信息技术股份公司 | Method for realizing SQL Server increment synchronization |
Family Cites Families (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US7940844B2 (en) * | 2002-06-18 | 2011-05-10 | Qualcomm Incorporated | Video encoding and decoding techniques |
| CN100386759C (en) * | 2003-04-15 | 2008-05-07 | 李琳山 | Chinese information retrieve method based on speech |
| US7565343B2 (en) * | 2004-03-31 | 2009-07-21 | Ipt Corporation | Search apparatus and search management method for fixed-length data |
| CN100407200C (en) * | 2005-10-26 | 2008-07-30 | 华为技术有限公司 | Correlation inquiry system and its method |
-
2007
- 2007-06-06 CN CN2007101106323A patent/CN101149738B/en not_active Expired - Fee Related
Cited By (8)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102158534A (en) * | 2011-02-09 | 2011-08-17 | 中兴通讯股份有限公司 | Inquiry method and device |
| WO2012106970A1 (en) * | 2011-02-09 | 2012-08-16 | 中兴通讯股份有限公司 | Method and device for querying |
| CN102158534B (en) * | 2011-02-09 | 2015-04-01 | 中兴通讯股份有限公司 | Inquiry method and device |
| CN110209798A (en) * | 2017-12-22 | 2019-09-06 | 北京奇虎科技有限公司 | A kind of method for exhibiting data and device of redis database |
| CN110209798B (en) * | 2017-12-22 | 2024-05-10 | 北京奇虎科技有限公司 | Data display method and device of redis database |
| CN109783535A (en) * | 2018-12-26 | 2019-05-21 | 航天恒星科技有限公司 | Transmitted data on network searching system based on ElasticSearch and Hbase technology |
| CN112463837A (en) * | 2020-12-17 | 2021-03-09 | 四川长虹电器股份有限公司 | Relational database data storage query method |
| CN112948420A (en) * | 2021-03-05 | 2021-06-11 | 浪潮云信息技术股份公司 | Method for realizing SQL Server increment synchronization |
Also Published As
| Publication number | Publication date |
|---|---|
| CN101149738B (en) | 2011-09-21 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US8055609B2 (en) | Efficient update methods for large volume data updates in data warehouses | |
| CN101149738B (en) | Method for utilizing fixed length field for optimizing iteration inquiry | |
| CN110109910A (en) | Data processing method and system, electronic equipment and computer readable storage medium | |
| CN109299154B (en) | Big data storage system and method | |
| US20130086091A1 (en) | Methods and apparatus for organizing data in a database | |
| CN101256561A (en) | Method, apparatus and system for storing and accessing database data | |
| CN107807932B (en) | Hierarchical data management method and system based on path enumeration | |
| CN108875077B (en) | Column storage method and device of database, server and storage medium | |
| WO2018205151A1 (en) | Data updating method and storage device | |
| US7870110B2 (en) | Method and system for generating a transaction-bound sequence of records in a relational database table | |
| CN110096509A (en) | Realize that historical data draws the system and method for storage of linked list modeling processing under big data environment | |
| CN101963993B (en) | Method for fast searching database sheet table record | |
| CN102750393A (en) | Composite index structure and searching method based on same | |
| CN101833511A (en) | Data management method, device and system | |
| CN101639851A (en) | Method for storing and querying data and devices therefor | |
| CN110442571A (en) | A kind of data processing method, device and computer storage medium | |
| CN104408128A (en) | Read optimization method for asynchronously updating indexes based on B+ tree | |
| CN104331460A (en) | Hbase-based data read-write operation method and system | |
| CN110515897B (en) | Method and system for optimizing reading performance of LSM storage system | |
| CN112463837B (en) | Relational database data storage query method | |
| CN101799803B (en) | Method, module and system for processing information | |
| CN115455040A (en) | Method, device, equipment and medium for improving database SQL query efficiency | |
| CN103150408B (en) | Real-time data base finds data base the System and method for accessed according to calling the roll | |
| CN103544157B (en) | The storage method and equipment of a kind of data | |
| CN114297227B (en) | Time sequence database construction method, query method and time sequence database |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| C06 | Publication | ||
| PB01 | Publication | ||
| C10 | Entry into substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| C14 | Grant of patent or utility model | ||
| GR01 | Patent grant | ||
| TR01 | Transfer of patent right |
Effective date of registration: 20180427 Address after: California, USA Patentee after: Global innovation polymerization LLC Address before: 518057 Department of law, Zhongxing building, South hi tech Industrial Park, Nanshan District hi tech Industrial Park, Guangdong, Shenzhen Patentee before: ZTE Corp. |
|
| TR01 | Transfer of patent right | ||
| CF01 | Termination of patent right due to non-payment of annual fee |
Granted publication date: 20110921 |
|
| CF01 | Termination of patent right due to non-payment of annual fee |