sql server - sql performance by complex queries -


i have table below:

create table metaltemprature(     idmetaltemprature int     rawtime bigint not null,     metal nchar(7) not null,     color nchar(5) not null,     temp float not null) 

and blow index:

primary key clustered  (     idmetaltemprature asc )with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on) on primary ) on primary  create nonclustered index nonclusteredindex1112 on metaltemprature (     rawtime desc )with (pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, drop_existing = off, online = off, allow_row_locks = on, allow_page_locks = on) on primary 

when run query take 0 sec that:

select  count(*)   metaltemprature   rawtime < 4449449575 ,  rawtime > (4449449575 -10000000) ,  metal = 'iron'; 

but when put query under other select below

select      select  count(*)           metaltemprature           rawtime < other.rawtime ,  rawtime > (other.rawtime -10000000) ,  metal = 'iron'; other_table_only_one_row other; 

this take 60 sec (when other.rawtime 4449449575 , result of both queries same)why?

select * other_table_only_one_row other, (select  count(*)           metaltemprature           rawtime < other.rawtime ,  rawtime > (other.rawtime -10000000) ,  metal = 'iron') cnt 

place from section execute once


Comments