October 18, 2010 at 12:25 pm
While this isn't specific to SQL 2008, our MS Search Server 2010 does use SQL 2008 R2, so perhaps someone here is also using it?
We have a VS 2008 winforms application that queries Search Server 2010 using the webservice and the queryex method to return a dataset containing the results of our query. However we need to do a join with data from another server.
I have created a link between the 2008R2 SQL server that hosts SearchServer and the server hosting the other database, but need a method that will allow me to query the search server databases directly and join with the data from the other SQL server. Currently, I am doing this all in memory, which is very slow when the result set contains over 100k results.
I have tried creating a SSIS web-service package, but that provides no performance improvement and is more difficult to document and maintain. I also created a trace while executing the web-service query, but the results were not straight forward - much more complex than simply executing a single stored procedure and not something I've been able to replicate.
Is it possible to query Search Server directly through a stored procedure?
October 18, 2010 at 1:19 pm
Additional info:
The following output is from a trace run using the web service. Whenever I execute the queryex method, 4 separate queries are executed. The last one of which returns the actual results needed. A sample of that query follows. If I change the query text (where it says 'MySearchString' below, the results are the same. It appears Search Server is passing the query string within the hex data at the end. I can take that data from another query, and place it in another and it returns the results for the query corresponding to the hex data - regardless of what is passed in 'MySearchString'.
Unfortunately, its not a simple string to hex conversion. It appears it has been encrypted (which I suppose makes sense since this is a SOAP query).
Edit: The 5th param passed to the sproc [proc_MSS_GetMultipleResults] is varbinary, but Im still working on deciphering that param.
I tried:
DECLARE @bin VARBINARY(MAX)
SET @bin = [varbinary string]
SELECT CONVERT(VARCHAR(MAX), @bin, 2)
Any ideas appreciated!
exec sp_executesql N'/* {C112F572-A124-4766-BC42-F5BEE71F7773} */
exec dbo.proc_MSS_GetMultipleResults @P1,@P2,@P3,@P4,@P5',N'@P1 int,@P2 nvarchar(4000),@P3 nvarchar(4000),
@P4 nvarchar(4000),@P5 varbinary(max)',5,N'MySearchString',N'',N'SET NOCOUNT ON ;
DECLARE @joinRows INT ; SET @joinRows = DATALENGTH(@joinData) / 8 ;;WITH DocIds(DocId, Value)
AS ( SELECT TOP(@joinRows) CAST(SUBSTRING(@joinData, ((ord.n-1)*8) + 1, 4) AS INT),
CAST(SUBSTRING(@joinData, ((ord.n-1)*8) + 5, 4) AS INT)
FROM dbo.MSSOrdinal AS ord WITH(NOLOCK)
WHERE ord.n <= @joinRows )
SELECT P.DocId, P.SummaryBlobSize, P.Size, P.LastModified, P.IsDocument, P.IsPictureUrl,
P.Author, P.Title, P.Url, P.PictureThumbnailUrl, P.ContentClass, P.FileExtension, P.Tags,
P.PropertyBlob, CASE WHEN P.PropertyBlob IS NULL THEN 0 ELSE DATALENGTH(P.PropertyBlob) END,
P.PopularSocialTags, P.SiteName, P.Description, P.ParentLink, P.NumberOfMembers,
P.PictureHeightAndWidth, P.DisplayDate FROM dbo.MSSDocResults AS P WITH(NOLOCK),
DocIds AS T WHERE P.DocId = T.DocId OPTION (MAXDOP 1) '
,0x000A34E7000000BE00099F4E000000BD000910F3000000BC00091586000000BB00090C43000000BA000961C1000000B90008DF8E000000B80008ED10000000B700096418000000B600096573000000B500096019000000B400096377000000B300090FFF000000B200091E15000000B1000A8678000000B000091605000000AF0008EB70000000AE0009AFCF000000AD00090D2D000000AC00099EE3000000AB0009E2FF000000AA0008EBEE000000A90009E624000000A8000984A0000000A700098092000000A6000A3676000000A5000A373F000000A4000A9200000000A3000A974B000000A2000A334C000000A10008EB1E000000A00009F2160000009F0009EDAA0000009E0009EA6B0000009D000A0F010000009C000A8C460000009B000944200000009A000A35FD000000990009F1620000009800095C6B000000970009B178000000960009BD55000000950009F520000000940009639C0000009300096EC3000000920009E714000000910009EEA200000090000A8E490000008F000A90B80000008E000A96CA0000008D000A71EA0000008C000A3EDD0000008B000A76AC0000008A000A41C6000000890009BC11000000880009F1DF000000870009D7D400000086000A8BFD00000085000A8DB500000084000A8D63000000830009EB7200000082000AAE3B00000081000AAF0E00000080000A97740000007F0009D4350000007E000A07B30000007D000A1A9B0000007C000A3C810000007B000A634D0000007A000A7D6600000079000AAAA300000078000A741F00000077000A0BAD0000007600098DCD0000007500093EE10000007400094329000000730009454F0000007200094748000000710009477400000070000949DC0000006F00094A2A0000006E00094D4C0000006D00094D610000006C00094DAA0000006B00094DAE0000006A00094E690000006900094EAC0000006800094EAE0000006700094F1A0000006600094F540000006500094F92000000640009506300000063000950A300000062000950D700000061000950EB00000060000951070000005F000951540000005E000951600000005D0009526B0000005C0009529D0000005B000953150000005A0009532800000059000953B500000058000953FA000000570009541C00000056000954270000005500095485000000540009548B00000053000954CB00000052000955030000005100095507000000500009551C0000004F000956010000004E000956640000004D000956950000004C0009569B0000004B0009569D0000004A000956A200000049000956A700000048000956B100000047000956B300000046000956B500000045000956B600000044000956B900000043000956BD00000042000956C500000041000956C600000040000956C80000003F000956CC0000003E000956CE0000003D000956D70000003C000956DD0000003B000956DF0000003A000956E200000039000956E300000038000956E700000037000956F900000036000956FF0000003500095708000000340009571000000033000957150000003200095717000000310009571D00000030000957270000002F0009572E0000002E0009572F0000002D000957300000002C000957310000002B000957320000002A000957330000002900095739000000280009573E000000270009573F000000260009574000000025000957410000002400095742000000230009574A000000220009574B000000210009575B000000200009575C0000001F000957730000001E000957740000001D000957760000001C000957830000001B0009578F0000001A00095796000000190009579900000018000957A000000017000957A400000016000957A500000015000957A700000014000957AA00000013000957AC00000012000957B100000011000957B700000010000957BA0000000F000957C00000000E000957C20000000D000957CF0000000C000957DF0000000B000957FA0000000A000957FF0000000900095805000000080009580700000007000958190000000600095820000000050009582100000004000958220000000300095824000000020009582900000001
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply