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 question

Author  Topic 

lamujerdetuhermano10
Yak Posting Veteran

75 Posts

Posted - 2008-09-04 : 19:17:20

I have index CallID as unique clustered index.

When I run a Select statement it takes like 3 minutes: I cleared the cache before running.

Select distinct CallID from CallLog where CallDesc like '%0d0d0857%' order by CallID

Then i index other columns on the table which is not in the select statement
I run the query again and it takes like 30 sec. I cleared the cache before running.

Anyone care to explain? Why indexing other columns which are not being using in the select statement makes it run faster?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-04 : 19:31:18
What does the execution plan show for each? What version of SQL Server are you using?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

lamujerdetuhermano10
Yak Posting Veteran

75 Posts

Posted - 2008-09-04 : 19:57:44
before answering you. can i create an index on a text field? if not. how can i perform tune it?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-04 : 20:02:03
Before answering you, I want to know the answer to my questions.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

lamujerdetuhermano10
Yak Posting Veteran

75 Posts

Posted - 2008-09-04 : 20:50:04
i was testing in SS2005. But the table is from SS2000.
Looks like its OK now. But I want to know how can i improve the data type from text on SS2000? or any way to improve the speed?
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-09-05 : 01:54:39
Had you not updated the statistics?

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com
Go to Top of Page

lamujerdetuhermano10
Yak Posting Veteran

75 Posts

Posted - 2008-09-05 : 14:28:29
jackv Yes

i will like to know how can i improve a text data type? if i can't index it. what else i can do ? the damn statement is taking minutes to run
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-09-05 : 14:40:57
Text is deprecated in SQL Server 2005, use varchar(max)

Jim
Go to Top of Page

lamujerdetuhermano10
Yak Posting Veteran

75 Posts

Posted - 2008-09-05 : 14:53:29
im on sql server 2000
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-09-05 : 15:11:41
Sorry, try varchar(8000) ?

Jim
Go to Top of Page

lamujerdetuhermano10
Yak Posting Veteran

75 Posts

Posted - 2008-09-05 : 16:20:35
need larger data type.

anyother wayt to fix it make it faster? anyway it doesnt matter. just give me inputs.
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-09-06 : 07:06:32
Have you considered full text indexing?

--
Gail Shaw
SQL Server MVP
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-06 : 10:46:00
1) Read about how and where TEXT columns are stored in SQL Server 2000.
2) Read about indexing and TEXT (varchar max) columns. Do the 900 bytes limit for index in SQL Server 2000 ring a bell?
3) Read about LIKE operator, especially for queries starting with wildcard character and their use of indexes.
4) Now, if you still haven't found the information you want from Books Online, please tell us what you didn't understand.



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

lamujerdetuhermano10
Yak Posting Veteran

75 Posts

Posted - 2008-09-07 : 04:57:44
quote:
Originally posted by GilaMonster

Have you considered full text indexing?

--
Gail Shaw
SQL Server MVP




thanks
Go to Top of Page

lamujerdetuhermano10
Yak Posting Veteran

75 Posts

Posted - 2008-09-08 : 20:22:59
quote:
Originally posted by Peso

1) Read about how and where TEXT columns are stored in SQL Server 2000.
2) Read about indexing and TEXT (varchar max) columns. Do the 900 bytes limit for index in SQL Server 2000 ring a bell?
3) Read about LIKE operator, especially for queries starting with wildcard character and their use of indexes.
4) Now, if you still haven't found the information you want from Books Online, please tell us what you didn't understand.



[font=Courier New]E 12°55'05.63"




1) Good stuff
2) I'm on sql server 2000. I think this is for 2005
3) I know when starting with % won't use the index.
4) I think 1 solved my problem. I don't need to upgrade the box to 2005
Go to Top of Page
   

- Advertisement -