September 26, 2008 at 9:50 am
Hi,
I have a doubt on my query.can you please correct me if i am wrong?
My query is like this,
declare @table table
(
tid int identity(1,1) ,
tname varchar(20)
)
insert into @table
select 'aaa'
union
select 'bbb'
--
select * from @table
where tname = 'aaa'
when i run this query editor i found its doing the "Table Scan" rather than "index scan". i had a misconception that if i declare a identity column its by defult go for "Cluster index scan".
but i am wrong..i need ur help...but when i change my query like this below
declare @table table
(
tid int identity(1,1) primary key ,
tname varchar(20)
)
insert into @table
select 'aaa'
union
select 'bbb'
--
select * from @table
where tname = 'aaa'
its doing index scan...
===========================================
My requirement like this, i am using inside my store proc the temp table and performing the operation as usual. inserts update and delete then finally updating my main table....
as per my skill set , please correct me. when u declare a index on a table and the table is highly transactional then it will perform slow..so in this situation should i use "primary key on identity column or not"
please let me know the correct concept so i can go ahead with the correct approach???
Thanks in advance..
Milu.
September 26, 2008 at 11:21 am
For 2 rows, or 10, or maybe 100, it will scan. It's not worth the effort to scan an index and then go get the data from the table. A scan is more efficient. As you add lots of data, if you index on the WHERE column, you should see seeks instead of scans.
That's a gross generalization, but without more information about what you actually will do (as opposed to this contrived example), it's hard to give more advice.
September 26, 2008 at 12:05 pm
Milu (9/26/2008)
when i run this query editor i found its doing the "Table Scan" rather than "index scan". i had a misconception that if i declare a identity column its by defult go for "Cluster index scan".
In SQL Server 2005, merely creating an identity column does NOT automatically create an index on that column. Therefore, in this case, your query is doing a "Table scan" rather than an "index scan" because there is no index to use.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 26, 2008 at 12:14 pm
If you actually have enough rows in the table variable to justify an index (which means over 1000 if I remember correctly), then you're better off using a temp table than a table variable. You can add indexes to a temp table, just like a regular table.
Yes, adding index will slow down insert/update/delete operations, but it will (if done correctly) speed up selects, and it can end up speeding up update/delete commands if they have Where clauses that can benefit from the index. (Total speed-up can be larger than total slow-down in those cases.)
A few indexes don't usually slow down a table enough to worry about. And they can speed up selects and selective update/deletes so much that they more than make up for it.
Of course, again, this only applies if there are enough rows to be worth indexing in the first place.
- 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
September 26, 2008 at 1:40 pm
One other misconception. A clustered index scan is not better than a table scan. It's the same thing. It's called a table scan when run on a heap (table without clustered index) and a clustered index scan when run on a table with a clustered index. It's a complete read of all the pages in 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 26, 2008 at 11:33 pm
hi gila wat need to be done if there is a clustered index create on a column and see index scan....
September 27, 2008 at 12:29 am
Guys, Apology for late reply...
Let me go through each and individual comments for more details and better understanding..
Steve:
--------------------------------------------------------------
That's a gross generalization, but without more information about what you actually will do (as opposed to this contrived example), it's hard to give more advice.
Steve for more details for you, I am a having a proc called as "SampleTableVariable" .
In side that i declare a table variable "@TempEmployee" with 5 columns wid one identity columns.
@TempEmployee table contains 100,000 records from the existing Employee Table. I am going to update EmpDeptID as per DeptID in Dept Table. and Delete records SectionID who is not matching my Parent DeptSection Table. then Updating the Monthly gross salary in Salary Column from MainSalary table and some calculation happen.
Now you decide should i declare a Identity column wid primary key or not?
hope you got my requirements..????
rbarryyoung
--------------------------------------------------
In SQL Server 2005, merely creating an identity column does NOT automatically create an index on that column. Therefore, in this case, your query is doing a "Table scan" rather than an "index scan" because there is no index to use.
rbarryyoung i am not clear what you are trying to say to me, its not automatically create one index in sql 2005? my qst is is it creating is SQL 2000 or SQL 2008??
2nd: in the above case, what would you suggest just tell me if my table is going for a table scan, how would you handle this type of situation.
GSquared
-----------------------------------------------------------------
Of course, again, this only applies if there are enough rows to be worth indexing in the first place.
GSquared let me give you my performance for my procedure, i am agree wid you...
when i applied the @TempEmployee wid out primary key its taking 377 ms even if going for table scan, but when i applied wid primary key then it took for me 557 ms even if going for Index scan..
Now tell me what should be correct approach, i meab to say Table scan or Index scan...
GilaMonster
----------------------------------------------------------------------
One other misconception. A clustered index scan is not better than a table scan. It's the same thing. It's called a table scan when run on a heap (table without clustered index) and a clustered index scan when run on a table with a clustered index. It's a complete read of all the pages in the table.
GilaMonster now you tell me "Index is good for me or not in this situation. If i am not wrong Best SQL Prof in this IT industry uses Table valuable to handle the this type operation inside Procedure. Is not it? If so...now you tell me a Index Tuning Specialist what should i do..should i go for the table variable or Temp table....???
Saby
---------------------------------
hi gila wat need to be done if there is a clustered index create on a column and see index scan....
Saby I think you are not into my topic...Am i rite??
Waiting for all your valuable Inputs........
Please correct me if i am wrong..and I need the correct approach rather than telling the it depends on the situation...
Waiting for you all reply..
Thanks...
Milu.
September 27, 2008 at 12:50 am
Milu (9/27/2008)
rbarryyoung i am not clear what you are trying to say to me, its not automatically create one index in sql 2005? my qst is is it creating is SQL 2000 or SQL 2008??
An identity column is just a column that auto-increments. It has never meant that an index will be created automatically.
The only time SQL will create an index without been issues a CREATE INDEX statement is when primary or unique constraints are created. That's because they are enforced by an index.
If so...now you tell me a Index Tuning Specialist what should i do..should i go for the table variable or Temp table....???
Temp table. That's way too many rows to put into a table variable. Table variables don't keep statistics (indexes or no indexes) and so the optimiser will always guess there is only one row. If there's a lot (100 +) than that bad guess will result in bad execution plans.
Also, if you're filtering on the tname column, that's where you need the index, not on the identity column at all.
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 27, 2008 at 2:14 am
Gila,
If i am not wrong, you can't creat an index in Table variable...rite explicitly...rite??
then wht u will do now??/
if table is highle highly transactional..then whould u go for Temp table or not??
Waiting Gail....
Milu.
September 27, 2008 at 3:47 am
Milu (9/27/2008)
Gila,If i am not wrong, you can't creat an index in Table variable...rite explicitly...rite??
then wht u will do now??/
Only as part of a primary key. That's one of the limitations or table variables
if table is highle highly transactional..then whould u go for Temp table or not??
Depends. What's it doing and why is there a need for a temp table at all?
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 29, 2008 at 5:04 am
Milu 🙂
I can say its depends on the time when you develop your codes.
if you are using the table for highly transactional then you can go for Table variable rather than Temp Table inside store procedure because it will be faster.
But if you are going for searching type procedure and heavy condition for searching, I mean to say for Data Selection then creates the Index on the columns used in the where clause in TempTable or you can also go for an Index View which will help you more rather than Table Variable.
If your data is huge then you just compare and consider with both case and Implements the same. I can suggest you this much. I just added few lines with Gails Post.
Cheers!
Sandy.
--
September 29, 2008 at 7:51 am
Milu (9/27/2008)
rbarryyoung
--------------------------------------------------
In SQL Server 2005, merely creating an identity column does NOT automatically create an index on that column. Therefore, in this case, your query is doing a "Table scan" rather than an "index scan" because there is no index to use.
rbarryyoung i am not clear what you are trying to say to me, its not automatically create one index in sql 2005? my qst is is it creating is SQL 2000 or SQL 2008??
What I am trying to say to you is that your first example did not use an index because you did not Create an index.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 29, 2008 at 1:37 pm
Sandy (9/29/2008)
if you are using the table for highly transactional then you can go for Table variable rather than Temp Table inside store procedure because it will be faster.
Why will it be faster?
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 30, 2008 at 10:49 am
Hi All, (Gail, Sandy, rbarryyoung)
Thanks you all for your valuable inputs regarding the Index on Temp table and Table variable Concepts.
Now I need to see Sandy's Reply for Gails Post.
also i need Steve and rbarryyoung look on this post if you come across this type situation.
Gail, I really appreciate your way of explanation on topic. I am impressed the way your way of approach..Sandy too...and also i have same question for sandy what gail asked to Sandy in previous post..
Luvs,
Milu.:)
September 30, 2008 at 11:30 pm
Gail & Meely,
I think Gail can better answer for this,
Well, As per my observation and understanding the Table Variable concepts. Its does not make more difference if primary key is define on the table variable as clustered index scan is same as table scan.
but here you can point out one thing, If your table variable data is highly transactional then your primary key on table variable will decrease your performance due to index which i recently observed so i prefer not to use primary key on table variable.
If you think as a layman view the index is necessary when we need best performance from the query by giving best execution plan to the query.
As per Temp Table concepts, You can go for Temp Table in time of requirements. like Some times your data is huge and you need the multiple index to make your query faster. then you can create Temp table and create Index as per you and it will help you in the Performance.
Frankly speaking to all that when you are creating a Temp Table its a additional burden for the server to create a Temp table on Tempdb and maintains the uniqueness between sessions. I mean to say If one Temp table present in your Store Procedure and same time calling by more users then how it will define inside the server memory, just consider this matter too another thing is Temp Table is following the traditional approach which follows the locking and memory from the server itself during the process. and its not upto the scope too. that is why MICROSOFT comes with new concept like "Table Variable and View Variable and CTE....etc....".
I can say one thing here, If you clearly analysis your topic as I told in my previous post its depends on what condition basis you are going to work.
Meely, This much I can say to you.
Gail correct me if I am wrong.
Cheers!
Sandy.
--
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply