compare of 2 big tables

Question

Post reply

compare of 2 big tables

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150467 More actions · Answer 1

First thing I notice is that SQL Server is suggesting you create exactly the same index I mentioned earlier:

CREATE NONCLUSTERED INDEX nc1

ON dbo.Con_RecipientToMessages (Id)

INCLUDE (ReasonRef)

WITH (

FILLFACTOR = 100,

MAXDOP = 4,

ONLINE = OFF,

SORT_IN_TEMPDB = ON

);

That will eliminate the costly index intersection at the start of the plan. Please create that index and re-run the query.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

peleg SSCrazy Points: 2611 More actions · Answer 2

i will be able to make the change only later.

my current index looks like this:

CREATE NONCLUSTERED INDEX [IX_REASONREF] ON [dbo].[Con_RecipientToMessages]

(

[ReasonRef] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

what makes the diffrence between the current index, and the one that is recommand? which of the parameters will make the improvment?

Thanks

Peleg

Paul White SSC Guru Points: 150467 More actions · Answer 3

peleg k (3/19/2010)
what makes the diffrence between the current index, and the one that is recommand? which of the parameters will make the improvment?

You have two indexes at present that are used in the plan: one called [PK_Con_RecipientToMessages], non-clustered on ID; and one called [IX_REASONREF], non-clustered on ReasonRef. Your query needs to find records based on ID, and return the ReasonRef. Neither index can do that on its own - so SQL Server hashes the two indexes together. If SQL Server just used [PK_Con_RecipientToMessages], it would have to go back to the table for every row to find the ReasonRef associated with the ID.

A single index on ID, with ReasonRef as an included column will allow a seek on ID, returning the ReasonRef at the same time.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

peleg SSCrazy Points: 2611 More actions · Answer 4

as i undestand, i should change the reasonRef column on Con_RecipientToMessages to an included column?

Paul White SSC Guru Points: 150467 More actions · Answer 5

peleg k (3/19/2010)
as i undestand, i should change the reasonRef column on Con_RecipientToMessages to an included column?

Con_RecipientToMessages is a table. You cannot make a column included on a table. That only applies to indexes. See Creating Indexes with Included Columns.

You have two separate indexes right now. One on ID, one on ReasonRef. I am not suggesting you modify either of them, since I don't know what else they are useful for. The index on ID appears to be a non-clustered primary key anyway, so definitely keep that one.

All I am asking is that you create the new index exactly as I posted before (twice).

It is up to you to decide whether the existing index on ReasonRef is worth keeping or not.

Thanks

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

The Natrix SSC Eights! Points: 835 More actions · Answer 6

Thanks for that recompile option suggestion and the batch compilation article, didn't know about that. I sometimes have huge tables that I have to work with as well, but I usually just try to have those tables indexed.