large tables with table scans - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

large tables with table scans

Author

Topic

dshoaf
Starting Member

3 Posts

Posted - 2004-04-16 : 14:15:39

I have two large tables (3.7M and 8M records) that I am joining and searching. The fields that I am joining on and searching are all keyed. I still get table scans. Is this a problem with the size of the tables, or am I doing something wrong? The query is:

SELECT
CD.Total_Charge
FROM
ada_data.dbo.Claim_Header CH INNER JOIN ada_data.dbo.Claim_Detail CD ON
CH.Claim_Number = CD.Claim_Number AND
CH.Worksheet_Number = CD.Worksheet_Number
WHERE
CH.Group_Number = @group AND
CH.Benefit_Type = 'M' AND
CH.Claim_Status_Acct = 'f' AND
CH.Claim_Status_Stats = 'f' AND
(CH.Paid_Date >= @fromDate AND CH.Paid_Date <= @thruDate)AND
CD.Inel_Code <> '02'

Thanks,
Dave

gpl
Posting Yak Master

195 Posts

Posted - 2004-04-16 : 14:20:13

can I be the first to say

please post the DDL for your tables, it makes it so much easier

one observation though the clause CD.Inel_Code <> '02' could be the culprit - as I understand it, negative conditions have a heavy impact on performance

Graham

dshoaf
Starting Member

3 Posts

Posted - 2004-04-16 : 15:04:40

Is this what you are looking for? (I'm not sure how to get the DDL, I generated a SQL create script)

CREATE TABLE [dbo].[Claim_Detail] (
[Claim_Number] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Worksheet_Number] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Total_Charge] [numeric](9, 2) NOT NULL ,
[Inel_Code] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE INDEX [IX_Claim_Detail_Claim_Number] ON [dbo].[Claim_Detail]([Claim_Number]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_Claim_Detail_Worksheet_Number] ON [dbo].[Claim_Detail]([Worksheet_Number]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_Claim_Detail_voidDate] ON [dbo].[Claim_Detail]([Void_Date]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_Claim_Detail_inelCode] ON [dbo].[Claim_Detail]([Inel_Code]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_Claim_Detail_Ineligible] ON [dbo].[Claim_Detail]([Ineligible]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_Claim_Detail_Amt_Pd_Claimant] ON [dbo].[Claim_Detail]([Amt_Pd_Claimant]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_Claim_Detail_Amt_Pd_Provider] ON [dbo].[Claim_Detail]([Amt_Pd_Provider]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_Claim_Detail_Benefit_Code] ON [dbo].[Claim_Detail]([Benefit_Code]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_Claim_Detail_Cov_Anal_Code] ON [dbo].[Claim_Detail]([Cov_Anal_Code]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE TABLE [dbo].[Claim_Header] (
[Group_Number] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Claim_Number] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Worksheet_Number] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Benefit_Type] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Claim_Status_Acct] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Claim_Status_Stats] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Paid_Date] [numeric](10, 0) NOT NULL ,
) ON [PRIMARY]
GO
CREATE INDEX [IX_Claim_Header] ON [dbo].[Claim_Header]([Group_Number]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_Claim_Header_ClaimNumber] ON [dbo].[Claim_Header]([Claim_Number]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_Claim_Header_WorksheetNumber] ON [dbo].[Claim_Header]([Worksheet_Number]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_Claim_Header_BenefitType] ON [dbo].[Claim_Header]([Benefit_Type]) ON [PRIMARY]
GO
CREATE INDEX [IX_Claim_Header_ClaimStatusStat] ON [dbo].[Claim_Header]([Claim_Status_Stats]) ON [PRIMARY]
GO
CREATE INDEX [IX_Claim_Header_ClaimStatusAcct] ON [dbo].[Claim_Header]([Claim_Status_Acct]) ON [PRIMARY]
GO
CREATE INDEX [IX_Claim_Header_Diagnosis_Code] ON [dbo].[Claim_Header]([Diagnosis_Code]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_Claim_Header_GN] ON [dbo].[Claim_Header]([Group_Number]) ON [PRIMARY]
GO
CREATE INDEX [IX_Claim_Header_Paid_Date] ON [dbo].[Claim_Header]([Paid_Date]) ON [PRIMARY]
GO
CREATE INDEX [IX_Claim_Header_Internal_Employee_Number] ON [dbo].[Claim_Header]([Internal_Employee_Number]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_Claim_Header_Dependent_Code] ON [dbo].[Claim_Header]([Dependent_Code]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_Claim_Header_Plan] ON [dbo].[Claim_Header]([Plan]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_Claim_Header_Plan_Revision] ON [dbo].[Claim_Header]([Plan_Revision]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

I changed the <> to = (just to test it) and I still get a table scan.

Thanks,
Dave

gpl
Posting Yak Master

195 Posts

Posted - 2004-04-16 : 16:33:28

Dave
I would be inclined to create an index with all of the columns relevant to your query in - depending on your data, the most important - ie first - column being the one that would restrict your search the greatest - the Paid_Date would be a good candidate - something like this

CREATE INDEX [IX_Main_Query] ON [dbo].[Claim_Header](
Paid_Date,
Group_Number,
Benefit_Type,
Claim_Status_Acct,
Claim_Status_Stats,
Worksheet_Number
) WITH FILLFACTOR = 90 ON [PRIMARY]

Changing the date comparison to this may also give a performance benefit
(CH.Paid_Date BETWEEN @fromDate AND @thruDate) AND ......

Graham

dshoaf
Starting Member

3 Posts

Posted - 2004-04-26 : 15:30:04

Sorry for the delay in my response. I found somewhat of a solution and I thought I would post it here. (I would be interested if anyone has any idea why this works better.) Every field that is queried on or joined has been indexed. I was playing with the query to see if I could find exactly what field was causing the problem, and I removed claim_detail (which is the biggest table) and the table scans were gone. I put that query into ()'s and then joined it to claim_detail so I had the following:

SELECT
CD.Total_Charge
FROM Claim_Detail CD inner join
(SELECT
CH.Claim_Number,
CH.Worksheet_Number
FROM
ada_data.dbo.Claim_Header
WHERE
CH.Group_Number = @group AND
CH.Benefit_Type = 'M' AND
CH.Claim_Status_Acct = 'f' AND
CH.Claim_Status_Stats = 'f' AND
(CH.Paid_Date >= @fromDate AND CH.Paid_Date <= @thruDate)) as A
on A.Claim_Number = CD.Claim_Number AND
A.Worksheet_Number = CD.Worksheet_Number
WHERE
CD.Inel_Code <> '02'

which still had tables scans until I added grouping. (I'm not sure why the grouping helped.)

SELECT
sum(CD.Total_Charge)
FROM Claim_Detail CD inner join
(SELECT
CH.Claim_Number,
CH.Worksheet_Number
FROM
ada_data.dbo.Claim_Header
WHERE
CH.Group_Number = @group AND
CH.Benefit_Type = 'M' AND
CH.Claim_Status_Acct = 'f' AND
CH.Claim_Status_Stats = 'f' AND
(CH.Paid_Date >= @fromDate AND CH.Paid_Date <= @thruDate)) as A
on A.Claim_Number = CD.Claim_Number AND
A.Worksheet_Number = CD.Worksheet_Number
WHERE
CD.Inel_Code <> '02'
group by
cd.claim_number, cd.worksheet_number, cd.line_number, cd.line_counter, cd.line_number_dupe, CD.Inel_Code, CD.Cov_Anal_Code, CD.Benefit_Code

Now magically my table scans are gone, and it now finishes in 1 minute instead of > 8 mins. It seems that it was joining the two tables before it applies the where clause.(just my guess.)

Thanks,
Dave

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources