one SQL server, multiple databases, one asp web project

[DKode]

I am preparing to build an app that will pull data from multiple
databases on the same sql server.

For performance sake, is there a more "effecient" way to grab data from
multiple databases at once? I've read a couple of threads and noone
really seemed to give a straight forward answer. I would imagine in a
SP in tsql, that I could just specify the database.owner.table and it
would be the most effecient, just checking my options.

Thank you

Sean

 

[Curt_C [MVP]]

I'd suggest hitting up a SQL group... they may have better info on the
efficiencies of MS SQL

 

[Kevin Spencer]

I'm not sure what you're asking. Of course, if you pull data from more than
one database in SQL Server, you will have to specify the database name(s) in
the query. your Connection String CAN specify a default database, in which
case your queries do not have to specify the database name (sort of like
"imports" or "using" in .Net code). However, if the query is using more than
one, at least the non-default database names must be specified in the query.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
What You Seek Is What You Get.

 

[Guest]

It sounds like you are talking about Linked Servers to join your app's
database table to other tables in another database or to just run queries
against the linked server.

We've done both. I had to use data from several databases for an
application, so for performance reasons, I just run a nightly dts package to
import only the data I need into my db.

On the other hand, someone else in our group is using a linked server to run
a query against a view in another database without any issues.

According our dba's, linked servers can have a performance hit if there is a
lot of traffic so it depends on your situation

Dave