Some of the techniques and methods may require special treatment different, depending on the database you use.
For example, performance improvements can be done from the administrative database like configu ration file and self-service or security updatean pack, which of course each database has its own uniqueness and techniques.
Then, with consideration of compatibility, is there any optimization that can be done in general?
There are a set of methods and techniques commonly applied when working with RDBMS (Relational Database Management System), may not be all that you can implement because the environment is highly dependent on each application, but at least you can use it as a guide and reference to establish a system best accordance with the conditions encountered.
Optimization via the SQL command also plays a no less important. The core of the SQL itself is a command to perform retrieval (retrieval), the addition (insertion), modification (updating), and elimination (deletion) of data, coupled with support functions of administration and database management.
SQL itself is a standard programming language or to the RDBMS. Although called a language, maybe a little awkward when we refer to the SQL programming language, more familiar when the sounds are programming C, Visual Basic, Java, Delphi, and so on.
The languages of the latter included in the imperative programming, is a language easily shaped core instructions. Meanwhile, the SQL included in declarative programming, which is more form sentences or statements.
In its development, SQL divided again in a variety of extensions that gave birth to a variety of titles such as SQL / PSM (Persistent Stored Modules) is a standard ANSI / ISO, T-SQL (Transact-SQL) of Microsoft and Sybase, PL / SQL (PL is stands for Procedural Language) which is used by Oracle, which later developed more into PL / pgsql is used PostgreSQL.
Quite confusing, is not it? Fortunately the concept and basic elements such as the SQL statement, query, expression, or continue to apply the general clause on every SQL extension.
We both ends meet theoretical discussion here, here are some simple optimizations that you can do, to at least fix or prevent problems, and improve the performance of your RDBMS.
Index
The first optimization that we discussed was the problem index, of course you know that the index can increase the search speed in the desired record. However, you must be quite selective in choosing a field that needs to index, since not all fields need it.
Proverbial read books, or scan the search process will read from beginning to end of the page. In the on-field index, search index scan is done, or read on the index, not directly on the table in question.
Meanwhile, direct searches made by reading the record after record on the table is called a table scan.
Is index scan is always faster than the table scan? It did not also, table scan can be run faster when accessing the record in a relatively small amount, or when the application does require reading the entire table.
Instead of accessing a great record in a particular field, index scan can reduce the reading operation I / O so that not infrequently result in faster performance.
As a rule, you can specify the index on the field that is often used, for example, fields that are often accessed by the WHERE clause, JOIN, ORDER BY, GROUP BY.
Defining Data Types
Data type is a problem tricky. In terms of capacity, the type of data that are too small or otherwise too large for a field, can cause a ticking time bomb that caused the problem in line with the rapid increase of data each day.
Determining the proper data type requires precision and good analysis. For example, we need to know when we use the data type char or varchar.
Both contain characters, the difference between char provides fixed storage size (fi xed-length), while VARCHAR provides storage size in accordance with the contents of data (variable-length).
General rule is to use the char data type if fi Eld is intended for the data with a consistent length. For example zip code, month consisting of two digits (01 to 12), and so on. Int used if the data is stored to a variable length, or use varchar (max) when its size exceeds 8000 bytes.
Do not Allow Allow Null
If possible, reduce the use of fields that allow null values. Instead, you can provide default values in the field.
Null values sometimes overlap in interpretation can lead programmers and programming logic errors. In addition, eating a null-byte field that adds an additional burden on queries that access it.
Easy Read Query
Because SQL is a declarative language, it is not surprising if you make a query form nan sentence length, although perhaps only for the purpose of displaying a single field!
Do not let your query hard to read and understand, unless you're going to make anyone dizzy seeing your query. Long queries written in a clear 1baris modification would complicate cation and understanding, would be far better if you write a query in a format that is easily digested.
The selection of large and small letters can also facilitate the reading, for example by consistently write SQL keywords in capital letters, and add comments if necessary.
Avoid SELECT *
Select keywords that may be the most frequently used, because the optimization of the SELECT command is likely to improve overall application performance. \
SELECT * is used to query all the fields contained in a table, but if you only want to process a certain field, then you should write down the field that wants to access it, so that your query becomes SELECT field1, field2, field3 and so on (never mind the source code which grew longer!). This will reduce the network traffic load and lock on the table, especially if the table has many fields and large.
Limit the ORDER BY
Use the ORDER BY to sort the data function, it has the burden of the consequences of adding the query, because it will add one more process, ie sort process.
Therefore, use ORDER BY only if absolutely needed by your application.
Or if possible, you can perform sorting on the client side and not on the server side. For example, by accommodating the first data grid components and perform sorting on the grid according to user needs.
Subquery Or JOIN
Sometimes a single instruction can be written in the form of subquery or JOIN command, you are advised to prioritize the use of JOIN because in the general case will result in faster performance.
However, the query processing is an art, it is always possible subquery to work faster than JOIN, for example in conditions of use
JOIN is too much, or the logic of the query that has not been optimal.
0 komentar