April 28, 2009 at 11:03 am
The following query takes a while(1 to 2 minutes) to return data(around 60000 rows). The table schema and indexes and exec plan is attached. I have added non clustered indexes but have not seen any improvement. My production server is a 64 bit, 8 processor 2.66GHz 16GB of RAM running 2005 SP2.
select
CAST(PC.tpcGCProviderID AS bigint) AS nbaGCProviderID,
CAST(Pc.tpcLocationID AS bigint) AS nbaLocationID,
P.tpProviderType as nbaProviderType,
P.tpLastName as nbaLastName,
P.tpFirstName as nbaFirstName,
P.tpCredentials as nbaCredentials,
P.tpGroupName as nbaGroupName,
PL.tplAddrLine1 nbaAddress,
PL.tplCity as nbaCity,
PL.tplState as nbaState,
PL.tplCounty as nbacounty,
LEFT(PL.tplZipCode, 5) AS nbaZip,
PC.tpcPhone as nbaBusinessPhone,
isnull(PC.tpcFaxPhone,'') as nbaFaxPhone,
PC.tpcNetwork as nbaNetwork
FROM
dbo.tbl_ProviderCoverage AS PC with (nolock)
INNER JOIN dbo.tbl_Provider AS P with (nolock) ON PC.tpcGCProviderID =P.tpGCProviderID
AND P.tpProviderType='physician'
INNER JOIN dbo.tbl_ProviderLocation AS PL ON PC.tpcLocationID =PL.tplLocationID
and (PL.tplAddressType = 'L')
WHERE
PL.tplState ='il'
and PL.tplZipLatitude BETWEEN 41.487714 and 42.212352
AND PL.tplZipLongitude BETWEEN -88.012371 and -87.287733
and PC.tpcNetwork='firsthlth'
AND (PC.tpcEffDate < DATEADD(year, 1, GETDATE())) AND (PC.tpcTermDate > DATEADD(year, 1, GETDATE()))
order by [nbaLocationID] ,[nbaGCProviderID]
--Schema
CREATE TABLE [dbo].[tbl_Provider](
[tpGCProviderID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[tpProviderType] [char](9) NOT NULL,
[tpLastName] [varchar](50) NULL,
[tpFirstName] [varchar](50) NULL,
[tpMiddleInit] [char](1) NULL,
[tpSuffix] [char](10) NULL,
[tpCredentials] [char](10) NULL,
[tpGroupName] [varchar](100) NULL,
CONSTRAINT [PK_tbl_Provider_1] PRIMARY KEY CLUSTERED
(
[tpGCProviderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PROVIDER_RELATIONAL]
) ON [PROVIDER_RELATIONAL]
GO
CREATE TABLE [dbo].[tbl_ProviderCoverage](
[tpcID] [numeric](18, 0) IDENTITY(1,1) NOT NULL ,--PRIMARY KEY but non clustered
[tpcGCProviderID] [numeric](18, 0) NOT NULL,
[tpcLocationID] [numeric](18, 0) NOT NULL,
[tpcTaxID] [varchar](50) NOT NULL,
[tpcNetwork] [varchar](50) NOT NULL,
[tpcEffDate] [datetime] NOT NULL,
[tpcTermDate] [datetime] NULL,
[tpcUserName] [varchar](50) NULL,
[tpcPhone] [char](10) NULL,
[tpcFaxPhone] [char](10) NULL
) ON [PROVIDER_RELATIONAL]
CREATE CLUSTERED INDEX [IX_tbl_ProviderCoverage] ON [dbo].[tbl_ProviderCoverage]
(
[tpcLocationID] ASC,
[tpcGCProviderID] ASC,
[tpcNetwork] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tbl_ProviderLocation](
[tplLocationID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[tplAddressType] [char](1) NULL,
[tplAddrLine1] [varchar](50) NULL,
[tplAddrLine2] [varchar](50) NULL,
[tplCity] [varchar](50) NULL,
[tplState] [char](2) NULL,
[tplZipCode] [char](9) NULL,
[tplCounty] [varchar](50) NULL,
[tplCountry] [varchar](50) NULL,
[tplLatitude] [decimal](10, 6) NULL,
[tplLongitude] [decimal](10, 6) NULL,
[tplDateCreated] [datetime] NULL,
[tplDateUpdated] [datetime] NULL,
[tplUserName] [varchar](50) NULL,
[tplFIPS_StateCode] [char](2) NULL,
[tplFIPS_CountyCode] [char](3) NULL,
[tplGeoCodeError] [bit] NOT NULL CONSTRAINT [DF_tbl_ProviderLocation_tplGeoCodeError] DEFAULT (0),
[tplZipLatitude] [decimal](10, 6) NULL,
[tplZipLongitude] [decimal](10, 6) NULL,
CONSTRAINT [PK_tbl_ProviderLocation] PRIMARY KEY CLUSTERED
(
[tplLocationID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PROVIDER_RELATIONAL]
) ON [PROVIDER_RELATIONAL]
GO
/****** Object: Index [IX_AddTypeStLongLatLocIDAddrCityCountyCoverinfZip] Script Date: 04/28/2009 12:48:35 ******/
CREATE NONCLUSTERED INDEX [IX_AddTypeStLongLatLocIDAddrCityCountyCoverinfZip] ON [dbo].[tbl_ProviderLocation]
(
[tplAddressType] ASC,
[tplState] ASC,
[tplZipLongitude] ASC,
[tplZipLatitude] ASC,
[tplLocationID] ASC,
[tplAddrLine1] ASC,
[tplCity] ASC,
[tplCounty] ASC
)
INCLUDE ( [tplZipCode]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_LocIDNetworkCoveringEffDateTermDate] ON [dbo].[tbl_ProviderCoverage]
(
[tpcLocationID] ASC,
[tpcNetwork] ASC,
[tpcID] ASC,
[tpcGCProviderID] ASC,
[tpcPhone] ASC
)
INCLUDE ( [tpcEffDate],
[tpcTermDate],
[tpcFaxPhone]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PROVIDER_RELATIONAL]
go
CREATE NONCLUSTERED INDEX [IX_AddTypeStLongLatLocIDAddrCityCountyCoverinfZip] ON [dbo].[tbl_ProviderLocation]
(
[tplAddressType] ASC,
[tplState] ASC,
[tplZipLongitude] ASC,
[tplZipLatitude] ASC,
[tplLocationID] ASC,
[tplAddrLine1] ASC,
[tplCity] ASC,
[tplCounty] ASC
)
INCLUDE ( [tplZipCode]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
go
CREATE NONCLUSTERED INDEX [IX_ProvIdTypeLNameFNameCredGName] ON [dbo].[tbl_Provider]
(
[tpGCProviderID] ASC,
[tpProviderType] ASC,
[tpLastName] ASC,
[tpFirstName] ASC,
[tpCredentials] ASC,
[tpGroupName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
All the indexex above are used by the query in the execution plan....
April 28, 2009 at 11:07 am
i didn't see the execution plan as getting attached; can you re-post it?
Lowell
April 28, 2009 at 11:18 am
A seat of the pants change I'd make would be the clustered index on this table:
CREATE TABLE [dbo].[tbl_ProviderCoverage](
[tpcID] [numeric](18, 0) IDENTITY(1,1) NOT NULL ,--PRIMARY KEY but non clustered
[tpcGCProviderID] [numeric](18, 0) NOT NULL,
[tpcLocationID] [numeric](18, 0) NOT NULL,
[tpcTaxID] [varchar](50) NOT NULL,
[tpcNetwork] [varchar](50) NOT NULL,
[tpcEffDate] [datetime] NOT NULL,
[tpcTermDate] [datetime] NULL,
[tpcUserName] [varchar](50) NULL,
[tpcPhone] [char](10) NULL,
[tpcFaxPhone] [char](10) NULL
) ON [PROVIDER_RELATIONAL]
CREATE CLUSTERED INDEX [IX_tbl_ProviderCoverage] ON [dbo].[tbl_ProviderCoverage]
(
[tpcLocationID] ASC,
[tpcGCProviderID] ASC,
[tpcNetwork] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
I would change it to (tpcEffDate asc, tpcTermDate asc, tpcGCProviderID asc). You are doing a range of dates, and this would order the data on these dates.
April 28, 2009 at 11:21 am
Would need to see the execution plan to know where to really start.
At a guess, I think this index might help, but I can't be sure without test data and so on:
create index IDX_ProviderCoverage_Test on dbo.tbl_ProviderCoverage
(tpcNetwork, tpcEffDate, tpcTermDate, tpcGCProviderID, tpcLocationID)
include (tpcPhone, tpcFaxPhone);
Just running the create scripts, and then the select, with no data in the tables, I'm getting a clustered index scan on the ProviderCoverage table. The index doesn't do much for that on mine, possibly because I don't have any data and a clustered index scan is just fine on an empty table.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 28, 2009 at 11:24 am
Thanks. Let me try that and will post.. I was finally able to find the attachement button to post the EXEC plan..please take a look at it.
April 28, 2009 at 11:30 am
Can you save the actual execution plan as a .sqlplan file, zip it and attach that? Without all the underlying data it's hard to read an execution plan.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 28, 2009 at 11:35 am
I saved the exec as sqlplan and Zipped it up and posted....
April 28, 2009 at 11:42 am
Looks like your statistics might be a tad off. The index seek operation that is taking up 86% of the cost shows an estimated 1 row, but is returning 65000. I'd suggest trying an update on the statistics there, probably with a full scan, just to be sure. Actually, same thing for all of the indexes. The query is trying to do loop joins on 60000 rows, so it's performing 60000 index seeks. I suspect with some updated statistics you might see a different set of join operators that are less costly than these things are right now.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 28, 2009 at 1:34 pm
I'd be willing to bet that rebuilding the indexes is in order here. That'll get your stats up to date, and index fragmentation is probably heavy.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 28, 2009 at 1:44 pm
We load the data into these tables every week with truncate data,create indexes and Update Stats. These are read only tables never updated. The updates happen in stage and we load the data in Prod every weekend(not the best way but that is what we have)
I have done a update stat with fullscan norecompute(??? should i do this) on these tables and still see the same execution plan.... I see some improvement in time but I would not bet on that as lot of this is cached now in Prod....by tomorrow I would see the slow times again.
I really appreciate you all helping me on this....
April 28, 2009 at 3:09 pm
There are a couple of conditions in the Join clause that look like they belong in the Where clause. The ones about provider type and address type.
Try moving those to the Where clause, see if it helps.
The other thing that might help would be to move parts of this into temp tables, then join those. Might reduce the complexity for the engine.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 28, 2009 at 3:21 pm
I added the index you suggested and there is a considerable improvement. But i have to force the query to use it, it uses the current index, if I drop that it uses the clustered index...attached is the exec plan...
April 29, 2009 at 7:25 am
Excellent. Definitely making progress. That also got rid of one of the nested loops joins and replaced it with a hash match. On this amount of data, that almost certainly makes a big difference.
Please move the two conditions I mentioned in my prior post down to the Where clause and see if that helps. I think it might.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 29, 2009 at 9:01 am
I did move the other two statements to the where clause. My only concern is that I have to force the index usage.. currently my clustered index is on LocationId,ProviderID,Network.
Looking at your index I can make that as a clustered index do you see some disadvantages in this approach?
Much thanks for your help..I am certainly moving in the right direction 🙂
April 29, 2009 at 9:10 am
If that index will work for clustering, then sure, go ahead. You'll know more about the table and its uses than I do, so your judgement on the subject is going to be better.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply