Query Syntax: million

Showing posts with label million. Show all posts

Friday, March 30, 2012

Query/table tweaking Help

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

Wednesday, March 7, 2012

Query timeout when rows returned < TOP (n)?

SQLServer 2005, ~7 million records, queries are using a clustered index keyed on the field "date".

Both queries below have the same execution plan, IO Cost, etc but Query 1 takes ~38 seconds whereas Query2 takes ~.3 seconds.

The only difference is in one of the where clauses (point=). It seems to have something to do with the fact that the first query is only returning 66 rows, but I'm at a loss as to why it's so slow. Query 1 is sub 1 second if I do a select top 66, but ~38 seconds with a top 67.

Obviously there's something I'm missing, but I'm completely clueless as to what it is.

Thanks - James

Query 1:

SELECT TOP (100) id, org, zone, point, date, eventType, data, dataName

FROM history

WHERE (org = 'Testbed') AND (zone = 'Reader202') AND (point = 'Antenna 2')

ORDER BY date DESC

37759 ms

66 rows

IO Cost: 188.225

Returns rows 1-61 < 1 second, 62-66 @. ~38 seconds

Query 2:

SELECT TOP (100) id, org, zone, point, date, eventType, data, dataName

FROM history

WHERE (org = 'Testbed') AND (zone = 'Reader202') AND (point = 'Antenna 1')

ORDER BY date DESC

334 ms

100 rows

IO Cost: 188.225

It really depends on how many records there are of Attenna 1 and Antenna 2.

Clearly out of all your records there are only 66 that match Atenna 2 so it probably had to look through every record taking 38 seconds. however, there seem to be a whole lot more Attenna 1 or they were toward the begging of your records. Because it filled the Top 100 you specified. Once that is filled there is no point for the query to keep executing and it popped back after only 3 seconds.

On top of that your index is no on any of the columns in your where clause. An index is not a magic item. You indexes on your WHERE criteria in order for it to take advantage of it.

|||

Query plans are your friend.

Look at the query plan and you will see the difference between both queries.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Yep query plans are the same for both. I've got indices for the other fields also.

The odd thing is with a if I give it a point name that doesn't exist like

SELECT TOP (100) id, org, zone, point, date, eventType, data, dataName

FROM history

WHERE (org = 'Testbed') AND (zone = 'Reader202') AND (point = 'xx')

ORDER BY date DESC

It uses the index for point then hits the clustered index, but the same query with a valid point name

SELECT TOP (100) id, org, zone, point, date, eventType, data, dataName

FROM history

WHERE (org = 'Testbed') AND (zone = 'Reader202') AND (point = 'Antenna 2')

ORDER BY date DESC

it uses only the clustered index. I'm starting to wonder if it might be a problem with Stastics being out of synch for some reason.

Thanks again - James

|||

Query 1 required a scan of 100% of the table. Even after looking at the whole table, only 66 records were returned.

In the second query, it found 100 records very quickly so there was no need to continue.

If you were to remove the "Top 100" from these queries, the execution times would be very similar as both queries would be required to scan the whole table (with this caviat: If Query 2 returne 200,000,000 records, it's going to take longer...the table scan won't take longer to locate the records, but actually reading the disk and moveing the bits will take longer).

Saturday, February 25, 2012

Query taking too long

Hi all.
I have a query that is selecting distinct product and state from a main
table with over 3 million rows. I have an index on state and product, and
used a hint to make it use the index, but it still takes 45 seconds or longer
to return. I need to get this down to less than a couple of seconds.
What is the best way to do this? Will a view help? Or create a lookup table?
Thanks!
SusanWhat kind of index do you have? Do you have 2 seperate indices on each field
or a one composite index on both fields? Is the index clustered? What does
the query and table structure look like?
--
MG
"Susan Cooper" wrote:
> Hi all.
> I have a query that is selecting distinct product and state from a main
> table with over 3 million rows. I have an index on state and product, and
> used a hint to make it use the index, but it still takes 45 seconds or longer
> to return. I need to get this down to less than a couple of seconds.
> What is the best way to do this? Will a view help? Or create a lookup table?
> Thanks!
> Susan|||The index is a composite index of both fields, with State first. It is not
clustered.
The table is:
[dbo].[policy] (
[policy_number] [char] (12) , --PK
[description] [char] (30) ,
[status] [char] (1) ,
[effective_datetime] [datetime] NULL ,
[state] [char] (2) ,
[source] [char] (3) ,
[source_details] [char] (10) ,
[num_of_rated_drivers] [int] NULL ,
[num_of_excluded_drivers] [int] NULL ,
[num_of_non_driver_residents] [int] NULL ,
[num_of_vehicles] [int] NULL ,
[program] [char] (1) ,
[term] [int] NULL ,
[bill_plan_id] [char] (10) ,
[percent_down_payment] [int] NULL ,
[number_of_installments] [int] NULL ,
[down_payment_amount] [int] NULL ,
[payment_method] [char] (1) ,
[premium_amount] [int] NULL ,
[market_code] [char] (12) ,
[agency_code] [char] (10) ,
[producer_code] [char] (10) ,
[local_fee_1] [int] NULL ,
[local_fee_2] [int] NULL ,
[municipal_tax_rate] [int] NULL ,
[datetime_quote_created] [datetime] NULL ,
[created_by] [char] (10) ,
[datetime_last_updated] [datetime] NULL ,
[updated_by] [char] (10) ,
[datetime_uploaded] [datetime] NULL ,
[uploaded_by] [char] (10) ,
[upload_status] [char] (1) ,
[fees] [int] NULL ,
[received_date] [datetime] NULL ,
[processed_date] [datetime] NULL ,
[Credit_Check_Result] [char] (1) ,
[Number_of_Credit_Requests] [int] NULL ,
[Credit_Score_Level] [char] (2) ,
[Score_Provider] [char] (3) ,
[Score_Reference_Number] [int] NULL ,
[Tier_Group] [char] (2) ,
[Tier] [int] NULL ,
[Adverse_Action_Notice] [char] (1) ,
[Product] [int] NULL ,
[Agent_License_Number] [char] (10) ,
[Underwriting_Group] [int] NULL ,
[Number_of_Nonchargeable_Incidents] [int] NULL ,
[Rate_Book_Indicator] [char] (10) ,
[Version_Number] [char] (50) ,
[Pos_status] [char] (1) ,
[POS_token_id] [char] (32) ,
[UDI_ID] [char] (38) ,
[Quote_ID] [char] (32)
The query is:
select distinct state, product
from dbo.policy
WITH (INDEX (state_program))
The purpose of this is that a new application needs all of the products
associated with each state.
Thanks!
"MGeles" wrote:
> What kind of index do you have? Do you have 2 seperate indices on each field
> or a one composite index on both fields? Is the index clustered? What does
> the query and table structure look like?
> --
> MG
>
> "Susan Cooper" wrote:
> > Hi all.
> >
> > I have a query that is selecting distinct product and state from a main
> > table with over 3 million rows. I have an index on state and product, and
> > used a hint to make it use the index, but it still takes 45 seconds or longer
> > to return. I need to get this down to less than a couple of seconds.
> >
> > What is the best way to do this? Will a view help? Or create a lookup table?
> >
> > Thanks!
> > Susan|||Is the query meant to build a list for a dropdown or something like that? If
so I'd probably just use a lookup table containing the distinct possibilities
like you had said originally.
I'm not sure that the query would benefit that much from an index because it
looks like it would need to go through the entire talbe to get teh results
that you want.
Did you recently add the index and get a peformance improvement?
--
MG
"Susan Cooper" wrote:
> The index is a composite index of both fields, with State first. It is not
> clustered.
> The table is:
> [dbo].[policy] (
> [policy_number] [char] (12) , --PK
> [description] [char] (30) ,
> [status] [char] (1) ,
> [effective_datetime] [datetime] NULL ,
> [state] [char] (2) ,
> [source] [char] (3) ,
> [source_details] [char] (10) ,
> [num_of_rated_drivers] [int] NULL ,
> [num_of_excluded_drivers] [int] NULL ,
> [num_of_non_driver_residents] [int] NULL ,
> [num_of_vehicles] [int] NULL ,
> [program] [char] (1) ,
> [term] [int] NULL ,
> [bill_plan_id] [char] (10) ,
> [percent_down_payment] [int] NULL ,
> [number_of_installments] [int] NULL ,
> [down_payment_amount] [int] NULL ,
> [payment_method] [char] (1) ,
> [premium_amount] [int] NULL ,
> [market_code] [char] (12) ,
> [agency_code] [char] (10) ,
> [producer_code] [char] (10) ,
> [local_fee_1] [int] NULL ,
> [local_fee_2] [int] NULL ,
> [municipal_tax_rate] [int] NULL ,
> [datetime_quote_created] [datetime] NULL ,
> [created_by] [char] (10) ,
> [datetime_last_updated] [datetime] NULL ,
> [updated_by] [char] (10) ,
> [datetime_uploaded] [datetime] NULL ,
> [uploaded_by] [char] (10) ,
> [upload_status] [char] (1) ,
> [fees] [int] NULL ,
> [received_date] [datetime] NULL ,
> [processed_date] [datetime] NULL ,
> [Credit_Check_Result] [char] (1) ,
> [Number_of_Credit_Requests] [int] NULL ,
> [Credit_Score_Level] [char] (2) ,
> [Score_Provider] [char] (3) ,
> [Score_Reference_Number] [int] NULL ,
> [Tier_Group] [char] (2) ,
> [Tier] [int] NULL ,
> [Adverse_Action_Notice] [char] (1) ,
> [Product] [int] NULL ,
> [Agent_License_Number] [char] (10) ,
> [Underwriting_Group] [int] NULL ,
> [Number_of_Nonchargeable_Incidents] [int] NULL ,
> [Rate_Book_Indicator] [char] (10) ,
> [Version_Number] [char] (50) ,
> [Pos_status] [char] (1) ,
> [POS_token_id] [char] (32) ,
> [UDI_ID] [char] (38) ,
> [Quote_ID] [char] (32)
> The query is:
> select distinct state, product
> from dbo.policy
> WITH (INDEX (state_program))
> The purpose of this is that a new application needs all of the products
> associated with each state.
> Thanks!
> "MGeles" wrote:
> > What kind of index do you have? Do you have 2 seperate indices on each field
> > or a one composite index on both fields? Is the index clustered? What does
> > the query and table structure look like?
> > --
> > MG
> >
> >
> > "Susan Cooper" wrote:
> >
> > > Hi all.
> > >
> > > I have a query that is selecting distinct product and state from a main
> > > table with over 3 million rows. I have an index on state and product, and
> > > used a hint to make it use the index, but it still takes 45 seconds or longer
> > > to return. I need to get this down to less than a couple of seconds.
> > >
> > > What is the best way to do this? Will a view help? Or create a lookup table?
> > >
> > > Thanks!
> > > Susan|||I think this will be used for a drop down or something similar. I almost
created a lookup table, but then wondered if there was a better way to do
this.
I did recently add the index, and it didn't help things much at all.
Thanks so much for your help.
"MGeles" wrote:
> Is the query meant to build a list for a dropdown or something like that? If
> so I'd probably just use a lookup table containing the distinct possibilities
> like you had said originally.
> I'm not sure that the query would benefit that much from an index because it
> looks like it would need to go through the entire talbe to get teh results
> that you want.
> Did you recently add the index and get a peformance improvement?
> --
> MG
>
> "Susan Cooper" wrote:
> > The index is a composite index of both fields, with State first. It is not
> > clustered.
> >
> > The table is:
> > [dbo].[policy] (
> > [policy_number] [char] (12) , --PK
> > [description] [char] (30) ,
> > [status] [char] (1) ,
> > [effective_datetime] [datetime] NULL ,
> > [state] [char] (2) ,
> > [source] [char] (3) ,
> > [source_details] [char] (10) ,
> > [num_of_rated_drivers] [int] NULL ,
> > [num_of_excluded_drivers] [int] NULL ,
> > [num_of_non_driver_residents] [int] NULL ,
> > [num_of_vehicles] [int] NULL ,
> > [program] [char] (1) ,
> > [term] [int] NULL ,
> > [bill_plan_id] [char] (10) ,
> > [percent_down_payment] [int] NULL ,
> > [number_of_installments] [int] NULL ,
> > [down_payment_amount] [int] NULL ,
> > [payment_method] [char] (1) ,
> > [premium_amount] [int] NULL ,
> > [market_code] [char] (12) ,
> > [agency_code] [char] (10) ,
> > [producer_code] [char] (10) ,
> > [local_fee_1] [int] NULL ,
> > [local_fee_2] [int] NULL ,
> > [municipal_tax_rate] [int] NULL ,
> > [datetime_quote_created] [datetime] NULL ,
> > [created_by] [char] (10) ,
> > [datetime_last_updated] [datetime] NULL ,
> > [updated_by] [char] (10) ,
> > [datetime_uploaded] [datetime] NULL ,
> > [uploaded_by] [char] (10) ,
> > [upload_status] [char] (1) ,
> > [fees] [int] NULL ,
> > [received_date] [datetime] NULL ,
> > [processed_date] [datetime] NULL ,
> > [Credit_Check_Result] [char] (1) ,
> > [Number_of_Credit_Requests] [int] NULL ,
> > [Credit_Score_Level] [char] (2) ,
> > [Score_Provider] [char] (3) ,
> > [Score_Reference_Number] [int] NULL ,
> > [Tier_Group] [char] (2) ,
> > [Tier] [int] NULL ,
> > [Adverse_Action_Notice] [char] (1) ,
> > [Product] [int] NULL ,
> > [Agent_License_Number] [char] (10) ,
> > [Underwriting_Group] [int] NULL ,
> > [Number_of_Nonchargeable_Incidents] [int] NULL ,
> > [Rate_Book_Indicator] [char] (10) ,
> > [Version_Number] [char] (50) ,
> > [Pos_status] [char] (1) ,
> > [POS_token_id] [char] (32) ,
> > [UDI_ID] [char] (38) ,
> > [Quote_ID] [char] (32)
> >
> > The query is:
> > select distinct state, product
> > from dbo.policy
> > WITH (INDEX (state_program))
> >
> > The purpose of this is that a new application needs all of the products
> > associated with each state.
> >
> > Thanks!
> >
> > "MGeles" wrote:
> >
> > > What kind of index do you have? Do you have 2 seperate indices on each field
> > > or a one composite index on both fields? Is the index clustered? What does
> > > the query and table structure look like?
> > > --
> > > MG
> > >
> > >
> > > "Susan Cooper" wrote:
> > >
> > > > Hi all.
> > > >
> > > > I have a query that is selecting distinct product and state from a main
> > > > table with over 3 million rows. I have an index on state and product, and
> > > > used a hint to make it use the index, but it still takes 45 seconds or longer
> > > > to return. I need to get this down to less than a couple of seconds.
> > > >
> > > > What is the best way to do this? Will a view help? Or create a lookup table?
> > > >
> > > > Thanks!
> > > > Susan|||On Tue, 25 Apr 2006 08:34:02 -0700, Susan Cooper wrote:
>The index is a composite index of both fields, with State first. It is not
>clustered.
(snip DDL)
>The query is:
>select distinct state, product
>from dbo.policy
>WITH (INDEX (state_program))
Hi Susan,
Since there is no WHERE clause, this query has to scan the complete
index. With over 3 milion rows, this will take time.
You might consider using an indexed view. This basically precomputes the
information you need and changes the precomputed results every time the
data in the table changes. This will introduce some overhead for changes
to the data in the table, but judging by the name, this is not a table
that gets changed very frequently.
CREATE VIEW ProductsPerState WITH SCHEMABINDING
AS
SELECT state, product, COUNT_BIG(*) AS tally
FROM dbo.policy
GROUP BY state, product
go
CREATE UNIQUE CLUSTERED INDEX ix_ProductsPerState
ON ProductsPerState(state, product)
go
Once the view is created, you can build the dropdown by querying the
ProductsPerState view.
--
Hugo Kornelis, SQL Server MVP

Friday, March 30, 2012

Query/table tweaking Help

Wednesday, March 7, 2012

Query timeout when rows returned < TOP (n)?

Saturday, February 25, 2012

Query taking too long

Query taking too long

Query Syntax

Blog Archive

About Me