Re: Improve Query

I assume you have a good indexing strategy. Not knowing the quantity of data
involved, I would consider that for the first query, you 'should' have these
columns indexed:

type.application_request_status_type_id
req.application_request_status_type_id
req.application_id
req.application_request_id
app.application_id

And for the second, these additional columns indexed:

det.application_request_detail_status_type_id
det.status_desc
req.application_request_detail_status_type_id

And my name is 'Arnie' -NOT 'Arnold'. (How's Philly these days?)

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf


"No Donut For Me via SQLMonster.com" <u28653@uwe> wrote in message
news:68cd33828d233@xxxxxx
Arnold,

You are right, my apologies for not being clear. I wanted to know on how
to
improve a query to monitor automation jobs. Below are 2 queries being
used
at the moment:

/***this query shows last 10 runs of an application with the status***/
SELECT top 10 app.application_id, app.application_desc, req.
application_request_id, req.items_count, req.items_processed,
req.start_time,
req.end_time, type.application_request_status_type_desc as 'Last Status'
FROM automation..application_request req
join automation..application_request_status_type type
ON type.application_request_status_type_id = req.
application_request_status_type_id
join automation..application app ON app.application_id =
req.application_id
WHERE req.application_id = 2
ORDER BY req.application_request_id desc

-AND-

/***this query shows failed/successful transactions. It queries a count
of
each application_request_detail_status_type_id for an
application_request_id***/
SELECT req.application_request_id, req.
application_request_detail_status_type_id, det.status_desc, count (req.
application_request_detail_status_type_id) as 'count'
FROM automation..application_request_detail req
join automation..application_request_detail_status_type det
ON det.application_request_detail_status_type_id = req.
application_request_detail_status_type_id
WHERE req.application_request_id = 4745
GROUP BY req.application_request_id, req.
application_request_detail_status_type_id, det.status_desc





Arnie Rowland wrote:
SELECT count(application_request_detail_status_type_id)
FROM MyTable

...Somehow, I think that you have a different question in mind, but it is
difficult to guess what it may have been.

Is it possible to create a query that returns a count of each
application_request_detail_status_type_id as columns?

Any help on this would be greatly appreciated, thanks :)

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-mseq/200611/1



.