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.
| Author |
Topic |
|
ereader
Yak Posting Veteran
50 Posts |
 Posted - 2002-12-02 : 02:48:43
|
| Hi! All I have a table from where i'm retrieving the data from 4 columns col1 500 varchar col2 500 varchar col3 200 varchar col4 200 varchar The query is select * from tb1 Where ((((col1 like '%xyz%') or (col2 like '%xyz%')) and ((col3 like '%abc%') or (col4 like '%abc%'))) or (((col3 like '%xyz%') or (col4 like '%xyz%')) and ((col1 like '%abc%') or (col2 like '%abc%')) col6='1')) The current information regarding the query I/O Cost 3.75 CPU 0.200 which takes 7 sec on my desktop it returns 550 rows after filtering the data from 200000 rows of data How should i minimize the I/O cost and increase the performance Indexes on the table are: ID1 nonclustered, unique, unique key located on PRIMARY on PK_Col1 and one clustered index on the col5(FK)-- varchar(15) Should i use Indexed View? How Thanks |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-12-02 : 08:01:31
|
| There must be a secret web site or cult somewhere hailing indexed views as the best thing since sliced bread...In your case, because you are doing pattern matches, indexes and indexed views will be very little help. The big problem is that you have the % wildcard at the beginning (%abc% instead of abc%). If you truly need to have that kind of matching, then indexing that column will be of no use. You can't index on part of a column's value (you can create indexes on computed columns and expressions in SQL Server 2000, but they won't help you here...you'd have to re-create the index whenever the search string changes)You may be able to get better performance out of full-text indexing, as long as you are searching for words and not substrings.Can you post the EXACT table structure (PLEASE PLEASE PLEASE don't abbreviate it or generalize it) What does col1-col4 hold? If they're the same kind of data, you should normalize the table so that those columns don't repeat. Then you only have to search one column; indexing still won't help, but the table can store more rows on one page, which will cut down on the amount of I/O needed to satisfy the query. |
 |
|
|
|
|
|
Query to be optmize