Link to home
Start Free TrialLog in
Avatar of Jacob L
Jacob LFlag for United States of America

asked on

sql openquery run time is really long

Hi experts,
i have this query that uses a linked server (from_ams) to connect to an oracle database. The query takes 12 minutes to run and I am just comparing about 10 records. I believe what is happening is the openquery is pulling every record from the oracle side which is millions of records. Is there any modifications I can make to this query to compare only the records in the WM_EME_to_AMS_PointsCompareFULL table?

sql query:
select a.HH_NUM, p.Amount, eme.HH_NUM, eme.emeBalance  
      from WM_EME_to_AMS_PointsCompareFULL a
         join CardIDs b on CONVERT(bigint, b.ExtCardID) = a.HH_NUM and b.CardTypeID = 1
         inner join points p on b.CustomerPK = p.CustomerPK and ProgramID = 1
             inner join Openquery([from_ams], ' SELECT H.HH_NUM, H.SPTSACCU10 as emeBalance FROM  EME.HOUSEHOLD H ' ) eme on a.HH_NUM = eme.hh_num
Avatar of Andrei Fomitchev
Andrei Fomitchev
Flag of United States of America image

You are correct - OpenQuery pulls the whole table EME.HOUSEHOLD. After that if filter the rows on a.HH_NUM = eme.hh_num.

You can make if work faster if you use some condition inside OpenQuery:
Openquery([from_ams], ' SELECT H.HH_NUM, H.SPTSACCU10 as emeBalance FROM  EME.HOUSEHOLD H WHERE H.HH_NUM=...' ) eme

You can make a cycle on SQL Side through a.HH_NUM and fetch a.HH_NUM to some variable @hhNum. Then you can compare that 1 value:
SET @sql = 'SELECT H.HH_NUM, H.SPTSACCU10 as emeBalance FROM  EME.HOUSEHOLD H WHERE H.HH_NUM='+CAST(@hhNum AS NVarChar)
and then
OpenQuery([from_ams],@sql)
Avatar of Jacob L

ASKER

Andrei,
Thanks for your response. Putting a condition inside the open query would involve too much manual work. Something that I have been doing and hence the question here because it's a pain in the butt.

I am not sure I follow what you are saying on the 2nd part, making a cycle on the sql side. Can you elaborate a little more please? Thank you
SOLUTION
Avatar of ste5an
ste5an
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial