SELECT Timeouts and slowdowns - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SELECT Timeouts and slowdowns

Author

Topic

discofish
Starting Member

5 Posts

Posted - 2004-08-02 : 16:14:49

I have inherited a project where I am to create an add-on to a legacy database running on SQL 6.5.

I have the following query which executes in ~0ms

SELECT DISTINCT Availability.RoomType,Availability.ThisDate,Availability.QtyRooms FROM Availability,RoomTypes WHERE Availability.ThisDate BETWEEN '10/1/2004' AND '10/10/2004'

When I add an additional table to the FROM clause, it takes nearly 5 seconds to complete! The additional table (RateTableDetail) contains about 2400 records and 12 fields. What could be going on here? Could a trigger be coming to life and hogging resources? There are 225 results returned from the query.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-02 : 16:23:13

Why are you using RoomTypes in your query when you don't use it anywhere. You only join to another table if you need a column out of it or need to include it in the where condition. You don't have RoomTypes anywhere in your query except in the from clause.

If you do need RoomTypes, then you'll need to add the join conditions as they are missing in your query. How are Availability and RoomTypes related to each other? These should be included in the WHERE clause. Or use this:

SELECT DISTINCT a.RoomType, a.ThisDate, a.QtyRooms
FROM Availability a
INNER JOIN RoomTypes r
a.SomePKColumn = r.SomeFKColumn
WHERE a.ThisDate BETWEEN '10/1/2004' AND '10/10/2004'

For the joins, the FK columns should be indexed. PKs automatically get indexed, but FKs do not.

Tara

discofish
Starting Member

5 Posts

Posted - 2004-08-02 : 16:28:19

Tara,

Thanks for your reply. Once I try joing tabes, it times out. I also wonder why adding that table into the FROM clause would even slow things down at all - since I don't use it. It seems like the SQL parser should be smart enough to know when something doesn't get used and just ignore it.

Discofish

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-02 : 16:30:23

Why would you add something and not use it? Your query should be:

SELECT DISTINCT RoomType, ThisDate, QtyRooms
FROM Availability
WHERE ThisDate BETWEEN '10/1/2004' AND '10/10/2004'

Unless you need the extra information from the other tables, do NOT add them to the query. You are probably missing indexes which is also causing your problem. Triggers are not the cause.

For the above query, you should have ThisDate indexed.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources