ASP - SQL speed problems

Results 1 to 2 of 2

Thread: ASP - SQL speed problems

  1. #1
    NickCodling Guest

    Default ASP - SQL speed problems

    Hi,<BR>We have an ASP that runs a qry to fetch a large amount of data from a sql 6.5 db. It takes forever. We have other qrys that <BR><BR>work fine, the troublesome part seems to be the fact that we want to join to a large table. The qry runs fine on its own, <BR><BR>only within the page do we have problems.<BR><BR>With Join in sql<BR><BR> 58 sec in ISQL<BR> 4hrs + in asp<BR><BR>Without Join ( hacked table to hold the other columns) not ideal but done to prove a point.<BR><BR> 21 secs in isql <BR> 45 secs <BR><BR><BR>The number of records to return for the test is 2830. None of us have done any asp before. <BR>There is a Crystal report that gets generated, to display the result.<BR><BR>Below is the loop that does the main work and the crystal bit. <BR>Any help would really be appreciated.<BR><BR>Thanks in advance<BR><BR>Nick<BR><BR><BR>Sub ViewReport_Click()<BR><BR> &#039The SQL string<BR> sReportParams="&P1=IPP=" & Press.Text _<BR> & "&P2=IPJ=" & JobRef.Text _<BR> & "&P3=JDT=" & Title.Text _<BR> & "&P4=PDS=" & HSCALFrom.Value _<BR> & "&P5=PDE=" & HSCALTo.Value _<BR> & "&TDS=" & HSCALFrom.Value _<BR> & "&TDE=" & HSCALTo.Value<BR><BR> &#039Fire off the report<BR> top.CrystalView.location.href="CrystalView.asp" & "?RPT=InkUse" & sReportParams<BR>End Sub<BR><BR>Sub GetJobRefs()<BR> JobRef.Clear<BR><BR>&#060;% SQLQuery="SELECT DISTINCT job_ref_d1,date_time,press_name,jobs.title_text FROM report..tmp_ink, jobs <BR><BR>WHERE job_ref_d1&#060;&#062;&#039blank&#039 AND job_ref_d1=jobs.job_ref ORDER BY job_ref_d1,date_time" <BR> Set JobRefList=Session("oConnect").Execute(SQLQuery)<B R><BR> Do While Not JobRefList.EOF<BR>%&#062;<BR> <BR>&#060;%<BR> sCurrentDate=Left(JobRefList("date_time"),8)<BR> sNextDate=sCurrentDate<BR> sLastJobRef=""<BR> Do While (Not JobRefList.EOF) And sNextDate=sCurrentDate<BR> Do While (Not JobRefList.EOF) And sNextPress=sCurrentPress And sNextDate=sCurrentDate<BR> sJobRef=JobRefList("job_ref_d1")<BR> If sJobRef&#060;&#062;sLastJobRef Or sLastJobRef="" Then<BR>%&#062;<BR> If (Title.Text="&#060;%=JobRefList("title_text")%&#06 2;" OR Title.Text="All") AND _<BR> (Press.Text="&#060;%=JobRefList("press_name")%&#06 2;" OR Press.Text="All") Then <BR><BR>JobRef.AddItem "&#060;%=sJobRef%&#062;"<BR><BR><BR>&#060;%<BR > End If<BR> JobRefList.MoveNext<BR> sLastJobRef=sJobRef<BR> sNextDate=Left(JobRefList("date_time"),8)<BR> Loop<BR> Loop<BR>%&#062;<BR>&#060;%<BR> Loop<BR>%&#062;<BR><BR> If JobRef.ListCount=0 Then<BR> JobRef.AddItem "None",0<BR> Else<BR> If JobRef.ListCount&#062;1 Then JobRef.AddItem "All",0<BR> End If<BR> JobRef.ListIndex=0<BR>End Sub<BR><BR>

  2. #2
    Scott Sargent Guest

    Default RE: ASP - SQL speed problems

    Why not use a stored procedure for the query? i find that calling stored procedures is blindingly fast when compared to passing sql queries through ASP. the Stored Procedure is precomplied by the database server hence much faster. give it a shot.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts