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 |
|
nizmaylo
Constraint Violating Yak Guru
258 Posts |
 Posted - 2001-04-13 : 11:31:01
|
| I'm reposting this quesion here, in the admin section, which seems to attract more serious people.I've noticed that everyone jumps on questions like: how do I find out which item in my orderlines table was sold the most - read a book - that would be my answer. We're slowly turning the forum into some kind of "Teach yourself SQL in 1 day". I think we're here to help each other solve problems that are not being well documented or described in books. 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 50F.Title,... F.Date,F.IdColectionFROM tblPicures F, tblColections CWHERE CONTAINS(F.*,'john')AND F.IdColection=C.numcolAND DATEPART(YEAR,F.Date)>=2000AND 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.comsaulo@elnorte.com"helena |
|
|
|
|
|
|
|
challenging question