Using Adapter:

I'm trying to understand exactly how the prefetch path chooses what to retrieve. The problem that I am having is that I am fetching Fork entities while filtering on fields in the ForkVersion table and I get too many rows back from the ForkVersion prefetch because the filter that I use on Fork is not being applied to ForkVersion. Instead the ForkVersion table is filtered on the distinct list of ForkId's from the Fork table. This gives me too many rows from ForkVersion because there is a one to many relation between Fork (one) and (to many) ForkVersion.

How can I make this fetch the correct ForkVersion rows while filtering Fork.

Here is the generated query. You can see the ForkVersion fields that I'm filtering on in the first query which also need to get filtered on the ForkVersion query.

exec sp_executesql N'SELECT [WS2].[dbo].[Fork].[ForkID], [WS2].[dbo].[Fork].[AdditionalStyles], [WS2].[dbo].[Fork].[BrakeType], [WS2].[dbo].[Fork].[CrownClamp], [WS2].[dbo].[Fork].[CrownHeight], [WS2].[dbo].[Fork].[CaliperSpacing], [WS2].[dbo].[Fork].[IsTripleClamp], [WS2].[dbo].[Fork].[MaxRotorSize], [WS2].[dbo].[Fork].[ShockType], [WS2].[dbo].[Fork].[WheelSize]
FROM (((((( [WS2].[dbo].[Product]
INNER JOIN [WS2].[dbo].[Fork]  ON  [WS2].[dbo].[Product].[ProductID]=[WS2].[dbo].[Fork].[ForkID])
LEFT JOIN [WS2].[dbo].[ProductBlurb]  ON  [WS2].[dbo].[Product].[ProductID]=[WS2].[dbo].[ProductBlurb].[ProductID])
INNER JOIN [WS2].[dbo].[Manufacturer]  ON  [WS2].[dbo].[Manufacturer].[ManufacturerID]=[WS2].[dbo].[Product].[ManufacturerID])
INNER JOIN [WS2].[dbo].[Company]  ON  [WS2].[dbo].[Company].[CompanyID]=[WS2].[dbo].[Manufacturer].[ManufacturerID])
INNER JOIN [WS2].[dbo].[ForkVersion]  ON  [WS2].[dbo].[Fork].[ForkID]=[WS2].[dbo].[ForkVersion].[ForkID])
INNER JOIN [WS2].[dbo].[ProductVersion]  ON  [WS2].[dbo].[ProductVersion].[ProductVersionID]=[WS2].[dbo].[ForkVersion].[ProductVersionID])
WHERE ( ( [WS2].[dbo].[Product].[ActiveState] = @ActiveState1
AND [WS2].[dbo].[ProductVersion].[ActiveState] = @ActiveState2
AND [WS2].[dbo].[ProductVersion].[IsPartSpecific] = @IsPartSpecific3
AND [WS2].[dbo].[ForkVersion].[SteererTubeDiameter] = @SteererTubeDiameter4
AND
    ( [WS2].[dbo].[Product].[StyleCode] = @StyleCode5
    OR
    [WS2].[dbo].[Product].[StyleCode] IN
    (SELECT [WS2].[dbo].[Fork].[AdditionalStyles] FROM [WS2].[dbo].[Fork]
    WHERE [WS2].[dbo].[Fork].[ForkID] = [WS2].[dbo].[Fork].[ForkID])
AND [WS2].[dbo].[ForkVersion].[TravelMax] >= @TravelMax6
AND [WS2].[dbo].[ForkVersion].[TravelMax] <= @TravelMax7)))
ORDER BY [WS2].[dbo].[Product].[Model] ASC'
, N'@ActiveState1 char(3),@ActiveState2 char(3),@IsPartSpecific3 bit,@SteererTubeDiameter4 decimal(4,2),@StyleCode5 char(2),@TravelMax6 decimal(5,2),@TravelMax7 decimal(5,2)'
, @ActiveState1 = 'Web', @ActiveState2 = 'Web', @IsPartSpecific3 = 0, @SteererTubeDiameter4 = 28.60, @StyleCode5 = 'XC', @TravelMax6 = 80.00, @TravelMax7 = 80.00
go
exec sp_executesql N'SELECT [WS2].[dbo].[ForkVersion].[ProductVersionID], [WS2].[dbo].[ForkVersion].[ForkID], [WS2].[dbo].[ForkVersion].[AxleSize], [WS2].[dbo].[ForkVersion].[BendType], [WS2].[dbo].[ForkVersion].[Rake], [WS2].[dbo].[ForkVersion].[SteererMaterial], [WS2].[dbo].[ForkVersion].[SteererTubeDiameter], [WS2].[dbo].[ForkVersion].[SteererTubeLength], [WS2].[dbo].[ForkVersion].[ThreadType], [WS2].[dbo].[ForkVersion].[TravelDefault], [WS2].[dbo].[ForkVersion].[TravelMax], [WS2].[dbo].[ForkVersion].[TravelMin] FROM [WS2].[dbo].[ForkVersion]  WHERE ( [WS2].[dbo].[ForkVersion].[ForkID] IN (@ForkID1, @ForkID2, @ForkID3, @ForkID4, @ForkID5, @ForkID6, @ForkID7, @ForkID8, @ForkID9, @ForkID10, @ForkID11, @ForkID12, @ForkID13, @ForkID14, @ForkID15, @ForkID16, @ForkID17, @ForkID18, @ForkID19, @ForkID20, @ForkID21, @ForkID22, @ForkID23, @ForkID24, @ForkID25, @ForkID26, @ForkID27, @ForkID28))', N'@ForkID1 int,@ForkID2 int,@ForkID3 int,@ForkID4 int,@ForkID5 int,@ForkID6 int,@ForkID7 int,@ForkID8 int,@ForkID9 int,@ForkID10 int,@ForkID11 int,@ForkID12 int,@ForkID13 int,@ForkID14 int,@ForkID15 int,@ForkID16 int,@ForkID17 int,@ForkID18 int,@ForkID19 int,@ForkID20 int,@ForkID21 int,@ForkID22 int,@ForkID23 int,@ForkID24 int,@ForkID25 int,@ForkID26 int,@ForkID27 int,@ForkID28 int', @ForkID1 = 4535, @ForkID2 = 4496, @ForkID3 = 4498, @ForkID4 = 4499, @ForkID5 = 4500, @ForkID6 = 4530, @ForkID7 = 4531, @ForkID8 = 4532, @ForkID9 = 5024, @ForkID10 = 5022, @ForkID11 = 4504, @ForkID12 = 5026, @ForkID13 = 5027, @ForkID14 = 4508, @ForkID15 = 4509, @ForkID16 = 4510, @ForkID17 = 4511, @ForkID18 = 5020, @ForkID19 = 5021, @ForkID20 = 4543, @ForkID21 = 4544, @ForkID22 = 4545, @ForkID23 = 4546, @ForkID24 = 4536, @ForkID25 = 4548, @ForkID26 = 4549, @ForkID27 = 4501, @ForkID28 = 4502
go
exec sp_executesql N'SELECT [WS2].[dbo].[Product].[ProductID], [WS2].[dbo].[Product].[ActiveState], [WS2].[dbo].[Product].[ProductTypeID], [WS2].[dbo].[Product].[StyleCode], [WS2].[dbo].[Product].[Religion], [WS2].[dbo].[Product].[ManufacturerID], [WS2].[dbo].[Product].[Model], [WS2].[dbo].[Product].[ModelYear], [WS2].[dbo].[Product].[Warranty], [WS2].[dbo].[Product].[Material], [WS2].[dbo].[Product].[MaterialDescription], [WS2].[dbo].[Product].[WeightLimit], [WS2].[dbo].[Product].[Discount], [WS2].[dbo].[Product].[SortOrder], [WS2].[dbo].[Product].[CreateDate], [WS2].[dbo].[Product].[UpdateDate] FROM [WS2].[dbo].[Product]  WHERE ( [WS2].[dbo].[Product].[ProductID] IN (@ProductID1, @ProductID2, @ProductID3, @ProductID4, @ProductID5, @ProductID6, @ProductID7, @ProductID8, @ProductID9, @ProductID10, @ProductID11, @ProductID12, @ProductID13, @ProductID14, @ProductID15, @ProductID16, @ProductID17, @ProductID18, @ProductID19, @ProductID20, @ProductID21, @ProductID22, @ProductID23, @ProductID24, @ProductID25, @ProductID26, @ProductID27, @ProductID28))', N'@ProductID1 int,@ProductID2 int,@ProductID3 int,@ProductID4 int,@ProductID5 int,@ProductID6 int,@ProductID7 int,@ProductID8 int,@ProductID9 int,@ProductID10 int,@ProductID11 int,@ProductID12 int,@ProductID13 int,@ProductID14 int,@ProductID15 int,@ProductID16 int,@ProductID17 int,@ProductID18 int,@ProductID19 int,@ProductID20 int,@ProductID21 int,@ProductID22 int,@ProductID23 int,@ProductID24 int,@ProductID25 int,@ProductID26 int,@ProductID27 int,@ProductID28 int', @ProductID1 = 4535, @ProductID2 = 4496, @ProductID3 = 4498, @ProductID4 = 4499, @ProductID5 = 4500, @ProductID6 = 4530, @ProductID7 = 4531, @ProductID8 = 4532, @ProductID9 = 5024, @ProductID10 = 5022, @ProductID11 = 4504, @ProductID12 = 5026, @ProductID13 = 5027, @ProductID14 = 4508, @ProductID15 = 4509, @ProductID16 = 4510, @ProductID17 = 4511, @ProductID18 = 5020, @ProductID19 = 5021, @ProductID20 = 4543, @ProductID21 = 4544, @ProductID22 = 4545, @ProductID23 = 4546, @ProductID24 = 4536, @ProductID25 = 4548, @ProductID26 = 4549, @ProductID27 = 4501, @ProductID28 = 4502
go
exec sp_executesql N'SELECT [WS2].[dbo].[ProductVersion].[ProductVersionID], [WS2].[dbo].[ProductVersion].[ProductID], [WS2].[dbo].[ProductVersion].[ActiveState], [WS2].[dbo].[ProductVersion].[Religion], [WS2].[dbo].[ProductVersion].[DistributorID], [WS2].[dbo].[ProductVersion].[DistributorNumber], [WS2].[dbo].[ProductVersion].[ManufacturerNumber], [WS2].[dbo].[ProductVersion].[IsPartSpecific], [WS2].[dbo].[ProductVersion].[OECostPrice], [WS2].[dbo].[ProductVersion].[OEPrice], [WS2].[dbo].[ProductVersion].[RPCostPrice], [WS2].[dbo].[ProductVersion].[RPPrice], [WS2].[dbo].[ProductVersion].[Discount], [WS2].[dbo].[ProductVersion].[Color], [WS2].[dbo].[ProductVersion].[ImageName], [WS2].[dbo].[ProductVersion].[MfrGrams], [WS2].[dbo].[ProductVersion].[WSGrams], [WS2].[dbo].[ProductVersion].[WSNotes] FROM [WS2].[dbo].[ProductVersion]  WHERE ( [WS2].[dbo].[ProductVersion].[ProductID] IN (@ProductID1, @ProductID2, @ProductID3, @ProductID4, @ProductID5, @ProductID6, @ProductID7, @ProductID8, @ProductID9, @ProductID10, @ProductID11, @ProductID12, @ProductID13, @ProductID14, @ProductID15, @ProductID16, @ProductID17, @ProductID18, @ProductID19, @ProductID20, @ProductID21, @ProductID22, @ProductID23, @ProductID24, @ProductID25, @ProductID26, @ProductID27, @ProductID28)) ORDER BY [WS2].[dbo].[ProductVersion].[RPPrice] ASC,[WS2].[dbo].[ProductVersion].[MfrGrams] ASC', N'@ProductID1 int,@ProductID2 int,@ProductID3 int,@ProductID4 int,@ProductID5 int,@ProductID6 int,@ProductID7 int,@ProductID8 int,@ProductID9 int,@ProductID10 int,@ProductID11 int,@ProductID12 int,@ProductID13 int,@ProductID14 int,@ProductID15 int,@ProductID16 int,@ProductID17 int,@ProductID18 int,@ProductID19 int,@ProductID20 int,@ProductID21 int,@ProductID22 int,@ProductID23 int,@ProductID24 int,@ProductID25 int,@ProductID26 int,@ProductID27 int,@ProductID28 int', @ProductID1 = 4496, @ProductID2 = 4498, @ProductID3 = 4499, @ProductID4 = 4500, @ProductID5 = 4501, @ProductID6 = 4502, @ProductID7 = 4504, @ProductID8 = 4508, @ProductID9 = 4509, @ProductID10 = 4510, @ProductID11 = 4511, @ProductID12 = 4530, @ProductID13 = 4531, @ProductID14 = 4532, @ProductID15 = 4535, @ProductID16 = 4536, @ProductID17 = 4543, @ProductID18 = 4544, @ProductID19 = 4545, @ProductID20 = 4546, @ProductID21 = 4548, @ProductID22 = 4549, @ProductID23 = 5020, @ProductID24 = 5021, @ProductID25 = 5022, @ProductID26 = 5024, @ProductID27 = 5026, @ProductID28 = 5027
go
exec sp_executesql N'SELECT [WS2].[dbo].[ProductBlurb].[ProductID], [WS2].[dbo].[ProductBlurb].[LongBlurb], [WS2].[dbo].[ProductBlurb].[ShortBlurb] FROM [WS2].[dbo].[ProductBlurb]  WHERE ( [WS2].[dbo].[ProductBlurb].[ProductID] IN (@ProductID1, @ProductID2, @ProductID3, @ProductID4, @ProductID5, @ProductID6, @ProductID7, @ProductID8, @ProductID9, @ProductID10, @ProductID11, @ProductID12, @ProductID13, @ProductID14, @ProductID15, @ProductID16, @ProductID17, @ProductID18, @ProductID19, @ProductID20, @ProductID21, @ProductID22, @ProductID23, @ProductID24, @ProductID25, @ProductID26, @ProductID27, @ProductID28))', N'@ProductID1 int,@ProductID2 int,@ProductID3 int,@ProductID4 int,@ProductID5 int,@ProductID6 int,@ProductID7 int,@ProductID8 int,@ProductID9 int,@ProductID10 int,@ProductID11 int,@ProductID12 int,@ProductID13 int,@ProductID14 int,@ProductID15 int,@ProductID16 int,@ProductID17 int,@ProductID18 int,@ProductID19 int,@ProductID20 int,@ProductID21 int,@ProductID22 int,@ProductID23 int,@ProductID24 int,@ProductID25 int,@ProductID26 int,@ProductID27 int,@ProductID28 int', @ProductID1 = 4496, @ProductID2 = 4498, @ProductID3 = 4499, @ProductID4 = 4500, @ProductID5 = 4501, @ProductID6 = 4502, @ProductID7 = 4504, @ProductID8 = 4508, @ProductID9 = 4509, @ProductID10 = 4510, @ProductID11 = 4511, @ProductID12 = 4530, @ProductID13 = 4531, @ProductID14 = 4532, @ProductID15 = 4535, @ProductID16 = 4536, @ProductID17 = 4543, @ProductID18 = 4544, @ProductID19 = 4545, @ProductID20 = 4546, @ProductID21 = 4548, @ProductID22 = 4549, @ProductID23 = 5020, @ProductID24 = 5021, @ProductID25 = 5022, @ProductID26 = 5024, @ProductID27 = 5026, @ProductID28 = 5027
go
exec sp_executesql N'SELECT [WS2].[dbo].[Manufacturer].[ManufacturerID], [WS2].[dbo].[Manufacturer].[MountainBlurb], [WS2].[dbo].[Manufacturer].[RoadBlurb] FROM [WS2].[dbo].[Manufacturer]  WHERE ( [WS2].[dbo].[Manufacturer].[ManufacturerID] IN (@ManufacturerID1, @ManufacturerID2, @ManufacturerID3, @ManufacturerID4, @ManufacturerID5, @ManufacturerID6, @ManufacturerID7, @ManufacturerID8, @ManufacturerID9, @ManufacturerID10, @ManufacturerID11, @ManufacturerID12, @ManufacturerID13, @ManufacturerID14, @ManufacturerID15, @ManufacturerID16, @ManufacturerID17, @ManufacturerID18, @ManufacturerID19, @ManufacturerID20, @ManufacturerID21, @ManufacturerID22, @ManufacturerID23, @ManufacturerID24, @ManufacturerID25, @ManufacturerID26, @ManufacturerID27, @ManufacturerID28))', N'@ManufacturerID1 int,@ManufacturerID2 int,@ManufacturerID3 int,@ManufacturerID4 int,@ManufacturerID5 int,@ManufacturerID6 int,@ManufacturerID7 int,@ManufacturerID8 int,@ManufacturerID9 int,@ManufacturerID10 int,@ManufacturerID11 int,@ManufacturerID12 int,@ManufacturerID13 int,@ManufacturerID14 int,@ManufacturerID15 int,@ManufacturerID16 int,@ManufacturerID17 int,@ManufacturerID18 int,@ManufacturerID19 int,@ManufacturerID20 int,@ManufacturerID21 int,@ManufacturerID22 int,@ManufacturerID23 int,@ManufacturerID24 int,@ManufacturerID25 int,@ManufacturerID26 int,@ManufacturerID27 int,@ManufacturerID28 int', @ManufacturerID1 = 166, @ManufacturerID2 = 166, @ManufacturerID3 = 166, @ManufacturerID4 = 166, @ManufacturerID5 = 166, @ManufacturerID6 = 166, @ManufacturerID7 = 184, @ManufacturerID8 = 184, @ManufacturerID9 = 184, @ManufacturerID10 = 184, @ManufacturerID11 = 184, @ManufacturerID12 = 185, @ManufacturerID13 = 185, @ManufacturerID14 = 185, @ManufacturerID15 = 186, @ManufacturerID16 = 186, @ManufacturerID17 = 207, @ManufacturerID18 = 207, @ManufacturerID19 = 207, @ManufacturerID20 = 207, @ManufacturerID21 = 207, @ManufacturerID22 = 207, @ManufacturerID23 = 184, @ManufacturerID24 = 184, @ManufacturerID25 = 184, @ManufacturerID26 = 184, @ManufacturerID27 = 184, @ManufacturerID28 = 184
go
exec sp_executesql N'SELECT [WS2].[dbo].[Company].[CompanyID], [WS2].[dbo].[Company].[IsActive], [WS2].[dbo].[Company].[Abbreviation], [WS2].[dbo].[Company].[CompanyName], [WS2].[dbo].[Company].[Phone], [WS2].[dbo].[Company].[Fax], [WS2].[dbo].[Company].[Email], [WS2].[dbo].[Company].[ImageName], [WS2].[dbo].[Company].[TimeZone], [WS2].[dbo].[Company].[Description], [WS2].[dbo].[Company].[AddressID], [WS2].[dbo].[Company].[ShippingCost] FROM [WS2].[dbo].[Company]  WHERE ( [WS2].[dbo].[Company].[CompanyID] IN (@CompanyID1, @CompanyID2, @CompanyID3, @CompanyID4, @CompanyID5))', N'@CompanyID1 int,@CompanyID2 int,@CompanyID3 int,@CompanyID4 int,@CompanyID5 int', @CompanyID1 = 166, @CompanyID2 = 184, @CompanyID3 = 185, @CompanyID4 = 186, @CompanyID5 = 207
go

Could you also please paste some of your code which created this query?

Otis wrote:

Could you also please paste some of your code which created this query?

Yes, its in a number of different methods so I'll try and put it together in a single method so it makes sense.

I just moved the code for the prefetch paths after the RelationPredicateBucket code so that I could pass the PredicateExpression and the Relations into the prefetch for ForkVersion. This seems to have helped by adding the Fork predicate into the ForkVersion. What is still not working though is the where clause for ForkVersion still doesn't seem to be filtering out rows based on the ForkVersion.TravelMax rule. Otis, can you tell me why this is happening?

Here is a new query.

exec sp_executesql N'SELECT [WS2].[dbo].[Fork].[ForkID], [WS2].[dbo].[Fork].[AdditionalStyles], [WS2].[dbo].[Fork].[BrakeType], [WS2].[dbo].[Fork].[CrownClamp], [WS2].[dbo].[Fork].[CrownHeight], [WS2].[dbo].[Fork].[CaliperSpacing], [WS2].[dbo].[Fork].[IsTripleClamp], [WS2].[dbo].[Fork].[MaxRotorSize], [WS2].[dbo].[Fork].[ShockType], [WS2].[dbo].[Fork].[WheelSize] FROM (((((( [WS2].[dbo].[Product]  INNER JOIN [WS2].[dbo].[Fork]  ON  [WS2].[dbo].[Product].[ProductID]=[WS2].[dbo].[Fork].[ForkID]) LEFT JOIN [WS2].[dbo].[ProductBlurb]  ON  [WS2].[dbo].[Product].[ProductID]=[WS2].[dbo].[ProductBlurb].[ProductID]) INNER JOIN [WS2].[dbo].[Manufacturer]  ON  [WS2].[dbo].[Manufacturer].[ManufacturerID]=[WS2].[dbo].[Product].[ManufacturerID]) INNER JOIN [WS2].[dbo].[Company]  ON  [WS2].[dbo].[Company].[CompanyID]=[WS2].[dbo].[Manufacturer].[ManufacturerID]) INNER JOIN [WS2].[dbo].[ForkVersion]  ON  [WS2].[dbo].[Fork].[ForkID]=[WS2].[dbo].[ForkVersion].[ForkID]) INNER JOIN [WS2].[dbo].[ProductVersion]  ON  [WS2].[dbo].[ProductVersion].[ProductVersionID]=[WS2].[dbo].[ForkVersion].[ProductVersionID]) WHERE ( ( [WS2].[dbo].[Product].[ActiveState] = @ActiveState1 AND [WS2].[dbo].[ProductVersion].[ActiveState] = @ActiveState2 AND [WS2].[dbo].[ProductVersion].[IsPartSpecific] = @IsPartSpecific3 AND [WS2].[dbo].[ForkVersion].[SteererTubeDiameter] = @SteererTubeDiameter4 AND ( [WS2].[dbo].[Product].[StyleCode] = @StyleCode5 OR [WS2].[dbo].[Product].[StyleCode] IN (SELECT [WS2].[dbo].[Fork].[AdditionalStyles] FROM [WS2].[dbo].[Fork]  WHERE [WS2].[dbo].[Fork].[ForkID] = [WS2].[dbo].[Fork].[ForkID]) AND [WS2].[dbo].[ForkVersion].[TravelMax] >= @TravelMax6 AND [WS2].[dbo].[ForkVersion].[TravelMax] <= @TravelMax7))) ORDER BY [WS2].[dbo].[Product].[Model] ASC', N'@ActiveState1 char(3),@ActiveState2 char(3),@IsPartSpecific3 bit,@SteererTubeDiameter4 decimal(4,2),@StyleCode5 char(2),@TravelMax6 decimal(5,2),@TravelMax7 decimal(5,2)', @ActiveState1 = 'Web', @ActiveState2 = 'Web', @IsPartSpecific3 = 0, @SteererTubeDiameter4 = 28.60, @StyleCode5 = 'XC', @TravelMax6 = 80.00, @TravelMax7 = 80.00
go
exec sp_executesql N'SELECT DISTINCT [WS2].[dbo].[ForkVersion].[ProductVersionID], [WS2].[dbo].[ForkVersion].[ForkID], [WS2].[dbo].[ForkVersion].[AxleSize], [WS2].[dbo].[ForkVersion].[BendType], [WS2].[dbo].[ForkVersion].[Rake], [WS2].[dbo].[ForkVersion].[SteererMaterial], [WS2].[dbo].[ForkVersion].[SteererTubeDiameter], [WS2].[dbo].[ForkVersion].[SteererTubeLength], [WS2].[dbo].[ForkVersion].[ThreadType], [WS2].[dbo].[ForkVersion].[TravelDefault], [WS2].[dbo].[ForkVersion].[TravelMax], [WS2].[dbo].[ForkVersion].[TravelMin] FROM (((((( [WS2].[dbo].[Product]  INNER JOIN [WS2].[dbo].[Fork]  ON  [WS2].[dbo].[Product].[ProductID]=[WS2].[dbo].[Fork].[ForkID]) LEFT JOIN [WS2].[dbo].[ProductBlurb]  ON  [WS2].[dbo].[Product].[ProductID]=[WS2].[dbo].[ProductBlurb].[ProductID]) INNER JOIN [WS2].[dbo].[Manufacturer]  ON  [WS2].[dbo].[Manufacturer].[ManufacturerID]=[WS2].[dbo].[Product].[ManufacturerID]) INNER JOIN [WS2].[dbo].[Company]  ON  [WS2].[dbo].[Company].[CompanyID]=[WS2].[dbo].[Manufacturer].[ManufacturerID]) INNER JOIN [WS2].[dbo].[ForkVersion]  ON  [WS2].[dbo].[Fork].[ForkID]=[WS2].[dbo].[ForkVersion].[ForkID]) INNER JOIN [WS2].[dbo].[ProductVersion]  ON  [WS2].[dbo].[ProductVersion].[ProductVersionID]=[WS2].[dbo].[ForkVersion].[ProductVersionID])
WHERE ( [WS2].[dbo].[ForkVersion].[ForkID] IN
    (@ForkID1, @ForkID2, @ForkID3, @ForkID4, @ForkID5, @ForkID6, @ForkID7, @ForkID8, @ForkID9, @ForkID10, @ForkID11, @ForkID12, @ForkID13, @ForkID14, @ForkID15, @ForkID16, @ForkID17, @ForkID18, @ForkID19, @ForkID20, @ForkID21, @ForkID22, @ForkID23, @ForkID24, @ForkID25, @ForkID26, @ForkID27, @ForkID28)
AND ( ( ( [WS2].[dbo].[Product].[ActiveState] = @ActiveState29
AND [WS2].[dbo].[ProductVersion].[ActiveState] = @ActiveState30
AND [WS2].[dbo].[ProductVersion].[IsPartSpecific] = @IsPartSpecific31
AND [WS2].[dbo].[ForkVersion].[SteererTubeDiameter] = @SteererTubeDiameter32
AND ( [WS2].[dbo].[Product].[StyleCode] = @StyleCode33
OR [WS2].[dbo].[Product].[StyleCode] IN
    (SELECT [WS2].[dbo].[Fork].[AdditionalStyles] FROM [WS2].[dbo].[Fork]  WHERE [WS2].[dbo].[Fork].[ForkID] = [WS2].[dbo].[Fork].[ForkID])
AND [WS2].[dbo].[ForkVersion].[TravelMax] >= @TravelMax34
AND [WS2].[dbo].[ForkVersion].[TravelMax] <= @TravelMax35)))))'
, N'@ForkID1 int,@ForkID2 int,@ForkID3 int,@ForkID4 int,@ForkID5 int,@ForkID6 int,@ForkID7 int,@ForkID8 int,@ForkID9 int,@ForkID10 int,@ForkID11 int,@ForkID12 int,@ForkID13 int,@ForkID14 int,@ForkID15 int,@ForkID16 int,@ForkID17 int,@ForkID18 int,@ForkID19 int,@ForkID20 int,@ForkID21 int,@ForkID22 int,@ForkID23 int,@ForkID24 int,@ForkID25 int,@ForkID26 int,@ForkID27 int,@ForkID28 int,@ActiveState29 char(3),@ActiveState30 char(3),@IsPartSpecific31 bit,@SteererTubeDiameter32 decimal(4,2),@StyleCode33 char(2),@TravelMax34 decimal(5,2),@TravelMax35 decimal(5,2)', @ForkID1 = 4535, @ForkID2 = 4496, @ForkID3 = 4498, @ForkID4 = 4499, @ForkID5 = 4500, @ForkID6 = 4530, @ForkID7 = 4531, @ForkID8 = 4532, @ForkID9 = 5024, @ForkID10 = 5022, @ForkID11 = 4504, @ForkID12 = 5026, @ForkID13 = 5027, @ForkID14 = 4508, @ForkID15 = 4509, @ForkID16 = 4510, @ForkID17 = 4511, @ForkID18 = 5020, @ForkID19 = 5021, @ForkID20 = 4543, @ForkID21 = 4544, @ForkID22 = 4545, @ForkID23 = 4546, @ForkID24 = 4536, @ForkID25 = 4548, @ForkID26 = 4549, @ForkID27 = 4501, @ForkID28 = 4502, @ActiveState29 = 'Web', @ActiveState30 = 'Web', @IsPartSpecific31 = 0, @SteererTubeDiameter32 = 28.60, @StyleCode33 = 'XC', @TravelMax34 = 80.00, @TravelMax35 = 80.00
go
exec sp_executesql N'SELECT [WS2].[dbo].[Product].[ProductID], [WS2].[dbo].[Product].[ActiveState], [WS2].[dbo].[Product].[ProductTypeID], [WS2].[dbo].[Product].[StyleCode], [WS2].[dbo].[Product].[Religion], [WS2].[dbo].[Product].[ManufacturerID], [WS2].[dbo].[Product].[Model], [WS2].[dbo].[Product].[ModelYear], [WS2].[dbo].[Product].[Warranty], [WS2].[dbo].[Product].[Material], [WS2].[dbo].[Product].[MaterialDescription], [WS2].[dbo].[Product].[WeightLimit], [WS2].[dbo].[Product].[Discount], [WS2].[dbo].[Product].[SortOrder], [WS2].[dbo].[Product].[CreateDate], [WS2].[dbo].[Product].[UpdateDate] FROM [WS2].[dbo].[Product]  WHERE ( [WS2].[dbo].[Product].[ProductID] IN (@ProductID1, @ProductID2, @ProductID3, @ProductID4, @ProductID5, @ProductID6, @ProductID7, @ProductID8, @ProductID9, @ProductID10, @ProductID11, @ProductID12, @ProductID13, @ProductID14, @ProductID15, @ProductID16, @ProductID17, @ProductID18, @ProductID19, @ProductID20, @ProductID21, @ProductID22, @ProductID23, @ProductID24, @ProductID25, @ProductID26, @ProductID27, @ProductID28))', N'@ProductID1 int,@ProductID2 int,@ProductID3 int,@ProductID4 int,@ProductID5 int,@ProductID6 int,@ProductID7 int,@ProductID8 int,@ProductID9 int,@ProductID10 int,@ProductID11 int,@ProductID12 int,@ProductID13 int,@ProductID14 int,@ProductID15 int,@ProductID16 int,@ProductID17 int,@ProductID18 int,@ProductID19 int,@ProductID20 int,@ProductID21 int,@ProductID22 int,@ProductID23 int,@ProductID24 int,@ProductID25 int,@ProductID26 int,@ProductID27 int,@ProductID28 int', @ProductID1 = 4535, @ProductID2 = 4496, @ProductID3 = 4498, @ProductID4 = 4499, @ProductID5 = 4500, @ProductID6 = 4530, @ProductID7 = 4531, @ProductID8 = 4532, @ProductID9 = 5024, @ProductID10 = 5022, @ProductID11 = 4504, @ProductID12 = 5026, @ProductID13 = 5027, @ProductID14 = 4508, @ProductID15 = 4509, @ProductID16 = 4510, @ProductID17 = 4511, @ProductID18 = 5020, @ProductID19 = 5021, @ProductID20 = 4543, @ProductID21 = 4544, @ProductID22 = 4545, @ProductID23 = 4546, @ProductID24 = 4536, @ProductID25 = 4548, @ProductID26 = 4549, @ProductID27 = 4501, @ProductID28 = 4502
go
exec sp_executesql N'SELECT DISTINCT [WS2].[dbo].[ProductVersion].[ProductVersionID], [WS2].[dbo].[ProductVersion].[ProductID], [WS2].[dbo].[ProductVersion].[ActiveState], [WS2].[dbo].[ProductVersion].[Religion], [WS2].[dbo].[ProductVersion].[DistributorID], [WS2].[dbo].[ProductVersion].[DistributorNumber], [WS2].[dbo].[ProductVersion].[ManufacturerNumber], [WS2].[dbo].[ProductVersion].[IsPartSpecific], [WS2].[dbo].[ProductVersion].[OECostPrice], [WS2].[dbo].[ProductVersion].[OEPrice], [WS2].[dbo].[ProductVersion].[RPCostPrice], [WS2].[dbo].[ProductVersion].[RPPrice], [WS2].[dbo].[ProductVersion].[Discount], [WS2].[dbo].[ProductVersion].[Color], [WS2].[dbo].[ProductVersion].[ImageName], [WS2].[dbo].[ProductVersion].[MfrGrams], [WS2].[dbo].[ProductVersion].[WSGrams], [WS2].[dbo].[ProductVersion].[WSNotes] FROM (((((( [WS2].[dbo].[Product]  INNER JOIN [WS2].[dbo].[Fork]  ON  [WS2].[dbo].[Product].[ProductID]=[WS2].[dbo].[Fork].[ForkID]) LEFT JOIN [WS2].[dbo].[ProductBlurb]  ON  [WS2].[dbo].[Product].[ProductID]=[WS2].[dbo].[ProductBlurb].[ProductID]) INNER JOIN [WS2].[dbo].[Manufacturer]  ON  [WS2].[dbo].[Manufacturer].[ManufacturerID]=[WS2].[dbo].[Product].[ManufacturerID]) INNER JOIN [WS2].[dbo].[Company]  ON  [WS2].[dbo].[Company].[CompanyID]=[WS2].[dbo].[Manufacturer].[ManufacturerID]) INNER JOIN [WS2].[dbo].[ForkVersion]  ON  [WS2].[dbo].[Fork].[ForkID]=[WS2].[dbo].[ForkVersion].[ForkID]) INNER JOIN [WS2].[dbo].[ProductVersion]  ON  [WS2].[dbo].[ProductVersion].[ProductVersionID]=[WS2].[dbo].[ForkVersion].[ProductVersionID]) WHERE ( [WS2].[dbo].[ProductVersion].[ProductID] IN (@ProductID1, @ProductID2, @ProductID3, @ProductID4, @ProductID5, @ProductID6, @ProductID7, @ProductID8, @ProductID9, @ProductID10, @ProductID11, @ProductID12, @ProductID13, @ProductID14, @ProductID15, @ProductID16, @ProductID17, @ProductID18, @ProductID19, @ProductID20, @ProductID21, @ProductID22, @ProductID23, @ProductID24, @ProductID25, @ProductID26, @ProductID27, @ProductID28) AND ( ( ( [WS2].[dbo].[Product].[ActiveState] = @ActiveState29 AND [WS2].[dbo].[ProductVersion].[ActiveState] = @ActiveState30 AND [WS2].[dbo].[ProductVersion].[IsPartSpecific] = @IsPartSpecific31 AND [WS2].[dbo].[ForkVersion].[SteererTubeDiameter] = @SteererTubeDiameter32 AND ( [WS2].[dbo].[Product].[StyleCode] = @StyleCode33 OR [WS2].[dbo].[Product].[StyleCode] IN (SELECT [WS2].[dbo].[Fork].[AdditionalStyles] FROM [WS2].[dbo].[Fork]  WHERE [WS2].[dbo].[Fork].[ForkID] = [WS2].[dbo].[Fork].[ForkID]) AND [WS2].[dbo].[ForkVersion].[TravelMax] >= @TravelMax34 AND [WS2].[dbo].[ForkVersion].[TravelMax] <= @TravelMax35))))) ORDER BY [WS2].[dbo].[ProductVersion].[RPPrice] ASC,[WS2].[dbo].[ProductVersion].[MfrGrams] ASC', N'@ProductID1 int,@ProductID2 int,@ProductID3 int,@ProductID4 int,@ProductID5 int,@ProductID6 int,@ProductID7 int,@ProductID8 int,@ProductID9 int,@ProductID10 int,@ProductID11 int,@ProductID12 int,@ProductID13 int,@ProductID14 int,@ProductID15 int,@ProductID16 int,@ProductID17 int,@ProductID18 int,@ProductID19 int,@ProductID20 int,@ProductID21 int,@ProductID22 int,@ProductID23 int,@ProductID24 int,@ProductID25 int,@ProductID26 int,@ProductID27 int,@ProductID28 int,@ActiveState29 char(3),@ActiveState30 char(3),@IsPartSpecific31 bit,@SteererTubeDiameter32 decimal(4,2),@StyleCode33 char(2),@TravelMax34 decimal(5,2),@TravelMax35 decimal(5,2)', @ProductID1 = 4496, @ProductID2 = 4498, @ProductID3 = 4499, @ProductID4 = 4500, @ProductID5 = 4501, @ProductID6 = 4502, @ProductID7 = 4504, @ProductID8 = 4508, @ProductID9 = 4509, @ProductID10 = 4510, @ProductID11 = 4511, @ProductID12 = 4530, @ProductID13 = 4531, @ProductID14 = 4532, @ProductID15 = 4535, @ProductID16 = 4536, @ProductID17 = 4543, @ProductID18 = 4544, @ProductID19 = 4545, @ProductID20 = 4546, @ProductID21 = 4548, @ProductID22 = 4549, @ProductID23 = 5020, @ProductID24 = 5021, @ProductID25 = 5022, @ProductID26 = 5024, @ProductID27 = 5026, @ProductID28 = 5027, @ActiveState29 = 'Web', @ActiveState30 = 'Web', @IsPartSpecific31 = 0, @SteererTubeDiameter32 = 28.60, @StyleCode33 = 'XC', @TravelMax34 = 80.00, @TravelMax35 = 80.00
go
exec sp_executesql N'SELECT [WS2].[dbo].[ProductBlurb].[ProductID], [WS2].[dbo].[ProductBlurb].[LongBlurb], [WS2].[dbo].[ProductBlurb].[ShortBlurb] FROM [WS2].[dbo].[ProductBlurb]  WHERE ( [WS2].[dbo].[ProductBlurb].[ProductID] IN (@ProductID1, @ProductID2, @ProductID3, @ProductID4, @ProductID5, @ProductID6, @ProductID7, @ProductID8, @ProductID9, @ProductID10, @ProductID11, @ProductID12, @ProductID13, @ProductID14, @ProductID15, @ProductID16, @ProductID17, @ProductID18, @ProductID19, @ProductID20, @ProductID21, @ProductID22, @ProductID23, @ProductID24, @ProductID25, @ProductID26, @ProductID27, @ProductID28))', N'@ProductID1 int,@ProductID2 int,@ProductID3 int,@ProductID4 int,@ProductID5 int,@ProductID6 int,@ProductID7 int,@ProductID8 int,@ProductID9 int,@ProductID10 int,@ProductID11 int,@ProductID12 int,@ProductID13 int,@ProductID14 int,@ProductID15 int,@ProductID16 int,@ProductID17 int,@ProductID18 int,@ProductID19 int,@ProductID20 int,@ProductID21 int,@ProductID22 int,@ProductID23 int,@ProductID24 int,@ProductID25 int,@ProductID26 int,@ProductID27 int,@ProductID28 int', @ProductID1 = 4496, @ProductID2 = 4498, @ProductID3 = 4499, @ProductID4 = 4500, @ProductID5 = 4501, @ProductID6 = 4502, @ProductID7 = 4504, @ProductID8 = 4508, @ProductID9 = 4509, @ProductID10 = 4510, @ProductID11 = 4511, @ProductID12 = 4530, @ProductID13 = 4531, @ProductID14 = 4532, @ProductID15 = 4535, @ProductID16 = 4536, @ProductID17 = 4543, @ProductID18 = 4544, @ProductID19 = 4545, @ProductID20 = 4546, @ProductID21 = 4548, @ProductID22 = 4549, @ProductID23 = 5020, @ProductID24 = 5021, @ProductID25 = 5022, @ProductID26 = 5024, @ProductID27 = 5026, @ProductID28 = 5027
go
exec sp_executesql N'SELECT [WS2].[dbo].[Manufacturer].[ManufacturerID], [WS2].[dbo].[Manufacturer].[MountainBlurb], [WS2].[dbo].[Manufacturer].[RoadBlurb] FROM [WS2].[dbo].[Manufacturer]  WHERE ( [WS2].[dbo].[Manufacturer].[ManufacturerID] IN (@ManufacturerID1, @ManufacturerID2, @ManufacturerID3, @ManufacturerID4, @ManufacturerID5, @ManufacturerID6, @ManufacturerID7, @ManufacturerID8, @ManufacturerID9, @ManufacturerID10, @ManufacturerID11, @ManufacturerID12, @ManufacturerID13, @ManufacturerID14, @ManufacturerID15, @ManufacturerID16, @ManufacturerID17, @ManufacturerID18, @ManufacturerID19, @ManufacturerID20, @ManufacturerID21, @ManufacturerID22, @ManufacturerID23, @ManufacturerID24, @ManufacturerID25, @ManufacturerID26, @ManufacturerID27, @ManufacturerID28))', N'@ManufacturerID1 int,@ManufacturerID2 int,@ManufacturerID3 int,@ManufacturerID4 int,@ManufacturerID5 int,@ManufacturerID6 int,@ManufacturerID7 int,@ManufacturerID8 int,@ManufacturerID9 int,@ManufacturerID10 int,@ManufacturerID11 int,@ManufacturerID12 int,@ManufacturerID13 int,@ManufacturerID14 int,@ManufacturerID15 int,@ManufacturerID16 int,@ManufacturerID17 int,@ManufacturerID18 int,@ManufacturerID19 int,@ManufacturerID20 int,@ManufacturerID21 int,@ManufacturerID22 int,@ManufacturerID23 int,@ManufacturerID24 int,@ManufacturerID25 int,@ManufacturerID26 int,@ManufacturerID27 int,@ManufacturerID28 int', @ManufacturerID1 = 166, @ManufacturerID2 = 166, @ManufacturerID3 = 166, @ManufacturerID4 = 166, @ManufacturerID5 = 166, @ManufacturerID6 = 166, @ManufacturerID7 = 184, @ManufacturerID8 = 184, @ManufacturerID9 = 184, @ManufacturerID10 = 184, @ManufacturerID11 = 184, @ManufacturerID12 = 185, @ManufacturerID13 = 185, @ManufacturerID14 = 185, @ManufacturerID15 = 186, @ManufacturerID16 = 186, @ManufacturerID17 = 207, @ManufacturerID18 = 207, @ManufacturerID19 = 207, @ManufacturerID20 = 207, @ManufacturerID21 = 207, @ManufacturerID22 = 207, @ManufacturerID23 = 184, @ManufacturerID24 = 184, @ManufacturerID25 = 184, @ManufacturerID26 = 184, @ManufacturerID27 = 184, @ManufacturerID28 = 184
go
exec sp_executesql N'SELECT [WS2].[dbo].[Company].[CompanyID], [WS2].[dbo].[Company].[IsActive], [WS2].[dbo].[Company].[Abbreviation], [WS2].[dbo].[Company].[CompanyName], [WS2].[dbo].[Company].[Phone], [WS2].[dbo].[Company].[Fax], [WS2].[dbo].[Company].[Email], [WS2].[dbo].[Company].[ImageName], [WS2].[dbo].[Company].[TimeZone], [WS2].[dbo].[Company].[Description], [WS2].[dbo].[Company].[AddressID], [WS2].[dbo].[Company].[ShippingCost] FROM [WS2].[dbo].[Company]  WHERE ( [WS2].[dbo].[Company].[CompanyID] IN (@CompanyID1, @CompanyID2, @CompanyID3, @CompanyID4, @CompanyID5))', N'@CompanyID1 int,@CompanyID2 int,@CompanyID3 int,@CompanyID4 int,@CompanyID5 int', @CompanyID1 = 166, @CompanyID2 = 184, @CompanyID3 = 185, @CompanyID4 = 186, @CompanyID5 = 207
go

OK, I'm getting somewhere with this. I just figured out that the StyleCode sub-query is causing my problem.

OR [WS2].[dbo].[Product].[StyleCode] IN
    (SELECT [WS2].[dbo].[Fork].[AdditionalStyles] FROM [WS2].[dbo].[Fork]  WHERE [WS2].[dbo].[Fork].[ForkID] = [WS2].[dbo].[Fork].[ForkID])

I would really like this sub-query to look like this.

OR @StyleCode IN
    (SELECT [WS2].[dbo].[Fork].[AdditionalStyles] FROM [WS2].[dbo].[Fork]  WHERE [WS2].[dbo].[Fork].[ForkID] = [WS2].[dbo].[Fork].[ForkID])

Is this possible?

I've to see the prefetch path setup, it's a prefetch path generated query I think, so it's a little hard to say what's needed.

Otis wrote:

I've to see the prefetch path setup, it's a prefetch path generated query I think, so it's a little hard to say what's needed.

Otis, here is a link to the thread I have going about this sub-query problem. The prefetch path and all related code is here. Would you please take a look and let me know what you think?

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=5345

thanks very much for the time.

tprohas wrote:

OK, I'm getting somewhere with this. I just figured out that the StyleCode sub-query is causing my problem.

OR [WS2].[dbo].[Product].[StyleCode] IN
    (SELECT [WS2].[dbo].[Fork].[AdditionalStyles] FROM [WS2].[dbo].[Fork]  WHERE [WS2].[dbo].[Fork].[ForkID] = [WS2].[dbo].[Fork].[ForkID])

I would really like this sub-query to look like this.

OR @StyleCode IN
    (SELECT [WS2].[dbo].[Fork].[AdditionalStyles] FROM [WS2].[dbo].[Fork]  WHERE [WS2].[dbo].[Fork].[ForkID] = [WS2].[dbo].[Fork].[ForkID])

Is this possible?

Isn't that second query the same as:

OR EXISTS 
    (SELECT * FROM [WS2].[dbo].[Fork]  WHERE [WS2].[dbo].[Fork].[ForkID] = [WS2].[dbo].[Fork].[ForkID] AND [WS2].[dbo].[Fork].[AdditionalStyles] = @StyleCode)

?

An exists query is doable with a FieldCompareSetPredicate.

The query that you gave me doesn't return the same results as the one that I want to use. What I'm trying to do is find the variable @StyleCode IN The Fork.AdditionalStyles column. I'm going to post more code, schema and such in a little while for this to give you more information.

I'm assuming at this point that using a parameter in place of the field for what being searched for in the IN statement is not possible.