Posts Tagged ‘TSQL’

Performance Tuning – Good For Developers – Part 2

December 27th, 2010 1 comment

Scene 1 — If you are suppossed to search range of values then use clustered index because in range search values will be at consecutive location in sorted order after clustered index creation. So effort for engine would be reduced.

Scene 2 — Use stored procedure instead of individual queries because
– It reduces the network traffic
– Query plan got created once for same query which could be re-used instead of creating again and again.
– Stored procedure can be invoked by passing different parameters instead of sending newly created query again and again.

Scene 3 – Make sure there are no table scan on large tables.

Categories: ASE, Developement Tags: ,

Performance Tuning – Good For Developers – Part 1

December 23rd, 2010 No comments

Performance Tuning – Part 1

1. Indexes improve select performance.

2. While writing queries few things must be considered – Use operators =,>,<,>=,<=,like,between.

3. Replace the BETWEEN with >= and <= operators because BETWEEN in turn converted to mentioned oprators. So with we can reduce one step.

4. Don’t use functions in the WHERE clause. For e.g. select col1 from Table1 where upper(col1) = col2 — If we are having index created on columns col1 and col2 then because of function query engine will not use the index in select operation.

5. Similarly don’t use the mathematical expression in WHERE clause. For e.g. select col1 from Table1 where (col1 * 3) = col2

6. One of the most common pitfall is mismatch in datatype on both side of join. for e.g. select col1 from table1 where col1=col2 — if datatype of col1 and col2 are different then it reduces the performance of query.

Categories: ASE, Developement Tags: ,