I've a performance related issue.
I've one big table A which is been used in one query having join with another big table B. Table A has no Primary key but a clustered index defined on four columns (composite) and two more nonclustered indexes. Table B also has no primary key but a clustered index with five columns (again composite) and three non clustered indexes. All of the indexes are on more than one column.
While running the query, it taking too much time and execution plan shows 23% of sort (execution plan shows its in one of the case statements with "in (select ")) on one column of table A which is part of clustered index. How should I resolve it ? Secondly, what is the significance of PK and clustered index separately ? Table A comprises of 35% of index seek and Table B shows 18% of index seek.
The query comprises of joins between these two tables and inserting in another table. Lots of columns in select statement and also few case statements with "exists (select" in them.