August 19, 2003 at 12:55 pm
Can anyone shed some light on this problem:
These two select statements return the exact same recordset:
select * from t1
where date>= '6/1/2003' and date < '6/2/2003'
select * from t1
where convert(varchar,date,101) = '6/1/2003'
But, when I try to use these select statements to insert into
another table, t2, where t2 has a clusered index covering 3 columns one of which is the date column, the two queries do not perform the same.
METHOD 1:
insert into t2
select * from t1
where date>= '6/1/2003' and date < '6/2/2003'
METHOD 2:
insert into t1
select * from t
where convert(varchar,date,101) = '6/1/2003'
In the execution plan, when doing method 1, if the resulting recordset has a large number of rows, then lots of time is spent sorting the resulting recordset, if there are few rows, then minimal time is spent doing the sort operation.
But when doing method 2, there is no sort operation in the execution plan. How can this be?
PS. If anyone knows of a good way to copy and paste an execution plan, let me know, I can add it here.
Thanks,
-J
August 19, 2003 at 3:28 pm
you can prefix your code with:
set showplan_text on
go
BTW: in the 2nd INSERT did you mean to have different source and destination tables than in the first one?
Cheers,
- Mark
Cheers,
- Mark
August 20, 2003 at 9:42 am
jraha,
My guess is that the first statement uses an index. The second query is forced to do a table scan and wouldn't need to sort anything. The CONVERT requires the table scan which is why I try to always use the first method.
Hope this helps.
Guarddata-
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply