Query performance problems

[rogerl101]

Hello,

I am running across performance problems with queries that reference tables in separate SQL databases.

I am using SQL7 with five databases on a single machine. The databases merge with other external databases from time to time, and the table structures are identical in each database. For example, Machine1 merges with Database1 on the local server, Machine2 merges with Database2, etc.

When I tested the queries, all of the tables were in a single database and the speed was great.

Query 1 - "Select * From Table1_1"
Query 2 - "Select * From Table1_2"
and so on.

Now I am trying to pull the same data from different databases and the performance has decreased dramatically (1 second query now takes 15 seconds). The queries now look like this.

Query 1 - "Select Database1.dbo.Table1"
Query 2 - "Select Database2.dbo.Table1"
and so on.

Is there something wrong with referencing the tables in this manner? I have looked through everything that I can find, and this seems to be the way to do this.

Thanks.

 

[TheBugSlayer]

Well, factors such as network latency and each remote PC's hardware can take a hit at your queries' performance.

If your db design is optimal and your tables indexed then you should probably setup a job that will pull all that data from the different sources together in the same database and query same one database only.

 

[rogerl101]

The different databases are already on the same machine. I have merge replication set up to periodically dump data from the remote machines on the LAN to the local server. All the data access is done locally. Merge replication won't let me rename destination tables (I don't think), and I need to maintain a consistent table structure on the remote servers, therefore I can't put them all in the same database locally.

 

[rogerl101]

After reviewing the Indexes a little on each separate database, I was able to speed up performance on each one to acceptable levels. That will teach me to rely solely on the Index Tuning Wizard to optimize database.

Thanks BugSlayer for your previous post.