View running slow. Any issues with this query?

Question

View running slow. Any issues with this query?

Keith Knox

Mr or Mrs. 500

Points: 588
More actions
February 12, 2008 at 3:11 pm

#122026

If I do a select * from dbo.v_department it takes 1 minute and 40 seconds to run.
It has run in a second or 2 at times but lately it's slow and a cpu hog.
Any ideas?
This is also on a VMWare vm.
thanks
this is the v_department view:
SELECT DepartmentID, DepartmentName, AgencyName, ShortName, CreatedOn, Status, UpdatedOn, UpdatedBy, CASE WHEN
(SELECT COUNT(z.DepartmentID)
FROM tblDepartment AS z LEFT JOIN
tblFooter AS b ON a.departmentid = b.departmentid LEFT JOIN
tblHeader AS c ON a.departmentid = c.departmentid LEFT JOIN
tblLogo AS d ON a.departmentid = d .departmentid LEFT JOIN
tblPressRelease AS e ON a.departmentid = e.departmentid LEFT JOIN
tblUser AS f ON a.departmentID = f.departmentID
WHERE a.DepartmentID = z.DepartmentID) > 1 THEN 'N' ELSE 'Y' END AS DeleteOK, CASE WHEN
(SELECT COUNT(z.DepartmentID)
FROM tblDepartment AS z LEFT JOIN
tblFooter AS b ON a.departmentid = b.departmentid LEFT JOIN
tblHeader AS c ON a.departmentid = c.departmentid LEFT JOIN
tblLogo AS d ON a.departmentid = d .departmentid LEFT JOIN
tblPressRelease AS e ON a.departmentid = e.departmentid LEFT JOIN
tblUser AS f ON a.departmentID = f.departmentID
WHERE a.DepartmentID = z.DepartmentID AND b.Status = 1 OR
c.Status = 1 OR
d .Status = 1 OR
e.Archived = 0 OR
f.Status = 1) > 1 THEN 'N' ELSE 'Y' END AS InactivateOK
FROM dbo.tblDepartment AS a

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

julie gordon SSC Enthusiast Points: 106 More actions · Answer 1

Did you already check to make sure the indexes on the tables being queried are in place? Did you look at the execution plan?

Keith Knox Mr or Mrs. 500 Points: 588 More actions · Answer 2

I just verified that the indexes(clustered) exist. I don't know what an execution plan is? I'm new to being a dba.

Also, If I run a select query and instead of using *, I select all the columns except the DeleteOK and InactivateOK fields, it runs fast.

thanks

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

Well - it looks to me that you have 2 correlated sub-queries, and one is substantially more complicated than it needs to be (gut-feeling - not proof).

How about this as a rewrite:

SELECT DepartmentID, DepartmentName, AgencyName, ShortName, CreatedOn, Status, UpdatedOn, UpdatedBy,

CASE WHEN delOK.delcount > 1 THEN 'N' ELSE 'Y' END AS DeleteOK,

CASE WHEN inactOK.inact > 1 THEN 'N' ELSE 'Y' END AS InactivateOK

FROM dbo.tblDepartment AS a

left outer join

(

SELECT

z.DepartmentID,

COUNT(*) as delcount

FROM tblDepartment as z

LEFT JOIN tblFooter AS b ON z.departmentid = b.departmentid

LEFT JOIN tblHeader AS c ON z.departmentid = c.departmentid

LEFT JOIN tblLogo AS d ON z.departmentid = d .departmentid

LEFT JOIN tblPressRelease AS e ON z.departmentid = e.departmentid

LEFT JOIN tblUser AS f ON z.departmentID = f.departmentID

group by departmentid

) as delOk on a.DepartmentID = delOK.DepartmentID

left outer join

(

SELECT z.departmentid, COUNT(*) as inact

FROM tblDepartment AS z LEFT JOIN

tblFooter AS b ON z.departmentid = b.departmentid LEFT JOIN

tblHeader AS c ON z.departmentid = c.departmentid LEFT JOIN

tblLogo AS d ON z.departmentid = d .departmentid LEFT JOIN

tblPressRelease AS e ON z.departmentid = e.departmentid LEFT JOIN

tblUser AS f ON z.departmentID = f.departmentID

WHEREb.Status = 1 OR

c.Status = 1 OR

d .Status = 1 OR

e.Archived = 0 OR

f.Status = 1

group by z.departmentid

) as InactOk on a.DepartmentID = InactOk.DepartmentID

Voila - 2 derived tables instead. Should work somewhat better. You need some good indexes on all of this stuff.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

Rewrite # 2

SELECT DepartmentID, DepartmentName, AgencyName, ShortName, CreatedOn, Status, UpdatedOn, UpdatedBy,

CASE WHEN delOK.delcount > 1 THEN 'N' ELSE 'Y' END AS DeleteOK,

CASE WHEN delOK.inact > 1 THEN 'N' ELSE 'Y' END AS InactivateOK

FROM dbo.tblDepartment AS a

left outer join

(

SELECT

z.DepartmentID,

COUNT(*) as delcount,

sum(case when b.Status = 1

OR c.Status = 1

OR d .Status = 1

OR e.Archived = 0

OR f.Status = 1

then 1 else 0 end) as inact

FROM tblDepartment as z

LEFT JOIN tblFooter AS b ON z.departmentid = b.departmentid

LEFT JOIN tblHeader AS c ON z.departmentid = c.departmentid

LEFT JOIN tblLogo AS d ON z.departmentid = d .departmentid

LEFT JOIN tblPressRelease AS e ON z.departmentid = e.departmentid

LEFT JOIN tblUser AS f ON z.departmentID = f.departmentID

group by departmentid

) as delOk on a.DepartmentID = delOK.DepartmentID

And then there was just one (derived table)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

Heh... proof again... performance is in the code... water cooled CPU's wouldn't have made that query run any faster.

Nice job, Matt.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Keith Knox Mr or Mrs. 500 Points: 588 More actions · Answer 6

Keith Knox

Mr or Mrs. 500

Points: 588

February 13, 2008 at 9:25 am

#777914

Thanks y'all.

Awesome!

Frances L SSCarpal Tunnel Points: 4727 More actions · Answer 7

What is the purpose of

sum(case when b.Status = 1 OR c.Status = 1 OR d .Status = 1 OR e.Archived = 0 OR f.Status = 1 then 1 else 0 end) as inact

For here ?

Thanks.

Keith Knox Mr or Mrs. 500 Points: 588 More actions · Answer 8

Sorry, I don't know. I'm a system dba and don't know much about this view as it was created by on of our developers. We had noticed it was running slow so that's why I posted this thread.

thanks

magarity kerns SSCrazy Points: 2763 More actions · Answer 9

Learn to use the tuning advisor - BUT just don't blindly do whatever it suggests because it will not take into account all the many things going on outside the query it is checking. It usually will get you quickly pointed in the right direction to make some improvements with complicated queries. The tuning advisor saves time in basic analysis but you'll have to know when not to add all the indexes it suggests because that may cause other problems, especially when loading.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 10

Frances L (2/14/2008)
What is the purpose of
sum(case when b.Status = 1 OR c.Status = 1 OR d .Status = 1 OR e.Archived = 0 OR f.Status = 1 then 1 else 0 end) as inact
For here ?
Thanks.

It's doing a "conditional count". In other words - it's counting the number of times those criteria are met.

Interestingly enough, you could probably re-write it to be

count(case when b.Status = 1

OR c.Status = 1

OR d .Status = 1

OR e.Archived = 0

OR f.Status = 1 then 1 else NULL end) as inact

but this relies (I think) on the ANSI_NULLS setting, so I dont use this method.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Frances L SSCarpal Tunnel Points: 4727 More actions · Answer 11

that is my thought ,too. but I do not know I can't use sum this way. Thx. I looked at bol and google conditional count , not that much come out . It might be undocumented way.