Speed Up Query? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Speed Up Query?

Author

Topic

JimAmigo
Posting Yak Master

119 Posts

Posted - 2007-01-18 : 10:07:34

I'm hoping to speed up the query below. Currently it takes 3 to 4 seconds to process. I think the issue is when it loops through each ORG to see when it was last modified but I have no idea of how to optimize it. Any of your suggestions would be greatly appreciated.

select DISTINCT o.ORGID
, o.NAME
, o.FIRSTNAME + ' ' + o.LASTNAME AS ORGOwner
, d.LASTMODIFIED
, d. BudgetYear
, s.FIRSTNAME + ' ' + s.LASTNAME AS modifiedby_NAME

from vw_Organizations_with_Budgets as o
left outer
join tblBMS_Data as d
on d.ORGID = o.ORGID
and d.LASTMODIFIED =
( select max(LASTMODIFIED)
from tblBMS_Data
where ORGID = o.ORGID
AND BudgetYear = 2007)
left outer
join vw_tblStaff as s
on s.STAFFID = d.LASTMODIFIEDBY
ORDER BY o.NAME

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-01-18 : 10:47:21

1. Why is DISTINCT in there?

2. You are querying a View it looks like (vw_Organizations_with_budgets). Obviously, therefore, this SELECT's efficiency is depedant on the efficiency of the view that you are querying. So, we need the definition for that view as well.

3. We also need table definitions, data types, indexes, and so on. Often, to make things more efficient, the first thing to check is table structures and indexes.

4. Just noticed another view ... vw_tblStaff ... same deal with that one as well.

- Jeff

JimAmigo
Posting Yak Master

119 Posts

Posted - 2007-01-18 : 11:37:45

quote:
Originally posted by jsmith8858

1. Why is DISTINCT in there?

2. You are querying a View it looks like (vw_Organizations_with_budgets). Obviously, therefore, this SELECT's efficiency is dependent on the efficiency of the view that you are querying. So, we need the definition for that view as well.

3. We also need table definitions, data types, indexes, and so on. Often, to make things more efficient, the first thing to check is table structures and indexes.

4. Just noticed another view ... vw_tblStaff ... same deal with that one as well.

- Jeff

1. Good Question. I took distinct out but the processing time was the same.

2. Here are the views..

1st view, vw_Organizations_with_budgets

SELECT TOP 100 PERCENT o.OrgID,o.TypeID, o.Name, o.HNC_Code, s.Prefix, s.FirstName, s.MidName, s.LastName,
s.Suffix, s.Degree
FROM vw_tblOrg o LEFT OUTER JOIN
vw_tblStaff s ON o.ManagerID = s.StaffID
WHERE (o.Active = 1) AND (o.HasBudget = 1) AND (o.IsCCR = 1) AND (o.ParentOrgID IS NULL OR o.ParentOrgID=0)
ORDER BY o.Name

2nd view, vw_tblStaff. This is a view to another db I don't have access to.

SELECT StaffID, NED_ID, Prefix, FirstName, MidName, LastName, Suffix, Degree, Active, IsPI, IsAO, IsARC, IsCCR, LastModified
FROM CCR_CMS.dbo.tblStaff

3. o.ORGID, type numeric
o.NAME, type varchar
o.FIRSTNAME & o.LASTNAME, both type varchar
d.LASTMODIFIED, type datetime
d. BudgetYear, type numeric
s.FIRSTNAME & s.LASTNAME type varchar

I have run the views individually and they run fast. I would suspect my issue could be improved by a different way to join and how the select max is handled?

Thank you for your feedback and suggestions.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-01-18 : 13:19:11

indexes ??

- Jeff

JimAmigo
Posting Yak Master

119 Posts

Posted - 2007-01-18 : 15:26:48

quote:
Originally posted by jsmith8858

indexes ??

- Jeff

No indexes...

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-01-18 : 15:43:28

Sounds like your first task is to create indexes on these tables. Good candidates (in addition to the PK columns) appear to be the LASTMODIFIED, BudgetYear, StaffID and ORGID columns on the various tables.

these tables DO have primary keys, right ?????

- Jeff

JimAmigo
Posting Yak Master

119 Posts

Posted - 2007-01-18 : 16:11:58

quote:
Originally posted by jsmith8858

Sounds like your first task is to create indexes on these tables. Good candidates (in addition to the PK columns) appear to be the LASTMODIFIED, BudgetYear, StaffID and ORGID columns on the various tables.

these tables DO have primary keys, right ?????

- Jeff

Sorry I miss spoke, in tblBMS_Data there is 1 index on the primarky key profileid. Let me turn on an index on the other fields you mentioned and see what happens.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-01-18 : 16:31:07

Also, you probably should have the year filter in your WHERE clause:

select ~~DISTINCT~~ o.ORGID
, o.NAME
, o.FIRSTNAME + ' ' + o.LASTNAME AS ORGOwner
, d.LASTMODIFIED
, d. BudgetYear
, s.FIRSTNAME + ' ' + s.LASTNAME AS modifiedby_NAME

from vw_Organizations_with_Budgets as o
left outer
join tblBMS_Data as d
on d.ORGID = o.ORGID
and d.LASTMODIFIED =
( select max(LASTMODIFIED)
from tblBMS_Data
where ORGID = o.ORGID
AND BudgetYear = 2007)
left outer
join vw_tblStaff as s
on s.STAFFID = d.LASTMODIFIEDBY

WHERE BudgetYear = 2007

ORDER BY o.NAME

Other than that, and proper indexing, it will be hard to help you out without knowing for sure what this SELECT is supposed to be doing, what data you have, etc ...

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources