Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help optimizing this query

Status
Not open for further replies.
BG12424

BG12424

Programmer
Jun 4, 2002
717
US
I need help optimizing this query. I know there are many table joins, but unfortunately, we need the data from these tables. If it's not smart to join this many tables, what alternatives do I have?

What can I do to make this run faster. Right now, it's slow as a slug returning any results. I tried using a temp table hoping to speed things up, but that didn't work for me. Thanks for your help ahead of time.

Code: 
select 	j.JobID, 
	j.JobNumber, 
	j.ClientID, 
	g.GroupName as 'CompanyName', 
	f.FacilityID, 
	f.FacilityName, 
	j.UserID, 
	u_pm.FirstName + ' ' + u_pm.LastName as 'ProjectManager', 
	j.ProjectName, 
	(select min(dbo.fn__FormatDate(b.TimeEventStart,'mm/dd/yyyy')) 
		from ems_test2..tblBooking b 
		where ReservationId = j.ReservationId) as 'MinStudyDate',
	m.Moderator, 
	j.Color, 
	j.ReservationID, 
	r.EventName, 
	s.Description as 'EMSStatus', 
	j.Notes, 
	j.SpecialNeeds, 
	j.CoopAdvancedRecd, 
	j.ContractSigned, 
	c.ID as 'ContactId', 
	c.Contact, 
	c.Phone as 'ContactPhone',
	(select max(dbo.fn__FormatDate(b.TimeEventStart,'mm/dd/yyyy')) 
		from ems_test2..tblBooking b 
		where ReservationId = j.ReservationId) as 'StudyDate'
into 	#tblJobList3
from 	tblJobs j,
	ems_test2..tblGroup g,
	tblFacility f,
	tblUsers u_pm,
	tblModerator m,
	ems_test2..tblReservation r,
	ems_test2..tblContact c,
	tblGlobalRelations gbl,
	tblBids b,
	ems_test2..tblStatus s
where 	j.ClientId = g.ID
and	j.FacilityID = f.FacilityID
and	j.UserID = u_pm.UserId
and	j.Moderator *= m.ModeratorId
and 	g.ID = c.GroupID
and 	j.JobId = gbl.JobId
and	b.BidId = gbl.BidId
and	b.ClientContactId = c.ID
and	j.ReservationId = r.ID
and	j.EMSStatus = s.ID
and	j.EMSStatus not in (5,6,12)
and	gbl.NetFacBidId is null
and 	j.FacilityID = @FacilityId
and	j.ActiveFlag = 1
order by StudyDate desc

select * from #tblJobList3 where cast(convert(varchar(20),StudyDate,101) as datetime) >= cast(convert(varchar(20),getdate(),101) as datetime) --order by StudyDate desc
drop table #tblJobList3

regards,
Brian
The problem with doing something right the first time is that nobody appreciates how difficult it was.
 
Sort by date Sort by votes
First I'd change from the old format of doing joins to the new format. It won't do anything for speed, but eventually the old format may not be supported any more.

I also remember a bug with SQL 2000 at some point where using an outer join using the old format gives funky issues. I can't remember more about it as it was a while ago.

With a query like this proper indexes and a good understanding of how to read the execution plan will be key in speeding things up.

Pulling from this may tables isn't really a problem.

I'd also get rid of the select into command. Instead manually create the table, then do a normal insert into command. Loading data into a table via the select into command can cause locking of syste objects which can then cause problems with other queries which are trying to read the system objects.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)
[noevil]
 
Upvote 0 Downvote
thanks Denny,

I've updated my query to create a temp table instead of using the select into and I don't get a performance enhancement.

Are there any thoughts with the 2 sub-selects within the main select? Also, should I be inserting data a certain way into the table, or removing any of my conditionals? Thanks

Code: 
create table #tblJobList3 (
	JobId int primary key,
	JobNumber varchar(15),
	ClientID int,
	CompanyName varchar(50),
	FacilityID int,
	FacilityName varchar(255),
	UserID int,
	ProjectManager varchar(200),
	ProjectName varchar(200),
	MinStudyDate varchar(10),
	Moderator varchar(100),
	Color varchar(25),
	ReservationID int,
	EventName varchar(255),
	EMSStatus varchar(30),
	Notes varchar(500),
	SpecialNeeds varchar(500),
	CoopAdvancedRecd bit,
	ContractSigned bit,
	ContactId int,
	Contact varchar(50),
	ContactPhone varchar(20),
	StudyDate varchar(10)
)

insert into #tblJobList3 (
	JobId,
	JobNumber,
	ClientID,
	CompanyName,
	FacilityID,
	FacilityName,
	UserID,
	ProjectManager,
	ProjectName,
	MinStudyDate,
	Moderator,
	Color,
	ReservationID,
	EventName,
	EMSStatus,
	Notes,
	SpecialNeeds,
	CoopAdvancedRecd,
	ContractSigned,
	ContactId,
	Contact,
	ContactPhone,
	StudyDate
) select     j.JobID, 
    j.JobNumber, 
    j.ClientID, 
    g.GroupName as 'CompanyName', 
    f.FacilityID, 
    f.FacilityName, 
    j.UserID, 
    u_pm.FirstName + ' ' + u_pm.LastName as 'ProjectManager', 
    j.ProjectName, 
    (select min(dbo.fn__FormatDate(b.TimeEventStart,'mm/dd/yyyy')) 
        from ems_test2..tblBooking b 
        where ReservationId = j.ReservationId) as 'MinStudyDate',
    m.Moderator, 
    j.Color, 
    j.ReservationID, 
    r.EventName, 
    s.Description as 'EMSStatus', 
    j.Notes, 
    j.SpecialNeeds, 
    j.CoopAdvancedRecd, 
    j.ContractSigned, 
    c.ID as 'ContactId', 
    c.Contact, 
    c.Phone as 'ContactPhone',
    (select max(dbo.fn__FormatDate(b.TimeEventStart,'mm/dd/yyyy')) 
        from ems_test2..tblBooking b 
        where ReservationId = j.ReservationId) as 'StudyDate'
from     tblJobs j,
    ems_test2..tblGroup g,
    tblFacility f,
    tblUsers u_pm,
    tblModerator m,
    ems_test2..tblReservation r,
    ems_test2..tblContact c,
    tblGlobalRelations gbl,
    tblBids b,
    ems_test2..tblStatus s
where     j.ClientId = g.ID
and    j.FacilityID = f.FacilityID
and    j.UserID = u_pm.UserId
and    j.Moderator *= m.ModeratorId
and     g.ID = c.GroupID
and     j.JobId = gbl.JobId
and    b.BidId = gbl.BidId
and    b.ClientContactId = c.ID
and    j.ReservationId = r.ID
and    j.EMSStatus = s.ID
and    j.EMSStatus not in (5,6,12)
and    gbl.NetFacBidId is null
and     j.FacilityID = 2
and    j.ActiveFlag = 1

select * from #tblJobList3 where cast(convert(varchar(20),StudyDate,101) as datetime) >= cast(convert(varchar(20),getdate(),101) as datetime) order by StudyDate desc
drop table #tblJobList3

regards,
Brian
The problem with doing something right the first time is that nobody appreciates how difficult it was.
 
Upvote 0 Downvote
Try changing create table #tblJobList3 to Declare @tblJobList3 table(definitions) and all the references from # to @. It may help a little because SQL won't be maintaining a transaction log.

You won't need to drop after that.
 
Upvote 0 Downvote
Thanks, I did that but it still seems a bit sluggish (about 9 seconds to run this query).

I also updated the routine to add min/max StudyDates into their own temp tables and add back to the main table prior to getting final results.

Here are row counts to the tables (growing of course)
tblBooking (26241)
tblGroup (3327)
tblReservations (11434)
tblContact (5169)

The rest of the tables are < 100 records, but (for future reference, the tblBids, tblJobs, tblGlobalRelations will grow significantly -- new tables using small amount of test data at the moment)

Code: 
declare @tblJobList table (
	JobId int primary key,
	JobNumber varchar(15),
	ClientID int,
	CompanyName varchar(50),
	FacilityID int,
	FacilityName varchar(255),
	UserID int,
	ProjectManager varchar(200),
	ProjectName varchar(200),
	MinStudyDate varchar(10),
	Moderator varchar(100),
	Color varchar(25),
	ReservationID int,
	EventName varchar(255),
	EMSStatus varchar(30),
	Notes varchar(500),
	SpecialNeeds varchar(500),
	CoopAdvancedRecd bit,
	ContractSigned bit,
	ContactId int,
	Contact varchar(50),
	ContactPhone varchar(20),
	StudyDate varchar(10)
)

insert into @tblJobList (
	JobId,
	JobNumber,
	ClientID,
	CompanyName,
	FacilityID,
	FacilityName,
	UserID,
	ProjectManager,
	ProjectName,
	MinStudyDate,
	Moderator,
	Color,
	ReservationID,
	EventName,
	EMSStatus,
	Notes,
	SpecialNeeds,
	CoopAdvancedRecd,
	ContractSigned,
	ContactId,
	Contact,
	ContactPhone,
	StudyDate
) select     j.JobID, 
    j.JobNumber, 
    j.ClientID, 
    g.GroupName as 'CompanyName', 
    f.FacilityID, 
    f.FacilityName, 
    j.UserID, 
    u_pm.FirstName + ' ' + u_pm.LastName as 'ProjectManager', 
    j.ProjectName, 
null,
    m.Moderator, 
    j.Color, 
    j.ReservationID, 
    r.EventName, 
    s.Description as 'EMSStatus', 
    j.Notes, 
    j.SpecialNeeds, 
    j.CoopAdvancedRecd, 
    j.ContractSigned, 
    c.ID as 'ContactId', 
    c.Contact, 
    c.Phone as 'ContactPhone',
	null
from     tblJobs j,
    ems_test2..tblGroup g,
    tblFacility f,
    tblUsers u_pm,
    tblModerator m,
    ems_test2..tblReservation r,
    ems_test2..tblContact c,
    tblGlobalRelations gbl,
    tblBids b,
    ems_test2..tblStatus s
where     j.ClientId = g.ID
and    j.FacilityID = f.FacilityID
and    j.UserID = u_pm.UserId
and    j.Moderator *= m.ModeratorId
and     g.ID = c.GroupID
and     j.JobId = gbl.JobId
and    b.BidId = gbl.BidId
and    b.ClientContactId = c.ID
and    j.ReservationId = r.ID
and    j.EMSStatus = s.ID
and    j.EMSStatus not in (5,6,12)
and    gbl.NetFacBidId is null
and     j.FacilityID = 2
and    j.ActiveFlag = 1

declare @tblMinDt table (
	ReservationID int primary key,
	MinStudyDate varchar(10)
)

insert into @tblMinDt
select b.ReservationID, min(dbo.fn__FormatDate(b.TimeEventStart,'mm/dd/yyyy'))
from ems_test2..tblBooking b, @tblJobList j
where j.ReservationID = b.ReservationID
group by b.ReservationID

declare @tblMaxDt table (
	ReservationID int primary key,
	MaxStudyDate varchar(10)
)

insert into @tblMaxDt
select b.ReservationID, max(dbo.fn__FormatDate(b.TimeEventStart,'mm/dd/yyyy'))
from ems_test2..tblBooking b, @tblJobList j
where j.ReservationID = b.ReservationID
group by b.ReservationID

update 	@tblJobList
set 	MinStudyDate = mind.MinStudyDate,
	StudyDate = maxd.MaxStudyDate
from 	@tblJobList j, 
	@tblMinDt mind,
	@tblMaxDt maxd
where 	j.ReservationID = mind.ReservationID
and	j.ReservationID = maxd.ReservationID

select * from @tblJobList where cast(convert(varchar(20),StudyDate,101) as datetime) >= cast(convert(varchar(20),getdate(),101) as datetime) order by StudyDate desc

regards,
Brian

 
Upvote 0 Downvote
What is this part:
Code: 
    (select max(dbo.fn__FormatDate(b.TimeEventStart,'mm/dd/yyyy'))
        from ems_test2..tblBooking b
        where ReservationId = j.ReservationId) as 'StudyDate'
... supposed to do?

If it formats date to US format - and data comes from more than one year - then MAX() will return wrong result.

And yes - I'd say these two subqueries can slow things exec time down significantly.

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]
[banghead]
 
Upvote 0 Downvote
thanks for the information. I should maybe add the max prior to the formatting, huh? ;)

here is the function for formatting the date

ALTER FUNCTION fn__FormatDate (
@date datetime,
@format_string varchar(100)
)
RETURNS varchar(100)
AS
-- Any format string (mm, m, dd, d, yyyy, yy, hh, h, nn, n, ss, s)
BEGIN
DECLARE @date_string as varchar(100)
SET @date_string = @format_string
-- handle year - yyyy
SET @date_string = REPLACE(@date_string, 'yyyy', CAST(YEAR(@date) AS char(4)))
-- handle year - yy
SET @date_string = REPLACE(@date_string, 'yy', RIGHT(CAST(YEAR(@date) AS char(4)), 2))
-- handle milliseconds - ms
-- handle before months and seconds not to confuse a single m or s
SET @date_string = REPLACE(@date_string, 'ms', REPLICATE('0', 3 - LEN(CAST(DATEPART(ms, @date) AS varchar(3)))) + CAST(DATEPART(ms, @date) AS varchar(3)))
-- handle month - mm - leading zero, m - no leading zero
SET @date_string = REPLACE(@date_string, 'mm', REPLICATE('0', 2 - LEN(CAST(MONTH(@date) AS varchar(2)))) + CAST(MONTH(@date) AS varchar(2)))
SET @date_string = REPLACE(@date_string, 'm', CAST(MONTH(@date) AS varchar(2)))
-- handle day - dd - leading zero, d - no leading zero
SET @date_string = REPLACE(@date_string, 'dd', REPLICATE('0', 2 - LEN(CAST(DAY(@date) AS varchar(2)))) + CAST(DAY(@date) AS varchar(2)))
SET @date_string = REPLACE(@date_string, 'd', CAST(DAY(@date) AS varchar(2)))

-- handle hour - hh - leading zero, h - no leading zero
SET @date_string = REPLACE(@date_string, 'hh', REPLICATE('0', 2 - LEN(CAST(DATEPART(hh, @date) AS varchar(2)))) + CAST(DATEPART(hh, @date) AS varchar(2)))
SET @date_string = REPLACE(@date_string, 'h', CAST(DATEPART(hh, @date) AS varchar(2)))
-- handle minute - nn - leading zero, n - no leading zero
SET @date_string = REPLACE(@date_string, 'nn', REPLICATE('0', 2 - LEN(CAST(DATEPART(n, @date) AS varchar(2)))) + CAST(DATEPART(n, @date) AS varchar(2)))
SET @date_string = REPLACE(@date_string, 'n', CAST(DATEPART(n, @date) AS varchar(2)))
-- handle second - ss - leading zero, s - no leading zero
SET @date_string = REPLACE(@date_string, 'ss', REPLICATE('0', 2 - LEN(CAST(DATEPART(ss, @date) AS varchar(2)))) + CAST(DATEPART(ss, @date) AS varchar(2)))
SET @date_string = REPLACE(@date_string, 's', CAST(DATEPART(ss, @date) AS varchar(2)))
RETURN @date_string
END


regards,
Brian

 
Upvote 0 Downvote
I modified where the FormatDate function is placed. Thanks for bringing that up.

Are there other areas which I should look into optimzing? Someone told me that if I narrow down the filtering with the where's before I actually do a join to all the tables, it should be a performance gain (was referring to Oracle, but not sure about SQL Server); so for example:

where date < somedate and someid < 1000 and table1.id = table2.id and table1.otherid = table3.id

does this hold true for SQL Server as well?

Code: 
declare @tblMinDt table (
	ReservationID int primary key,
	MinStudyDate varchar(10)
)

insert into @tblMinDt
select b.ReservationID, dbo.fn__FormatDate(min(b.TimeEventStart),'mm/dd/yyyy')
from ems_test2..tblBooking b, @tblJobList j
where j.ReservationID = b.ReservationID
group by b.ReservationID

declare @tblMaxDt table (
	ReservationID int primary key,
	MaxStudyDate varchar(10)
)

insert into @tblMaxDt
select b.ReservationID, dbo.fn__FormatDate(max(b.TimeEventStart),'mm/dd/yyyy')
from ems_test2..tblBooking b, @tblJobList j
where j.ReservationID = b.ReservationID
group by b.ReservationID

update 	@tblJobList
set 	MinStudyDate = mind.MinStudyDate,
	StudyDate = maxd.MaxStudyDate
from 	@tblJobList j, 
	@tblMinDt mind,
	@tblMaxDt maxd
where 	j.ReservationID = mind.ReservationID
and	j.ReservationID = maxd.ReservationID

select * from @tblJobList where cast(convert(varchar(20),StudyDate,101) as datetime) >= cast(convert(varchar(20),getdate(),101) as datetime) order by StudyDate desc

regards,
Brian

 
Upvote 0 Downvote
Can you resolve MIN and MAX with one query?

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]
[banghead]
 
Upvote 0 Downvote
Are suggesting using a single MinMaxDate @tempTable and store the MIN and MAX dates into that single table using a single query getting both? If so, I tried that, but I wasn't able to get it. Do you know what that would be? Thanks for your help.

regards,
Brian

 
Upvote 0 Downvote
Also, I going to change the original @tblJobList table to store a datetime field instead of the formatted date string that I parse for each of the 26K records for both MIN and MAX values. I'll instead perform this format on the final select statement.

regards,
Brian

 
Upvote 0 Downvote
Something like:
Code: 
declare @tblMinMaxDt table (
    ReservationID int primary key,
    MinStudyDate varchar(10),
    MaxStudyDate varchar(10)
)

insert into @tblMinDt
select b.ReservationID, 
	dbo.fn__FormatDate(min(b.TimeEventStart),'mm/dd/yyyy'),
	dbo.fn__FormatDate(max(b.TimeEventStart),'mm/dd/yyyy')
from ems_test2..tblBooking b, @tblJobList j
where j.ReservationID = b.ReservationID
group by b.ReservationID

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]
[banghead]
 
Upvote 0 Downvote
Thanks, I did that and also removed the date formatting as well. I think this would slow things down as well. Again my tblBooking table has upwards of 26K records in it. Any ideas about some of my other questions listed above?

Code: 
declare @tblStudyDt table (
	ReservationID int primary key,
	MinStudyDate datetime,
	MaxStudyDate datetime
)

insert into @tblStudyDt
select b.ReservationID, min(b.TimeEventStart), max(b.TimeEventStart)
from ems_test2..tblBooking b, @tblJobList j
where j.ReservationID = b.ReservationID
group by b.ReservationID

update 	@tblJobList
set 	MinStudyDate = sd.MinStudyDate,
	StudyDate = sd.MaxStudyDate
from 	@tblJobList j, 
	@tblStudyDt sd
where 	j.ReservationID = sd.ReservationID

regards,
Brian

 
Upvote 0 Downvote
About first query... the big one?

Get rid of T-SQL joins.
For max. performance don't use UDF - in this case simple CONVERT() with format 101 should do fine.
Replace MIN()/MAX() subqueries with derived table and left outer join.

And in very first query ORDER BY is meaningless... unless I missed something.



------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]
[banghead]
 
Upvote 0 Downvote
Can you elaborate on the

"Replace MIN()/MAX() subqueries with derived table and left outer join."

I'm not sure I follow what you're saying here. Thanks

regards,
Brian

 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

V
  • Locked
  • Question
xp_cmdshell vs psexec - this SQL server forgets credential to an W10 share
Replies
0
Views
351
Volvere
V
f0rg3tfu1
  • Locked
  • Question
PLSQL Package Help
Replies
0
Views
190
f0rg3tfu1
f0rg3tfu1
peac3
  • Locked
  • Question
split one row into multiple rows... help pls 2
Replies
12
Views
483
Chris Miller
Chris Miller
leifoet
  • Locked
  • Question
Error in Select query
Replies
14
Views
280
Andrzejek
Andrzejek
bmacbmac
  • Locked
  • Question
Help searching within this Json file
Replies
0
Views
74
bmacbmac
bmacbmac

Part and Inventory Search

Sponsor

Back
Top