SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Open Query for select


Open Query for select

Author
Message
newbieuser
newbieuser
SSC Eights!
SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)

Group: General Forum Members
Points: 851 Visits: 915
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
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28305 Visits: 39955
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
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28305 Visits: 39955
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
newbieuser
SSC Eights!
SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)

Group: General Forum Members
Points: 851 Visits: 915
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
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28305 Visits: 39955
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
newbieuser
SSC Eights!
SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)

Group: General Forum Members
Points: 851 Visits: 915
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
newbieuser
SSC Eights!
SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)

Group: General Forum Members
Points: 851 Visits: 915
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
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28305 Visits: 39955
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!

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search