Hi David,

You may consider a couple more options:
- Using Table variables instead of temp tables
- Passing the list of PKs in XML format and use OPENXML() in queries

Only test can tell which options will be most efficient.


>Our database is far away from some of its clients, so we are trying to pull as much in a single query as we can. I am writing a stored procedure that takes a comma delimited string of PKs and then uses them to find other records, a total of 15 SELECTs. There could be up to around 10 PKs or so in the string, but the number needs to be variable for flexibility.
>
>I wanted to know how to write the most efficient SP. The two options that occurred to me are:

1. EXECUTE ('SELECT street, city, state
>             FROM   Address
>             WHERE  customerID IN (' + @pkString + ')')
>
>   EXECUTE ('SELECT orderID, amount
>             FROM   Orders
>             WHERE  customerID IN (' + @pkString + ')')
>
>   -- Additional SELECTs...
>
>2. CREATE TABLE #customerIDTable (customerID INT)
>
>   DECLARE @StartIndex INT
>   SET @StartIndex = 0
>   DECLARE @CustomerID VARCHAR(10)
>
>   SET @CommaIndex = CHARINDEX(',', @pkString, @StartIndex)
>		
>   WHILE @CommaIndex > 0
>   BEGIN
>      SET @CustomerID =
>         LTRIM(SUBSTRING(@pkString, @StartIndex, @CommaIndex - @StartIndex))
>			
>      INSERT #customerIDTable VALUES (@CustomerID)
>			
>      SET @StartIndex = @CommaIndex + 1
>      SET @CommaIndex = CHARINDEX(',', @pkString, @StartIndex)
>   END
>
>   -- This either gets the last pk in @pkString or the single
>   -- customerID in @pkString
>   SET @WarehouseID =
>      LTRIM(SUBSTRING(@Warehouses, @StartIndex, @WarehousesLength - @StartIndex + 1))
>   INSERT #WarehouseIDs VALUES (@WarehouseID)
>
>   SELECT add.street, add.city, add.state
>   FROM   Address add
>          INNER JOIN #customerIDTable cidt
>              ON add.customerID = cidt.customerID
>
>   SELECT ord.orderID, ord.amount
>   FROM   Orders ord
>          INNER JOIN #customerIDTable cidt
>              ON ord.customerID = cidt.customerID
>
>   -- Additional SELECTs...
>
>   DROP TABLE #customerIDTable

>Is there a more efficient way? I doubt that the second option would pay off unless I was doing quite a few SELECTs. All the tables that I am JOINing them with are pretty big.
>