|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
|
Hello!
I’m using a statement with LIKE in the WHERE clause. Searching for rows containing a prefix (LIKE ‘MyPrefix%’) SQL Server seeks the index on that column – that’s fine. But if I want to search for a suffix (LIKE ‘%MySuffix’) an Index Scan is used and takes very long. Do I have any possibility that SQL Server does an Index Seek in case of LIKE ‘%MySuffix’? How can I optimize such a query? I also couldn’t find a use of FTS in this case, because CONTAINS only provides a <prefix term> but not a term for suffixes or am I wrong? Thank you all! What kind of data is in that column? Could you give a few examples?
ML --- http://milambda.blogspot.com/ The data type is NVARCHAR(50) and the columnn contains article names with
spaces, hyphens etc. There are about 370.000 rows in that table and also if the resultset contains only some rows (e.g. 20) an Index Scan is done (using a query with LIKE '%xxx' or LIKE '%xxx%'). Show quote "ML" wrote: > What kind of data is in that column? Could you give a few examples? > > > ML > > --- > http://milambda.blogspot.com/ If the need is great enough, you could consider storing another column
with the REVERSE() of the existing column, then querying with the REVERSE() of the search string, 'xiffuSyM%'. This could also be achieved with an indexed view, saving the space in the base table. Roy Harvey Beacon Falls, CT On Thu, 13 Jul 2006 05:06:02 -0700, RobRoma <RobR***@discussions.microsoft.com> wrote: Show quote >Hello! > >I’m using a statement with LIKE in the WHERE clause. Searching for rows >containing a prefix (LIKE ‘MyPrefix%’) SQL Server seeks the index on that >column – that’s fine. But if I want to search for a suffix (LIKE ‘%MySuffix’) >an Index Scan is used and takes very long. Do I have any possibility that SQL >Server does an Index Seek in case of LIKE ‘%MySuffix’? > >How can I optimize such a query? > >I also couldn’t find a use of FTS in this case, because CONTAINS only >provides a <prefix term> but not a term for suffixes or am I wrong? > >Thank you all! boy.. that's one helluva suggestion :)
EXCELLENT! Nice suggestion Harvey. Thanks.
And perhaps the reverse column would only have to be the last x characters. -- Show quoteArnie Rowland* "To be successful, your heart must accompany your knowledge." "Roy Harvey" <roy_har***@snet.net> wrote in message news:h8fcb2hmbn1dbh40mk9ls9o7qkdk9d7mif@4ax.com... > If the need is great enough, you could consider storing another column > with the REVERSE() of the existing column, then querying with the > REVERSE() of the search string, 'xiffuSyM%'. This could also be > achieved with an indexed view, saving the space in the base table. > > Roy Harvey > Beacon Falls, CT > > On Thu, 13 Jul 2006 05:06:02 -0700, RobRoma > <RobR***@discussions.microsoft.com> wrote: > >>Hello! >> >>I'm using a statement with LIKE in the WHERE clause. Searching for rows >>containing a prefix (LIKE 'MyPrefix%') SQL Server seeks the index on that >>column - that's fine. But if I want to search for a suffix (LIKE >>'%MySuffix') >>an Index Scan is used and takes very long. Do I have any possibility that >>SQL >>Server does an Index Seek in case of LIKE '%MySuffix'? >> >>How can I optimize such a query? >> >>I also couldn't find a use of FTS in this case, because CONTAINS only >>provides a <prefix term> but not a term for suffixes or am I wrong? >> >>Thank you all! Arnie Rowland wrote:
> EXCELLENT! Nice suggestion Harvey. Thanks. Arnie,> can you elaborate why do you think that adding a regular column for reverse is better than adding a computed column. Note that you will have to maintain your additional regular column in sync with the original one, and use up additional storage. On the other hand, a computed column is always in sync automatically, and it needs not any storage by itself, only the index on it needs storage. Makes sense? If you notice, the post he commented on (mine) does mention that "This
could also be achieved with an indexed view, saving the space in the base table." Which is effectively what you are asking about, and what you suggested around the same time in your own post. Roy On 13 Jul 2006 08:31:12 -0700, "Alexander Kuznetsov" <AK_TIREDOFSPAM@hotmail.COM> wrote: Show quote > >Arnie Rowland wrote: >> EXCELLENT! Nice suggestion Harvey. Thanks. >> > >Arnie, > >can you elaborate why do you think that adding a regular column for >reverse is better than adding a computed column. Note that you will >have to maintain your additional regular column in sync with the >original one, and use up additional storage. On the other hand, a >computed column is always in sync automatically, and it needs not any >storage by itself, only the index on it needs storage. Makes sense? ONLY a computed column in an indexed view.
-- Show quoteArnie Rowland* "To be successful, your heart must accompany your knowledge." "Alexander Kuznetsov" <AK_TIREDOFSPAM@hotmail.COM> wrote in message news:1152804672.682664.110690@b28g2000cwb.googlegroups.com... > > Arnie Rowland wrote: >> EXCELLENT! Nice suggestion Harvey. Thanks. >> > > Arnie, > > can you elaborate why do you think that adding a regular column for > reverse is better than adding a computed column. Note that you will > have to maintain your additional regular column in sync with the > original one, and use up additional storage. On the other hand, a > computed column is always in sync automatically, and it needs not any > storage by itself, only the index on it needs storage. Makes sense? > Not a problem, and good to ask for clarification so the OP isn't
confused -must less us! -- Show quoteArnie Rowland* "To be successful, your heart must accompany your knowledge." "Alexander Kuznetsov" <AK_TIREDOFSPAM@hotmail.COM> wrote in message news:1152807271.719386.18200@b28g2000cwb.googlegroups.com... > > Arnie, I misunderstood you - sorry for that > By the it's nature a - '%MyPrefix' will scan rather than seek. But ,
something like 'a%Myprefix' will seek.This is because in the '%MyPrefix' , the optimizer cannot predict the outcome. ---- Jack Vamvas ___________________________________ Receive free SQL tips - www.ciquery.com/sqlserver.htm ___________________________________ Show quote "RobRoma" <RobR***@discussions.microsoft.com> wrote in message news:122996AB-1DAA-4A56-94CB-20F958FC744E@microsoft.com... > Hello! > > I'm using a statement with LIKE in the WHERE clause. Searching for rows > containing a prefix (LIKE 'MyPrefix%') SQL Server seeks the index on that > column - that's fine. But if I want to search for a suffix (LIKE '%MySuffix') > an Index Scan is used and takes very long. Do I have any possibility that SQL > Server does an Index Seek in case of LIKE '%MySuffix'? > > How can I optimize such a query? > > I also couldn't find a use of FTS in this case, because CONTAINS only > provides a <prefix term> but not a term for suffixes or am I wrong? > > Thank you all! If you frequently search by specifying how a string ends, such as the
following: last_name like '%stone' Consider creating a computed column as reverse(last_name) and then creating an index on it. When you do that and rewrite your query as reverse_last_name like 'enots%', your searches are likely to get a performance boost. http://www.devx.com/dbzone/Article/30786 RobRoma wrote:
Show quote > Hello! Indexes are binary trees. When doing an index seek, the query engine > > I’m using a statement with LIKE in the WHERE clause. Searching for rows > containing a prefix (LIKE ‘MyPrefix%’) SQL Server seeks the index on that > column – that’s fine. But if I want to search for a suffix (LIKE ‘%MySuffix’) > an Index Scan is used and takes very long. Do I have any possibility that SQL > Server does an Index Seek in case of LIKE ‘%MySuffix’? > > How can I optimize such a query? > > I also couldn’t find a use of FTS in this case, because CONTAINS only > provides a <prefix term> but not a term for suffixes or am I wrong? > > Thank you all! determines which branch of the tree to take by deciding if the value it's looking for is greater than or less than the value it's currently sitting on: Seeking a value 'ABG': +----MMM----+ | | +-ABF-+ +-XYY-+ | | | | ABE ABG XYX XYZThree hops gets us to the desired value, because we can navigate the tree. Seeking a value of '%G', we start with the first node, 'MMM'. Which way do we branch to continue our search? We don't know, therefore we have to look at EVERY node to find those that end in 'G', thus an index (or table) scan.  |
|||||||||||||||||||||||
Other interesting topics