How to improve performance

VINOD 2015-05-14 11:01:24

Hi
i attached a query plan . This query some times run well , sometimes take more time to execute.
Some temp tables are used to store data based upon conditions.
Please suggest how to improve performance.

Kin 2015-05-14 14:17:50
You have to work on getting rid of table scans and keylookups.
SQLkiwi 2015-05-23 09:11:42
Hard to say based on an anonymized estimates-only plan. Provide a post-execution analysis file as a minimum please.
VINOD 2015-05-23 09:41:10
Sorry sir i can not upload full execution plan from office , it contains sensitive information and could be track on servers . Please provide following info that will be use-full :-

  1. if a independent query is used in join and returning for e.g 500 rows , should i use a temp table to store that data and use it in join ?
  2. one table is showing keylookup in excution plan used in dynamic query of 300 lines of code, What should be the best way to add non cluster and covering index instead of looking each column and joins ?

3 .e.g

Select
b.value
from table1 a
INNER JOIN table2 b on a.userID = b.userID
where b.groupCode = 'cc'

This is a sample query on which table1 is having clustered index on UserID and table2 have non clustered index on columns.

This type of joins we see oftenly and face issue on joined tables like table1.

On table1 clustered index scan runs , even if we add non clustered index it does not convert to seek. Another table uses seek because that columns are used in joins, select and where cluase.

Please suggest .

SQLkiwi 2015-05-23 10:34:24
Just asking for a post-execution plan, i.e. one with actual numbers. You can keep it anonymized of course.