CODEWITHSUNDEEP
sqlsql-servert-sql
Thej
Thej

Reputation: 375

Creating non clustered Index for multi table joins

I have written a query by joining multiple tables and with multiple column where condition. As we have large amount of data I need to create index on all these columns. So that query should use this index for better performance. Let me know how to create it for below scenario ? 

SELECT 
  A.NAME,
  B.SAL,
  C.DATE1 
FROM 
  A 
  INNER JOIN B ON A.ID = B.ID 
  INNER JOIN C ON B.ID1 = C.ID1
WHERE 
  A.STATUS1 = '0' 
  AND A.TOTALAMT <> 0 
  AND A.FLAG = 'N' 
  AND B.SAL > 100 
  AND C.DATE1 NOT IN('2008-08-08', '2009-09-09')

Upvotes: 0

Views: 1972

Answers (2)

KumarHarsh
KumarHarsh

Reputation: 5094

I am tying to say same thing what @Ozren has already written.But i always verify my where condition about which I have written .

Primary Key

  Table A=ID
  Table B=ID1
  Table C=ID1
  Verify uncessary where condition like 
  i)  when sal>100 then TOTALAMT will always >0 (is it so?)
  ii) what is A.STATUS1 and A.FLAG ?(like when Flag='N' then Status is always='0' 
      you can put such validation while insert And avoid extra where condition.

Non CI

Table A (STATUS1, TOTALAMT, FLAG)
Table B (SAL)
Table C (DATE1)
  1. Analyze all your proc to check which amonth thee columns are very frequently use in where condition.
  2. If FOR example Flag='N' is very often use then you can use Filtered Index on this column
  3. Also ensure proper data type like Flag is char(1) and not varchar etc.

Upvotes: 0

OzrenTkalcecKrznaric
OzrenTkalcecKrznaric

Reputation: 5646

Rule of thumb here would be to include fields in your join clause and sargs. There is more to it (e.g. clustering, selectivity, covering, including...) but you can get even sub-optimal results that satisfy.

Try creating these indexes:

  • Table A (ID)
  • Table B (ID, ID1)
  • Table C (ID1)

...run and test your application and its database access performance.

If you encounter problems, you may find that something like this perform better:

  • Table A (ID, STATUS1, TOTALAMT, FLAG)
  • Table B (ID, ID1, SAL)
  • Table C (ID1, DATE1)

Upvotes: 1

Related Questions