asked
Can anyone help me fine tune this Query
CREATE TABLE #TMPLOCALE(key_val NVARCHAR(255),value NVARCHAR(255),locale NVARCHAR(2000),source_value NVARCHAR(255),module NVARCHAR(255)) DECLARE @key NVARCHAR(255) DECLARE @value NVARCHAR(255) DECLARE @source_value NVARCHAR(255) DECLARE @module NVARCHAR(255) DECLARE C1 CURSOR LOCAL FAST_FORWARD FOR WITH cte AS ( SELECT key_value,value,ROW_NUMBER() OVER(PARTITION BY key_value ORDER BY VALUE DESC) slno,source_value,module FROM (SELECT DISTINCT key_value,CASE WHEN locale = 'en-us' THEN CAST(text_value AS NVARCHAR(2000)) ELSE NULL END AS VALUE,source_value,module FROM localization l1 (NOLOCK) WHERE l1.product = 'TC' AND l1.locale IN (SELECT DISTINCT REPLACE(iso_code,'_','-') FROM lang (NOLOCK) WHERE iso_code LIKE '%[_]%' AND iso_code NOT IN ('en_ca','en_in','en_za', 'en_au') UNION ALL SELECT 'ar' ) -- and l1.key_value = 'com.ceb.shl.shlonline.model.MessageFeeds.message.7' )tbl ) SELECT key_value,value,source_value,module FROM cte WHERE slno = 1 OPEN C1 FETCH NEXT FROM C1 INTO @key,@value,@source_value,@module WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO #TMPLOCALE(key_val,value,locale,source_value,module) select @key,@value, STUFF(( select ', ' + locale FROM(SELECT DISTINCT locale FROM localization (NOLOCK) WHERE locale IN (SELECT DISTINCT REPLACE(iso_code,'_','-') FROM lang (NOLOCK) WHERE iso_code LIKE '%[_]%' AND iso_code NOT IN ('en_ca','en_in','en_za', 'en_au') UNION ALL SELECT 'ar') AND product ='TC' EXCEPT SELECT locale FROM localization (NOLOCK) WHERE locale IN (SELECT DISTINCT REPLACE(iso_code,'_','-') FROM lang (NOLOCK) WHERE iso_code LIKE '%[_]%' AND iso_code NOT IN ('en_ca','en_in','en_za', 'en_au') UNION ALL SELECT 'ar' ) AND key_value = @key AND product ='TC' ) tbl ORDER BY locale FOR XML PATH('')),1,1,''),@source_value,@module FETCH NEXT FROM C1 INTO @key,@value,@source_value,@module END CLOSE C1 DEALLOCATE C1 SELECT * FROM #TMPLOCALE where locale IS NOT NULL
You have a cursor...this is a performance black hole. You have multiple nonSARGable predicates. You are littering your query with NOLOCK hints. If you use hints you need to include the WITH keyword. Omitting it is deprecated.
http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/ This entire cursor infested thing could be rewritten as a single set based insert.
There are more than 600000 records in the localization table
Better, use execution plan and identify the areas where the cost is too high. Find an alternate for cursor.
It's hard to be sure without any tables or data but I think this should do the same job: CREATE TABLE #locale ( locale NVARCHAR(255) ); INSERT #locale (locale) SELECT DISTINCT REPLACE(iso_code,'_','-') FROM lang WHERE iso_code LIKE '%[_]%' AND iso_code NOT IN ('en_ca','en_in','en_za', 'en_au') UNION ALL SELECT 'ar'; SELECT key_val , value , locale , source_value , module FROM ( SELECT cte.key_value as key_val , cte.value , STUFF (( select ', ' + locale FROM ( SELECT DISTINCT l2.locale FROM localization l2 INNER JOIN #locale l ON l.locale = l2.locale WHERE product = 'TC' AND l2.key_value cte.key_value ) tbl ORDER BY locale FOR XML PATH('')),1,1,'') AS locale , cte.source_value , cte.module FROM ( SELECT key_value , value , source_value , module FROM ( SELECT key_value , value , ROW_NUMBER() OVER(PARTITION BY key_value ORDER BY VALUE DESC) slno , source_value , module FROM ( SELECT DISTINCT key_value , CASE WHEN locale = 'en-us' THEN CAST(text_value AS NVARCHAR(2000)) ELSE NULL END AS VALUE , source_value , module FROM localization l1 INNER JOIN #locale l ON l.locale = l1.locale WHERE l1.product = 'TC' ) tbl ) ctex WHERE slno = 1 ) cte ) x WHERE locale IS NOT NULL;
21 People are following this question.
