Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
MrRay
Starting Member
28 Posts |
 Posted - 2005-08-24 : 10:43:52
|
| Hi there,I have a datatable containing about 150 employee UserIDs. For each UserID I need to hit a corporate database and extract the rest of the user's info (name, employee#, etc). Note: The corporate database contains about 2,500 employees.Solution 1: I could loop through the datatable of 150 employees and, for each of the entries, hit the corporate database with a SELECT statement to extract the employee's data.Solution 2: I could hit the corporate database with a single SELECT statement extracting the information for all 2,500 employees, and then sift through the large datatable looking for the info I need on the 150 employees.I'm not sure which is worse - tying up the SQL server with 150 queries in a row, or tying up the IIS server's resources while I play around with an unnecessarily large datatable.I suppose a third solution would be to programmatically add the UserIDs I want to the SELET statement, but that would give me a huge SELECT query, and I don't know if that's a good idea or not.Any recommendations? I'm also open to any other solutions.Thanks,Benjamin Ray |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-08-24 : 11:55:54
|
| is the corporate database on a physically separate box?-ec |
 |
|
|
MrRay
Starting Member
28 Posts |
Posted - 2005-08-24 : 11:59:06
|
quote:
Originally posted by eyechart
is the corporate database on a physically separate box?-ec
Yes, the two databases are on separate boxes, so I don't believe I can accomplish this without at least two separate queries (one to get my users, one to get their corporate info). |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-08-24 : 12:15:33
|
| you could make a linked server and try to JOIN (as Brett suggested) This will probably be the fastest and cleanest method to use. Certainly the cleanest.-ec |
|
|
|
MrRay
Starting Member
28 Posts |
Posted - 2005-08-24 : 12:33:29
|
quote:
Originally posted by eyechart
you could make a linked server and try to JOIN (as Brett suggested) This will probably be the fastest and cleanest method to use. Certainly the cleanest.-ec
I could see how that would be helpful, but in the end I really do want to end up with two separate datatables: dtMyUsers and dtCorporateUsers. That way I can go through the data one record at a time and update my database with the corporate data in the event of a discrepancy.My newest (and currently-functional) solution is this: Fill dtMyUsers with info from my database, convert the column of UserIDs to a string strUserIDs (containing "'ID1','ID2','ID3',..." etc), then hit the corporate database with a single SELECT statement: "SELECT [stuff] where UserID IN (" & strUserIDs & ")"This gives me a datatable containing the corporate info for the same 150 users in dtMyUsers. From there I can use VB to compare the two datatables and do what I need to do. This way I avoid doing 150 separate queries, and I avoid doing a single 2,500 record query (of which 2350 records would be useless to me). |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-25 : 00:27:37
|
| If this is an ongoing need, and the SQL Server can "link" to the Corporate Database, but you don't want to do real time JOINs, I would look at keeping a copy of the corporate information in your database (just for the actually employees in your database sounds right to me).This could then be freshened up periodically, or on demand, and then just JOINed to your data whenever you needed to.Kristen |
|
|
|
MrRay
Starting Member
28 Posts |
Posted - 2005-08-25 : 05:59:36
|
quote:
Originally posted by Kristen
If this is an ongoing need, and the SQL Server can "link" to the Corporate Database, but you don't want to do real time JOINs, I would look at keeping a copy of the corporate information in your database (just for the actually employees in your database sounds right to me).This could then be freshened up periodically, or on demand, and then just JOINed to your data whenever you needed to.Kristen
That's also a good solution - I'll look into that.Thanks! |
|
|
|
|
|
|
|
|
1 Large Query or 150 Small Queries?