SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 index question
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

lamujerdetuhermano10
Yak Posting Veteran

75 Posts

Posted - 09/04/2008 :  19:17:20  Show Profile  Reply with Quote

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

USA
36907 Posts

Posted - 09/04/2008 :  19:31:18  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 - 09/04/2008 :  19:57:44  Show Profile  Reply with Quote
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

USA
36907 Posts

Posted - 09/04/2008 :  20:02:03  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 - 09/04/2008 :  20:50:04  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
2058 Posts

Posted - 09/05/2008 :  01:54:39  Show Profile  Visit jackv's Homepage  Reply with Quote
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 - 09/05/2008 :  14:28:29  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 09/05/2008 :  14:40:57  Show Profile  Reply with Quote
Text is deprecated in SQL Server 2005, use varchar(max)

Jim
Go to Top of Page

lamujerdetuhermano10
Yak Posting Veteran

75 Posts

Posted - 09/05/2008 :  14:53:29  Show Profile  Reply with Quote
im on sql server 2000
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 09/05/2008 :  15:11:41  Show Profile  Reply with Quote
Sorry, try varchar(8000) ?

Jim
Go to Top of Page

lamujerdetuhermano10
Yak Posting Veteran

75 Posts

Posted - 09/05/2008 :  16:20:35  Show Profile  Reply with Quote
need larger data type.

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

Edited by - lamujerdetuhermano10 on 09/05/2008 16:21:44
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 09/06/2008 :  07:06:32  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
Have you considered full text indexing?

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

SwePeso
Patron Saint of Lost Yaks

Sweden
30241 Posts

Posted - 09/06/2008 :  10:46:00  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 - 09/07/2008 :  04:57:44  Show Profile  Reply with Quote
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 - 09/08/2008 :  20:22:59  Show Profile  Reply with Quote
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

Edited by - lamujerdetuhermano10 on 09/08/2008 20:23:55
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000