|
Home > Archive > IIS Index Server > April 2004 > Joining Indexing service results with SQL data
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Joining Indexing service results with SQL data
|
|
| Falkon 2004-04-29, 12:34 am |
| I'm integrating an Indexing/SQL search solution to allow users to
search for friendly file titles and file descriptions as they are
stored in our SQL database but I need to link back to indexing service
to retrieve the characterization data to make the results more
friendly. I also need to combine the results of the SQl search with
the results of the Indexing search and sort the SQL results to the
top. I have everything worked out but my query that searches SQL and
joins to Indexing Service runs very slow (using the Oledb provider).
My query that searches Indexing Service and joins to SQL for
additional data fields runs extremly fast!
Here is the slow query and I can't seem to reverse the join to improve
the speed:
SELECT *
FROM OPENQUERY(FileSystem, 'SELECT FileName, Path,
Characterization FROM SCOPE()')
indexing INNER JOIN
(SELECT *
FROM tbl_DMS_File where
contains(*,'lms')) SQL ON SQL.File_Path_Full = Indexing.PATH
Anyone else have any advice on how I can combine a resultset from SQL
with the characterization field from Indexing Service?
I really don't want to store the files as Blobs in SQL nor do I want
to store the characterization data in SQL although I may consider the
latter as a solution.
Thanks in advance for any advice!
| |
|
| Riley@Falkon.net (Falkon) wrote in
news:7e9695f.0404282039.b9b9eef@posting.google.com:
My inclination would be to do it from IS and just pick up the sql result as
needed as you output the data. I assume that you've got a lot of non-unique
file names?
> I'm integrating an Indexing/SQL search solution to allow users to
> search for friendly file titles and file descriptions as they are
> stored in our SQL database but I need to link back to indexing service
> to retrieve the characterization data to make the results more
> friendly. I also need to combine the results of the SQl search with
> the results of the Indexing search and sort the SQL results to the
> top. I have everything worked out but my query that searches SQL and
> joins to Indexing Service runs very slow (using the Oledb provider).
>
> My query that searches Indexing Service and joins to SQL for
> additional data fields runs extremly fast!
>
> Here is the slow query and I can't seem to reverse the join to improve
> the speed:
>
> SELECT *
> FROM OPENQUERY(FileSystem, 'SELECT FileName, Path,
> Characterization FROM SCOPE()')
> indexing INNER JOIN
> (SELECT *
> FROM tbl_DMS_File where
> contains(*,'lms')) SQL ON SQL.File_Path_Full = Indexing.PATH
>
>
> Anyone else have any advice on how I can combine a resultset from SQL
> with the characterization field from Indexing Service?
>
> I really don't want to store the files as Blobs in SQL nor do I want
> to store the characterization data in SQL although I may consider the
> latter as a solution.
>
> Thanks in advance for any advice!
>
| |
|
| > SELECT *
> FROM OPENQUERY(FileSystem, 'SELECT FileName, Path,
> Characterization FROM SCOPE()')
> indexing INNER JOIN
> (SELECT *
> FROM tbl_DMS_File where
> contains(*,'lms')) SQL ON SQL.File_Path_Full = Indexing.PATH
Since an IS query thru OpenQuery has a high overhead, I would run the IS
query and build a string of primary keys that will give you the
corresponding rows from SQL server.
FileSystem, 'SELECT FileName, Path,
Characterization FROM SCOPE()
where #filename <whatever_condition>
loop thru these results building a string by repeatedly concatenating the
full path to itself. Then
SELECT *
FROM tbl_DMS_File where
SQL.File_Path_Full in <built_string>
Cheers
Stu
| |
| Falkon 2004-04-29, 10:34 pm |
| Thanks for the feedback!
The only problem with starting my search with Indexing Service is that
matches found in SQL need to be ranked higher than the matches found
only in IS. Since both ranking systems use 1 - 1000 but they really
can't compare to each other, I add 1000 to each result found in SQL
which ensures the SQL search results sort to the top. The system I'm
working on allows the posting of documents to an Intranet and the
users can assign a friendly title and description which needs to be
searched and ranked higher than any IS results.
You are probably right though, for speed I think I'm going to have to
start with the Indexing Service recordset and I'm sure I can find a
way to handle my rankings correctly.
One last question, is there a fast way to navigate Indexing Service to
return data on one file using a locator ID like full path or file
name?
FYI, Currently I join my SQL and IS recordsets by using the Full Path
because its the one unique item. Its amazing how Indexing Service is
lighting fast even when joined to SQL but when I try to search SQL and
join to IS to obtain the Characterization field it crushes
performance.
Thanks again!
| |
|
| > One last question, is there a fast way to navigate Indexing Service to
> return data on one file using a locator ID like full path or file
> name?
Yip, I find full path is the fastest way. Modified date is also quick but
how usefull?
> lighting fast even when joined to SQL but when I try to search SQL and
> join to IS to obtain the Characterization field it crushes performance.
Yip bummer! IXSSO will always have a certain amount of overhead and if you
are going from SQL->IS this overhead will be multiplied n times. Im sure if
you used an "IN" query like I described in my last post it would be
seriously fast. Alternatively use ADO with a parameterised query in a loop
as a 2nd choice.
Have a good weekend!
Stu
| |
|
| > Yip bummer! IXSSO will always have a certain amount of overhead and if
you
sorry meant MSIDXS provider!
|
|
|
|
|