|
|
|
Old Hand
      
Group: General Forum Members
Last Login: Wednesday, May 22, 2013 2:25 PM
Points: 306,
Visits: 724
|
|
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
|
|
|
|
|
SSChampion
        
Group: General Forum Members
Last Login: Today @ 4:22 PM
Points: 11,789,
Visits: 28,063
|
|
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
--There is no spoon, and there's no default ORDER BY in sql server either. Actually, Common Sense is so rare, it should be considered a Superpower. --my son
|
|
|
|
|
SSChampion
        
Group: General Forum Members
Last Login: Today @ 4:22 PM
Points: 11,789,
Visits: 28,063
|
|
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
--There is no spoon, and there's no default ORDER BY in sql server either. Actually, Common Sense is so rare, it should be considered a Superpower. --my son
|
|
|
|
|
Old Hand
      
Group: General Forum Members
Last Login: Wednesday, May 22, 2013 2:25 PM
Points: 306,
Visits: 724
|
|
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
|
|
|
|
|
SSChampion
        
Group: General Forum Members
Last Login: Today @ 4:22 PM
Points: 11,789,
Visits: 28,063
|
|
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
--There is no spoon, and there's no default ORDER BY in sql server either. Actually, Common Sense is so rare, it should be considered a Superpower. --my son
|
|
|
|
|
Old Hand
      
Group: General Forum Members
Last Login: Wednesday, May 22, 2013 2:25 PM
Points: 306,
Visits: 724
|
|
| 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.
|
|
|
|
|
Old Hand
      
Group: General Forum Members
Last Login: Wednesday, May 22, 2013 2:25 PM
Points: 306,
Visits: 724
|
|
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
|
|
|
|
|
SSChampion
        
Group: General Forum Members
Last Login: Today @ 4:22 PM
Points: 11,789,
Visits: 28,063
|
|
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
--There is no spoon, and there's no default ORDER BY in sql server either. Actually, Common Sense is so rare, it should be considered a Superpower. --my son
|
|
|
|