Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-02-22 : 09:05:38
|
Kevin writes "We have designed a database that allows user feedback for a number of web sites. It consists of 9 tables that store everything from the company to the end user's answers. One table stores the company name, one stores the form name and id, one stores questions associated to the forms.
In particular there are 6 tables that deal with answers, based on the type of input field that was used. There are tables for checkboxes, radio buttons, text boxes (areas), text fields, and list boxes. Each of these tables consists of two columns, answerid and value. The answer table contains information for each entry into each of the subtables. Specifically it contains answerid, questionid, type, entrytime, extracttime, and submitid.
We do a full join on each of the subtables to get answers for a particular form:
select a.submitid submitid,a.questionid questionid, a.answerid, a.entrytime, tf.value tf_value, lb.value lb_value, cb.value cb_value, tb.value tb_value, rb.value rb_value
from answers a
full join ans_TextFields tf
on a.answerid = tf.answerid
full join ans_ListBoxes lb
on a.answerid = lb.answerid
full join ans_checkboxesBoxes cb
on a.answerid = cb.answerid
full join ans_TextBoxes tb
on a.answerid = tb.answerid
full join ans_RadioButtons rb
on a.answerid = rb.answerid
where a.extracttime is NULL
and a.questionid > question1
and a.questionid < question10
order by a.submitid, a.questionid
When the table was small < 1 million records this worked flawlessly, now that it's up around 5.5 million the query executes but the ASP page appears not to wait for records to return and begins processing on nothing which generates an infinite loop that can only be stopped by restarting the IIS service and killing the query.
When run manually the query will take anywhere from 7 to 25 minutes to complete, depending on the form. Records usually start displaying after 1:30 or more.
1) how can I ensure that the ASP processing has some reocrds to process before entering the processing loop?
2) what would be the best way to troubleshoot what is causing this?
Any help you can provide would be greatly appreciated.
SQL 7.0 SP2, NT 4.0 SP6a Web and DB server.
Thanks,
Kevin" |
|
|
|
|
|
|
|
Joining multiple large tables