Multiple Indexes - SQL Server Forums

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 2005 Forums

Other SQL Server Topics (2005)

Multiple Indexes

Author

Topic

sduffy77
Starting Member

18 Posts

Posted - 2008-04-15 : 10:22:08

I have a stored procedure that we use to search our inventory.

The sproc is using dynamic sql and executing a parameterized.

It generates the sql string based on all the parameters passed in.

I'm trying to figure out how I should set up the indexes on the table(s) being queried.

There are 12 different fields possible in the where clause and 7 different order by's.

I heard that SQL 2005 might hash together multiple indexes so should I create an index for each of these fields?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-15 : 13:12:21

We'd have to see the code in order to provide an answer.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-15 : 13:40:00

Please provide your stored procedure so that it will helpful to give quick and accurate soln.

sduffy77
Starting Member

18 Posts

Posted - 2008-04-15 : 13:47:10

Ah Sorry,
Here's the sproc:


CREATE PROCEDURE [dbo].[sto_SearchInventoryV2]
	(	
	@Where varchar(8000),
	@Zip varchar(5),
	@Radius int,
	@RowIndex int,
	@PageSize int,
	@SortExp varchar(20),
	@SortDir varchar(4),
	@SessionID varchar(50),

	@VEH_Condition varchar(4)=NULL,
	@BEGINVEH_year varchar(4)=NULL,
	@ENDVEH_year varchar(4)=NULL,
	@VEH_make varchar(100)=NULL,
	@VEH_model varchar(100)=NULL,
	@MINVEH_price float=0,
	@MAXVEH_price float=0,
	@NOVEH_price bit=0,
	@VEH_Mileage int=0,
	@VEH_Body varchar(100)=NULL,
	@VEH_Trans varchar(50)=NULL,
	@VEH_ExtColor varchar(100)=NULL,
	@VEH_EngineTxt varchar(100)=NULL,
	@VEH_DealerID int=0,
	@VEH_DealerLotID int=0,
	@VEH_BodyStyle1 varchar(100)=null,
	@VEH_BodyStyle2 varchar(100)=null,
	@VEH_BodyStyle3 varchar(100)=null,
	@VEH_BodyStyle4 varchar(100)=null,
	@VEH_BodyStyle5 varchar(100)=null,
	@VEH_BodyStyle6 varchar(100)=null,
	@VEH_BodyStyle7 varchar(100)=null,
	@VEH_BodyStyle8 varchar(100)=null
	)
AS
BEGIN

SET NOCOUNT ON

	Declare @sortString as varchar(25),
		@paramlist  nvarchar(4000),
		@sqlstr as nvarchar(4000)


	SET @sortString = @SortExp + ' ' + @SortDir

	SELECT @paramlist = '@sortString varchar(25), 
			@RowIndex varchar(10), 
			@PageSize varchar(10), 
			@VEH_Condition varchar(4),
			@BEGINVEH_year varchar(4),
			@ENDVEH_year varchar(4),
			@VEH_make varchar(100),
			@VEH_model varchar(100),
			@MINVEH_price float,
			@MAXVEH_price float,
			@NOVEH_price bit,
			@VEH_Mileage int,
			@VEH_Body varchar(100),
			@VEH_Trans varchar(50),
			@VEH_ExtColor varchar(100),
			@VEH_EngineTxt varchar(100),
			@VEH_DealerID int,
			@VEH_DealerLotID int,
			@VEH_BodyStyle1 varchar(100),
			@VEH_BodyStyle2 varchar(100),
			@VEH_BodyStyle3 varchar(100),
			@VEH_BodyStyle4 varchar(100),
			@VEH_BodyStyle5 varchar(100),
			@VEH_BodyStyle6 varchar(100),
			@VEH_BodyStyle7 varchar(100),
			@VEH_BodyStyle8 varchar(100)'
		SELECT @sqlstr = 'select VEH.[VEH_Rank],VEH.[VEH_ID]
		  ,VEH.[VEH_VIN]
		  ,VEH.[VEH_DealerLotNumber]
		  ,VEH.[VEH_Year]
		  ,VEH.[VEH_Mileage]
		  ,VEH.[VEH_EngineSize]
		  ,VEH.[VEH_Make]
		  ,VEH.[VEH_Model]
		  ,VEH.[VEH_Trim]
		  ,VEH.[VEH_Body]
		  ,VEH.[VEH_Trans]
		  ,VEH.[VEH_ExtColor]
		  ,VEH.[VEH_Price]
		  ,VEH.[VFT_ID]
		  ,VEH.[VEH_IMG]
		  ,VEH.[VEH_IsActive]
		  ,VEH.[VEH_DealerID]
		  ,VEH.[VEH_DealerAddress1]
		  ,VEH.[VEH_DealerAddress2]
		  ,VEH.[VEH_DealerCity]
		  ,VEH.[VEH_DealerState]
		  ,VEH.[VEH_DealerZip]
		  ,VEH.[VEH_DealerHomepage]
		  ,VEH.[VEH_DealerEmail]
		  ,VEH.[VEH_DealerFax]
		  ,VEH.[VEH_EngineTxt]
		  ,VEH.[VEH_DealerName]
		  ,VEH.[VEH_DealerPhone]
		  ,VEH.[VEH_ImageInfo]
		  ,VEH.[VEH_DealerLotID]
			,VEH.VEH_DefaultImage
			,VEH.VEH_DefaultThumb
			,VEH.cfs_VIN
			,VEH.CFS_OneOwnerFlag
		, myCount.count from (
		select  ROW_NUMBER() OVER (ORDER BY @sortString, VFT_DisplayOrder ASC) AS ''VEH_RANK'', 
			TMP.[VEH_ID]
		  ,TMP.[VEH_VIN]
		  ,TMP.[VEH_DealerLotNumber]
		  ,TMP.[VEH_Year]
		  ,TMP.[VEH_Mileage]
		  ,TMP.[VEH_EngineSize]
		  ,TMP.[VEH_Make]
		  ,TMP.[VEH_Model]
		  ,TMP.[VEH_Trim]
		  ,TMP.[VEH_Body]
		  ,TMP.[VEH_Trans]
		  ,TMP.[VEH_ExtColor]
		  ,TMP.[VEH_Price]
		  ,TMP.[VFT_ID]
		  ,TMP.[VEH_IMG]
		  ,TMP.[VEH_IsActive]
		  ,TMP.[VEH_DealerID]
		  ,TMP.[VEH_DealerAddress1]
		  ,TMP.[VEH_DealerAddress2]
		  ,TMP.[VEH_DealerCity]
		  ,TMP.[VEH_DealerState]
		  ,TMP.[VEH_DealerZip]
		  ,TMP.[VEH_DealerHomepage]
		  ,TMP.[VEH_DealerEmail]
		  ,TMP.[VEH_DealerFax]
		  ,TMP.[VEH_EngineTxt]
		  ,TMP.[VEH_DealerName]
		  ,TMP.[VEH_DealerPhone]
		  ,TMP.[VEH_ImageInfo]
		  ,TMP.[VEH_DealerLotID]
			,TMP.VEH_DefaultImage
			,TMP.VEH_DefaultThumb
			,TMP.cfs_VIN
			,TMP.CFS_OneOwnerFlag
		 from [dbo].[tblVehicleInventory_VEH] TMP
		where 1 = 1'

--BEGIN Build WhereClause
		--Condition
		IF @VEH_Condition IS NOT NULL AND @VEH_Condition <> ''
			Select @sqlstr = @sqlstr + ' AND VEH_Condition = @VEH_Condition'
		--Year Beginning
		IF @BEGINVEH_year <> 0
			Select @sqlstr = @sqlstr + ' AND VEH_year >= @BEGINVEH_year'
		--Year Ending
		IF @ENDVEH_year <> 0
			Select @sqlstr = @sqlstr + ' AND VEH_year <= @ENDVEH_year'
		--Make
		IF @VEH_make IS NOT NULL AND @VEH_make <> ''
			Select @sqlstr = @sqlstr + ' AND VEH_make = @VEH_make'
		--Model
		IF @VEH_model IS NOT NULL AND @VEH_model <> ''
			Select @sqlstr = @sqlstr + ' AND VEH_model LIKE + ''%'' + @VEH_model + ''%'''
		--PRICE MIN
		IF @MINVEH_price <> 0
		BEGIN
			Select @sqlstr = @sqlstr + ' AND'
			IF @NOVEH_price <> 0
				 Select @sqlstr = @sqlstr + ' ('			
			Select @sqlstr = @sqlstr + ' VEH_price >= @MINVEH_price'
		END
		--PRICE MAX
		IF @MAXVEH_price <> 0
		BEGIN
			Select @sqlstr = @sqlstr + ' AND'
			IF @NOVEH_price <> 0 AND @MINVEH_price = 0
				 Select @sqlstr = @sqlstr + ' ('
			Select @sqlstr = @sqlstr + ' VEH_price <= @MAXVEH_price'
		END
		--No Price
		IF @NOVEH_price <> 0
		BEGIN
			IF @MAXVEH_price <> 0 OR @MINVEH_price <> 0
				Select @sqlstr = @sqlstr + ' OR VEH_price IS NULL )'
		END
		--Mileage
		IF @VEH_Mileage <> 0
		BEGIN
			Select @sqlstr = @sqlstr + ' AND VEH_Mileage'
			IF @VEH_Mileage <> 100000
				Select @sqlstr = @sqlstr + ' < @VEH_Mileage'
			ELSE
				Select @sqlstr = @sqlstr + ' > @VEH_Mileage'
		END
		--Doors
		if @VEH_Body IS NOT NULL AND @VEH_Body <> '' AND @VEH_Body <> '0'
			Select @sqlstr = @sqlstr + ' AND VEH_Body LIke + ''%'' + @VEH_Body + ''%'''
		--Transmission
		IF @VEH_Trans IS NOT NULL AND @VEH_Trans <> ''
			Select @sqlstr = @sqlstr + ' AND VEH_Trans LIke + ''%'' + @VEH_Trans + ''%'''
		--Exterior Color
		IF @VEH_ExtColor IS NOT NULL AND @VEH_ExtColor <> ''
			Select @sqlstr = @sqlstr + ' AND VEH_ExtColor LIke + ''%'' + @VEH_ExtColor + ''%'''
		--Engine Type
		IF @VEH_EngineTxt IS NOT NULL AND @VEH_EngineTxt <> ''
			Select @sqlstr = @sqlstr + ' AND VEH_EngineTxt LIke + ''%'' + @VEH_EngineTxt + ''%'''
		--Dealer ID
		IF @VEH_DealerID <> 0
			Select @sqlstr = @sqlstr + ' AND VEH_DealerID = @VEH_DealerID'
		--DealerLot ID
		IF @VEH_DealerLotID <> 0
			Select @sqlstr = @sqlstr + ' AND VEH_DealerLotID = @VEH_DealerLotID'
		--Body Styles	
		IF COALESCE(@VEH_BodyStyle1,@VEH_BodyStyle2,@VEH_BodyStyle3,@VEH_BodyStyle4,@VEH_BodyStyle5,@VEH_BodyStyle6,@VEH_BodyStyle7,@VEH_BodyStyle8) IS NOT NULL
		BEGIN
			Select @sqlstr = @sqlstr + ' AND ( '

			IF @VEH_BodyStyle1 IS NOT NULL AND @VEH_BodyStyle1 <> ''
			BEGIN
				Select @sqlstr = @sqlstr + ' VEH_Body LIke + ''%'' + @VEH_BodyStyle1 + ''%'''
				IF COALESCE(@VEH_BodyStyle2,@VEH_BodyStyle3,@VEH_BodyStyle4,@VEH_BodyStyle5,@VEH_BodyStyle6,@VEH_BodyStyle7,@VEH_BodyStyle8) IS NOT NULL
					Select @sqlstr = @sqlstr + ' OR'
			END

			IF @VEH_BodyStyle2 IS NOT NULL AND @VEH_BodyStyle2 <> ''
			BEGIN
				Select @sqlstr = @sqlstr + ' VEH_Body LIke + ''%'' + @VEH_BodyStyle2 + ''%'''
				IF COALESCE(@VEH_BodyStyle3,@VEH_BodyStyle4,@VEH_BodyStyle5,@VEH_BodyStyle6,@VEH_BodyStyle7,@VEH_BodyStyle8) IS NOT NULL
					Select @sqlstr = @sqlstr + ' OR'
			END

			IF @VEH_BodyStyle3 IS NOT NULL AND @VEH_BodyStyle3 <> ''
			BEGIN
				Select @sqlstr = @sqlstr + ' VEH_Body LIke + ''%'' + @VEH_BodyStyle3 + ''%'''
				IF COALESCE(@VEH_BodyStyle4,@VEH_BodyStyle5,@VEH_BodyStyle6,@VEH_BodyStyle7,@VEH_BodyStyle8) IS NOT NULL
					Select @sqlstr = @sqlstr + ' OR'
			END

			IF @VEH_BodyStyle4 IS NOT NULL AND @VEH_BodyStyle4 <> ''
			BEGIN
				Select @sqlstr = @sqlstr + ' VEH_Body LIke + ''%'' + @VEH_BodyStyle4 + ''%'''
				IF COALESCE(@VEH_BodyStyle5,@VEH_BodyStyle6,@VEH_BodyStyle7,@VEH_BodyStyle8) IS NOT NULL
					Select @sqlstr = @sqlstr + ' OR'
			END

			IF @VEH_BodyStyle5 IS NOT NULL AND @VEH_BodyStyle5 <> ''
			BEGIN
				Select @sqlstr = @sqlstr + ' VEH_Body LIke + ''%'' + @VEH_BodyStyle5 + ''%'''
				IF COALESCE(@VEH_BodyStyle6,@VEH_BodyStyle7,@VEH_BodyStyle8) IS NOT NULL
					Select @sqlstr = @sqlstr + ' OR'
			END

			IF @VEH_BodyStyle6 IS NOT NULL AND @VEH_BodyStyle6 <> ''
			BEGIN
				Select @sqlstr = @sqlstr + ' VEH_Body LIke + ''%'' + @VEH_BodyStyle6 + ''%'''
				IF COALESCE(@VEH_BodyStyle7,@VEH_BodyStyle8) IS NOT NULL
					Select @sqlstr = @sqlstr + ' OR'
			END

			IF @VEH_BodyStyle7 IS NOT NULL AND @VEH_BodyStyle7 <> ''
			BEGIN
				Select @sqlstr = @sqlstr + ' VEH_Body LIke + ''%'' + @VEH_BodyStyle7 + ''%'''
				IF ISNULL(@VEH_BodyStyle8, 0) <> 0
					Select @sqlstr = @sqlstr + ' OR'
			END

			IF @VEH_BodyStyle8 IS NOT NULL AND @VEH_BodyStyle8 <> ''
				Select @sqlstr = @sqlstr + ' VEH_Body LIke + ''%'' + @VEH_BodyStyle8 + ''%'''

			Select @sqlstr = @sqlstr + ' )'
		END
--END Build WhereClause

		Select @sqlstr = @sqlstr + ' AND VEH_IsActive = 1 ) VEH 
			
			where VEH_Rank > @RowIndex AND VEH_Rank <= @RowIndex + @PageSize
			AND VEH_IsActive = 1'
		
		print @sqlstr

		EXEC sp_executesql @sqlstr, @paramlist,
			@sortString,@RowIndex, @PageSize, @VEH_Condition,@BEGINVEH_year,@ENDVEH_year,@VEH_make,
			@VEH_model,@MINVEH_price,@MAXVEH_price,@NOVEH_price,@VEH_Mileage,@VEH_Body,@VEH_Trans,
			@VEH_ExtColor,@VEH_EngineTxt,@VEH_DealerID,@VEH_DealerLotID,
			@VEH_BodyStyle1,@VEH_BodyStyle2,@VEH_BodyStyle3,@VEH_BodyStyle4,@VEH_BodyStyle5,
			@VEH_BodyStyle6,@VEH_BodyStyle7,@VEH_BodyStyle8

END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-15 : 13:49:36

It seems to me that you should be using the COALESCE trick to achieve a dynamic WHERE clause without dynamic SQL. Search the articles here for COALESCE dynamic WHERE clause article.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

sduffy77
Starting Member

18 Posts

Posted - 2008-04-15 : 14:13:59

Ok, but my original question was how should I set up my indexes.

Say I have a Table Named tblA with 3 fields (ID, Name, Date)

Say I have an sproc that selected from this table either by Name, ID, and/or Date.
The given permutations could be:
where ID = @ID
where Name = @Name
Where Date = @Date
where ID = @ID AND Name = @Name
where ID = @ID AND Date = @Date
where Name = @Name AND Date = @Date

Should I create 1 index for each field?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-15 : 14:16:38

For your simple example, I would create indexes on ID, Name, and Date. You may also want to consider an index on (ID, Name), (ID, Date), and (Name, Date). But you would need to determine the selectivity of the data in ID, Name, and Date first. You would also need to examine the execution plans to determine which indexes work best.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

sduffy77
Starting Member

18 Posts

Posted - 2008-04-15 : 14:26:52

Ahh great. So Sql doesn't hash together multiple indexes, for instance, the ID index and the Name index when the where is "ID = @ID AND Name = @Name".

I would have to make an index on every single permutation...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources