delay "reading records".....

[kaiso]

While using CR8, on NT 4.0 sp6 and SQL Server 7.0, my report accesses the database in a timely fashion. ( i know this because i turned on 'count number of records first' option ).

However, it then reads approx 2000 records before delaying for about 4 minites before completing the request ( 10,000 recs ).

The strange thing is other reports work fine, but with this particular report, only 1 specific criteria value causes the delay...

This is causing my users' browsers to time out ( i am using wcs thru a url ).

I'am stumped!! Any ideas????

Thanks Kaiso

 

[kenhamady]

What is the selection formula in this report?

What SQL is it generating (Database - Show SQL)? Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[kaiso]

Ken;
Here is the sql being generated:

SELECT
Stats_Site."SiteName", StatsSS."sDate", StatsSS."ClientIP", StatsSS."ServerIP", StatsSS."extension"
FROM
{ oj "Hit"."dbo"."Stats_Site" Stats_Site INNER JOIN "Hit"."dbo"."StatsSS" StatsSS ON
Stats_Site."ServerIP" = StatsSS."ServerIP"}
WHERE
StatsSS."sDate" >= ? AND
StatsSS.&quot;sDate&quot; <= ? AND
StatsSS.&quot;ServerIP&quot; = '64.58.213.239'


and this is the selection formaula:


{StatsSS.sDate} in

switch
(
LowerCase ({?period}) = &quot;daily&quot; ,
{?Reference Date}-{?units} + 1
to {?Reference Date},

LowerCase ({?period}) = &quot;weekly&quot; ,
{?Reference Date}-({?units}*7)- DayOfWeek({?Reference Date})+1
to {?Reference Date},

LowerCase ({?period}) = &quot;monthly&quot; ,
dateserial
( year({?Reference Date}-({?units}*30)), month({?Reference Date}-({?units}*30)),1 )
to {?Reference Date}
)

and

{StatsSS.ServerIP} = {?serverIP}



To elaborate further, the specific criteria change was the ?serverip.

Timing for the working serverip was 7 secs to access the database and 12 secs to read and format 40,692 records for the report; for the non-working serverip 7 secs to access the database and 4+ min to read and format 18,600 records for the report.

Hope this answers your questions and thanks for the quick response.

 

[kenhamady]

Do you mean that only one specific IP causes this behavior? And it isn't the largest one? That is weird.

If you run the exact same SQL statement (with dates filled in) from another SQL tool do you get similar behavior?

Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[kaiso]

Yes, I did try in SQL Analyser with the 2 ips and the timings were 20 secs for each query.

The 'execution plan' were also basically both the same.

Thanks....

 

[kenhamady]

I am stumped. Can't see how CR or the database would treat them differently.

And it didn't complain about the OJ in the FROM line of the SQL, sometimes CR sticks that in and causes a problem.

One last thing. You said the execution plans was &quot;basically&quot; the same. Was there a difference? Grasping at straws. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[kaiso]

I checked the execution plans and the only difference is the row count returned. The nodes in the plan all match up exactly.

Regarding the OJ, it works fine for all my other reports, however, when I paste it into SQL Analyser I have to remove it along with the curly braces and double quotation marks.

Thanks

 

[kenhamady]

Sorry I couldn't be more help. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/