Slow refresh against oracle tables

[rksandman]

Hi Please Help, I am using CR ver 9 on a win2k platform oracle client 8i, the data source is oracle 8i, the ODBC Drivers tried are Cr Oracle, MS Oracle, Oracle and Oracle Wire..

I run the following qry and it takes at least 10 min to return 106 records.
If I remove the date portion of the qry it returns all 500 records in under a min. so this means I am having a problem with odbc and oracle dates?

If a open Access and import from oracle these tables, then set location on the report and establish an access odbc it runs in less than 5 seconds. This tells me this is related to Oracle....

I have been in contact with Crystaldecisions Support but they have no idea.

Any Ideas what may be causing this turtle like speed?

Thanks.

-----------------------SQL Query----------------------------
SELECT "COMMUNICATION"."CALL_TYPE", "COMMUNICATION"."CREATED", "COMMUNICATION"."DOCUMENTATION_STATUS", "MEMBER"."CLIENT_NAME"
FROM "DTMTTBO"."COMMUNICATION" "COMMUNICATION", "DTMTTBO"."MEMBER" "MEMBER"
WHERE (&quot;COMMUNICATION&quot;.&quot;CONTACT_ID&quot;=&quot;MEMBER&quot;.&quot;ROW_ID&quot AND (&quot;COMMUNICATION&quot;.&quot;CALL_TYPE&quot; LIKE 'Call-Inbound' OR &quot;COMMUNICATION&quot;.&quot;CALL_TYPE&quot; LIKE 'Call-Outbound') AND &quot;MEMBER&quot;.&quot;CLIENT_NAME&quot;='OREGON' AND (&quot;COMMUNICATION&quot;.&quot;CREATED&quot;>=TO_DATE ('01-11-2002 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND &quot;COMMUNICATION&quot;.&quot;CREATED&quot;<TO_DATE ('01-04-2003 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) AND &quot;COMMUNICATION&quot;.&quot;DOCUMENTATION_STATUS&quot;<>'Invalid'

-----------------Record Selection---------------------------
{COMMUNICATION.CALL_TYPE} like [&quot;Call-Inbound&quot;, &quot;Call-Outbound&quot;] and
{COMMUNICATION.CREATED}in Date(2002,11,01) to Date(2003,03,31) and
{COMMUNICATION.DOCUMENTATION_STATUS} <> &quot;Invalid&quot; and
{MEMBER.CLIENT_NAME} = &quot;OREGON&quot;

 

[synapsevampire]

Try:

({COMMUNICATION.CALL_TYPE} = &quot;Call-Inbound&quot; or
{COMMUNICATION.CALL_TYPE} = &quot;Call-Outbound&quot
and
{COMMUNICATION.CREATED}in Date(2002,11,01) to Date(2003,03,31) and
{COMMUNICATION.DOCUMENTATION_STATUS} <> &quot;Invalid&quot; and
{MEMBER.CLIENT_NAME} = &quot;OREGON&quot;

A Like predicate is punishing.

Also try copying the SQL generated and running it in SQL Spreadsheet, or whatever your preferred SQL execution means is.

-k

 

[rksandman]

Hello Synaps, thanks for the quick reply, I changed the like statement, as you suggested but the problem still exist. I have already taken the sql and run it through SQL Navigator and the process time is just over 5 seconds. Any other ideas??

if I run the following it return all 500 records and it runs in less than 60 seconds,Unfortunately this is our smallest client the others are over 100,000 records.

{COMMUNICATION.CALL_TYPE} like [&quot;Call-Inbound&quot;, &quot;Call-Outbound&quot;] and
{COMMUNICATION.DOCUMENTATION_STATUS} <> &quot;Invalid&quot; and
{MEMBER.CLIENT_NAME} = &quot;OREGON&quot;

 

[jimmarkham]

You could try this (in sql*plus or somewhere) and if it works then perhaps it could be incorporated into your CR.

Change this part of the sql

(&quot;COMMUNICATION&quot;.&quot;CREATED&quot;>=TO_DATE ('01-11-2002 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND &quot;COMMUNICATION&quot;.&quot;CREATED&quot;<TO_DATE ('01-04-2003 00:00:00', 'DD-MM-YYYY HH24:MI:SS'))

to

(&quot;COMMUNICATION&quot;.&quot;CREATED&quot;+0 >= TO_DATE ('01-11-2002 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND &quot;COMMUNICATION&quot;.&quot;CREATED&quot;+0 < TO_DATE ('01-04-2003 00:00:00', 'DD-MM-YYYY HH24:MI:SS'))

What I'm thinking is that there may be a problem with indexes on the 'communication.created' column. Perhaps the optimizer is choosing an index when it is better not to. Putting in the +0 might stop that happening.

Jim

 

[synapsevampire]

Jim's suggestion is interesting as it does seem to be going through some ugly table scans...

Or:

You can use SQL_TRACE to capture the actual SQL being passed by the ODBC driver.

Some concerns with this are that the trace files will contain a lot of other data to sift through, and it will be punishingly slow.

Anotehr tool at your disposal is the ODBC tracing built into Windows.

I'd also try changing to the Crystal Oracle direct connection, and/or OLE DB.

-k

 

[rksandman]

I looked at Jims idea, when adding the +0 to the query it then removes it from the SQL being pushed to the server, Ill have to take a look at the ODBC tracing tool that you mention. I have run this same query using ole db and tns native connection and Im seeing the same problem which leads me to believe that its in the table structure, Ill keep digging.

Thanks to Jim and Synaps for giving this a shot.

RKS

 

[AnnetteH]

If you would like to send me your e-mail address, I can send you a subreport that you can link into your report, which will show you the actual execution plan that Oracle is using. That will tell you how indexes are being used, etc. which should help diagnose this problem.

I would say that it is not a 'table structure' problem, or even an indexing problem, if the identical query runs quickly from SQL Navigator. I'm surprised that you're getting significantly different execution times running from Crystal than from SQL Navigator. When you run from SQL Navigator, are you displaying the entire result set? You would need to do that to really compare to the Crystal time, especially if your report does any second pass type of computations.

You didn't really mention what else your report is doing. Is it just displaying the records?

 

[synapsevampire]

Annette: Since RKS is using ODBC, something may get interpreted there, hence the possibility of the date being at fault. If a report is doing a fair amount of business, such as Crosstabs, the times could be vastly different.

RKS: I'd just write a Stored Procedure and be done with it...

-k

 

[rksandman]

To respond to Annette there is nothing else going on in the report. I set it up to test the speed bucause I am having this problem with most of my reports that run off this Oracle db. here is my email address rksanders@attbi.com

I will be looking into doing what Synaps has suggested using stored procedures but that doesn’t really solve the problem however it seems to be the best workaround. I have built views in oracle using SQL+, are Stored Procedures just as easy?

Thanks for the help!!

RKS

 

[synapsevampire]

SP's on Oracle are a tad more complicated, especially since Crystal has special considerations, but it's not too bad.

If you've built Views, a View might be an interesting thing to test too, however an SP will outperform a View.

Here's the CD whitepaper on Oracle SP's:

http://support.crystaldecisions.com/library/kbase/articles/c2001157.asp

Good luck.

-k

 

[AnnetteH]

synapsevampire: If the ODBC driver is somehow misinterpreting the date fields, then I would say that the driver is at fault, not the database structure. However, rksandman also stated that he got the same slow timings using the native driver and OLE DB. That seems a real mystery to me, and means it is not driver specific. If he was having the same problem running the query from SQL*Plus, I would think it was a structure problem.

But in any case, looking at the execution plan will show EXACTLY what statement Oracle is executing and how indexes are being used, etc. That will show whether the statement is getting rewritten in some way before it is passed to Oracle. It may not provide a solution, but it should help determine where the problem is coming from.

I'm a strong advocate for the use of stored procedures, but this seems like a problem that needs to be explained, before making any decisions. At least if it was happening in my environment, I would want to know why.

 

[pelajhia]

I am also hitting oracle 8i, and it is really sensitive about dates AND parentheses, although I haven't seen this exact problem.
My suggestions would be to separate all your ands with parentheses:
({COMMUNICATION.CALL_TYPE} like [&quot;Call-Inbound&quot;, &quot;Call-Outbound&quot;])
and
({COMMUNICATION.CREATED}in Date(2002,11,01) to Date(2003,03,31))
and
({COMMUNICATION.DOCUMENTATION_STATUS} <> &quot;Invalid&quot
and
({MEMBER.CLIENT_NAME} = &quot;OREGON&quot

Further, I have found (for no reason I can discern) that simply moving around the more 'complicated' part of your sql to the end of the statement sometimes resolves 'translation' confusion.
eg:
({COMMUNICATION.CALL_TYPE} like [&quot;Call-Inbound&quot;, &quot;Call-Outbound&quot;])
and
({COMMUNICATION.DOCUMENTATION_STATUS} <> &quot;Invalid&quot
and
({MEMBER.CLIENT_NAME} = &quot;OREGON&quot
and
({COMMUNICATION.CREATED}in Date(2002,11,01) to Date(2003,03,31))
If this sort of tinkering doesn't work, please repost your current sql.
Good luck.