Archiving Database?

[Monkey820]

Hi There!
I have a report that takes 7-8 minutes to load bcz it is sorting through years and years of data.
How can I make this report faster?
I thought if I could get it to record select on the server for dates > 1/1/04, that might help, but I really don't know how to do that.
Can portions of the database be archived? If so, can the records that were archived still be accessed? And if this is a possibility, where do I go to learn how to do it?

TIA,
Gertie

 

[vidru]

You likely need to tune up your record selection formula, or add one if there isn't one. Go to Report > Edit Selection Formula > Record, and paste what's there back here, or indicate that it's blank.

Also, it's a good idea to include the following information when posting:
Crystal version
Database Type/Connectivity

... and optionally:
Sample Data
Expected output

-dave

 

[Monkey820]

Thank you for your response!

In record selection I have:

{@ShipDate} = {?Date}

Crystal Reports 8.5
Oracle Server

@shipdate: DTSToDate ({SHIPMENTS.SHIPDATE})
?Date: Date Range Value

Thank you for any help you can give me!

Gertie

 

[IdoMillet]

Change @shipdate from a formula to a SQL Expression.

That would allow Crystal to pass the WHERE clause to Oracle and speed up processing dramatically.

You can always check whether the WHERE clause is passed to the DBMS by selecting Database, Show SQL Query...

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[IdoMillet]

Also, see faq766-3826 for more detail.

hth,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[vidru]

I think a SQL Expression field to convert the SHIPMENTS.SHIPDATE field would help. I'm a little rusty on my Oracle syntax, so this might need to be tweaked...

In the Field Explorer, click on SQL Expression Fields, and click the New button. Name it "ShipDate", and enter the following as the text:

TO_DATE(SHIPMENTS.SHIPDATE,'MM/DD/YYYY')

Go to your record selection formula, and replace what's there with this:

{%ShipDate} in {?Date}

The point here is to keep Crystal from having to convert the string to a date before applying the parameter criteria. It's taking so long to run your report right now because it's having to read every record in your table, convert the date string to a date, then apply the filtering.

-dave

 

[Monkey820]

vidru -
I tried your suggestion and I keep getting the errors:

ORA-01821: date format not recognized

Error detected by database DLL.


Any ideas on what I am doing wrong?

Gertie

 

[Monkey820]

sorry,

The first error is:

ORA-01843: not a valid month


Gertie

 

[vidru]

What's the format of your DateTime string?

-dave (hoping that someone with more Oracle experience will jump in here)

 

[synapsevampire]

Which Oracle driver are you using to access the data?

Note that Dave had asked for this information because it's important.

The key to optimizing a report is to make sure that the record selection formula is reflected within the Database->Show SQL Query. If it isn't, then Crystal is doing the work.

Try:

TO_DATE(table.datetime, 'yyyy/mm/dd')

-k

 

[synapsevampire]

Sorry, posted too fast, should be:

TO_DATE(table.datetime, 'dd/mm/yyyy')

-k

 

[Monkey820]

When I use TO_DATE(table.datetime, 'dd/mm/yyyy') I recieve the error:
ORA-00936: missing expression
Error in compiling SQL Expression: Invalid field found here.

and the cursor is placed before "table.datetime"

How do I check which Oracle driver I am using?

The SQL Query shows this:

SELECT
"SHIPMENTS"."PACKSLIPNO", "SHIPMENTS"."SHIPDATE", "SHIPMENTS"."SHIP_TO_ATTN", "SHIPMENTS"."SHIP_TO_ADDR1", "SHIPMENTS"."SHIP_TO_ADDR2", "SHIPMENTS"."SHIP_TO_CITY", "SHIPMENTS"."SHIP_TO_STATE", "SHIPMENTS"."SHIP_TO_ZIP", "SHIPMENTS"."FREIGHT_DESCRIP", "SHIPMENT_DTL"."RELEASES_ID", "V_SHIP_HIST"."MISC_ITEM", "V_SHIP_HIST"."ITEMNO", "V_SHIP_HIST"."DESCRIP", "V_SHIP_HIST"."REV", "SHIPMENT_BOL"."TOTAL_BOXES", "SHIPMENT_BOL"."WEIGHT", "SHIPMENT_RELEASES"."LOTNO", "SHIPMENT_RELEASES"."QTYSHIPPED", "AKA"."CUST_ITEMNO", "AKA"."CUST_DESCRIP",
TO_DATE("SHIPMENTS"."SHIPDATE", 'DD/MM/YYYY HH24:MI:SS')
FROM
"IQMS"."SHIPMENTS" "SHIPMENTS",
"IQMS"."SHIPMENT_DTL" "SHIPMENT_DTL",
"IQMS"."V_SHIP_HIST" "V_SHIP_HIST",
"IQMS"."SHIPMENT_BOL" "SHIPMENT_BOL",
"IQMS"."SHIPMENT_RELEASES" "SHIPMENT_RELEASES",
"IQMS"."AKA" "AKA"
WHERE
"SHIPMENTS"."ID" = "SHIPMENT_DTL"."SHIPMENTS_ID"(+) AND
"SHIPMENT_DTL"."ID" = "V_SHIP_HIST"."SHIPMENT_DTL_ID"(+) AND
"SHIPMENT_DTL"."ID" = "SHIPMENT_BOL"."SHIPMENT_DTL_ID"(+) AND
"SHIPMENT_DTL"."ID" = "SHIPMENT_RELEASES"."SHIPMENT_DTL_ID"(+) AND
"V_SHIP_HIST"."ARINVT_ID" = "AKA"."ARINVT_ID"(+) AND
"V_SHIP_HIST"."ARCUSTO_ID" = "AKA"."ARCUSTO_ID"(+) AND
TO_DATE("SHIPMENTS"."SHIPDATE", 'DD/MM/YYYY HH24:MI:SS') >= TO_DATE ('14-03-2004 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND
TO_DATE("SHIPMENTS"."SHIPDATE", 'DD/MM/YYYY HH24:MI:SS') <= TO_DATE ('14-04-2004 23:59:59', 'DD-MM-YYYY HH24:MI:SS')
ORDER BY
"SHIPMENTS"."SHIP_TO_ATTN" ASC,
"SHIPMENTS"."SHIPDATE" ASC

 

[IdoMillet]

You need to replace table.datetime with your own datetime column...

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[Monkey820]

I replaced it as such:

TO_DATE("SHIPMENTS"."SHIPDATE", 'dd/mm/yyyy')

Is this incorrect?