Performance of query joining tables linked between different datab

The following query takes almost two minutes to run:
SELECT Reference.dbo.tlkpInstruments.IDInstrument,
Reference.dbo.tlkpInstruments.Instrument, dbo.tblAccounts.*
FROM dbo.tblAccounts LEFT OUTER JOIN
Reference.dbo.tlkpInstruments ON dbo.tblAccounts.Commodities =
Reference.dbo.tlkpInstruments.IDInstrument
tlkpInstruments contains 4140 rows. Primary key is IDInstrument (int).
All rows retrieved in less than 5 seconds.
tblAccounts contains 43348 rows. Primary key is AccID (bigint) + OffID (int)
.
All rows retrieved in less than 5 seconds.
Why is the query running so slowly?
Thanks.Start by adding indexes on the columns that you do the join over. Also consi
der making one or even
both of the indexes clustered indexes (assuming that is suitable).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"TLD" <TLD@.discussions.microsoft.com> wrote in message
news:5FF860E0-152C-4C5C-AB90-1FA78D9E05B1@.microsoft.com...
> The following query takes almost two minutes to run:
> SELECT Reference.dbo.tlkpInstruments.IDInstrument,
> Reference.dbo.tlkpInstruments.Instrument, dbo.tblAccounts.*
> FROM dbo.tblAccounts LEFT OUTER JOIN
> Reference.dbo.tlkpInstruments ON dbo.tblAccounts.Commodities =
> Reference.dbo.tlkpInstruments.IDInstrument
> tlkpInstruments contains 4140 rows. Primary key is IDInstrument (int).
> All rows retrieved in less than 5 seconds.
> tblAccounts contains 43348 rows. Primary key is AccID (bigint) + OffID (in
t).
> All rows retrieved in less than 5 seconds.
> Why is the query running so slowly?
> Thanks.|||Here are table and index definitions:
CREATE TABLE [dbo].[tblAccounts] (
[AccID] [bigint] NOT NULL ,
[OffID] [int] NOT NULL ,
[ID] [bigint] NULL ,
[AccNo] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AccName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Office] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Level] [tinyint] NULL ,
[3rdPartyGty] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
[Division] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[USRating] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UKRating] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[InitReqmnts] [int] NULL ,
[StressExposure] [int] NULL ,
[MarDefExcess] [int] NULL ,
[ARTL] [int] NULL ,
[SRTL] [int] NULL ,
[IMTL] [int] NULL ,
[PCOvr] [tinyint] NULL ,
[Action] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EvalDate] [smalldatetime] NULL ,
[RenewDate] [smalldatetime] NULL ,
[LevelPrevious] [int] NULL ,
[EvalDatePrevious] [smalldatetime] NULL ,
[Note] [varchar] (125) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Exclude] [bit] NULL ,
[NetLiquidity] [int] NULL ,
[MaxRisk] [int] NULL ,
[NotAmt] [int] NULL ,
[MarReqd] [int] NULL ,
[Country] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SHEquity] [int] NULL ,
[EstProfit] [int] NULL ,
[Exposure] [numeric](10, 2) NULL ,
[Options] [bit] NULL ,
[Contracts] [int] NULL ,
[Symbol] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PriceCurrent] [money] NULL ,
[PriceHistoric] [money] NULL ,
[Documentation] [bit] NULL ,
[Commodities] [int] NULL ,
[Comment] [varchar] (125) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[MD] [bit] NULL ,
[Diversified] [smallint] NULL ,
[Doc] [smallint] NULL ,
[Credit] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Legal] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Accepted] [smallint] NULL ,
[Man] [bit] NULL ,
[TriggerContracts] [int] NULL ,
[Inst] [nvarchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
CREATE UNIQUE CLUSTERED
INDEX [PK_tblAccounts] ON [dbo].[tblAccounts] ([AccID], [
;OffID],
[Commodities])
WITH
FILLFACTOR = 90
,DROP_EXISTING
ON [PRIMARY]
CREATE TABLE [dbo].[tlkpInstruments] (
[IDInstrument] [int] IDENTITY (1, 1) NOT NULL ,
[Instrument] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NO
T NULL ,
[Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ExchangeID] [int] NOT NULL ,
[GroupID] [int] NULL ,
[Collective] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL
) ON [PRIMARY]
CREATE UNIQUE CLUSTERED
INDEX [PK_tlkpInstruments] ON [dbo].[tlkpInstruments] ([IDIn
strument])
WITH
DROP_EXISTING
ON [PRIMARY]
"TLD" wrote:

> The following query takes almost two minutes to run:
> SELECT Reference.dbo.tlkpInstruments.IDInstrument,
> Reference.dbo.tlkpInstruments.Instrument, dbo.tblAccounts.*
> FROM dbo.tblAccounts LEFT OUTER JOIN
> Reference.dbo.tlkpInstruments ON dbo.tblAccounts.Commodities =
> Reference.dbo.tlkpInstruments.IDInstrument
> tlkpInstruments contains 4140 rows. Primary key is IDInstrument (int).
> All rows retrieved in less than 5 seconds.
> tblAccounts contains 43348 rows. Primary key is AccID (bigint) + OffID (in
t).
> All rows retrieved in less than 5 seconds.
> Why is the query running so slowly?
> Thanks.|||The two minutes is slow, of course,
But your "5 seconds" timings are suspect, too.
Are you running on a tiny, tiny machine, or on a busy server with just
all kinds of other stuff going on?
You ought to be able to fetch both tables and the join in five seconds
easy, even with no keys at all, on anything like a current machine.
J.
On Fri, 26 Jan 2007 09:38:01 -0800, TLD
<TLD@.discussions.microsoft.com> wrote:

>The following query takes almost two minutes to run:
>SELECT Reference.dbo.tlkpInstruments.IDInstrument,
>Reference.dbo.tlkpInstruments.Instrument, dbo.tblAccounts.*
>FROM dbo.tblAccounts LEFT OUTER JOIN
> Reference.dbo.tlkpInstruments ON dbo.tblAccounts.Commodities =
>Reference.dbo.tlkpInstruments.IDInstrument
>tlkpInstruments contains 4140 rows. Primary key is IDInstrument (int).
>All rows retrieved in less than 5 seconds.
>tblAccounts contains 43348 rows. Primary key is AccID (bigint) + OffID (int
).
>All rows retrieved in less than 5 seconds.
>Why is the query running so slowly?
>Thanks.