navinbabu
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi ..
I did that I dont see any differnce in the excution plan(performance )
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?
how many seconds will take your query to run now?
ASKER
Where can i observe that ?
ASKER
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
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
ASKER
Client processing time 3 3.0000
Total execution time 6 6.0000
Wait time on server replies 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?
http://odetocode.com/Articles/70.aspx