Need to speed up report 1

[MisterrMac]

I have written a report that sorts records of two tables in order to get a trend report for clients that submit tickets that "need more info" and "need more time" to complete. Each of these are reflected by a Detail-Type in the Ticket-Detail Table.
The report drills down like this:
NEEDMOREINFO
NAME
TICKET1
TICKET2
NAME
TICKET1
NEEDMORETIME
NAME
TICKET1
TICKET2
Etc…

The only problem is that it takes about 10 minutes to run the report and generate the data!


The two data tables I have linked by the only common field: Ticket#.
The following are the tables and the main data that I am using:

Table 1:Ticket Table 2: Ticket-Detail
*Ticket# *Ticket#
*Client-Name *Detail-Type


I have two groups set up in this order:
Ticket-Detail.Detail-Type
Then Ticket.Client-Name
Finally, to get only those Detail types I want I am doing a record sort using:
{Ticket-Detail.Detail-Type} in [“NEEDMOREINFO”, “NEEDMORETIME”]

Is there some way that I can get this to run faster? There are about 2000 Ticket records with an average of 6 Ticket-Detail records that link to each.

Thanks in advance!

 

[Naith]

I'm going to assume that both Ticket# columns are indexed. (If they're part of composite indexes: i.e. TableA has an index comprised of Ticket#+Client-Name and TableB's is made up of Ticket#+Detail-Type, then that doesn't count.)

Can you confirm your full Record Selection criteria, and how much of it is getting passed to your SQL?

Thanks,

Naith

 

[MisterrMac]

If I am understanding you correctly, yes, both tables are indexed. Ticket.Ticket# is the index for that table. There is a different index for Ticket-Detail.

After posting the question I tried generating a new version of the report with no grouping and no sorting at all.

I put the Ticket-Detail.Detail-Type on the Page header, added a second details section and put the Client-Name in one and the Ticket-Number in the second. The only thing I did then was to use the Select Expert for Detail-Type to be 'is one of' and the two I wanted. This ran VERY quickly and more or less generated what I wanted only without a drilldown. Next I added a sort order on Client-Name. This sorted the already pulled records in next to no time at all.

Finally I tried refreshing the data: again I was sitting for an eternity waiting...

 

[Naith]

Check that Perform Grouping on Server is turned on, from the Database menu.

Paste your total selection criteria on the forum, and do the same for your SQL - which can be obtained from Database/Show SQL query.

Naith

 

[MisterrMac]

Ok... Above I changed some of the field and record names to make it easy. The following is directly from the report and should be self explanatory:

This is the version of the report with no grouping, only sorting on the Last Name. With grouping on the server the time is 7mins.


Selection formula:
{Help Desk Details.Action ID} in ["NEEDMOREINFO", "SENDLATENOTICE"]
****

SQL Query:
SELECT
"Help_Desk"."Problem #", "Help_Desk"."Open Date", "Help_Desk"."Last Name", "Help_Desk_Details"."Action ID"
FROM
"_SMDBA_"."Help Desk" "Help_Desk",
"_SMDBA_"."Help Desk Details" "Help_Desk_Details"
WHERE
"Help_Desk"."Problem #" = "Help_Desk_Details"."Prob #" AND
("Help_Desk_Details"."Action ID" = 'NEEDMOREINFO' OR
"Help_Desk_Details"."Action ID" = 'SENDLATENOTICE')
ORDER BY
"Help_Desk"."Last Name" ASC

 

[synapsevampire]

Are you certain that:

("Help_Desk_Details"."Action ID" = 'NEEDMOREINFO'

is correct?

Generally an ID field contains a link to a reference table used to obtain the description.

Anyway, the record selection IS passing the SQL, which is a good thing, the problem is either in what's being passed, or on the database. Perhaps you don't have an index on the last name? A good general rule of thumb for indexing is that if you don't have one for joins, where clauses, group bys or sorts, you're not optimized.

Try pasting the SQL into a querying tool and see what the performance is like.

Also do a:

select distinct Action ID from Help_Desk_Details

to make sure that you understand what's in there.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[MisterrMac]

Thanks for all the tips.
Our Help Desk software has quite a complicated database of tables. "Action ID" = "SENDLATENOTICE" is correct. I pulled it directly from the field just to avoid spelling errors. The naming scheme is not always understandable or what you think by initially reading it.

I'm just confused as to why, if I only select what I want then, when it has been pulled, sort it, it takes no more than a few seconds, but trying to do both at the same time throws it into a tizzy...
It is only 7 minutes and I can live with it if I'm not doing anything wrong. I just thought I might be missing something.

 

[Naith]

Out of curiosity, if you take out the sort, and instead group on LastName - suppressing the group header and footer - does that improve your performance at all?

 

[MisterrMac]

No change in the performance at all.

I guess that there are just too many records to sort or group records at the same time as selecting on sub records... about 2000 Ticket records and about 16000 Ticket Detail records. Maybe this is causing it to hit all 16000 records 2000 times as it sorts or groups?

 

[synapsevampire]

Make sure that all of the fields in:

"Help_Desk"."Problem #" = "Help_Desk_Details"."Prob #" AND
("Help_Desk_Details"."Action ID" = 'NEEDMOREINFO' OR
"Help_Desk_Details"."Action ID" = 'SENDLATENOTICE')
ORDER BY
"Help_Desk"."Last Name" ASC

have an index.

What database are they using?

Performance should be very fast for what you've described with any database that is properly indexed.

Are you certain that something else in the report isn't causing the problem?

And did you test the SQL directly against the database using a query tool?

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[MisterrMac]

In the order you Posted...

I guess I'm just too DB illiterate to fuly understand your indexing question. I know that each table has its own Index field. The Help_Desk index is the Problem# field which corrasponds to the Details Prob# field. The Details index field is called Index and there is no corrasponding field in the Help_Desk table. There is a table with client information and an index in the table that would point back to that but I do not have that table in the mix... (would that help?). The Details table has no fields for the name.

This is an Oracle DB

There is nothing else in the simple version of the report and it does the same thing.

I still need to get with our DB expert about having this checked out with a query tool.

 

[synapsevampire]

All of the fields used in the previous post should have an index, not just one per table.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[rosemaryl]

Dear MisterMac,

MagicTSD happens to be the software that I do the majority of my consulting work on - so I am sure that we can work this out.

The Help Desk Problem Number and the Help Desk Details Prob# field are both indexed fields.

This represents a one to many relationship.

The master is the Problem # and each detail (Action) is related by the Prob#.

If you email me your report with data saved (that is real important). I will test it for you and then post what needs to done.

But, just to get started I can tell you that one of the reasons it takes long is that there are alot of records to read.

Let's say you have 2000 calls. Well every call can have an unlimited number of details, but let's say that the average is 6.

As you can see, it must read through every record (12,000) just to find what you are looking for.

I will be happy to help you.

ro


Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[MisterrMac]

...it takes a while but the gods do answer!
This is, as you guessed a MagicTSD database and the way the multitude of tables are interconnectd makes it a pain to work with from the get go.

I'll get with you by email once I get a chance to.

Thanks Ro!

--
Loren McDonald

 

[rosemaryl]

Dear Loren,

I went ahead and worked this out:

Here you go.

Two tables: Help Desk and Help Desk Details

Link (= join) from Help Desk.Probelm # to Help Desk Details.Prob#

Select Statement:

{Help Desk Details.Action ID} in ["'NEEDMOREINFO' ", "SENDLATENOTICE"]

In the report, perform the following:

(Group 1) Group on Help Desk Details.Action ID
(Group 2) Group on Help Desk.Client ID (this is indexed!)

Create the formula:
//@Client Full Name
{Incident.Last Name} & ", " & {Incident.First Name}

Place the formula in the Group 2 Header

Now, place the {Help Desk.Problem #} field and any other call information in the details.

Report is fast.

Hope this helps,

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.