August 25, 2011 at 5:13 pm
Hi i have a table A with 4 million records which doesn't have any keys (heap) and when i write a select * then it takes 4:30 secs to execute the query.
so i had created one clustered index on unique values column and non -clustered index on couple of other column which i use in my where clause on a TEST TABLE B which has same amount of data.
When i execute both .. i see the same execution time for both.
Why i see the same execution time on my test table B even though i had created clustered and non-clustered indexes???? I saw the execution plan too and it is using those indexes.
What i should do if i want to bring out the difference in execution time on my test table B ??
Thanks ,
SAM
August 25, 2011 at 5:50 pm
That's just too little information. We'd need to see the execution plans, the table schema, and the indexes you built to be able to assist you in troubleshooting a particular issue.
Check out the second link down on the left in my signature, it'll walk you through what we'll need to assist.
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
August 26, 2011 at 5:53 am
SELECT * FROM TABLE without any where clause means that every single row on the table has to get retrieved. Putting an index on it, clustered or not, doesn't change the fact that every single row has to get retrieved. And when retrieving every single row, the only way to make it faster is to increase your disk speed. The question is, why are you trying to query every single row from the table?
----------------------------------------------------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
August 26, 2011 at 9:51 am
Hi,
Thanks for the information.
I am making use of a where clause for a date feild between two days. I placed a non-clustered index on that date field column in TABLE B since data is not unique and TABLE A is a heap.
When i ran the same query in TABLE A and in TABLE B. I see the same execution time ???
What would be the reason ???
August 26, 2011 at 9:53 am
Not enough information.
Please post table definitions, index definitions and execution plan, as per http://qa.sqlservercentral.com/articles/SQLServerCentral/66909/
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
August 26, 2011 at 10:11 am
@Kraig
I had enclosed a word doc with execution plans. Let me know if you need any more info ??
SAM.
August 26, 2011 at 10:19 am
Didn't ask for a word doc with a picture of the exec plan. Please post the actual execution plan. See the article I previously referred you to.
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
August 26, 2011 at 10:24 am
Actual Exec plans...
August 26, 2011 at 10:27 am
Cool, now the table and index definitions please? And maybe the query that you were running?
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
August 26, 2011 at 10:41 am
I had enclosed them in the word doc.
August 29, 2011 at 10:13 am
Can Any please respond. I still didn't get any answers.
Thanks,
SAM.
August 29, 2011 at 10:20 am
Create an index with business_date as the leading column, and stop using select *
The nonclustered index that you had was useless because business_date was not the leading column, and that was the only column that the query filtered on.
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
August 29, 2011 at 10:28 am
I need to select all columns so you suggest me to write a select statement with all columns instead of select * ??
and the reason i created non-clustered on multiple columns because i use other columns too(station,suppliername) in some other where clause along with this business_date.
What do you suggest in this case??
August 31, 2011 at 1:06 pm
sam-1083699 (8/29/2011)
I need to select all columns so you suggest me to write a select statement with all columns instead of select * ??and the reason i created non-clustered on multiple columns because i use other columns too(station,suppliername) in some other where clause along with this business_date.
What do you suggest in this case??
Sam,
The reason Gail advised against SELECT * was that in most cases this is lazy SQL coding. Most of the time you don't really need SELECT *, but a small subset of the columns in the table. If you really need all columns returned, then by all means use SELECT *.
If you need those other columns as an index for other queries, then keep that index. Do what Gail suggested and create another index on the date column. If it is selective enough, then the query should use that index.
Todd Fifield
August 31, 2011 at 1:13 pm
sam-1083699 (8/29/2011)
I need to select all columns so you suggest me to write a select statement with all columns instead of select * ??
Yes. Select the absolute minimum of columns you need. It's rare for a query to really need every single column. That's usually just a sign of laziness on the part of the developers
and the reason i created non-clustered on multiple columns because i use other columns too(station,suppliername) in some other where clause along with this business_date.
What do you suggest in this case??
Exactly what I already suggested. An index on the business_date column. Only on the business_date column. The existing index is useless for this query as the business_date is not the leading column.
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 17 total)
You must be logged in to reply to this topic. Login to reply