current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 4 down vote favorite

I have the following columns in my database table (Medicines).

ID bigint, 
MedicineName nvarchar(50), 
BrandName nvarchar(50), 
MedicineCode nvarchar(20),
and price,quantity.

I am making a stored procedure with this query:

create proc searchmedicine
@name nvarchar(50)=null,@brand nvarchar(50)=null, @code nvarchar(20)=null
as
select * from Medicines
where MedicineName= Case WHEN @name IS NOT NULL THEN @name ELSE MedicineName END
AND BrandName=Case WHEN @brand IS NOT NULL THEN @brand ELSE BrandName END
AND MedicineCode=Case WHEN @code IS NOT NULL THEN @code ELSE MedicineCode END

Now I am confused which non-clustered index is more suitable to help optimize the query: composite or single column?

share|improve this question

migrated from stackoverflow.com May 20 '13 at 12:39

This question came from our site for professional and enthusiast programmers.
2  
Not related to indexes, but your proc has an issue. Since none of your input parameters have default values, they can't be null. That makes your select query overengineered. –  Dan Bracuk May 20 '13 at 12:07
    
sorry i forgot to write default values. in actual there are default values. –  aliraza iftikhar May 20 '13 at 12:13
    
thanks for reminding me :) –  aliraza iftikhar May 20 '13 at 12:14
    
I would be surprised if there were any indexes that could be used by that stored procedure. –  Neil May 20 '13 at 12:39
3  
@Dan the default values just specify what happens if a parameter is not specified. You can still explicitly pass NULL even if no defaults are supplied. –  Aaron Bertrand♦ May 20 '13 at 13:32

2 Answers 2

up vote 3 down vote accepted

Using SELECT * is bad practice especially in a stored procedure. Even though you have a WHERE clause to filter the rows returned, I would explicitly state the columns.

As for indexes you will probably have to do the tuning yourself by looking at the execution plan for each type of index applied. However I would have a clustered index on ID and 1 non clustered index on MedicineName, BrnadName, MedicineCode since it is them 3 columns you are selecting from. 

CREATE NONCLUSTERED INDEX IX_Medicines_MedicineBrands ON Medicines (MedicineName, BrandName, MedicineCode)

I'd then personally include the execution plan and see how your stored procedure performs compared to having the following indexes:

CREATE NONCLUSTERED INDEX IX_Medicines_MedicineCode ON Medicines (MedicineCode)

CREATE NONCLUSTERED INDEX IX_Medicines_MedicineName ON Medicines (MedicineName)

CREATE NONCLUSTERED INDEX IX_Medicines_BrandName ON Medicines (BrandName)
share|improve this answer
    
thanks for suggestion. i know select * is bad practice but it is my requirement in this query i needed all the columns. i am just confused which index is suitable to make this query work efficiently. and i know execution plan can help me. but i am not familiar with execution plan hope so you can give me more better suggestion/solution. –  aliraza iftikhar May 20 '13 at 12:03
    
thanks Darren Davies. hope so it will help me. –  aliraza iftikhar May 20 '13 at 12:05
    
@alirazaiftikhar - it would be hard to determine which is best because we do not have your data. If you cannot provide the data then use the first nonclustered index I have provided and if it is efficient then use that, if not drop the index and split them up into 3 (as I have shown) and see if it is faster. –  Darren Davies May 20 '13 at 12:06
    
okay. thanks Darren Davies –  aliraza iftikhar May 20 '13 at 12:12
1  
@aliraza just because you "need all the columns" doesn't mean you should use SELECT *. Please read this in full. –  Aaron Bertrand♦ May 20 '13 at 13:34
up vote 0 down vote

Try this one -

CREATE TABLE dbo.Medicines
(
      ID BIGINT IDENTITY(1,1) PRIMARY KEY
    , MedicineName nvarchar(50)
    , BrandName nvarchar(50)
    , MedicineCode nvarchar(20)
    , Price DECIMAL(10,2)
    , Quantity INT
)

CREATE NONCLUSTERED INDEX [ind_Medicines] ON [dbo].[Medicines]
(
    [MedicineName] ASC,
    [BrandName] ASC,
    [MedicineCode] ASC
)
INCLUDE ([Price], [Quantity]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
  SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, 
  ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE PROCEDURE dbo.usp_SearchMedicine
      @name NVARCHAR(50)
    , @brand NVARCHAR(50)
    , @code NVARCHAR(20)

AS BEGIN

    SELECT 
          m.MedicineName
        , m.BrandName
        , m.MedicineCode
        , m.Price
        , m.Quantity
    FROM dbo.Medicines m
    WHERE m.MedicineName = ISNULL(@name, m.MedicineName)
        AND m.BrandName = ISNULL(@brand, m.BrandName)
        AND m.MedicineCode= ISNULL(@code, m.MedicineCode)

END
share|improve this answer
    
this has nothing to do with indexes, rather you have just refactored the query. –  Darren Davies May 20 '13 at 11:58
    
Please see updated answer. –  Devart May 20 '13 at 12:03
    
@devart: seems like you are also suggesting that composite is more better, include column is a good practice or bad? –  aliraza iftikhar May 20 '13 at 12:11
    
I think, if filtration goes on 3 columns - the composite index is more profitable. –  Devart May 20 '13 at 12:15
    
"include column" - in your case it will be okay. –  Devart May 20 '13 at 12:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.