make query logicaly faster - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

make query logicaly faster

Author

Topic

marconi8
Yak Posting Veteran

73 Posts

Posted - 2003-05-08 : 07:23:09

for example let take this simple query

 
SELECT a.ID,
       P_IMAGE = CAST(b.ID AS VARCHAR)+b.IMAGE_EXT	

FROM DOSKA_RAZDEL a LEFT JOIN MAIN_IMAGE_LIST b
			   ON a.ID=b.OVNER_ID

a.ID, is clustered index, primary key ( all next execution plan results connects to this a.ID )
===================================================

using this query, ine ep (execution plan ) i get

1) clustered index scan
2) cost 43%

in some manuals i have readed about clustered indexes, there is writed that clustered index seek is more faster than full scan.
then i add where statement

 
SELECT a.ID,
       P_IMAGE = CAST(b.ID AS VARCHAR)+b.IMAGE_EXT	

FROM DOSKA_RAZDEL a LEFT JOIN MAIN_IMAGE_LIST b
			   ON a.ID=b.OVNER_ID
WHERE a.ID != 0

and in ep plan now is writed clustered index seek
2) cost 150%

i always must try to use seek instead scan for better perfomance, it is right ? or ...

2. cost x% value must be more for better perfomance ?

Teroman
Posting Yak Master

115 Posts

Posted - 2003-05-08 : 11:31:03

A seek is faster than a scan because it grabs a limited number of rows from the index and works on them, while a scan grabs the whole lot and works those.

In the first example you have no where clause so you will need all the rows from table a.

If you put in your where clause

where a.id = 99

then you would get an index seek as the optimiser could get just that row and then do the join to table b.

If you need all the rows from the tables then a scan on the index is as good as you are going to get.

marconi8
Yak Posting Veteran

73 Posts

Posted - 2003-05-08 : 12:05:30

re>If you need all the rows from the tables then a scan on the index is as good as you are going to get

sorry, i think that i am realy little stupid, hm how can i miss this thing, eh :(

ok, i have some question about cost,

for example i have a table

create table NN
(
ID primary key
)

in table NN are inserted values
ID 1
ID 2

i make this select

select * from NN
where ID = 1

after this to me is returned only one row, but in execution plan i something cannot understand...

in my select statement is used clustered index, because id is primary and by default id = clustered

in execution plan i see that was performed clustered index seek, it is good, but why cost = 0%

my sum(I/O cost + CPU cost) = 13

and execution is only one,

then why total cost = 0%, what i doing wrong

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources