To make simple a scalar function to get data from Sql query as CPU usage reaches to 100% and take a lot of memory and time
i have 8 column i.e matrial1 to matrial8 due to different grade and types so when ever a matrial is used or mixed they recorded as in database Material 1 or 2 or so on but mixes only two type of matrial one time, so in data base it recorded as true or false if you look about the function it give output but if have to search arround 40,000 barcodes in 12hours recorded data it take 100% of CPu and a lot of memory as i am using 4 processor machine for report server but even then cpu gone to 100% i increase the memory from 4Gb to 6 but even then when geting reprot its did not change any status, Any fellow person has some idea to make it simple which take less resources and efficient as well. thanks in advance..............Eric ------------------------------------- CREATE FUNCTION GetMatrialType ( @BarCodes NCHAR(10) ) RETURNS nchar(20) AS BEGIN DECLARE @Matrial NCHAR(18); DECLARE @Matrial1 int; DECLARE @Matrial2 int; DECLARE @Matrial3 int; DECLARE @Matrial4 int; DECLARE @Matrial5 int; DECLARE @Matrial6 int; DECLARE @Matrial7 int; DECLARE @Matrial8 int; set @Matrial1 = (Select cast(Matrial1 as int) from dbo.ProductA where (dbo.ProductA.BarCodes = @BarCodes)) set @Matrial2 = (Select cast(Matrial2 as int) from dbo.ProductA where (dbo.ProductA.BarCodes = @BarCodes)) set @Matrial3 = (Select cast(Matrial3 as int) from dbo.ProductA where (dbo.ProductA.BarCodes = @BarCodes)) set @Matrial4 = (Select cast(Matrial4 as int) from dbo.ProductA where (dbo.ProductA.BarCodes = @BarCodes)) set @Matrial5 = (Select cast(Matrial5 as int) from dbo.ProductA where (dbo.ProductA.BarCodes = @BarCodes)) set @Matrial6 = (Select cast(Matrial6 as int) from dbo.ProductA where (dbo.ProductA.BarCodes = @BarCodes)) set @Matrial7 = (Select cast(Matrial7 as int) from dbo.ProductA where (dbo.ProductA.BarCodes = @BarCodes)) set @Matrial8 = (Select cast(Matrial8 as int) from dbo.ProductA where (dbo.ProductA.BarCodes = @BarCodes)) IF @Matrial1 + @Matrial2 =2 SET @Matrial = 'Mix_1&2_Type_Matrial' ELSE IF @Matrial1 + @Matrial3 =2 SET @Matrial = 'Mix_1&3_Type_Matrial' ELSE IF @Matrial1 + @Matrial4 =2 SET @Matrial = 'Mix_1&4_Type_Matrial' ELSE IF @Matrial1 + @Matrial5 =2 SET @Matrial = 'Mix_1&5_Type_Matrial' ELSE IF @Matrial1 + @Matrial6 =2 SET @Matrial = 'Mix_1&6_Type_Matrial' ELSE IF @Matrial1 + @Matrial7 =2 SET @Matrial = 'Mix_1&7_Type_Matrial' ELSE IF @Matrial1 + @Matrial8 =2 SET @Matrial = 'Mix_1&8_Type_Matrial' ELSE --==================================================================Block 1 IF @Matrial2 + @Matrial3 =2 SET @Matrial = 'Mix_2&3_Type_Matrial' ELSE IF @Matrial2 + @Matrial4 =2 SET @Matrial = 'Mix_2&4_Type_Matrial' ELSE IF @Matrial2 + @Matrial5 =2 SET @Matrial = 'Mix_2&5_Type_Matrial' ELSE IF @Matrial2 + @Matrial6 =2 SET @Matrial = 'Mix_2&6_Type_Matrial' ELSE IF @Matrial2 + @Matrial7 =2 SET @Matrial = 'Mix_2&7_Type_Matrial' ELSE IF @Matrial2 + @Matrial8 =2 SET @Matrial = 'Mix_2&8_Type_Matrial' ELSE --==================================================================Block 2 IF @Matrial3 + @Matrial4 =2 SET @Matrial = 'Mix_3&4_Type_Matrial' ELSE IF @Matrial3 + @Matrial5 =2 SET @Matrial = 'Mix_3&5_Type_Matrial' ELSE IF @Matrial3 + @Matrial6 =2 SET @Matrial = 'Mix_3&6_Type_Matrial' ELSE IF @Matrial3 + @Matrial7 =2 SET @Matrial = 'Mix_3&7_Type_Matrial' ELSE IF @Matrial3 + @Matrial8 =2 SET @Matrial = 'Mix_3&8_Type_Matrial' ELSE --==================================================================Block 3 IF @Matrial4 + @Matrial5 =2 SET @Matrial = 'Mix_4&5_Type_Matrial' ELSE IF @Matrial4 + @Matrial6 =2 SET @Matrial = 'Mix_4&6_Type_Matrial' ELSE IF @Matrial4 + @Matrial7 =2 SET @Matrial = 'Mix_4&7_Type_Matrial' ELSE IF @Matrial4 + @Matrial8 =2 SET @Matrial = 'Mix_4&8_Type_Matrial' ELSE --==================================================================Block 4 IF @Matrial5 + @Matrial6 =2 SET @Matrial = 'Mix_5&6_Type_Matrial' ELSE IF @Matrial5 + @Matrial7 =2 SET @Matrial = 'Mix_5&7_Type_Matrial' ELSE IF @Matrial5 + @Matrial8 =2 SET @Matrial = 'Mix_5&8_Type_Matrial' ELSE --==================================================================Block 5 IF @Matrial6 + @Matrial7 =2 SET @Matrial = 'Mix_6&7_Type_Matrial' ELSE IF @Matrial6 + @Matrial8 =2 SET @Matrial = 'Mix_6&8_Type_Matrial' ELSE --==================================================================Block 6 IF @Matrial7 + @Matrial8 =2 SET @Matrial = 'Mix_7&8_Type_Matrial' ELSE --==================================================================Block 7 Singles IF @Matrial1 =1 SET @Matrial = 'Matrial_1' ELSE IF @Matrial2 =1 SET @Matrial = 'Matrial_2' ELSE IF @Matrial3 =1 SET @Matrial = 'Matrial_3' ELSE IF @Matrial4 =1 SET @Matrial = 'Matrial_4' ELSE IF @Matrial5 =1 SET @Matrial = 'Matrial_5' ELSE IF @Matrial6 =1 SET @Matrial = 'Matrial_6' ELSE IF @Matrial7 =1 SET @Matrial = 'Matrial_7' ELSE IF @Matrial8 =1 SET @Matrial = 'Matrial_8' Else SET @Matrial = 'Matrial_?'; RETURN @Matrial END Thanks in advance.
November 12th, 2010 5:43am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics