db2 sql performance tuning

One of the important issues to consider when designing a new or analyzing an existing system is the design of the application. Even if the database is well designed and optimized, application design is not appropriate or a database that can cause performance problems. Practice has shown that if there is a design problem with the application, modifying these issues can improve the performance of the applicat

rownumObviously, this writing will result in reading the table first, then sorting, and then taking the first 5 records, the plan is as follows:If we write like this, the semantics are the same, but it saves a lot of reading and sorting costs:Select/*+ Index (T1,IDX1_T1) */* from T1 where rownumThe execution plan for this SQL is as follows:As you can see, the SQL does not follow the hint instructions and s

1. Check the database space usage to see which tables are consuming larger disk spaceExecute the following statement:SelectO.name, SUM (p.reserved_page_count) asReserved_page_count, SUM (p.used_page_count) asUsed_page_count, SUM ( CaseWhen (p.index_id2) Then (p.in_row_data_page_count+P.lob_used_page_count+p.row_overflow_used_page_count)Elsep.lob_used_page_count+P.row_overflow_used_page_count End) asDataPages, SUM ( CaseWhen (p.index_id2) then Row_countElse 0end) asrowcounts fromsys.dm_db_partiti

DISTINCTdatabase_id,object_idFrom Sys.dm_db_missing_index_details) as Ddmid on ddmid.database_id = ddios.database_idand ddmid.object_id = ddios.object_idWHERE Ddios.page_lock_wait_in_ms > 0 and object_name (ddios.[ OBJECT_ID]) like ' Pos_transmst 'ORDER by Ddios.page_lock_wait_count DESC;/*******************************************************************************************--page IO Latch Wait *******************************************************************************************/ SEL

not in with not existsIn a subquery, the NOT IN clause performs an internal sort and merge, in either case, not in is the least effective because it performs a full table traversal of the table in the subquery, and in order to avoid using not, it can be written as an outer join (Outer Joins) or not EXISTSLow efficiencySELECT *FROM dbo. OrdersWHERE id_p>0 and Id_p not in (SELECT IdFROM dbo. Persons)EfficientSELECT *From OrdersWHERE id_p >0 and not EXISTS (SELECT IdFrom Persons WHERE persons.id =

, one is not perfect function also dare to take Out (2000) The second functional architecture of the outstanding design makes the function has a strong continuity and Scalability (2005 2000 of the function to retain and improve). The optimization tool is simple to use 1: Use SQL Server Porfiler (Event Viewer) to record all the steps of the operation database in the business system and save it as a working file. 2: Open sql2005 's database Engine

Brief introductionWhen it comes to ensuring that enterprise applications built with IBM DB2 (DB2 certified DB2 Training) ®universal Database "(DB2 UDB) and borland® tools (such as Delphi, C++builder, or Kylix) have the best performance, programmers The ability of the

-oriented applications, typically for data entry and retrieval transactions in many industries, including banking, Aviation, mail order, supermarkets and manufacturing. Typically, OLTP workloads include many short transactions that run concurrently. Today's online transaction processing increasingly requires support across networks and transactions that may include multiple companies. As a result, new OLTP software uses client/server processing and proxy software, which allows transactions to ru

the user visit slow, off time and more normal. Operating System performance: typically the middleware server (was) system is normal, CPU and IO consumption will not last more than 50%, the system running process will not have a continuous wait. The database server is very busy, the CPU occupies more than 50%, tend to reach 90% or so, IO occupation may not be high. Judging from the system level, the performance