Sql-server – improving performance – how to pass a table as parameter in sql server 2005

Based on your code alone, I would suggest changing the XQuery in the .value() function to be ./text()[1] instead of just .. Doing this will get you closer to the efficiency of attribute-based XML:

select  BulletPoint.NoteTypeCode.value('./text()[1]', 'varchar(50)') as NoteTypeCode

Other minor considerations:

• Change @ListNoteTypeCode datatype to be XML so that you do not need the CONVERT step.
• If you can't pass in an XML type, at the very least change the @ListNoteTypeCode datatype to be NVARCHAR(MAX) so that no implicit conversion needs to be done since XML is UTF-16, same as NVARCHAR / NCHAR.

Major consideration: if the datatype of the ProductBulletPoint.NoteTypeCode column is really VARCHAR (which it seems to be since there is no implicit conversion in the execution plan), then:

• Ideally you shouldn't do string comparisons on codes. Codes should be defined in a lookup table that you can translate into a numeric key that will be the value that is stored in the ProductBulletPoint table, just like the @LanguageID. So there should be a NoteTypeCodes table similar to:

  CREATE TABLE dbo.NoteTypeCodes
  (
    NoteTypeCodeID TINYINT NOT NULL
                   CONSTRAINT [PK_NoteTypeCodes] PRIMARY KEY,
    NoteTypeCode   VARCHAR(50) NOT NULL
  );
  

  Then, it would be best to have the app code pass in a list of the IDs so you would only need to split them with a simple string splitter into @NoteTypeCodeTable which would have a single TINYINT column. Otherwise, you would need to split the code names and JOIN to NoteTypeCodes to get the ID values to insert into @NoteTypeCodeTable.

• If you can't change the NoteTypeCode column to be NoteTypeCodeID TINYINT, then at the very least consider using a binary Collation (i.e. one ending in _BIN2 if available) on that column since it is highly doubtful that there is any need for case-insensitive and/or culturally-aware comparisons of codes. You can alter the column using:

  ALTER TABLE [dbo].[ProductBulletPoint]
    ALTER COLUMN [NoteTypeCode] {current_datatype}
    COLLATE Latin1_General_100_BIN2 {current_NULL_or_NOT-NULL_setting};
  

  You might need to drop and re-create any indexes that use the [NoteTypeCode] column. But this one-time fix will mean that you don't need to add COLLATE Latin1_General_100_BIN2 to any queries using this column as Collation Precedence will take care of it.

  However, you will have to normalize the code values as they are inserted / updated by calling LOWER on the incoming values. You can do this in each stored procedure that does an INSERT and/or UPDATE, or you can handle it automatically in one spot via an AFTER INSERT, UPDATE Trigger. Then you just need to remember to use LOWER on values use in joins and WHERE conditions.

• This advice would most likely also apply equally to the SeasonItemId column and @SeasonItemID input parameter.

Based on the posted XML execution plan and what you found while doing the trace, it would seem that what might possibly be the most impacting factor is that the Stored Procedure is being called with @ListNoteTypeCode being set to empty string. This results in no rows getting inserted into the @NoteTypeCodeTable Table Variable. And, because @NoteTypeCodeTable is used as a filter (since it is Inner Joined to ProductBulletPoint), that results in no rows getting returned from the main query. Hence, any work being done by this stored procedure is all for nothing if @ListNoteTypeCode is ever set to empty string.

Given the frequency of calls to this Stored Procedure, you need to do one of the following:

1. Since @ListNoteTypeCode is a required parameter and an empty string is not a valid value, this could indicate a bug in the app code. If this is a bug, fix the app code to not call this stored procedure when @ListNoteTypeCode is empty.

2. If for some reason it is valid for the app code to call this Stored Procedure with @ListNoteTypeCode being empty, then when that empty value is passed in, skip the actual processing and simply return an empty result set (this will appear to behave the same to all callers, hence no app code will break if any code expects to get a result set when passing in an empty string):

   create procedure [dbo].[myprocedure] (
   ...
   as
   
   IF (@ListNoteTypeCode <> '')
   BEGIN
     declare @NoteTypeCodeTable table ( NoteTypeCode varchar(50) )
     ...
     select  pbp.Tier1, pbp.LanguageId, pbp.NoteText, pbp.NoteTypeCode,
             pbp.NoteGroup, pbp.SortOrder
     from    dbo.ProductBulletPoint pbp
     join    @NoteTypeCodeTable ntc on...
     ...
   END;
   ELSE
   BEGIN
     select  pbp.Tier1, pbp.LanguageId, pbp.NoteText, pbp.NoteTypeCode,
             pbp.NoteGroup, pbp.SortOrder
     from    dbo.ProductBulletPoint pbp
     WHERE   1 = 0;
   END;
   
   select  @CacheDuration = Duration
   ...