June 13, 2012 at 12:34 pm
I am configuring a Sharepoint 2010 site running on Sql Server 2012. All of our data is in Oracle, but the reporting infrastructure consists of either SSRS, SSAS, or Powerpivot workbooks. I have a Linked Server working well between the sql and oracle servers, but there has been discussion that it should be "best practice" to embed oracle queries directly in the various reports instead of using sql stored procedures using OpenQuery to query the oracle db. I am looking to see if anyone has experianced issues using one over the other or what the common knolwedge base thinks of both methods.
All queries are purely ad hoc report queries that return anywhere up to 100k rows at a time. In my limited testing, I have not found any measureable difference between the two methods evenwith clearing the caches between each run.
Please let me know if there is anything I should be considering or if it truly doesnt matter.
Thanks!
June 13, 2012 at 4:15 pm
Anything but four-part naming query would do it. Four-part naming queries have the tendency of moving the whole target tables over the link then apply filtering on the local host instead of doing it on the remote host.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 13, 2012 at 4:20 pm
Thank you for the response! Do you see any advantages/disadvantages of using embedded queries in the reports vs stored procedures on the sql server? I believe that stored procedures would be better from an administrative standpoint, but that doesnt seem to be an large enough argument.
any other thoughts?
June 14, 2012 at 5:09 am
chenthor (6/13/2012)
Thank you for the response! Do you see any advantages/disadvantages of using embedded queries in the reports vs stored procedures on the sql server? I believe that stored procedures would be better from an administrative standpoint, but that doesnt seem to be an large enough argument.any other thoughts?
My personal take on it is to go with stored procedures. I just love back-end code for a number of reasons that include (but are not limited to):
1- Back-end code ensures that there is a single instance of a particular code; front end code allows for multiple versions - not allways identical - of the same piece of code.
2- Back-end code puts the implementation of changes to the code on the hands of the DBA which ensures proper change control. In a large number of organizations change control on the app side might be a little fuzzy.
3- Using front-end code adds a layer of parsing to each execution.
4- Back-end code helps during performance optimization; there is just one instance of the offending then optimized piece of code and once you have optimized it you are sure nobody touches it anymore (with the exception of proper change control which allows you to re-test the target code).
Hope this helps.
By the way, as Craig Ferguson would say: "...looking forward for your angry posts dear developers :-D"
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply