I don't have the figures, but I think "seconds" is what Bob is getting now, probably with a lot less of I/O and CPU usage that a scan would take.
I just did an Advanced search (all authors, recipients, boards, current year) on the string "amd" and the results came back in about 4 seconds.
Hmmmm.... 1 second for the same search from the AMD board.
At face value, that says MSSearch isn't the bottleneck. Either way, MSSearch had to first look up "amd" in the current year's catalog, then SQL Server could process the results.
I won't mess with it quite yet, though. I'm supposed to be giving iHub all of my "programming love" this week.
Couldn't resist taking a cursory peek.
It works so much faster when searching from a Subject page because that search is calling a stored proc, which is do-able there because I was able to hard-code the JOINs in the proc as well as the WHERE clause, which only has two parameters: the contains() function and the subjectid.
The Advanced search can't work this way unless I were to make a separate SP for each possible combination of tables that're going to be joined and WHERE'd. At least, that's my strong suspicion, since I can't imagine the query execution planner would be able to do anything useful with a single proc whose JOINs and WHEREs are different each time it's called. The QEP might actually get in the way in that case, if it optimizes for the first one submitted then insists on using the same methods for subsequent calls to that proc.
Sounds daunting. And 4 seconds isn't really all that bad for search speed.
Edit: But I'm still going to have a hard look at my indexes and makes sure I've got all the appropriate ones for any T-SQL joins and wheres it might have to deal with. |
