Link to home
Start Free TrialLog in
Avatar of Bob Schneider
Bob SchneiderFlag for United States of America

asked on

SQL Query Optimization

I would like to optimize this sql statement.  It is a fitness events results page and I am getting larger and larger races so I want to ensure that it doesn't crash (like happened last year during a large race) as folks went on the site to view their results.  Specifically I want to know if the sql looks well constructed, where the indexes should be, and anything else that would make it likely to absorb a high number of requests.  I think that the asp portion of the page is in pretty good shape.  

Here is the sql statement:
SELECT pr.Bib, p.LastName, p.FirstName, p.Gender, pr.Age, ir.ChipTime, ir.FnlTime, ir.ChipStart, p.City, p.St FROM Participant p INNER JOIN IndResults ir ON p.ParticipantID = ir.ParticipantID INNER JOIN PartRace pr ON pr.ParticipantID = p.ParticipantID INNER JOIN RaceData rd ON rd.RaceID = pr.RaceID AND rd.RaceID = ir.RaceID WHERE ir.RaceID = 695 AND ir.FnlTime IS NOT NULL AND ir.EventPl >= 1 AND ir.FnlTime <> '00:00:00.000' ORDER BY ir.EventPl

Open in new window


Here is what Enterprise Manager did to the sql:
SELECT        TOP (100) PERCENT pr.Bib, p.LastName, p.FirstName, p.Gender, pr.Age, ir.ChipTime, ir.FnlTime, ir.ChipStart, p.City, p.St
FROM            dbo.Participant AS p INNER JOIN
                         dbo.IndResults AS ir ON p.ParticipantID = ir.ParticipantID INNER JOIN
                         dbo.PartRace AS pr ON pr.ParticipantID = p.ParticipantID INNER JOIN
                         dbo.RaceData AS rd ON rd.RaceID = pr.RaceID AND rd.RaceID = ir.RaceID
WHERE        (ir.RaceID = 695) AND (ir.FnlTime IS NOT NULL) AND (ir.EventPl >= 1) AND (ir.FnlTime <> '00:00:00.000')
ORDER BY ir.EventPl

Open in new window


Would using this speed up the result or are there other issues that I could address?  What is the best way to show you some data, if that would help.
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image
At the first sight it looks ok but we can't guess about which indexes it's using.
Execute the query in SSMS with the Query Plan turned on and post here the current query plan so we can advise you.
Avatar of Jim Horn
Jim Horn
Flag of United States of America image
For starters, please indent your SQL statement, as the one-line version is difficult to read 
SELECT 
   pr.Bib, 
   p.LastName, 
   p.FirstName, 
   p.Gender,
   pr.Age, 
   ir.ChipTime, 
   ir.FnlTime, 
   ir.ChipStart, 
   p.City, 
   p.St 
FROM Participant p 
   JOIN IndResults ir ON p.ParticipantID = ir.ParticipantID 
   JOIN PartRace pr ON pr.ParticipantID = p.ParticipantID 
   JOIN RaceData rd ON rd.RaceID = pr.RaceID AND rd.RaceID = ir.RaceID 
WHERE ir.RaceID = 695 AND 
   ir.FnlTime IS NOT NULL AND 
   ir.EventPl >= 1 AND 
   ir.FnlTime <> '00:00:00.000' 
ORDER BY ir.EventPl

Open in new window


After that...
1   What's the purpose of RaceData in this query?  I don't see it used in the SELECT or WHERE.
2  What's the purpose of  ir.FnlTime <> '00:00:00.000'  ?
3  Otherwise the query looks fine.  Tell us the data types of each column, and the indexes on these tables.
SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Bob Schneider
Bob Schneider
Flag of United States of America image

ASKER

What is the purpose of the /* and */?
SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Bob Schneider
Bob Schneider
Flag of United States of America image

ASKER

Ummm...where do I run that script...I'm kind of a newb...
SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Bob Schneider
Bob Schneider
Flag of United States of America image

ASKER

The first pane showed nore results.  Here is the second one:
NULL	VIRA	PK_AdminLogin	Y.Y	AdminLogin	1	2336	AdminLoginID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_Admins	Y.Y	Admins	1	4	AdminsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_AdminTasks	Y.Y	AdminTasks	1	21	AdminTasksID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_AdViews	Y.Y	AdViews	1	173288	AdViewsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_AgeGroups	Y.Y	AgeGroups	1	5756	AgeGroupsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_Announcer	Y.Y	Announcer	1	10	AnnouncerID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_AnnouncerReads	Y.Y	AnnouncerReads	1	1185	AnnouncerReadsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_Attach	Y.Y	Attach	1	6	AttachID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_AuthAccess	Y.Y	AuthAccess	1	259388	AuthAccessID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_BannerAds	Y.Y	BannerAds	1	3	BannerAdsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	1	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_ChangesConv	Y.Y	ChangesConv	1	76	ChangesConvID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_ChangesRFID	Y.Y	ChangesRFID	1	99	ChangesRFIDID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_ChangesSql	Y.Y	ChangesSql	1	358	ChangesSqlID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_ContactLog	Y.Y	ContactLog	1	100	ContactLogID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_Debt	Y.Y	Debt	1	7	DebtID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	1	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_DebtActivity	Y.Y	DebtActivity	1	29	DebtActivityID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_DontSend	Y.Y	DontSend	1	481	DontSendID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_EmailMktg	Y.Y	EmailMktg	1	532	EmailMrktgID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_EmailRslts	Y.Y	EmailRslts	1	391	EmailRsltsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_EventAds	Y.Y	EventAds	1	9	EventAdsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_EventAsgmt	Y.Y	EventAsgmt	1	306	EventAsgmtID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_EventDir	Y.Y	EventDir	1	100	EventDirID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	2	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_EventDirLogin	Y.Y	EventDirLogin	1	136	EventDirLoginID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_EventFamily	Y.Y	EventFamily	1	5	EventFamilyID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_Events	Y.Y	Events	1	285	EventID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_EventsWeb	Y.Y	EventsWeb	1	254	EventsWebID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_EventWinner	Y.Y	EventWinner	1	47	EventWinnerID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_EvntRaceTypes	Y.Y	EvntRaceTypes	1	10	EvntRaceTypesID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_Feedback	Y.Y	Feedback	1	5	FeedbackID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_HonorRollDivs	Y.Y	HonorRollDivs	1	15	HonorRollDivsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_IndDelay	Y.Y	IndDelay	1	4	IndDelayID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_IndResults	Y.Y	IndResults	1	44179	IndRsltsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_InfoSheet	Y.Y	InfoSheet	1	1	InfoSheetID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_InventoryBib	Y.Y	InventoryBib	1	9	InventoryBibID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_InventoryChip	Y.Y	InventoryChip	1	14	InventoryChipID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_InventoryPins	Y.Y	InventoryPins	1	2	InventoryPinsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_InventoryPrepped	Y.Y	InventoryPrepped	1	1	InventoryPreppedID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_InventorySpacers	Y.Y	InventorySpacers	1	1	InventorySpacersID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_MediaOrder	Y.Y	MediaOrder	1	81	MediaOrderID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_MultiSettings	Y.Y	MultiSettings	1	0	MultiSettingsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_MultiSettingsChip	Y.Y	MultiSettingsChip	1	8	MultiSettingsChipID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_MyHist	Y.Y	MyHist	1	172	MyHistID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	1	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_MyHistLogin	Y.Y	MyHistLogin	1	206	MyHistLoginID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_OfficialRslts	Y.Y	OfficialRslts	1	205	OfficialRsltsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_OrderChips	Y.Y	OrderChips	1	0	OrderChipsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_Participant	Y.Y	Participant	1	44461	ParticipantID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_PartRace	Y.Y	PartRace	1	52848	PartRaceID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_PartReg	Y.Y	PartReg	1	52147	PartRegID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_PartReminders	Y.Y	PartReminders	1	228	PartRemindersID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_PreRaceRecips	Y.Y	PreRaceRecips	1	3222	PreRaceRecipsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_PreRaceSent	Y.Y	PreRaceSent	1	28	PreRaceSentID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_PromoEmail	Y.Y	PromoEmail	1	85	PromoEmailID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	1	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_PromoRecips	Y.Y	PromoRecips	1	8104	PromoRecipsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_ProspectMessage	Y.Y	ProspectMessage	1	1	ProspectMessageID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_Prospects	Y.Y	Prospects	1	131	ProspectsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	1	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_ProspectsYWR	Y.Y	ProspectsYWR	1	319	ProspectsYWRID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_ProspectYWRMessage	Y.Y	ProspectYWRMessage	1	1	ProspectYWRMessageID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_ProsTrkr	Y.Y	ProsTrkr	1	0	ProsTrkrtID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_RaceData	Y.Y	RaceData	1	420	RaceID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	6	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_RaceGallery	Y.Y	RaceGallery	1	56	RaceGalleryID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_RacePix	Y.Y	RacePix	1	144	RacePixID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_RaceVids	Y.Y	RaceVids	1	131	RaceVidsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_Records	Y.Y	Records	1	8	RecordsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_ReminderSent	Y.Y	ReminderSent	1	1	ReminderSentID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_ResultsSent	Y.Y	ResultsSent	1	20671	ResultsSentID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_RFIDRslts	Y.Y	RFIDRslts	1	0	RFIDRsltsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_RFIDSettings	Y.Y	RFIDSettings	1	154	RFIDSettingsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_Series	Y.Y	Series	1	11	SeriesID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	2	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_SeriesEvents	Y.Y	SeriesEvents	1	46	SeriesEventsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	1	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_SeriesParts	Y.Y	SeriesParts	1	4834	SeriesPartsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	1	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_SeriesRaces	Y.Y	SeriesRaces	1	21	SeriesRacesID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_SeriesResults	Y.Y	SeriesResults	1	4500	SeriesResultsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_SeriesStdgs	Y.Y	SeriesStdgs	1	4789	SeriesStdgsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_SiteInfo	Y.Y	SiteInfo	1	290	SiteMapID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_Splits	Y.Y	Splits	1	0	SplitsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_Sponsors	Y.Y	Sponsors	1	34	SponsorsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_Staff	Y.Y	Staff	1	16	StaffID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	3	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_StaffAsgmt_1	Y.Y	StaffAsgmt	1	202	StaffAsgmtID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_StaffAsgmt	Y.Y	StaffAvail	1	321	StaffAvailID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_StaffAvailPref	Y.Y	StaffAvailPref	1	11	StaffAvailPrefID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_StaffEventInfo	Y.Y	StaffEventInfo	1	1	StaffEventInfoID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_StaffLogin	Y.Y	StaffLogin	1	431	StaffLoginID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_StaffPmt	Y.Y	StaffPmt	1	270	StaffPmtID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_StaffAvail	Y.Y	StaffRqd	1	99	StaffRqdID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	1	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_StaffVids	Y.Y	StaffVids	1	13	StaffVidsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_TeamMmbrs	Y.Y	TeamMmbrs	1	145	TeamMmbrsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_Teams	Y.Y	Teams	1	6	TeamsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_TeamScoring	Y.Y	TeamScoring	1	2	TeamScoringID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_ToDoLog	Y.Y	ToDoLog	1	224	ToDoLogID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_ToDoPmts	Y.Y	ToDoPmts	1	4	ToDoPmts	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_ToDoTasks	Y.Y	ToDoTasks	1	126	ToDoTasksID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	1	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_TransData	Y.Y	TransData	1	188	TransDataID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_TShirtData	Y.Y	TShirtData	1	386	TShirtID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_VideoNotifSent	Y.Y	VideoNotifSent	1	7	VideoNotifSentID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_VideosSent	Y.Y	VideosSent	1	682	VideosSentID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_Visitors	Y.Y	Visitors	1	952689	VisitorID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_Waiver	Y.Y	Waiver	1	253	WaiverID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_WaveData	Y.Y	WaveData	1	23	WaveID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_YaWannaRace	Y.Y	YaWannaRace	1	7	YaWannaRaceID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	1	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_YWREvents	Y.Y	YWREvents	1	0	YWREventsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL

Open in new window


Here is the message returned:

Started @ 2014-11-11 10:15:17

(0 row(s) affected)
Midpoint @ 2014-11-11 10:15:17

(100 row(s) affected)
Ended @ 2014-11-11 10:15:18
SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Bob Schneider
Bob Schneider
Flag of United States of America image

ASKER

I got no security errors, no.  

A little background: I created this db over 10 years ago when I knew even less than I do now...so there is likely some design issues.  I am open to advice you can give me that will improve what I have here...and what you all have done so far is much appreciated!
SOLUTION
Avatar of Zberteoc
Zberteoc
Flag of Canada image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Zberteoc
Zberteoc
Flag of Canada image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Bob Schneider
Bob Schneider
Flag of United States of America image

ASKER

@Zberteoc, I did that and it suggested a nonclustered index so I created it as follows:

USE [VIRA]
GO
CREATE NONCLUSTERED INDEX [IndRsltsIndex]
ON [dbo].[IndResults] ([RaceID],[EventPl],[FnlTime])
INCLUDE ([ParticipantID],[ChipStart],[ChipTime])
GO

Open in new window


Is this now a part of the db?  In other words, if I repeat the process will it show that this index exists or is not needed?

@ScottPletcher, I am looking at your post now.
Avatar of Zberteoc
Zberteoc
Flag of Canada image
@Scott

No, it is NOT false. If he listens to me I guarantee will solve the problem. Changing the cluster index is NOT necessary here.
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image
>> I understand what you are saying but I don't think is necessary to redesign indexes. <<

I'm sorry but you're just wrong.  The problem with that approach is that you will end up with gazillions of indexes, one for each query.

If you properly cluster the table instead, you have vastly fewer indexes to maintain, and all queries can share the same data blocks in the buffer pool, which is also critical to performance.

The first thing to do is to get the best clustered index on the table.  After that, you can add nonclus and other covering index(es) if needed.

I'm not saying the index above won't help that query, but it comes at too high a cost.  It's a waste.  Look at how many columns in the table are being duplicated:
([RaceID],[EventPl],[FnlTime]) ([ParticipantID],[ChipStart],[ChipTime])
just for one very simple query, as you rightly noted.

Now what happens as I write thousands of queries against that table?  For every query, I have to check the indexes.  That's just terrible practice.

What about when the table has 4B rows?  Three extra indexes is 12B extra index rows.

All those indexes have to be maintained every time the table is modified.  Every DELETE, INSERT and UPDATE affects every such index.
Avatar of Bob Schneider
Bob Schneider
Flag of United States of America image

ASKER

First of all, I have so much respect for the knowledge needed to do this right and I am concerned that I will mess it up.

Secondly, how would I go about having a pro look at this db and "fix it", or at least tell me what needs to be done, and how much it would cost to do it.  We are very small but getting bigger and this is the biggest part of what we do.

Finally, ScottPletcher, are you suggesting I copy this in the New Query window and run it as is?

ALTER TABLE Participant DROP CONSTRAINT PK_Participant
CREATE UNIQUE CLUSTERED INDEX CL_Participant ON Participant ( RaceID, ParticipantID ) WITH ( FILLFACTOR = 96 )
ALTER TABLE Participant ADD CONSTRAINT PK_Participant PRIMARY KEY NONCLUSTERED ( ParticipantID )

ALTER TABLE IndResults DROP CONSTRAINT PK_IndResults
CREATE UNIQUE CLUSTERED INDEX CL_IndResults ON IndResults ( RaceID, ParticipantID ) WITH ( FILLFACTOR = 96 )
ALTER TABLE IndResults ADD CONSTRAINT PK_IndResults PRIMARY KEY NONCLUSTERED ( IndRsltsID )

ALTER TABLE PartRace DROP CONSTRAINT PK_PartRace
CREATE CLUSTERED INDEX CL_PartRace ON PartRace ( RaceID, ParticipantID ) WITH ( FILLFACTOR = 96 )
ALTER TABLE PartRace ADD CONSTRAINT PK_PartRace PRIMARY KEY NONCLUSTERED ( PartRaceID )

Open in new window

Avatar of Zberteoc
Zberteoc
Flag of Canada image
Scott, leave the theory for articles not for forums. We all know you are always right only that what you propose is NOT NECESSARY here...

One thing, though, I assumed that he has at least the PKs on those ID columns.
Avatar of Zberteoc
Zberteoc
Flag of Canada image
@BobbyBuoy:

"Finally, ScottPletcher, are you suggesting I copy this in the New Query window and run it as is?"

Don't do that before you try my suggestions. Scott's is a way to radical solution. Like I said, is no need to redesign indexes. Beside that Scott assumed only that you don't have those indexes already.

I am for the idea to fix rather than redesigned.

Check if you are missing any indexes and if not check the fragmentation. You should do that anyway, actually, and I recommend to do it periodically. Scott is right, this is a DBA job but if you don't have one or if he doesn't do it you should tell him.
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image
He does, and THAT IS THE PROBLEM!

This idiotic notion that there is such a thing as a "default" clustered index is the worst myth in the history of dbs.

You're just too short-sighted here Z.  Re-read what I wrote above.

Trust me, BobbaBuoy, I am an expert on this.  I've reduced I/O 100-fold just by properly clustering tables.  And removed thousands of indexes at the same time, while dramatically increasing SELECT speed.

A developer quick-and-dirty fix and "my query runs ok so what do I care" is not the proper answer here.  

Trying to tune a table without the wrong clustered index is like trying to get better mileage with a 2-ton weight on your vehicle.  Yeah, you can do some minor things to tweak your mileage, but the main problem still exists.
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image
>> ScottPletcher, are you suggesting I copy this in the New Query window and run it as is? <<

Yes.  Then re-run your query.  Better yet, if you have other query(ies) that were slow before, run them too.  You'll see a dramatic improvement in speed in almost all queries.
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image
@Z:
>>  Beside that Scott assumed only that you don't have those indexes already. <<

You need to read the q.  I had him run a query that listed the indexes on those tables.

Even if those indexes did happen to exist but are not clustered, they don't solve the problem anyway.

Over and out.  You're wasting our time here.

To the OP, I can guarantee you now that Z is not a DBA (or he's an awful one, maybe, I guess).
Avatar of Zberteoc
Zberteoc
Flag of Canada image
I am not saying Scott's solution won't work, only that is not necessary... You DON'T HAVE to redesign your indexes. One simple index that is missing or de-fragmentation can solve your problem.
Avatar of Bob Schneider
Bob Schneider
Flag of United States of America image

ASKER

First of all this is very, very helpful.  Thank you both!  

@ScottPletcher, I did that and got this in return: Column name 'RaceID' does not exist in the target table or view.
Avatar of Zberteoc
Zberteoc
Flag of Canada image
"I'm sorry but you're just wrong.  The problem with that approach is that you will end up with gazillions of indexes, one for each query.

If you properly cluster the table instead, you have vastly fewer indexes to maintain, and all queries can share the same data blocks in the buffer pool, which is also critical to performance."

One way of clustering or another DOESN't guarantee that will cover ALL possible querying alternatives or queries. In the end you will still need multiple indexes. It would be nice to one shot for all the cases but that will never be the case.
SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Bob Schneider
Bob Schneider
Flag of United States of America image

ASKER

To expand, RaceID does not exist in the Participant table only the IndResults and PartRace tables.  Of course, it is the PK in the RaceData table....
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image
>> One way of clustering or another DOESN't guarantee that will cover ALL possible querying alternatives or queries. In the end you will still need multiple indexes. It would be nice to one shot for all the cases but that will never be the case. <<

Again: you need to actually read the q.

Here's what I wrote earlier:

The first thing to do is to get the best clustered index on the table.  After that, you can add nonclus and other covering index(es) if needed.
Avatar of Bob Schneider
Bob Schneider
Flag of United States of America image

ASKER

Now I get this...

Msg 1505, Level 16, State 1, Line 2
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.IndResults' and the index name 'CL_IndResults'. The duplicate key value is (21, 343).
The statement has been terminated.
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image
Just drop the word UNIQUE.  I was hoping it could be UNIQUE, but if not, that's not a big deal.
Avatar of Bob Schneider
Bob Schneider
Flag of United States of America image

ASKER

Now this...

Msg 3728, Level 16, State 1, Line 1
'PK_IndResults' is not a constraint.
Msg 3727, Level 16, State 0, Line 1
Could not drop constraint. See previous errors.
Avatar of Zberteoc
Zberteoc
Flag of Canada image
"The first thing to do is to get the best clustered index on the table.  After that, you can add nonclus and other covering index(es) if needed."

So how is that different from creating a "gazillion indexes"? In the end you still need to create indexes based on different way of querying. Beside that you can build indexes and include columns, in which case the cluster is not even used.
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image
>> It would be nice to one shot for all the cases but that will never be the case. <<

Actually, a lot of tables end up with just a single index, particularly look up tables.  

If, for example, I have a lookup table that tells me code '07' means 'Invoiced, not shipped yet', I don't typically use anything but the code value to look up a row.  And I cluster the table on code, rather than by identity and then create a separate, nonclustered index with keyed by code, because that's just a stupid approach in that case.
SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Zberteoc
Zberteoc
Flag of Canada image
"If, for example, I have a lookup table that tells me code '07' means 'Invoiced, not shipped yet',"

Well, of course you do that but I am not talking about these simple cases. However, unless 07 has some specific meaning that I would preffer an identity column and just insert descriptions as needed instead of "inventing" codes. Not to mention that integer 7 takes less space, half to be exact, that 07 varchar. Multiple that with gazillion of invoices/orders.
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image
An int takes 4 bytes.  A char(2) would take 2 bytes.

I'm truly done wasting time.  You have no clue what you're talking about in properly designing and administering indexes.
SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Zberteoc
Zberteoc
Flag of Canada image
@BobbaBuoy

That is what I was afraid of. Scott made you drop PK constraints and recreate them which in case of production environment is not really recommended. Instead, checking the missing index and creating it would have been much less intrusive and with the same result...
SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Bob Schneider
Bob Schneider
Flag of United States of America image

ASKER

Now I have this:

Msg 1018, Level 15, State 1, Line 1
Incorrect syntax near 'INDEX'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.
Msg 1018, Level 15, State 1, Line 5
Incorrect syntax near 'INDEX'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.
Avatar of Bob Schneider
Bob Schneider
Flag of United States of America image

ASKER

BTW, while I sense a great deal of professional animosity between you guys, I want you to know that it is all very helpful to me.  Thanks!!!!
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image
Yeah, I've corrected that.  Check my previous post.

Normally I'd review even more things before changing indexes, but this is obvious it's an easy one.
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image
Still, it's odd that no index usage showed in the stats.  I guess you're on a dev server or something?
Avatar of Bob Schneider
Bob Schneider
Flag of United States of America image

ASKER

And I also know better now how little I know.  If we get this optimized I will be eternally grateful...and probably look for the resources to have a pro re-do this db for me.  But for now I just need it to work as well as possible.
Avatar of Zberteoc
Zberteoc
Flag of Canada image
"An int takes 4 bytes.  A char(2) would take 2 bytes."

How much takes a tiny int then? You knew very well what I meant...

Scott, yo don't need to drop PK constraints to improve performance of a simple query. Period.
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image
Not animosity from me, I just don't like false info being not just offered but forced on people.

For example, yet again:
>> Instead, checking the missing index and creating it would have been much less intrusive and with the same result... <<
Changing the clustered index on a table will never yield the "same result" as creating a new nonclus index on a table.  That's axiomatic.
Avatar of Bob Schneider
Bob Schneider
Flag of United States of America image

ASKER

Msg 3701, Level 11, State 7, Line 1
Cannot drop the index 'IndResults.PK_IndResults', because it does not exist or you do not have permission.
Msg 3723, Level 16, State 4, Line 5
An explicit DROP INDEX is not allowed on index 'PartRace.PK_PartRace'. It is being used for PRIMARY KEY constraint enforcement.
Avatar of Bob Schneider
Bob Schneider
Flag of United States of America image

ASKER

I am using SQL Server 2012 Express...
Avatar of Bob Schneider
Bob Schneider
Flag of United States of America image

ASKER

Sorry...I was working remotely....now I logged into the server and go this:

Msg 1913, Level 16, State 1, Line 2
The operation failed because an index or statistics with name 'CL_IndResults' already exists on table 'IndResults'.

That looks like good news to me????
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image
Hmm, it has to be a constraint or an index.

Let's do this.  Just script out the DROP and CREATE of the existing constraints.  Right-click on the constraint in Object Explorer, and select "Script Constraint as" and "DROP and CREATE to" a new query window.

Run the DROPs only first,
run my CREATE CLUSTERED INDEX commands,
then run the CREATEs.

Or I can write a quick script that will generate the necessary commands.
Avatar of Zberteoc
Zberteoc
Flag of Canada image
"Msg 3701, Level 11, State 7, Line 1
Cannot drop the index 'IndResults.PK_IndResults', because it does not exist or you do not have permission.
Msg 3723, Level 16, State 4, Line 5
An explicit DROP INDEX is not allowed on index 'PartRace.PK_PartRace'. It is being used for PRIMARY KEY constraint enforcement."

That is what I was talking about. What Scott does is way to intrusive and un-necessary. You don't need a cannon to kill a fly!
SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Bob Schneider
Bob Schneider
Flag of United States of America image

ASKER

First one:
CL_IndResults	clustered located on PRIMARY	RaceID, ParticipantID
IndRsltsIndex	nonclustered located on PRIMARY	RaceID, EventPl, FnlTime

Open in new window


Second one:
IndResultsIndex1	nonclustered located on PRIMARY	ParticipantID, RaceID
IndRsltsIndex	nonclustered located on PRIMARY	RaceID
PK_PartRace	clustered, unique, primary key located on PRIMARY	PartRaceID

Open in new window

SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Bob Schneider
Bob Schneider
Flag of United States of America image

ASKER

Sorry...got called away.  I agree on the abbreviation issue.  Again, did that a long time ago.
The first work we did today was run on EM remotely.  At the end I went into the server itself and ran from EM there.

First went fine...

First part of the second one said I didn't have permissions to do it or it didn't exist so I did it again from my remote EM and it still didn't go.

Second part of the second generated this:
Msg 1913, Level 16, State 1, Line 1
The operation failed because an index or statistics with name 'CL_IndResults' already exists on table 'IndResults'.
SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Bob Schneider
Bob Schneider
Flag of United States of America image

ASKER

Both completed successfully Homer.  :)
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Bob Schneider
Bob Schneider
Flag of United States of America image

ASKER

Thank you very much!
Avatar of Bob Schneider
Bob Schneider
Flag of United States of America image

ASKER

Iincredibly helpful solution and discussion!  Thanks to all!!!!!
Avatar of Scott Fell
Scott Fell
Flag of United States of America image
This was a very good discussion.   Can you estimate how much this sped things up?
Avatar of Bob Schneider
Bob Schneider
Flag of United States of America image

ASKER

I didn't check before or after rendering speed but within a week we are going to try to crash the site and then I will have an idea of how much it has improved.
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image
Interesting.  Thanks for letting us know the results.
Avatar of Bob Schneider
Bob Schneider
Flag of United States of America image

ASKER

Hopefully I can do this with my limited staff this weekend...fortunately or unfortunately, I am also upgrading to the sql server web so I hope there is a ton of improvement but I won't know where the improvement came from (indexing or server upgrade).  Nonetheless this site, once again, has proven its value.  Now I just need to find a way to get a dba on retainer so that I can run my little operation as it should be run.
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image
Or keeping posting such qs here :-).

But seriously, yes, indexing in particular does require a DBA.  And, in fact, not just any DBA but a good one.  Too many people indexing myths instead of studying real numbers and results.

A really good DBA could do more for you in table and index designs in 2-4 hours than weeks of developers' time.
Avatar of Scott Fell
Scott Fell
Flag of United States of America image
Going to another sql should be easy.  I think you just need to do a back of of the db on express, then restore to the new db.  The only gotcha is you will end up with an orphaned user.    If you are using plesk, there is an automatic function for that.  Otherwise, you know where to go if you get stuck.

Once you get the user updated it should work fine.