Query process time

I have a weird issue that I just can't figure out. I have a query that runs on tables that are all located in db x. If I run the query under a different db (like use database y) then it runs in like 11 seconds. If I run the query under the db that all of the tables reside under (use database x) then it takes almost 4 minutes.

Why would it take so much longer to run under the db where all of the data/tables exists? Both dbs are on the same SQL 2005 server.

I just don't get it?

Any ideas?

S

We can't possibly give you a meaningful response without having more information. What is the query, what is the nature of the data, what is different between the two databases, etc.

Perhaps, using your description about, database X has data in it, and database Y does not?

|||If you think that execution plan will be same then why SQl statement take different time on different server. But have you checked the CPU Cost and I/O cost.

query multiple databases on different servers

I want to query from a SQL Server 2000 database and put the results into a
SQL Server 2005 database located on another server. I thought that I had
used the following syntax in the past (without added any linked servers):
INSERT INTO houston.mytestdb.dbo.memo
SELECT * FROM tennessee.mytestdb.dbo.memo
But I get an error: Could not find server 'tennessee' in sysservers. Execute
sp_addlinkedserver...
If I do add this server using the stored procedure I get the following
errors when trying to execute the sql:
Server: Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
Server: Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
OLE DB provider "SQLNCLI" for linked server "tennessee" returned message
"Communication link failure".
What's that all about?
Anyway I can avoid adding linked servers in the first place?
Thanks!
-j
The error you are getting is that the link is trying to use integrated
security and there is no context being passed. Could be a lot of reasons
- firewall, no trust between domains, ...
Did you set you set up the linked server to use integrated security?
You will also probably need to make sure that dtc is started on both
m/c's.
Do you really want to own a remote table from the local server? It's
usually better to populate from the machine where the table resides
calling a stored procedure to extract.
*** Sent via Developersdex http://www.codecomments.com ***

query multiple databases on different servers

I want to query from a SQL Server 2000 database and put the results into a
SQL Server 2005 database located on another server. I thought that I had
used the following syntax in the past (without added any linked servers):
INSERT INTO houston.mytestdb.dbo.memo
SELECT * FROM tennessee.mytestdb.dbo.memo
But I get an error: Could not find server 'tennessee' in sysservers. Execute
sp_addlinkedserver...
If I do add this server using the stored procedure I get the following
errors when trying to execute the sql:
Server: Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
Server: Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
OLE DB provider "SQLNCLI" for linked server "tennessee" returned message
"Communication link failure".
What's that all about?
Anyway I can avoid adding linked servers in the first place?
Thanks!
-jThe error you are getting is that the link is trying to use integrated
security and there is no context being passed. Could be a lot of reasons
- firewall, no trust between domains, ...
Did you set you set up the linked server to use integrated security?
You will also probably need to make sure that dtc is started on both
m/c's.
Do you really want to own a remote table from the local server? It's
usually better to populate from the machine where the table resides
calling a stored procedure to extract.
*** Sent via Developersdex http://www.codecomments.com ***

query multiple databases on different servers

I want to query from a SQL Server 2000 database and put the results into a
SQL Server 2005 database located on another server. I thought that I had
used the following syntax in the past (without added any linked servers):
INSERT INTO houston.mytestdb.dbo.memo
SELECT * FROM tennessee.mytestdb.dbo.memo
But I get an error: Could not find server 'tennessee' in sysservers. Execute
sp_addlinkedserver...
If I do add this server using the stored procedure I get the following
errors when trying to execute the sql:
Server: Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
Server: Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
OLE DB provider "SQLNCLI" for linked server "tennessee" returned message
"Communication link failure".
What's that all about?
Anyway I can avoid adding linked servers in the first place?
Thanks!
-jThe error you are getting is that the link is trying to use integrated
security and there is no context being passed. Could be a lot of reasons
- firewall, no trust between domains, ...
Did you set you set up the linked server to use integrated security?
You will also probably need to make sure that dtc is started on both
m/c's.
Do you really want to own a remote table from the local server? It's
usually better to populate from the machine where the table resides
calling a stored procedure to extract.
*** Sent via Developersdex http://www.developersdex.com ***