17 issues</title> <p>SQL performance can be optimized by following good practice |
17 issues</title> <p>SQL performance can be optimized by following good practice |
18 in the design of databases, formulation of SQL queries, use of indexes and |
18 in the design of databases, formulation of SQL queries, use of indexes and |
19 other techniques. </p> <p id="GUID-828B8B44-8CC3-5CCA-A4C9-BF90353B2002"><b> Transient tables</b> </p> <p>Transient |
19 other techniques. </p> <p id="GUID-828B8B44-8CC3-5CCA-A4C9-BF90353B2002"><b> Transient tables</b> </p> <p>Transient |
20 tables are created by the database engine to compute intermediate results. </p> <p>Transient |
20 tables are created by the database engine to compute intermediate results. </p> <p>Transient |
21 tables are stored on disk by default, which may result in unexpected I/O calls |
21 tables are stored on disk by default, which may result in unexpected I/O calls |
22 that will probably have a negative impact on database performance. </p> <p id="GUID-0F43181C-2C32-5B5D-8AA2-4588D3DAF171-GENID-1-8-1-21-1-1-6-1-1-9-1-7-1-5-1-4-1-3-2-6"><b>Prevent datafile corruption</b> </p> <p>Datafile |
22 that will probably have a negative impact on database performance. </p> <p id="GUID-0F43181C-2C32-5B5D-8AA2-4588D3DAF171-GENID-1-10-1-22-1-1-6-1-1-9-1-7-1-5-1-4-1-3-2-6"><b>Prevent datafile corruption</b> </p> <p>Datafile |
23 corruption is corruption of the file containing a database with invalid data. </p> <p>Datafile |
23 corruption is corruption of the file containing a database with invalid data. </p> <p>Datafile |
24 corruption can occur after a system crash or loss of power. SQLite maintains |
24 corruption can occur after a system crash or loss of power. SQLite maintains |
25 files called rollback journals from which the last valid state of a database |
25 files called rollback journals from which the last valid state of a database |
26 can be reconstructed. However, if a rollback journal is removed or renamed |
26 can be reconstructed. However, if a rollback journal is removed or renamed |
27 during recovery from a crash, for instance in the course of application recovery |
27 during recovery from a crash, for instance in the course of application recovery |
28 the database will remain corrupted. </p> <p id="GUID-0F43181C-2C32-5B5D-8AA2-4588D3DAF171-GENID-1-8-1-21-1-1-6-1-1-9-1-7-1-5-1-4-1-3-2-9"><b>SQL index tips</b> </p> <p>Indexes |
28 the database will remain corrupted. </p> <p id="GUID-0F43181C-2C32-5B5D-8AA2-4588D3DAF171-GENID-1-10-1-22-1-1-6-1-1-9-1-7-1-5-1-4-1-3-2-9"><b>SQL index tips</b> </p> <p>Indexes |
29 are added either manually or automatically to columns of a data table to speed |
29 are added either manually or automatically to columns of a data table to speed |
30 up SELECT operations. </p> <p>Indexes greatly speed up the performance of |
30 up SELECT operations. </p> <p>Indexes greatly speed up the performance of |
31 SELECT statements but can impact on insertions, updates and deletions and |
31 SELECT statements but can impact on insertions, updates and deletions and |
32 memory usage. It is not efficient to index all columns regardless of their |
32 memory usage. It is not efficient to index all columns regardless of their |
33 purpose and under certain circumstances indexes decrease performance. They |
33 purpose and under certain circumstances indexes decrease performance. They |