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 CallIDThen i index other columns on the table which is not in the select statementI 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 |
|
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? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
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? |
|
|
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 |
|
|
lamujerdetuhermano10
Yak Posting Veteran
75 Posts |
Posted - 2008-09-05 : 14:28:29
|
jackv Yesi 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 |
|
|
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 |
|
|
lamujerdetuhermano10
Yak Posting Veteran
75 Posts |
Posted - 2008-09-05 : 14:53:29
|
im on sql server 2000 |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2008-09-05 : 15:11:41
|
Sorry, try varchar(8000) ?Jim |
|
|
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. |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-09-06 : 07:06:32
|
Have you considered full text indexing?--Gail ShawSQL Server MVP |
|
|
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" |
|
|
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 ShawSQL Server MVP
thanks |
|
|
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 stuff2) I'm on sql server 2000. I think this is for 20053) 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 |
|
|
|