Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 confused about Exec Plan

Author  Topic 

DBASlut
Yak Posting Veteran

71 Posts
Posted - 2006-04-20 : 17:10:24
vehicle records =2047
package records =95068

select * from package p inner join
vehicle v on p.trimid = v.[Trim ID]
where p.trimid=12083 and v.[trim id]=12083
Execute Plan:
bookmark lookup =22% (package)
bookmark lookup =8% (vehicle)
index seek = 15% (package)
index scan = 55% (vehicle)


select * from package p inner join
vehicle v on p.trimid = v.[Trim ID]
where p.trimid=12083
Execute Plan:
hash match/inner join = 8%
bookmark lookup =44% (package)
index seek = 1% (package)
table scan = 47% (vehicle)

Both will give me the same result set, but which one is more optimized?
I did the 1st one to use the indexes, but is it really more efficient? I mean i'm using the ,where p.trimid=12083 and v.[trim id]=12083, just to use the 2 indexes.
The 2nd one uses a table scan because the vehicle table is fairly small.
Which one would you use and why?

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2006-04-20 : 17:17:00
Could you post the DDL for both tables including the indexes?

Tara Kizer
aka tduggan
Go to Top of Page

DBASlut
Yak Posting Veteran

71 Posts
Posted - 2006-04-20 : 17:23:21
Tara,
vehicle was imported in and it has 133 columns, all columns defined as VARCHAR (255) ouch
composite index on [Manufacturer Name], [Model Year], [Trim ID]

package:
CREATE TABLE [package] (
[ModelID] [int] NULL ,
[TrimID] [int] NULL ,
[PkgID] [int] NULL ,
[PkgCode] [varchar] (8) NULL ,
[PkgTitle] [varchar] (50) NULL ,
[PkgCatStrings] [varchar] (128) NULL ,
[PKGCategoryNumbers] [varchar] (59) NULL ,
[PriceSortOrder] [varchar] (2) NULL ,
[PkgPriceMSRP] [varchar] (5) NULL ,
[PkgPriceDlrInv] [varchar] (5) NULL ,
[GeneralLogic] [varchar] (130) NULL ,
[PriceLogic] [varchar] (173) NULL
) ON [PRIMARY]
index on [TrimID]
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2006-04-20 : 17:29:52
So there is an index on TrimID in the package table, but is there one in the vehicle table? Can you convert the TrimID column in the vehicle table to int?

Could you post the actual DDL for both tables plus the actual DDL for the indexes on both tables? This will help us help you as it allows us to create your environment on our machines.

Tara Kizer
aka tduggan
Go to Top of Page

DBASlut
Yak Posting Veteran

71 Posts
Posted - 2006-04-20 : 17:46:51
CREATE TABLE [vehicle] (
[Trim ID] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Trim Name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Trim Photo] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Marketing Division Name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Manufacturer Name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Model Name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Model Year] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Base Price MSRP] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Destination Charge] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Package Required] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Standard Engine] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Standard Transmission] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Doors Standard] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EPA Class] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Company Country] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Sales Volume] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Where Built] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Curb Weight MT] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Curb Weight AT] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Ground Clearance] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Height] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Length] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Top Type Standard] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Top Type Optional] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Track Front] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Track Rear] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Weight Distribution AT Front] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Weight Distribution AT Rear] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Weight Distribution MT Front] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Weight Distribution MT Rear] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Wheelbase] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Width] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Cargo Volume EPA] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Cargo Volume MFR] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fuel Capacity] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Passenger Volume EPA] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Passenger Volume MFR] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Seating Standard] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Seating Optional] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Tow Capacity Standard] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Tow Capacity Maximum] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Headroom Front] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Headroom Row 2] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Hiproom Front] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Hiproom Row 2] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Legroom Front] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Legroom Row 2] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Shoulder Room Front] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Shoulder Room Row 2] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Brakes Front] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Brakes Rear] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Driveline] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Driveline Opt] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Steering] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Steering Diameter Left] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Steering Diameter Right] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Suspension Front] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Suspension Rear] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Basic Time Months] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Basic Miles] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Powertrain Time Months] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Powertrain Miles] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Rust Time Months] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Rust Miles] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Engine Name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Cylinders] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Valves] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Valve Configuration] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Displacement CC] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Displacement CI] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Bore] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Stroke] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Bore X Stroke] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Compression] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Horsepower] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RPM at Peak Horsepower] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Horsepower @ RPM] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Torque Feet Per Pound] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RPM at Peak Torque] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Torque @ RPM] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fuel System] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fuel Type] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Turbo] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Transmission Speeds] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Transmission Automatic Manual] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Overdrive Transmission] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Manufacturer Transmission Code] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fuel Economy City] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fuel Economy Highway] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Combined Fuel Economy] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Final Drive Ratio] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Final Drive Ratio Opt] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Supercharged] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BasePriceMSRP] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Dealer Invoice] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DestinationCharge] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Gas Guzzler Tax] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Box Cargo Height] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Box Cargo Length] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Box Cargo Width Wheel] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Box Cargo Width Wall] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GVWR Standard] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GVWR Maximum] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Payload Standard] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Payload Maximum] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Tire Type] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Tire Width] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Tire Aspect Ratio] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Tire Construction] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Tire Wheel Diameter] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Seating Maximum] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Doors Maximum] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Headroom Row 3] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Hiproom Row 3] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Legroom Row 3] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Shoulder Room Row 3] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ModelPhotoName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TrimPhotoName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Crash Test Rating] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PowerToWeightRatio] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CruisingRange] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ElecHorsePowerBHP] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ElecHorsePowerRPM] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ElecTorqueFtLBs] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ElecTorqueRPM] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BatteryOutput] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Model Mnemonic] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Unique File Extension] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Segment Code 1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Segment Code 2] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Segment Code 3] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Segment Description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Model ID] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE INDEX [IX_VehicleMfgYearTrimID] ON [dbo].[vehicle]([Manufacturer Name], [Model Year], [Trim ID]) ON [PRIMARY]
GO

CREATE TABLE [package] (
[ModelID] [int] NULL ,
[TrimID] [int] NULL ,
[PkgID] [int] NULL ,
[PkgCode] [varchar] (8) NULL ,
[PkgTitle] [varchar] (50) NULL ,
[PkgCatStrings] [varchar] (128) NULL ,
[PKGCategoryNumbers] [varchar] (59) NULL ,
[PriceSortOrder] [varchar] (2) NULL ,
[PkgPriceMSRP] [varchar] (5) NULL ,
[PkgPriceDlrInv] [varchar] (5) NULL ,
[GeneralLogic] [varchar] (130) NULL ,
[PriceLogic] [varchar] (173) NULL
) ON [PRIMARY]
GO
CREATE INDEX [IX_VehicleMfgYearTrimID] ON [dbo].[vehicle]([Manufacturer Name], [Model Year], [Trim ID]) ON [PRIMARY]
GO
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2006-04-20 : 17:57:03
You don't seem to have any indexes on the package table, at least that's what the DDL shows. You need to create a new index solely on TrimID as that column is too far down the composite index for SQL Server to use. This index should exist on both tables.

Execution plans are identical for both on my machine.

Tara Kizer
aka tduggan
Go to Top of Page

DBASlut
Yak Posting Veteran

71 Posts
Posted - 2006-04-20 : 18:19:31
Duh, Tara so sorry..
I did as you suggested, made teh [trim id] in the vehicle table an INT and created an index on both tables. Now the second query:

select * from package p inner join
vehicle v on p.trimid = v.[Trim ID]
where p.trimid=12083

is using Index Seeks/Scan on both tables vs. Table Scan on 1 and Index Seek on the other.

I'm going to go home, too much work already. Thanks sweetie..
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2006-04-20 : 18:21:16
Run DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS, then rerun.

Tara Kizer
aka tduggan
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site