April 23, 2003 at 8:39 am
Very simple select statement but SQL Server generates different execution plan when statement runs against different database.
select * from approval where table_name = 'worksheet' and record_key = 1160
Execution plan against database LabWareACVI
|--Clustered Index Scan(OBJECT:([LabWareACVI].[dbo].[APPROVAL].[APPROVAL_ID_401]), WHERE:(Convert([APPROVAL].[RECORD_KEY])=Convert([@2]) AND [APPROVAL].[TABLE_NAME]=[@1]))
Execution plan against database LabWareDEV
|--Clustered Index Scan(OBJECT:([LabWareDev].[dbo].[APPROVAL].[APPROVAL_ID_401]), WHERE:([APPROVAL].[TABLE_NAME]=[@1] AND Convert([APPROVAL].[RECORD_KEY])=Convert([@2])))
First execution plan searchs data according to 'record_key' and 'table_name' and second try to find records by 'table_name' and 'record_key' sequence.
Tables indexes have been rebuilt and distribution statistics have been updated.
The number of records of that table are different in different databases. I guess that could be the reason.
Thanks for your reply.
Edited by - Allen_Cui on 04/23/2003 08:45:00 AM
Edited by - Allen_Cui on 04/23/2003 08:46:39 AM
April 28, 2003 at 12:00 pm
This was removed by the editor as SPAM
April 28, 2003 at 1:45 pm
Number of records in a table can definitely affect the execution plans. SQL Server will usually create multiple plans, estimate the total execution time for each, and then show you the one it thinks is likely to be fastest.
HTH
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply