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 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...
> 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...
> > 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.
>|||Having the tables cross databases shouldn't affect performance (but cross instances would). Next
step would be considering what index that should be the clustered index.
--
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:A384955A-EB0A-43E1-B2DE-A03768D317E1@.microsoft.com...
> 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...
>> > 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.
>>|||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:
> 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...
>> 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.
-Dave Markle
http://www.markleconsulting.com/blog|||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:
> 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.|||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:
> 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:
>> 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.
-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) 0 0
Number of INSERT, UPDATE, DELETE statements 0 0
Rows effected by INSERT, UPDATE, DELETE statements 0 0
Number of SELECT statements 1 1
Rows effected by SELECT statements 43348 43348
Number of user transactions 2 2
Average fetch time 0 0
Cumulative fetch time 0 0
Number of fetches 0 0
Number of open statement handles 0 0
Max number of opened statement handles 0 0
Cumulative number of statement handles 0 0
Network Statistics
Number of server roundtrips 1 1
Number of TDS packets sent 1 1
Number of TDS packets received 2709 2709
Number of bytes sent 644 644
Number of bytes received 1.10936e+007 1.10936e+007
Time Statistics
Cumulative client processing time 0 0
Cumulative wait time on server replies 64 64
"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:
> > 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:
> >
> >> 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.
>
> --
> -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:
> >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 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:
> >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.
>|||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 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 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:
> >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.
>|||On Sun, 28 Jan 2007 08:46:00 -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.
Aha. Was that five seconds / two minutes to your app, or were those
timings from the query analyzer? I've seen situations where the front
end was busy and didn't accept the results for thirty seconds or more,
especially if all development is being done on a single system, was
that your situation?
Not to mention, a dual-64 with (only!) 2gb RAM, running SQLServer and
an open instance of VS, might be running short of memory - VS being an
unbelievable pig. Might run a lot faster on even a small server over
the network.
J.
>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:
>> >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.
>>|||That was from Query Analyzer (five seconds) on the local system. I also
tested on a network with the Access front end on a 2G Thinkpad (5400 rpm
disk) with 1G of memory. There it runs in just under 30 seconds, which is
equivalent to the opening of the main form.
Thanks for your help.
"JXStern" wrote:
> On Sun, 28 Jan 2007 08:46:00 -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.
> Aha. Was that five seconds / two minutes to your app, or were those
> timings from the query analyzer? I've seen situations where the front
> end was busy and didn't accept the results for thirty seconds or more,
> especially if all development is being done on a single system, was
> that your situation?
> Not to mention, a dual-64 with (only!) 2gb RAM, running SQLServer and
> an open instance of VS, might be running short of memory - VS being an
> unbelievable pig. Might run a lot faster on even a small server over
> the network.
> J.
>
>
> >
> >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:
> >>
> >> >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.
> >>
> >>
>|||On Sun, 28 Jan 2007 10:28:01 -0800, TLD
<TLD@.discussions.microsoft.com> wrote:
>That was from Query Analyzer (five seconds) on the local system. I also
>tested on a network with the Access front end on a 2G Thinkpad (5400 rpm
>disk) with 1G of memory. There it runs in just under 30 seconds, which is
>equivalent to the opening of the main form.
>Thanks for your help.
So, was VS open on the "server"?
If I helped, you're welcome,
but I guess I'm still mystified.
J.
>
>"JXStern" wrote:
>> On Sun, 28 Jan 2007 08:46:00 -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.
>> Aha. Was that five seconds / two minutes to your app, or were those
>> timings from the query analyzer? I've seen situations where the front
>> end was busy and didn't accept the results for thirty seconds or more,
>> especially if all development is being done on a single system, was
>> that your situation?
>> Not to mention, a dual-64 with (only!) 2gb RAM, running SQLServer and
>> an open instance of VS, might be running short of memory - VS being an
>> unbelievable pig. Might run a lot faster on even a small server over
>> the network.
>> J.
>>
>>
>> >
>> >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:
>> >>
>> >> >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.
>> >>
>> >>
>>|||It's still shocking that these queries take more than a second to execute.
JXStern wrote:
> On Sun, 28 Jan 2007 10:28:01 -0800, TLD
> <TLD@.discussions.microsoft.com> wrote:
>> That was from Query Analyzer (five seconds) on the local system. I also
>> tested on a network with the Access front end on a 2G Thinkpad (5400 rpm
>> disk) with 1G of memory. There it runs in just under 30 seconds, which is
>> equivalent to the opening of the main form.
>> Thanks for your help.
> So, was VS open on the "server"?
> If I helped, you're welcome,
> but I guess I'm still mystified.
> J.
>
>> "JXStern" wrote:
>> On Sun, 28 Jan 2007 08:46:00 -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.
>> Aha. Was that five seconds / two minutes to your app, or were those
>> timings from the query analyzer? I've seen situations where the front
>> end was busy and didn't accept the results for thirty seconds or more,
>> especially if all development is being done on a single system, was
>> that your situation?
>> Not to mention, a dual-64 with (only!) 2gb RAM, running SQLServer and
>> an open instance of VS, might be running short of memory - VS being an
>> unbelievable pig. Might run a lot faster on even a small server over
>> the network.
>> J.
>>
>>
>> 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:
>> 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.
>>
>
-Dave Markle
http://www.markleconsulting.com/blog|||You're not the only one who's mystified.
The summary is this:
When the query had the field TriggerContracts plus those following, it took
1m55s to run locally on workstation, 2m20s to run on networked laptop. When
the number of fields was reduced, it took 5s to run locally on workstation,
30s to run on networked laptop. It didn't matter which fields were there; it
was purely the number of them.
What I've learned from all of this is that, to test a join you should use
only one field from each table.
Thanks again,
Tom
"Dave Markle" <"dma[remove_ZZ]ZZrkle" wrote:
> It's still shocking that these queries take more than a second to execute.
> JXStern wrote:
> > On Sun, 28 Jan 2007 10:28:01 -0800, TLD
> > <TLD@.discussions.microsoft.com> wrote:
> >
> >> That was from Query Analyzer (five seconds) on the local system. I also
> >> tested on a network with the Access front end on a 2G Thinkpad (5400 rpm
> >> disk) with 1G of memory. There it runs in just under 30 seconds, which is
> >> equivalent to the opening of the main form.
> >>
> >> Thanks for your help.
> >
> > So, was VS open on the "server"?
> >
> > If I helped, you're welcome,
> > but I guess I'm still mystified.
> >
> > J.
> >
> >
> >
> >>
> >> "JXStern" wrote:
> >>
> >> On Sun, 28 Jan 2007 08:46:00 -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.
> >> Aha. Was that five seconds / two minutes to your app, or were those
> >> timings from the query analyzer? I've seen situations where the front
> >> end was busy and didn't accept the results for thirty seconds or more,
> >> especially if all development is being done on a single system, was
> >> that your situation?
> >>
> >> Not to mention, a dual-64 with (only!) 2gb RAM, running SQLServer and
> >> an open instance of VS, might be running short of memory - VS being an
> >> unbelievable pig. Might run a lot faster on even a small server over
> >> the network.
> >>
> >> J.
> >>
> >>
> >>
> >>
> >> 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:
> >>
> >> 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.
> >>
> >>
> >
>
> --
> -Dave Markle
> http://www.markleconsulting.com/blog
>