Query taking too long

Forum|Forum|19 years ago
April 3, 2007
1 reply
955 views

Below is my query which is taking a long time to execute, DB is SQL Server 2005 and MX 7.02
I have downloaded the latest MS SQL 2005 driver 1.xxx and JDBC 3.5 and still the query takes long to execute

The Description field is a Full_text indexed catalog column
the p.vendornumber is a primary key same with c.ID

The Execution Time is: 13640 ms Which I think is very long

SELECT Upper(p.Type) Type,p.Modelname,p.partno,Upper(p.description) description,
Upper(p.classification)classification,p.vendornumber,p.mfg,
p.price,c.CompanyName,c.City,c.State,p.thumbnail
FROM P_all p, Acts c
WHERE p.vendornumber = c.ID
AND CONTAINS(p.Description, '"helmet*"')
Order by p.VendorNumber

Database access

This topic has been closed for replies.

T

The_ScareCrow

Inspiring

You are doing a full text search, so it will take some time.
If you execute the query in sql server how long does it take ?
Do you have all your indexes done and up to date ?

But the attched query "may" be a bit quicker, but I doubt it
Ken

U

umuayoAuthor

Inspiring

Thanks Ken,
The results are still same. Are there settings that has to be checked in the SQL SERVER 2005 for optimization?

Do not know SQL SERVER 2005 well, but will start dabbling my hands on it at this time to make this thing work.

P

paross1

Participating Frequently

Since doing a full text search with CONTAINS is probably a major performance dog, you may want to consider reseaching Full-Text Catalogs and Indexes:

A Microsoft® SQL Server™ 2000 full-text index provides efficient support for sophisticated word searches in character string data. The full-text index stores information about significant words and their location within a given column. This information is used to quickly complete full-text queries that search for rows with particular words or combinations of words.

Full-text indexes are contained in full-text catalogs. Each database can contain one or more full-text catalogs. A catalog cannot belong to multiple databases and each catalog can contain full-text indexes for one or more tables. A table can only have one full-text index, so each table with a full-text index belongs to only one full-text catalog.

Full-text catalogs and indexes are not stored in the database to which they belong. The catalogs and indexes are managed separately by the Microsoft Search service.

A full-text index must be defined on a base table; it cannot be defined on a view, system table, or temporary table. A full-text index definition includes:

-- A column that uniquely identifies each row in the table (primary or candidate key) and does not allow NULLs.

-- One or more character string columns covered by the index.
....

Full-text indexing is the component that implements two Transact-SQL predicates for testing rows against a full-text search condition:

-- CONTAINS

-- FREETEXT

etc. etc.

Phil

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded