SQLTeam.com | Weblogs | Forums

How to write dynamic statement with way do it run quickly?

Transact-SQL
,
#1

I work on SQL server 2012 I face Issue I implement dynamic SQL it very slow

so I need to enhance statement below to work quickly .

DECLARE @SQL NVARCHAR(MAX)
	select @SQL =CONCAT('  
 SELECT *  Into #NewTable
FROM #SplitNumberAndUnitsFinal
PIVOT(max(Value) FOR DKFeatureName IN ('+@Columns+')) AS PVTTable 	  
',
N'  Select ''Variant ID'' as ''[Variant ID]'',''PART_ID'' as ''PART_ID'' ,''Vendor ID'' as ''Vendor ID'',''Manufacturer'' as ''Manufacturer'',''Digi-Key Part No.'' as ''Digi-Key Part No.'' ,''Mfr Part No.'' as ''Mfr Part No.'' ,''Description'' as ''Description'',''Category'' as ''Category'',''Family'' as ''Family'',''Obsolete'' as ''Obsolete'' ,''Non-Stock'' as ''Non-Stock'' ,	''Part Status'' as ''Part Status'',''PartNumber'' as ''Z2 Part number'' , ''CompanyName'' as ''Z2 Manufacturer'' ,   ' +@Header + ' 
union all
select [Variant Number],PART_ID,[Vendor ID],Manufacturer,[Digi-Key Part No.] ,[Mfr Part No.],[Description],Category,Family ,Obsolete ,[Non-Stock] ,	[Part_Status], PartNumber , CompanyName  ,   ' +@Columns + '  from  #NewTable
	

   ')

so please any advice for code above to enhance it to work quickly ?

so are there are any thing wrong you see on code above ?

#2

How many rows does #SplitNumberAndUnitsFinal have?
How is it populated and how do you know the slowness is in the sql you posted? What do the columns for it look like

#3

5 rows

#4

Answer the other questions also

#5

How much data are we talking about !!!

Millions of rows !! makes a difference to simple queries also ..

:slight_smile:

#6

it is not million of rows it is only small count of rows but it take too much time

small count i mean is 5 records or rows

the purpose from question I need to enhance query to work best

so can you give me key points how to rewrite query below to give me best practice

DECLARE @SQL NVARCHAR(MAX)
	select @SQL =CONCAT('  
 SELECT *  Into #NewTable
FROM #SplitNumberAndUnitsFinal
PIVOT(max(Value) FOR DKFeatureName IN ('+@Columns+')) AS PVTTable 	  
',
N'  Select ''Variant ID'' as ''[Variant ID]'',''PART_ID'' as ''PART_ID'' ,''Vendor ID'' as ''Vendor ID'',''Manufacturer'' as ''Manufacturer'',''Digi-Key Part No.'' as ''Digi-Key Part No.'' ,''Mfr Part No.'' as ''Mfr Part No.'' ,''Description'' as ''Description'',''Category'' as ''Category'',''Family'' as ''Family'',''Obsolete'' as ''Obsolete'' ,''Non-Stock'' as ''Non-Stock'' ,	''Part Status'' as ''Part Status'',''PartNumber'' as ''Z2 Part number'' , ''CompanyName'' as ''Z2 Manufacturer'' ,   ' +@Header + ' 
union all
select [Variant Number],PART_ID,[Vendor ID],Manufacturer,[Digi-Key Part No.] ,[Mfr Part No.],[Description],Category,Family ,Obsolete ,[Non-Stock] ,	[Part_Status], PartNumber , CompanyName  ,   ' +@Columns + '  from  #NewTable
	

   ')
exec (@SQL)
#7

So only 5 rows in SplitNumberAndUnitsFinal?

#8

hi

please see this below link ..

it may help !!