How can I optimize this query?
Since I am new to SQL server, Kindly help me to optimize this query?
That said, the most obvious thing is that most of the tables used in the query have no indexes. The one table that does have an index seems to have a clustered index only. If you can share the query text and CREATE TABLE definitions, we might be able to offer some specific advice.
As it is, you should probably create at least a unique clustered index on the heap tables. Heaps are not necessarily bad in all circumstances, but it is almost certainly not a good choice here. Aside from other concerns, heaps do not routinely release space due to deletions, meaning your heap tables may be much larger than the current contents warrant.
Please share the query statement and table definitions if you can (edit your question rather than commenting).