1

I'm fetching data from two tables by joining (on [icn] & [report_date] ). The intent is to have the data as pivot on [responsible team] column from UHCG_INVENTORY_RAW table. So as you can see I'm getting the unique teams first and then applying it on the main query to get the pivot data.

Right now the total data stands around 25 million, and that's why the query is taking around 35 sec to fetch the records. I'm planning to have non-clustered indexing on [icn] columns on both the tables. Kindly suggest the best way to achieve it.

declare @icn nvarchar(15) ='7869080726',
@cols nvarchar(max),
@stmt nvarchar(max);

select @cols = isnull(@cols + ', ', '') + '[' + T.team + ']' from (select 
distinct [responsible team] team from UHCG_INVENTORY_RAW where ICN=@icn) as 
T
select @stmt = '
select * from (select (case when gmi.client_type=''UHA Portal'' then 
gmi.queue_name  when gmi.client_type=''Auto Batch'' then batch_comment end) 
as comments,gmi.client_type, convert(varchar(10),[report date],101)[Report 
Date], [responsible team] team,(case when [responsible team]!='''' then 1 
else 0 end) value
from UHCG_INVENTORY_RAW uiw left join global_market_inventory_report gmi on 
gmi.claim_icn_number=uiw.ICN and gmi.report_date=uiw.[report date] where 
uiw.ICN='''+@icn+''') T
pivot 
(
  max(value)
  for T.team in (' + @cols + ')
 ) as P order by cast([Report Date] as date) desc'

 exec sp_executesql  @stmt = @stmt
iklakh.shekh
  • 101
  • 1
  • 1
  • 7
  • 1
    To create a nonclustered index you can use the [`CREATE INDEX`](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql?view=sql-server-2017) statement. Probably something like `CREATE NONCLUSTERED INDEX IX_UHCG_INVENTORY_RAW__ICN ON dbo.UHCG_INVENTORY_RAW (ICN) INCLUDE ([responsible team], [report date])`. You may need to include further non-key columns so that the index also assists the main query. – GarethD Aug 22 '19 at 16:13
  • Thanks, I was thinking same. Right now I'm having non clustered index only on the icn only, and the performance has been improved a lot (now fetching the records in a sec) – iklakh.shekh Aug 22 '19 at 16:20
  • 1
    As an aside, I would not recommend using `SELECT @Cols = isnull(@cols, '') + ...` to generate your column list. The behaviour of [String concatenation using variable assignment is undefined](https://stackoverflow.com/a/15163136/1048425) and is not guaranteed to work. You should use `STRING_AGG` if you are using 2017 or later, or `FOR XML PATH` (it is discussed in the linked answer) if you are using earlier versions. – GarethD Aug 22 '19 at 16:47

0 Answers0