|
Comments
|
|
I love the idea of interactive puzzles like this. What a great way to learn.
|
|
|
1) I added a nonclustered index on registrations.eventID (covering registrationsStatus)
2) I rewrote the table using a join instead of using "in."
SELECT COUNT(*)
FROM dbo.Registrations r
JOIN dbo.RegistrationStatus rs
ON r.RegistrationStatus = rs.RegistrationStatusID
AND rs.CountAsAttending = 1
AND EventID = 49
This reduced the # of reads on Registrations to 3 and the # of reads on RegistrationStatus to 2.
|
|
andre porter on
4/29/2010
I also used the nonclustered index added in your step1 but instead of rewriting the query I...
1) added a clustered index on the RegistrationStatus tbl that included (RegistrationStatusID & CountAsAttending)
2) I added column statistics that DTA recommended on the Registrations tbl for (RegistrationStatus and EventID).
Profiler shows the reads droping from 600+ to 300 and then finally to 7.
|
|
Jason Willis on
4/30/2010
It's good to have something like this. If people actually use this they can actually learn the lessons that are presented.
|
|
|
Awesome idea!
|
|
|
Love the challenge!
|
|
Royston Neal on
4/30/2010
Great idea
|
|
|
gud
|
|
|
k
|
|
|
Great idea with throwing out a puzzle to solve
|
|
Kasey Wheeler on
4/30/2010
Cool stuff...
|
|
|
Quiz! Yay!
|
|
|
/* step 1: Create a Clustered Primary Key
on the Registration..RegistrationID column and
on the RegistrationStatus..RegistrationStatusID column
step 2: Create Foreign Key Relationship
[Registrations].[dbo].[RegistrationStatus])REFERENCES [RegistrationStatus].[dbo].[RegistrationStatusID])
step 2: On Registration Table: Determine selectivity on the eventID column and registrationStatus column
which will be used as a nonclustered index (most selective = first column).
*/
|
|
|
got it down to two logical reads on each table with clustered index on each of the id columns. Filtered index on registrationstatus.CountsAsAttending, and non-clustered on Registrations.eventid with Registrationid and registrationstatus included
|
|
|
Output is hard to read - consider a larger font
|
|
Chris Howarth on
4/30/2010
Down to 2 reads in *total* by creating an indexed view on a reworked version of the query:
CREATE VIEW dbo.Temp
WITH SCHEMABINDING
AS
SELECT COUNT(*) AS CountRows
FROM dbo.Registrations r
INNER JOIN dbo.RegistrationStatus rs
ON rs.RegistrationStatusID = r.RegistrationStatus
WHERE rs.CountAsAttending = 1
AND r.EventID = 49
GO
CREATE UNIQUE CLUSTERED INDEX [IX_TMP] ON dbo.Temp(CountRows)
GO
SELECT *
FROM dbo.Temp
WITH (NOEXPAND)
GO
Chris
|
|
Chris Howarth on
4/30/2010
Previous comment - first line of SELECT statement in the View should be: SELECT COUNT_BIG(*) AS CountRows
|
|
|
Good but is necessary more information.
|
|
|
can't wait to see if I can solve the puzzle, I like the format
|
|
|
excellent
|
|
|
good but i ibetter if explanation is more.
|
|
|
great format of the presentation -- a problem to solve in the first video, then the proposed solution(s) in the second video.
|
|
|
Love this idea. (although it would have helped some of us to tell us where to find the # of reads - I got from profiler)
I got the reads down to 4 by adding primary keys to the two tables and adding indexes to RegistrationStatus and CountAsAttending.
Did not change to a join as someone else did above. That was a good idea - duh :-)
|
|
|
Great idea. Is the database compatible with SQL Server 2008 Express Edition?
|
|
|
I noticed something funny. I optimized the query and tables. Restarted the database, as suspected i got 300 reads on 1st attempt, but subsequent runs were good. Anyone else have this?
|
|
|
Where are the links?
|
|
|
Brilliant - more of these please!
|
|
|
Is there a 2005 equivalent of the mdf please?
|
|
|
Good narration but wheres the puzzle or am I missing a trick? ;-)
|
|
|
Duh... Got the references (Puzzle, .mdf etc)
|
|
|
Great idea. I would love to follow but I'm having difficulties attaching the mdf file to SQL 2008 express edition. Is it 'cos ldf file is missing?
|
|
|
It's really nice to have these scenarios posted - please keep up the good work!
|
|
|
among other things I added an index to the dbo.RegistrationStatus table using the ID column together with the CountAsAttending column. I also made the ID the PK. I added a filter of CountAsAttending = 1 to the index. Got an index scan on the new IX and an index seek on the PK. Reads = 0.
|
|
|
I get it. The puzzle is to try and figure out how to attach the database without the log file. Still working on it...
|