September 1, 2004 at 8:03 am
I have a join that produces different exceution plans on different database with similar structure, and because of this, dramatic execution time differences.
The joins use "with (index='IX_TEST')" on both databases. The index is not clustered and defined identicaly on both databases.
On the first database it uses a Index Seek and the result is an estimated row count of 1. The second database uses an Index Scan with an estimated row count of 124,361 ! I dropped and recreated the index and I updated statistics on the table with FULLSCAN on second database, but still the same result.
Is there a way to force an Index Seek on the second database? Any idea what might be the cause for this difference in execution plan?
Any help will be highly appreciated.
Gabriela
September 2, 2004 at 1:00 am
Hi Gabriela,
Can you please post the table definition (you can leave out or change column names if you like), including index definitions and the query that is behaving differently on both databases. Also, can you tell us if the databases have the same, similar or different data and do both tables contain the same amount of data? Finally, I'd like to know if both databases use the same collation and if they differ, what they are. The information you've given us so far is not enough for me to even try to answer your question.
Cheers,
Henk
September 2, 2004 at 6:38 am
How many records are in each table?
September 2, 2004 at 8:47 am
Here is the stuff. I hope I didn't mess it up when I renamed the fields. I isolated the remote side in the inner query because when mixed with the local table in the same join the whole thing was terribly slow.
The inner query (remote) is very fast and returns a single row.
First database:
RECS has ~900,000 records and excution plan uses Index Seek on index IX_RECS_TI. The whole query is very fast (less than a second, including the remote).
Second database:
RECS has ~70,000 records and execution plan uses Index Scan on on index IX_RECS_TI. The whole query is much slower than on first database (~ 2sec). The query seems to be faster without the hint on the INDEX.
Both databases are on the same server SQL Server 2000 SP3/win 2003 Server.
The remote server is also SQL Server 2000 /SP3 on Win 2003 Server.
declare @pc varchar(1)
set @pc='C'
declare @cbi varchar(10)
set @cbi='33783'
declare @i_br int
set @i_br=1
declare @T TABLE(
[BI] [bigint] NULL,
[TI] [int] NULL ,
[a] [money] NULL ,
[JS] [int] NULL ,
[SLFF] [nvarchar] (30) NULL ,
[DI] [nvarchar] (30) NULL ,
[CC] [nvarchar] (30) NULL ,
[cid] [varchar] (20) NULL ,
[crt] [varchar] (25) NULL ,
[nr] [nvarchar] (30) NULL ,
[exp] [varchar] (5) NULL )
insert into @T
select x.BI, x.TI,x.a, x.br, x.SLFF,x.DI,x.CC,
r.cid, r.crt,
x.nr, x.exp
from (
select t.BI, t.TI,
case when @pc = 'A' then cast(s.Sa as money)/100
when @pc = 'M' then cast(s.Sa as money)/100
when @pc = 'V' then cast(s.Sa as money)/100
else CAST(ISNULL(c.Ca,s.Sa) as money)/100
end a,
case when @pc = 'A' then s.IS
when @pc = 'M' then s.IS
when @pc = 'V' then s.IS
else ISNULL(c.JS,s.IS)
end br,
s.SLFF,s.DI,s.CC,
s.nr, s.exp
from [linkedSVR\instance].linkedDB.dbo.Trans t
left join [linkedSVR\instance].linkedDB.dbo.C c on t.TI=c.TI and t.BI=@cbi
left join [linkedSVR\instance].linkedDB.dbo.ST s on t.TI=s.TI and t.BI=@cbi
where t.BI = @cbi and trnr = @i_br
and isdel = 0
) x
left join RECS r with (index=IX_RECS_TI) on x.TI = r.crd_TI and x.BI=@cbi
CREATE TABLE [RECS] (
[B_ID] [char] (6) NULL ,
[CID] [varchar] (20) NOT NULL ,
[CRT] [varchar] (25) NOT NULL ,
[CRD_BI] [bigint] NOT NULL ,
[CRD_TI] [bigint] NOT NULL ,
CONSTRAINT [PK_CI_ITEMID] PRIMARY KEY CLUSTERED ([CRD_TI]),
CONSTRAINT [FK_CI_BI] FOREIGN KEY ([B_ID]) REFERENCES [BATCH] ([B_ID]),
CONSTRAINT [FK_CI_RECTYPE] FOREIGN KEY ([CRT]) REFERENCES [CRT] ([CRT])
)
CREATE INDEX [IX_RECS_TI] ON [dbo].[CRD_RECS]([crd_TI])
September 2, 2004 at 8:48 am
And yes, both databases use the same collation
September 2, 2004 at 9:29 am
Hi Gabriela,
Looking at the code, I assume that the query optimizer somehow decided that the index doesn't offer the fastest access. You wrote that you did an "update statistics" and that the "slow" query seemed to improve in speed a little after removing the index hint. This supports my assumption.
I can think of 2 reasons why the optimizer comes to this - correct - conclusion:
If neither of this is the case, please show me the index creation statement and the (significant part of) the query execution plans.
Oh, before I forget: you can leave out the "and x.BI=@cbi" clause in the joins. This criterrium is covered in the inner query "where t.BI = @cbi" clause. Maybe it is even confusing for the outer query.
Hope this helps, good luck,
Henk
September 3, 2004 at 3:57 pm
Size can make a difference as can index matching methods. Please run
SET SHOWPLAN_TEXT ON
then your query right after that on each server and post the text output here which is your execution plan and we can look to see if anything gives us a clue why it is acting so different.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply