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.
The linked column in the subsidiary table is the primary key of that table
and is indexed that way. In the main table, I've tried indexing the linked
column separately and adding it to the primary key. Neither approach makes
any substantial change.
I've also tried importing the subsidiary table into the main database. There
is no change to the performance.
Anything else I can try?
"Tibor Karaszi" wrote:

> Start by adding indexes on the columns that you do the join over. Also consider 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...
>
|||99% chance it's the way you've indexed the tables.
Post the DDL (CREATE TABLE and CREATE INDEX statements) and someone can
probably help.
-Dave
TLD wrote:[vbcol=seagreen]
> The linked column in the subsidiary table is the primary key of that table
> and is indexed that way. In the main table, I've tried indexing the linked
> column separately and adding it to the primary key. Neither approach makes
> any substantial change.
> I've also tried importing the subsidiary table into the main database. There
> is no change to the performance.
> Anything else I can try?
> "Tibor Karaszi" wrote:
-Dave Markle
http://www.markleconsulting.com/blog
|||TLD, I don't see a thing wrong with your query from a performance
perspective.
Try this:
SELECT
i.IDInstrument,
i.Instrument,
a.*
into #bleh
FROM dbo.tblAccounts a
LEFT JOIN dbo.tlkpInstruments i
ON a.Commodities = i.IDInstrument;
DROP TABLE #bleh;
How long does that take? If it's quick, it probably indicates that you
have a problem with the speed of returning rows to the client. This can
be caused by a network configuration issue or by having some task on the
client side that's eating up CPU cycles.
What happens when you put this in front of your query? What are the
results in your "Messages" window?
SET STATISTICS IO ON;
-Dave
TLD wrote:[vbcol=seagreen]
> 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 NULL ,
> [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 NOT 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 NULL
> ) ON [PRIMARY]
> CREATE UNIQUE CLUSTERED
> INDEX [PK_tlkpInstruments] ON [dbo].[tlkpInstruments] ([IDInstrument])
> WITH
> DROP_EXISTING
> ON [PRIMARY]
>
> "TLD" wrote:
-Dave Markle
http://www.markleconsulting.com/blog
|||This is a local instance of SServer. There is no network connection. The only
client running is Query Analyzer.
The SELECT... INTO took one second.
Re-running the query takes 1m54s:
Application Profile Statistics
Timer resolution (milliseconds)00
Number of INSERT, UPDATE, DELETE statements00
Rows effected by INSERT, UPDATE, DELETE statements00
Number of SELECT statements11
Rows effected by SELECT statements4334843348
Number of user transactions22
Average fetch time00
Cumulative fetch time00
Number of fetches00
Number of open statement handles00
Max number of opened statement handles00
Cumulative number of statement handles00
Network Statistics
Number of server roundtrips11
Number of TDS packets sent11
Number of TDS packets received27092709
Number of bytes sent644644
Number of bytes received1.10936e+0071.10936e+007
Time Statistics
Cumulative client processing time00
Cumulative wait time on server replies6464
"Dave Markle" <"dma[remove_ZZ]ZZrkle" wrote:

> TLD, I don't see a thing wrong with your query from a performance
> perspective.
> Try this:
> SELECT
> i.IDInstrument,
> i.Instrument,
> a.*
> into #bleh
> FROM dbo.tblAccounts a
> LEFT JOIN dbo.tlkpInstruments i
> ON a.Commodities = i.IDInstrument;
> DROP TABLE #bleh;
> How long does that take? If it's quick, it probably indicates that you
> have a problem with the speed of returning rows to the client. This can
> be caused by a network configuration issue or by having some task on the
> client side that's eating up CPU cycles.
> What happens when you put this in front of your query? What are the
> results in your "Messages" window?
> SET STATISTICS IO ON;
> -Dave
>
> TLD wrote:
>
> --
> -Dave Markle
> http://www.markleconsulting.com/blog
>
|||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.
|||I'm using a dual-Opteron 64 workstation with 2G of memory. SQL Server is
installed as a local instance. There is no active network connection.
"JXStern" wrote:

> 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:
>
>
|||I should add that the performance is the same when running with an Access
front-end in a server environment. This is why I'm trying to get it
straightened out on a local machine first. The speed of return of the tables
by themselves is less than five seconds, more like three.
This shouldn't make any difference but there are many more records in the
main table without an associated record in the subsidiary than there are with
one. That brings up a lot of NULLs.
What about the order of the fields in the join? Could that make any
difference?
"JXStern" wrote:

> 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:
>
>
|||Here is an answer:
The field which is causing the delay is
TriggerContracts int 4
When all except this field are accessed with the join, the return is less
than five seconds. When this field is included the return is two minutes.
Bracketing the name doesn't change the performance. Changing the name to
TContracts doesn't change the performance. Add the field to the clustered
index doesn't change the performance. What in the world is going on?
"JXStern" wrote:

> 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 problem has been solved. By reworking my front end, I was able to
eliminate some of the fields in the query. Now it runs in less than five
seconds.
Thanks to all for your help.
"JXStern" wrote:

> 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:
>
>