| Author |
Message |
blandow
Newbie
Joined: 22 Apr 2011
Online Status: Offline
Posts: 8
|
  
Topic: Optimizing Speed
Posted: 28 Apr 2011 at 9:02am |
|
I have a report that is taking 5min to execute right now, and I'd like to see if I can improve on that. Below is the SQL that Crystal is generating. If anyone sees anything obvious, please let me know. I have heard using SQL expressions is a good way to go, but I am just learning how to use them.
SELECT "distribution_stop_information"."route_date", "distribution_stop_information"."stop_exception_code", "distribution_stop_information"."unique_id_no", "distribution_stop_information"."customer_reference", "distribution_stop_information"."stop_name", "distribution_stop_information"."stop_comment", "distribution_stop_information"."branch_id", "distribution_stop_information"."stop_expected_pieces", "distribution_stop_information"."datetime_updated", "distribution_stop_information"."updated_by", "distribution_stop_information"."route_code", "distribution_stop_information"."customer_no", "distribution_stop_information"."item_is_to_be_redelivered", "distribution_stop_information"."bol_number"
FROM "cops_reporting"."cops_reporting"."distribution_stop_information" "distribution_stop_information"
WHERE "distribution_stop_information"."stop_exception_code"<>'null' AND ("distribution_stop_information"."datetime_updated">={ts '2011-04-28 08:59:43'} AND "distribution_stop_information"."datetime_updated"<{ts '2011-04-28 13:59:42'})
ORDER BY "distribution_stop_information"."datetime_updated" DESC
(Below is the forumla I have entered with the Select Expert)
{distribution_stop_information.datetime_updated} < CurrentDateTime and
{distribution_stop_information.datetime_updated} > dateadd("h",-5,CurrentDateTime)
|
 IP Logged |
|
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 28 Apr 2011 at 9:12am |
|
does not appear that you joined your tables
Edited by DBlank - 28 Apr 2011 at 9:13am
|
| IP Logged |
|
blandow
Newbie
Joined: 22 Apr 2011
Online Status: Offline
Posts: 8
|
Posted: 28 Apr 2011 at 10:12am |
|
Thanks for the response.
There is only one table being selected in this query..
distribution_stop_information
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 28 Apr 2011 at 10:32am |
sorry, was interpretting the "cops_reporting"."cops_reporting" as another table.
What is the DB type and how many rows are in your table?
|
| IP Logged |
|
blandow
Newbie
Joined: 22 Apr 2011
Online Status: Offline
Posts: 8
|
Posted: 28 Apr 2011 at 10:34am |
|
PostGreSql Unicode
There are several million records
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 28 Apr 2011 at 10:38am |
|
how long does the same query take directly in the db?
|
| IP Logged |
|
blandow
Newbie
Joined: 22 Apr 2011
Online Status: Offline
Posts: 8
|
Posted: 28 Apr 2011 at 10:46am |
|
Honestly I haven't tried-- and this database cannot be redesigned. It's controlled by a 3rd party. Right now it's taking Crystal 7 minutes to process. By the way, the average return of results is about 100-500 records.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 28 Apr 2011 at 10:59am |
seems like a LONG time but there are so many factors involved.
I would try and query the db directly to see if you are looking at crystal issues or not. If your db query takes forever then the issue is at your source.
You do not have any run time parameters correct?
|
| IP Logged |
|
blandow
Newbie
Joined: 22 Apr 2011
Online Status: Offline
Posts: 8
|
Posted: 28 Apr 2011 at 11:06am |
|
No. I've never messed with run time parameters..Still a newbie.
As far as querying the db directly, I assume you mean using a program like cute-sql and running my select statement?
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 28 Apr 2011 at 11:57am |
I think that would do fine. I have not used that data source source type so not sure the best test but the idea is to remove crystal from the to test the speed you get. If it runs similarly you know it is more the db than the report itself.
|
| IP Logged |
|
|
|
