Query Syntax: rows

Showing posts with label rows. 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

Query, help with where clause to reduce resultset

I need some help with the @.LanguageId part of this query to exclude/include
rows to be returned.
ctbl_content_rel can contain rows with different language ids, e.g 'dk' and
'nn'.
If rows with the specified @.LanguageId exists, then I want only those rows,
otherwise I want only the 'nn' rows (a fallback mechanism).
Now the query returns all the rows for both the specified @.LanguageId and
those with @.LanguageId = 'nn'. Any ideas?
SELECT distinct CONTENTREL.fk_filegroup, CONTENTREL.languageid
FROM ctbl_content_rel CONTENTREL
JOIN ctbl_content CONTENT ON CONTENTREL.fk_content = CONTENT.pk_content
WHERE CONTENT.pk_content = @.ContentId
AND (CONTENTREL.languageId = @.LanguageId OR CONTENTREL.languageid = 'NN')
/tedHow about this?
SELECT distinct CONTENTREL.fk_filegroup, CONTENTREL.languageid
FROM ctbl_content_rel CONTENTREL
JOIN ctbl_content CONTENT ON CONTENTREL.fk_content = CONTENT.pk_content
WHERE CONTENT.pk_content = @.ContentId
AND CONTENTREL.languageId = CASE WHEN @.LanguageId = '' THEN 'NN' ELSE
@.LanguageId END
HTH,
Pierre
/"\ ASCII Ribbon Campaign
\ /
X Against HTML
/ \ in e-mail & news|||I'm afraid it will not solve the problem. @.LanguageId will always be
something as input parameter. The clue is that I want the 'nn' rows if the
specified 'dk' rows does not exist in the database.
/ted :)
"Pierre Albisser" <pierre.no_spam@.albisser.de> skrev i melding
news:%23079C5GXFHA.4032@.tk2msftngp13.phx.gbl...
> How about this?
> SELECT distinct CONTENTREL.fk_filegroup, CONTENTREL.languageid
> FROM ctbl_content_rel CONTENTREL
> JOIN ctbl_content CONTENT ON CONTENTREL.fk_content = CONTENT.pk_content
> WHERE CONTENT.pk_content = @.ContentId
> AND CONTENTREL.languageId = CASE WHEN @.LanguageId = '' THEN 'NN' ELSE
> @.LanguageId END
> --
> HTH,
> Pierre
> /"\ ASCII Ribbon Campaign
> \ /
> X Against HTML
> / \ in e-mail & news|||Try,
if exists(select * from ctbl_content_rel CONTENTREL JOIN ctbl_content
CONTENT ON CONTENTREL.fk_content = CONTENT.pk_content WHERE
CONTENT.pk_content = @.ContentId AND (CONTENTREL.languageId = @.LanguageId))
select distinct CONTENTREL.fk_filegroup, CONTENTREL.languageid
from ctbl_content_rel CONTENTREL JOIN ctbl_content CONTENT ON
CONTENTREL.fk_content = CONTENT.pk_content WHERE CONTENT.pk_content =
@.ContentId AND (CONTENTREL.languageId = @.LanguageId)
else
select distinct CONTENTREL.fk_filegroup, CONTENTREL.languageid
from ctbl_content_rel CONTENTREL JOIN ctbl_content CONTENT ON
CONTENTREL.fk_content = CONTENT.pk_content WHERE CONTENT.pk_content =
@.ContentId AND (CONTENTREL.languageid = 'NN')
AMB
"ted" wrote:

> I need some help with the @.LanguageId part of this query to exclude/includ
e
> rows to be returned.
> ctbl_content_rel can contain rows with different language ids, e.g 'dk' an
d
> 'nn'.
> If rows with the specified @.LanguageId exists, then I want only those rows
,
> otherwise I want only the 'nn' rows (a fallback mechanism).
> Now the query returns all the rows for both the specified @.LanguageId and
> those with @.LanguageId = 'nn'. Any ideas?
> SELECT distinct CONTENTREL.fk_filegroup, CONTENTREL.languageid
> FROM ctbl_content_rel CONTENTREL
> JOIN ctbl_content CONTENT ON CONTENTREL.fk_content = CONTENT.pk_content
> WHERE CONTENT.pk_content = @.ContentId
> AND (CONTENTREL.languageId = @.LanguageId OR CONTENTREL.languageid = 'NN')
> /ted
>
>|||Hi Ted
Probable you can try this way
SELECT distinct CONTENTREL.fk_filegroup, CONTENTREL.languageid
FROM ctbl_content_rel CONTENTREL
JOIN ctbl_content CONTENT ON CONTENTREL.fk_content = CONTENT.pk_content
WHERE CONTENT.pk_content = @.ContentId
AND CONTENTREL.languageId = ISNULL(@.LanguageId,'NN')
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"ted" wrote:

> I need some help with the @.LanguageId part of this query to exclude/includ
e
> rows to be returned.
> ctbl_content_rel can contain rows with different language ids, e.g 'dk' an
d
> 'nn'.
> If rows with the specified @.LanguageId exists, then I want only those rows
,
> otherwise I want only the 'nn' rows (a fallback mechanism).
> Now the query returns all the rows for both the specified @.LanguageId and
> those with @.LanguageId = 'nn'. Any ideas?
> SELECT distinct CONTENTREL.fk_filegroup, CONTENTREL.languageid
> FROM ctbl_content_rel CONTENTREL
> JOIN ctbl_content CONTENT ON CONTENTREL.fk_content = CONTENT.pk_content
> WHERE CONTENT.pk_content = @.ContentId
> AND (CONTENTREL.languageId = @.LanguageId OR CONTENTREL.languageid = 'NN')
> /ted
>
>|||Thanks :) It seems to be a way to go.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> skrev i melding
news:2E7D1FD3-54AC-4A40-87E4-8EA9EEFF5F6F@.microsoft.com...
> Try,
> if exists(select * from ctbl_content_rel CONTENTREL JOIN ctbl_content
> CONTENT ON CONTENTREL.fk_content = CONTENT.pk_content WHERE
> CONTENT.pk_content = @.ContentId AND (CONTENTREL.languageId = @.LanguageId))
> select distinct CONTENTREL.fk_filegroup, CONTENTREL.languageid
> from ctbl_content_rel CONTENTREL JOIN ctbl_content CONTENT ON
> CONTENTREL.fk_content = CONTENT.pk_content WHERE CONTENT.pk_content =
> @.ContentId AND (CONTENTREL.languageId = @.LanguageId)
> else
> select distinct CONTENTREL.fk_filegroup, CONTENTREL.languageid
> from ctbl_content_rel CONTENTREL JOIN ctbl_content CONTENT ON
> CONTENTREL.fk_content = CONTENT.pk_content WHERE CONTENT.pk_content =
> @.ContentId AND (CONTENTREL.languageid = 'NN')
>
> AMB
> "ted" wrote:
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
Yoiu might also want to start following proper data modeling
conventions and stop using prefixes that tell us HOW something is used
in one table, and come with names that tell us what something is in the
whole data model. A prefix that tells us something is a table in a
language that hsa only one data strucutre is absurd.
See ISO-11179 for the standards. Can I assume that you meant to use
the ISO language codes and not a identifier?|||On Thu, 19 May 2005 14:19:58 +0200, ted wrote:

>I need some help with the @.LanguageId part of this query to exclude/include
>rows to be returned.
>ctbl_content_rel can contain rows with different language ids, e.g 'dk' and
>'nn'.
>If rows with the specified @.LanguageId exists, then I want only those rows,
>otherwise I want only the 'nn' rows (a fallback mechanism).
>Now the query returns all the rows for both the specified @.LanguageId and
>those with @.LanguageId = 'nn'. Any ideas?
>SELECT distinct CONTENTREL.fk_filegroup, CONTENTREL.languageid
>FROM ctbl_content_rel CONTENTREL
>JOIN ctbl_content CONTENT ON CONTENTREL.fk_content = CONTENT.pk_content
>WHERE CONTENT.pk_content = @.ContentId
>AND (CONTENTREL.languageId = @.LanguageId OR CONTENTREL.languageid = 'NN')
>/ted
>
Hi ted,
If you're not afraid of using proprietary SQL that won't port to other
databases, you can use this:
SELECT TOP 1 CONTENTREL.fk_filegroup, CONTENTREL.languageid
FROM ctbl_content_rel CONTENTREL
JOIN ctbl_content CONTENT ON CONTENTREL.fk_content = CONTENT.pk_content
WHERE CONTENT.pk_content = @.ContentId
AND (CONTENTREL.languageId = @.LanguageId OR CONTENTREL.languageid =
'NN')
ORDER BY CASE WHEN CONTENTREL.languageid = 'NN' THEN 2 ELSE 1 END
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql

Query works then later timeouts

We have a query which joins 3 tables where all 3 of the tables have less tha
n 300 rows each.
The query is fine when there is less than 50 users but when it gets over 50
it runs until it timeouts. During this time while it is running and not ret
urning I can do selects on each individual table but if I run the query with
the joins it never returnsWhat is the waittype for that spid during the slow times?
Andrew J. Kelly
SQL Server MVP
"Darin Browne" <anonymous@.discussions.microsoft.com> wrote in message
news:00EDE2AB-4CE0-4014-A0F6-18552B409DB1@.microsoft.com...
> We have a query which joins 3 tables where all 3 of the tables have less
than 300 rows each.
> The query is fine when there is less than 50 users but when it gets over
50 it runs until it timeouts. During this time while it is running and not
returning I can do selects on each individual table but if I run the query
with the joins it never returns.
> We've checked locks, if anything is blocking it, indexes used and
everything is fine during the never ending select.
> Any suggestions on what we could do? We're stumped.
> Thanks.
>

Monday, March 26, 2012

Query window (sql2005) displays only 1000 rows

Hi Guys,

when I run a query , it only displays 1000 rows in the query window. Any one know how to change this setting?

thanks

Sonny

On the [Tools] menu, then [Option], then [Query Execution].

Check the SET ROWCOUNT value. If it is 1000, change to 0 for all rows.

|||

thanks Arnie, that way quick

Sonny

Friday, March 23, 2012

Query using two column names in a table (to find rows near each other)

I have a table called Seats in my database...
CREATE TABLE [dbo].[Seats] (
[SeatSerialNo] [int] IDENTITY (1, 1) NOT NULL ,
[VehicleSerialNo] [int] NOT NULL ,
[RowNo] [smallint] NOT NULL ,
[ColumnNo] [smallint] NOT NULL ,
[SeatNo] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
This table defines the seats that are available for a given VehicleSerialNo
(the typical scenario would be a motor coach or possibly an aircraft). A
seat has a RowNo (how far down the vehicle it is), a ColumnNo (it's position
left to right on the vehicle) and a SeatNo (the actual seat number the
customer is given - e.g. please sit in seat number 40 - these can be numeric
or like aircraft seats numbers 23D etc).
I have another table called Passengers which stores the VehicleSerialNo (the
actual motor coach) and the SeatSerialNo (the seat they are sitting in on
that motor coach). Let's now assume this table contains lots of entries
already specifying where the existing passengers will be sitting.
Example: I now want to add 6 people on this vehicle and automatically
allocate each person a seat. I am trying to figure out whether this
automatic seat selection can be achieved in SQL Server or whether this
should be done on the client side using VB. Ideally you would always like
to make sure all 6 people are sitting on the same part of the motor coach
(unless it is getting full). Would it be possible to construct a query that
would select seats that are near each other based on RowNo and ColumnNo
(excluding SeatSerialNo's that exists in the Passengers table - e.g. no
double booking of a seat)? So I want to return a query that returns the 6
seats the system thinks are best. Can such a query be performed comparing
these column names (RowNo and ColumnNo) finding seats that are near to each
other.
Many thanks,
ChrisC-W
Its hard to suggest without seeing sample data+ relationship+ expected
result.
Why you don't have a primary on the table?
"C-W" <nomailplease@.microsoft.nospam> wrote in message
news:O0cKXJanFHA.3312@.tk2msftngp13.phx.gbl...
>I have a table called Seats in my database...
>
> CREATE TABLE [dbo].[Seats] (
> [SeatSerialNo] [int] IDENTITY (1, 1) NOT NULL ,
> [VehicleSerialNo] [int] NOT NULL ,
> [RowNo] [smallint] NOT NULL ,
> [ColumnNo] [smallint] NOT NULL ,
> [SeatNo] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> This table defines the seats that are available for a given
> VehicleSerialNo (the typical scenario would be a motor coach or possibly
> an aircraft). A seat has a RowNo (how far down the vehicle it is), a
> ColumnNo (it's position left to right on the vehicle) and a SeatNo (the
> actual seat number the customer is given - e.g. please sit in seat number
> 40 - these can be numeric or like aircraft seats numbers 23D etc).
>
> I have another table called Passengers which stores the VehicleSerialNo
> (the actual motor coach) and the SeatSerialNo (the seat they are sitting
> in on that motor coach). Let's now assume this table contains lots of
> entries already specifying where the existing passengers will be sitting.
>
> Example: I now want to add 6 people on this vehicle and automatically
> allocate each person a seat. I am trying to figure out whether this
> automatic seat selection can be achieved in SQL Server or whether this
> should be done on the client side using VB. Ideally you would always like
> to make sure all 6 people are sitting on the same part of the motor coach
> (unless it is getting full). Would it be possible to construct a query
> that would select seats that are near each other based on RowNo and
> ColumnNo (excluding SeatSerialNo's that exists in the Passengers table -
> e.g. no double booking of a seat)? So I want to return a query that
> returns the 6 seats the system thinks are best. Can such a query be
> performed comparing these column names (RowNo and ColumnNo) finding seats
> that are near to each other.
>
> Many thanks,
> Chris
>
>|||Sorry, that's just the way I scripted the table. SeatSerialNo is the
primary key.
I will try and work on some sample data.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uwOTGPanFHA.320@.TK2MSFTNGP09.phx.gbl...
> C-W
> Its hard to suggest without seeing sample data+ relationship+ expected
> result.
> Why you don't have a primary on the table?
>|||On Wed, 10 Aug 2005 12:54:59 +0100, C-W wrote:

>I have a table called Seats in my database...
>
>CREATE TABLE [dbo].[Seats] (
> [SeatSerialNo] [int] IDENTITY (1, 1) NOT NULL ,
> [VehicleSerialNo] [int] NOT NULL ,
> [RowNo] [smallint] NOT NULL ,
> [ColumnNo] [smallint] NOT NULL ,
> [SeatNo] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
>GO
Hi Chris,
You have two good candidate keys in the table without the extra identity
column: (VehicleSerialNo, SeatNo) and (VehicleSerialNo, RowNo,
ColumnNo). My advice would be to drop the SeatSerialNo column, declare
one of the composite candidate keys to be the PRIMARY KEY (probably the
one with SeatNo, but it depends on a lot of factors I don't know) and
define a UNIQUE constraint for the other one.

>I have another table called Passengers which stores the VehicleSerialNo (th
e
>actual motor coach) and the SeatSerialNo (the seat they are sitting in on
>that motor coach).
That's redundant. What if a passenger has VehicleSerialNo 1 and
SeatSerialN0 17, but the row in Seats for SeatSerialNo says it's in
VehicleSerialNo 2?
If you keep SeatSerialNo in Seats and use it to refer to a seat in the
Passengers table, then remove VehicleSerialNo from the Passengers table
(the seat will always be in the same vehicle, regardless of who is
sitting on it). Or, if you drop SeatSerialNo from Seats, store the
combination of VehicleSerialNo and SeatNo in the Passengers table.
(snip)
>So I want to return a query that returns the 6
>seats the system thinks are best. Can such a query be performed comparing
>these column names (RowNo and ColumnNo) finding seats that are near to each
>other.
As Uri said. A repro script that others can run to recreate your test
data and the expected output would make it much easier to help you.
See www.aspfaq.com/5006 for more details and hints.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo,
Once i've generate the script to reproduce this I will explain the table
structure further (and hopefully will all make sense). I tried to reproduce
a simple example before but probably caused more confusion. The Seats table
does not actually contain the VehicleSerialNo. Hopefully all will make
sense when I post my script.
Thanks,
Chris
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:bg3kf15cvqe3r4k8hfo1jnr7uhpf52o3el@.
4ax.com...
> On Wed, 10 Aug 2005 12:54:59 +0100, C-W wrote:
>
> Hi Chris,
> You have two good candidate keys in the table without the extra identity
> column: (VehicleSerialNo, SeatNo) and (VehicleSerialNo, RowNo,
> ColumnNo). My advice would be to drop the SeatSerialNo column, declare
> one of the composite candidate keys to be the PRIMARY KEY (probably the
> one with SeatNo, but it depends on a lot of factors I don't know) and
> define a UNIQUE constraint for the other one.
>
> That's redundant. What if a passenger has VehicleSerialNo 1 and
> SeatSerialN0 17, but the row in Seats for SeatSerialNo says it's in
> VehicleSerialNo 2?
> If you keep SeatSerialNo in Seats and use it to refer to a seat in the
> Passengers table, then remove VehicleSerialNo from the Passengers table
> (the seat will always be in the same vehicle, regardless of who is
> sitting on it). Or, if you drop SeatSerialNo from Seats, store the
> combination of VehicleSerialNo and SeatNo in the Passengers table.
>
> (snip)
> As Uri said. A repro script that others can run to recreate your test
> data and the expected output would make it much easier to help you.
> See www.aspfaq.com/5006 for more details and hints.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Query Tuning.

Hi,
I have the following query which takes 12- 15 seconds to return almost
900,000 rows. I would like the query to return in less than 4 seconds
(1-3). I have added indexes where needed but was not successful. I am
wondering if this query can be rewritten in anyway to respond in lesser
time. Well the question every one will ask is why are you returning so many
rows?. Thats something we cant do away at present but may be later we might.
The query and query plan are as follows:
SELECT cdp.SerialNumber, PB.ProductId , c.ClaimID, cd.ClaimDetailID, 'spif'
AS Program,
cdp.SerialNumber + '-PID-' + convert(varchar,PB.ProductId) As
SerialNumberProductId
FROM dbo.tblClaimDetailProduct cdp (NOLOCK)
INNER JOIN dbo.tblClaimDetailBundle cdb (NOLOCK)
ON cdp.ClaimDetailBundleID = cdb.ClaimDetailBundleID
AND cdb.ClaimDetailBundleStatusID = 1
INNER JOIN dbo.tblClaimDetail cd (NOLOCK)
ON cdb.ClaimDetailID = cd.ClaimDetailID
AND cd.ClaimDetailStatusID = 1
INNER JOIN dbo.tblClaim c (NOLOCK)
ON cd.ClaimID = c.ClaimID
AND c.ClaimStatusID IN (1, 2, 5, 6, 7, 8, 9, 10, 11,12,14)
INNER JOIn dbo.tblProductBundle PB (NOLOCK)
ON PB.ProductBundleId = cdp.ProductBundleId
|--Compute
Scalar(DEFINE:([Expr1006]=[cdp].[SerialNumber]+'-PID-'+Convert([PB].[ProductID])))
|--Hash Match(Inner Join, HASH:([c].[ClaimID])=([cd].[ClaimID]))
|--Index
S

(OBJECT:([HPSpifCentral].[dbo].[tblClaim].[IX_tblClaim_1] AS [c]),
SEEK:([c].[ClaimStatusID]=1 OR [c].[ClaimStatusID]=2 OR
[c].[ClaimStatusID]=5 OR [c].[ClaimStatusID]=6 OR [c].[ClaimStatusID]=7 OR
[c].[ClaimStatusID]=8 OR [c].[Cla
|--Hash Match(Inner Join,
HASH:([cd].[ClaimDetailID])=([cdb].[ClaimDetailID]))
|--Index
Scan(OBJECT:([HPSpifCentral].[dbo].[tblClaimDetail].[IX_tblClaimDetail_CDCS]
AS [cd]), WHERE:([cd].[ClaimDetailStatusID]=1))
|--Hash Match(Inner Join,
HASH:([PB].[ProductBundleID])=([cdp].[ProductBundleID]))
|--Index
Scan(OBJECT:([HPSpifCentral].[dbo].[tblProductBundle].[IX_tblProductBundle8]
AS [PB]))
|--Merge Join(Inner Join,
MERGE:([cdb].[ClaimDetailBundleID])=([cdp].[ClaimDetailBundleID]),
RESIDUAL:([cdp].[ClaimDetailBundleID]=[cdb].[ClaimDetailBundleID]))
|--Index
Scan(OBJECT:([HPSpifCentral].[dbo].[tblClaimDetailBundle].[PK_tblClaimDetailBundle]
AS [cdb]), WHERE:([cdb].[ClaimDetailBundleStatusID]=1) ORDERED FORWARD)
|--Index
Scan(OBJECT:([HPSpifCentral].[dbo].[tblClaimDetailProduct].[IX_tblClaimDetailProduct_SNo]
AS [cdp]), ORDERED FORWARD)
I would be happy to provide the table schemas as well.
Any help is greatly appreciated.
Thanks
MCheck out UPDATE STATISTICS and see if you get a performance boost.
"Sagar" <mmsagar@.hotmail.com> wrote in message
news:eWEVxpTlFHA.3256@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have the following query which takes 12- 15 seconds to return almost
> 900,000 rows. I would like the query to return in less than 4 seconds
> (1-3). I have added indexes where needed but was not successful. I am
> wondering if this query can be rewritten in anyway to respond in lesser
> time. Well the question every one will ask is why are you returning so
> many rows?. Thats something we cant do away at present but may be later we
> might. The query and query plan are as follows:
> SELECT cdp.SerialNumber, PB.ProductId , c.ClaimID, cd.ClaimDetailID,
> 'spif' AS Program,
> cdp.SerialNumber + '-PID-' + convert(varchar,PB.ProductId) As
> SerialNumberProductId
> FROM dbo.tblClaimDetailProduct cdp (NOLOCK)
> INNER JOIN dbo.tblClaimDetailBundle cdb (NOLOCK)
> ON cdp.ClaimDetailBundleID = cdb.ClaimDetailBundleID
> AND cdb.ClaimDetailBundleStatusID = 1
> INNER JOIN dbo.tblClaimDetail cd (NOLOCK)
> ON cdb.ClaimDetailID = cd.ClaimDetailID
> AND cd.ClaimDetailStatusID = 1
> INNER JOIN dbo.tblClaim c (NOLOCK)
> ON cd.ClaimID = c.ClaimID
> AND c.ClaimStatusID IN (1, 2, 5, 6, 7, 8, 9, 10, 11,12,14)
> INNER JOIn dbo.tblProductBundle PB (NOLOCK)
> ON PB.ProductBundleId = cdp.ProductBundleId
>
> |--Compute
> Scalar(DEFINE:([Expr1006]=[cdp].[SerialNumber]+'-PID-'+Convert([PB].[ProductID])))
> |--Hash Match(Inner Join, HASH:([c].[ClaimID])=([cd].[ClaimID]))
> |--Index
> S

(OBJECT:([HPSpifCentral].[dbo].[tblClaim].[IX_tblClaim_1] AS [c]),
> SEEK:([c].[ClaimStatusID]=1 OR [c].[ClaimStatusID]=2 OR
> [c].[ClaimStatusID]=5 OR [c].[ClaimStatusID]=6 OR [c].[ClaimStatusID]=7 OR
> [c].[ClaimStatusID]=8 OR [c].[Cla
> |--Hash Match(Inner Join,
> HASH:([cd].[ClaimDetailID])=([cdb].[ClaimDetailID]))
> |--Index
>
Scan(OBJECT:([HPSpifCentral].[dbo].[tblClaimDetail].[IX_tblClaimDetail_CDCS]
> AS [cd]), WHERE:([cd].[ClaimDetailStatusID]=1))
> |--Hash Match(Inner Join,
> HASH:([PB].[ProductBundleID])=([cdp].[ProductBundleID]))
> |--Index
>
Scan(OBJECT:([HPSpifCentral].[dbo].[tblProductBundle].[IX_tblProductBundle8]
> AS [PB]))
> |--Merge Join(Inner Join,
> MERGE:([cdb].[ClaimDetailBundleID])=([cdp].[ClaimDetailBundleID]),
> RESIDUAL:([cdp].[ClaimDetailBundleID]=[cdb].[ClaimDetailBundleID]))
> |--Index
>
Scan(OBJECT:([HPSpifCentral].[dbo].[tblClaimDetailBundle].[PK_tblClaimDetail
Bundle]
> AS [cdb]), WHERE:([cdb].[ClaimDetailBundleStatusID]=1) ORDERED FORWARD)
> |--Index
>
Scan(OBJECT:([HPSpifCentral].[dbo].[tblClaimDetailProduct].[IX_tblClaimDetai
lProduct_SNo]
> AS [cdp]), ORDERED FORWARD)
>
> I would be happy to provide the table schemas as well.
> Any help is greatly appreciated.
> Thanks
> M
>|||Thanks for all your replies. I appreciate it. Well Here is the table schema:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblClaimDetail_tblClaim]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblClaimDetail] DROP CONSTRAINT
FK_tblClaimDetail_tblClaim
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblClaimInvoice_tblClaim]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblClaimInvoice] DROP CONSTRAINT
FK_tblClaimInvoice_tblClaim
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblClaimNote_tblClaim]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblClaimNote] DROP CONSTRAINT FK_tblClaimNote_tblClaim
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblClaimStatusHistory_tblClaim]') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblClaimStatusHistory] DROP CONSTRAINT
FK_tblClaimStatusHistory_tblClaim
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblUserRequest_tblClaim]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblUserRequest] DROP CONSTRAINT
FK_tblUserRequest_tblClaim
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo]. [FK_tblClaimDetailBundle_tblClaimDetail]
') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblClaimDetailBundle] DROP CONSTRAINT
FK_tblClaimDetailBundle_tblClaimDetail
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo]. [FK_tblClaimDetailProduct_tblClaimDetail
Bundle]') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblClaimDetailProduct] DROP CONSTRAINT
FK_tblClaimDetailProduct_tblClaimDetailB
undle
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo]. [FK_tblClaimDetailProduct_tblProductBund
le]') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblClaimDetailProduct] DROP CONSTRAINT
FK_tblClaimDetailProduct_tblProductBundl
e
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblClaim]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblClaim]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblClaimDetail]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[tblClaimDetail]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblClaimDetailBundle]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblClaimDetailBundle]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblClaimDetailProduct]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblClaimDetailProduct]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblProductBundle]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblProductBundle]
GO
CREATE TABLE [dbo].[tblClaim] (
[ClaimID] [int] IDENTITY (10000, 1) NOT NULL ,
[ClaimDate] [datetime] NOT NULL ,
[PromotionID] [int] NOT NULL ,
[UserID] [int] NOT NULL ,
[UserEmail] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HistoricalOutletID] [int] NULL ,
[HistoricalCompanyID] [int] NULL ,
[ClaimStatusID] [int] NOT NULL ,
[ClaimStatusReason] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ClaimExpirationDate] [datetime] NULL ,
[FaxDate] [datetime] NULL ,
[FaxTime] [int] NULL ,
[PaymentTypeID] [int] NULL ,
[PaymentDate] [datetime] NULL ,
[PaymentNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PaymentAmount] [money] NULL ,
[ClaimSourceTypeID] [int] NULL ,
[BatchID] [int] NULL ,
[ExpiryEmailSentDate] [datetime] NULL ,
[FraudAuditStatusID] [int] NOT NULL ,
[InDepthAudit] [bit] NULL ,
[TicketId] [int] NULL ,
[InsertDate] [datetime] NOT NULL ,
[InsertUser] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[UpdateDate] [datetime] NULL ,
[UpdateUser] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecordStatus] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblClaimDetail] (
[ClaimDetailID] [int] IDENTITY (1, 1) NOT NULL ,
[ClaimID] [int] NOT NULL ,
[BundleID] [int] NOT NULL ,
[ClaimInvoiceID] [int] NULL ,
[BenefitDescription] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[BenefitAmount] [money] NULL ,
[ClaimDetailStatusID] [int] NOT NULL ,
[InsertDate] [datetime] NOT NULL ,
[InsertUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UpdateDate] [datetime] NULL ,
[UpdateUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecordStatus] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblClaimDetailBundle] (
[ClaimDetailBundleID] [int] IDENTITY (1, 1) NOT NULL ,
[ClaimDetailID] [int] NOT NULL ,
[BundleID] [int] NOT NULL ,
[ClaimDetailBundleStatusID] [int] NOT NULL ,
[DeniedReasonID] [int] NULL ,
[InsertDate] [datetime] NOT NULL ,
[InsertUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UpdateDate] [datetime] NULL ,
[UpdateUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecordStatus] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblClaimDetailProduct] (
[ClaimDetailProductID] [int] IDENTITY (1, 1) NOT NULL ,
[ProductBundleID] [int] NOT NULL ,
[ClaimDetailBundleID] [int] NOT NULL ,
[SerialNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BenefitAmount] [money] NULL ,
[InsertDate] [datetime] NOT NULL ,
[InsertUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UpdateDate] [datetime] NULL ,
[UpdateUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecordStatus] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblProductBundle] (
[ProductBundleID] [int] IDENTITY (1, 1) NOT NULL ,
[ProductID] [int] NULL ,
[ProductCode] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[PCProductID] [int] NULL ,
[BundleID] [int] NOT NULL ,
[IsSNRequired] [bit] NOT NULL ,
[BenefitAmount] [money] NULL ,
[InsertDate] [datetime] NOT NULL ,
[InsertUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UpdateDate] [datetime] NULL ,
[UpdateUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecordStatus] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblClaim] WITH NOCHECK ADD
CONSTRAINT [PK_tblClaim] PRIMARY KEY CLUSTERED
(
[ClaimID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [IX_tblClaimDetail] ON
[dbo].[tblClaimDetail]([ClaimID], [BundleID]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
CREATE CLUSTERED INDEX [IX_tblClaimDetailBundle] ON
[dbo].[tblClaimDetailBundle]([ClaimDetailID], [BundleID],
[ClaimDetailBundleStatusID], [DeniedReasonID]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
CREATE CLUSTERED INDEX [IX_tblProductBundle] ON
[dbo].[tblProductBundle]([BundleID], [ProductID]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
ALTER TABLE [dbo].[tblClaim] WITH NOCHECK ADD
CONSTRAINT [DF_tblClaim_PaymentTypeID] DEFAULT (3) FOR [PaymentTypeID],
CONSTRAINT [DF_tblClaim_SourceType] DEFAULT (1) FOR [ClaimSourceTypeID],
CONSTRAINT [DF_tblClaim_FraudAuditStatusID] DEFAULT (0) FOR
[FraudAuditStatusID],
CONSTRAINT [DF_tblClaim_InDepthAudit] DEFAULT (0) FOR [InDepthAudit]
GO
ALTER TABLE [dbo].[tblClaimDetail] WITH NOCHECK ADD
CONSTRAINT [PK_tblClaimDetail] PRIMARY KEY NONCLUSTERED
(
[ClaimDetailID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblClaimDetailBundle] WITH NOCHECK ADD
CONSTRAINT [PK_tblClaimDetailBundle] PRIMARY KEY NONCLUSTERED
(
[ClaimDetailBundleID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblClaimDetailProduct] WITH NOCHECK ADD
CONSTRAINT [PK_tblClaimDetailProduct] PRIMARY KEY NONCLUSTERED
(
[ClaimDetailProductID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblProductBundle] WITH NOCHECK ADD
CONSTRAINT [PK_tblProductBundle] PRIMARY KEY NONCLUSTERED
(
[ProductBundleID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_tblClaim] ON [dbo].[tblClaim]([PromotionID]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_tblClaim_Date] ON [dbo].[tblClaim]([ClaimDate]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [tblClaim6] ON [dbo].[tblClaim]([BatchID], [ClaimID],
[PromotionID], [HistoricalOutletID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [tblClaim_CS] ON [dbo].[tblClaim]([ClaimStatusID],
[PromotionID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_tblClaim_CP] ON [dbo].[tblClaim]([ClaimID],
[PromotionID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_tblClaim_1] ON [dbo].[tblClaim]([ClaimStatusID],
[ClaimID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
/****** The index created by the following statement is for internal use
only. ******/
/****** It is not a real index but exists as statistics only. ******/
if (@.@.microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [hind_1714821171_3A_1A] ON [dbo].[tblClaim]
([PromotionID], [ClaimID]) ')
GO
/****** The index created by the following statement is for internal use
only. ******/
/****** It is not a real index but exists as statistics only. ******/
if (@.@.microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [hind_165575628_1A_2A_6A_7A] ON
[dbo].[tblClaimDetail] ([ClaimDetailID], [ClaimID], [BenefitAmount],
[ClaimDetailStatusID]) ')
GO
CREATE INDEX [tblClaimDetail_ClaimDetail] ON
[dbo].[tblClaimDetail]([ClaimDetailID], [ClaimID], [BenefitAmount],
[ClaimDetailStatusID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [tblClaimDetai_BAmount] ON [dbo].[tblClaimDetail]([ClaimID],
[ClaimDetailStatusID], [BenefitAmount]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
/****** The index created by the following statement is for internal use
only. ******/
/****** It is not a real index but exists as statistics only. ******/
if (@.@.microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [hind_165575628_1A_2A_7A] ON [dbo].[tblClaimDetail]
([ClaimDetailID], [ClaimID], [ClaimDetailStatusID]) ')
GO
CREATE INDEX [IX_tblClaimDetail_CCD] ON [dbo].[tblClaimDetail]([ClaimID],
[ClaimDetailID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_tblClaimDetail_CDCS] ON
[dbo].[tblClaimDetail]([ClaimDetailID], [ClaimDetailStatusID]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
/****** The index created by the following statement is for internal use
only. ******/
/****** It is not a real index but exists as statistics only. ******/
if (@.@.microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [hind_165575628_1A_7A_2A] ON [dbo].[tblClaimDetail]
([ClaimDetailID], [ClaimDetailStatusID], [ClaimID]) ')
GO
/****** The index created by the following statement is for internal use
only. ******/
/****** It is not a real index but exists as statistics only. ******/
if (@.@.microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [hind_165575628_1A_2A_7A_6A] ON
[dbo].[tblClaimDetail] ([ClaimDetailID], [ClaimID], [ClaimDetailStatusID],
[BenefitAmount]) ')
GO
CREATE INDEX [IX_tblClaimDetailBundle_NCX] ON
[dbo]. [tblClaimDetailBundle]([ClaimDetailBundl
eStatusID], [ClaimDetailID],
[DeniedReasonID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_tblClaimDetailBundle_CDB] ON
[dbo]. [tblClaimDetailBundle]([ClaimDetailBundl
eID], [ClaimDetailID],
[ClaimDetailBundleStatusID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_tblClaimDetailBundle_CDIT] ON
[dbo].[tblClaimDetailBundle]([ClaimDetailID]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
CREATE INDEX [tblClaimDetailBundle13] ON
[dbo]. [tblClaimDetailBundle]([ClaimDetailBundl
eStatusID]) WITH FILLFACTOR =
90 ON [PRIMARY]
GO
/****** The index created by the following statement is for internal use
only. ******/
/****** It is not a real index but exists as statistics only. ******/
if (@.@.microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [hind_181575685_2A_4A_5A] ON
[dbo].[tblClaimDetailBundle] ([ClaimDetailID], [ClaimDetailBundleStatusID],
[DeniedReasonID]) ')
GO
CREATE INDEX [IX_tblClaimDetailProduct_1] ON
[dbo]. [tblClaimDetailProduct]([ClaimDetailBund
leID], [ProductBundleID]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_tblClaimDetailProduct] ON
[dbo].[tblClaimDetailProduct]([SerialNumber]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
CREATE INDEX [IX_tblClaimDetailProduct_SNo] ON
[dbo]. [tblClaimDetailProduct]([ClaimDetailBund
leID], [ProductBundleID],
[SerialNumber]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_tblClaimDetailProduct3] ON
[dbo]. [tblClaimDetailProduct]([ProductBundleID
], [SerialNumber]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
/****** The index created by the following statement is for internal use
only. ******/
/****** It is not a real index but exists as statistics only. ******/
if (@.@.microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [hind_1627152842_1A_2A] ON [dbo].[tblProductBundle]
([ProductBundleID], [ProductID]) ')
GO
CREATE INDEX [IX_tblProductBundle8] ON
[dbo].[tblProductBundle]([ProductBundleID], [ProductID]) WITH FILLFACTOR =
90 ON [PRIMARY]
GO
setuser
GO
EXEC sp_bindefault N'[dbo].[LastModified]', N'[tblClaim].[InsertDate]'
GO
EXEC sp_bindefault N'[dbo].[LastModifiedBy]', N'[tblClaim].[InsertUser]'
GO
EXEC sp_bindefault N'[dbo].[RecordStatus]', N'[tblClaim].[RecordStatus]'
GO
EXEC sp_bindefault N'[dbo].[LastModified]', N'[tblClaim].[UpdateDate]'
GO
EXEC sp_bindefault N'[dbo].[LastModifiedBy]', N'[tblClaim].[UpdateUser]'
GO
setuser
GO
setuser
GO
EXEC sp_bindefault N'[dbo].[LastModified]', N'[tblClaimDetail].[InsertDate]'
GO
EXEC sp_bindefault N'[dbo].[LastModifiedBy]',
N'[tblClaimDetail].[InsertUser]'
GO
EXEC sp_bindefault N'[dbo].[RecordStatus]',
N'[tblClaimDetail].[RecordStatus]'
GO
EXEC sp_bindefault N'[dbo].[LastModified]', N'[tblClaimDetail].[UpdateDate]'
GO
EXEC sp_bindefault N'[dbo].[LastModifiedBy]',
N'[tblClaimDetail].[UpdateUser]'
GO
setuser
GO
setuser
GO
EXEC sp_bindefault N'[dbo].[LastModified]',
N'[tblClaimDetailBundle].[InsertDate]'
GO
EXEC sp_bindefault N'[dbo].[LastModifiedBy]',
N'[tblClaimDetailBundle].[InsertUser]'
GO
EXEC sp_bindefault N'[dbo].[RecordStatus]',
N'[tblClaimDetailBundle].[RecordStatus]'
GO
EXEC sp_bindefault N'[dbo].[LastModified]',
N'[tblClaimDetailBundle].[UpdateDate]'
GO
EXEC sp_bindefault N'[dbo].[LastModifiedBy]',
N'[tblClaimDetailBundle].[UpdateUser]'
GO
setuser
GO
setuser
GO
EXEC sp_bindefault N'[dbo].[LastModified]',
N'[tblClaimDetailProduct].[InsertDate]'
GO
EXEC sp_bindefault N'[dbo].[LastModifiedBy]',
N'[tblClaimDetailProduct].[InsertUser]'
GO
EXEC sp_bindefault N'[dbo].[RecordStatus]',
N'[tblClaimDetailProduct].[RecordStatus]'
GO
EXEC sp_bindefault N'[dbo].[LastModified]',
N'[tblClaimDetailProduct].[UpdateDate]'
GO
EXEC sp_bindefault N'[dbo].[LastModifiedBy]',
N'[tblClaimDetailProduct].[UpdateUser]'
GO
setuser
GO
setuser
GO
EXEC sp_bindefault N'[dbo].[LastModified]',
N'[tblProductBundle].[InsertDate]'
GO
EXEC sp_bindefault N'[dbo].[LastModifiedBy]',
N'[tblProductBundle].[InsertUser]'
GO
EXEC sp_bindefault N'[dbo].[RecordStatus]',
N'[tblProductBundle].[RecordStatus]'
GO
EXEC sp_bindefault N'[dbo].[LastModified]',
N'[tblProductBundle].[UpdateDate]'
GO
EXEC sp_bindefault N'[dbo].[LastModifiedBy]',
N'[tblProductBundle].[UpdateUser]'
GO
setuser
GO
ALTER TABLE [dbo].[tblClaimDetail] ADD
CONSTRAINT [FK_tblClaimDetail_tblBundle] FOREIGN KEY
(
[BundleID]
) REFERENCES [dbo].[tblBundle] (
[BundleID]
),
CONSTRAINT [FK_tblClaimDetail_tblClaim] FOREIGN KEY
(
[ClaimID]
) REFERENCES [dbo].[tblClaim] (
[ClaimID]
),
CONSTRAINT [FK_tblClaimDetail_tblClaimInvoice] FOREIGN KEY
(
[ClaimInvoiceID]
) REFERENCES [dbo].[tblClaimInvoice] (
[ClaimInvoiceID]
),
CONSTRAINT [FK_tblClaimDetail_tblLUClaimDetailStatu
s] FOREIGN KEY
(
[ClaimDetailStatusID]
) REFERENCES [dbo].[tblLUClaimDetailStatus] (
[ClaimDetailStatusID]
)
GO
ALTER TABLE [dbo].[tblClaimDetailBundle] ADD
CONSTRAINT [FK_tblClaimDetailBundle_tblBundle] FOREIGN KEY
(
[BundleID]
) REFERENCES [dbo].[tblBundle] (
[BundleID]
),
CONSTRAINT [FK_tblClaimDetailBundle_tblClaimDetail]
FOREIGN KEY
(
[ClaimDetailID]
) REFERENCES [dbo].[tblClaimDetail] (
[ClaimDetailID]
),
CONSTRAINT [FK_tblClaimDetailBundle_tblLUClaimDetai
lStatus] FOREIGN KEY
(
[ClaimDetailBundleStatusID]
) REFERENCES [dbo].[tblLUClaimDetailStatus] (
[ClaimDetailStatusID]
)
GO
ALTER TABLE [dbo].[tblClaimDetailProduct] ADD
CONSTRAINT [FK_tblClaimDetailProduct_tblClaimDetail
Bundle] FOREIGN KEY
(
[ClaimDetailBundleID]
) REFERENCES [dbo].[tblClaimDetailBundle] (
[ClaimDetailBundleID]
),
CONSTRAINT [FK_tblClaimDetailProduct_tblProductBund
le] FOREIGN KEY
(
[ProductBundleID]
) REFERENCES [dbo].[tblProductBundle] (
[ProductBundleID]
)
GO
ALTER TABLE [dbo].[tblProductBundle] ADD
CONSTRAINT [FK_tblProductBundle_tblBundle] FOREIGN KEY
(
[BundleID]
) REFERENCES [dbo].[tblBundle] (
[BundleID]
)
GO
As far as the other questions, I did update statistics and it did not give
me a performance boot. I also did DBCC DBREINDEX which did not help me
either. Mowgli, I cant answer your question at this time since this is the
only table I have 900,000 rows. others are like 1000 rows and they do return
data in less than a second (milli seconds). I have also verified that the
problem is the query itself taking time and not communication. When I do a
profiler trace i see most of the time is spent processing the query .(i did
insert into temp as well but the insert is longer because the 900,00 rows
returned and inserted is taking time). After the insert is done. the select
* from temp is faster.
Any other things i need to look into?.
Thanks
M|||Sagar
I would like to know (if possible) how long the much simpler query
below takes to return the data...
SELECT TOP 900000
cdp.SerialNumber
, cdp.ClaimDetailBundleID
, cdp.ProductBundleId
, 'spif' AS Program
, cdp.SerialNumber + '-PID-' AS SerialNumberProductId
FROM dbo.tblClaimDetailProduct cdp
If this takes longer than 4 seconds then I think that you may have to
revise your performance expectations.|||Why aren't the clustered indexes on the primary keys? The key of a
clustered index should be as small as possible. In SQL Server 2000, if a
table has a clustered index, then instead of record pointers, nonclustered
indexes contain the key value from the clustered index. (Record pointers
are only used if the table is a heap.) Thus, the clustered index (if it
exists) is always used to locate a row. This differs from previous versions
of SQL Server. This means that every join of a table that has a
nonclustered primary key requires an additional index s

per row. This is
documented in BOL. I suggest you read up on it, create a testing database
with clustered primary keys, and try it out. I think you'll be pleasantly
surprised.
"Sagar" <mmsagar@.hotmail.com> wrote in message
news:eUWSqTVlFHA.3756@.TK2MSFTNGP15.phx.gbl...
> Thanks for all your replies. I appreciate it. Well Here is the table
schema:
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_tblClaimDetail_tblClaim]') and OBJECTPROPERTY(id,
> N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[tblClaimDetail] DROP CONSTRAINT
> FK_tblClaimDetail_tblClaim
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_tblClaimInvoice_tblClaim]') and OBJECTPROPERTY(id,
> N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[tblClaimInvoice] DROP CONSTRAINT
> FK_tblClaimInvoice_tblClaim
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_tblClaimNote_tblClaim]') and OBJECTPROPERTY(id,
> N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[tblClaimNote] DROP CONSTRAINT FK_tblClaimNote_tblClaim
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_tblClaimStatusHistory_tblClaim]') and
> OBJECTPROPERTY(id, N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[tblClaimStatusHistory] DROP CONSTRAINT
> FK_tblClaimStatusHistory_tblClaim
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_tblUserRequest_tblClaim]') and OBJECTPROPERTY(id,
> N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[tblUserRequest] DROP CONSTRAINT
> FK_tblUserRequest_tblClaim
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo]. [FK_tblClaimDetailBundle_tblClaimDetail]
') and
> OBJECTPROPERTY(id, N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[tblClaimDetailBundle] DROP CONSTRAINT
> FK_tblClaimDetailBundle_tblClaimDetail
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo]. [FK_tblClaimDetailProduct_tblClaimDetail
Bundle]') and
> OBJECTPROPERTY(id, N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[tblClaimDetailProduct] DROP CONSTRAINT
> FK_tblClaimDetailProduct_tblClaimDetailB
undle
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo]. [FK_tblClaimDetailProduct_tblProductBund
le]') and
> OBJECTPROPERTY(id, N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[tblClaimDetailProduct] DROP CONSTRAINT
> FK_tblClaimDetailProduct_tblProductBundl
e
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblClaim]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[tblClaim]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblClaimDetail]') and OBJECTPROPERTY(id,
N'IsUserTable')
> = 1)
> drop table [dbo].[tblClaimDetail]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblClaimDetailBundle]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[tblClaimDetailBundle]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblClaimDetailProduct]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[tblClaimDetailProduct]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblProductBundle]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[tblProductBundle]
> GO
> CREATE TABLE [dbo].[tblClaim] (
> [ClaimID] [int] IDENTITY (10000, 1) NOT NULL ,
> [ClaimDate] [datetime] NOT NULL ,
> [PromotionID] [int] NOT NULL ,
> [UserID] [int] NOT NULL ,
> [UserEmail] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [HistoricalOutletID] [int] NULL ,
> [HistoricalCompanyID] [int] NULL ,
> [ClaimStatusID] [int] NOT NULL ,
> [ClaimStatusReason] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [ClaimExpirationDate] [datetime] NULL ,
> [FaxDate] [datetime] NULL ,
> [FaxTime] [int] NULL ,
> [PaymentTypeID] [int] NULL ,
> [PaymentDate] [datetime] NULL ,
> [PaymentNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [PaymentAmount] [money] NULL ,
> [ClaimSourceTypeID] [int] NULL ,
> [BatchID] [int] NULL ,
> [ExpiryEmailSentDate] [datetime] NULL ,
> [FraudAuditStatusID] [int] NOT NULL ,
> [InDepthAudit] [bit] NULL ,
> [TicketId] [int] NULL ,
> [InsertDate] [datetime] NOT NULL ,
> [InsertUser] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
> ,
> [UpdateDate] [datetime] NULL ,
> [UpdateUser] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [RecordStatus] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[tblClaimDetail] (
> [ClaimDetailID] [int] IDENTITY (1, 1) NOT NULL ,
> [ClaimID] [int] NOT NULL ,
> [BundleID] [int] NOT NULL ,
> [ClaimInvoiceID] [int] NULL ,
> [BenefitDescription] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [BenefitAmount] [money] NULL ,
> [ClaimDetailStatusID] [int] NOT NULL ,
> [InsertDate] [datetime] NOT NULL ,
> [InsertUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
> [UpdateDate] [datetime] NULL ,
> [UpdateUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [RecordStatus] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[tblClaimDetailBundle] (
> [ClaimDetailBundleID] [int] IDENTITY (1, 1) NOT NULL ,
> [ClaimDetailID] [int] NOT NULL ,
> [BundleID] [int] NOT NULL ,
> [ClaimDetailBundleStatusID] [int] NOT NULL ,
> [DeniedReasonID] [int] NULL ,
> [InsertDate] [datetime] NOT NULL ,
> [InsertUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
> [UpdateDate] [datetime] NULL ,
> [UpdateUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [RecordStatus] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[tblClaimDetailProduct] (
> [ClaimDetailProductID] [int] IDENTITY (1, 1) NOT NULL ,
> [ProductBundleID] [int] NOT NULL ,
> [ClaimDetailBundleID] [int] NOT NULL ,
> [SerialNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [BenefitAmount] [money] NULL ,
> [InsertDate] [datetime] NOT NULL ,
> [InsertUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
> [UpdateDate] [datetime] NULL ,
> [UpdateUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [RecordStatus] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[tblProductBundle] (
> [ProductBundleID] [int] IDENTITY (1, 1) NOT NULL ,
> [ProductID] [int] NULL ,
> [ProductCode] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
> ,
> [PCProductID] [int] NULL ,
> [BundleID] [int] NOT NULL ,
> [IsSNRequired] [bit] NOT NULL ,
> [BenefitAmount] [money] NULL ,
> [InsertDate] [datetime] NOT NULL ,
> [InsertUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
> [UpdateDate] [datetime] NULL ,
> [UpdateUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [RecordStatus] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tblClaim] WITH NOCHECK ADD
> CONSTRAINT [PK_tblClaim] PRIMARY KEY CLUSTERED
> (
> [ClaimID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE CLUSTERED INDEX [IX_tblClaimDetail] ON
> [dbo].[tblClaimDetail]([ClaimID], [BundleID]) WITH FILLFACTOR = 90 ON
> [PRIMARY]
> GO
> CREATE CLUSTERED INDEX [IX_tblClaimDetailBundle] ON
> [dbo].[tblClaimDetailBundle]([ClaimDetailID], [BundleID],
> [ClaimDetailBundleStatusID], [DeniedReasonID]) WITH FILLFACTOR = 90 ON
> [PRIMARY]
> GO
> CREATE CLUSTERED INDEX [IX_tblProductBundle] ON
> [dbo].[tblProductBundle]([BundleID], [ProductID]) WITH FILLFACTOR = 90 ON
> [PRIMARY]
> GO
> ALTER TABLE [dbo].[tblClaim] WITH NOCHECK ADD
> CONSTRAINT [DF_tblClaim_PaymentTypeID] DEFAULT (3) FOR [PaymentTypeID],
> CONSTRAINT [DF_tblClaim_SourceType] DEFAULT (1) FOR [ClaimSourceTypeID],
> CONSTRAINT [DF_tblClaim_FraudAuditStatusID] DEFAULT (0) FOR
> [FraudAuditStatusID],
> CONSTRAINT [DF_tblClaim_InDepthAudit] DEFAULT (0) FOR [InDepthAudit]
> GO
> ALTER TABLE [dbo].[tblClaimDetail] WITH NOCHECK ADD
> CONSTRAINT [PK_tblClaimDetail] PRIMARY KEY NONCLUSTERED
> (
> [ClaimDetailID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tblClaimDetailBundle] WITH NOCHECK ADD
> CONSTRAINT [PK_tblClaimDetailBundle] PRIMARY KEY NONCLUSTERED
> (
> [ClaimDetailBundleID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tblClaimDetailProduct] WITH NOCHECK ADD
> CONSTRAINT [PK_tblClaimDetailProduct] PRIMARY KEY NONCLUSTERED
> (
> [ClaimDetailProductID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tblProductBundle] WITH NOCHECK ADD
> CONSTRAINT [PK_tblProductBundle] PRIMARY KEY NONCLUSTERED
> (
> [ProductBundleID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [IX_tblClaim] ON [dbo].[tblClaim]([PromotionID]) WITH
> FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [IX_tblClaim_Date] ON [dbo].[tblClaim]([ClaimDate]) WITH
> FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [tblClaim6] ON [dbo].[tblClaim]([BatchID], [ClaimID],
> [PromotionID], [HistoricalOutletID]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [tblClaim_CS] ON [dbo].[tblClaim]([ClaimStatusID],
> [PromotionID]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [IX_tblClaim_CP] ON [dbo].[tblClaim]([ClaimID],
> [PromotionID]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [IX_tblClaim_1] ON [dbo].[tblClaim]([ClaimStatusID],
> [ClaimID]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> /****** The index created by the following statement is for internal use
> only. ******/
> /****** It is not a real index but exists as statistics only. ******/
> if (@.@.microsoftversion > 0x07000000 )
> EXEC ('CREATE STATISTICS [hind_1714821171_3A_1A] ON [dbo].[tblClaim]
> ([PromotionID], [ClaimID]) ')
> GO
> /****** The index created by the following statement is for internal use
> only. ******/
> /****** It is not a real index but exists as statistics only. ******/
> if (@.@.microsoftversion > 0x07000000 )
> EXEC ('CREATE STATISTICS [hind_165575628_1A_2A_6A_7A] ON
> [dbo].[tblClaimDetail] ([ClaimDetailID], [ClaimID], [BenefitAmount],
> [ClaimDetailStatusID]) ')
> GO
> CREATE INDEX [tblClaimDetail_ClaimDetail] ON
> [dbo].[tblClaimDetail]([ClaimDetailID], [ClaimID], [BenefitAmount],
> [ClaimDetailStatusID]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [tblClaimDetai_BAmount] ON
[dbo]. [tblClaimDetail]([ClaimID],d">
> [ClaimDetailStatusID], [BenefitAmount]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> /****** The index created by the following statement is for internal use
> only. ******/
> /****** It is not a real index but exists as statistics only. ******/
> if (@.@.microsoftversion > 0x07000000 )
> EXEC ('CREATE STATISTICS [hind_165575628_1A_2A_7A] ON
[dbo].[tblClaimDetail]
> ([ClaimDetailID], [ClaimID], [ClaimDetailStatusID]) ')
> GO
> CREATE INDEX [IX_tblClaimDetail_CCD] ON
[dbo]. [tblClaimDetail]([ClaimID],d">
> [ClaimDetailID]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [IX_tblClaimDetail_CDCS] ON
> [dbo].[tblClaimDetail]([ClaimDetailID], [ClaimDetailStatusID]) WITH
> FILLFACTOR = 90 ON [PRIMARY]
> GO
> /****** The index created by the following statement is for internal use
> only. ******/
> /****** It is not a real index but exists as statistics only. ******/
> if (@.@.microsoftversion > 0x07000000 )
> EXEC ('CREATE STATISTICS [hind_165575628_1A_7A_2A] ON
[dbo].[tblClaimDetail]
> ([ClaimDetailID], [ClaimDetailStatusID], [ClaimID]) ')
> GO
> /****** The index created by the following statement is for internal use
> only. ******/
> /****** It is not a real index but exists as statistics only. ******/
> if (@.@.microsoftversion > 0x07000000 )
> EXEC ('CREATE STATISTICS [hind_165575628_1A_2A_7A_6A] ON
> [dbo].[tblClaimDetail] ([ClaimDetailID], [ClaimID], [ClaimDetailStatusID],
> [BenefitAmount]) ')
> GO
> CREATE INDEX [IX_tblClaimDetailBundle_NCX] ON
> [dbo]. [tblClaimDetailBundle]([ClaimDetailBundl
eStatusID], [ClaimDetailID],
> [DeniedReasonID]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [IX_tblClaimDetailBundle_CDB] ON
> [dbo]. [tblClaimDetailBundle]([ClaimDetailBundl
eID], [ClaimDetailID],
> [ClaimDetailBundleStatusID]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [IX_tblClaimDetailBundle_CDIT] ON
> [dbo].[tblClaimDetailBundle]([ClaimDetailID]) WITH FILLFACTOR = 90 ON
> [PRIMARY]
> GO
> CREATE INDEX [tblClaimDetailBundle13] ON
> [dbo]. [tblClaimDetailBundle]([ClaimDetailBundl
eStatusID]) WITH FILLFACTOR
=
> 90 ON [PRIMARY]
> GO
> /****** The index created by the following statement is for internal use
> only. ******/
> /****** It is not a real index but exists as statistics only. ******/
> if (@.@.microsoftversion > 0x07000000 )
> EXEC ('CREATE STATISTICS [hind_181575685_2A_4A_5A] ON
> [dbo].[tblClaimDetailBundle] ([ClaimDetailID],
[ClaimDetailBundleStatusID],ed">
> [DeniedReasonID]) ')
> GO
> CREATE INDEX [IX_tblClaimDetailProduct_1] ON
> [dbo]. [tblClaimDetailProduct]([ClaimDetailBund
leID], [ProductBundleID])
WITH
> FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [IX_tblClaimDetailProduct] ON
> [dbo].[tblClaimDetailProduct]([SerialNumber]) WITH FILLFACTOR = 90 ON
> [PRIMARY]
> GO
> CREATE INDEX [IX_tblClaimDetailProduct_SNo] ON
> [dbo]. [tblClaimDetailProduct]([ClaimDetailBund
leID], [ProductBundleID],
> [SerialNumber]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [IX_tblClaimDetailProduct3] ON
> [dbo]. [tblClaimDetailProduct]([ProductBundleID
], [SerialNumber]) WITH
> FILLFACTOR = 90 ON [PRIMARY]
> GO
> /****** The index created by the following statement is for internal use
> only. ******/
> /****** It is not a real index but exists as statistics only. ******/
> if (@.@.microsoftversion > 0x07000000 )
> EXEC ('CREATE STATISTICS [hind_1627152842_1A_2A] ON
[dbo].[tblProductBundle]
> ([ProductBundleID], [ProductID]) ')
> GO
> CREATE INDEX [IX_tblProductBundle8] ON
> [dbo].[tblProductBundle]([ProductBundleID], [ProductID]) WITH FILLFACTOR
=
> 90 ON [PRIMARY]
> GO
> setuser
> GO
> EXEC sp_bindefault N'[dbo].[LastModified]', N'[tblClaim].[InsertDate]'
> GO
> EXEC sp_bindefault N'[dbo].[LastModifiedBy]', N'[tblClaim].[InsertUser]'
> GO
> EXEC sp_bindefault N'[dbo].[RecordStatus]', N'[tblClaim].[RecordStatus]'
> GO
> EXEC sp_bindefault N'[dbo].[LastModified]', N'[tblClaim].[UpdateDate]'
> GO
> EXEC sp_bindefault N'[dbo].[LastModifiedBy]', N'[tblClaim].[UpdateUser]'
> GO
> setuser
> GO
> setuser
> GO
> EXEC sp_bindefault N'[dbo].[LastModified]',
N'[tblClaimDetail].[InsertDate]'
> GO
> EXEC sp_bindefault N'[dbo].[LastModifiedBy]',
> N'[tblClaimDetail].[InsertUser]'
> GO
> EXEC sp_bindefault N'[dbo].[RecordStatus]',
> N'[tblClaimDetail].[RecordStatus]'
> GO
> EXEC sp_bindefault N'[dbo].[LastModified]',
N'[tblClaimDetail].[UpdateDate]'
> GO
> EXEC sp_bindefault N'[dbo].[LastModifiedBy]',
> N'[tblClaimDetail].[UpdateUser]'
> GO
> setuser
> GO
> setuser
> GO
> EXEC sp_bindefault N'[dbo].[LastModified]',
> N'[tblClaimDetailBundle].[InsertDate]'
> GO
> EXEC sp_bindefault N'[dbo].[LastModifiedBy]',
> N'[tblClaimDetailBundle].[InsertUser]'
> GO
> EXEC sp_bindefault N'[dbo].[RecordStatus]',
> N'[tblClaimDetailBundle].[RecordStatus]'
> GO
> EXEC sp_bindefault N'[dbo].[LastModified]',
> N'[tblClaimDetailBundle].[UpdateDate]'
> GO
> EXEC sp_bindefault N'[dbo].[LastModifiedBy]',
> N'[tblClaimDetailBundle].[UpdateUser]'
> GO
> setuser
> GO
> setuser
> GO
> EXEC sp_bindefault N'[dbo].[LastModified]',
> N'[tblClaimDetailProduct].[InsertDate]'
> GO
> EXEC sp_bindefault N'[dbo].[LastModifiedBy]',
> N'[tblClaimDetailProduct].[InsertUser]'
> GO
> EXEC sp_bindefault N'[dbo].[RecordStatus]',
> N'[tblClaimDetailProduct].[RecordStatus]'
> GO
> EXEC sp_bindefault N'[dbo].[LastModified]',
> N'[tblClaimDetailProduct].[UpdateDate]'
> GO
> EXEC sp_bindefault N'[dbo].[LastModifiedBy]',
> N'[tblClaimDetailProduct].[UpdateUser]'
> GO
> setuser
> GO
> setuser
> GO
> EXEC sp_bindefault N'[dbo].[LastModified]',
> N'[tblProductBundle].[InsertDate]'
> GO
> EXEC sp_bindefault N'[dbo].[LastModifiedBy]',
> N'[tblProductBundle].[InsertUser]'
> GO
> EXEC sp_bindefault N'[dbo].[RecordStatus]',
> N'[tblProductBundle].[RecordStatus]'
> GO
> EXEC sp_bindefault N'[dbo].[LastModified]',
> N'[tblProductBundle].[UpdateDate]'
> GO
> EXEC sp_bindefault N'[dbo].[LastModifiedBy]',
> N'[tblProductBundle].[UpdateUser]'
> GO
> setuser
> GO
> ALTER TABLE [dbo].[tblClaimDetail] ADD
> CONSTRAINT [FK_tblClaimDetail_tblBundle] FOREIGN KEY
> (
> [BundleID]
> ) REFERENCES [dbo].[tblBundle] (
> [BundleID]
> ),
> CONSTRAINT [FK_tblClaimDetail_tblClaim] FOREIGN KEY
> (
> [ClaimID]
> ) REFERENCES [dbo].[tblClaim] (
> [ClaimID]
> ),
> CONSTRAINT [FK_tblClaimDetail_tblClaimInvoice] FOREIGN KEY
> (
> [ClaimInvoiceID]
> ) REFERENCES [dbo].[tblClaimInvoice] (
> [ClaimInvoiceID]
> ),
> CONSTRAINT [FK_tblClaimDetail_tblLUClaimDetailStatu
s] FOREIGN KEY
> (
> [ClaimDetailStatusID]
> ) REFERENCES [dbo].[tblLUClaimDetailStatus] (
> [ClaimDetailStatusID]
> )
> GO
> ALTER TABLE [dbo].[tblClaimDetailBundle] ADD
> CONSTRAINT [FK_tblClaimDetailBundle_tblBundle] FOREIGN KEY
> (
> [BundleID]
> ) REFERENCES [dbo].[tblBundle] (
> [BundleID]
> ),
> CONSTRAINT [FK_tblClaimDetailBundle_tblClaimDetail]
FOREIGN KEY
> (
> [ClaimDetailID]
> ) REFERENCES [dbo].[tblClaimDetail] (
> [ClaimDetailID]
> ),
> CONSTRAINT [FK_tblClaimDetailBundle_tblLUClaimDetai
lStatus] FOREIGN KEY
> (
> [ClaimDetailBundleStatusID]
> ) REFERENCES [dbo].[tblLUClaimDetailStatus] (
> [ClaimDetailStatusID]
> )
> GO
> ALTER TABLE [dbo].[tblClaimDetailProduct] ADD
> CONSTRAINT [FK_tblClaimDetailProduct_tblClaimDetail
Bundle] FOREIGN KEY
> (
> [ClaimDetailBundleID]
> ) REFERENCES [dbo].[tblClaimDetailBundle] (
> [ClaimDetailBundleID]
> ),
> CONSTRAINT [FK_tblClaimDetailProduct_tblProductBund
le] FOREIGN KEY
> (
> [ProductBundleID]
> ) REFERENCES [dbo].[tblProductBundle] (
> [ProductBundleID]
> )
> GO
> ALTER TABLE [dbo].[tblProductBundle] ADD
> CONSTRAINT [FK_tblProductBundle_tblBundle] FOREIGN KEY
> (
> [BundleID]
> ) REFERENCES [dbo].[tblBundle] (
> [BundleID]
> )
> GO
> As far as the other questions, I did update statistics and it did not give
> me a performance boot. I also did DBCC DBREINDEX which did not help me
> either. Mowgli, I cant answer your question at this time since this is the
> only table I have 900,000 rows. others are like 1000 rows and they do
return
> data in less than a second (milli seconds). I have also verified that the
> problem is the query itself taking time and not communication. When I do a
> profiler trace i see most of the time is spent processing the query .(i
did
> insert into temp as well but the insert is longer because the 900,00 rows
> returned and inserted is taking time). After the insert is done. the
select
> * from temp is faster.
> Any other things i need to look into?.
> Thanks
> M
>|||M,
A note about your schema. It seems you have a policy of adding an
Identity column to each table, and calling it the Primary Key. However,
you haven't named the natural key, since there are no Unique constraints
(or unique indexes).
Take table ClaimDetails as an example. Maybe (ClaimID, BundleID) is the
natural key. If it is, then it is a good practice to either make it the
Primary Key, or at least create a Unique Constraint for it. You can make
the Unique constraint clustered if you like. The point is, that it will
get a unique index, and that is important for the query optimizer.
The di

vantage of adding a surrogate key like this Identity column, is
that it makes it harder to efficiently join tables if selection is done
based on the natural key (especially if it is not unique, as noted
before). For example, if the primary key of table ClaimDetails was
indeed (ClaimID, BundleID), then table tblClaimDetailBundle would have a
ClaimID column (and no ClaimDetailID) and the join between
tblClaimDetailBundle - tblClaimDetail - tblClaim would be much more
efficient, since it would not require a hash.
Unfortunately, redesigning your schema will have a lot of impact, and
might not be possible. But it would make the decision about the
clustered index easier, because if you don't have to choose between the
surrogate key and the natural key then it will always be the Primary Key
that you want to have clustered.
You could try the following tips, and see if they help:
- make the index IX_tblClaimDetailProduct_1 on table
tblClaimDetailProduct clustered
- for all indexes that will contain only unique values, please create
the index with the Unique keyword
- if you have an SMP machine, then make sure parallelism is available,
because your query is likely to benefit from parallelism (because of the
many hashes).
I guess it won't help much. Although I must say that I think SQL-Server
is doing a pretty good job (given the circumstances).
Gert-Jan
Sagar wrote:
> Thanks for all your replies. I appreciate it. Well Here is the table schema:[/colo
r]
<snip>
> As far as the other questions, I did update statistics and it did not give
> me a performance boot. I also did DBCC DBREINDEX which did not help me
> either. Mowgli, I cant answer your question at this time since this is the
> only table I have 900,000 rows. others are like 1000 rows and they do retu
rn
> data in less than a second (milli seconds). I have also verified that the
> problem is the query itself taking time and not communication. When I do a
> profiler trace i see most of the time is spent processing the query .(i di
d
> insert into temp as well but the insert is longer because the 900,00 rows
> returned and inserted is taking time). After the insert is done. the selec
t
> * from temp is faster.
> Any other things i need to look into?.
> Thanks
> M