Avatar of Jacob L
Jacob L
Flag 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
SQLDatabasesOracle DatabaseMicrosoft SQL Server

Avatar of undefined
Last Comment
Andrei Fomitchev

8/22/2022 - Mon
Andrei Fomitchev

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)
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
ste5an

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Andrei Fomitchev

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes