473,840 Members | 1,544 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

slow select statement -- please help

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
Jul 20 '05 #1
5 1885
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)
Jul 20 '05 #2
ed********@hotm ail.com (eddie wang) wrote in message news:<87******* *************** ****@posting.go ogle.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
Jul 20 '05 #3
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
Jul 20 '05 #4
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 )
Jul 20 '05 #5
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

aj*****@cusat.a c.in (Ajmal) wrote in message news:<b6******* *************** ****@posting.go ogle.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

Jul 20 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
3147
by: Shay | last post by:
essentially I am trying to do some counts based on some assumptions in the recordset. So I get the RS back, put the values into a variable, move to the next record in the RS and compare what is in the variable to the value in the next record in the recordset and do a count. Then overwrite the value in the variables and do the same for the next record and so. But this runs extremly slow. 5000 records takes about 10 minutes in IE6 and I...
1
6180
by: Gent | last post by:
am using FOR UPDATE triggers to audit a table that has 67 fields. My problem is that this slows down the system significantly. I have narrowed down the problem to the size (Lines of code) that need to be compiled after the trigger has been fired. There is about 67 IF Update(fieldName) inside the trigger and a not very complex select statement inside the if followed by an insert to the audit table. When I leave only a few IF-s in the...
11
17588
by: DJJ | last post by:
I am using the MySQL ODBC 3.51 driver to link three relatively small MySQL tables to a Microsoft Access 2003 database. I am finding that the data from the MySQL tables takes a hell of a long time to load making any kind linkage with my Access data virtually useless. I have the MySQL driver setup in as a USER DSN. The MySQL data is sitting out on a server and the Access database is running locally. The network connection is very...
4
5380
by: sherkozmo | last post by:
SQL2000 - AccessXP I built an adp file with a stored procedure from SQL as follows: SELECT * FROM Z_mis_sjk_job_code_access WHERE job_code=@JobCode UNION ALL SELECT * FROM Z_mis_sjk_job_code_access_mkey WHERE job_code=@JobCode ORDER BY app_only, submenu_number, menu_routine_number,
2
1768
by: Steve Hall | last post by:
Dear All We have a SQL Server 2000 BE on a Win 2K Server box. Access 2000 clients, running on Win 2000 Pro. Front end design is fairly straight forward - 5 different "record types" (split across approx 20 tables). For each "record type", there is a datasheet view, listing the records of that type, and this can be filtered by using a combination of text / combo / check box filters above the subform datasheet. On clicking a record ID in...
7
1573
by: mp | last post by:
Hi, MS Access DB, C#, VS, SQL I have implemented search with SQL statements like follows: SQLString = "SELECT ENGLISH FROM MyTable WHERE ENGLISH LIKE '"+txtWordManipulation.Text+"%' ORDER BY ENGLISH"; end everything is desperately slow. DB is more than 150k words and I haven't defined primary key because indexed option is set on Yes (Duplicates OK).
6
6715
by: MadMan2004 | last post by:
Hello all! I'm having a problem with a project I'm working on and I'd like to ask for anyone's input that might be helpful. I'm building a rather large front-end application connecting to an AS400 for the back end database and I'm experiencing slow response times when executing sql statements. Some select statement response times are bad. Not all, but some. And there doesn't seem to be a consistent factor in any of the sql statements...
9
8688
by: dan | last post by:
within a loop i am building a sql insert statement to run against my (programatically created) mdb. it works but it seems unreasonably SLOW! Sorry, dont have the code here but the jist is very standard (I think!); e.g: # get connection loop
12
3960
by: grace | last post by:
i am wondering why my database retrieval becomes too slow...we set up a new server (ubuntu, breezy badger) machine where we transferred all our files from the old server.. Our new server uses Asus p5pe-vm motherboard and an Intel Pentium D 3.0Ghz processor, compared to the old one where we uses asrock motherboard and AMD Duron. Both has the same version of mysql installed... To summarized, both machine has the same configuration except...
0
9861
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9699
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10924
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10605
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10301
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9444
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7839
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
1
4498
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4078
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.