5

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?

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
  • 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
4

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)
  • 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
  • @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 May 20 '13 at 12:06
  • 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
  • okay.then what should i do? – Ali Raza Iftikhar May 20 '13 at 13:48
  • Name the columns. You can do this just as easily as you named them in the question above. – Aaron Bertrand May 20 '13 at 13:52
0

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
  • @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

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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