|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
DB to DB spHi,
i am writing a tool that interfaces to a third-party database. I will be creating a database for my tool which contains stored procedures that accesses tables from the third-party database. I am doing this only to aviod manipulating the third party database. My database will be located in the same server as the third-party database. My question is, if i were to create these stored procedures and views that accesses only tables on the third-party database in my database, would my app still benefit from the performance gain which stored procedures and views offers? regards
Show quote
"Emmanuel" <emman***@email.com> wrote in message That depends...news:OP8RCD4kFHA.3580@TK2MSFTNGP09.phx.gbl... > Hi, > > i am writing a tool that interfaces to a third-party database. I will be > creating a database for my tool which contains stored procedures that > accesses tables from the third-party database. I am doing this only to > aviod manipulating the third party database. My database will be located > in the same server as the third-party database. > > My question is, if i were to create these stored procedures and views that > accesses only tables on the third-party database in my database, would my > app still benefit from the performance gain which stored procedures and > views offers? > > regards > > Are you going to use OPENROWSET and/or OPENQUERY, or are you going to create a Linked server? Rick Sawtell MCT, MCSD, MCDBA Hi Rick,
No, since the database will be created on the same server i will access the third-party db tables using queries like: SELECT column from [ThirdPartyDB].[dbo].[ThirdPartyTable] and at most i would make queries which join some tables.. Show quote "Rick Sawtell" <r_sawt***@hotmail.com> wrote in message news:OenmiI4kFHA.3312@tk2msftngp13.phx.gbl... > > "Emmanuel" <emman***@email.com> wrote in message > news:OP8RCD4kFHA.3580@TK2MSFTNGP09.phx.gbl... >> Hi, >> >> i am writing a tool that interfaces to a third-party database. I will be >> creating a database for my tool which contains stored procedures that >> accesses tables from the third-party database. I am doing this only to >> aviod manipulating the third party database. My database will be located >> in the same server as the third-party database. >> >> My question is, if i were to create these stored procedures and views >> that accesses only tables on the third-party database in my database, >> would my app still benefit from the performance gain which stored >> procedures and views offers? >> >> regards >> >> > > That depends... > > Are you going to use OPENROWSET and/or OPENQUERY, or are you going to > create a Linked server? > > > Rick Sawtell > MCT, MCSD, MCDBA > > > > My question is, if i were to create these stored procedures and views that Yes, if on the same server (instance).> accesses only tables on the third-party database in my database, would my > app still benefit from the performance gain which stored procedures and > views offers? -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ "Emmanuel" <emman***@email.com> wrote in message news:OP8RCD4kFHA.3580@TK2MSFTNGP09.phx.gbl... > Hi, > > i am writing a tool that interfaces to a third-party database. I will be > creating a database for my tool which contains stored procedures that > accesses tables from the third-party database. I am doing this only to aviod > manipulating the third party database. My database will be located in the > same server as the third-party database. > > My question is, if i were to create these stored procedures and views that > accesses only tables on the third-party database in my database, would my > app still benefit from the performance gain which stored procedures and > views offers? > > regards > > Yes, this will work; we do it all the time.
Having had a lot of expeience with integrating with 3rd-part databases, here's some hard-earned lessons: 1. If you plan on EVER upgrading the third-party aplication, build a relationship with the vendor. Let them know what you're doing, and find out if they have a recommended solution for what you are planning to do. 2. Always try to integrate using an application layer BEFORE going to the data layer. Sometimes you have no choice, but if they support an API, use it. It will save you some major headaches when they change their schema at a later date (and that happens more often than you know). 3. If there is no API, see if you can get them to commit to a standard schema for exposng their database (much like Microsoft's INFORMATION_SCHEMA for their system tables). HTH, Stu Hi,
thanks everyone! this was really helpful. regards, Show quote "Stu" <stuart.ainswo***@gmail.com> wrote in message news:1122564554.859930.125160@g44g2000cwa.googlegroups.com... > Yes, this will work; we do it all the time. > > Having had a lot of expeience with integrating with 3rd-part databases, > here's some hard-earned lessons: > > 1. If you plan on EVER upgrading the third-party aplication, build a > relationship with the vendor. Let them know what you're doing, and > find out if they have a recommended solution for what you are planning > to do. > > 2. Always try to integrate using an application layer BEFORE going to > the data layer. Sometimes you have no choice, but if they support an > API, use it. It will save you some major headaches when they change > their schema at a later date (and that happens more often than you > know). > > 3. If there is no API, see if you can get them to commit to a standard > schema for exposng their database (much like Microsoft's > INFORMATION_SCHEMA for their system tables). > > HTH, > Stu >  |
|||||||||||||||||||||||
Other interesting topics