Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Indexes for Booking Search


Indexes for Booking Search

Author
Message
RaviinBne
RaviinBne
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 28
Gidday,

We run a Reservation Software where bookings are the core of the business. The Reservation table has about 25 columns like bookedDate, PickupDate, DropOffDate, PickupLocation.....

And the users often search for reservations by specifing one of more fields. And I have been adding indexes to make these searches faster. We have now got to a stage where the index size is 3 times the data size on this table and insert and updates on this table is getting slower.

Any idea how to find a balance for this issue?

thanks & Regards,
Ravi
LutzM
LutzM
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7569 Visits: 13559
You might want to read this article to verify your data types are as efficient as possible.
Aother issue might be the indexing strategy itself. Maybe you can narrow it down by moving some of the columns to the INCLUDE section. It's also possible that some of your indexes are either redundant or not queried often enough.
What method did you use to define the indexes? (I surely hope the answer is not DTA...).
Do you regulary run a missing index as well as an unused index check?

Without table def including definition for all indexes as well as the results of sys.dm_db_index_usage_stats (including the index name based on sys.indexes) for this specific table it'll be hard to tell...

Edit: Link added.



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19539 Visits: 32331
Also if you're just adding single column indexes all over the place, you might be hurting yourself without knowing it. Are you adding the indexes based on the execution plans and do you know that, after adding the index, that performance improves, or are you making assumptions? The best way to solve this issue is through lots of gathered metrics so you know which queries are run most frequently, which will benefit from specific indexes and which won't and begin to narrow down what you're maintaining. Also, do you have a clustered index on the table? Is it the most frequently used access point to the data?

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
GilaMonster
GilaMonster
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54266 Visits: 44637
LutzM (1/1/2011)
You might want to read this article to verify your data types are as efficient as possible.


Which article? ;-)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


GilaMonster
GilaMonster
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54266 Visits: 44637
Ravi, can you list the indexes on the table? Good chance you have some redundant ones.

My usual indexing strategy in that kind of situation is to tailor indexes for the most important/most common queries and then deal with others on a case-by-case basis, often SQL can manage with indexes that aren't ideal, which for less important queries is probably OK.

Can you also maybe post a couple of example queries? Have you verified that the queries are indeed using the indexes?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


LutzM
LutzM
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7569 Visits: 13559
GilaMonster (1/3/2011)
LutzM (1/1/2011)
You might want to read this article to verify your data types are as efficient as possible.


Which article? ;-)


Ouch. Blush Link added. Thanx Gail. (It's the Disk Is Cheap! editorial link btw...)



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
RaviinBne
RaviinBne
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 28
Hi Guys, Thanks for your responses... I have generalised the issue to make it more readable ...

The Reservation(actual name Requirement) Table def looks like this

ID [uniqueidentifier],
Kind [nvarchar] 255 Not Null, /* Kinds: Sale / Purchase */
ReservationNo [nvarchar] 16 Not Null,
ItineraryID [uniqueidentifier] Not Null, /* One Itinerary can have multiple Reservations */
BookedDate Datetime Not Null,
PickupDate Datetime Not Null,
DropOffDate DateTime Not Null,
PickUpLocationID [uniqueidentifier] Not Null, /* FK */
DropOffLocationID [uniqueidentifier] Not Null, /* FK */
RatePlanID [uniqueidentifier] Not Null, /* FK */
PromotionID [uniqueidentifier] Null, /* FK */
SalesPersonID [uniqueidentifier] Null, /* FK */
SellingCompanyID [uniqueidentifier] not null, /* FK */
AgentID [uniqueidentifier] not null, /* FK */
CustomerID [uniqueidentifier] not null, /* FK */
BookingValue Money not null,
ProductID [uniqueidentifier] not null,
CurrencyCode [nvarchar] 2 not null
..... and many more fields that are just value fields

Primary Clustered Index on [ID]
other Covering Indexs
-- Search for all reservations that belong to an Itinerary
1) Indexed Columns (
[Kind] ,
[ItineraryID]
)
INCLUDE ( [Id],
[Version], [ItemValue], [ChildrenValue], [Flags], [ItemQuantityMinimum], [ItemQuantityMaximum], and almost all the fields in the table to avoid Lookups.

--Search by reservation Number
2) Indexed Columns (
[ReservationNo]
)
INCLUDE ( [Id],
[Version], [ItemValue], [ChildrenValue], [Flags], [ItemQuantityMinimum], [ItemQuantityMaximum], and almost all the fields in the table to avoid Lookups.

-- Search by Customer Last Name &
-- Search by customer last name and Brand
3) Indexed Columns (
[Kind] ,
[CustomerID]
[SellingCompanyID]

)
INCLUDE ( [Id],
[Version], [ItemValue], [ChildrenValue], [Flags], [ItemQuantityMinimum], [ItemQuantityMaximum], and almost all the fields in the table to avoid Lookups.

-- Search by Booked Date or travel Dates or locations
4) Indexed Columns (
[Kind]
[BookedDate]
[PickupDate]
[DropOffDate]
[PickupLocationID]
[DropOffLocationID]
)
INCLUDE ( [Id],
[Version], [ItemValue], [ChildrenValue], [Flags], [ItemQuantityMinimum], [ItemQuantityMaximum], and almost all the fields in the table to avoid Lookups.

and this goes on...

One of the issues is we use Nhibernate to access the database... and I might be wrong but Nhibernate always access the whole object .... i.e select this_.ID, this_.ReservationNo..... (all columns) from Requirement where......

and due to this I am having to include all columns in the include column list of all indexes to avoid lookups...

Questions
1) would it be better for me to just have the ID as the included column so that SQL server uses the covering index and clustered index to retrieve the data.

2) Does SQL server use the included columns in a convering index to Search? or is this only a solution for lookups?

Yes I do keep a close eye on dm_db_index_usage_stats to make sure there are no unused indexes.

Guys, thanks again for taking the time to look into this.
Regards,
Ravi
GilaMonster
GilaMonster
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54266 Visits: 44637
RaviinBne (1/4/2011)
and this goes on...


If you want help checking for duplicate (redundant) indexes, you'll need to post the whole lot, not a sampling

and due to this I am having to include all columns in the include column list of all indexes to avoid lookups...


Well that's why your index is so large. By doing that you're duplicating the entire table with each index. Including every column is almost never a good idea. Run some traces, see what queries are being run, index accordingly.

Covering indexes are all well and good, but the general though is that you can't cover all queries and you shouldn't try to cover all queries.

Questions
1) would it be better for me to just have the ID as the included column so that SQL server uses the covering index and clustered index to retrieve the data.


If the ID is the cluster, it's in the index anyway, regardless of whether or not you add it.

If your index is covering, SQL won't go to the cluster for additional columns. It's only when the index is not covering that a lookup is necessary.

Maybe the lookups are OK, maybe not. You'll have to test to see.

2) Does SQL server use the included columns in a convering index to Search?


No. Only key columns can be used in seeks and queries can only seek on an index if they're filtering on a left-based subset of the index key.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


LutzM
LutzM
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7569 Visits: 13559
The key information in your reply is NHibernate and most probably a misuse of it.

Check your app while a trace is running and check the values needed for functionality vs. queried from database.

If there are columns queried that are not needed for a specific app functionality, it's time to call the vendor in and make them fix their code. If you have a poorly written app, index tuning will provide just minor improvement...



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19539 Visits: 32331
Notice how the leading edge of three of the indexes is the [Kind] column. That starts to look like a better candidate for your clustered index than the primary key (although, as a varchar(256) it's an amazingly bad choice for a clustered index). But that would be the start of trying to solve this dilema. You have the cluster (which Gail explained, never needs a lookup) on the PK, but, at least from what you've shown, the PK is not being used to determine access to the table. That at least suggests you should be looking elsewhere for the PK.

Also, having the same column, [Kind], as the leading edge of all your indexes, will cause the optimizer to not use some indexes that might be better suited to a given query and instead use an index that isn't as well suited. This is because the histogram, part of the statistics of the index, is only made on the first column of the index. It's not the only thing used to determine index usage, but it is a primary one.

Don't even get me started on just how bad nHibernate could be making your queries. Just a hint, unless your developers have specifically coded for it, nHibernate doesn't use the data type size when setting it's parameters, but uses the length of the value passed. So 'Dog' becomes varchar(3). 'Horse' is varchar(5). 'Elephant' is a varchar(8). When each of these is passed to SQL Server, a whole new execution plan is created instead of reusing an existing one, which would have happened if the datatype was set to varchar(125) as it should have been. Oh yes, you have lots of joy coming from nHibernate. I'd suggest looking up the N+1 problem and seeing if you can find evidence of that in your code because, again, it's extremely common unless your developers specifically coded around it.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search