Crystal 11 Command Object and Query Running Slow 2

[python138]

Hello,

I am running a report using SQL Server 2000 and and inquiry has been created which is running from Crystall Reports 11. To be honest I am not so good at this. Is there anyway to improve the query below? The purpose is to get the cumulative cash flows of a deal from the very beginning to the reporting end date. But the way I have it it takes a very long time to bring back the data in the report. It only ends up being 2 pages of data so my thinking is it is the query I have created....
==========================================
select d.deal_no, acr.acrl_en_dt, sum(cf.amount)as CumulativeCflows from

deals d, cflows cf, acrlrpt acr where

(d.deal_no=acr.deal_no and

cf.deal_no=acr.deal_no and

cf.comments in ('COUPON', 'INTEREST') and

cf.cflow_dt>=d.deal_dt and cf.cflow_dt<= acr.acrl_en_dt and

acr.acc_type in ('COUP', 'INTR'))

group by

d.deal_no, acr.acrl_en_dt
============================================

Any help would be much appreciated!
Thanks a lot!
Joe

 

[synapsevampire]

Well you're doing the grouping on the server, so that's a good thing.

Youi might define what running slow means, stating specifics is always better.

You may find that the query is doing full table scans, and that by rearranging the where clause you'll get better performance, and adding indexes may be in order.

I suggest using the SQL Server forums.

-k

 

[alexfromuno]

Ditto the remark about doing the Grouping on the server.

Have you tried NOT doing the (2) in clauses against alpha characters? An In clause sometimes can slow down the process, since the statement:

cf.comments in ('COUPON', 'INTEREST') is probably doing a full table space scan.

Is there a code number of any type that is associated with what you are looking for?

As an example, if the cflows table has a field called cf.comments, maybe it has a field called cf.comments_ID. If so, go into t the table and see what the cf.comments_ID is for 'COUPON' and 'INTEREST' and change he SQL to say be the following:

Old code

cf.comments in ('COUPON', 'INTEREST') and

New code (if cf.comments_ID for COUPON is 23 and INTEREST is 35

cf.comments_ID in (23,35) and

That will hopefully make the query run faster. A numeric IN clause look-up should run faster than an alphanumeric lookup.

Good luck!

-alex

 

[synapsevampire]

Alex: ThatIN clause shouldn't effect performance, an example of an IN that does would be a subquery, as in:

select id from table
where
id in (select id from table2)

in which case a derived table might prove faster, but if you think it impacts it, just use:

acr.acc_type = 'COUP'
or
acr.acc_type = 'INTR'

You shouldn't see a difference in SQL Server queries unless it's a subquery.

-k