Issue with T-SQL and SQL 7 to SQL2000 conversion

I'm in the process of converting a database from SQL 7 to SQL 2000 and
have come up against a problem. The following code executes correctly in
the existing SQL7 database and runs to completion in about 1 minute.
In the 2000 database, it runs until I cancel execution -- I've let it
run up to 30 minutes without showing any signs of finishing.
I am using the identical code to populate the tables in both databases
-- the data actually gets fed in from text files via a BULK INSERT
command, and that runs correctly in the both databases. The indexes and
primary keys are created via T-SQL code, and that runs correctly in both
databases.
I have deleted and re-created the stored procedure containing this code
to no avail. It will still run until I cancel execution.
Did something change between SQL7 and 2000 with the UPDATE command, or
am I missing something else?
Any help will be appreciated --
Carl
UPDATE tblProcedureHistory
SET tblProcedureHistory.Status_A = [derived].MaxPostingDate
FROM
(
SELECT T1.OFFICE_NUM,
T1.PatientID,
T1.PatientType,
T1.StudentID,
T1.ProcedureID,
T1.ProcedureSuffix,
T1.Tooth,
T1.Surface,
MAX(T1.PostingDate) AS MaxPostingDate
FROM tbl_AHSTDN AS T1
INNER JOIN tblProcedureHistory AS T2
ON T1.OFFICE_NUM = T2.OFFICE_NUM
AND T1.PatientID = T2.PatientID
AND T1.PatientType = T2.PatientType
AND T1.StudentID = T2.StudentID
AND T1.ProcedureID = T2.ProcedureID
AND T1.ProcedureSuffix = T2.ProcedureSuffix
AND T1.Tooth = T2.Tooth
AND T1.Surface = T2.Surface
AND T1.Status = 'A'
GROUP BY
T1.OFFICE_NUM,
T1.PatientID,
T1.PatientType,
T1.StudentID,
T1.ProcedureID,
T1.ProcedureSuffix,
T1.Tooth,
T1.Surface
)
AS [derived]
WHERE
tblProcedureHistory.OFFICE_NUM = [derived].OFFICE_NUM
AND tblProcedureHistory.PatientID = [derived].PatientID
AND tblProcedureHistory.PatientType = [derived].PatientType
AND tblProcedureHistory.StudentID = [derived].StudentID
AND tblProcedureHistory.ProcedureID = [derived].ProcedureID
AND tblProcedureHistory.ProcedureSuffix = [derived].ProcedureSuffix
AND tblProcedureHistory.Tooth = [derived].Tooth
AND tblProcedureHistory.Surface = [derived].SurfaceLet's see your DDL including Primary Keys and Indexes.
"Carl Imthurn" <nospam@.all.thanks> wrote in message
news:%23di7RAWlGHA.1208@.TK2MSFTNGP02.phx.gbl...
> I'm in the process of converting a database from SQL 7 to SQL 2000 and
> have come up against a problem. The following code executes correctly in
> the existing SQL7 database and runs to completion in about 1 minute.
> In the 2000 database, it runs until I cancel execution -- I've let it run
> up to 30 minutes without showing any signs of finishing.
> I am using the identical code to populate the tables in both databases --
> the data actually gets fed in from text files via a BULK INSERT command,
> and that runs correctly in the both databases. The indexes and primary
> keys are created via T-SQL code, and that runs correctly in both
> databases.
> I have deleted and re-created the stored procedure containing this code to
> no avail. It will still run until I cancel execution.
> Did something change between SQL7 and 2000 with the UPDATE command, or am
> I missing something else?
> Any help will be appreciated --
> Carl
> UPDATE tblProcedureHistory
> SET tblProcedureHistory.Status_A = [derived].MaxPostingDate
> FROM
> (
> SELECT T1.OFFICE_NUM,
> T1.PatientID,
> T1.PatientType,
> T1.StudentID,
> T1.ProcedureID,
> T1.ProcedureSuffix,
> T1.Tooth,
> T1.Surface,
> MAX(T1.PostingDate) AS MaxPostingDate
> FROM tbl_AHSTDN AS T1
> INNER JOIN tblProcedureHistory AS T2
> ON T1.OFFICE_NUM = T2.OFFICE_NUM
> AND T1.PatientID = T2.PatientID
> AND T1.PatientType = T2.PatientType
> AND T1.StudentID = T2.StudentID
> AND T1.ProcedureID = T2.ProcedureID
> AND T1.ProcedureSuffix = T2.ProcedureSuffix
> AND T1.Tooth = T2.Tooth
> AND T1.Surface = T2.Surface
> AND T1.Status = 'A'
> GROUP BY
> T1.OFFICE_NUM,
> T1.PatientID,
> T1.PatientType,
> T1.StudentID,
> T1.ProcedureID,
> T1.ProcedureSuffix,
> T1.Tooth,
> T1.Surface
> )
> AS [derived]
> WHERE
> tblProcedureHistory.OFFICE_NUM = [derived].OFFICE_NUM
> AND tblProcedureHistory.PatientID = [derived].PatientID
> AND tblProcedureHistory.PatientType = [derived].PatientType
> AND tblProcedureHistory.StudentID = [derived].StudentID
> AND tblProcedureHistory.ProcedureID = [derived].ProcedureID
> AND tblProcedureHistory.ProcedureSuffix = [derived].ProcedureSuffix
> AND tblProcedureHistory.Tooth = [derived].Tooth
> AND tblProcedureHistory.Surface = [derived].Surface|||Here's the DDL for table/PK/index creation:
The table data gets sucked out of an AS/400 every morning into text
files and fed into SQL Server via BULK INSERT. The data in tbl_AHSTDN is
not subject to updates; ie, it's a static table.
Thanks in advance --
Carl
CREATE TABLE [dbo].[tbl_AHSTDN] (
[DNHSTD] [char] (1) NULL ,
[PatientID] [int] NULL ,
[PatientType] [int] NULL ,
[DNTYY] [int] NULL ,
[DNTMM] [int] NULL ,
[DNTDD] [int] NULL ,
[DNSEQ] [int] NULL ,
[DNID] [int] NULL ,
[DNIDTY] [char] (1) NULL ,
[TicketID] [int] NULL ,
[ProcedureID] [int] NULL ,
[ProcedureSuffix] [int] NULL ,
[DNTICX] [int] NULL ,
[Discipline] [varchar] (100) NULL ,
[SessionID] [int] NULL ,
[Grade] [int] NULL ,
[DNMTHS] [int] NULL ,
[StudentID] [char] (3) NULL ,
[DNCGCD] [char] (1) NULL ,
[DOCMASID] [int] NULL ,
[DollarAmount] [money] NULL ,
[DNIN01] [int] NULL ,
[DNDAT1] [int] NULL ,
[DNIN02] [int] NULL ,
[DNDAT2] [int] NULL ,
[DNCLM_NUM] [char] (5) NULL ,
[Status] [char] (1) NULL ,
[DNFILE] [char] (1) NULL ,
[DNSEQN] [int] NULL ,
[DNBK06] [char] (6) NULL ,
[DNFLAG] [char] (1) NULL ,
[BatchID] [int] NULL ,
[Tooth] [char] (2) NULL ,
[Surface] [char] (5) NULL ,
[DNTTH2] [char] (2) NULL ,
[DNSUR2] [char] (5) NULL ,
[DNTTH3] [char] (2) NULL ,
[DNSUR3] [char] (5) NULL ,
[DNTTH4] [char] (2) NULL ,
[DNSUR4] [char] (5) NULL ,
[DNTTH5] [char] (2) NULL ,
[DNSUR5] [char] (5) NULL ,
[Location] [char] (4) NULL ,
[DNCDAT] [int] NULL ,
[User] [varchar] (10) NULL ,
[DNUQID] [int] NULL ,
[DNBL19] [varchar] (19) NULL ,
[DNPTS] [real] NULL ,
[DNGRP] [int] NULL ,
[DNCMDT] [int] NULL ,
[OFFICE_NUM] [int] NULL ,
[TransactionDate] [datetime] NULL ,
[CompletionDate] [datetime] NULL ,
[PostingDate] [datetime] NULL
) ON [PRIMARY]
-- PRIMARY KEY
ALTER TABLE [dbo].[tbl_AHSTDN] WITH NOCHECK ADD
CONSTRAINT [PK_tbl_AHSTDN] PRIMARY KEY NONCLUSTERED
(
[PatientID],
[PatientType],
[DNTYY],
[DNTMM],
[DNTDD],
[DNSEQ],
[OFFICE_NUM]
) ON [PRIMARY]
-- INDEXES
CREATE INDEX [PATIENTID] ON [dbo].[tbl_AHSTDN]([PatientID]) ON [PRIMARY]
CREATE INDEX [PATIENTTYPE] ON [dbo].[tbl_AHSTDN]([PatientType]) ON
[PRIMARY]
CREATE INDEX [TICKETID] ON [dbo].[tbl_AHSTDN]([TicketID]) ON [PRIMARY]
CREATE INDEX [PROCEDUREID] ON [dbo].[tbl_AHSTDN]([ProcedureID]) ON
[PRIMARY]
CREATE INDEX [PROCEDURESUFFIX] ON
[dbo].[tbl_AHSTDN]([ProcedureSuffix]) ON [PRIMARY]
CREATE INDEX [GRADE] ON [dbo].[tbl_AHSTDN]([Grade]) ON [PRIMARY]
CREATE INDEX [STUDENTID] ON [dbo].[tbl_AHSTDN]([StudentID]) ON [PRIMARY]
CREATE INDEX [OFFICE_NUM] ON [dbo].[tbl_AHSTDN]([OFFICE_NUM]) ON
[PRIMARY]
CREATE INDEX [DNUQID] ON [dbo].[tbl_AHSTDN]([DNUQID]) ON [PRIMARY]
CREATE INDEX [TRANSACTIONDATE] ON
[dbo].[tbl_AHSTDN]([TransactionDate]) ON [PRIMARY]
CREATE INDEX [COMPLETIONDATE] ON [dbo].[tbl_AHSTDN]([CompletionDate])
ON [PRIMARY]
CREATE INDEX [POSTINGDATE] ON [dbo].[tbl_AHSTDN]([PostingDate]) ON
[PRIMARY]
-- added on 7 December 2004 to improve performance on clinic attendance
stored procedures
CREATE INDEX ATTENDANCE_REPORT_INDEX ON dbo.tbl_AHSTDN (ProcedureID,
TransactionDate, OFFICE_NUM, StudentID, SessionID) ON [PRIMARY]
-- added on 20 January 2005 to improve performance on ticket count
reports for PBO
CREATE INDEX TICKET_COUNT_REPORT_INDEX ON dbo.tbl_AHSTDN (OFFICE_NUM,
Location, [User], TicketID, PostingDate, Status) ON [PRIMARY]|||First thing, that's a heckuva lot of nullable columns. Even your PRIMARY
KEY columns are all nullable!? :( I don't know if it's just me, but I also
don't see a clustered index on this table anywhere either :(.
Anyways, here's one suggestion - before the BULK INSERT drop all indexes
(except the clustered index should you decide to add one), do the BULK
INSERT, and then rebuild the indexes.
At the very least I would imagine the table could stand to be reindexed big
time. One more quick suggestion - check to see if the database size is near
the upper limit; i.e., is Auto-Grow likely to kick in during the BULK INSERT
process? If so, resize the database to make it bigger. And if possible use
the simple recovery model for this database.
"Carl Imthurn" <nospam@.all.thanks> wrote in message
news:%23g6sjYWlGHA.1208@.TK2MSFTNGP02.phx.gbl...
> Here's the DDL for table/PK/index creation:
> The table data gets sucked out of an AS/400 every morning into text files
> and fed into SQL Server via BULK INSERT. The data in tbl_AHSTDN is not
> subject to updates; ie, it's a static table.
> Thanks in advance --
> Carl
> CREATE TABLE [dbo].[tbl_AHSTDN] (
> [DNHSTD] [char] (1) NULL ,
> [PatientID] [int] NULL ,
> [PatientType] [int] NULL ,
> [DNTYY] [int] NULL ,
> [DNTMM] [int] NULL ,
> [DNTDD] [int] NULL ,
> [DNSEQ] [int] NULL ,
> [DNID] [int] NULL ,
> [DNIDTY] [char] (1) NULL ,
> [TicketID] [int] NULL ,
> [ProcedureID] [int] NULL ,
> [ProcedureSuffix] [int] NULL ,
> [DNTICX] [int] NULL ,
> [Discipline] [varchar] (100) NULL ,
> [SessionID] [int] NULL ,
> [Grade] [int] NULL ,
> [DNMTHS] [int] NULL ,
> [StudentID] [char] (3) NULL ,
> [DNCGCD] [char] (1) NULL ,
> [DOCMASID] [int] NULL ,
> [DollarAmount] [money] NULL ,
> [DNIN01] [int] NULL ,
> [DNDAT1] [int] NULL ,
> [DNIN02] [int] NULL ,
> [DNDAT2] [int] NULL ,
> [DNCLM_NUM] [char] (5) NULL ,
> [Status] [char] (1) NULL ,
> [DNFILE] [char] (1) NULL ,
> [DNSEQN] [int] NULL ,
> [DNBK06] [char] (6) NULL ,
> [DNFLAG] [char] (1) NULL ,
> [BatchID] [int] NULL ,
> [Tooth] [char] (2) NULL ,
> [Surface] [char] (5) NULL ,
> [DNTTH2] [char] (2) NULL ,
> [DNSUR2] [char] (5) NULL ,
> [DNTTH3] [char] (2) NULL ,
> [DNSUR3] [char] (5) NULL ,
> [DNTTH4] [char] (2) NULL ,
> [DNSUR4] [char] (5) NULL ,
> [DNTTH5] [char] (2) NULL ,
> [DNSUR5] [char] (5) NULL ,
> [Location] [char] (4) NULL ,
> [DNCDAT] [int] NULL ,
> [User] [varchar] (10) NULL ,
> [DNUQID] [int] NULL ,
> [DNBL19] [varchar] (19) NULL ,
> [DNPTS] [real] NULL ,
> [DNGRP] [int] NULL ,
> [DNCMDT] [int] NULL ,
> [OFFICE_NUM] [int] NULL ,
> [TransactionDate] [datetime] NULL ,
> [CompletionDate] [datetime] NULL ,
> [PostingDate] [datetime] NULL
> ) ON [PRIMARY]
> -- PRIMARY KEY
> ALTER TABLE [dbo].[tbl_AHSTDN] WITH NOCHECK ADD
> CONSTRAINT [PK_tbl_AHSTDN] PRIMARY KEY NONCLUSTERED
> (
> [PatientID],
> [PatientType],
> [DNTYY],
> [DNTMM],
> [DNTDD],
> [DNSEQ],
> [OFFICE_NUM]
> ) ON [PRIMARY]
> -- INDEXES
> CREATE INDEX [PATIENTID] ON [dbo].[tbl_AHSTDN]([PatientID]) ON [PRIMARY]
> CREATE INDEX [PATIENTTYPE] ON [dbo].[tbl_AHSTDN]([PatientType]) ON
> [PRIMARY]
> CREATE INDEX [TICKETID] ON [dbo].[tbl_AHSTDN]([TicketID]) ON [PRIMARY]
> CREATE INDEX [PROCEDUREID] ON [dbo].[tbl_AHSTDN]([ProcedureID]) ON
> [PRIMARY]
> CREATE INDEX [PROCEDURESUFFIX] ON [dbo].[tbl_AHSTDN]([ProcedureSuffix])
> ON [PRIMARY]
> CREATE INDEX [GRADE] ON [dbo].[tbl_AHSTDN]([Grade]) ON [PRIMARY]
> CREATE INDEX [STUDENTID] ON [dbo].[tbl_AHSTDN]([StudentID]) ON [PRIMARY]
> CREATE INDEX [OFFICE_NUM] ON [dbo].[tbl_AHSTDN]([OFFICE_NUM]) ON
> [PRIMARY]
> CREATE INDEX [DNUQID] ON [dbo].[tbl_AHSTDN]([DNUQID]) ON [PRIMARY]
> CREATE INDEX [TRANSACTIONDATE] ON [dbo].[tbl_AHSTDN]([TransactionDate])
> ON [PRIMARY]
> CREATE INDEX [COMPLETIONDATE] ON [dbo].[tbl_AHSTDN]([CompletionDate]) ON
> [PRIMARY]
> CREATE INDEX [POSTINGDATE] ON [dbo].[tbl_AHSTDN]([PostingDate]) ON
> [PRIMARY]
> -- added on 7 December 2004 to improve performance on clinic attendance
> stored procedures
> CREATE INDEX ATTENDANCE_REPORT_INDEX ON dbo.tbl_AHSTDN (ProcedureID,
> TransactionDate, OFFICE_NUM, StudentID, SessionID) ON [PRIMARY]
> -- added on 20 January 2005 to improve performance on ticket count reports
> for PBO
> CREATE INDEX TICKET_COUNT_REPORT_INDEX ON dbo.tbl_AHSTDN (OFFICE_NUM,
> Location, [User], TicketID, PostingDate, Status) ON [PRIMARY]|||Hi Mike --
Thanks for your reply. Actually, this stored procedure has been running
without a hitch for so long that I had to go back and refresh my memory
about the columns, NULLs, etc.
Here's what happens:
1) The table is dropped and recreated every morning with no indexes or
primary keys
2) The data is fed in from text files via BULK INSERT
3) The nullable columns in the primary key are modified to be NOT NULL
4) The primary key is added
5) The indexes are added
No clustered index -- I need to rectify that one. Thanks for catching
it. And, since the bulk insert is done and then indexes are added, do I
need to do a reindex?
Anyway, I appreciate your time -- I will keep at it to figure out why it
works in SQL7 but not in SQL2000
Carl
Mike C# wrote:
> First thing, that's a heckuva lot of nullable columns. Even your PRIMARY
> KEY columns are all nullable!? :( I don't know if it's just me, but I als
o
> don't see a clustered index on this table anywhere either :(.
> Anyways, here's one suggestion - before the BULK INSERT drop all indexes
> (except the clustered index should you decide to add one), do the BULK
> INSERT, and then rebuild the indexes.
> At the very least I would imagine the table could stand to be reindexed bi
g
> time. One more quick suggestion - check to see if the database size is ne
ar
> the upper limit; i.e., is Auto-Grow likely to kick in during the BULK INSE
RT
> process? If so, resize the database to make it bigger. And if possible u
se
> the simple recovery model for this database.
>|||This can't be the actual DDL. You cannot put the primary key on a NULLable
column:
Msg 8111, Level 16, State 1, Line 1
Cannot define PRIMARY KEY constraint on nullable column in table
'tbl_AHSTDN'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
HTH
Kalen Delaney, SQL Server MVP
"Carl Imthurn" <nospam@.all.thanks> wrote in message
news:%23g6sjYWlGHA.1208@.TK2MSFTNGP02.phx.gbl...
> Here's the DDL for table/PK/index creation:
> The table data gets sucked out of an AS/400 every morning into text files
> and fed into SQL Server via BULK INSERT. The data in tbl_AHSTDN is not
> subject to updates; ie, it's a static table.
> Thanks in advance --
> Carl
> CREATE TABLE [dbo].[tbl_AHSTDN] (
> [DNHSTD] [char] (1) NULL ,
> [PatientID] [int] NULL ,
> [PatientType] [int] NULL ,
> [DNTYY] [int] NULL ,
> [DNTMM] [int] NULL ,
> [DNTDD] [int] NULL ,
> [DNSEQ] [int] NULL ,
> [DNID] [int] NULL ,
> [DNIDTY] [char] (1) NULL ,
> [TicketID] [int] NULL ,
> [ProcedureID] [int] NULL ,
> [ProcedureSuffix] [int] NULL ,
> [DNTICX] [int] NULL ,
> [Discipline] [varchar] (100) NULL ,
> [SessionID] [int] NULL ,
> [Grade] [int] NULL ,
> [DNMTHS] [int] NULL ,
> [StudentID] [char] (3) NULL ,
> [DNCGCD] [char] (1) NULL ,
> [DOCMASID] [int] NULL ,
> [DollarAmount] [money] NULL ,
> [DNIN01] [int] NULL ,
> [DNDAT1] [int] NULL ,
> [DNIN02] [int] NULL ,
> [DNDAT2] [int] NULL ,
> [DNCLM_NUM] [char] (5) NULL ,
> [Status] [char] (1) NULL ,
> [DNFILE] [char] (1) NULL ,
> [DNSEQN] [int] NULL ,
> [DNBK06] [char] (6) NULL ,
> [DNFLAG] [char] (1) NULL ,
> [BatchID] [int] NULL ,
> [Tooth] [char] (2) NULL ,
> [Surface] [char] (5) NULL ,
> [DNTTH2] [char] (2) NULL ,
> [DNSUR2] [char] (5) NULL ,
> [DNTTH3] [char] (2) NULL ,
> [DNSUR3] [char] (5) NULL ,
> [DNTTH4] [char] (2) NULL ,
> [DNSUR4] [char] (5) NULL ,
> [DNTTH5] [char] (2) NULL ,
> [DNSUR5] [char] (5) NULL ,
> [Location] [char] (4) NULL ,
> [DNCDAT] [int] NULL ,
> [User] [varchar] (10) NULL ,
> [DNUQID] [int] NULL ,
> [DNBL19] [varchar] (19) NULL ,
> [DNPTS] [real] NULL ,
> [DNGRP] [int] NULL ,
> [DNCMDT] [int] NULL ,
> [OFFICE_NUM] [int] NULL ,
> [TransactionDate] [datetime] NULL ,
> [CompletionDate] [datetime] NULL ,
> [PostingDate] [datetime] NULL
> ) ON [PRIMARY]
> -- PRIMARY KEY
> ALTER TABLE [dbo].[tbl_AHSTDN] WITH NOCHECK ADD
> CONSTRAINT [PK_tbl_AHSTDN] PRIMARY KEY NONCLUSTERED
> (
> [PatientID],
> [PatientType],
> [DNTYY],
> [DNTMM],
> [DNTDD],
> [DNSEQ],
> [OFFICE_NUM]
> ) ON [PRIMARY]
> -- INDEXES
> CREATE INDEX [PATIENTID] ON [dbo].[tbl_AHSTDN]([PatientID]) ON [PRIMARY]
> CREATE INDEX [PATIENTTYPE] ON [dbo].[tbl_AHSTDN]([PatientType]) ON
> [PRIMARY]
> CREATE INDEX [TICKETID] ON [dbo].[tbl_AHSTDN]([TicketID]) ON [PRIMARY]
> CREATE INDEX [PROCEDUREID] ON [dbo].[tbl_AHSTDN]([ProcedureID]) ON
> [PRIMARY]
> CREATE INDEX [PROCEDURESUFFIX] ON [dbo].[tbl_AHSTDN]([ProcedureSuffix])
> ON [PRIMARY]
> CREATE INDEX [GRADE] ON [dbo].[tbl_AHSTDN]([Grade]) ON [PRIMARY]
> CREATE INDEX [STUDENTID] ON [dbo].[tbl_AHSTDN]([StudentID]) ON [PRIMARY]
> CREATE INDEX [OFFICE_NUM] ON [dbo].[tbl_AHSTDN]([OFFICE_NUM]) ON
> [PRIMARY]
> CREATE INDEX [DNUQID] ON [dbo].[tbl_AHSTDN]([DNUQID]) ON [PRIMARY]
> CREATE INDEX [TRANSACTIONDATE] ON [dbo].[tbl_AHSTDN]([TransactionDate])
> ON [PRIMARY]
> CREATE INDEX [COMPLETIONDATE] ON [dbo].[tbl_AHSTDN]([CompletionDate]) ON
> [PRIMARY]
> CREATE INDEX [POSTINGDATE] ON [dbo].[tbl_AHSTDN]([PostingDate]) ON
> [PRIMARY]
> -- added on 7 December 2004 to improve performance on clinic attendance
> stored procedures
> CREATE INDEX ATTENDANCE_REPORT_INDEX ON dbo.tbl_AHSTDN (ProcedureID,
> TransactionDate, OFFICE_NUM, StudentID, SessionID) ON [PRIMARY]
> -- added on 20 January 2005 to improve performance on ticket count reports
> for PBO
> CREATE INDEX TICKET_COUNT_REPORT_INDEX ON dbo.tbl_AHSTDN (OFFICE_NUM,
> Location, [User], TicketID, PostingDate, Status) ON [PRIMARY]|||In adddition to adding the clustered index, if this database has been
updated from SQL7 to SQL2K, you should update the statistics (see the
sp_updatestats stored procedure documentation in BOL) after the conversion.
If you have not already done this, you should. WARNING, if you have a large
database, this can take a considerable length of time.
Also, look at the query plans for the updates in both databases and see if
there is any differences. If there are, you may need different indexes on
SQL2K than you did in SQL7. If SQL2K is choosing a bad plan, you might
consider using index hints if that signifigantly improves performance.
As an aside, it seems a shame to be moving to SQL2K at this time, expecially
if you are having trouble with it. Any posibility of going directly to SQL
2005?
Tom
"Carl Imthurn" <nospam@.all.thanks> wrote in message
news:us5wC1WlGHA.1240@.TK2MSFTNGP04.phx.gbl...
> Hi Mike --
> Thanks for your reply. Actually, this stored procedure has been running
> without a hitch for so long that I had to go back and refresh my memory
> about the columns, NULLs, etc.
> Here's what happens:
> 1) The table is dropped and recreated every morning with no indexes or
> primary keys
> 2) The data is fed in from text files via BULK INSERT
> 3) The nullable columns in the primary key are modified to be NOT NULL
> 4) The primary key is added
> 5) The indexes are added
> No clustered index -- I need to rectify that one. Thanks for catching it.
> And, since the bulk insert is done and then indexes are added, do I need
> to do a reindex?
> Anyway, I appreciate your time -- I will keep at it to figure out why it
> works in SQL7 but not in SQL2000
> Carl
> Mike C# wrote:|||Tom --
Thanks for your help. I updated the statistics -- no improvement.
I will look at the query plans and check for differences.
I used the index tuning wizard in SQL2K and it suggested an additional
index on tbl_AHSTDN.Status (that column is already indexed, but only in
conjunction with other columns in a composite index). I tried that with
no success.
I will also check out index hints to see if that makes a difference.
And as far as SQL2005 goes, I would like to, but need to get a little
more up-to-speed with it first.
Thanks again -- I appreciate your time.
Carl
Tom Cooper wrote:
> In adddition to adding the clustered index, if this database has been
> updated from SQL7 to SQL2K, you should update the statistics (see the
> sp_updatestats stored procedure documentation in BOL) after the conversion
.
> If you have not already done this, you should. WARNING, if you have a lar
ge
> database, this can take a considerable length of time.
> Also, look at the query plans for the updates in both databases and see if
> there is any differences. If there are, you may need different indexes on
> SQL2K than you did in SQL7. If SQL2K is choosing a bad plan, you might
> consider using index hints if that signifigantly improves performance.
> As an aside, it seems a shame to be moving to SQL2K at this time, expecial
ly
> if you are having trouble with it. Any posibility of going directly to SQ
L
> 2005?
> Tom
>|||sp_updatestats is weak, IMHO. Try doing an UPDATE STATISTICS WITH FULLSCAN.
That made a huge difference for me.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Carl Imthurn" <nospam@.all.thanks> wrote in message
news:OYuDJOYlGHA.380@.TK2MSFTNGP05.phx.gbl...
Tom --
Thanks for your help. I updated the statistics -- no improvement.
I will look at the query plans and check for differences.
I used the index tuning wizard in SQL2K and it suggested an additional
index on tbl_AHSTDN.Status (that column is already indexed, but only in
conjunction with other columns in a composite index). I tried that with
no success.
I will also check out index hints to see if that makes a difference.
And as far as SQL2005 goes, I would like to, but need to get a little
more up-to-speed with it first.
Thanks again -- I appreciate your time.
Carl
Tom Cooper wrote:
> In adddition to adding the clustered index, if this database has been
> updated from SQL7 to SQL2K, you should update the statistics (see the
> sp_updatestats stored procedure documentation in BOL) after the
> conversion.
> If you have not already done this, you should. WARNING, if you have a
> large
> database, this can take a considerable length of time.
> Also, look at the query plans for the updates in both databases and see if
> there is any differences. If there are, you may need different indexes on
> SQL2K than you did in SQL7. If SQL2K is choosing a bad plan, you might
> consider using index hints if that signifigantly improves performance.
> As an aside, it seems a shame to be moving to SQL2K at this time,
> expecially
> if you are having trouble with it. Any posibility of going directly to
> SQL
> 2005?
> Tom
>|||Hmmm... I thought the table was static - and that new records were just
being appended to the end. If you're dropping and recreating it each time,
then no need to reindex. Just create a clustered index before you start the
BULK INSERT, and create your other indexes after the fact. I'm not sure
where the speed problem is coming from. You don't happen to be pulling the
file from across the network (mapped drive or something)? And your database
is large enough to accomodate the new data (i.e., it's not autoshrinking and
autogrowing)?
"Carl Imthurn" <nospam@.all.thanks> wrote in message
news:us5wC1WlGHA.1240@.TK2MSFTNGP04.phx.gbl...
> Hi Mike --
> Thanks for your reply. Actually, this stored procedure has been running
> without a hitch for so long that I had to go back and refresh my memory
> about the columns, NULLs, etc.
> Here's what happens:
> 1) The table is dropped and recreated every morning with no indexes or
> primary keys
> 2) The data is fed in from text files via BULK INSERT
> 3) The nullable columns in the primary key are modified to be NOT NULL
> 4) The primary key is added
> 5) The indexes are added
> No clustered index -- I need to rectify that one. Thanks for catching it.
> And, since the bulk insert is done and then indexes are added, do I need
> to do a reindex?
> Anyway, I appreciate your time -- I will keep at it to figure out why it
> works in SQL7 but not in SQL2000
> Carl
> Mike C# wrote: