Link to home
Start Free TrialLog in
Avatar of navinbabu
navinbabuFlag for India

asked on

MS SQL Query Indexing

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

https://www.experts-exchange.com/questions/25101051/MS-SQL-Query-Help.html
ASKER CERTIFIED SOLUTION
Avatar of Reza Rad
Reza Rad
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
this is great article on indexing, but in sql server 2000:
http://odetocode.com/Articles/70.aspx
Avatar of navinbabu

ASKER

Hi ..

I did that I dont see any differnce in the excution plan(performance )
difference will be obvious on large amount of data ,
how many seconds will take your query to run now?
Where can i observe that ?
I see the Query Cost (relative to the batch ) :100%
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
 Client processing time      3            3.0000
  Total execution time      6            6.0000
  Wait time on server replies      3            3.0000
could you take screenshot of execution plan and upload it here?