CHROMIUM

Welcome to CHROMIUM's very own blog!

Optimization of Query/SP/DB

Database Performance Tip # 9

Do not use “select *…” or “insert into dbo.Table select…” (without explicit column list). Explicit column lists reduce disk I/O (no need to read system tables to identify columns), and are more maintainable when columns are added or re-positioned If a cursor is unavoidable, use a simple WHILE loop instead, to loop through the table, […]

Select Composite key colums from same table in VIEWs

While creating views keep it mind that columns of composite key should be select from a same table otherwise views will perform very slow. Recently while doing WFS GAC optimization we found that the root cause of slow problem was a view in which a composite key columns was selected from different tables. After updating […]

Ensure fields index exist when Joining

The query should be carefully written and getting sure that on joining fields index must exist otherwise serious performance issue can raise as data volume will increase. The recent example of such case is found in MBFSJ. The dayend was executing in 3 hours and after monitoring the process below query found that was consuming […]

Optimise database deployement

Regarding NFS data storage architecture following points are important to get maximum system performance. Database server must be recommended with multiple physical hard disks or SAN/NAS with RAID or multiple logical units. NFS data files should be deployed on multiple disks as much as possible. Index and Data Files should be on separate disks. Database […]

‘Where Clause’ may kill application performance

Regarding query optimization it is observed that in where clause below mentioned cases are often happened. Due to which existing index on filter column is not used by database engine and table is full scan which kills performance. So try to avoid these cases while writing a query. During Optimization of different NFS projects it […]

Avoid functions returning tables

In queries which returns large data set don’t use table return type function in main query join part. It contribute in increasing of query execution time. The solution is to make the temporary table from the function and created the index on joining fields with main query. This improve the significant time of main query […]

Avoid VIEWs in queries returning large data sets

In queries which returns large data set, avoid using complicated views in main query join part. It contribute in increasing of query execution time. The solution is to make the temporary table from the complicated views and created the index on joining fields with main query. This improve the significant time of main query execution. […]

Non functional history triggers can be disabled while executing dayend to optimize the process

During day end optimization for MBAFC we disabled history triggers on contract table to optimize day end procedure “SP_dayend_upd_od_days”. This change reduced the 5 minutes of procedure execution time. This post was viewed (17) times.

Cursor impact on Performance

Cursor usage should be avoided in procedures, functions and triggers. But if in some situations can’t be avoided then there is one important tip is for maximum performance. This is that cursor definition query should be kept simple as possible. The multiple tables joins and filters should not be used in cursor query. In below […]

Welcome to CHROMIUM !

Welcome to Techiest blog everwhich is there right for you. This blog is aimed to keep all techies like you, to get to know each other more, talk, share, stay in touch and when you leave, you leave your glow. All techies out there are welcome to join this blog and feel free to post. […]