|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Select from a diffrent database in a Stored procedure.Hi I have a Stored Procedure in Database DB_A, where i'm getting data from
Database DB_B, both on the same SQL 2000 server S. Like. Select * from DB_B.dbo.Table_A A, DB_B.dbo.Table_B B Where A.A_Id = B.A_Id And it works fine, but it is very slow, compared to running the SQL on the DB_B. What can I do to improve the speed? I have tried with triggers ect. Regards Karsten Lundsgaard Accessing a different database should not be any different performance-wise
from having both objects in the same database. Have you compared the execution plans of the two queries? Are there indexes on A_Id and B_Id? Have you considered using proper joins instead of this deprecated variety, and not using SELECT *? A Show quote "Karsten Lundsgaard" <KarstenLundsga***@discussions.microsoft.com> wrote in message news:A125B4DA-B293-41C4-9329-8846FE6AF85B@microsoft.com... > Hi I have a Stored Procedure in Database DB_A, where i'm getting data from > Database DB_B, both on the same SQL 2000 server S. Like. > Select * > from > DB_B.dbo.Table_A A, > DB_B.dbo.Table_B B > Where > A.A_Id = B.A_Id > > And it works fine, but it is very slow, compared to running the SQL on the > DB_B. > > What can I do to improve the speed? > I have tried with triggers ect. > > Regards > > Karsten Lundsgaard > > > > > Karsten Lundsgaard wrote:
Show quote > Hi I have a Stored Procedure in Database DB_A, where i'm getting data from There should be no performance difference cause by a cross-database > Database DB_B, both on the same SQL 2000 server S. Like. > Select * > from > DB_B.dbo.Table_A A, > DB_B.dbo.Table_B B > Where > A.A_Id = B.A_Id > > And it works fine, but it is very slow, compared to running the SQL on the > DB_B. > > What can I do to improve the speed? > I have tried with triggers ect. > > Regards > > Karsten Lundsgaard > > > > > query on the same server. Look at the execution plan, see where the bottleneck is. Hi
You should not use * in production code, only specify the columns you require. Try: SELECT A.A_Id, B.A_Id FROM DB_B.dbo.Table_A A, JOIN DB_B.dbo.Table_B B ON A.A_Id = B.A_Id You could also create the procedure on database B and call it USE DB_B GO CREATE PROCEDURE ProcB AS SELECT A.A_Id, B.A_Id FROM dbo.Table_A A, JOIN dbo.Table_B B ON A.A_Id = B.A_Id GO USE DB_A GO EXEC DB_B.dbo.ProcB John Show quote "Karsten Lundsgaard" <KarstenLundsga***@discussions.microsoft.com> wrote in message news:A125B4DA-B293-41C4-9329-8846FE6AF85B@microsoft.com... > Hi I have a Stored Procedure in Database DB_A, where i'm getting data from > Database DB_B, both on the same SQL 2000 server S. Like. > Select * > from > DB_B.dbo.Table_A A, > DB_B.dbo.Table_B B > Where > A.A_Id = B.A_Id > > And it works fine, but it is very slow, compared to running the SQL on the > DB_B. > > What can I do to improve the speed? > I have tried with triggers ect. > > Regards > > Karsten Lundsgaard > > > > > Thanks for the answers Aaron, Tracy and John.
I know that I shouldn't use "Select *", the SQL-statement is just an example, the real SQL-statement is complex. John, I cannot use the "Use DB_B" in a Stored Procedure. Thanks for your help, I will study the ExecutionsPlans some more. Karsten Lundsgaard. Hi
The USE statement was because he procedures were being created in one database and to run it you had to be in the other, you would not need it in your actual procedure. John Show quote "Karsten Lundsgaard" <KarstenLundsga***@discussions.microsoft.com> wrote in message news:3F3581A5-B7F9-4487-893E-05182AB6F396@microsoft.com... > Thanks for the answers Aaron, Tracy and John. > > I know that I shouldn't use "Select *", the SQL-statement is just an > example, the real SQL-statement is complex. > > John, I cannot use the "Use DB_B" in a Stored Procedure. > > Thanks for your help, I will study the ExecutionsPlans some more. > > Karsten Lundsgaard.  |
|||||||||||||||||||||||
Other interesting topics