I am having trouble coming up with an efficient method to performing a search across a database table and a file index. I have a table in a SQL 2008 database and files in the Microsoft Indexing Service.
The results coming back from the Indexing service are filenames, which match to filenames in the database table.
Right now, I submit a search for "food" and it searches the database table for record IDs. Next I search the indexing service for "food" and it returns file names of files that have "food. Then I lookup the recordIDs of the filenames and merge the two lists together. So I now have a single list of record IDs. Problem is I can't sort because the column data is not known...SO I have to return more data than just record IDs to accomplish this...
So what do you normally do when you search two merged data stores like this? Get everything and store it in a session variable or construct the sql to store the ids in temp tables?? I know I am not doing it efficiently by putting the data in a session variable, but I can sort and page on the server...
Question
+chorpeac MVC
I am having trouble coming up with an efficient method to performing a search across a database table and a file index. I have a table in a SQL 2008 database and files in the Microsoft Indexing Service.
The results coming back from the Indexing service are filenames, which match to filenames in the database table.
Right now, I submit a search for "food" and it searches the database table for record IDs. Next I search the indexing service for "food" and it returns file names of files that have "food. Then I lookup the recordIDs of the filenames and merge the two lists together. So I now have a single list of record IDs. Problem is I can't sort because the column data is not known...SO I have to return more data than just record IDs to accomplish this...
So what do you normally do when you search two merged data stores like this? Get everything and store it in a session variable or construct the sql to store the ids in temp tables?? I know I am not doing it efficiently by putting the data in a session variable, but I can sort and page on the server...
Any words of advice?
Link to comment
Share on other sites
0 answers to this question
Recommended Posts