hello,
I have the following query. it returns result in less than 1 second.
select *
from employee e, address a
where e.id=a.emp_id
and e.id=1234

The problem is that it becomes extremely slow if i take the last line
out. So the query looks like:
select *
from employee e, address a
where e.id=a.emp_id

The above query is only supposed to return ~500 rows. but i still
haven't got the result back after 30 minutes.

Does anyone have any suggestions about troubleshooting this problem?

Thank you in advance!
Eddy

On 16 Jun 2004 16:59:09 -0700, eddie wang wrote:

>hello,
>I have the following query. it returns result in less than 1 second.
>select *
>from employee e, address a
>where e.id=a.emp_id
>and e.id=1234
>
>The problem is that it becomes extremely slow if i take the last line
>out. So the query looks like:
>select *
>from employee e, address a
>where e.id=a.emp_id
>
>The above query is only supposed to return ~500 rows. but i still
>haven't got the result back after 30 minutes.
>
>Does anyone have any suggestions about troubleshooting this problem?
>
>Thank you in advance!
>Eddy

Hi Eddy,

You've given very little information to go by, but my first guess is that
your query is blocked. Open a new window in Query Analyzer and execute the
query "EXEC sp_who2". Pay special attention to the column labelled
"BlkBy".

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

eddiekwang@hotmail.com (eddie wang) wrote in message news:<879e0e64.0406161559.3333b6f3@posting.google. com>...
> hello,
> I have the following query. it returns result in less than 1 second.
> select *
> from employee e, address a
> where e.id=a.emp_id
> and e.id=1234
>
> The problem is that it becomes extremely slow if i take the last line
> out. So the query looks like:
> select *
> from employee e, address a
> where e.id=a.emp_id
>
> The above query is only supposed to return ~500 rows. but i still
> haven't got the result back after 30 minutes.
>
> Does anyone have any suggestions about troubleshooting this problem?
>
> Thank you in advance!
> Eddy

If your query is taking an unexpectedly long time, you might want to
check that your query isn't blocked by another user - use sp_who2 for
this. Otherwise, it's not really possible to say without more
information - which version of MSSQL, what are the CREATE TABLE and
CREATE INDEX statements for each table, how many rows in each table
etc.

Simon

First of all I feel when you are talking about the performance, you
should consider the total number of records in the table.
Secondly if you could remove the '*' in the query and specify the only
the required fields from each table, you can expect an improved
performance.

Regards

On 16 Jun 2004 16:59:09 -0700, eddie wang wrote:

> hello,
> I have the following query. it returns result in less than 1 second.
> select *
> from employee e, address a
> where e.id=a.emp_id
> and e.id=1234
>
> The problem is that it becomes extremely slow if i take the last line
> out. So the query looks like:
> select *
> from employee e, address a
> where e.id=a.emp_id
>
> The above query is only supposed to return ~500 rows. but i still
> haven't got the result back after 30 minutes.
>
> Does anyone have any suggestions about troubleshooting this problem?
>
> Thank you in advance!
> Eddy

First, as the others have said, you should check if your query is blocked.

Second, you should reduce the fields being queried if possible. Instead of
select *, it may be enough to do something like select e.id, e.name,
a.emp_id, a.city.

Third, if it doesn't already exist, you may need an index like this one:
CREATE INDEX idx_address_xxx ON address ( e.emp_id )

Thank you everyone for the quick response!

it turns out that the speed issue I had was caused by too many columns
were selected. The two tables I had were not well designed. Each of
them has ~80 columns. (btw, I didn't design the tables).

Again, thank you for your time and help.
Eddy

ajmalvs@cusat.ac.in (Ajmal) wrote in message news:<b6086383.0406170522.2a92c257@posting.google. com>...
> First of all I feel when you are talking about the performance, you
> should consider the total number of records in the table.
> Secondly if you could remove the '*' in the query and specify the only
> the required fields from each table, you can expect an improved
> performance.
>
> Regards