I am not a programmer but have noticed in this query peaking my processor
when running. The table has over 2.7 million rows. Is there a better way
to write the sp for better performance or tweak the table? The excution
plan shows TableScan 37%>Filter 56%> Sort 4%>pParallelism/Gather Stream 3%>
Select 0%.
-- TABLE:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Appointment]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[Appointment]
GO
CREATE TABLE [dbo].[Appointment] (
[RecordID] [int] IDENTITY (1, 1) NOT NULL ,
[CPINumber] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[VisitSuffix] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PatientName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[DOB] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Sex] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Race] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SSN] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ApptLocation] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[ResourceCode] [varchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[ResourceGroup] [varchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ApptDate] [datetime] NOT NULL ,
[Duration] [int] NULL ,
[EncounterID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CreatedBy] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CreatedDate] [datetime] NULL ,
[LastChangeBy] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastChangeDate] [datetime] NULL ,
[ApptStatus] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[InterfaceStatus] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[ActivityType] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Comments] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Comments2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Arrived] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COLD] [datetime] NULL ,
[Comments3] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Comments4] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PatientTypeCode] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FinancialClassCode] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Scraped] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BaseDate] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Appointment] WITH NOCHECK ADD
CONSTRAINT [DF_Appointment_Scraped] DEFAULT (0) FOR [Scraped],
CONSTRAINT [PK_Appointment] PRIMARY KEY NONCLUSTERED
(
[RecordID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_ResourceCode] ON [dbo].[Appointment]([ResourceCode]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_AppointmentCPIDate] ON [dbo].[Appointment]([CPINumber],
[ApptDate]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_Appointment] ON [dbo].[Appointment]([CPINumber],
[VisitSuffix], [ActivityType], [ApptStatus]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
CREATE INDEX [IX_Appointment_ResourceGroup] ON
[dbo].[Appointment]([ResourceGroup]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
-- STORED PROCEDURE::
CREATE Procedure spGetApptsForInterface
As
SELECT *
FROM dbo.Appointment
WHERE ((ResourceCode LIKE 'SHC%' OR
ResourceCode LIKE 'GM%' OR
ResourceCode LIKE 'GI%' or ResourceCode like 'PUL%' or
ResourceCode like 'CAR%' or ResourceCode like 'MED%') AND (ApptDate >=
'04/07/2003') AND (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate,
GETDATE()) > 4)) OR
(((ResourceCode = 'GHCCARAD') or (resourcecode =
'GHCJONES')) AND (ApptDate >= '01/27/2003') AND (InterfaceStatus = 'R') AND
(DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) OR
(((ResourceCode = 'OPCNUTRI') or (resourcecode like
'MON%') or (resourcecode like 'GON%') or (resourcecode like 'LON%') or
(resourcecode like 'END%')) AND (ApptDate >= '07/01/2003') AND
(InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) OR
(((ResourceCode LIKE 'ID%') AND (ApptDate >= '08/12/2003') AND
(InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) or
(((ResourceCode = 'SUREIDTS') or (ResourceCode = 'SUREIDTW') or
(ResourceCode = 'VASLAB1') or (ResourceCode = 'VASLAB2')) AND (ApptDate >=
'12/01/2003') AND (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate,
GETDATE()) > 4)) or
(((ResourceCode = 'PFT') or (ResourceCode = 'PFT2')) AND (ApptDate
>= '1/13/2004') AND (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate,
GETDATE()) > 4)) or
(((ResourceCode like 'CWC%') or (ResourceCode like 'TEL%')) AND
(ApptDate >= '4/13/2004') AND (InterfaceStatus = 'R') AND (DATEDIFF(mi,
CreatedDate, GETDATE()) > 4)) or
(ResourceCode like 'CHEM%' AND ApptDate >= '8/13/2004' AND
InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
(ResourceCode like 'PREOP%' AND ApptDate >= '2/14/2005' AND
InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
(ResourceCode like 'UWHC%' AND ApptDate >= '3/08/2005'
AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
(ResourceCode like 'REN%' AND ApptDate >= '3/08/2005'
AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
(ResourceCode like 'SUR%' AND ApptDate >= '3/08/2005'
AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
(((ResourceCode like 'SON%') or (ResourceCode like
'BON%') or (ResourceCode like 'BMO%')) AND (ApptDate >= '03/14/2005') AND
(InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) or
(((ResourceCode like 'JEI%') or (ResourceCode like
'NEU%') or (ResourceCode like 'ACRC%')) AND (ApptDate >= '04/07/2005') AND
(InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) or
(((ResourceCode like 'DON%') or (ResourceCode like
'HON%') or (ResourceCode like 'JON%') or (ResourceCode like 'UON%') or
(ResourceCode like 'ORT%')) AND (ApptDate >= '04/07/2005') AND
(InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) or
(ResourceCode like 'OON%' AND ApptDate >= '5/23/2005'
AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
(ResourceCode like 'NSG%' AND ApptDate >= '7/27/2005'
AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
(ResourceCode like 'JTS%' AND ApptDate >= '7/27/2005'
AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
(ResourceCode like 'URO%' AND ApptDate >= '7/28/2005'
AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
(ResourceCode like 'LT%' AND ApptDate >= '11/14/2005'
AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
(((ResourceCode = 'CVTOBLE') or (ResourceCode =
'CVANTAK')) AND (ApptDate >= '10/12/2005') AND (InterfaceStatus = 'R') AND
(DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) or
(((ResourceCode like 'INFCH%') or (ResourceCode like
'INFRM%')) AND (ApptDate >= '11/07/2005') AND (InterfaceStatus = 'R') AND
(DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) or
(((ResourceCode like 'MICHAIR%') or (ResourceCode like
'MIEXP%') or (ResourceCode like 'MIROOM%')) AND (ApptDate >= '11/07/2005')
AND (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)))
ORDER BY ApptDate
return
GOThat is a horrendously long WHERE clause and reads like a suspense novel;
could it possibly be simplified?
I've found that such queries can be optimized by wrapping it around a
sub-query that utilizes a simple and selective index search. In doing so,
the remaining WHERE clause is guaranteed to apply against a much smaller
resultset.
Try this: In the example below, I have simply replaced the reference to
table Appointment with a sub-query and aliased it as Appointment. The
remaining query can be left unmodified. If my guess about ApptDate is wrong,
then replace the sub-query condition with something more appropriate.
SELECT
*
FROM
(
select * from dbo.Appointment where ApptDate >= '2003/04/07'
) as Appointment
WHERE
. . .
. . .
. . .
"CD" <mcdye1@.hotmail.REMOVETHIS.com> wrote in message
news:eYmG3WCIGHA.1132@.TK2MSFTNGP10.phx.gbl...
>I am not a programmer but have noticed in this query peaking my processor
>when running. The table has over 2.7 million rows. Is there a better way
>to write the sp for better performance or tweak the table? The excution
>plan shows TableScan 37%>Filter 56%> Sort 4%>pParallelism/Gather Stream 3%>
>Select 0%.
> -- TABLE:
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Appointment]') and OBJECTPROPERTY(id, N'IsUserTable') =
> 1)
> drop table [dbo].[Appointment]
> GO
> CREATE TABLE [dbo].[Appointment] (
> [RecordID] [int] IDENTITY (1, 1) NOT NULL ,
> [CPINumber] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [VisitSuffix] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [PatientName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [DOB] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Sex] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Race] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SSN] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ApptLocation] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [ResourceCode] [varchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [ResourceGroup] [varchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ApptDate] [datetime] NOT NULL ,
> [Duration] [int] NULL ,
> [EncounterID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CreatedBy] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CreatedDate] [datetime] NULL ,
> [LastChangeBy] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [LastChangeDate] [datetime] NULL ,
> [ApptStatus] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [InterfaceStatus] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [ActivityType] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Comments] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Comments2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Arrived] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [COLD] [datetime] NULL ,
> [Comments3] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Comments4] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PatientTypeCode] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [FinancialClassCode] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Scraped] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [BaseDate] [datetime] NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Appointment] WITH NOCHECK ADD
> CONSTRAINT [DF_Appointment_Scraped] DEFAULT (0) FOR [Scraped],
> CONSTRAINT [PK_Appointment] PRIMARY KEY NONCLUSTERED
> (
> [RecordID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [IX_ResourceCode] ON [dbo].[Appointment]([ResourceCode])
> WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [IX_AppointmentCPIDate] ON [dbo].[Appointment]([CPINumber],
> [ApptDate]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [IX_Appointment] ON [dbo].[Appointment]([CPINumber],
> [VisitSuffix], [ActivityType], [ApptStatus]) WITH FILLFACTOR = 90 ON
> [PRIMARY]
> GO
> CREATE INDEX [IX_Appointment_ResourceGroup] ON
> [dbo].[Appointment]([ResourceGroup]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
>
> -- STORED PROCEDURE::
> CREATE Procedure spGetApptsForInterface
> As
> SELECT *
> FROM dbo.Appointment
> WHERE ((ResourceCode LIKE 'SHC%' OR
> ResourceCode LIKE 'GM%' OR
> ResourceCode LIKE 'GI%' or ResourceCode like 'PUL%'
> or ResourceCode like 'CAR%' or ResourceCode like 'MED%') AND (ApptDate >=
> '04/07/2003') AND (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate,
> GETDATE()) > 4)) OR
> (((ResourceCode = 'GHCCARAD') or (resourcecode =
> 'GHCJONES')) AND (ApptDate >= '01/27/2003') AND (InterfaceStatus = 'R')
> AND (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) OR
> (((ResourceCode = 'OPCNUTRI') or (resourcecode like
> 'MON%') or (resourcecode like 'GON%') or (resourcecode like 'LON%') or
> (resourcecode like 'END%')) AND (ApptDate >= '07/01/2003') AND
> (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) OR
> (((ResourceCode LIKE 'ID%') AND (ApptDate >= '08/12/2003') AND
> (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) or
> (((ResourceCode = 'SUREIDTS') or (ResourceCode = 'SUREIDTW') or
> (ResourceCode = 'VASLAB1') or (ResourceCode = 'VASLAB2')) AND (ApptDate >=
> '12/01/2003') AND (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate,
> GETDATE()) > 4)) or
> (((ResourceCode = 'PFT') or (ResourceCode = 'PFT2')) AND (ApptDate
> GETDATE()) > 4)) or
> (((ResourceCode like 'CWC%') or (ResourceCode like 'TEL%')) AND
> (ApptDate >= '4/13/2004') AND (InterfaceStatus = 'R') AND (DATEDIFF(mi,
> CreatedDate, GETDATE()) > 4)) or
> (ResourceCode like 'CHEM%' AND ApptDate >= '8/13/2004' AND
> InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (ResourceCode like 'PREOP%' AND ApptDate >= '2/14/2005' AND
> InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (ResourceCode like 'UWHC%' AND ApptDate >=
> '3/08/2005' AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate,
> GETDATE()) > 4) or
> (ResourceCode like 'REN%' AND ApptDate >= '3/08/2005'
> AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (ResourceCode like 'SUR%' AND ApptDate >= '3/08/2005'
> AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (((ResourceCode like 'SON%') or (ResourceCode like
> 'BON%') or (ResourceCode like 'BMO%')) AND (ApptDate >= '03/14/2005') AND
> (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) or
> (((ResourceCode like 'JEI%') or (ResourceCode like
> 'NEU%') or (ResourceCode like 'ACRC%')) AND (ApptDate >= '04/07/2005') AND
> (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) or
> (((ResourceCode like 'DON%') or (ResourceCode like
> 'HON%') or (ResourceCode like 'JON%') or (ResourceCode like 'UON%') or
> (ResourceCode like 'ORT%')) AND (ApptDate >= '04/07/2005') AND
> (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) or
> (ResourceCode like 'OON%' AND ApptDate >= '5/23/2005'
> AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (ResourceCode like 'NSG%' AND ApptDate >= '7/27/2005'
> AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (ResourceCode like 'JTS%' AND ApptDate >= '7/27/2005'
> AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (ResourceCode like 'URO%' AND ApptDate >= '7/28/2005'
> AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (ResourceCode like 'LT%' AND ApptDate >= '11/14/2005'
> AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (((ResourceCode = 'CVTOBLE') or (ResourceCode =
> 'CVANTAK')) AND (ApptDate >= '10/12/2005') AND (InterfaceStatus = 'R') AND
> (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) or
> (((ResourceCode like 'INFCH%') or (ResourceCode like
> 'INFRM%')) AND (ApptDate >= '11/07/2005') AND (InterfaceStatus = 'R') AND
> (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) or
> (((ResourceCode like 'MICHAIR%') or (ResourceCode like
> 'MIEXP%') or (ResourceCode like 'MIROOM%')) AND (ApptDate >= '11/07/2005')
> AND (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate, GETDATE()) >
> 4)))
>
> ORDER BY ApptDate
> return
> GO
>|||Thanks for the reply. The results are the same as without the subquery.
"JT" <someone@.microsoft.com> wrote in message
news:ug4YLpCIGHA.916@.TK2MSFTNGP10.phx.gbl...
> That is a horrendously long WHERE clause and reads like a suspense novel;
> could it possibly be simplified?
> I've found that such queries can be optimized by wrapping it around a
> sub-query that utilizes a simple and selective index search. In doing so,
> the remaining WHERE clause is guaranteed to apply against a much smaller
> resultset.
> Try this: In the example below, I have simply replaced the reference to
> table Appointment with a sub-query and aliased it as Appointment. The
> remaining query can be left unmodified. If my guess about ApptDate is
> wrong, then replace the sub-query condition with something more
> appropriate.
> SELECT
> *
> FROM
> (
> select * from dbo.Appointment where ApptDate >= '2003/04/07'
> ) as Appointment
> WHERE
> . . .
> . . .
> . . .
>
> "CD" <mcdye1@.hotmail.REMOVETHIS.com> wrote in message
> news:eYmG3WCIGHA.1132@.TK2MSFTNGP10.phx.gbl...
>|||You have no clustered index. That is likely the biggest problem. Pick a
good candidate (appointment date?) and try the query without changes. You'll
need to expericment (and know how the table is usually queried) to pick the
best one. Not something that we can do for you.
Option 1. Update statistics.
Option 2. Compute this value "(DATEDIFF(mi, CreatedDate, GETDATE()) < 4" as
a variable (e.g., now - 4 minutes) and use that in a direct comparison to
CreatedDate. Then, index created date and update your query. I'm not
going to wade through all of this logic, but it appears that this condition
is common to all. If so, you should remove it from each of the condition
sets, making it a condition for the query as a whole. Do likewise for any
other similar conditions.
Option 3. Index on appointment date.
Option 4. Disable parallel exection (with a maxdop hint). Perhaps
parallelism is the problem. Actually, try this first, and cluster, then try
this again if clustering did not help.
Option 5. Index interface status. Also appears to be a common condition.|||The majority of the processing (56%) is by the Filter operator, which scans
the input and returns those rows that satisfy the filter expression. The
additional 37% used by the table scan could also be improved, if you could
find a way to utilize the indexes.
SQL Server's fallback use of the table scan (rather than the more efficient
index scan) is probably the result of the complex filter conditions.
Microsoft attempts to impove the logic of the query optimizer in service
packs, but it will never be perfect. Try some of these techniques to
optimize the WHERE clause:
SQL Server Transact-SQL WHERE Clause
http://www.sql-server-performance.c...t_sql_where.asp
Also, the query optimizer uses table statistics to determine what indexes to
use. If the statistics are out of date, then this can result in a less than
optimal choice or a fallback to a non-indexed table scan.
http://msdn2.microsoft.com/en-us/library/ms187348.aspx
If the above do not help enough, then consider using index hints with the
select statement to force the optimizer to utilze a specfic index.
http://www.sql-server-performance.com/hints.asp
"CD" <mcdye1@.hotmail.REMOVETHIS.com> wrote in message
news:eBR$dzCIGHA.2696@.TK2MSFTNGP14.phx.gbl...
> Thanks for the reply. The results are the same as without the subquery.
> "JT" <someone@.microsoft.com> wrote in message
> news:ug4YLpCIGHA.916@.TK2MSFTNGP10.phx.gbl...
>|||First, the following logic is repeated in every part of this query (every or
clause) and should appear only once.
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
Moving this out of the nested ors MAY allow the database to filter on these
fields up front, thus reducing your dataset before processing all the ORs.
Second, reformatting this miserable code to group things more logically, and
removing many of the redundant () will make ti much mroe readable and thus
easier to understand. I left some redundant () in place for consistency
reasons, but the following should be much easier to follow.
SELECT *
FROM dbo.Appointment
WHERE
(
(
(
ResourceCode LIKE 'SHC%'
or ResourceCode LIKE 'GM%'
or ResourceCode LIKE 'GI%'
or ResourceCode like 'PUL%'
or ResourceCode like 'CAR%'
or ResourceCode like 'MED%'
)
AND ApptDate >= '04/07/2003'
)
or
(
(
ResourceCode = 'GHCCARAD'
or resourcecode = 'GHCJONES'
)
AND ApptDate >= '01/27/2003'
)
or
(
(
ResourceCode = 'OPCNUTRI'
or resourcecode like 'MON%'
or resourcecode like 'GON%'
or resourcecode like 'LON%'
or resourcecode like 'END%'
)
AND ApptDate >= '07/01/2003'
)
or
(
(
ResourceCode LIKE 'ID%'
)
AND ApptDate >= '08/12/2003'
)
or
(
(
ResourceCode = 'SUREIDTS'
or ResourceCode = 'SUREIDTW'
or ResourceCode = 'VASLAB1'
or ResourceCode = 'VASLAB2'
)
AND ApptDate >= '12/01/2003'
)
or
(
(
ResourceCode = 'PFT'
or ResourceCode = 'PFT2'
)
AND ApptDate >= '1/13/2004'
)
or
(
(
ResourceCode like 'CWC%'
or ResourceCode like 'TEL%'
)
AND ApptDate >= '4/13/2004'
)
or
(
(
ResourceCode like 'CHEM%'
)
AND ApptDate >= '8/13/2004'
)
or
(
(
ResourceCode like 'PREOP%'
)
AND ApptDate >= '2/14/2005'
)
or
(
(
ResourceCode like 'UWHC%'
or ResourceCode like 'REN%'
or ResourceCode like 'SUR%'
)
AND ApptDate >= '3/08/2005'
)
or
(
(
ResourceCode like 'SON%'
or ResourceCode like 'BON%'
or ResourceCode like 'BMO%'
)
AND ApptDate >= '03/14/2005'
)
or
(
(
ResourceCode like 'JEI%'
or ResourceCode like 'NEU%'
or ResourceCode like 'ACRC%'
or ResourceCode like 'DON%'
or ResourceCode like 'HON%'
or ResourceCode like 'JON%'
or ResourceCode like 'UON%'
or ResourceCode like 'ORT%'
)
AND ApptDate >= '04/07/2005'
)
or
(
(
ResourceCode like 'OON%'
)
AND ApptDate >= '5/23/2005'
)
or
(
(
ResourceCode like 'NSG%'
or ResourceCode like 'JTS%'
)
AND ApptDate >= '7/27/2005'
)
or
(
(
ResourceCode like 'URO%'
)
AND ApptDate >= '7/28/2005'
)
or
(
(
ResourceCode like 'LT%'
)
AND ApptDate >= '11/14/2005'
)
or
(
(
ResourceCode = 'CVTOBLE'
or ResourceCode = 'CVANTAK'
)
AND ApptDate >= '10/12/2005'
)
or
(
(
ResourceCode like 'INFCH%'
or ResourceCode like 'INFRM%'
or ResourceCode like 'MICHAIR%'
or ResourceCode like 'MIEXP%'
or ResourceCode like 'MIROOM%'
)
AND ApptDate >= '11/07/2005'
)
)
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
"CD" <mcdye1@.hotmail.REMOVETHIS.com> wrote in message
news:eYmG3WCIGHA.1132@.TK2MSFTNGP10.phx.gbl...
> I am not a programmer but have noticed in this query peaking my processor
> when running. The table has over 2.7 million rows. Is there a better way
> to write the sp for better performance or tweak the table? The excution
> plan shows TableScan 37%>Filter 56%> Sort 4%>pParallelism/Gather Stream
3%>
> Select 0%.
> -- TABLE:
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Appointment]') and OBJECTPROPERTY(id, N'IsUserTable') =
> 1)
> drop table [dbo].[Appointment]
> GO
> CREATE TABLE [dbo].[Appointment] (
> [RecordID] [int] IDENTITY (1, 1) NOT NULL ,
> [CPINumber] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [VisitSuffix] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
> [PatientName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
> ,
> [DOB] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Sex] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Race] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SSN] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ApptLocation] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
> ,
> [ResourceCode] [varchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
> ,
> [ResourceGroup] [varchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [ApptDate] [datetime] NOT NULL ,
> [Duration] [int] NULL ,
> [EncounterID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CreatedBy] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CreatedDate] [datetime] NULL ,
> [LastChangeBy] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [LastChangeDate] [datetime] NULL ,
> [ApptStatus] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
> [InterfaceStatus] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
> ,
> [ActivityType] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Comments] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Comments2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Arrived] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [COLD] [datetime] NULL ,
> [Comments3] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Comments4] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PatientTypeCode] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [FinancialClassCode] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Scraped] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [BaseDate] [datetime] NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Appointment] WITH NOCHECK ADD
> CONSTRAINT [DF_Appointment_Scraped] DEFAULT (0) FOR [Scraped],
> CONSTRAINT [PK_Appointment] PRIMARY KEY NONCLUSTERED
> (
> [RecordID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [IX_ResourceCode] ON [dbo].[Appointment]([ResourceCode])
WITH
> FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [IX_AppointmentCPIDate] ON [dbo].[Appointment]([CPINumber],
> [ApptDate]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [IX_Appointment] ON [dbo].[Appointment]([CPINumber],
> [VisitSuffix], [ActivityType], [ApptStatus]) WITH FILLFACTOR = 90 ON
> [PRIMARY]
> GO
> CREATE INDEX [IX_Appointment_ResourceGroup] ON
> [dbo].[Appointment]([ResourceGroup]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
>
> -- STORED PROCEDURE::
> CREATE Procedure spGetApptsForInterface
> As
> SELECT *
> FROM dbo.Appointment
> WHERE ((ResourceCode LIKE 'SHC%' OR
> ResourceCode LIKE 'GM%' OR
> ResourceCode LIKE 'GI%' or ResourceCode like 'PUL%'
or
> ResourceCode like 'CAR%' or ResourceCode like 'MED%') AND (ApptDate >=
> '04/07/2003') AND (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate,
> GETDATE()) > 4)) OR
> (((ResourceCode = 'GHCCARAD') or (resourcecode =
> 'GHCJONES')) AND (ApptDate >= '01/27/2003') AND (InterfaceStatus = 'R')
AND
> (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) OR
> (((ResourceCode = 'OPCNUTRI') or (resourcecode like
> 'MON%') or (resourcecode like 'GON%') or (resourcecode like 'LON%') or
> (resourcecode like 'END%')) AND (ApptDate >= '07/01/2003') AND
> (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) OR
> (((ResourceCode LIKE 'ID%') AND (ApptDate >= '08/12/2003') AND
> (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) or
> (((ResourceCode = 'SUREIDTS') or (ResourceCode = 'SUREIDTW') or
> (ResourceCode = 'VASLAB1') or (ResourceCode = 'VASLAB2')) AND (ApptDate >=
> '12/01/2003') AND (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate,
> GETDATE()) > 4)) or
> (((ResourceCode = 'PFT') or (ResourceCode = 'PFT2')) AND
(ApptDate
CreatedDate,
> GETDATE()) > 4)) or
> (((ResourceCode like 'CWC%') or (ResourceCode like 'TEL%')) AND
> (ApptDate >= '4/13/2004') AND (InterfaceStatus = 'R') AND (DATEDIFF(mi,
> CreatedDate, GETDATE()) > 4)) or
> (ResourceCode like 'CHEM%' AND ApptDate >= '8/13/2004' AND
> InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (ResourceCode like 'PREOP%' AND ApptDate >= '2/14/2005' AND
> InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (ResourceCode like 'UWHC%' AND ApptDate >=
'3/08/2005'
> AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (ResourceCode like 'REN%' AND ApptDate >=
'3/08/2005'
> AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (ResourceCode like 'SUR%' AND ApptDate >=
'3/08/2005'
> AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (((ResourceCode like 'SON%') or (ResourceCode like
> 'BON%') or (ResourceCode like 'BMO%')) AND (ApptDate >= '03/14/2005') AND
> (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) or
> (((ResourceCode like 'JEI%') or (ResourceCode like
> 'NEU%') or (ResourceCode like 'ACRC%')) AND (ApptDate >= '04/07/2005') AND
> (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) or
> (((ResourceCode like 'DON%') or (ResourceCode like
> 'HON%') or (ResourceCode like 'JON%') or (ResourceCode like 'UON%') or
> (ResourceCode like 'ORT%')) AND (ApptDate >= '04/07/2005') AND
> (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) or
> (ResourceCode like 'OON%' AND ApptDate >=
'5/23/2005'
> AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (ResourceCode like 'NSG%' AND ApptDate >=
'7/27/2005'
> AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (ResourceCode like 'JTS%' AND ApptDate >=
'7/27/2005'
> AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (ResourceCode like 'URO%' AND ApptDate >=
'7/28/2005'
> AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (ResourceCode like 'LT%' AND ApptDate >=
'11/14/2005'
> AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (((ResourceCode = 'CVTOBLE') or (ResourceCode =
> 'CVANTAK')) AND (ApptDate >= '10/12/2005') AND (InterfaceStatus = 'R') AND
> (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) or
> (((ResourceCode like 'INFCH%') or (ResourceCode like
> 'INFRM%')) AND (ApptDate >= '11/07/2005') AND (InterfaceStatus = 'R') AND
> (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) or
> (((ResourceCode like 'MICHAIR%') or (ResourceCode
like
> 'MIEXP%') or (ResourceCode like 'MIROOM%')) AND (ApptDate >= '11/07/2005')
> AND (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate, GETDATE()) >
4)))
>
> ORDER BY ApptDate
> return
> GO
>|||I should note that I consolidate some of the resource code comparisons where
the dates were the same.
Also, you might try changing all those OR clauses to unions, although I am
not certain if SQL Server will handle them the same or not. The idea is
that the unions will be ~ 12 independent queries which may individually
process quite fast, as compared to the ORs which MAY require the database to
process every single row in order to evaluate all conditions.
Not fully understanding the SQL server tuning engine, I cant be sure if
either of these ideas will make any difference at all. Adding the indexes
as suggested by others may make a much bigger difference wihtout changing
the code.
Just in case, here is the SQL as a bunch of unions...
SELECT *
FROM dbo.Appointment
WHERE
(
ResourceCode LIKE 'SHC%'
or ResourceCode LIKE 'GM%'
or ResourceCode LIKE 'GI%'
or ResourceCode like 'PUL%'
or ResourceCode like 'CAR%'
or ResourceCode like 'MED%'
)
AND ApptDate >= '04/07/2003'
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
Union all
SELECT *
FROM dbo.Appointment
WHERE
(
ResourceCode = 'GHCCARAD'
or resourcecode = 'GHCJONES'
)
AND ApptDate >= '01/27/2003'
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
Union all
SELECT *
FROM dbo.Appointment
WHERE
(
ResourceCode = 'OPCNUTRI'
or resourcecode like 'MON%'
or resourcecode like 'GON%'
or resourcecode like 'LON%'
or resourcecode like 'END%'
)
AND ApptDate >= '07/01/2003'
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
Union all
SELECT *
FROM dbo.Appointment
WHERE
(
ResourceCode LIKE 'ID%'
)
AND ApptDate >= '08/12/2003'
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
Union all
SELECT *
FROM dbo.Appointment
WHERE
(
ResourceCode = 'SUREIDTS'
or ResourceCode = 'SUREIDTW'
or ResourceCode = 'VASLAB1'
or ResourceCode = 'VASLAB2'
)
AND ApptDate >= '12/01/2003'
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
Union all
SELECT *
FROM dbo.Appointment
WHERE
(
ResourceCode = 'PFT'
or ResourceCode = 'PFT2'
)
AND ApptDate >= '1/13/2004'
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
Union all
SELECT *
FROM dbo.Appointment
WHERE
(
ResourceCode like 'CWC%'
or ResourceCode like 'TEL%'
)
AND ApptDate >= '4/13/2004'
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
Union all
SELECT *
FROM dbo.Appointment
WHERE
(
ResourceCode like 'CHEM%'
)
AND ApptDate >= '8/13/2004'
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
Union all
SELECT *
FROM dbo.Appointment
WHERE
(
ResourceCode like 'PREOP%'
)
AND ApptDate >= '2/14/2005'
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
Union all
SELECT *
FROM dbo.Appointment
WHERE
(
ResourceCode like 'UWHC%'
or ResourceCode like 'REN%'
or ResourceCode like 'SUR%'
)
AND ApptDate >= '3/08/2005'
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
Union all
SELECT *
FROM dbo.Appointment
WHERE
(
ResourceCode like 'SON%'
or ResourceCode like 'BON%'
or ResourceCode like 'BMO%'
)
AND ApptDate >= '03/14/2005'
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
Union all
SELECT *
FROM dbo.Appointment
WHERE
(
ResourceCode like 'JEI%'
or ResourceCode like 'NEU%'
or ResourceCode like 'ACRC%'
or ResourceCode like 'DON%'
or ResourceCode like 'HON%'
or ResourceCode like 'JON%'
or ResourceCode like 'UON%'
or ResourceCode like 'ORT%'
)
AND ApptDate >= '04/07/2005'
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
Union all
SELECT *
FROM dbo.Appointment
WHERE
(
ResourceCode like 'OON%'
)
AND ApptDate >= '5/23/2005'
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
Union all
SELECT *
FROM dbo.Appointment
WHERE
(
ResourceCode like 'NSG%'
or ResourceCode like 'JTS%'
)
AND ApptDate >= '7/27/2005'
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
Union all
SELECT *
FROM dbo.Appointment
WHERE
(
ResourceCode like 'URO%'
)
AND ApptDate >= '7/28/2005'
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
Union all
SELECT *
FROM dbo.Appointment
WHERE
(
ResourceCode like 'LT%'
)
AND ApptDate >= '11/14/2005'
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
Union all
SELECT *
FROM dbo.Appointment
WHERE
(
ResourceCode = 'CVTOBLE'
or ResourceCode = 'CVANTAK'
)
AND ApptDate >= '10/12/2005'
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
Union all
SELECT *
FROM dbo.Appointment
WHERE
(
ResourceCode like 'INFCH%'
or ResourceCode like 'INFRM%'
or ResourceCode like 'MICHAIR%'
or ResourceCode like 'MIEXP%'
or ResourceCode like 'MIROOM%'
)
AND ApptDate >= '11/07/2005'
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:%23y7yjkDIGHA.2912@.tk2msftngp13.phx.gbl...
> First, the following logic is repeated in every part of this query (every
or
> clause) and should appear only once.
> AND InterfaceStatus = 'R'
> AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
> Moving this out of the nested ors MAY allow the database to filter on
these
> fields up front, thus reducing your dataset before processing all the ORs.
> Second, reformatting this miserable code to group things more logically,
and
> removing many of the redundant () will make ti much mroe readable and thus
> easier to understand. I left some redundant () in place for consistency
> reasons, but the following should be much easier to follow.
> SELECT *
> FROM dbo.Appointment
> WHERE
> (
> (
> (
> ResourceCode LIKE 'SHC%'
> or ResourceCode LIKE 'GM%'
> or ResourceCode LIKE 'GI%'
> or ResourceCode like 'PUL%'
> or ResourceCode like 'CAR%'
> or ResourceCode like 'MED%'
> )
> AND ApptDate >= '04/07/2003'
> )
> or
> (
> (
> ResourceCode = 'GHCCARAD'
> or resourcecode = 'GHCJONES'
> )
> AND ApptDate >= '01/27/2003'
> )
> or
> (
> (
> ResourceCode = 'OPCNUTRI'
> or resourcecode like 'MON%'
> or resourcecode like 'GON%'
> or resourcecode like 'LON%'
> or resourcecode like 'END%'
> )
> AND ApptDate >= '07/01/2003'
> )
> or
> (
> (
> ResourceCode LIKE 'ID%'
> )
> AND ApptDate >= '08/12/2003'
> )
> or
> (
> (
> ResourceCode = 'SUREIDTS'
> or ResourceCode = 'SUREIDTW'
> or ResourceCode = 'VASLAB1'
> or ResourceCode = 'VASLAB2'
> )
> AND ApptDate >= '12/01/2003'
> )
> or
> (
> (
> ResourceCode = 'PFT'
> or ResourceCode = 'PFT2'
> )
> AND ApptDate >= '1/13/2004'
> )
> or
> (
> (
> ResourceCode like 'CWC%'
> or ResourceCode like 'TEL%'
> )
> AND ApptDate >= '4/13/2004'
> )
> or
> (
> (
> ResourceCode like 'CHEM%'
> )
> AND ApptDate >= '8/13/2004'
> )
> or
> (
> (
> ResourceCode like 'PREOP%'
> )
> AND ApptDate >= '2/14/2005'
> )
> or
> (
> (
> ResourceCode like 'UWHC%'
> or ResourceCode like 'REN%'
> or ResourceCode like 'SUR%'
> )
> AND ApptDate >= '3/08/2005'
> )
> or
> (
> (
> ResourceCode like 'SON%'
> or ResourceCode like 'BON%'
> or ResourceCode like 'BMO%'
> )
> AND ApptDate >= '03/14/2005'
> )
> or
> (
> (
> ResourceCode like 'JEI%'
> or ResourceCode like 'NEU%'
> or ResourceCode like 'ACRC%'
> or ResourceCode like 'DON%'
> or ResourceCode like 'HON%'
> or ResourceCode like 'JON%'
> or ResourceCode like 'UON%'
> or ResourceCode like 'ORT%'
> )
> AND ApptDate >= '04/07/2005'
> )
> or
> (
> (
> ResourceCode like 'OON%'
> )
> AND ApptDate >= '5/23/2005'
> )
> or
> (
> (
> ResourceCode like 'NSG%'
> or ResourceCode like 'JTS%'
> )
> AND ApptDate >= '7/27/2005'
> )
> or
> (
> (
> ResourceCode like 'URO%'
> )
> AND ApptDate >= '7/28/2005'
> )
> or
> (
> (
> ResourceCode like 'LT%'
> )
> AND ApptDate >= '11/14/2005'
> )
> or
> (
> (
> ResourceCode = 'CVTOBLE'
> or ResourceCode = 'CVANTAK'
> )
> AND ApptDate >= '10/12/2005'
> )
> or
> (
> (
> ResourceCode like 'INFCH%'
> or ResourceCode like 'INFRM%'
> or ResourceCode like 'MICHAIR%'
> or ResourceCode like 'MIEXP%'
> or ResourceCode like 'MIROOM%'
> )
> AND ApptDate >= '11/07/2005'
> )
> )
> AND InterfaceStatus = 'R'
> AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
>
> "CD" <mcdye1@.hotmail.REMOVETHIS.com> wrote in message
> news:eYmG3WCIGHA.1132@.TK2MSFTNGP10.phx.gbl...
processor
way
> 3%>
=
,
NULL
> ,
> NULL
> NULL
> NULL
NULL
> ,
,
,
NULL
> ,
> NULL
,
NULL
> ,
> WITH
[dbo]. [Appointment]([CPINumber],[color=darkred
]
'PUL%'
> or
> AND
like
OR
or
>=
> (ApptDate
> CreatedDate,
> '3/08/2005'
or
> '3/08/2005'
or
> '3/08/2005'
or
AND
or
AND
or
or
> '5/23/2005'
or
> '7/27/2005'
or
> '7/27/2005'
or
> '7/28/2005'
or
> '11/14/2005'
or
AND
like
AND
> like
'11/07/2005')
> 4)))
>
sql
