Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Open Query for select Expand / Collapse
Author
Message
Posted Tuesday, February 12, 2013 12:24 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 23, 2014 3:06 PM
Points: 319, Visits: 757
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
Post #1419147
Posted Tuesday, February 12, 2013 1:28 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 8:17 PM
Points: 12,881, Visits: 31,820
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
Post #1419171
Posted Tuesday, February 12, 2013 1:34 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 8:17 PM
Points: 12,881, Visits: 31,820
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
Post #1419172
Posted Tuesday, February 12, 2013 2:34 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 23, 2014 3:06 PM
Points: 319, Visits: 757
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
Post #1419194
Posted Tuesday, February 12, 2013 2:40 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 8:17 PM
Points: 12,881, Visits: 31,820
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
Post #1419197
Posted Tuesday, February 12, 2013 2:48 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 23, 2014 3:06 PM
Points: 319, Visits: 757
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.
Post #1419202
Posted Tuesday, February 12, 2013 2:50 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 23, 2014 3:06 PM
Points: 319, Visits: 757
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
Post #1419203
Posted Tuesday, February 12, 2013 2:57 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 8:17 PM
Points: 12,881, Visits: 31,820
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
Post #1419208
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse