Bizarre performance hit

I create backups of a remote, production database and copy it after compression to a local,
development machine.
The remote machine is a Intel P4, 3.20GHz + 1G. The development machine is an AMD X2 3800+ dual
processor with 2G memory. Subjectively, I feel the development machine is noticeably faster in most
ways.
On the remote machine, I execute a SP which inserts approximately 6000 records. From the profiler, I
see each insertion takes about 50 Reads and about 0 Duration, which is acceptable.
After synchronizing the database logins with the those in the Master table on the development
machine, I execute the same SP on the development machine. The Reads are about 7500 and the Duration
around 3200!
Can anyone suggest reasons why this differential might occur? I really do need help. I'm not a DBA
but a developer. But it doesn't take a DBA to know that this won't fly.
TIARun sp_updatestats after a restore and then try it.
--
Andrew J. Kelly SQL MVP
"larzeb" <larzeb@.community.nospam> wrote in message
news:5os5k15iub07uqubpdqb8acgg7vcaq0grj@.4ax.com...
>I create backups of a remote, production database and copy it after
>compression to a local,
> development machine.
> The remote machine is a Intel P4, 3.20GHz + 1G. The development machine is
> an AMD X2 3800+ dual
> processor with 2G memory. Subjectively, I feel the development machine is
> noticeably faster in most
> ways.
> On the remote machine, I execute a SP which inserts approximately 6000
> records. From the profiler, I
> see each insertion takes about 50 Reads and about 0 Duration, which is
> acceptable.
> After synchronizing the database logins with the those in the Master table
> on the development
> machine, I execute the same SP on the development machine. The Reads are
> about 7500 and the Duration
> around 3200!
> Can anyone suggest reasons why this differential might occur? I really do
> need help. I'm not a DBA
> but a developer. But it doesn't take a DBA to know that this won't fly.
> TIA|||Thanks for the reply, Andrew. I'm sorry to say it had no effect.
On Tue, 4 Oct 2005 18:28:55 -0400, "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote:
>Run sp_updatestats after a restore and then try it.|||Are you sure there were no indexes added to the remote machine between the
time the backup was created and now? If the stats are the same (try running
in on the remote machine and see if it changes) then they should be the same
plan. Can you post the code for the sp?
--
Andrew J. Kelly SQL MVP
"larzeb" <larzeb@.community.nospam> wrote in message
news:bg46k11u0j542q5ua7dddol0g2fqfi58jt@.4ax.com...
> Thanks for the reply, Andrew. I'm sorry to say it had no effect.
> On Tue, 4 Oct 2005 18:28:55 -0400, "Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> wrote:
>>Run sp_updatestats after a restore and then try it.|||May be optimizer is not using the right indexes, you can try forcing the index.
Mohammed.
"Andrew J. Kelly" wrote:
> Are you sure there were no indexes added to the remote machine between the
> time the backup was created and now? If the stats are the same (try running
> in on the remote machine and see if it changes) then they should be the same
> plan. Can you post the code for the sp?
> --
> Andrew J. Kelly SQL MVP
>
> "larzeb" <larzeb@.community.nospam> wrote in message
> news:bg46k11u0j542q5ua7dddol0g2fqfi58jt@.4ax.com...
> > Thanks for the reply, Andrew. I'm sorry to say it had no effect.
> >
> > On Tue, 4 Oct 2005 18:28:55 -0400, "Andrew J. Kelly"
> > <sqlmvpnooospam@.shadhawk.com> wrote:
> >
> >>Run sp_updatestats after a restore and then try it.
>
>|||You did run sp_updatestats in the correct database context not the default
for [master]?
i.e.
USE [MyDB]
GO
exec sp_updatestats
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"larzeb" <larzeb@.community.nospam> wrote in message
news:bg46k11u0j542q5ua7dddol0g2fqfi58jt@.4ax.com...
> Thanks for the reply, Andrew. I'm sorry to say it had no effect.
> On Tue, 4 Oct 2005 18:28:55 -0400, "Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> wrote:
>>Run sp_updatestats after a restore and then try it.|||Andrew,
Sorry for the delay - DSL down for over 24 hours.
No changes made to anything execpt restoring DB, syncying user ids from Master, and running the
sp_updatestats. I also went to the remote, production machine and copied the BAK file to DVD, rather
than use the compressed BAK. The results were the same.
The SP and associated table definitions follow. I abridged some of the tables for simplicity.
exec usp_Mailings_Ins @.MailCampaignDetailID = 68, @.AddressID = 1639896, @.PersonID = 1659603,
@.BatchID = 411, @.TrayNo = 1, @.SerialNo = 3, @.NextMailDate = 'Dec 31 2100 12:00AM', @.OEL =N'****************AUTO**5-DIGIT 90210', @.MailID = @.P1 output
select @.P1
CREATE PROCEDURE [dbo].usp_Mailings_Ins
@.MailCampaignDetailID int,
@.AddressID int,
@.PersonID int,
@.BatchID int,
@.TrayNo int,
@.SerialNo int,
@.NextMailDate DATETIME,
@.OEL varchar(50),
@.MailID int OUTPUT
AS
INSERT INTO [dbo].[Mailings] (
[MailCampaignDetailID],
[AddressID],
[PersonID],
[BatchID],
[TrayNo],
[SerialNo],
NextMailDate,
OEL
) VALUES (
@.MailCampaignDetailID,
@.AddressID,
@.PersonID,
@.BatchID,
@.TrayNo,
@.SerialNo,
@.NextMailDate,
@.OEL
)
SET @.MailID = SCOPE_IDENTITY()
GO
CREATE TABLE [dbo].[MailingBatch] (
[BatchID] [int] IDENTITY (1, 1) NOT NULL ,
[CompID] [int] NOT NULL ,
[MailCampaignDetailID] [int] NOT NULL ,
[BatchType] [int] NOT NULL ,
[PostageType] [int] NULL ,
[Cost] [decimal](18, 0) NOT NULL ,
[parameters] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[dateAdded] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[MailCampaignDetail] (
[MailCampaignDetailID] [int] IDENTITY (1, 1) NOT NULL ,
[MailCampaignID] [int] NOT NULL ,
...
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[AddressValid] (
[AddressID] [int] NOT NULL ,
...
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Person] (
[PersonID] [int] IDENTITY (1, 1) NOT NULL ,
[AddressID] [int] NOT NULL ,
...
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Mailings] (
[MailID] [int] IDENTITY (1, 1) NOT NULL ,
[MailCampaignDetailID] [int] NOT NULL ,
[AddressID] [int] NOT NULL ,
[PersonID] [int] NOT NULL ,
[BatchID] [int] NULL ,
[TrayNo] [int] NULL ,
[SerialNo] [int] NULL ,
[NextMailDate] [datetime] NULL ,
[MailDate] [smalldatetime] NOT NULL ,
[OEL] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MailingBatch] WITH NOCHECK ADD
CONSTRAINT [PK_Batch] PRIMARY KEY CLUSTERED
(
[BatchID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MailCampaignDetail] WITH NOCHECK ADD
CONSTRAINT [PK_MailCampaignDetail] PRIMARY KEY CLUSTERED
(
[MailCampaignDetailID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[AddressValid] WITH NOCHECK ADD
CONSTRAINT [PK_AddressValid] PRIMARY KEY CLUSTERED
(
[AddressID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Person] WITH NOCHECK ADD
CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
(
[PersonID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Mailings] WITH NOCHECK ADD
CONSTRAINT [PK_Mailings] PRIMARY KEY CLUSTERED
(
[MailID]
) ON [PRIMARY]
GO
CREATE INDEX [IX_Mailings_BatchID] ON [dbo].[Mailings]([BatchID], [SerialNo]) ON [PRIMARY]
GO
CREATE INDEX [IX_Mailings_PersonID] ON [dbo].[Mailings]([PersonID]) ON [PRIMARY]
GO
CREATE INDEX [IX_Mailings_MCDID_AddressID] ON [dbo].[Mailings]([MailCampaignDetailID],
[AddressID]) ON [PRIMARY]
GO
CREATE INDEX [IX_Mailings_AddressID] ON [dbo].[Mailings]([AddressID]) ON [PRIMARY]
GO
CREATE INDEX [Mailings27] ON [dbo].[Mailings]([AddressID], [MailCampaignDetailID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Mailings] ADD
CONSTRAINT [FK_Mailings_AddressValid] FOREIGN KEY
(
[AddressID]
) REFERENCES [dbo].[AddressValid] (
[AddressID]
),
CONSTRAINT [FK_Mailings_MCD] FOREIGN KEY
(
[MailCampaignDetailID]
) REFERENCES [dbo].[MailCampaignDetail] (
[MailCampaignDetailID]
),
CONSTRAINT [FK_Mailings_Person] FOREIGN KEY
(
[PersonID]
) REFERENCES [dbo].[Person] (
[PersonID]
)
GO
On Tue, 4 Oct 2005 19:54:56 -0400, "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote:
>Are you sure there were no indexes added to the remote machine between the
>time the backup was created and now? If the stats are the same (try running
>in on the remote machine and see if it changes) then they should be the same
>plan. Can you post the code for the sp?|||I was sure I ran sp_updatestats against the appropriate DB.
On Wed, 05 Oct 2005 06:13:44 GMT, "Nik Marshall-Blank" <Nik@.here.com> wrote:
>You did run sp_updatestats in the correct database context not the default
>for [master]?
>i.e.
>USE [MyDB]
>GO
>exec sp_updatestats|||Mohammed,
I'm afraid I don't know how to "force" an index. Can you give me an example?
On Tue, 4 Oct 2005 18:08:03 -0700, "Mohammed" <Mohammed@.discussions.microsoft.com> wrote:
>May be optimizer is not using the right indexes, you can try forcing the index.
>Mohammed.
>"Andrew J. Kelly" wrote:
>> Are you sure there were no indexes added to the remote machine between the
>> time the backup was created and now? If the stats are the same (try running
>> in on the remote machine and see if it changes) then they should be the same
>> plan. Can you post the code for the sp?
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "larzeb" <larzeb@.community.nospam> wrote in message
>> news:bg46k11u0j542q5ua7dddol0g2fqfi58jt@.4ax.com...
>> > Thanks for the reply, Andrew. I'm sorry to say it had no effect.
>> >
>> > On Tue, 4 Oct 2005 18:28:55 -0400, "Andrew J. Kelly"
>> > <sqlmvpnooospam@.shadhawk.com> wrote:
>> >
>> >>Run sp_updatestats after a restore and then try it.
>>|||Well one thing I see is that you should put SET NOCOUNT ON at the beginning
of your sp but that should be the same on both.
Andrew J. Kelly SQL MVP
"larzeb" <larzeb@.community.nospam> wrote in message
news:fs4bk1de83u04keblbk0qnnr0hit4fvtae@.4ax.com...
> Andrew,
> Sorry for the delay - DSL down for over 24 hours.
> No changes made to anything execpt restoring DB, syncying user ids from
> Master, and running the
> sp_updatestats. I also went to the remote, production machine and copied
> the BAK file to DVD, rather
> than use the compressed BAK. The results were the same.
> The SP and associated table definitions follow. I abridged some of the
> tables for simplicity.
> exec usp_Mailings_Ins @.MailCampaignDetailID = 68, @.AddressID = 1639896,
> @.PersonID = 1659603,
> @.BatchID = 411, @.TrayNo = 1, @.SerialNo = 3, @.NextMailDate = 'Dec 31 2100
> 12:00AM', @.OEL => N'****************AUTO**5-DIGIT 90210', @.MailID = @.P1 output
> select @.P1
> CREATE PROCEDURE [dbo].usp_Mailings_Ins
> @.MailCampaignDetailID int,
> @.AddressID int,
> @.PersonID int,
> @.BatchID int,
> @.TrayNo int,
> @.SerialNo int,
> @.NextMailDate DATETIME,
> @.OEL varchar(50),
> @.MailID int OUTPUT
> AS
> INSERT INTO [dbo].[Mailings] (
> [MailCampaignDetailID],
> [AddressID],
> [PersonID],
> [BatchID],
> [TrayNo],
> [SerialNo],
> NextMailDate,
> OEL
> ) VALUES (
> @.MailCampaignDetailID,
> @.AddressID,
> @.PersonID,
> @.BatchID,
> @.TrayNo,
> @.SerialNo,
> @.NextMailDate,
> @.OEL
> )
> SET @.MailID = SCOPE_IDENTITY()
> GO
> CREATE TABLE [dbo].[MailingBatch] (
> [BatchID] [int] IDENTITY (1, 1) NOT NULL ,
> [CompID] [int] NOT NULL ,
> [MailCampaignDetailID] [int] NOT NULL ,
> [BatchType] [int] NOT NULL ,
> [PostageType] [int] NULL ,
> [Cost] [decimal](18, 0) NOT NULL ,
> [parameters] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [dateAdded] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[MailCampaignDetail] (
> [MailCampaignDetailID] [int] IDENTITY (1, 1) NOT NULL ,
> [MailCampaignID] [int] NOT NULL ,
> ...
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[AddressValid] (
> [AddressID] [int] NOT NULL ,
> ...
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Person] (
> [PersonID] [int] IDENTITY (1, 1) NOT NULL ,
> [AddressID] [int] NOT NULL ,
> ...
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Mailings] (
> [MailID] [int] IDENTITY (1, 1) NOT NULL ,
> [MailCampaignDetailID] [int] NOT NULL ,
> [AddressID] [int] NOT NULL ,
> [PersonID] [int] NOT NULL ,
> [BatchID] [int] NULL ,
> [TrayNo] [int] NULL ,
> [SerialNo] [int] NULL ,
> [NextMailDate] [datetime] NULL ,
> [MailDate] [smalldatetime] NOT NULL ,
> [OEL] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[MailingBatch] WITH NOCHECK ADD
> CONSTRAINT [PK_Batch] PRIMARY KEY CLUSTERED
> (
> [BatchID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[MailCampaignDetail] WITH NOCHECK ADD
> CONSTRAINT [PK_MailCampaignDetail] PRIMARY KEY CLUSTERED
> (
> [MailCampaignDetailID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[AddressValid] WITH NOCHECK ADD
> CONSTRAINT [PK_AddressValid] PRIMARY KEY CLUSTERED
> (
> [AddressID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Person] WITH NOCHECK ADD
> CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
> (
> [PersonID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Mailings] WITH NOCHECK ADD
> CONSTRAINT [PK_Mailings] PRIMARY KEY CLUSTERED
> (
> [MailID]
> ) ON [PRIMARY]
> GO
> CREATE INDEX [IX_Mailings_BatchID] ON [dbo].[Mailings]([BatchID],
> [SerialNo]) ON [PRIMARY]
> GO
> CREATE INDEX [IX_Mailings_PersonID] ON [dbo].[Mailings]([PersonID]) ON
> [PRIMARY]
> GO
> CREATE INDEX [IX_Mailings_MCDID_AddressID] ON
> [dbo].[Mailings]([MailCampaignDetailID],
> [AddressID]) ON [PRIMARY]
> GO
> CREATE INDEX [IX_Mailings_AddressID] ON [dbo].[Mailings]([AddressID]) ON
> [PRIMARY]
> GO
> CREATE INDEX [Mailings27] ON [dbo].[Mailings]([AddressID],
> [MailCampaignDetailID]) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Mailings] ADD
> CONSTRAINT [FK_Mailings_AddressValid] FOREIGN KEY
> (
> [AddressID]
> ) REFERENCES [dbo].[AddressValid] (
> [AddressID]
> ),
> CONSTRAINT [FK_Mailings_MCD] FOREIGN KEY
> (
> [MailCampaignDetailID]
> ) REFERENCES [dbo].[MailCampaignDetail] (
> [MailCampaignDetailID]
> ),
> CONSTRAINT [FK_Mailings_Person] FOREIGN KEY
> (
> [PersonID]
> ) REFERENCES [dbo].[Person] (
> [PersonID]
> )
> GO
>
> On Tue, 4 Oct 2005 19:54:56 -0400, "Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> wrote:
>>Are you sure there were no indexes added to the remote machine between the
>>time the backup was created and now? If the stats are the same (try
>>running
>>in on the remote machine and see if it changes) then they should be the
>>same
>>plan. Can you post the code for the sp?|||I began comparing the execution plans on each of the two machines. Of course, they were different.
One big difference was a Hash Match/Inner Join which has an estimated row count of 2,250,000 on the
development machine.
The execution plan says that it is doing an Index Scan on Addressvalid.Address21 and also on
UseCode.IX_UseCode which feeds the Hash Match/Inner Join:
|--Hash Match(Inner Join, HASH:([UseCode].[UseCodeID],
[UseCode].[CountyFipsID])=([AddressValid].[useCodeID], [AddressValid].[countyCodeFips]),
RESIDUAL:([AddressValid].[useCodeID]=[UseCode].[UseCodeID] AND
[AddressValid].[countyCodeFips]=[UseCode].[CountyFipsID]
|--Index Scan(OBJECT:([MailHouse].[dbo].[UseCode].[IX_UseCode]))
|--Index Scan(OBJECT:([MailHouse].[dbo].[AddressValid].[AddressValid21]))
Why is doing all this when it's supposed to be inserting rows in Mailings?
Why is the development machine doing this and the production not?
How can I get to two machines in sync?
Thanks for you help.
CREATE TABLE [dbo].[AddressValid] (
[AddressID] [int] NOT NULL,
[dataSourceID] [int] NOT NULL,
...
[useCodeID] [char] (6) NOT NULL,
...
[houseNo] [varchar] (10) NULL,
[preDir] [char] (2) NULL,
[streetName] [varchar] (28) NULL,
[streetSuffix] [char] (4) NULL,
[postDir] [char] (2) NULL,
[city] [varchar] (28) NULL,
[state] [char] (2) NULL,
[zip5] [char] (5) NULL,
[zip4] [char] (4) NULL,
[sud] [char] (4) NULL,
[unitNum] [varchar] (8) NULL,
...
[countyCodeFips] [char] (5) NULL,
...
[dateAdded] [smalldatetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[AddressValid] WITH NOCHECK ADD
CONSTRAINT [PK_AddressValid] PRIMARY KEY CLUSTERED
(
[AddressID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[AddressValid] WITH NOCHECK ADD
CONSTRAINT [IX_AddressValid] UNIQUE NONCLUSTERED
(
[streetName],
[houseNo],
[streetSuffix],
[preDir],
[postDir],
[zip5],
[zip4],
[sud],
[unitNum]
) ON [PRIMARY]
GO
CREATE INDEX [IX_Zip5] ON [dbo].[AddressValid]([zip5]) ON [PRIMARY]
GO
CREATE INDEX [AddressValid21] ON [dbo].[AddressValid]([AddressID], [useCodeID], [zip5],
[countyCodeFips]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[AddressValid] ADD
CONSTRAINT [FK_AddressValid_AddressSource] FOREIGN KEY
(
[AddressID]
) REFERENCES [dbo].[AddressSource] (
[addressID]
),
CONSTRAINT [FK_AddressValid_datasource] FOREIGN KEY
(
[dataSourceID]
) REFERENCES [dbo].[datasource] (
[DataSourceID]
),
CONSTRAINT [FK_AddressValid_UseCode] FOREIGN KEY
(
[countyCodeFips],
[useCodeID]
) REFERENCES [dbo].[UseCode] (
[CountyFipsID],
[UseCodeID]
)
GO
CREATE TABLE [dbo].[UseCode] (
[UseCodePK] [int] IDENTITY (1, 1) NOT NULL,
[CountyFipsID] [char] (5) NOT NULL,
[UseCodeID] [char] (6) NOT NULL,
[descr] [varchar] (50) NOT NULL,
[dateAdded] [smalldatetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[UseCode] WITH NOCHECK ADD
CONSTRAINT [PK_UseCode] PRIMARY KEY CLUSTERED
(
[UseCodePK]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[UseCode] WITH NOCHECK ADD
CONSTRAINT [IX_UseCode] UNIQUE NONCLUSTERED
(
[CountyFipsID],
[UseCodeID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[UseCode] ADD
CONSTRAINT [FK_UseCode_CountyFips] FOREIGN KEY
(
[CountyFipsID]
) REFERENCES [dbo].[CountyFips] (
[CountyFipsID]
)
GO
On Thu, 6 Oct 2005 18:14:28 -0400, "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote:
>Well one thing I see is that you should put SET NOCOUNT ON at the beginning
>of your sp but that should be the same on both.|||Hello,
I have tested the issue on my side but I am unable to reproduce the issue.
To narrow down the issue, I suggest that you perform the following steps:
1. Restore the database on another known working machine using the same
backup file. The machine has the similar hardware configuration as the
remote machine. Check if you can reproduce the issue on another machine.
2. Create a new test database. Create some tables and a SP in the test
database to check if you can reproduce the issue on another database. Let
me know the results.
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Sounds like you have referential integrity on some of the columns. In order
to enforce the RI it has to search the other tables. If they don't have
proper indexes it can be a real mess. If the schemas really are identical
(use a tool such as www.red-gate.com to verify) and the number of rows are
the same it would usually boil down to statistics being different. You say
you updated the ones on the dev server, what about the production?
--
Andrew J. Kelly SQL MVP
"larzeb" <larzeb@.community.nospam> wrote in message
news:58bbk11d1g82ein2issf0c878q6kjj6r6o@.4ax.com...
>I began comparing the execution plans on each of the two machines. Of
>course, they were different.
> One big difference was a Hash Match/Inner Join which has an estimated row
> count of 2,250,000 on the
> development machine.
> The execution plan says that it is doing an Index Scan on
> Addressvalid.Address21 and also on
> UseCode.IX_UseCode which feeds the Hash Match/Inner Join:
> |--Hash Match(Inner Join,
> HASH:([UseCode].[UseCodeID],
> [UseCode].[CountyFipsID])=([AddressValid].[useCodeID],
> [AddressValid].[countyCodeFips]),
> RESIDUAL:([AddressValid].[useCodeID]=[UseCode].[UseCodeID] AND
> [AddressValid].[countyCodeFips]=[UseCode].[CountyFipsID]
> |--Index
> Scan(OBJECT:([MailHouse].[dbo].[UseCode].[IX_UseCode]))
> |--Index
> Scan(OBJECT:([MailHouse].[dbo].[AddressValid].[AddressValid21]))
> Why is doing all this when it's supposed to be inserting rows in Mailings?
> Why is the development machine doing this and the production not?
> How can I get to two machines in sync?
> Thanks for you help.
> CREATE TABLE [dbo].[AddressValid] (
> [AddressID] [int] NOT NULL,
> [dataSourceID] [int] NOT NULL,
> ...
> [useCodeID] [char] (6) NOT NULL,
> ...
> [houseNo] [varchar] (10) NULL,
> [preDir] [char] (2) NULL,
> [streetName] [varchar] (28) NULL,
> [streetSuffix] [char] (4) NULL,
> [postDir] [char] (2) NULL,
> [city] [varchar] (28) NULL,
> [state] [char] (2) NULL,
> [zip5] [char] (5) NULL,
> [zip4] [char] (4) NULL,
> [sud] [char] (4) NULL,
> [unitNum] [varchar] (8) NULL,
> ...
> [countyCodeFips] [char] (5) NULL,
> ...
> [dateAdded] [smalldatetime] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[AddressValid] WITH NOCHECK ADD
> CONSTRAINT [PK_AddressValid] PRIMARY KEY CLUSTERED
> (
> [AddressID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[AddressValid] WITH NOCHECK ADD
> CONSTRAINT [IX_AddressValid] UNIQUE NONCLUSTERED
> (
> [streetName],
> [houseNo],
> [streetSuffix],
> [preDir],
> [postDir],
> [zip5],
> [zip4],
> [sud],
> [unitNum]
> ) ON [PRIMARY]
> GO
> CREATE INDEX [IX_Zip5] ON [dbo].[AddressValid]([zip5]) ON [PRIMARY]
> GO
> CREATE INDEX [AddressValid21] ON [dbo].[AddressValid]([AddressID],
> [useCodeID], [zip5],
> [countyCodeFips]) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[AddressValid] ADD
> CONSTRAINT [FK_AddressValid_AddressSource] FOREIGN KEY
> (
> [AddressID]
> ) REFERENCES [dbo].[AddressSource] (
> [addressID]
> ),
> CONSTRAINT [FK_AddressValid_datasource] FOREIGN KEY
> (
> [dataSourceID]
> ) REFERENCES [dbo].[datasource] (
> [DataSourceID]
> ),
> CONSTRAINT [FK_AddressValid_UseCode] FOREIGN KEY
> (
> [countyCodeFips],
> [useCodeID]
> ) REFERENCES [dbo].[UseCode] (
> [CountyFipsID],
> [UseCodeID]
> )
> GO
> CREATE TABLE [dbo].[UseCode] (
> [UseCodePK] [int] IDENTITY (1, 1) NOT NULL,
> [CountyFipsID] [char] (5) NOT NULL,
> [UseCodeID] [char] (6) NOT NULL,
> [descr] [varchar] (50) NOT NULL,
> [dateAdded] [smalldatetime] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[UseCode] WITH NOCHECK ADD
> CONSTRAINT [PK_UseCode] PRIMARY KEY CLUSTERED
> (
> [UseCodePK]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[UseCode] WITH NOCHECK ADD
> CONSTRAINT [IX_UseCode] UNIQUE NONCLUSTERED
> (
> [CountyFipsID],
> [UseCodeID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[UseCode] ADD
> CONSTRAINT [FK_UseCode_CountyFips] FOREIGN KEY
> (
> [CountyFipsID]
> ) REFERENCES [dbo].[CountyFips] (
> [CountyFipsID]
> )
> GO
>
> On Thu, 6 Oct 2005 18:14:28 -0400, "Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> wrote:
>>Well one thing I see is that you should put SET NOCOUNT ON at the
>>beginning
>>of your sp but that should be the same on both.