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:
Showing posts with label executes. Show all posts
Showing posts with label executes. Show all posts
Monday, March 12, 2012
Issue with SqlUserDefinedAggregate
I am using the code below but I am getting a "zero" result for dbo.AggredIssue('Test') user defined aggregate everytime that the query executes parallel processing and uses the "Merge" method. It seems that my private variable "private List<string> myList" gets nullified everytime it goes through the "Merge".
I saw other people reporting the same issue in other forums, but nobody was able to provide a solution or explanation.
See below a simplified version of my code (posted just after the queries) that replicates the issue.
The query below works because it does't process the query in parallel.
SELECT GroupID, dbo.AggregIssue('Test')
FROM MyTable
where fund = 2
group by GroupID
The query below doesn't work because it process the query in parallel.
SELECT GroupID, dbo.AggregIssue('Test')
FROM MyTable
where fund <= 20
group by GroupID
[Serializable]
[SqlUserDefinedAggregate(
Format.UserDefined,
IsInvariantToNulls = true,
IsInvariantToDuplicates = false,
IsInvariantToOrder = true,
MaxByteSize = 1000)]
public class AggregIssue : IBinarySerialize {
private List<string> myList;
private int myResult;
public void Init() {
myList = new List<string>();
}
public void Accumulate(SqlString Value) {
if (Value.IsNull) { return; }
myList.Add(Value.ToString());
}
public void Merge(AggregIssue Other) {
if (Other.myList != null) {
if (myList == null) {
myList = Other.myList;
}
else {
myList.AddRange(Other.myList);
}
}
}
public SqlInt32 Terminate() {
return new SqlInt32(myResult);
}
public void Read(BinaryReader r) {
myResult = r.ReadInt32();
}
//The code below is simplified for posting in the forum.
//I do additional manipulation of the list and require
//the aggregation to be IBinarySerialize.
//But this code replicates the issue also
w.Write(myList.Count);
}
}
? If you want to maintain the list properly you should serialize/deserialize the list itself -- and not its count -- in the Read and Write methods. They may be called more than one during the course of aggregation. Your current code is probably failing because it's making assumptions about how and when these will be called. You should instead do something like: public void Write(BinaryWriter w) { w.Write(myList.Count); foreach (string theString in myList) w.Write(theString); } public void Read(BinaryReader r) { this.myList = new List<string>(); int numStrings = r.ReadInt32(); for (int i = 0; i<numStrings; i++) myList.Add(r.ReadString()); } ... Then, in your Terminate method: public SqlInt32 Terminate() { return new SqlInt32(myList.Count); } -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <FernandoT@.discussions..microsoft.com> wrote in message news:e4178255-6dd9-4b48-b5ae-eb072c88e314_WBRev3_@.discussions..microsoft.com...This post has been edited either by the author or a moderator in the Microsoft Forums: http://forums.microsoft.com I am using the code below but I am getting a "zero" result for dbo.AggredIssue('Test') user defined aggregate everytime that the query executes parallel processing and uses the "Merge" method. It seems that my private variable "private List<string> myList" gets nullified everytime it goes through the "Merge". I saw other people reporting the same issue in other forums, but nobody was able to provide a solution or explanation. See below a simplified version of my code (posted just after the queries) that replicates the issue. The query below works because it does't process the query in parallel. SELECT GroupID, dbo.AggregIssue('Test') FROM MyTable where fund = 2 group by GroupID The query below doesn't work because it process the query in parallel. SELECT GroupID, dbo.AggregIssue('Test') FROM MyTable where fund <= 20 group by GroupID [Serializable] [SqlUserDefinedAggregate( Format.UserDefined, IsInvariantToNulls = true, IsInvariantToDuplicates = false, IsInvariantToOrder = true, MaxByteSize = 1000)] public class AggregIssue : IBinarySerialize { private List<string> myList; private int myResult; public void Init() { myList = new List<string>(); } public void Accumulate(SqlString Value) { if (Value.IsNull) { return; } myList.Add(Value.ToString()); } public void Merge(AggregIssue Other) { if (Other.myList != null) { if (myList == null) { myList = Other.myList; } else { myList.AddRange(Other.myList); } } } public SqlInt32 Terminate() { return new SqlInt32(myResult); } public void Read(BinaryReader r) { myResult = r.ReadInt32(); } //The code below is simplified for posting in the forum. //I do additional manipulation of the list and require //the aggregation to be IBinarySerialize. //But this code replicates the issue also public void Write(BinaryWriter w) { w.Write(myList.Count); } }|||
Hi Adam,
Thank you very much for your response.
The reason that I don't serialize the list itself is because of the MaxByteSize limitation of 8096. My list will easily go beyond that limitation. I test your solution and it works for a small list, but not for long ones.
I noticed that the list is loosing the information in the merge. If the query doesn't go through parallel threads, it works fine.
My assumption is that the serialization will happen before terminate and final output of aggregate value for each row of the result set.
Any other ideas?
Thanks!!
Fernando
|||Hi, Fernando,
I think Adam is right about you didn't serialize the List. Other people on the forum is talking about "break the 8k boundary" stuff, don't know what conclusion they have right now. But since you are using a List to join strings together, you always facing the problem.
About Merge() stuff, to my limited understanding:
If single thread (no parallel op):
Init() -> Accumulate() -> Terminate()
If multi threads (parallel plan):
T1: Init() -> Accumulate()
T2: Init() -> Accumulate() -> Merge( with T1)
T3: Init() -> Accumulate() -> Merge( with T3) -> Terminate()
This is only to illustrate the way Merge() works. Any T can be reused during this, thus why Init() must clean everything.
In Adam's book Chapter 6, p. 195, I quote:
"It is important to understand when dealing with aggregates that the intermediate result will be serialized and deserialized once per row of aggregated data. Therefore, it is imperative for performance that serialization and deserialization be as efficient as possible"
I'm a bit confused here, could Adam give some explanation for this paragraph? What's the relationship between Merge() and once per row of aggregated data?
Regards,
Dong Xie
|||? Actually, that quote from the book is not quite accurate anymore -- when I wrote it against an earlier CTP it appeared to be true, but now it does not. I believe that it has been changed in a later printing of the book, but I'll check today with Apress to make sure. Thanks for pointing it out! Anyway, the correct phrasing at this point should be "the intermediate result can be serialized and deserialized up to one time per row of aggregated data" In other words, the SQL Server engine may not do it for every row (or even nearly that often in most cases), but you need to code your aggregate as if it will. There is not necessarily a direct/stated relationship between Merge and Read/Write, but it appears that when Merge is called the engine also does a round of serialization/deserialization. I'm not sure why, though. Hopefully Steven Hemingray or one of the other dataworks guys will show up in this thread and clarify! -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Dong Xie@.discussions.microsoft.com> wrote in message news:c5fafeee-262b-43a4-9295-10a7cb07d92a@.discussions.microsoft.com...Hi, Fernando,I think Adam is right about you didn't serialize the List. Other people on the forum is talking about "break the 8k boundary" stuff, don't know what conclusion they have right now. But since you are using a List to join strings together, you always facing the problem.About Merge() stuff, to my limited understanding:If single thread (no parallel op):Init() -> Accumulate() -> Terminate()If multi threads (parallel plan):T1: Init() -> Accumulate()T2: Init() -> Accumulate() -> Merge( with T1)T3: Init() -> Accumulate() -> Merge( with T3) -> Terminate()This is only to illustrate the way Merge() works. Any T can be reused during this, thus why Init() must clean everything.In Adam's book Chapter 6, p. 195, I quote:"It is important to understand when dealing with aggregates that the intermediate result will be serialized and deserialized once per row of aggregated data. Therefore, it is imperative for performance that serialization and deserialization be as efficient as possible"I'm a bit confused here, could Adam give some explanation for this paragraph? What's the relationship between Merge() and once per row of aggregated data?Regards,Dong Xie|||
It seems that you are right and serialization/deserialization happens when merge is called.
For now I have a workaround that was suggested in another forum to use "MAXDOP=1" and it works as merge is never executed.
It is not ideal, as parallelism cannot be leveraged, but it is a workaround.
Thanks to all for the responses!!
Fernando
|||There seems to be a few issues within the thread:
1) Why is the private field myList nullified?
Whenever serialization takes place (Read/Write), a new instance is instantiated and it is up to your serialization code to fill the instance. Since your Write() only sets myResult, myList will be remain on the default value of null.
2) Why is MaxByteSize not always enforced?
MaxByteSize is enforced during serialization. You could cause instances to become much larger than 8k over Accumulate() calls and then not serialize out 8k.
With this said, there should not be a reason to build UDAggs that become larger than 8k but do not serialize out 8k. Read/write should serialize all necessary information for the UDAggs.
The UDAgg code within this thread is a good example of a contrived case for this. Since Terminate() only returns the count, there is not a need to accumulate the actual strings, but Accumulate() could increment a counter instead.
3) When is Read/Write called?
Within SQL Server 2005, serialization takes place during Merge() and before Terminate() is called. If the UDAgg provided accessed the myList within Terminate(), you would see a NullReferenceException from within Terminate() (since Write() does not reconstruct myList and null is its default reference).
As pointed out within this thread, Read/Write is not called for every row. However, please write your UDAgg with proper serialization semantics as if it were called every row.
Hope that helps!
-Jason
|||Jason,
Thanks for all your explanation. It makes sense to me now how all this work.
But I still have one issue, which is the limitation of 8K. The list that I am building cannot be contrived until it is complete just before "Terminate". In the example I am passing the count and I could do that in the merge also. But in my real case, I need the complete list to be used in the resolution of a non-linear equation, so I cannot do anything with it until I pass it to a iterative algorithm in order to solve the equation. But this list can grow bigger than 8K.
The only way to avoid so far is to limit the degree of parallelism to 1, so the code doesn't go to merge.
Any thoughts on this?
Thanks,
Fernando
|||Fernando-
Merge is also called in some more complex queries (such as GROUP BY WITH CUBE).
This is an interesting workaround to the 8k limitation for your scenario. I do worry that your workaround leaves your UDAgg prone to internal changes within SQL Server causes serialization to be more frequently performed or eliminated. For this reason, I'd recommend always using an approach where instances created from Read/Write are no different than the original.
I cannot recommend using this approach for these reasons, but limiting DOP to 1 should eliminate Merge() calls for the most part. If you absolutely must use this workaround, I'd recommend safeguarding against your assumptions (Merge never called, serialization takes place once after all Accumulate calls but before Terminate):
Always throw an exception within your Merge() call so that in the case that Merge() is invoked, you'll know what the issue is and you can try to simplify the query so that Merge is not called.|||Jason,
Thanks for all your help and explanations.
Fernando
Monday, February 20, 2012
isqlw.exe from a console app
Can someone who knows a lot more about this than me please tell why, when the following code executes, I get a pop-up window telling me the usage of isqlw.exe?
<code>
strQueryCommandPath = "C:\\Program Files\\Microsoft SQL Server\\80\\Tools\\Binn\\";
strArguments = "-S(local)\\SQLEXPRESS ";
strArguments += "-Usa ";
strArguments += "-Padminmlc ";
strArguments += "-i" + strCurrentDir + strFileName + " ";
myProcess.StartInfo.WorkingDirectory = strQueryCommandPath;
myProcess.StartInfo.FileName = "ISQLW.EXE";
myProcess.StartInfo.Arguments = strArguments;
myProcess.StartInfo.UseShellExecute = false;
return myProcess.Start();
</code>
I've also tried it with a space in between the - switch and also with quotes around the switch arguments. I've put the entire path in just the StartInfo.FileName instead of switching the working directory as well. I can't for the life of me get it to actually fire off this command for some reason.
p.s. I've also tried using "/"s instead of "-"s as well.
Generally this is becuase you have one of the switches wrong... I would put some debug code to display the command line command that you are trying to run... then copy and past it to see what it look like.
|||I finally got all the switches to go through right, but now it's telling me access is denied to my My Documents folder (where the .bak file is) to do the restore. It keeps erroring and teminating the RESTORE command. It's not read only or anything (not that I'd think that would necessarily affect it) and I don't know why it would throw an OS access denied error on any part of my computer for me.
Subscribe to:
Comments (Atom)