MS SQL Query Indexing

navinbabu
navinbabu used Ask the Experts™
on
I have 3 tables

City ( CityId , Name )

Items( Itemsid , ItemsName , HotelId , CityId)

Hotels (HotelId ,HotelName)

Im trying to write a query to get Hotelname ,City Name,  Number of items in that hotel in that city in that database

Query :

select count(i.Itemid), h.HotelName, c.Name from Items i inner join Hotels h on i.HotelId = h.HotelId inner join City c on c.CityId = i.CityId
group by h.HotelName, c.Name

How can I index the joining fields ?

This is a continuation for question

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_25101051.html
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Consultant, Trainer
Commented:
Hi again,

right click on hotel table,
select design or modify
then right click on a blank area in design view
select indexes/keys
now add new index on hotelid

do the same for cityid
Reza RadConsultant, Trainer

Commented:
this is great article on indexing, but in sql server 2000:
http://odetocode.com/Articles/70.aspx

Author

Commented:
Hi ..

I did that I dont see any differnce in the excution plan(performance )
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Reza RadConsultant, Trainer

Commented:
difference will be obvious on large amount of data ,
how many seconds will take your query to run now?

Author

Commented:
Where can i observe that ?

Author

Commented:
I see the Query Cost (relative to the batch ) :100%
Reza RadConsultant, Trainer

Commented:
in ssms, in your query window
far right and far down you will see the execution time,
i think it must be no more than 1 second at all for not large amount of data

Author

Commented:
 Client processing time      3            3.0000
  Total execution time      6            6.0000
  Wait time on server replies      3            3.0000
Reza RadConsultant, Trainer

Commented:
could you take screenshot of execution plan and upload it here?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial