SELECT from one table hangs browser

[Rustywater]

Hi,

Recently when loading data from one msaccess table from dozens in an access
database causes the web page hang, then eventually time out when the CF timeout
is reached. Looking at the server, the CF ODBC service, swsoc.exe, jumps from
3mb to 70mb and runs continually at 99% on the server. I have to kill the
service and restart it.

I'm running CF MX 6, with latest updater applied.

I've restored an earlier version of the DB, repaired and compacted it, copied
off the server and back, rebooted server etc but cannot isolate the problem.

It only does it to one table, (users), which has 1398 rows. It's not the
biggest table in the DB either. Total DB size is about 3 mb.

Has anyone seen this before or know why it happens and/or how to fix this?

thanks
Tanya

[BKBK]

It's most probably got something to do with the way you access the database. Could we have a look at your query?

[Rustywater]

Here's the query...

<cfquery name="get_users" datasource="#Application.Datasource#">
select users.uid_user, full_name from users, user_roles
<cfif #get_club.show_inactive# is False and get_active.is_committee is false>
where status = true
</cfif>

<cfif #session.sequence# is True>
order by first_name, last_name
<cfelse>
order by last_name, first_name
</cfif>
</cfquery>

<cfif isDefined('URL.user')>
<cfset FORM.user=#URL.user#>
<cfelse>
<cfif Not isDefined('FORM.user')>
<cfset FORM.user=#get_users.uid_user#>
</cfif>
</cfif>

This query is included in several other scripts. It's a standard select list.
I've found the problem. I was trying to join users with another table
user_roles; something I've done plenty of times. I will post a new thread
covering what that problm is. But for this one, the saga of the "hanging
swsoc.exe service" is solved... Kept me up all night tho :-)

Thanks for your help.

[paross1]

For what it is worth, what you have is a Cartesian join, since you have nothing
in your where clause where you relate the two tables. Such a join will return a
number of rows equal to the product of the number of rows in each table. Can be
pretty big if either table is large.

Phil