Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Turbo for SQL Server

By Kathi Kellenberger,

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.

Ease of Use 4.5 Creating indexes is easy and very quick.
Feature Set 5 They thought of everything!
Technical Support 5 They have lots of support options and are always very helpful.
Lack of Bugs 5 None found.
Documentation 4 There were a few typos in the documentation, but is is very comprehensive. You'll spend a lot of time learning how to use all of the amazing features.
Performance 5 They don't call it Turbo for nothing!
Installation 5 Quick and painless.
Learning Curve 4 Not bad, but it will depend on how many of the advance techniques you want to use.
Overall 4.5 This is another great product from Imceda.

Product Information

Web Site: Imceda's SQL Turbo

Developer: Imceda Software
Pricing: Contact Imceda Sales

Total article views: 7259 | Views in the last 30 days: 9
 
Related Articles
FORUM

Turbo for SQL Server

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/column...

FORUM

Help with Query - Products

Help With query - Products

BLOG

Querying Microsoft SQL Server : Basics of Indexes in SQL Server

Querying Microsoft SQL Server : Basics of Indexes in SQL Server: Indexes in SQL Server: If you see...

FORUM

SQL Turbo Performance

Has anyone had issues with the SQL Turbo built in stored procedures having slow performance? We are...

FORUM

production server slower than development server

performance difference between production server and development server

Tags
product reviews    
reviews    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones