Ineffecient inner join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Ineffecient inner join

Author

Topic

Sean_B
Posting Yak Master

111 Posts

Posted - 2006-12-01 : 04:51:42

Hi,

Can anyone help please ?

I've got a large table (20 million rows) that I'm joining to several other tables. There is one table in particular that my query slows up on, and I can't see why it is, I think I've created all the appropriate indexes.

Any suggestions would be greatly appreciated.

Here's the query
_____________________________
Select count(*)
from IbesEstimateHist hist
inner join IbesEstimate est on est.IbesEstimateId = hist.IbesEstimateId
inner join IbesIdentifier id on est.IbesIdentifierId = id.IbesIdentifierId
and substring(id.IbesTicker,1,1) = 'B' --added to limit the query
inner join IbesForecastPeriodIndicator fpi on fpi.IbesForecastPeriodIndId = est.IbesForecastPeriodIndId
inner join IbesMeasure mea on est.IbesMeasureId = mea.IbesMeasureId
inner join IbesEstimator estr on est.IbesEstimatorId = estr.IbesEstimatorId
inner join IbesAnalyst anal on hist.IbesAnalystId = anal.IbesAnalystId
inner join IbesCurrency cur on cur.IbesCurrencyId = hist.IbesCurrencyId
_____________________________

Using a subsection of my data (about a million rows)it runs in 20 secs or so until I include the IbesCurrency table join when it suddenly takes 4 minutes, even though this is probably the smallest table with only 107 rows.

Below is the table create script for the currency table.

CREATE TABLE [dbo].[IbesCurrency](
[IbesCurrencyId] [int] IDENTITY(1,1) NOT NULL,
[CurrencyCode] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CurrencyName] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Representation] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_IbesCurrency] PRIMARY KEY CLUSTERED
(
[IbesCurrencyId] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE UNIQUE NONCLUSTERED INDEX [IX_IbesCurrencyCode] ON [dbo].[IbesCurrency]
(
[CurrencyCode] ASC
)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

Sean_B

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-12-01 : 05:05:38

try changing
and substring(id.IbesTicker,1,1) = 'B' --added to limit the query
to
and id.IbesTicker like 'B%' --added to limit the query

Sean_B
Posting Yak Master

111 Posts

Posted - 2006-12-01 : 05:13:44

Thanks,
Sorry I should have remoived this line as I added this to the query afterwards so that I could run it with a small set of data, it isn't part of the final query.

Sean_B

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-01 : 05:20:42

Can you post the textual execution plan for the query?

Harsh Athalye
India.
"Nothing is Impossible"

Sean_B
Posting Yak Master

111 Posts

Posted - 2006-12-01 : 06:03:59

Hi Harsh,

Thanks for this I didn't think of running the execution plan.

I've run the query within management studio to produce an execution plan as a diagram although I'm not sure how to get this as a text file. The search of the clustered index on the currency table was the problem, I'm not sure why.

I've dropped the currency table and recreated it and rerun the query and the problem has disappeared.

Thanks for your advice.

Sean_B

Kristen
Test

22859 Posts

Posted - 2006-12-01 : 06:09:53

"I'm not sure how to get this as a text file"

Comment-IN one of the first two options


-- SET SHOWPLAN_TEXT ON
GO
-- SET STATISTICS IO ON; SET STATISTICS TIME ON

... your query here ...

SET STATISTICS IO OFF; SET STATISTICS TIME OFF
GO
SET SET SHOWPLAN_TEXT OFF
GO

EDIT: It helps to have both sets of answers. The SHOWPLAN shows which indexes etc. are being used, whereas the STATISTICS shows how many SCANs are required for each table, and the amount of Logical I/O.

Kristen

Sean_B
Posting Yak Master

111 Posts

Posted - 2006-12-01 : 08:45:41

Hi,
thanks Kristen.

Even thouh the problem appears to be resolved I've no idea what caused it.

Here are the textual execution plans

****OLD Table - slow query****
|--Compute Scalar(DEFINE:([Expr1016]=CONVERT_IMPLICIT(int,[Expr1021],0)))
|--Stream Aggregate(DEFINE:([Expr1021]=Count(*)))
|--Parallelism(Gather Streams)
|--Nested Loops(Inner Join, OUTER REFERENCES:([est].[IbesMeasureId]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([est].[IbesForecastPeriodIndId]))
| |--Nested Loops(Inner Join, OUTER REFERENCES:([est].[IbesEstimatorId]))
| | |--Nested Loops(Inner Join, OUTER REFERENCES:([hist].[IbesAnalystId]))
| | | |--Nested Loops(Inner Join, OUTER REFERENCES:([est].[IbesIdentifierId]))
| | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([hist].[IbesEstimateId]) OPTIMIZED)
| | | | | |--Hash Match(Inner Join, HASH:([cur].[IbesCurrencyId])=([hist].[IbesCurrencyId]), RESIDUAL:([Equity].[dbo].[IbesCurrency].[IbesCurrencyId] as [cur].[IbesCurrencyId]=[Equity].[dbo].[IbesEstimateHist].[IbesCurr
| | | | | | |--Bitmap(HASH:([cur].[IbesCurrencyId]), DEFINE:([Bitmap1020]))
| | | | | | | |--Parallelism(Distribute Streams, Hash Partitioning, PARTITION COLUMNS:([cur].[IbesCurrencyId]))
| | | | | | | |--Index Scan(OBJECT:([Equity].[dbo].[IbesCurrency].[IX_IbesCurrencyCode] AS [cur]))
| | | | | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([hist].[IbesCurrencyId]), WHERE:(PROBE([Bitmap1020])=TRUE))
| | | | | | |--Clustered Index Scan(OBJECT:([Equity].[dbo].[IbesEstimateHist].[PK_IbesEstimateHist] AS [hist]))
| | | | | |--Clustered Index Seek(OBJECT:([Equity].[dbo].[IbesEstimate].[PK_IbesEstimate] AS [est]), SEEK:([est].[IbesEstimateId]=[Equity].[dbo].[IbesEstimateHist].[IbesEstimateId] as [hist].[IbesEstimateId]) ORDERED FO
| | | | |--Clustered Index Seek(OBJECT:([Equity].[dbo].[IbesIdentifier].[PK_IbesTickerID] AS [id]), SEEK:([id].[IbesIdentifierId]=[Equity].[dbo].[IbesEstimate].[IbesIdentifierId] as [est].[IbesIdentifierId]), WHERE:(subst
| | | |--Clustered Index Seek(OBJECT:([Equity].[dbo].[IbesAnalyst].[PK_IbesAnalyst] AS [anal]), SEEK:([anal].[IbesAnalystId]=[Equity].[dbo].[IbesEstimateHist].[IbesAnalystId] as [hist].[IbesAnalystId]) ORDERED FORWARD)
| | |--Clustered Index Seek(OBJECT:([Equity].[dbo].[IbesEstimator].[PK_IbesContributor] AS [estr]), SEEK:([estr].[IbesEstimatorId]=[Equity].[dbo].[IbesEstimate].[IbesEstimatorId] as [est].[IbesEstimatorId]) ORDERED FORWARD)
| |--Clustered Index Seek(OBJECT:([Equity].[dbo].[IbesForecastPeriodIndicator].[PK_IbesForecastPeriodIndicator] AS [fpi]), SEEK:([fpi].[IbesForecastPeriodIndId]=[Equity].[dbo].[IbesEstimate].[IbesForecastPeriodIndId] as [est].[Ibes
|--Clustered Index Seek(OBJECT:([Equity].[dbo].[IbesMeasure].[PK_IbesMeasure] AS [mea]), SEEK:([mea].[IbesMeasureId]=[Equity].[dbo].[IbesEstimate].[IbesMeasureId] as [est].[IbesMeasureId]) ORDERED FORWARD)

****NEW Table - Fast query****
|--Compute Scalar(DEFINE:([Expr1016]=CONVERT_IMPLICIT(int,[globalagg1018],0)))
|--Stream Aggregate(DEFINE:([globalagg1018]=SUM([partialagg1017])))
|--Parallelism(Gather Streams)
|--Hash Match(Inner Join, HASH:([hist].[IbesAnalystId])=([anal].[IbesAnalystId]), RESIDUAL:([Equity].[dbo].[IbesEstimateHist].[IbesAnalystId] as [hist].[IbesAnalystId]=[Equity].[dbo].[IbesAnalyst].[IbesAnalystId] as [anal].[IbesAnalystId]))
|--Bitmap(HASH:([hist].[IbesAnalystId]), DEFINE:([Bitmap1019]))
| |--Hash Match(Aggregate, HASH:([hist].[IbesAnalystId]), RESIDUAL:([Equity].[dbo].[IbesEstimateHist].[IbesAnalystId] as [hist].[IbesAnalystId] = [Equity].[dbo].[IbesEstimateHist].[IbesAnalystId] as [hist].[IbesAnalystId]) DEFINE:([partialagg1017]=COUNT(*)))
| |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([hist].[IbesAnalystId]))
| |--Hash Match(Inner Join, HASH:([cur].[IbesCurrencyId])=([hist].[IbesCurrencyId]), RESIDUAL:([Equity].[dbo].[IbesCurrency2].[IbesCurrencyId] as [cur].[IbesCurrencyId]=[Equity].[dbo].[IbesEstimateHist].[IbesCurrencyId] as [hist].[IbesCurrencyId]))
| |--Parallelism(Distribute Streams, Hash Partitioning, PARTITION COLUMNS:([cur].[IbesCurrencyId]))
| | |--Index Scan(OBJECT:([Equity].[dbo].[IbesCurrency2].[IX_IbesCurrency2] AS [cur]))
| |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([hist].[IbesCurrencyId]))
| |--Hash Match(Inner Join, HASH:([est].[IbesEstimateId])=([hist].[IbesEstimateId]), RESIDUAL:([Equity].[dbo].[IbesEstimateHist].[IbesEstimateId] as [hist].[IbesEstimateId]=[Equity].[dbo].[IbesEstimate].[IbesEstimateId] as [est].[IbesEstimateId]))
| |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([est].[IbesEstimateId]))
| | |--Hash Match(Inner Join, HASH:([estr].[IbesEstimatorId])=([est].[IbesEstimatorId]), RESIDUAL:([Equity].[dbo].[IbesEstimate].[IbesEstimatorId] as [est].[IbesEstimatorId]=[Equity].[dbo].[IbesEstimator].[IbesEstimatorId] as [estr].[IbesEstimatorId]))
| | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([estr].[IbesEstimatorId]))
| | | |--Index Scan(OBJECT:([Equity].[dbo].[IbesEstimator].[IX_IbesEstimatorCode] AS [estr]))
| | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([est].[IbesEstimatorId]))
| | |--Hash Match(Inner Join, HASH:([mea].[IbesMeasureId])=([est].[IbesMeasureId]), RESIDUAL:([Equity].[dbo].[IbesEstimate].[IbesMeasureId] as [est].[IbesMeasureId]=[Equity].[dbo].[IbesMeasure].[IbesMeasureId] as [mea].[IbesMeasureId]))
| | |--Parallelism(Distribute Streams, Broadcast Partitioning)
| | | |--Index Scan(OBJECT:([Equity].[dbo].[IbesMeasure].[IX_IbesMeasure] AS [mea]))
| | |--Hash Match(Inner Join, HASH:([id].[IbesIdentifierId])=([est].[IbesIdentifierId]))
| | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([id].[IbesIdentifierId]))
| | | |--Index Seek(OBJECT:([Equity].[dbo].[IbesIdentifier].[IX_IbesTicker] AS [id]), SEEK:([id].[IbesTicker] >= '?þ' AND [id].[IbesTicker] < '['), WHERE:([Equity].[dbo].[IbesIdentifier].[IbesTicker] as [id].[IbesTicker] like '@%') ORDERED FORWARD)
| | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([est].[IbesIdentifierId]))
| | |--Hash Match(Inner Join, HASH:([fpi].[IbesForecastPeriodIndId])=([est].[IbesForecastPeriodIndId]), RESIDUAL:([Equity].[dbo].[IbesEstimate].[IbesForecastPeriodIndId] as [est].[IbesForecastPeriodIndId]=[Equity].[dbo].[IbesForecastPeriodIndicator].[IbesForecastPeriodIndId] as [fpi].[IbesForecastPeriodIndId]))
| | |--Parallelism(Distribute Streams, Broadcast Partitioning)
| | | |--Index Scan(OBJECT:([Equity].[dbo].[IbesForecastPeriodIndicator].[IX_IbesForecastPeriodIndicator] AS [fpi]))
| | |--Index Scan(OBJECT:([Equity].[dbo].[IbesEstimate].[ix_estimatedetails] AS [est]))
| |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([hist].[IbesEstimateId]))
| |--Clustered Index Scan(OBJECT:([Equity].[dbo].[IbesEstimateHist].[PK_IbesEstimateHist] AS [hist]))
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([anal].[IbesAnalystId]), WHERE:(PROBE([Bitmap1019])=TRUE) [IN ROW])
|--Index Scan(OBJECT:([Equity].[dbo].[IbesAnalyst].[IX_IbesAnalystCode] AS [anal]))
**********************

If anyone can see what the problem was I'd appreciate knowing.

Thanks for your help

Sean

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-12-01 : 09:36:58

"...I dropped the currency table and recreated it "
This may have re-set the statistics to make the index scan on the 'IbesCurrency' table NOT the best choice for the SQL Optimiser to use.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources