September 18, 2010 at 4:35 am
I have Db table containing following number of records,
Table data,
BusinessData bdf = 13335713
SubCategoryNAICSCode subnaics = 79680
SubCategories sub = 344
Categories cat = 121
Industries ind = 6
I have the following sql query(sql server 2005) ,
1)
Select top 1001 bdf.*,sub.SubcategoryID,sub.SubCategoryName,cat.CategoryId,cat.CategoryName,
ind.IndustryID,ind.IndustryName from
BusinessData bdf,SubCategoryNAICSCode subnaics,SubCategories sub,Categories cat, Industries ind
where bdf.[NAICS Code]=subnaics.NAICSCode and subnaics.Subcategoryid=sub.SubCategoryid and sub.CategoryId=cat.CategoryId
and cat.industryid=ind.industryid
and bdf.[Business Name] like 'a%' and bdf.CityState like '%f%' and bdf.[State] like 'South Carolina%'
(return data in 0.01 sec)
First two records
3046712 556620052 APARTMENT SERVICES INC 1765 GREAT NORTH RD Great Falls South Carolina 29055 803-482-6500 Unknown 651300 Apartment Bld Opers 531110 Lessors of Residential Buildings and Dwellings Single Location 2000 3 3 600000 600000 Regular Listing Not a Home Business Non-member 34.568090 -80.914349 0 16900 Chester, SC 45023 Chester County 14527 NULL Great Falls, South Carolina 2 USA Great Falls, SC 342 Clay products 90 Non Metallic Mineral Product 3 Manufacturing
3046712 556620052 APARTMENT SERVICES INC 1765 GREAT NORTH RD Great Falls South Carolina 29055 803-482-6500 Unknown 651300 Apartment Bld Opers 531110 Lessors of Residential Buildings and Dwellings Single Location 2000 3 3 600000 600000 Regular Listing Not a Home Business Non-member 34.568090 -80.914349 0 16900 Chester, SC 45023 Chester County 14527 NULL Great Falls, South Carolina 2 USA Great Falls, SC 342 Clay products 90 Non Metallic Mineral Product 3 Manufacturing
2.
Select top 1001 bdf.*,sub.SubcategoryID,sub.SubCategoryName,cat.CategoryId,cat.CategoryName,
ind.IndustryID,ind.IndustryName from
BusinessData bdf,SubCategoryNAICSCode subnaics,SubCategories sub,Categories cat, Industries ind
where bdf.[NAICS Code]=subnaics.NAICSCode and subnaics.Subcategoryid=sub.SubCategoryid and sub.CategoryId=cat.CategoryId
and cat.industryid=ind.industryid
and bdf.[Business Name] like 'a%' and bdf.CityState like '%f%' and bdf.[State] like 'California%'
(return data in 2.33 sec)
First two records
3547866 541199279 ALL CITY PLUMBING PO BOX 901927 Los Angeles California 90003 310-312-1450 Unknown 171102 Plumbing Contractor 238220 Plumbing/Heating & Air-Conditioning Contractors Single Location 1984 4 4 700000 700000 Regular Listing Not a Home Business Non-member 33.965254 -118.273619 0 31100 Los Angeles-Long Beach et al, CA 06037 Los Angeles County 2275 NULL Los Angeles, California 2 USA Los Angeles, CA 105 Audio/ Visual Repair, Maintenance & Rental 10 Audio/ Visual Repair, Maintenance & Rental 5 Services
3547866 541199279 ALL CITY PLUMBING PO BOX 901927 Los Angeles California 90003 310-312-1450 Unknown 171102 Plumbing Contractor 238220 Plumbing/Heating & Air-Conditioning Contractors Single Location 1984 4 4 700000 700000 Regular Listing Not a Home Business Non-member 33.965254 -118.273619 0 31100 Los Angeles-Long Beach et al, CA 06037 Los Angeles County 2275 NULL Los Angeles, California 2 USA Los Angeles, CA 105 Audio/ Visual Repair, Maintenance & Rental 10 Audio/ Visual Repair, Maintenance & Rental 5 Services
query 1 response within 0.01 sec however query 2 response 2.33 sec even though I just change 'South Carolina%' to 'California%'. And indexes are already applied in each LIKE column.
we need 0.10 sec maximum response time that's why we cut the result set top 1001.
September 18, 2010 at 8:28 am
Please provide scripts for table structure and details of indexes
regards gah
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 18, 2010 at 10:03 am
abcim
When posting a performance problem, to assist you in obtaining the best answer, please click on the second link in my signature block, and attempt to follow its instruction for posting the performance plan.
September 19, 2010 at 11:22 pm
Thanks for you replies,
My problem is only at this line,When I use
bdf.[State] like 'South Carolina%'
It takes 0.01 Sec
And when I use the same query with,
bdf.[State] like 'California%'
It takes 2.33 Minutes
Is you see, when I just changed 'South Carolina%' to 'California%', then response becomes 2.33 Minutes
Here is the index and tables details,
USE [DB_Business]
GO
/****** Object: Table [dbo].[Industries] Script Date: 09/20/2010 09:34:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Industries](
[IndustryID] [int] IDENTITY(1,1) NOT NULL,
[IndustryCode] [nvarchar](50) NULL,
[IndustryName] [nvarchar](100) NULL,
[IndustryDescription] [nvarchar](100) NULL,
[IndustryURLName] [nvarchar](100) NULL,
[IsActive] [bit] NULL,
[CreatedDate] [datetime] NULL,
[ModifiedDate] [datetime] NULL,
CONSTRAINT [PK_Industries] PRIMARY KEY CLUSTERED
(
[IndustryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[SubCategoryNAICSCode] Script Date: 09/20/2010 09:34:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SubCategoryNAICSCode](
[SubCategoryNAICSCodeID] [int] IDENTITY(1,1) NOT NULL,
[SubCategoryID] [int] NOT NULL,
[NAICSCode] [nvarchar](50) NOT NULL,
[Type] [int] NOT NULL,
[CreatedDate] [datetime] NULL,
CONSTRAINT [PK_SubCategoryNAICSCode] PRIMARY KEY CLUSTERED
(
[SubCategoryNAICSCodeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[BusinessData] Script Date: 09/20/2010 09:34:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[BusinessData](
[Business ID] [int] IDENTITY(1,1) NOT NULL,
[Business ID Number] [nvarchar](50) NULL,
[Business Name] [nvarchar](50) NULL,
[Address] [nvarchar](50) NULL,
[City] [nvarchar](50) NULL,
[State] [nvarchar](50) NULL,
[ZIP] [nvarchar](50) NULL,
[ZIP+4] [nvarchar](50) NULL,
[Phone] [nvarchar](50) NULL,
[Contact] [nvarchar](50) NULL,
[Gender] [nvarchar](50) NULL,
[Title] [nvarchar](50) NULL,
[SIC Code] [nvarchar](50) NULL,
[SIC Description] [nvarchar](50) NULL,
[NAICS Code] [nvarchar](50) NULL,
[NAICS Description] [nvarchar](50) NULL,
[Location Type] [nvarchar](50) NULL,
[Year of 1st Appearance] [nvarchar](50) NULL,
[Number of Local Employees] [nvarchar](50) NULL,
[Total Number of Employees] [nvarchar](50) NULL,
[Local Annual Sales] [nvarchar](50) NULL,
[Total Annual Sales] [nvarchar](50) NULL,
[Ad Size] [nvarchar](50) NULL,
[Business At Home] [nvarchar](50) NULL,
[Ultimate Parent Id] [nvarchar](50) NULL,
[Ultimate Parent Name] [nvarchar](50) NULL,
[Subsidiary Parent Id] [nvarchar](50) NULL,
[Subsidiary Parent Name] [nvarchar](50) NULL,
[Fortune 1000 Rank] [nvarchar](50) NULL,
[Ultimate Site Number] [nvarchar](50) NULL,
[Latitude] [nvarchar](50) NULL,
[Longitude] [nvarchar](50) NULL,
[Match Flag] [nvarchar](50) NULL,
[CBSA Code] [nvarchar](50) NULL,
[CBSA Name] [nvarchar](50) NULL,
[FIPS Code] [nvarchar](50) NULL,
[County] [nvarchar](50) NULL,
[Secondary Address] [nvarchar](50) NULL,
[Secondary State] [nvarchar](50) NULL,
[Secondary City] [nvarchar](50) NULL,
[Secondary ZIP] [nvarchar](50) NULL,
[Secondary ZIP4] [nvarchar](50) NULL,
[Fax] [nvarchar](50) NULL,
[Secondary SIC 1] [nvarchar](50) NULL,
[Secondary SIC 2] [nvarchar](50) NULL,
[Secondary SIC 3] [nvarchar](50) NULL,
[Secondary SIC 4] [nvarchar](50) NULL,
[Brand Codes] [nvarchar](50) NULL,
[Franchise Codes] [nvarchar](50) NULL,
[Chain Codes] [nvarchar](50) NULL,
[Specialty Codes] [nvarchar](50) NULL,
[CityID] [int] NULL,
[StateID] [int] NULL,
[CityState] [nvarchar](75) NULL,
[CountryID] [int] NULL,
[Country] [nvarchar](50) NULL,
[CityStateCode] [nvarchar](75) NULL,
CONSTRAINT [PK_BusinessDataFinal] PRIMARY KEY CLUSTERED
(
[Business ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Categories] Script Date: 09/20/2010 09:34:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Categories](
[CategoryID] [int] IDENTITY(1,1) NOT NULL,
[IndustryID] [int] NULL,
[CategoryCode] [nvarchar](50) NULL,
[CategoryName] [nvarchar](100) NULL,
[CategoryDescription] [nvarchar](100) NULL,
[CategoryURLName] [nvarchar](100) NULL,
[IsActive] [bit] NULL,
[CreatedDate] [datetime] NULL,
[ModifiedDate] [datetime] NULL,
[CategoryImage] [nvarchar](200) NULL,
CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED
(
[CategoryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[SubCategories] Script Date: 09/20/2010 09:34:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SubCategories](
[SubCategoryID] [int] IDENTITY(1,1) NOT NULL,
[CategoryID] [int] NULL,
[SubCategoryName] [nvarchar](100) NULL,
[SubCategoryCode] [nvarchar](50) NULL,
[SubCategoryDescription] [nvarchar](200) NULL,
[SubCategoryURLName] [nvarchar](100) NULL,
[IsActive] [bit] NULL,
[CreatedDate] [datetime] NULL,
[ModifiedDate] [datetime] NULL,
[SubCategoryImage] [nvarchar](200) NULL,
[SubCatInd] [nvarchar](200) NULL,
CONSTRAINT [PK_SubCategories] PRIMARY KEY CLUSTERED
(
[SubCategoryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Index [PK_BusinessDataFinal] Script Date: 09/20/2010 10:08:29 ******/
ALTER TABLE [dbo].[BusinessData] ADD CONSTRAINT [PK_BusinessDataFinal] PRIMARY KEY CLUSTERED
(
[Business ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IX_BusinessData_Address] Script Date: 09/20/2010 09:37:06 ******/
CREATE NONCLUSTERED INDEX [IX_BusinessData_Address] ON [dbo].[BusinessData]
(
[Address] 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
/****** Object: Index [IX_BusinessData_Address] Script Date: 09/20/2010 09:37:06 ******/
CREATE NONCLUSTERED INDEX [IX_BusinessData_Address] ON [dbo].[BusinessData]
(
[Address] 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
GO
/****** Object: Index [IX_BusinessData_BusinessName] Script Date: 09/20/2010 09:38:30 ******/
CREATE NONCLUSTERED INDEX [IX_BusinessData_BusinessName] ON [dbo].[BusinessData]
(
[Business Name] 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
/****** Object: Index [IX_BusinessData_City] Script Date: 09/20/2010 09:39:15 ******/
CREATE NONCLUSTERED INDEX [IX_BusinessData_City] ON [dbo].[BusinessData]
(
[City] 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
/****** Object: Index [IX_BusinessData_CityState] Script Date: 09/20/2010 10:05:58 ******/
CREATE NONCLUSTERED INDEX [IX_BusinessData_CityState] ON [dbo].[BusinessData]
(
[CityState] 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
/****** Object: Index [IX_BusinessData_CityStateCode] Script Date: 09/20/2010 10:06:02 ******/
CREATE NONCLUSTERED INDEX [IX_BusinessData_CityStateCode] ON [dbo].[BusinessData]
(
[CityStateCode] 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
/****** Object: Index [IX_BusinessData_NAICSCode] Script Date: 09/20/2010 10:06:05 ******/
CREATE NONCLUSTERED INDEX [IX_BusinessData_NAICSCode] ON [dbo].[BusinessData]
(
[NAICS Code] 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
/****** Object: Index [IX_BusinessData_Phone] Script Date: 09/20/2010 10:06:09 ******/
CREATE NONCLUSTERED INDEX [IX_BusinessData_Phone] ON [dbo].[BusinessData]
(
[Phone] 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
/****** Object: Index [IX_BusinessData_Zip] Script Date: 09/20/2010 10:07:51 ******/
CREATE NONCLUSTERED INDEX [IX_BusinessData_Zip] ON [dbo].[BusinessData]
(
[ZIP] 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
/****** Object: Index [IX_BusinessData_Zip+4] Script Date: 09/20/2010 10:08:23 ******/
CREATE NONCLUSTERED INDEX [IX_BusinessData_Zip+4] ON [dbo].[BusinessData]
(
[ZIP+4] 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
/****** Object: Index [IX_BusinessDataState] Script Date: 09/20/2010 10:08:27 ******/
CREATE NONCLUSTERED INDEX [IX_BusinessDataState] ON [dbo].[BusinessData]
(
[State] 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]
September 19, 2010 at 11:31 pm
Since you didn't post the execution plan, I'll ask the next question. 🙂
Is there a difference in the execution plans between the two queries when you run them? Devil there will be in the details, check for estimated rows vs. actual and the like, as well as surface items like seeks vs. scans.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 19, 2010 at 11:51 pm
Sounds like classic parameter sniffing. Let me guess, there's a massive difference in row counts between South Carolina and California?
http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/
I'd like to see the execution plans for both please, plus details of the indexes on the table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 19, 2010 at 11:58 pm
I am using top 1001 and execution plan are attached,
September 20, 2010 at 1:27 am
They are estimated plans not actual plans , so its hard to tell if sqlserver has made the right choice.
But you can plainly see that in the in the california example , it scans the businessdata table rather than seeking as in the South Carolina example.
Why does it do this ? Seeking is cheap , if you only need to do a key lookup on a 'few' rows. In the scan example it thinks a lot of rows will be returned and hence that is cheaper.
Now you havent stated how many rows are to be returned from each ( the top 1001 is irrelevant at this step) but this looks good for what sqlserver knows.
Are the statistics up to date ?
You could try pulling the top 1001 ids's into a temp table and seeing if that helps.
September 20, 2010 at 2:05 am
Actual execution plans please, not estimated. The estimated don't show the row count and that's critical for telling if this is indeed parameter sniffing.
The use of TOP doesn't change the question of cardinality. Is there is big discrepancy in the total number of rows for South Carolina vs California?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 20, 2010 at 4:18 am
Parameter sniffing isnt an issue here as literal values are provided.
Turning on forced parametrization may provide a more 'even' plan, is this a dynamic sql query ?
September 20, 2010 at 4:25 am
Thanks I have found some interesting result,
By Adding, WITH (INDEX (IX_BusinessDataState)) hint, and I get result 0.01 sec.
Select top 1001 bdf.*,sub.SubcategoryID,sub.SubCategoryName,cat.CategoryId,cat.CategoryName,
ind.IndustryID,ind.IndustryName from
BusinessData bdf WITH (INDEX (IX_BusinessDataState)), SubCategoryNAICSCode subnaics,SubCategories sub,Categories cat, Industries ind
where bdf.[NAICS Code]=subnaics.NAICSCode and subnaics.Subcategoryid=sub.SubCategoryid and sub.CategoryId=cat.CategoryId
and cat.industryid=ind.industryid
and bdf.[State] like 'California%'
is this right solution... ?? or it will effect other conditional clause like city, subcategory etc...
Please give also some suggestion to improve this sql. I have 20 Million records in one table.
I really appreciate your help.
find the attached actual plan,
September 20, 2010 at 11:09 pm
Any one?
September 21, 2010 at 12:12 am
Getting this error off qry1:
TITLE: Microsoft SQL Server Management Studio Express
------------------------------
Error loading execution plan XML file C:\ssc\qry1.sqlplan. (Microsoft.SqlServer.Express.SQLEditors)
------------------------------
ADDITIONAL INFORMATION:
There is an error in XML document (1009, 15). (System.Xml)
------------------------------
Data at the root level is invalid. Line 1009, position 15. (System.Xml)
Anyone else?
Either way, you pretty much nailed down the issue. It's switching indexes because it doesn't like the estimate or the selectivity. If you're going to keep that index hint, you'll want to make sure that index covers by adding in NAICS CODE for the join back to SubCategory.
Without building an index that covers the entire BDF table for the select * component, however, you're going to have to either thin down that select splat, or live with the bookmark lookup.
Maybe one of the more experienced experts here though will have better luck with the execution plans you posted. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 21, 2010 at 1:57 am
Yup , i get the same plan with that plan. It does look like including the SubCategoryID to the IX_NAICSSCODE index ,for which i cant see the DDL, will have a dramatic effect.
September 21, 2010 at 2:49 am
Yup, something wrong with those xml files. I can't load them either.
abcim, how did you generate those plans?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply