Index - 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

General SQL Server Forums

New to SQL Server Programming

Index

Author

Topic

kiruthika
Yak Posting Veteran

67 Posts

Posted - 2008-02-04 : 01:44:24

Hi all,
I want to know the performance of my querey.So i posted here some sample data.can anybody tell it to me which one is better?

Before creating index on my table:
----------------------------------
set statistics io on
select message,id from languageoption where messagetype='Alert'
set statistics io off

result:
Scan count:1,logical reads:4,physical reads:0,read ahead reads 4
and table scan cost is:0.040283

After creating non-clustered index on my table:
----------------------------------------------
create index myindex on languageoption(messagetype)

set statistics io on
select message,id from languageoption where messagetype='Alert'
set statistics io off

result:
Scan count:1,logical reads:174,physical reads:2,read ahead reads 0
and table scan cost is:0.000014

Whether i don't know it is best to use the table with index or not?
I dont know much details about index.
Thanks in advance!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-04 : 02:05:18

How may records are there in the LanguageOption table? What's the size of MessageType column?
How many bytes are each record?

E 12°55'05.25"
N 56°04'39.16"

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-02-04 : 02:12:05

Hi,

check the URL
http://www.sqlteam.com/article/sql-server-indexes-the-basics

try placing the index on Message column also and see the differences in scan cost.

kiruthika
Yak Posting Veteran

67 Posts

Posted - 2008-02-04 : 02:27:13

quote:
Originally posted by Peso

How may records are there in the LanguageOption table? What's the size of MessageType column?
How many bytes are each record?

E 12°55'05.25"
N 56°04'39.16"

Hi Peso!
Messagetype varchar(50),id int,message varchar(100)
Now the table is having 300 rows only.In future it may increase.
I dont know these information is enough or not
Thanks!

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-02-04 : 03:00:06

put an index on ID column

Vinod
Even you learn 1%, Learn it with 100% confidence.

kiruthika
Yak Posting Veteran

67 Posts

Posted - 2008-02-04 : 03:46:11

quote:
Originally posted by sunsanvin

put an index on ID column

Vinod
Even you learn 1%, Learn it with 100% confidence.

Hi vinodh!
I created index on id column
create index myindex on languageoption (id)
set statistics io on
select message,id from languageoption with(index(myindex)) where messagetype='Alert'
set statistics io off

output
----------
Table 'languageoption'. Scan count 1, logical reads 368, physical reads 4, read-ahead reads 0.cost is 0.000014

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-02-04 : 04:37:51

i think it is fine

Vinod
Even you learn 1%, Learn it with 100% confidence.

kiruthika
Yak Posting Veteran

67 Posts

Posted - 2008-02-04 : 07:30:02

quote:
Originally posted by sunsanvin

i think it is fine

Vinod
Even you learn 1%, Learn it with 100% confidence.

Thanks Vinod!
In the previous querey logical read is 368.Which one is better logical read value is low or high?and please give some details about the result.

kiruthika
Yak Posting Veteran

67 Posts

Posted - 2008-02-05 : 00:47:24

quote:
Originally posted by kiruthika

quote:
Originally posted by sunsanvin

i think it is fine

Vinod
Even you learn 1%, Learn it with 100% confidence.

Thanks Vinod!
In the previous querey logical read is 368.Which one is better logical read value is low or high?and please give some details about the result.

Hi all,
Please any help would be appreciated.I'm waiting for your reply

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-05 : 01:33:41

Since the table is so small, I think the query engine decides that it is more efficient to scan the table than to incorporate an index.

E 12°55'05.25"
N 56°04'39.16"

kiruthika
Yak Posting Veteran

67 Posts

Posted - 2008-02-05 : 02:08:41

quote:
Originally posted by Peso

Since the table is so small, I think the query engine decides that it is more efficient to scan the table than to incorporate an index.

E 12°55'05.25"
N 56°04'39.16"

Thanks Peso!
could you please explain about logical reads ? and if it is low or
high for an optimized querey? Sorry for asking so much questions
because i dont know much details....

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-05 : 03:05:33

A page in SQL Server is 8060 bytes of available storage.

If you have to columns (50 bytes plus 100 bytes equals 150 bytes) you can store (depending on fill factor) approx 53 records per page.
And since you have 300 records, you need 300 / 53 equals 6 pages of storage.

When you create an index, the size of the index can vary very much depending on the granularity of the column data.
If the column you index is unique, then you would need another 6 pages of index data!
Then the query engine may decide that "I can scan the table anyway".

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources