Search dilema - SQL Server Forums

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.

All Forums

Old Forums

CLOSED - General SQL Server

Search dilema

Author

Topic

jazzym
Yak Posting Veteran

94 Posts

Posted - 2004-07-21 : 04:08:59

I've created my own forum system as we have very specific needs. I'm currently working on the search feature of the forums and have come across a problem that i've yet to solve. I was wondering if SQL may be the way forward.

My problem is that my search feature must be able to search through topic posts and replies to topics. Returning the title of the original topic and it's ID is no problem. The problem i do have is that if you set very unspecific criteria you get back results that include both the original topic and also replies to that topic. They all link to the same topic so it's a waste to see them all. what i need is to just show it once. Whether it's a reply or the original topic it doesn't matter as the same data is display, linking to the topic.

My first approach was an array in ASP which was checked to see if it contained the topic ID alreayd, meaning it had already output it, if not found it would output and store the topic ID in the array.

This whole looping inside i loop since seriously inefficient. I was wondering if there is anyway i could deal with this problem in SQL. At present i have a view which joins together all the data i need for my search results, then when i call the view i just add a few WHERE clauses to filter. Straight forward stuff at present.

If you need any more info just ask. Any ideas much appreciated.

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-07-21 : 04:38:21

Post the schema of your topic tables and some sample data.

jazzym
Yak Posting Veteran

94 Posts

Posted - 2004-07-21 : 04:45:16

a basic idea would be a simple messages table

ID,title, message,originator_id

originator_id is for replies, 0 if topic and ID of topic if replies.

Simple

As for sample data. i dont really have any.

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-07-21 : 04:54:09

[code]
DECLARE @SearchResults TABLE
(ID INT,
Title VARCHAR(100),
Message VARCHAR(6000)
)

-- Topic items
INSERT INTO @SearchResults
SELECT ID, Title, Message
FROM MyTable
WHERE Originator_ID=0 AND (Title LIKE '%SEARCHSTRING%' OR Message LIKE '%SEARCHSTRING%')

-- Replies not in the topics already selected
INSERT INTO @SearchResults
SELECT ID, Title, Message
FROM MyTable
WHERE (Title LIKE '%SEARCHSTRING%' OR Message LIKE '%SEARCHSTRING%')
AND NOT EXISTS (SELECT 1 FROM @SearchResults WHERE ID=MyTable.Originator_ID)

--Return all the Results
SELECT * FROM @SearchResults
[/code]

jazzym
Yak Posting Veteran

94 Posts

Posted - 2004-07-21 : 06:14:18

Works a treat, with a slight tweak to the second INSERT statement as it only needs to search through replies, not all records. Thats sorted.

My next problem is that i build a long where clause in my ASP page for filtering the records. I was told that you can store an SQL statement in a variable then execute the variable.

SET @sql = 'SELECT * FROM table'
EXEC @sql

So i am sending my already generated where clause to the store procedure and was hoping to do this.

-- Topic items
SET @sql = '
INSERT INTO @SearchResults
   SELECT ID, Title, Message
   FROM MyTable
   WHERE Originator_ID=0 ' + @where_clause

EXEC(@sql)

The error i get is "Must declare the variable '@SearchResults'".

I take it there is a problem with storing data into variables when code is executed this way?

Any ideas?

Sorry i forgot to mention that i had declared the vairable as mentioned in the code you supplied. SO it's nothing obvious like that.

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-07-21 : 06:41:06

Ah, the problem here is one of scope. Variables are only in scope for the process in which they run. Dynamic SQL like that in your code executes in it's own scope and can't see the table variable declared in the calling proc.
Thinking on a solution that doesn't need the table...watch this space.

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-07-21 : 06:44:12

OK, one solution is to put the whole thing in a dynamic statement (a bit messy). For example...


Declare @cmd varchar(6000)
SELECT @cmd='
declare @t table
(dt datetime)
INSERT INTO @t VALUES(GETDATE())
SELECT * FROM @t'
exec (@cmd)

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-07-21 : 06:47:25

Re-reading, is it possible to only pass the search string through, as opposed to the entire WHERE clause. If so, it means a query plan can be stored for the proc, making it far more efficient than executing dynamic SQL. Just a thought..

jazzym
Yak Posting Veteran

94 Posts

Posted - 2004-07-21 : 06:51:29

I currently have 5 criteria and 4 types of searching of keywords (any word, all words, exact match)

Either way i'd need to build up a large where clause. Whether i get it from the ASP page as a variable or build a variable in the sproc. It's still a variable.

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-07-21 : 06:53:22

And here's a pure set-based solution...


   SELECT ID, Title, Message
   FROM MyTable
   WHERE Originator_ID=0 AND (Title LIKE '%SEARCHSTRING%' OR Message LIKE '%SEARCHSTRING%')
   UNION ALL
   SELECT A.ID, A.Title, A.Message
   FROM MyTable A LEFT JOIN MyTable B ON A.Originator_ID=B.ID
   WHERE B.ID IS NULL
   AND A.Originator_ID > 0
   AND (A.Title LIKE '%SEARCHSTRING%' OR A.Message LIKE '%SEARCHSTRING%')

jazzym
Yak Posting Veteran

94 Posts

Posted - 2004-07-21 : 07:03:40

I put the original code you gave me (or at least the concept that you gave me) inside a variable. So all of my original sproc was inside the variable. I then added the @where_clause values in the right places. Effectively turning it into one big string which i then executed.

Worked a treat. Everything in scope.

Thanks for your help! It's awesome!

Kristen
Test

22859 Posts

Posted - 2004-07-21 : 13:48:47

[code]-- Topic items
SET @sql = '
INSERT INTO @SearchResults
SELECT ID, Title, Message
FROM MyTable
WHERE Originator_ID=0 ' + @where_clause

EXEC(@sql)[/code]
What you need here is sp_executeSQL, rather than EXEC. This little chappie takes parameters AND stores the (parameterised) query and its execution plan in the cache - so it should run pretty much as fast as a stored procedure subsequently.

You can't use dynamic SQL to store into a table variable though.

So you would need to use
[code]
SELECT @where_clause = 'MyColumn1 = @MyCol1 AND MyColumn2 = @MyCol2'
SELECT @sql = '
SELECT ID, Title, Message
FROM MyTable
WHERE Originator_ID=0 ' + @where_clause

INSERT INTO #MyTempTable
EXEC sp_ExecuteSQL(@sql,
N'@MyCol1 varchar(10), @MyCol2 int, @MyCol3 float',
@MyCol1, @MyCol2, @MyCol3)
[/code]
Note that I have used "@MyCol3" in the sp_ExecuteSQL even though it isn't referenced. In this way you can pass all the POSSIBLE parameters to sp_ExecuteSQL, even if the dynamic part of the SQL doesn't always use them all. SQL Server caches the Statement (1st parameter) to sp_ExecuteSQL, so its obviously important that it doesn't change all the time - but once the WHERE clause is parameterised its likely to only be one of a few possible choices, hence the efficiency gain.

(I haven't tested this code, so its only a close approximation of what you actually need. Hopefully someone pedantic will be along in a minute to point out all my syntax errors!)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources