Weird query problem, what am I missing? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server Development (2000)

Weird query problem, what am I missing?

Author

Topic

sbt1
Yak Posting Veteran

89 Posts

Posted - 2004-05-04 : 08:59:24

I have two tables, one called Tasks and one called TaskDates. The TaskDates table has a field called TaskID that matches a field in Tasks.

Using this query to find any Tasks without any corresponding TaskDates records works fine:

select tasks.TaskID, tasks.customer from tasks WHERE tasks.TaskID not in (select taskdates.taskid from taskdates) (returns very quickly, always works correctly).

But this one to find the opposite, TaskDates without associated Tasks, fails:
select taskdates.TaskID, taskdates.customer from taskdates WHERE taskdates.TaskID not in (select tasks.taskid from tasks).

It'll sit there and run for a long time then fail with a timeout error.

The Tasks table has about 25000 records, the Task Dates about 40000.

Any thoughts? Is it just a timeout issue, or something else?

Both tables have a primary key. Here's the script to create the TaskDates table:

CREATE TABLE [TaskDates] (
[DateValue] [datetime] NULL ,
[StartTime] [datetime] NULL ,
[EndTime] [datetime] NULL ,
[Resource] [nvarchar] (50) NULL ,
[Status] [nvarchar] (50) NULL ,
[Confirmed] [bit] NOT NULL CONSTRAINT [DF__Temporary__Confi__1699586C] DEFAULT (0),
[CallFirstRequired] [bit] NOT NULL CONSTRAINT [DF__Temporary__CallF__178D7CA5] DEFAULT (0),
[Locked] [bit] NOT NULL CONSTRAINT [DF__Temporary__Locke__1881A0DE] DEFAULT (0),
[Private] [bit] NOT NULL CONSTRAINT [DF__Temporary__Priva__1975C517] DEFAULT (0),
[Duration] [real] NULL CONSTRAINT [DF__Temporary__Durat__1A69E950] DEFAULT (0),
[OTDuration] [real] NULL CONSTRAINT [DF__Temporary__OTDur__1B5E0D89] DEFAULT (0),
[Customer] [nvarchar] (255) NULL ,
[TaskID] [int] NULL CONSTRAINT [DF__Temporary__TaskI__1C5231C2] DEFAULT (0),
[Type] [nvarchar] (50) NULL ,
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Owner] [nvarchar] (50) NULL ,
[LastSyncTime] [datetime] NULL ,
[DateName] [varchar] (50) NULL ,
CONSTRAINT [aaaaaTaskDates_PK] PRIMARY KEY NONCLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

and the one to create the Tasks table:
CREATE TABLE [Tasks] (
[TaskID] [int] IDENTITY (1, 1) NOT NULL ,
[Sent] [bit] NOT NULL CONSTRAINT [DF__TemporaryU__Sent__2116E6DF] DEFAULT (0),
[Confirmed] [bit] NOT NULL CONSTRAINT [DF__Temporary__Confi__220B0B18] DEFAULT (0),
[Customer] [nvarchar] (50) NULL ,
[CallerName] [nvarchar] (50) NULL ,
[CallerPhone] [nvarchar] (50) NULL ,
[TaskType] [nvarchar] (50) NULL ,
[TaskDescription] [nvarchar] (255) NULL ,
[JobNumber] [nvarchar] (50) NULL ,
[StartDate] [datetime] NULL ,
[CustomersID] [int] NULL ,
[EmployeesID] [int] NULL ,
[CallCloseComments] [nvarchar] (255) NULL ,
[IsPM] [bit] NOT NULL CONSTRAINT [DF__TemporaryU__IsPM__22FF2F51] DEFAULT (0),
[PMTemplate] [nvarchar] (50) NULL ,
[ScheduleTemplate] [nvarchar] (50) NULL ,
[DateEntered] [datetime] NULL ,
[TimeEntered] [datetime] NULL ,
[EnteredBy] [nvarchar] (50) NULL ,
[IsTSP] [bit] NOT NULL CONSTRAINT [DF__Temporary__IsTSP__23F3538A] DEFAULT (0),
[InitAssigned] [nvarchar] (50) NULL ,
[LaborType] [nvarchar] (50) NULL ,
[Printed] [bit] NOT NULL CONSTRAINT [DF__Temporary__Print__24E777C3] DEFAULT (0),
[Attachment] [nvarchar] (50) NULL ,
[SubLocation] [nvarchar] (50) NULL ,
CONSTRAINT [aaaaaTasks_PK] PRIMARY KEY NONCLUSTERED
(
[TaskID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

Any help would be appreciated.

thanks

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-05-04 : 09:08:59

This is not an attempt to be glib, but what is a TaskDate without a Task?

For speed, build an index on the TaskID field in the TaskDates table.

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2004-05-04 : 09:10:30

Try Left outer join..
select taskdates.TaskID, taskdates.customer
from taskdates
Left Outer Join tasks
on taskdates = tasks.taskid
where task.TaskID is null

- Sekar

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources