Tuning options for the query

ALISH 2016-11-14 15:39:10

Any suggestion to rewrite the query for better performance.Currently taking 2 min and all the required indexes are in place and the table contains only 10K records
Vlady Oselsky 2016-11-14 20:08:37
Not exactly sure what you are trying to achieve here. Does the table not have a primary key that you have to join it back to itself by a COALESCE function? Also what is the data type of EXTERNAL_CLIENT_CONTRACT_ID and EXTERNAL_CODE_2? I see CONVERT_IMPLICIT operations on COALESCE operation which usually nullify gains that indexes provide.
ALISH 2016-11-15 14:26:27
The current query is taking 2 min to complete and the table contains on 10k records . so trying to rewrite to improve the perf. Yes table do have PK , but that's on diff columns. For this req we need the info like this .
the columns are varchar type
SQLkiwi 2016-11-16 04:30:40
There's not enough detail in the question to rewrite the query to do what you need, but from a purely plan analysis perspective (the point of this site) the following index would at least avoid the sort:

CREATE INDEX i 
ON dbo.T_PXREC_SUMGEN_OTC_POSITION
    (JPM_INTERNAL_ASSET ASC, EXTERNAL_CLIENT_CONTRACT_ID ASC, EXTERNAL_CODE_2 ASC)
INCLUDE
    (EXTERNAL_CODE_1, PRODUCT_TYPE);

The real problem with the query is the number of times you are computing the string list (once per row) and the use of COALESCE as Vlady mentioned in a comment. If you can provide table definitions, a clear statement of the problem, some sample data and expected output, you could get help writing an optimal query at Database Administrators Stack Exchange.