question

Naina_S avatar image
Naina_S asked

When search by value, application will be Unresponsive or Hang

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
sql-server-2008t-sqlstored-procedures
9 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Of the 11 parameters, the procedure only uses `@xCode2` When you search by "value" what does that mean, in terms of the code provided? I could imagine dbo.SplitCode2 running long if you changed values for `@xCode2` but don't see how that corresponds to `@StringValue`
1 Like 1 ·
@Naina_s - is this a repost of your earlier question? I'm loathed to close this one now as there has been lots of activity on this thread, but please do not repeat questions. If you haven't had a satisfactory response on the original question, add a comment with further details and ask for further help.
1 Like 1 ·
Thanks Fatherjack.. Please find the complete Procedure. When we search by value (string value )in our application (front-end) it will be unresponsive. eg - > srach by stringvalue - **upgrade** Related **upgrade** we may have many pages in our application or huge data. Please let me know is this due to StringValue VARCHAR(1000) , when we search huge data 2 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.
0 Likes 0 ·
Thanks KenJ
0 Likes 0 ·
Kindly do the needful as its required ASAP. Thanks a lot in advance. Thanks Naina
0 Likes 0 ·
Show more comments
WilliamD avatar image
WilliamD answered
I would think you are having problems with parameter sniffing here. A plan is being generated that is perfect for one type of search and really terrible for a different value. You also have a potential problem using the table variables for `@ContextCode` and `@Category`. First of all, table variables don't have statistics, which can cause poor plan creation in the query optimiser. Secondly, you state that the variable have to be varchar to allow multiple IDs to be sent together, but then don't convert them to their "real" datatype in the table variables. Data-type mismatches is another reason why a poor execution plan can be created. Could you supply us with an execution plan to see if that sheds some light on the matter?
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
I suspect that the problem is down to the ID query being able to make good use of the indexes, and the text search not, forcing a table scan. Combine that with the three-way join of the table on itself, and, if the table consists of more than a handful of lines of data, you'll be waiting a while... Check the execution plans. Compare & contrast for ID vs text lookups. Play "spot the difference". If you really have to search on %something%, then you might also want to investigate use of full text indexes.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Too true Thomas! I just noticed the like search on `@StringValue` - it not being part of the PK (and not having any other indexes to use) a table scan is very likely. The ID search mentioned doesn't seem to be a part of this sproc, unless `@StringName` is meant - which being part of the PK would then definitely be faster to search on.
0 Likes 0 ·
I'm guessing that the ID search mentioned by the OP is a search using the `@xCode` parameter.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.