• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 219
  • Last Modified:

Need help with a qry's efficiency

Friends,

I have a qry (attached) that is taking forever to run.   Can anyone look to see where the problem lies?  It really runs into trouble with the Order By.  If I take out the group by and the order by statement, it works great, it just doesn't order it right (obviously).

Thanks in advance!

Best Regards,
Eric
Select
t.TrackOrder,
RTRIM(r.No) as CarNumber,
p.ResultItemID,
p.PassingTime,
p.ElapsedTime,
p.PassingID,
p.DecoderID,
p.LeaderLap, 
p.LapCount 
From Passings p with (nolock) inner join Results r with (nolock)
ON p.RunID=r.RunID
AND p.ResultItemID=r.ResultID
Inner Join Timelines t 
on t.DecoderID=p.DecoderID
AND t.LoopID=p.LoopID
Where 
p.Pit = 0
And 
passingtime in (SELECT max(i.passingtime) FROM passings i with (NOLOCK) WHERE
    i.ResultItemID = p.ResultItemID
    AND i.passingtime >=0
    AND i.lapcount>0
    AND i.Deleted=0
    AND p.Deleted=0
    AND i.RunID=6
	AND p.RunID=6
    AND i.DecoderID<> 0)--(Select Top 1 DecoderID From Timelines where IsLapCounter=1))
And p.RunID=6
And r.RunID=6
Group by p.ResultItemID, 
p.PassingTime, 
p.ElapsedTime, 
p.PassingID, 
p.DecoderID, 
p.LapCount, 
p.LeaderLap, 
t.TrackOrder,
r.No, 
r.RaceRank
Order By t.TrackOrder Desc, p.ElapsedTIme

Open in new window

0
indy500fan
Asked:
indy500fan
1 Solution
 
bull_riderCommented:
You have missed one more WITH (NOLOCK) in this statement

Inner Join Timelines t

Can you add and check?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
pls post the table structue including the indexes
0
 
cdaly33Commented:
You have a couple options.  You could try putting an index on the t.TrackOrder and p.ElapsedTIme fields which may or may not help depending on the amount of data.  Or you can try to find a way to sort it after the data has been retrieved such as in the program utilizing the data.  Or you if this is in a stored procedure you could create a temp table variable matching the structure that is returned from the statement and then select out of that temp table variable with the appropriate sorting applied then.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
indy500fanAuthor Commented:
ttached is the passings table structure.  By the way, the data inside the Timelines table doesn't change.

SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Passings](
	[RunID] [int] NOT NULL,
	[PassingID] [int] NOT NULL,
	[FlagState] [int] NOT NULL,
	[DecoderID] [int] NOT NULL,
	[TranNr] [int] NOT NULL,
	[PassingTime] [int] NOT NULL,
	[PhotocellTime] [int] NOT NULL,
	[ElapsedTime] [int] NOT NULL,
	[Hits] [int] NOT NULL,
	[Strength] [int] NOT NULL,
	[LowBatt] [bit] NOT NULL,
	[LoopID] [int] NOT NULL,
	[Pit] [bit] NOT NULL,
	[Deleted] [bit] NOT NULL,
	[Manual] [bit] NOT NULL,
	[Photocell] [bit] NOT NULL,
	[PhotocellState] [int] NOT NULL,
	[Flag] [bit] NOT NULL,
	[LeaderLap] [int] NOT NULL,
	[LapCount] [int] NOT NULL,
	[Laptime] [int] NOT NULL,
	[LaptimeViolation] [bit] NOT NULL,
	[Finish] [bit] NOT NULL,
	[ResultItemID] [int] NOT NULL,
	[EventID] [int] NOT NULL DEFAULT ((1000)),
	[SeasonID] [int] NOT NULL DEFAULT ((1001))
) ON [PRIMARY]
 
/****** Object:  Index [PASSINGID_INDEX]    Script Date: 05/15/2009 12:45:05 ******/
CREATE NONCLUSTERED INDEX [PASSINGID_INDEX] ON [dbo].[Passings] 
(
	[PassingID] DESC
)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]
 
/****** Object:  Table [dbo].[Results]    Script Date: 05/15/2009 12:45:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Results](
	[RunID] [int] NOT NULL,
	[ResultID] [int] NOT NULL,
	[TranNr] [int] NOT NULL,
	[Deleted] [bit] NOT NULL,
	[ExtraTotaltime] [int] NOT NULL,
	[ExtraLapcount] [int] NOT NULL,
	[ExtraBestLaptime] [int] NOT NULL,
	[OverallBestLaptime] [int] NOT NULL,
	[ResultStatus] [int] NOT NULL,
	[StartPosition] [int] NOT NULL,
	[Comment] [char](255) NOT NULL,
	[QualifyRank] [int] NOT NULL,
	[RaceRank] [int] NOT NULL,
	[BestLaptime] [int] NOT NULL,
	[BestLap] [int] NOT NULL,
	[BestSpeed] [float] NOT NULL,
	[SecondBestLaptime] [int] NOT NULL,
	[SecondBestLap] [int] NOT NULL,
	[SecondBestSpeed] [float] NOT NULL,
	[LastLaptime] [int] NOT NULL,
	[LastSpeed] [float] NOT NULL,
	[Laps] [int] NOT NULL,
	[Totaltime] [int] NOT NULL,
	[DiffQual] [char](70) NOT NULL,
	[GapQual] [char](70) NOT NULL,
	[DiffRace] [char](70) NOT NULL,
	[GapRace] [char](70) NOT NULL,
	[CompetitorID] [int] NOT NULL,
	[RaceMarker] [int] NOT NULL,
	[QualMarker] [int] NOT NULL,
	[AvgSpeed] [float] NOT NULL,
	[OnTrack] [int] NOT NULL,
	[PitStops] [int] NOT NULL,
	[LastPitLap] [int] NOT NULL,
	[SincePit] [int] NOT NULL,
	[SincePitLap] [int] NOT NULL,
	[FlagStatus] [char](255) NOT NULL,
	[No] [char](70) NOT NULL,
	[FirstName] [char](70) NOT NULL,
	[LastName] [char](70) NOT NULL,
	[Class] [char](70) NOT NULL,
	[DriverID] [char](70) NOT NULL,
	[TrpndrCtrl#] [char](70) NOT NULL,
	[Equipment] [char](70) NOT NULL,
	[License] [char](70) NOT NULL,
	[Team] [char](70) NOT NULL,
	[TeamID] [char](70) NOT NULL,
	[Engine] [char](70) NOT NULL,
	[EntrantID] [char](70) NOT NULL,
	[EventID] [int] NOT NULL DEFAULT ((1000)),
	[SeasonID] [int] NOT NULL DEFAULT ((1001))
 
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF
 
GO
/****** Object:  Index [RESULT_RUNID_INDEX]    Script Date: 05/15/2009 12:47:15 ******/
CREATE NONCLUSTERED INDEX [RESULT_RUNID_INDEX] ON [dbo].[Results] 
(
	[RunID] DESC
)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]
 
 
/****** Object:  Table [dbo].[Timelines]    Script Date: 05/15/2009 12:48:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Timelines](
	[m_UniqueID] [int] NOT NULL,
	[TrackID] [int] NOT NULL,
	[Name] [char](70) NOT NULL,
	[DecoderID] [int] NOT NULL,
	[LoopID] [int] NOT NULL,
	[TrackOrder] [int] NOT NULL,
	[DistanceFromSF] [float] NOT NULL,
	[IsSendToFeed] [bit] NOT NULL,
	[IsLapCounter] [bit] NOT NULL,
	[IsInPit] [bit] NOT NULL,
	[IsPitIn] [bit] NOT NULL,
	[IsPitOut] [bit] NOT NULL,
	[IsFirstAfterPitOut] [bit] NOT NULL,
	[IsBlendline] [bit] NOT NULL,
	[EventID] [int] NOT NULL DEFAULT ((1000)),
	[SeasonID] [int] NOT NULL DEFAULT ((1001))
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF

Open in new window

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Question?
Do you really need a GROUP BY part ?
I am not sure about the size of and values on the timelines table, can u create the following index on that table and try it..
if possibgle post the execution plan too
create index idx_Timelines_DecoderID_LoopID_TrackOrder ON TimeLines (DecoderID) INCLUDE (LoopID,TrackOrder)  
0
 
indy500fanAuthor Commented:
aneeshattingal,

I have tried it with the index.  No change.  Execution Plan attached as image.
ExecutionPlan.bmp
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
You need to create indexes on the RUNID's (both the tables)
Wgy dont you have a Primary key / Clustured indexes on those tables /
0
 
indy500fanAuthor Commented:
These tables are created by 3rd pary software  I'm not very good with DB's, and I'm weary about creating indexes.  I have very little sql experience.

Do you have suggestions as to creating the indexes?  Which fields on each table or do I create a new field and seed it?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
create index idx_Results_ResultID_RunID ON Results (RunID) INCLUDE (ResultID)  
create index idx_Passings_ResultItemID_RunID ON Passings (RunID) INCLUDE (ResultItemID)
0
 
indy500fanAuthor Commented:
Interesting.  I ran it and it was faster against today's dataset, but I need to try one more test tomorrow morning with that dataset.

Thank you for your help so far!

Regards,
Eric
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
the query can be even faster with a primary key &clustured index, as a rule you should make sure that you have a primary key for all the tables
0
 
indy500fanAuthor Commented:
I may try that!  I really think we are on the right path!
0
 
indy500fanAuthor Commented:
The indexes did the trick!  Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now