InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 11:26:18 GMT20http://www.sqlservercentral.com/Forums/Topic1419147-1042-1.aspxCTE 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.Tue, 12 Feb 2013 14:57:48 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1419147-1042-1.aspxThanks 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 againTue, 12 Feb 2013 14:50:09 GMTnewbieuserhttp://www.sqlservercentral.com/Forums/Topic1419147-1042-1.aspxThanks 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.Tue, 12 Feb 2013 14:48:37 GMTnewbieuserhttp://www.sqlservercentral.com/Forums/Topic1419147-1042-1.aspx[quote][b]newbieuser (2/12/2013)[/b][hr]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[/quote]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 [i]this [/i]to the SET of data when t[i]his other criteria is true[/i] is the DBA's job...look at things as Sets.Tue, 12 Feb 2013 14:40:57 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1419147-1042-1.aspxThanks 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 helpTue, 12 Feb 2013 14:34:29 GMTnewbieuserhttp://www.sqlservercentral.com/Forums/Topic1419147-1042-1.aspxbah 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.[code] --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 localtabINNER JOIN MyCTE ON MyCTE.rfield3 = localtab.rfield3[/code]Tue, 12 Feb 2013 13:34:45 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1419147-1042-1.aspxamybe...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:[code]SELECT * FROM OPENQUERY( [linked server],'SELECT localtab.field1 FROM REMOTEUSER.TABLE remtabINNER JOIN localtab ON remtab.rfield3 = localtab.rfield3WHERE ((remtab.rfield1 = ''value'' OR remtab.rfield1 = ''value'' ) OR (remtab.rfield2 = ''value'' ) ) ')[/code]Tue, 12 Feb 2013 13:28:52 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1419147-1042-1.aspxHi 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..[code]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[/code] Is it possible to run the above SQL using open query? Any help is much appreciated..Thanks so muchTue, 12 Feb 2013 12:24:50 GMTnewbieuser