Reducing reads question
-
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).
the unique clustered index is on an identity field (1,2,3,4,5,6...)
the nonclustered index is on a getdate() field.
I read creating indexes helps reduce reads. But it's not happening.
Any ideas what is going on?
-
If you're selecting all the rows from a table, you'll read all the data pages. No index would help with that. If you were reading a subset of the columns and those columns were included in an index, the optimizer might choose to read the index rather than the table, but an all-rows all-columns select doesn't leave the optimizer much choice but to read all the data.
And then again, I might be wrong ...
David Webb -
Agreed.
Only thing that might make a tiny improvement would be to not force the execution plan to go out and find the fields names. By not specifying the column names (even if you specify EVERY column), there has to be a query of SYSCOLUMNS for each table to find out the columns.
As I said - wouldn't expect this to be much in terms of improvement, but it would cut down on the reads (of another table). No getting around reading AA like david pointed out.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
Did my own testing.
1-select * into table1 from table2 is the best
2-insert table1 select * from table2
The reads value went dramatically lower using the first statement.
-
select into assumes a new table (meaning table2 doesn't exist ebfore that statement.) No, it's not the same level of effort - that does make it easier (no existing records/page splits, indexes to worry about).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply