Timeout on Large SQL DELETE

Results 1 to 3 of 3

Thread: Timeout on Large SQL DELETE

  1. #1
    Join Date
    Dec 1969

    Default Timeout on Large SQL DELETE

    I seem to be having a problem with ASP. I can quickly SELECT 400 entries from the SQL database and display them on the sreen. However, when I try to delete those same 400 TASKS I receive a server timeout message. I ran the DELETE Statement in SQL itesef and that took while at well 1m 25s. I was wondering if there is something I am doing wrong to cause such a delay. Here is a copy of my delete procedure for one specific set of entries.<BR><BR>&#060;%@language=VBScript%&#062;<B R>&#060;!--#include file="connect.asp"--&#062;<BR>&#060;%<BR>sql1 = "DELETE FROM PRSN_CFETP WHERE tmpl_id =167"<BR>conn.execute sql1<BR>response.write "1"<BR>sql1 = "DELETE FROM WRKCNTR_TASK WHERE tmpl_id =167"<BR>conn.execute sql1<BR>response.write "2"<BR>sql1 = "DELETE FROM TASK_PROF_LVL WHERE tmpl_id =167"<BR>conn.execute sql1<BR>response.write "3"<BR>sql1 = "DELETE FROM PRSN_TASK WHERE tmpl_id =167"<BR>conn.execute sql1<BR>response.write "4"<BR>sql1 = "DELETE FROM TASK WHERE tmpl_id =167"<BR>conn.execute sql1<BR>response.write "5"<BR>sql1 = "DELETE FROM TMPL WHERE tmpl_id =167"<BR>conn.execute sql1<BR>response.write "6"<BR>%&#062;

  2. #2
    Join Date
    Dec 1969

    Default Maybe not the BEST way, but...

    Just had a random thought... why not break up your delete into six different sections, like so...<BR><BR>dim iCounter<BR>iCounter = request.querystring("counter")<BR>Select Case strCounter<BR>Case ""<BR>sql1 = "DELETE FROM PRSN_CFETP WHERE tmpl_id =167"<BR>Case "1"<BR>sql1 = "DELETE FROM WRKCNTR_TASK WHERE tmpl_id =167"<BR>Case "2"<BR>...<BR>Case "5"<BR>...<BR>End Select<BR>conn.execute sql1<BR>if cInt(iCounter) &#060; 6 then<BR>response.redirect "mypage.asp?counter=" & (CInt(iCounter) + 1)<BR>end if<BR><BR>Okay, so I&#039;m just banging this out as I go along... but do you see what this is attempting to do? You do one execution on a page, increment a counter, and pass the counter back to the SAME page. It will then do the NEXT execution, and continue looping until the last procedure. This way, at one execution per page, you shouldn&#039;t get any timeouts.

  3. #3
    Join Date
    Dec 1969

    Default check indexing

    I think your db need indexing. This should work fine.<BR>In ms-sql Enterprise Manager <BR>Right click your table &#062; Manage Indexes &#062; New Index ....<BR>Make sure tmpl_id is indexed!<BR><BR><BR>

Posting Permissions

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