Code Project

Link Unit

Wednesday, September 03, 2014

Performance Improvement

 
Update Statistics: 

Let's look at following query.

SELECT *
FROM customer
WHERE city = 'Pune'
 AND phone = '2020666666' -- dummy Number 
 
Above query contain two fields in the "WHERE" clause and suppose there are two indexes defined, each containing one field. One very important notion to remember is that the optimizer can only use ONE index per table.

Therefore, it has to make a decision as to which index to use. Ideally, optimizer should pick index created on phone but in absence of updated statistics it can pick cityIndex.

This decision can be informed one if statistics are up to date.


Avoid foreign key constraints

Foreign keys constraints ensure data integrity at the cost of performance. Therefore, if performance is your primary goal you can push the data integrity rules to your application layer. A good example of a database design that avoids foreign key constraints is the System tables in most databases. Every major RDBMS has a set of tables known as system tables. These tables contain meta data information about user databases. Although there are relationships among these tables, there is no foreign key relationship. This is because the client, in this case the database itself, enforces these rules.

Monday, September 01, 2014

Find Range of continous values

There are many ways to find out gaps & range, but this standard gaps-and-island solution that I found on net is coolest one. Basic idea of this solution is to group by (value minus row_number), since that is invariant within a consecutive sequence. The start and end dates are just the MIN() and MAX() of the group.

The value could be int or date.


DECLARE @data TABLE (i INT)

INSERT INTO @data
VALUES (1)
 ,(2)
 ,(4)
 ,(5)
 ,(10)
 ,(11)
 ,(15)
 ,(16)
 ,(17)
 ,(18)
 ,(19)
 ,(20)
 ,(21);

WITH tab
AS (
 SELECT i d
  ,ROW_NUMBER() OVER (
   ORDER BY i
   ) r
 FROM @data
 )
SELECT min(d)
 ,max(d)
FROM tab
GROUP BY d - r
 
Output: 
 
 

Hope it helps