linking tables and performance

azwaan · Mar 2, 2003

i have 2 tables to extract information from.
one is really huge..around 200000 records and one has around 100. i have restricted the displayed reccords by using a selection forumula..which looks like this

{dps.calldate}>=cdate({@DateFrom}) and {dps.calldate}<=cdate({@DateTo})

{@DateTo}, {@DateFrom} is passed from a vb appllication

the problem is the report seems to read all the 200000 records before displaying the one's within range. this takes an awfully long time. how can i improve performance
on this.

chelseatech · Mar 2, 2003

What sort of database?

The cDate in your selection formula is making it all evaluate in CR. Can you convert to a SQL expression? Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

synapsevampire · Mar 3, 2003

I'd guess that you're comparing dates to datetimes, and Crystal is not passing the SQL to the database, which is annoying, but generally easy enough to remedy.

Use similar data types, that should resolve it, as in:

(
{dps.calldate} >= cdatetime(year({@DateFrom}), month({@DateFrom}), day({@DateFrom}),0,0,0)
and
{dps.calldate} >= cdatetime(year({@DateFrom}), month({@DateFrom}), day({@DateTo}),11,59,59)
)

-k

azwaan · Mar 3, 2003

ok..guys..it was the cdate thing..alright..now i am using a 2 date parameters instead.it all seems to work fine..until u have multiple conditions in my record selection formula.

here..what's interesting..now this formula..

if {?Ext}="ALL" then
{dps.calldate}>={?datefrom} and {dps.calldate}<={?dateto}

only reads the 600 records actually in range.and takes only 2 secs

this one...

if {?Ext}="ALL" then
{dps.calldate}>={?datefrom} and {dps.calldate}<={?dateto}
else
{dps.calldate}>={?datefrom} and {dps.calldate}<={?dateto}

reads all 200000 records and returns the needed 600 and takes more than 2 mins..

now notice that evaluation of both has the same statement . i used this to make sure there wasn't something in the actual selection formula that's causing this.

so how can it read 200000 records when there's only an additional "ELSE" clause. this is very confusing any ideas guys...!

GJParker · Mar 4, 2003

Whats the differenec between the 2 different sets of SQL passed to the server ? Gary Parker
Systems Support Analyst

lyanch · Mar 4, 2003

If you are going to use an if..else statement in your selection formula you need to you else if not (first statent). For some reason crystal will send that to the server and not the other. Your formula should be:

if {?Ext}="ALL" then
{dps.calldate}>={?datefrom} and {dps.calldate}<={?dateto}
else if {?Ext} <> "ALL" then
{dps.calldate}>={?datefrom} and {dps.calldate}<={?dateto}

That should send the selection to the SQL correctly.

Lisa

azwaan · Mar 4, 2003

well..lisa..the formula u suggested is the first thing i tried before ..and i tried it again..and still doesn't work..the results are ok..but it ends up reading all the records..

i need to have multiple selection formulas based on the parameters passed..i tried a select..case too..but still no use.

in case i forgot to mention it's a sql server database.

lyanch · Mar 5, 2003

Hmm... do you know if the parameters are all being sent for sure? Sometimes you just have to play aroung with it until it actually sends the parameters. The basic style I usually start with is:

(if condition then xxxxx else if not condition then xxxx)
AND
(if condition2 then xxxxx else if not condition2 then xxxx)
etc.

Things I know that don't work:

sometimes...multiple xxxxxx's per condition (use the confining if twice)

never multiple else if's ie you cant use
if x=y then
else if x=z then
else if x= a thne

You need to break each down into a separate if else if.

Can you post your complete selection statement as is? I am sure that there are people here who can help you get crystal to send it to the server (this is a crystal problem.. not your server)

Lisa

synapsevampire · Mar 5, 2003

The record selection is identical in either case, the IF ELSE seems pointless...

Try:

if {?Ext} <> "ALL" then
(
{dps.calldate}>={?datefrom}
)
and
(
{dps.calldate}<={?dateto}
)

else
true

-k

lyanch · Mar 5, 2003

Is that you in there -k? Your the one who (rightly) pointed it out to me ages ago that the else if was needed to get crystal to send it to the DB?

Lisa

azwaan · Mar 5, 2003

ok..here's what i need to be doing...

if {@Ext}="" then
if {@CallType}="ALL" then
{dps.calldate}>=cdate({@DateFrom}) and {dps.calldate}<=cdate({@DateTo})
else if {@CallType}="IDD" then
{dps.calldate}>=cdate({@DateFrom}) and {dps.calldate}<=cdate({@DateTo}) and {dps.desttype}="IDD"
else if {@CallType}="STD" then
{dps.calldate}>=cdate({@DateFrom}) and {dps.calldate}<=cdate({@DateTo})and {dps.desttype}="STD"
else if {@CallType}="LOC" then
{dps.calldate}>=cdate({@DateFrom}) and {dps.calldate}<=cdate({@DateTo}) and {dps.desttype}="LOC"
else if {@CallType}="IDDLOC" then
{dps.calldate}>=cdate({@DateFrom}) and {dps.calldate}<=cdate({@DateTo}) and ({dps.desttype}="IDD" or {dps.desttype}="LOC&quot

else if {@CallType}="IDDSTD" then
{dps.calldate}>=cdate({@DateFrom}) and {dps.calldate}<=cdate({@DateTo}) and ({dps.desttype}="IDD" or {dps.desttype}="STD&quot

else
{dps.calldate}>=cdate({@DateFrom}) and {dps.calldate}<=cdate({@DateTo}) and ({dps.desttype}="LOC" or {dps.desttype}="STD&quot

else

if {@CallType}="ALL" then
{dps.calldate}>=cdate({@DateFrom}) and {dps.calldate}<=cdate({@DateTo}) and {dps.Extension}={@Div}
else if {@CallType}="IDD" then
{dps.calldate}>=cdate({@DateFrom}) and {dps.calldate}<=cdate({@DateTo}) and {dps.desttype}="IDD" and {dps.Extension}={@Div}
else if {@CallType}="STD" then
{dps.calldate}>=cdate({@DateFrom}) and {dps.calldate}<=cdate({@DateTo})and {dps.desttype}="STD" and {dps.Extension}={@ext}
else if {@CallType}="LOC" then
{dps.calldate}>=cdate({@DateFrom}) and {dps.calldate}<=cdate({@DateTo}) and {dps.desttype}="LOC" and {dps.divid}={@ext}
else if {@CallType}="IDDLOC" then
{dps.calldate}>=cdate({@DateFrom}) and {dps.calldate}<=cdate({@DateTo}) and ({dps.desttype}="IDD" or {dps.desttype}="LOC&quot

and {dps.Extension}={@ext}
else if {@CallType}="IDDSTD" then
{dps.calldate}>=cdate({@DateFrom}) and {dps.calldate}<=cdate({@DateTo}) and ({dps.desttype}="IDD" or {dps.desttype}="STD&quot

and {dps.Extension}={@xt}
else if {@CallType}="STDLOC" then
{dps.calldate}>=cdate({@DateFrom}) and {dps.calldate}<=cdate({@DateTo}) and ({dps.desttype}="LOC" or {dps.desttype}="STD&quot

and {dps.Extension}={@ext}

this code works ok..but only thing is reads all the records as i said earlier.

in response to -k , i am only using this piece of code

if {?Ext}="ALL" then
{dps.calldate}>={?datefrom} and {dps.calldate}<={? dateto}
else if {?Ext}<>"ALL" then
{dps.calldate}>={?datefrom} and {dps.calldate}<={?dateto}

so that to make sure that it's not my actual selection formula that's causing it to read all the records.so if both formulas are the same it should return and read the same set of records right..! besides i am having a problem of performance here..not the actual results.

ok..now here's the weird part...without using the VB App ..i previewed the report in design time..

i entered ALL for the {?Ext} parameter and a suitable date range , it ended up reading all the records.

then what i did was i deleted the else if part ...then it only read the records it had to. now ...i think u can say that it's not something to do with incorrect paramater values or passing it from an APP.

well is there any other method i can use.

lyanch · Mar 6, 2003

Try this for starters... at least the dates should always be sent to the DB.

{dps.calldate}>=cdate({@DateFrom}) and {dps.calldate}<=cdate({@DateTo} AND

(if {@Ext}="" then
if {@CallType}="IDD" then
{dps.desttype}="IDD"
else if {@CallType}="STD" then
{dps.desttype}="STD"
else if {@CallType}="LOC" then
{dps.desttype}="LOC"
else if {@CallType}="IDDLOC" then
({dps.desttype}="IDD" or {dps.desttype}="LOC&quot

else if {@CallType}="IDDSTD" then
({dps.desttype}="IDD" or {dps.desttype}="STD&quot

else
({dps.desttype}="LOC" or {dps.desttype}="STD&quot

else if {@Ext} <> ""

if {@CallType}="IDD" then
{dps.desttype}="IDD" and {dps.Extension}={@Div}
else if {@CallType}="STD" then
{dps.desttype}="STD" and {dps.Extension}={@ext}
else if {@CallType}="LOC" then
{dps.desttype}="LOC" and {dps.divid}={@ext}
else if {@CallType}="IDDLOC" then
({dps.desttype}="IDD" or {dps.desttype}="LOC&quot

and {dps.Extension}={@ext}
else if {@CallType}="IDDSTD" then
({dps.desttype}="IDD" or {dps.desttype}="STD&quot

and {dps.Extension}={@xt}
else if {@CallType}="STDLOC" then
({dps.desttype}="LOC" or {dps.desttype}="STD&quot

and {dps.Extension}={@ext})

Lisa

synapsevampire · Mar 6, 2003

Right you are, Lisa, I guess I was kinda tired last night...

-k

synapsevampire · Mar 6, 2003

This IF seems to do nothing different between the IF and ELSE, so why not just avoid the IF entirely and just use:

Your formula:
if {?Ext}="ALL" then
{dps.calldate}>={?datefrom} and {dps.calldate}<={? dateto}
else if {?Ext}<>"ALL" then
{dps.calldate}>={?datefrom} and {dps.calldate}<={?dateto}

Suggested:
{dps.calldate}>={?datefrom} and {dps.calldate}<={? dateto}

I didn't review how Lisa came to her conclusions, but her IFs seem logical.

Your original post is nothing like what you're showing now...

*If you're comparing datetimes to dates, sometimes this will cause the problem of not passing SQL, so convert the parm collected date to a datetime and pass that, and there are lots of other potential factors.

I think that your approach of using CR to develop the proper record selection formula without VB is smart, explore that fully.

To correct my original formula (thanks Lisa):

if {?Ext} <> "ALL" then
(
{dps.calldate}>={?datefrom}
)
and
(
{dps.calldate}<={?dateto}
)

else if
{?Ext} = "ALL"
true

-k

azwaan · Mar 6, 2003

ok.. -k was right..the cdate thing will not pass the sql to the database ..so i'm using date parameters now..

i changed my formula to what lisa has suggested..it looks like this now

{dps.calldate}>={?datefrom} and {dps.calldate}<={?dateto}
and

if {?Ext}="ALL" then
if {?CallType}="IDD" then
{dps.desttype}="IDD"
else if {?CallType}="STD" then
{dps.desttype}="STD"
else if {?CallType}="LOC" then
{dps.desttype}="LOC"
else if {?CallType}="IDDLOC" then
({dps.desttype}="IDD" or {dps.desttype}="LOC&quot

else if {?CallType}="IDDSTD" then
({dps.desttype}="IDD" or {dps.desttype}="STD&quot

else if {?CallType}="STDLOC" then
({dps.desttype}="LOC" or {dps.desttype}="STD&quot

else if {?Ext}<>"ALL" then

if {?CallType}="ALL" then
{dps.extension}={?Ext}
else if {?CallType}="IDD" then
{dps.desttype}="IDD" and {dps.extension}={?Ext}
else if {?CallType}="STD" then
{dps.desttype}="STD" and {dps.extension}={?Ext}
else if {?CallType}="LOC" then
{dps.desttype}="LOC" and {dps.extension}={?Ext}
else if {?CallType}="IDDLOC" then
({dps.desttype}="IDD" or {dps.desttype}="LOC&quot

and {dps.extension}={?Ext}
else if {?CallType}="IDDSTD" then
({dps.desttype}="IDD" or {dps.desttype}="STD&quot

and {dps.extension}={?Ext}
else if {?CallType}="STDLOC" then
({dps.desttype}="LOC" or {dps.desttype}="STD&quot

and {dps.extension}={?Ext}

still i have the same problem...it reads all the records.., the results are ok..but takes a long time.

-k said "This IF seems to do nothing different between the IF and ELSE, so why not just avoid the IF entirely and just use: "

Your formula:
if {?Ext}="ALL" then
{dps.calldate}>={?datefrom} and {dps.calldate}<={? dateto}
else if {?Ext}<>"ALL" then
{dps.calldate}>={?datefrom} and {dps.calldate}<={?dateto}

Suggested:
{dps.calldate}>={?datefrom} and {dps.calldate}<={? dateto}

well it's just my point!. your suggested formula works..if i use just one if statement or just a selection formula as u have suggested , the records that are read is exactly the way i want it to work.

but when i use multiple if 's as shown by lisa or my previous code..it ends up reading all the records, but returns the correct data.

u can discard any theories of the correct parameters not being passed from the APP , because i tested it using crystal report preview also.

synapsevampire · Mar 7, 2003

Check teh database->Show SQL Query as you add in each piece of the record selection formula, that way you'll discover when it stops passing the SQL, something you're doing is breaking it.

-k

azwaan · Mar 7, 2003

ok..that was a good idea..i started off using this..selection formula

if {?Ext}="ALL" then
{dps.calldate}>={?datefrom} and {dps.calldate}<={?dateto}

here's how the SHOW SQL ..looks like

SELECT
dps."extension", dps."telephone", dps."countrycode", dps."startdatetime", dps."duration", dps."divid", dps."deptid", dps."sectid", dps."finalcharge", dps."calldate", dps."desttype",
compext."username"
FROM
{ oj "Ebilling"."dbo"."DPS" dps LEFT OUTER JOIN "Ebilling"."dbo"."compext" compext ON
dps."extension" = compext."userext"}
WHERE
dps."calldate" >= {ts '2003-03-01 00:00:00.00'} AND
dps."calldate" < {ts '2003-04-01 00:00:00.00'}
ORDER BY
dps."extension" ASC

now ..i took i took it a bit further,...

if {?Ext}="ALL" then
{dps.calldate}>={?datefrom} and {dps.calldate}<={?dateto}
else if {?Ext}<>"ALL" then
{dps.calldate}>={?datefrom} and {dps.calldate}<={?dateto} and {dps.desttype}="IDD"

now..the sql looks like

SELECT
dps."extension", dps."telephone", dps."countrycode", dps."startdatetime", dps."duration", dps."divid", dps."deptid", dps."sectid", dps."finalcharge", dps."calldate", dps."desttype",
compext."username"
FROM
{ oj "Ebilling"."dbo"."DPS" dps LEFT OUTER JOIN "Ebilling"."dbo"."compext" compext ON
dps."extension" = compext."userext"}
ORDER BY
dps."extension" ASC

notice that the WHERE clause is completely gone..! and mind u i passed the same parameters for both formulas. so the first IF statement should be executed..but somehow it seems crystal is not evaluating the selection forumla before it is sent to the DB , when there are ELSE/ ELSE IF Statements..

any way around this?

synapsevampire · Mar 8, 2003

Try:

(
{dps.calldate}>={?datefrom}
and
{dps.calldate}<={?dateto}
)
and
(
if {?Ext}<> "ALL" then
{dps.desttype}= "IDD"
else if {?Ext} = "ALL" then
true
)

That should pass everything correctly.

-k

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

linking tables and performance

Programmer

Instructor

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor