2 added 10 characters in body
source | link

I am going to assume that the event type is in the child events table, hence the join is needed. I noticed that a large part of the cost is doing a sort. You can avoid sorts by having the right columns in an index in the right order. Here are two indexes that you should probably have. Fair warning, I am an Oracle DBA, not a SQL Server DBA. So the syntax might not be correct. Also notice that all of the columns that you need are in the indexes, hence SQL Server won't have to read both the index and the table. this is how I would do it in an Oracle database, it should work for SQL Server as well. But test and you will know.

Counting potty breaks, sounds like a George Orwell book? ;-)

CREATE INDEX CHILDEVENTS_IDX1 ON CHILDEVENTS ( TYPE, CHILDID ) CREATE UNIQUE INDEX CHILDINFORMATION_IDX1 ON CHILDID ( ADDDATE, CHILDID )

CREATE INDEX CHILDEVENTS_IDX1      ON CHILDEVENTS ( TYPE, CHILDID )
CREATE UNIQUE INDEX CHILDINFORMATION_IDX1 ON CHILDID     ( ADDDATE, CHILDID )

I am going to assume that the event type is in the child events table, hence the join is needed. I noticed that a large part of the cost is doing a sort. You can avoid sorts by having the right columns in an index in the right order. Here are two indexes that you should probably have. Fair warning, I am an Oracle DBA, not a SQL Server DBA. So the syntax might not be correct. Also notice that all of the columns that you need are in the indexes, hence SQL Server won't have to read both the index and the table. this is how I would do it in an Oracle database, it should work for SQL Server as well. But test and you will know.

Counting potty breaks, sounds like a George Orwell book? ;-)

CREATE INDEX CHILDEVENTS_IDX1 ON CHILDEVENTS ( TYPE, CHILDID ) CREATE UNIQUE INDEX CHILDINFORMATION_IDX1 ON CHILDID ( ADDDATE, CHILDID )

I am going to assume that the event type is in the child events table, hence the join is needed. I noticed that a large part of the cost is doing a sort. You can avoid sorts by having the right columns in an index in the right order. Here are two indexes that you should probably have. Fair warning, I am an Oracle DBA, not a SQL Server DBA. So the syntax might not be correct. Also notice that all of the columns that you need are in the indexes, hence SQL Server won't have to read both the index and the table. this is how I would do it in an Oracle database, it should work for SQL Server as well. But test and you will know.

Counting potty breaks, sounds like a George Orwell book? ;-)

CREATE INDEX CHILDEVENTS_IDX1      ON CHILDEVENTS ( TYPE, CHILDID )
CREATE UNIQUE INDEX CHILDINFORMATION_IDX1 ON CHILDID     ( ADDDATE, CHILDID )
1
source | link

I am going to assume that the event type is in the child events table, hence the join is needed. I noticed that a large part of the cost is doing a sort. You can avoid sorts by having the right columns in an index in the right order. Here are two indexes that you should probably have. Fair warning, I am an Oracle DBA, not a SQL Server DBA. So the syntax might not be correct. Also notice that all of the columns that you need are in the indexes, hence SQL Server won't have to read both the index and the table. this is how I would do it in an Oracle database, it should work for SQL Server as well. But test and you will know.

Counting potty breaks, sounds like a George Orwell book? ;-)

CREATE INDEX CHILDEVENTS_IDX1 ON CHILDEVENTS ( TYPE, CHILDID ) CREATE UNIQUE INDEX CHILDINFORMATION_IDX1 ON CHILDID ( ADDDATE, CHILDID )