question

Hello All, In our application when we search by ID , will get the result fast but when we do search by value ,application will be Unresponsive or Hang Reason :- Please let me know is this due to StringValue VARCHAR(1000) , when we search huge data 1 page or more , is it possible that application will become Unresponsive or Hang ?? Is this reason is relevent ? Also plz let me know if StringValue VARCHAR(MAX) , will be the best possible option for this procedure. Please find below details : /* Data Types missing, hopefully we will get them from @Naina_S CREATE TABLE dbo.LanguageString ( Code1, Code2, LanguageCode, StringName, StringValue, LastUpdate, Updateby, Context, ContentType, DESCRIPTION, PRIMARY KEY CLUSTERED (Code1, Code2, LanguageCode, StringName) ) CREATE PROCEDURE dbo.SearchTool @xCode2 char(8), @DefaultLanguageCode char(2) = NULL, @TranslationLanguageCode char(2) = NULL, @ReferenceLanguageCode char(2) = NULL, @StringName varchar(65) = NULL, --varchar to allow comparison with % @ContentType varchar(40) = NULL, @ContextCode varchar(50) = NULL, @StringValue varchar(1000) = NULL, @Category varchar(50) = NULL, -- normally TINYINT but needs to be a varchar to allow parsing @Admin tinyint = NULL, @StringState smallint = NULL AS BEGIN DECLARE @Code1 char(4), @Code2 char(4), @separator char(1), @INDEX int, @SLICE varchar(10) SET @separator = '#' EXEC dbo.SplitCode2 @xCode2, @Code1 = @Code1 OUTPUT, @Code2 = @Code2 OUTPUT -- Begin parsing multiple value parameters ----------------------- DECLARE @T_Category TABLE (Category varchar(50)) SET @INDEX = 1 IF @Category IS NOT NULL BEGIN WHILE @INDEX != 0 BEGIN SELECT @INDEX = CHARINDEX(@separator, @Category) IF @INDEX != 0 SELECT @SLICE = LEFT(@Category, @INDEX - 1) ELSE SELECT @SLICE = @Category INSERT INTO @T_Category (Category) VALUES (@SLICE) SELECT @Category = RIGHT(@Category, LEN(@Category) - @INDEX) IF LEN(@Category) = 0 BREAK END END ------------------------ DECLARE @T_Context TABLE (ContextCode varchar(50)) SET @INDEX = 1 IF @ContextCode IS NOT NULL BEGIN WHILE @INDEX != 0 BEGIN SELECT @INDEX = CHARINDEX(@separator, @ContextCode) IF @INDEX != 0 SELECT @SLICE = LEFT(@ContextCode, @INDEX - 1) ELSE SELECT @SLICE = @ContextCode INSERT INTO @T_Context (ContextCode) VALUES (@SLICE) SELECT @ContextCode = RIGHT(@ContextCode, LEN(@ContextCode) - @INDEX) IF LEN(@ContextCode) = 0 BREAK END END ----------------------- -- end of parsing SELECT DEFLT.LanguageCode 'DefaultLanguage', DEFLT.StringName, TRANSLAT.StringValue 'TranslationValue', REFERENCE.StringValue 'ReferenceValue', DEFLT.ContentType, DEFLT.Admin, DEFLT.Category, DEFLT.ContextCode, DEFLT.ContentSubType, DEFLT.Description FROM LanguageString DEFLT -- get default language info LEFT JOIN LanguageString TRANSLAT ON DEFLT.Code1 = TRANSLAT.Code1 AND DEFLT.Code2 = TRANSLAT.Code2 AND DEFLT.StringName = TRANSLAT.StringName AND DEFLT.ContentType = TRANSLAT.ContentType AND DEFLT.ContextCode = TRANSLAT.ContextCode AND (@TranslationLanguageCode IS NULL OR TRANSLAT.LanguageCode = @TranslationLanguageCode) -- get reference language info LEFT JOIN LanguageString REFERENCE ON DEFLT.Code1 = REFERENCE.Code1 AND DEFLT.Code2 = REFERENCE.Code2 AND DEFLT.StringName = REFERENCE.StringName AND DEFLT.ContentType = REFERENCE.ContentType AND DEFLT.ContextCode = REFERENCE.ContextCode AND (@ReferenceLanguageCode IS NULL OR REFERENCE.LanguageCode = @ReferenceLanguageCode) WHERE DEFLT.Code1 = @Code1 AND DEFLT.Code2 = @Code2 AND (@DefaultLanguageCode IS NULL OR DEFLT.LanguageCode = @DefaultLanguageCode) AND (@StringName IS NULL OR DEFLT.StringName LIKE @StringName) -- so that the call can include % in the parametername AND (@StringValue IS NULL OR TRANSLAT.StringValue LIKE '%' + @StringValue + '%') AND (@ContentType IS NULL OR DEFLT.ContentType = @ContentType) AND (@ContextCode IS NULL OR DEFLT.ContextCode IN (SELECT ContextCode FROM @T_Context)) AND (@Category IS NULL OR DEFLT.Category IN (SELECT Category FROM @T_Category)) AND (@Admin IS NULL OR DEFLT.Admin = @Admin) AND (@StringState IS NULL OR DEFLT.StringState = @StringState) END Kindly do the needful. Thanks Naina -------------------------------------------------------------------------------------------- Table Structre Column_name Type Computed Length Prec Scale Nullable code1 char no 4 no code2 char no 4 no LanguageCode char no 2 no StringName char no 65 no StringValue varchar no 4000 yes LastUpdate datetime no 8 yes Updateby varchar no 40 yes Admin tinyint no 1 3 0 no Description varchar no 255 yes Category tinyint no 1 3 0 no ContextCode char no 50 no ContentType varchar no 40 no ContentSubType varchar no 40 yes StringState smallint no 2 5 0 yes