Open Query for select

Question

Open Query for select

newbieuser

SSCrazy Eights

Points: 9423
More actions
February 12, 2013 at 12:24 pm

#270086

Hi friends,
I have created another thread regarding a performance problem on a select statement to oracle table...But would like to know if the below Select query can be used as open query and if it would make the sql run faster..
SELECT localtab.field1 FROM LINKEDSERVER..REMOTEUSER.TABLE remtab, localtab
WHERE ((remtab.rfield1 = 'value' OR remtab.rfield1 = 'value' ) OR (remtab.rfield2 = 'value' ))
AND remtab.rfield3 = localtab.rfield3
Is it possible to run the above SQL using open query? Any help is much appreciated..Thanks so much

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

amybe...but the problem with open query is your command has to be a static string...no variable, or concatenation of stirngs plus variables are allowed.

how would this worl for you:

SELECT * FROM OPENQUERY( [linked server],'SELECT

localtab.field1

FROM REMOTEUSER.TABLE remtab

INNER JOIN localtab

ON remtab.rfield3 = localtab.rfield3

WHERE ((remtab.rfield1 = ''value'' OR remtab.rfield1 = ''value'' )

OR

(remtab.rfield2 = ''value'' )

) ')

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323504 More actions · Answer 2

bah the above won't work because of the local table.

divide and conquer is what you'll need to do i think.

i would try to use a CTE that gets just the rows that match your values on the linked server, then join that locally.

--just the data that matches kind of

;With MyCTE AS

(

SELECT

remtab.rfield1,

remtab.rfield3

FROM LINKEDSERVER..REMOTEUSER.TABLE

WHERE remtab.rfield1 = 'Value'

UNION

SELECT

remtab.rfield2, --the other column

remtab.rfield3

FROM LINKEDSERVER..REMOTEUSER.TABLE

WHERE remtab.rfield2 = 'Value'

)

--now the join:

SELECT * FROM localtab

INNER JOIN MyCTE

ON MyCTE.rfield3 = localtab.rfield3

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 3

Thanks a lot Lowell. CTE works perfect. This sql is run within a loop in a program and it is causing performance issue.. I have created a topic(Remote Query to Oracle very slow) in this forum regarding this problem.. Can you please tell me would using CTE increase performance in this case? Also, can we use variables in CTE?

Thank you so much for your help

Lowell SSC Guru Points: 323504 More actions · Answer 4

newbieuser (2/12/2013)
Thanks a lot Lowell. CTE works perfect. This sql is run within a loop in a program and it is causing performance issue.. I have created a topic(Remote Query to Oracle very slow) in this forum regarding this problem.. Can you please tell me would using CTE increase performance in this case? Also, can we use variables in CTE?
Thank you so much for your help

can't say for sure; your other post doesn't show the loop you mentioned here; Can you post the offending code? if it's using a loop or cursor to go to Oracle linked server once per iteration, the loop needs to be replaced with a set based operation instead;

so to help you'll need to provide more info;

pasting the program code might give us insight into replacing THAT with something all done at the SQL server in one shot.

Programmers tend to think of data as one row at a time, because they can step through the code, look at the variables and values in debug, and confirm to themselves it's working the way they want;

stepping back from the picutre, and abstracting out to say "i want to do this to the SET of data when this other criteria is true is the DBA's job...look at things as Sets.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 5

Thanks Lowell. I've asked the application team to provide the code.. I have been told this particular SQL is executed in a loop but would be interesting to look in the code.

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 6

Thanks Lowell. I've asked the application team to provide the code.. I have been told this particular SQL is executed in a loop but would be interesting to look in the code. While I wait for the code, I would like to try the program with CTE as well just to see if it helps or not.. Would like to know if CTE can be referenced with bind variables?

Thanks again

Lowell SSC Guru Points: 323504 More actions · Answer 7

CTE with variables? sure, it's just like any other SELECT statement WHERE SomeColumn = @Param is certainly valid; just declare the variables before teh CTE, adn make sure you end the definitions with a semicolon.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!