which answer is right?
-
I have a table containing production information that your users query frequently, They specifically use this query most often (that is only use name to search in the where condition): SELECT Name,Description,Vendor,Instock,Price FROM Products where Name='name' Have a nonclustered index on this table on the Name column,but your users are complaining the query is still too slow,what can you do to speed it up? A、modify the index to include the Description,vendor,Instock, and price columns as nonkey columns. B、Create a new nonclustered index on the Description,vendor,Instock, and price as nonkey columns. C、Create a new clustered index on the Description,vendor,Instock, and price as nonkey columns. D、You can't do anything to speed up this query. Database is MS SQL SERVER. Above four choices, which answer is right?please tell the reason.Thanks
-
I have a table containing production information that your users query frequently, They specifically use this query most often (that is only use name to search in the where condition): SELECT Name,Description,Vendor,Instock,Price FROM Products where Name='name' Have a nonclustered index on this table on the Name column,but your users are complaining the query is still too slow,what can you do to speed it up? A、modify the index to include the Description,vendor,Instock, and price columns as nonkey columns. B、Create a new nonclustered index on the Description,vendor,Instock, and price as nonkey columns. C、Create a new clustered index on the Description,vendor,Instock, and price as nonkey columns. D、You can't do anything to speed up this query. Database is MS SQL SERVER. Above four choices, which answer is right?please tell the reason.Thanks
Surely its obvious? Look at the select SELECT Name,Description,Vendor,Instock,Price FROM Products where Name='name' and the options: B and C - why will creating these indices help? The search is on name only. D - of course it can be speeded up This leaves A. By including the other columns on the non-clustered index only the index will be read, therefore saving the read from the underlying table. Hope this explains it, it really is a bit of a no-brainer question.
Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP
-
Surely its obvious? Look at the select SELECT Name,Description,Vendor,Instock,Price FROM Products where Name='name' and the options: B and C - why will creating these indices help? The search is on name only. D - of course it can be speeded up This leaves A. By including the other columns on the non-clustered index only the index will be read, therefore saving the read from the underlying table. Hope this explains it, it really is a bit of a no-brainer question.
Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP
You know it would never occur to me to add these other fields into the index, I would leave it at D. The penalty of AED on that table/index would outweigh the benefits I would have thought. Taking that advice to the next level and you get something like - every table that is in a select query should carry an index with every field in it!
-
You know it would never occur to me to add these other fields into the index, I would leave it at D. The penalty of AED on that table/index would outweigh the benefits I would have thought. Taking that advice to the next level and you get something like - every table that is in a select query should carry an index with every field in it!
Mycroft Holmes wrote:
The penalty of AED on that table/index would outweigh the benefits I would have thought.
Based on the OP "I have a table containing production information that your users query frequently" implies to me that there are many more reads than writes, so once the index is created there is little overhead - however, based on the info given, that is not certain so you may be correct. In real life you make a judgement call based on your knowledge of the system.
Mycroft Holmes wrote:
I would leave it at D
Not if you have a load of irate users on your back :) In reality, the guy wanted to know the answer to an interview/exam question, and I would put my money on adding the other columns to the index being what they were looking for.
Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP
-
Surely its obvious? Look at the select SELECT Name,Description,Vendor,Instock,Price FROM Products where Name='name' and the options: B and C - why will creating these indices help? The search is on name only. D - of course it can be speeded up This leaves A. By including the other columns on the non-clustered index only the index will be read, therefore saving the read from the underlying table. Hope this explains it, it really is a bit of a no-brainer question.
Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP
Thank you for your kindly help!
-
I have a table containing production information that your users query frequently, They specifically use this query most often (that is only use name to search in the where condition): SELECT Name,Description,Vendor,Instock,Price FROM Products where Name='name' Have a nonclustered index on this table on the Name column,but your users are complaining the query is still too slow,what can you do to speed it up? A、modify the index to include the Description,vendor,Instock, and price columns as nonkey columns. B、Create a new nonclustered index on the Description,vendor,Instock, and price as nonkey columns. C、Create a new clustered index on the Description,vendor,Instock, and price as nonkey columns. D、You can't do anything to speed up this query. Database is MS SQL SERVER. Above four choices, which answer is right?please tell the reason.Thanks
I would keep the index on Name and create another index on Name,Description,Vendor,Instock,Price * This should solve any Bookmark problem - have you ran execution plan on your query? if this is a heavily 'write' table, you will pay the price when rebuilding indexes
-
Mycroft Holmes wrote:
The penalty of AED on that table/index would outweigh the benefits I would have thought.
Based on the OP "I have a table containing production information that your users query frequently" implies to me that there are many more reads than writes, so once the index is created there is little overhead - however, based on the info given, that is not certain so you may be correct. In real life you make a judgement call based on your knowledge of the system.
Mycroft Holmes wrote:
I would leave it at D
Not if you have a load of irate users on your back :) In reality, the guy wanted to know the answer to an interview/exam question, and I would put my money on adding the other columns to the index being what they were looking for.
Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP
Ashfield wrote:
In reality, the guy wanted to know the answer to an interview/exam question
It definitely feels like a theory question that has no basis in reality. I would have trouble answering D as I know I don't know everything and I hate having to answer that to irate users, so I would be looking for the E option - more research required.