Why does this take so long? - 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)

Why does this take so long?

Author

Topic

myk247365
Starting Member

7 Posts

Posted - 2006-06-01 : 15:28:41

Here's my query:

SELECT VenueID
FROM VirtualVenues V
WHERE IncludeRandom = 1
AND Inactive = 0
AND SiteID=39 AND
(VenueID NOT IN
(SELECT M.VirtualVenue
FROM Meetings M LEFT JOIN Reservations R ON ReservationMeetingID=MeetingID
WHERE Is_Master=0
AND M.Cancelled=0
AND R.Cancelled=0
AND ( (DATEADD(mi, (ResSetupMin*-1), DateTimeStart)
BETWEEN CAST('6/2/2006 4:00:00 PM' AS datetime)
AND CAST('6/2/2006 5:00:00 PM' AS datetime))
OR (CAST('6/2/2006 4:00:00 PM' AS datetime)
BETWEEN DATEADD(mi, (ResSetupMin*-1), DateTimeStart)
AND DATEADD(mi, (ResClosureMin-1), DateTimeEnd)))))
ORDER BY VenueID

This query takes about a minute to complete. If I set:
IncludeRandom = 0
Inactive = 0
It completes in about 3 seconds but that's not the data I want. If I set:
IncludeRandom = 0
Inactive = 1
It completes in about 3 seconds but that's not the data I want. If I set:
IncludeRandom = 1
Inactive = 1
The query returns no data. That's fine, but not what I need.

The problem here is that the first query will return the data I need but takes way too long and my connection times out. The other variations finish quickly enough but are not the data sets I'm looking for. Why does changing the true/false have such a great effect on how long the query takes to run?

I've looked at this in MS SQL Query Analyzer and found that the execution plan is very different for my query as opposed to the variations. I'm not a SQL expert, so if I'm doing something stupid, please let me know.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-01 : 15:44:44

How many rows does it return when IncludeRandom = 1 and Inactive = 0?

What indexes do you have on VirtualVenues, Reservations, and Meetings?

Tara Kizer
aka tduggan

myk247365
Starting Member

7 Posts

Posted - 2006-06-01 : 16:01:07

I don't have any indexes on the tables. There's about 15k rows in reservations, 13k rows in meetings and 90 rows in VirtualVenues. So there's not much to check. The result with IncludeRandom = 1 and Inactive = 0 is 13 rows.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-01 : 16:07:25

Do you at least have a primary key on each of the tables?

Tara Kizer
aka tduggan

myk247365
Starting Member

7 Posts

Posted - 2006-06-01 : 16:08:21

yes.

myk247365
Starting Member

7 Posts

Posted - 2006-06-01 : 16:24:48

There's an ID column that is the primary key and identity for each of the tables. I'm looking at how to create indexes and use them, but I don't see as there's enough data to go through. None of the tables have more than 20 fields. Will indexes really make a difference here?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-01 : 16:29:42

It depends on the execution plans of your queries. Can you do a screen capture of each of them and post them on some web server on the Internet, then provide us a link to these images? You could also post the text of the execution plan, but I can't read it as well as I can with the GUI way.

You probably need indexes on VenueID and SiteID (maybe a composite index will do).

ReservationMeetingID and MeetingID need indexes.

This whole part needs to be rewritten so that it can use an index (which it can't right now even if you had indexes on these datetime and minute columns):
( (DATEADD(mi, (ResSetupMin*-1), DateTimeStart)
BETWEEN CAST('6/2/2006 4:00:00 PM' AS datetime)
AND CAST('6/2/2006 5:00:00 PM' AS datetime))
OR (CAST('6/2/2006 4:00:00 PM' AS datetime)
BETWEEN DATEADD(mi, (ResSetupMin*-1), DateTimeStart)
AND DATEADD(mi, (ResClosureMin-1), DateTimeEnd)))))

Tara Kizer
aka tduggan

myk247365
Starting Member

7 Posts

Posted - 2006-06-01 : 16:40:28

ok, I'll post pics of the execution plans in a few minutes. Can you recommend an online resource for a tutorial on creating and using indexes?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-01 : 16:49:44

I don't have any recommendations. I use SQL Server Books Online if I want more information about SQL Server concepts.

Tara Kizer
aka tduggan

myk247365
Starting Member

7 Posts

Posted - 2006-06-01 : 16:56:09

You can find pics of the execution plans here:
http://www.ncsa.uiuc.edu/People/mimiller/querypics/query.html

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-01 : 17:08:03

The index and table scans are killing the performance of your queries.

The most important indexes to add are to VenueID, ReservationMeetingID, and MeetingID. You might want to make VenueID a clustered index if you'll be doing a lot of ordering by it.

If you could explain what the DATEADD/BETWEEN section of your code is doing, we can help you rewrite it so that indexes can be used. Right now, you can't even use indexes on that section due to how it is coded.

Tara Kizer
aka tduggan

myk247365
Starting Member

7 Posts

Posted - 2006-06-01 : 17:31:47

the date stuff is checking for conflicts so we don't have two meetings in the same venue at the same time. Essentially I need to check that the following is not true:

date1start
date2start
date1end

date2start
date1start
date2end

I could check for the endtimes in between, but that would just be duplicating effort. So I take the time entered by the user as date1 and any meetings in the DB as date2. Or vice-versa...

If there's a better way to do this, I'd love to hear it...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources