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 tableID,title, message,originator_id originator_id is for replies, 0 if topic and ID of topic if replies.SimpleAs 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 itemsINSERT 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 selectedINSERT 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 ResultsSELECT * 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 itemsSET @sql = 'INSERT INTO @SearchResults SELECT ID, Title, Message FROM MyTable WHERE Originator_ID=0 ' + @where_clauseEXEC(@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 itemsSET @sql = 'INSERT INTO @SearchResults SELECT ID, Title, Message FROM MyTable WHERE Originator_ID=0 ' + @where_clauseEXEC(@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_clauseINSERT INTO #MyTempTableEXEC 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 |
|
|
|