Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
funketekun
Constraint Violating Yak Guru
491 Posts |
 Posted - 2007-08-24 : 09:32:53
|
| I'm trying to insert all the rows from a table to a new table.(insert A select * from AA)The reads on Profiler shows ar really high value (10253548).First I created a unique clustered index and the reads shows (3258445), then I created a non clustered index expecting to have lower reads. Instead the reads shows (10253548).I read creating indexes helps reduce reads. But it's not happening.Any ideas what is going on?=============================http://www.sqlserverstudy.com |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-24 : 09:36:30
|
It depends on the query!If you have some calculations over the columns being indexed, no index is going to help you, and you get a table scan with many reads...
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
funketekun
Constraint Violating Yak Guru
491 Posts |
Posted - 2007-08-24 : 10:43:09
|
| the unique clustered index is on an identity field (1,2,3,4,5,6...)the nonclustered index is on a getdate() field.=============================http://www.sqlserverstudy.com |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-24 : 14:06:14
|
Ketö schör! -- This is hungarianIt might help if you posted the query.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
funketekun
Constraint Violating Yak Guru
491 Posts |
Posted - 2007-08-24 : 16:32:34
|
| query is on the top....i was testing with those 2 tables A and AA=============================http://www.sqlserverstudy.com |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-08-24 : 17:36:55
|
| indexes help reduce reads on selects. inserts and updates screw up indexes causing fragmentation._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-08-24 : 18:12:51
|
| Funketon - your query is select * from table. No where clause. A "read" is an 8KB page read from the disk. Since you specify that you wanted the entire table the number of reads will be the number of pages the table takes up on the disk.No doubt you have read that optimisation involves reducing the number of reads. But there is no optimising "select * from table". The only way I can think of to reduce the reads for this one query is to fill the pages as densly as possible (a rebuild with fillfactor of 100) but this is really not something to do for this one query.You would probably be best reading up on table & index structures to get more of a background understanding. You don't just stick indexes on tables and find that they magically shrink. |
|
|
|
|
|
|
Reducing reads question