Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
standers
Starting Member
4 Posts |
 Posted - 2009-04-22 : 13:03:03
|
| I'm wondering if I should change the keys/indexes on the tables I currently maintain. I'm having some performance issues and thought that maybe if I made some changes to the keys it could help.Here's what I currently have:tblMain (idMain int identity, <other cols>) primary key = idMaintblDetails (idDetails int identity, idMain int, <other cols>) primary key = idDetails + idMainWas thinking about going to:tblDetails primary key = idDetailstblDetails index (clustered/non?) = idMainIs it quicker to join on the second column of a composite key, or the first column of a non-unique index?Thanks,Stephen |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2009-04-22 : 13:10:42
|
| It depends on how the table is used.>> Is it quicker to join on the second column of a composite key, or the first column of a non-unique index?The former will do an index scan the latter has a chance of doing an index seek.In both cases it may be followed by a rid lookup which can also affect performance - consider making indexes covering for frequent queries.The clustered index columns are included in all other indexes so a small clustered index (and unique) will make all other indexes more efficient - but that might not be the most important factor.Have a look at your query plans and try to optimise.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
standers
Starting Member
4 Posts |
Posted - 2009-04-22 : 14:59:02
|
| Well yes, I had forgotten about the dreaded bookmark lookups after using non-clustered indexes... What about re-ordering the composite key so that the idMain comes before the idDetails? That should make the joins faster right (turn Scans into Seeks)? That would mean the first column of the composite key is not unique, but selectivity should still be very high. The penalty there would be during inserts right? b/c the details may not be inserted in the same order as the main records..These tables (used for reporting) are only updated monthly, so I may be able to deal with slower inserts if I get much faster performance on the selects. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2009-04-22 : 15:12:11
|
| In that case you pobably shouldn't worry about the inserts and try to make the indexes covering.If necessary create new aggregate tables to fulfil the reports.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
pootle_flump
1064 Posts |
Posted - 2009-04-22 : 16:18:17
|
quote:
Originally posted by standers
Well yes, I had forgotten about the dreaded bookmark lookups after using non-clustered indexes...
There's nothing wrong with lookups - why are they dreaded?Indexes (apart from those used internally to enforce constraints) have only one purpose - to serve DML operations. Without knowing the queries, there is no way to determine an optimal index structure.As a broad statement, if they are updated only 12 times a year, the window for updates is large and there are a lot of reporting reads to support then you can index pretty liberally. As Nigel mentions, covering indexes can be very useful for these - don't forget to make use of INCLUDES if you are using 2005 or 2008. |
|
|
|
standers
Starting Member
4 Posts |
Posted - 2009-04-22 : 16:48:07
|
 I just remember from the little bit of index optimization that I have done that if I can remove the lookups through better indexing, I get much better performance. Maybe that should be a reflection on the index rather than the lookup! If we are reporting on most of the columns in the table, then covering indexes would almost double the disk usage for the table, right? Is there any way to get the performance without the increase in disk usage? What about instead of creating another covering index adding those cols instead to the PK? I know, I know - it sounds so bad I almost didn't mention it, but wouldn't that create a covering PK? That would get the same result with less disk space, right? Sorry if that is an incredibly stupid question (cause it sounds like it to me!), its just something I've not thought about till now, and wonder if there's something else to think about that would make that a terrible idea.Thanks to you both for your help BTW! |
|
|
|
pootle_flump
1064 Posts |
Posted - 2009-04-23 : 07:04:02
|
| You obviously know something about indexes, but are not quite there. I think you know this though How much extra disk space is used depends on all sorts of things but broadly yes - you sacrifice disk space for performance. For reporting applications especially this is a common sacrifice. In the database world we typically value performance over a few quid for some more drives To properly help you with your task I'll refer you back to mine and Nigel's points - we need to see your queries. We need to know how many rows they return, how many rows in the table, how often they are run, the performance required (for example not much point tuning indexes for a query run once a month), how are they performing now etc. |
|
|
|
pootle_flump
1064 Posts |
Posted - 2009-04-23 : 07:05:54
|
quote:
Originally posted by standers
I just remember from the little bit of index optimization that I have done that if I can remove the lookups through better indexing, I get much better performance.
Yes, but indexing is about balancing things. Please note there is nothing wrong with lookups. The only time you need to worry about them is with regard to parameter sniffing (which we will ignore right now) - other than that they are an optimisation, not "dreaded". Since this is a reporting application you are unlikely to many, if any, in any event |
|
|
|
|
|
|
|
|
Composite Key vs clustered/nonclustered index