Hello all,
I'm working on a SAP database which I have to connect to a telephony
exchange database. Both databases are installed on a SQL Server 2000 SP4
machine.

I have to create a view over those two databases; the join columns have
actually different type of data: the one in SAP is varbinary and the other
one is varchar(32). So, in order to join those two tables I have created a
function:

create function fn_bw (@in varbinary(32))
returns nvarchar(32)
as
begin
declare @str varchar(34)
exec master.dbo.xp_varbintohexstr @in, @str out
return (substring (@str,3,32))
end

and used this function to convert varbinary to varchar; the problem is that
the whole query works pretty slow (about 30 minutes, on a 1 milion set of
data...and the set is expected to grow). Luckily, this view will be used
only during night time...:-)
I tested the whole query with fn_varbintohexstr instead of xp_varbintohexstr
but it takes about 10 minutes more.

Is there any way I can avoid this data conversion?

Thanks,
Tudor

By the way,
here's the view...:-)

CREATE VIEW BWSRV.NBRD_CAMPANIIACTIVE
AS
SELECT  A.BP_CUSTOMER, A.EXTERNAL_ID, A.BP_CONSILIER, A.BP_AGENCY,
A.CRDAT, A.CRTIM, A.IMPORT_STATUS, A.CONTACT_RESOLUT, A.DUPLICATENO,
B.CONTACTID, CONVERT(char(8), C.calldate, 112) AS CALLDATE
  FROM
(SELECT
T1.BP_CUSTOMER, T2.EXTERNAL_ID, T1.BP_CONSILIER,
T1.BP_AGENCY, T1.CRDAT, T1.CRTIM,
T1.IMPORT_STATUS, T1.CONTACT_RESOLUT, T1.DUPLICATENO
FROM prd.ZMDCUSTOM T1
INNER JOIN  prd.CGPL_PROJECT T2
ON T1.CLIENT = T2.CLIENT AND T1.CMPG_ID = T2.GUID
WHERE (T1.CLIENT = '400'))
AS A
INNER JOIN
(SELECT T2.PID AS CONTACTID, T4.PARTNER, T5.EXTERNAL_ID
FROM
prd.CRMD_TM_IACT_BP T3
INNER JOIN
prd.CRMD_TM_IACT T2 ON T3.CLIENT = T2.CLIENT AND T3.PID = T2.PID INNER JOIN
prd.BUT000 T4 ON T3.CLIENT = T4.CLIENT AND T3.BP_GUID = T4.PARTNER_GUID
INNER JOIN
prd.CRMD_TM_CL_CMPGN T1 ON T2.CL_PID = T1.PID AND T2.CLIENT = T1.CLIENT
INNER JOIN
prd.CGPL_PROJECT T5 ON T1.CLIENT = T5.CLIENT AND T1.CMPG_ID = T5.GUID INNER
JOIN
prd.ZCMPG_START T6 ON T5.CLIENT = T6.CLIENT AND T5.GUID = T6.CMPG_ID
WHERE (T6.CLOSE_DATE < '20060101'))
AS B
ON A.BP_CUSTOMER = B.PARTNER AND A.EXTERNAL_ID = B.EXTERNAL_ID
INNER JOIN
(SELECT contactid, MAX(calldate) as calldate
FROM CTI.dbo.callhistory
group BY contactid
union
SELECT contactid, MAX(calldate) as calldate
FROM CTI.dbo.callhistory_hist
group BY contactid)
AS C
ON dbo.fn_bw(B.CONTACTID)  = C.contactid





Show quoteHide quote

Try using a computed column on either table - either the conversion of
varchar to varbinary or vice versa.

This will affect the performance of inserts and updates, but will increase
performance of selects, especially if the computed column is indexed.


ML

---
http://milambda.blogspot.com/

I'm not sure how indexes work when crossing databases in SQL Server, but
could the problem be that your index is not being used due to the
conversion?

Have you tried a hint to force the optimizer to use an index on the varchar
value based on the converted binary value?

It may simply be that the conversion function itself slows things down, or
maybe indexes don't work across servers (I assume both databases are on
separate servers).

I know Oracle allows function based indexes with their more recent products
(8 or 9), does SQL Server have some functionality that allows you to do the
same?  Indexed views maybe?


Show quoteHide quote

Warning: you are not under attack. :)


Yes, using the function results in a scan.


Would not help.


Also true. Both assumptions.


Would not be possible, since the function cannot be schemabound - it
contains an EXECUTE statement.


ML

---
http://milambda.blogspot.com/

inserting single precision data into sql server float column
SQL dilema
CTE vs. Table Variable (Paging)
can't insert into ...because of nulls problme!
Return random number from trigger