Better Query instead of loop

Question

Better Query instead of loop

Viewing 11 posts - 16 through 26 (of 26 total)

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

Jeff Moden SSC Guru Points: 1004639 More actions · Answer 1

No... you can't use it in a view as it currently is. A view must be a single query. You'll need to "fold" the dates table in as a CTE. However, be aware that such an action may actually cause a major performance problem in such cases.

My question would be, "Why does this need to be a view?"

--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)

uciltas-924976 SSC Veteran Points: 226 More actions · Answer 2

I will display the results in Crystal reports for management.

And they may use it pretty often during each month.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Put it into a stored procedure. Crystal works fine with them, the performance is likely to be better, and there will be no temptation for folks to attempt to do something silly like JOINing it into another query.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

uciltas-924976 SSC Veteran Points: 226 More actions · Answer 4

Excellent. I will do that.

Never thought of that since I always used views for Crystal reports.

I learned new tricks today, I appreciate that .

tfifield SSCrazy Eights Points: 9655 More actions · Answer 5

ChrisM@Work (8/23/2011)
Put it into a stored procedure. Crystal works fine with them, the performance is likely to be better, and there will be no temptation for folks to attempt to do something silly like JOINing it into another query.

Chris,

I hate to tell you this, but I've seen people join stored procedures (actually the output of stored procedures) in both Crystal and MS Access pass-through queries. They wondered why the reports took hours! (I gather the Jet engine doesn't really care how a record set is created.)

They were also amazed when I fixed them to run in a minute or less by just re-writing one of the stored procedures to produce the full output desired and getting rid of the joins.

Todd Fifield

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

tfifield (8/24/2011)
ChrisM@Work (8/23/2011)
Put it into a stored procedure. Crystal works fine with them, the performance is likely to be better, and there will be no temptation for folks to attempt to do something silly like JOINing it into another query.
Chris,
I hate to tell you this, but I've seen people join stored procedures (actually the output of stored procedures) in both Crystal and MS Access pass-through queries. They wondered why the reports took hours! (I gather the Jet engine doesn't really care how a record set is created.)
They were also amazed when I fixed them to run in a minute or less by just re-writing one of the stored procedures to produce the full output desired and getting rid of the joins.
Todd Fifield

There's one born every day Todd. I should have said less temptation 😉

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

uciltas-924976 SSC Veteran Points: 226 More actions · Answer 7

Used stored procesure only and worked beutifully.

Performance is excellent.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

Job's a good 'un. Thanks for the feedback.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

uciltas-924976 SSC Veteran Points: 226 More actions · Answer 9

Hi Chris,

I just realized a minor problem on the code you sent.

To handle [AVG-DAYS] we said to use when it is current month data CTR_UPDATED will be getdate()

So in the case statement we used d.dtDate = 0 which can never be 0 since it is a date field.

I think you meant to use the n.n from the tally table. Is that correct?

SELECT

d.[MMMYY],

d.[YEAR],

d.[MONTHNUMBER],

ZONE,

[AVG-DAYS] = AVG(DATEDIFF(DAY, CTR_CREATED, ISNULL(CTR_UPDATED, CASE WHEN d.dtDate = 0 THEN GETDATE() ELSE d.upperbound END))),

[Count] = COUNT(*),

[DESC] = 'KPI-XYZ'

FROM INCIDENTS

CROSS JOIN #Dates d

LEFT JOIN [EVENTS]

ON CTR_EVENT = EVT_CODE

WHERE CTR_SERVICECATEGORY = 'XYZ' AND (CTR_STATUS IN ('CL', 'F', 'O') OR EVT_STATUS IN ('C','R', 'Z'))

AND ( ----Last Day of Previous Month ----Last Day of Current Month

(EVT_STATUS='C' AND CTR_UPDATED >d.lowerbound AND CTR_UPDATED <= d.upperbound)

OR

(CTR_STATUS= 'CL' AND CTR_UPDATED >d.lowerbound AND CTR_UPDATED <= d.upperbound)

OR

(EVT_STATUS in ('R','Z' ) AND CTR_CREATED <= d.upperbound)

OR

(CTR_STATUS in ( 'F', 'O') AND CTR_CREATED <= d.upperbound)

)

GROUP BY ZONE

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

uciltas-924976 (9/9/2011)
Hi Chris,
I just realized a minor problem on the code you sent.
To handle [AVG-DAYS] we said to use when it is current month data CTR_UPDATED will be getdate()
So in the case statement we used d.dtDate = 0 which can never be 0 since it is a date field....

What is the full logic for [AVG-DAYS]? I can't make sense of the above statement.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

uciltas-924976 SSC Veteran Points: 226 More actions · Answer 11

[AVG_DAYS] = AVG (dateDIFF(DAY,CTR_CREATED,CTR_UPDATED)),

CTR_UPDATED conditions are:

When CTR_UPDATED is not null ; if CTR_UPDATED> then month's upperbound then upperbound

When CTR_UPDATED is not null ; if CTR_UPDATED< then month's upperbound then CTR_UPDATED

when current month data and CTR_UPDATED is null and CTR_STATUS='O' and , it will be getdate()

when not current month and CTR_UPDATED is null and CTR_STATUS='O' and previous month data it has to be previous months upper bound

'

when May 2011 CTR_UPDATED is null and CTR_STATUS='O' andit has to be upper bound of May 2011.