How to improve performance

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.

avatar image By VINOD 1 asked May 14, 2015 at 11:01 AM
more ▼
(comments are locked)
avatar image Kin May 14, 2015 at 02:17 PM

You have to work on getting rid of table scans and keylookups.

avatar image SQLkiwi ♦ May 23, 2015 at 09:11 AM

Hard to say based on an anonymized estimates-only plan. Provide a post-execution analysis file as a minimum please.

avatar image VINOD May 23, 2015 at 09:41 AM

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 .

avatar image SQLkiwi ♦ May 23, 2015 at 10:34 AM

Just asking for a post-execution plan, i.e. one with actual numbers. You can keep it anonymized of course.

10|10000 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question



asked: May 14, 2015 at 11:01 AM

Seen: 354 times

Last Updated: May 23, 2015 at 10:34 AM