Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-04-12 : 08:32:53
|
Saul writes "Dear SQL Expert:
I am working on a DB that contains around 2 millions of records and 16 text columns, all of them included in a Full Text index. I am experiencing a pretty low performance when I make searches using the Contains function and ordering by date, the query statement is as follows:
SELECT TOP 50
F.Title,
...
F.Date,
F.IdColection
FROM tblPicures F, tblColections C
WHERE CONTAINS(F.*,'john')
AND F.IdColection=C.numcol
AND DATEPART(YEAR,F.Date)>=2000
AND F.IdSource IN (SELECT IdSource FROM Filters WHERE
IdUser='saulo' Visible=1)
ORDER BY Date DESC
(It means that I need the most recent pictures from the DB.)
When I examine the execution plan I realize that it created a Table Spool With 83 millions of rows and then it Joins this table with the original one. I have to wait almost 2 minutes to get results, users won't be that patient; and that's not all, they need an advanced search in which they choose the fields they are going to look in, so I have to use multiple Contains conditions in the Where clause... you just imagine the eternity.
I hope you have an answer soon.
Regards.
Saúl Ollervides.
www.elnorte.com
saulo@elnorte.com" |
|
|
|
|
|
Multiple Contains in a Full Text Search take so much!