Query Taking too Long

Below is my query which is taking a long time to execute, DB is SQL Server 2005 through a web Application
I have downloaded the latest MS SQL 2005 driver 1.xxx 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

Any one have an idea why it is taking this long to run

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

Thanks

First off, since you are using a WHERE criteria of [ p.VendorNumber ], the ORDER BY is pointless, and depending upon the resultset, perhaps even wasting time.

Then, I think the most significant part of the query is

FROM P_all p, Acts c

which 'may' have the effect of causing a CROSS JOIN.

Use the 'proper' form for a JOIN, including the JOIN conditions. (I've assumed that VendorNumber is common to both tables, if that is incorrect, then use the correct linking column.)

Code Snippet

SELECT
Type = upper( p.Type ),
p.ModelName,
p.PartNo,
Description = upper( p.Description ),
Classification = upper( p.Classification ),
p.VendorNumber,
p.Mfg,
p.Price,
c.CompanyName,
c.City,
c.State,
p.Thumbnail
FROM P_All p
JOIN Acts c
ON p.VendorNumber = c.ID
WHERE contains( p.Description, '"helmet*"' )
ORDER BY p.VendorNumber

|||Thanks Arnie,

The changes still did not solve the problem.
|||

what are indexes on these table. And also remove Upper functions from all column and see the performance. and post back the result of the query without upper function

Madhu

|||There are indexes on the table. The culprit column "Description, is a Full-text indexed catalog column. The main searches are on this column based on character search. I was using the "like" search before creating the "Full-text indexed catalog.

The Upper function is not the problem, the query takes longer to run without the Upper function.

Are there more things I need to do in the SQL 2005 Database? I have installed the latest MS JDBC drivers for MS SQL 2005.

Thanks
|||If by chance, this database was migrated to SQL 2005 from SQL 2000, you would need to rebuild the indexes, and update statistics.