September 21, 2010 at 4:35 am
Thanks your resposnses,
Here is the files attached again.
September 21, 2010 at 4:45 am
Recommendation:
1) Consider removing that index hint
2) Widen the index [dbo].[SubCategoryNAICSCode].[IX_NAICSCode], add SubCategoryID as an include column
What's the total number of rows in the BusinessData table?
Post revised exec plans (preferably without the index hint) once you've widened that index.
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 21, 2010 at 4:57 am
How to widen index?
There are 13 million records in BusinessData
Table data,
BusinessData bdf = 13335713
SubCategoryNAICSCode subnaics = 79680
SubCategories sub = 344
Categories cat = 121
Industries ind = 6
September 21, 2010 at 5:27 am
abcim (9/21/2010)
How to widen index?
as gail suggested in her prior post
2) Widen the index [dbo].[SubCategoryNAICSCode].[IX_NAICSCode], add SubCategoryID as an include column
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 21, 2010 at 7:44 am
abcim (9/21/2010)
How to widen index?
If you need help with the syntax, please post the current definition of the index (you didn't include it before) and someone will help you. Can you also say whether the combination of NAICSCode and SubCategoryID is guaranteed to be unique in that table? I would imagine it is, so one could make a case for a unique index rather than an included column.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 21, 2010 at 11:09 pm
Thanks for your kind replies.
Is you saying some thing like this to widen index,
CREATE NONCLUSTERED INDEX [IX_NAICSCode] ON [dbo].[SubCategoryNAICSCode]
(
[NAICSCode] ASC,[SubCategoryID]
)
It won't effect any thing
I am getting very fast results in all cases when I just reduced SubCategoryNAICSCode Table Data to 1600 rows (previously it contains 79680 rows)
BusinessData bdf = 13335713
SubCategoryNAICSCode subnaics = 1600
SubCategories sub = 344
Categories cat = 121
Industries ind = 6
Please tell me that if i use a query with new keyword, it will produced the result in 30 sec, Next time the same query is executed which produced the result in 1 sec. Is SQl Server cache any query?
Why the same query response time is not same on every execution of sql?
Please tell me that if I increase CPU speed or increase CPU cores, then whether it effect sql query?
September 21, 2010 at 11:48 pm
abcim (9/21/2010)
Is you saying some thing like this to widen index...
No. Gail's suggestion was to use an included column, and I mentioned the alternative of a unique index. Our suggestions would be:
CREATE NONCLUSTERED INDEX [choose an index name]
ON [dbo].[SubCategoryNAICSCode] ([NAICSCode] ASC)
INCLUDE [SubCategoryID];
--- OR
CREATE UNIQUE NONCLUSTERED INDEX [choose an index name]
ON [dbo].[SubCategoryNAICSCode] ([NAICSCode] ASC, [SubCategoryID] ASC);
It won't effect any thing
Did you try it?
I am getting very fast results in all cases when I just reduced SubCategoryNAICSCode Table Data to 1600 rows (previously it contains 79680 rows)
That's changing the problem just a bit isn't it? Where's the actual execution plan for the new problem?
Please tell me that if i use a query with new keyword, it will produced the result in 30 sec, Next time the same query is executed which produced the result in 1 sec. Is SQl Server cache any query? Why the same query response time is not same on every execution of sql? Please tell me that if I increase CPU speed or increase CPU cores, then whether it effect sql query?
SQL Server caches many things, including execution plans and data/index pages. The first time you run a query, it needs to be compiled, and data for the tables referenced might need to be brought in to memory from disk. On subsequent executions, you may well find that the data is already in memory.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 22, 2010 at 12:03 am
abcim (9/21/2010)
Please tell me that if I increase CPU speed or increase CPU cores, then whether it effect sql query?
i would say NO. let Sql server handle the CPU or IO usage. just to tune one or couple of queries, increasing cpu core is not good decision.its better to concentrate on query and indexes design. this is my take.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 22, 2010 at 12:48 am
abcim (9/21/2010)
Thanks for your kind replies.Is you saying some thing like this to widen index,
CREATE NONCLUSTERED INDEX [IX_NAICSCode] ON [dbo].[SubCategoryNAICSCode]
(
[NAICSCode] ASC,[SubCategoryID]
)
It won't effect any thing
And you say that why?
If you look at the execution plan, 80% of the cost is in a key lookup because that index is not wide enough. Hence, widen that index and 80% of the cost of the query disappears.
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 22, 2010 at 10:47 pm
Thanks Paul White NZ and Gail Shaw, After removing Hint and adding INCLUDE INDEX,
CREATE NONCLUSTERED INDEX [IX_SubCategoryNAICSCode] ON [dbo].[SubCategoryNAICSCode]
(
[NAICSCode] ASC
)
INCLUDE ( [SubCategoryID]) 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]
sql works very fast.
But I find another query becomes slow,
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 '%z%' and ind.industryname like 'Franchising%'
It takes 40 Seconds.
But when I remove, and ind.industryname like 'Franchising%',
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 '%z%'
It takes 0.01 Seconds.
It really hurting me.
Please Help me.
Execution Plan is attached.
September 22, 2010 at 11:08 pm
abcim (9/22/2010)
It takes 40 Seconds.But when I remove, and ind.industryname like 'Franchising%',
It takes 0.01 Seconds.
Because the column IndustryName doesnt have any index , create an index on this column, to improve the performance.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 22, 2010 at 11:14 pm
There are only six rows in Industry table.
When I add a new index i get IndustryName, my execution time decline from 40 to 21 Sec.
But 21 Sec is also huge.
New Execution plan is attached
September 22, 2010 at 11:23 pm
What's the definition of the index [IX_BusinessData_CityState]?
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 22, 2010 at 11:25 pm
GilaMonster (9/22/2010)
What's the definition of the index [IX_BusinessData_CityState]?
/****** Object: Index [IX_BusinessData_CityState] Script Date: 09/23/2010 10:19:51 ******/
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]
it containg data [City, State ] format
Thanks
September 22, 2010 at 11:25 pm
Exec plan shows index scan and key lookup on BusinessData.
so create non clus index on BusinessData
(
[NAICS Code],
[Business name],
CityState)
But one concern , bdf.* in select , Do you really need it?. it is causing key lookup.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply