Databases
StackOverflow2013
Postsdbo
POBest way to perform distributed SQL query and joins, calling from .Net code
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POBest way to perform distributed SQL query and joins, calling from .Net code
    primarykey
    data
    text
    <p>Here's my scenario:</p> <p>I have to query two PeopleSoft Databases on different servers (both are SQL Server 2000) and do a join of the data. My application is a .Net application (BizTalk). </p> <p>I'm wondering what the best option is with regards to performance? </p> <ol> <li><p>use standard select queries to get data and do the join in memory (e.g. LINQ) for example</p></li> <li><p>generated complex dynamic queries using LINKED Server, e.g.</p> <p>select blah from Server1.HRDB.dbo.MyTable1 left join Server2.FinanceDb.dbo.MyTable2</p></li> <li><p>use standard select queries to get the data into an intermediate / staging sql server database and do my queries / joins on this database instead.</p></li> <li><p>should I consider using SSIS? ( are there features here that might be better than doing an in-memory, e.g. LINQ? )</p></li> <li><p>I wish I could use stored procedures on the source database, but the owners of the PeopleSoft database refuse it</p></li> </ol> <p>The main constraints we have is that the source database is old (SQL Server 2000) and that performance of the source database is paramount. Whatever queries I run on this server must not block the other users. Hence, the DBAs are adamant about no Stored Procedures. They also believe that queries involving Linked Servers will trump (i.e. take higher priority) to other queries being run against the the database.</p> <p>Any feedback would be greatly appreciated.</p> <p>Thanks!</p> <p>Update: additional background information on the project We are primarily integrating PeopleSoft databases (the HR and Finance) into another product. Some are simple - like AccountCode and Department. Others are more complex, like the personal data, job, and leave accrual. Some are real-time, other's are scheduled, and other's are 'batch' (e.g. at payroll runs).</p> <p>Regardless, we have to get source data out of PeopleSoft database -- and my hope had been to let the (source) database do the 'heavy' lifting by executing SQL Queries. I don't really want BizTalk, or SSIS, or C# LINQ to be the ones doing the transformations/filtering.</p> <p>Definitely open to suggestions.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USRaymond
    1. USRaymond
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. POBest way to perform distributed SQL query and joins, calling from .Net code
      1. This table or related slice is empty.
      1. VTUpMod
    1. COIf your dba's believe that only stored procedures block users, then they are idiots. Feel free to reference this comment when you tell them so
      singulars
      1. POBest way to perform distributed SQL query and joins, calling from .Net code
      1. USbillinkc
    2. COThat said, your problem is that you need to bring this data together with minimal impact to your users. Normally, I would ask how much latency they can incur but I'm certain you'll say they need real-time to the millisecond, yes? If that's the case, then you're stuck between a rock and very hard place. Querying the data, regardless of stored procedure or dynamic sql, is going to acquire locks. Locks lead to blocking until queries are done. At my client, we are dealing with PS on DB2 for zOS. We take the approach of snapshotting data at 3 in the morning into staging tables and work from there.
      singulars
      1. POBest way to perform distributed SQL query and joins, calling from .Net code
      1. USbillinkc
    3. COLinked server challenge is going to be that to make it efficient, you need to pass your criteria to one of the servers for filtering. Otherwise, you'll be streaming the entire table to the "other" server and then filtering there (where you won't get the benefit of indexes).
      singulars
      1. POBest way to perform distributed SQL query and joins, calling from .Net code
      1. USbillinkc
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload