Introduction

Full-text searching is one of the many useful features that ships with SQL

Server. It provides enhanced indexing and searching of character and

binary data columns replacing sometimes inefficient queries using the

"like" operator or queries that would be impossible to do with a

regular "where" clause. Imceda Software, now part of Quest Software, has

recently released SQL Turbo v2 for MS SQL Server that leaves native SQL Server

full-text capabilities at the starting line!

Environment

I installed SQL Turbo v2 on a Windows 2000 Server running SQL

Server 2000 SP3. This product is supported on Windows 2000 Server and

Professional, Windows XP, and Windows 2003 Server. It works with SQL Server

versions 7 and 2000, including MSDE installations.

Installation

SQL Turbo v2 is available in two versions: standard and

server. Install the standard version on a single SQL Server. The

server product, which can be installed on a box that is not running SQL Server,

manages load balancing and fail-over of the search functionality for a group of

SQL Servers in addition to the standard features.

I installed the server version in a few seconds after

downloading. After agreeing to the EULA, the installation package

displayed information about the upgrade process if you happen to be upgrading an earlier version. I was then prompted for

an installation directory. In less than 30 seconds the software was

running.

Using SQL Turbo

I spent quite a bit of time reading the documentation. The "Quick

Start" tutorial gave me just a small taste of what was possible, I

realized, as I learned about the many advanced features and

options. Some of the cool features are phonetic and synonym

searching, indexing on numeric and dateTime fields, results returned a page at a

time, plain text searches, fuzzy searches, searches on ranges, searches on sets, and specially defined sort orders. Luckily, the documentation has lots of example queries to help you

get started because Imceda has thought of everything.

After starting up the software, you connect to the local server and choose a

database. If it is the first time to connect to the database, a configuration process runs on the database

to get it ready for SQL Turbo.

Figure 1: SQL Turbo Full-Text Index Manager

Creating a SQL Turbo index is easy. Right-click on "Indexes",

fill in the information and click "OK".

Figure 2: Create a New Index

While the query syntax is simple to learn and the documentation

extensive, the syntax is very different from the native queries. Instead

of regular T-SQL statements, SQL Turbo uses stored procedures.

This query example is from the "Quick Start" tutorial

and returns the first 100 products that contain the word "tofu":

exec tdbExecute 'SELECT * FROM [Alphabetical

list of products] WHERE {Alphabetical_list_of_products_idx=''tofu''}',1,100

You may be wondering why numeric and date fields can be indexed

with SQL Turbo. If you also had to filter on an integer, for example

a "CategoryID" field, full-text first returns to SQL all rows that

qualify. Those rows are then filtered on the "CategoryID"

field. So, if one million rows contained "tofu", but only one

thousand of them were of a certain "CategoryID", the query may take

longer than it needs to. With SQL Turbo, you just create an additional

index on "CategoryID", and the query would then look like this:

exec tbdExecute 'SELECT * FROM

[Alphabetical list of products] WHERE {ProductName_idx=''tofu'' and

CategoryID_idx=2},1,100

If you are or have been a web developer, you have probably

struggled with displaying a page of data t a time with the correct navigation

links. SQL Turbo provides an excellent stored procedure,

tdbExecutePageNav, to get the page of data along with the navigation

information.

I created an index on the product table in AdventureWorks2000

and then ran this query which returns the seventh page of ten records

with five navigation links:

declare @query varchar(4000)

set @query = 'select name from [product] where {Product_idx=''Mountain'' } '

exec tdbExecuteReturnPageNav @query, 7,10, 5

This was the data returned in three recordsets:

name

--------------------------------------------------

Mountain-300 Black, 62

Mountain-300 Red, 44

Mountain-300 Red, 48

Mountain-300 Red, 52

Mountain-300 Red, 56

Mountain-300 Red, 58

Mountain-300 Red, 60

Mountain-300 Red, 62

Mountain-300 Silver, 40

Mountain-300 Silver, 52

TotalRecordCount     TotalPageCount     CurrentStartIndex    

CurrentRecordCount 

   ----------------          

--------------           -----------------          

------------------ 

         157                            

16                           

61                              

10

Page 

----------- 

-5

6

7

8

9

10

-11

Note: You may get one or two extra links meaning previous or next when your data

is in the middle of possible pages.

I don't know about you, but I think this is extremely cool!

Performance

Creating and populating the catalogues and indexes are very

fast. On the tables I worked with, it took less time to create and

populate the index with SQL Turbo than just creating the index with native

full-text. On small tables, both SQL full-text and SQL Turbo performed

equally fast. Populating the index on a table with 3.8 million rows took 7

minutes with SQL Turbo. It took several hours to populate a native

full-text index on the same table. Queries against this table were six

times faster with SQL Turbo.

Check out their demo web

site where you can see the performance difference for yourself.

Support

I did not have any problems using SQL Turbo so I did not contact them.

You can enter a support request online or a ask a question on their support

forum. I use Imceda's product, SQL LiteSpeed, on a dozen servers, and I have

always received excellent service from their support staff via the online

support request form.

Conclusions

SQL Turbo has everything you need in a search engine: speed and great

functionality. If you are serious about full-text searching, download

the 30 day trial and take it for a spin.

Ratings

I will rate each of the following using a scale from 1 to 5. 5 being the best and 1 being the worst. Comments are in the last column.

Product Information

Web Site: Imceda's SQL Turbo

Developer: Imceda Software

Pricing: Contact Imceda Sales