firebird database performance comparison

filtering out unwanted records before group by. The following two queries return the same result but the second one is significantly faster.The code is as follows:Low efficiency:SELECT JOB, AVG (SAL)From EMP GROUP job has job = ' president ' and AVG (SAL) >xxxEfficient:SELECT JOB, AVG (SAL)From EMPWHERE JOB = ' President 'OR job = ' MANAGER ' GROUP JOB having and AVG (SAL) >xxx7, usually, if the statement can avoid the use of subqueries, as far as possible without sub-query.Because the overhead

. You can look at the following table: MyISAM InnoDB The composition of the difference Each myisam is stored on disk as three files. The file name is the table name and the extension is the file type.. frm file storage table definition;. MyD (MYData) The extension of the data file;. The extension of the Myi (myindex) index file. A disk-based resource is a InnoDB tablespace data file and its log files, and the size of the InnoDB table is limited to the s

necessary, it is best to put these fields separately, there is an additional table, on the one hand to increase the efficiency of the main table query, on the other hand, when necessary to the additional table for this big data query.Use the int type to store the time, using From_unixtime () and Unix_timestamp () two functions to convert.such as From_unixtime (1443922330, '%y-%m-%d '); Get a formatted: year-month-dayUnix_timestamp (' 2015-10-4 '); Get a time stampUse the bigint type to store IP

Label:Original address: http://www.cnblogs.com/sydeveloper/archive/2013/04/03/2992881.html First, the Database design optimization 1. Do not use cursors. Using cursors not only takes up memory, but also locks tables in incredible ways, so that all the performance optimizations that DBAs can do are equal to not being done. Each fetch in the cursor is equal to the execution of a select. 2. Create an appropria

First, the question of the proposedIn the early stage of application system development, due to less development database data, the query SQL statement, complex view of the writing of the performance of the SQL statement is not good or bad, but if the application system submitted to the actual application, as the data in the database increases, The response speed

Optimize your goals1. Reduce the number of IOIo is always the most vulnerable to the database, which is determined by the responsibility of the database, most of the database operations more than 90% of the time is occupied by IO operations, reduce the number of IO is the first priority in SQL optimization, of course, is the most obvious optimization means.2. Red

First, performance detection and bottleneck analysis 1 common commands for performance detection Show status Display status information, reference: Mysql Show status Command detailed Show Processlist View current SQL execution, including execution status, whether to lock the table, etc., reference: Mysql show processlist Command detailed Show variables Display system variables, reference: Mysql Show

Optimization objectives 1, reduce the number of IO Io is always the easiest place to bottleneck the database, which is determined by the responsibility of the database, most of the database operations over 90% of the time is occupied by IO operations, reducing IO times is the first priority in SQL optimization, of course, is the most obvious optimization method

-clustered Index. So when the table gets bigger, this extensibility is pretty bad. If you put the function on the right side of the expression, SQL Server can execute the seek operation. --Results in a Index Seek SELECT * from Sales.SalesOrderHeader WHERE creditcardid = CAST (' 1347 ' as INT) GO Summarize Through today's blog, I think you have realized that you should not apply the function directly on the indexed column, or SQL Server will scan your entire index instead of the seek operation.

the 10W.Y axis that represents the time spent per insertion, in seconds.Blue Line: Insert; The reason behind the stability is that the computer does not run other programs. Therefore, the computer working condition has certain influence to the Postgre efficiency.Pink: Copy;Yellow Line: copy+unloggedAlthough the index was built, and the data in the table has been added, there is no effect on the subsequent insertion of data performance, which is what

emp where deptno = ' + ' and ename like ' smith% '; You can use the Decode function to get the same results efficiently. Select COUNT (Decode (deptno, ' + ', ' x ', null)) D20_count, count (Decode (deptno, ' + ', ' x ', null)) D30_count, SUM (Decode (deptno, ' A ', Sal, null)) D20_sal, sum (decode (deptno, +, Sal, null)) D30_sal from emp ' X ' represents any one field. Similarly, the Decode function can also be used in the group BY and ORDER BY clauses. Integrated simple, non-associative

index, and if the condition is like '%abc ', MySQL will not use the index.ü In an order by operation, MySQL uses the index only if the sort condition is not a query condition expression. However, in queries involving multiple data tables, even if an index is available, those indexes have little effect in accelerating the order by operation.ü If a data column contains many duplicate values, it will not be very effective even if you set up an index for it. For example, if a data column contains t

also in; Solution, or add a field, but this calculation is real-time, a comment, the integration will change, here should be used only stored procedures, every night to calculate the situation in the answer table plus a field, average, updated, similar to the first interface; =============================================================================================================== ====== In fact, SQL optimization those queries, online those methods, what query results required fields Ah,