optimizing a query to delete duplicates

I have a DELETE statement that deletes duplicate data from a table. It
takes a long time to execute, so I thought I'd seek advice here. The
structure of the table is little funny. The following is NOT the table,
but the representation of the data in the table:

+----+
| a | b |
+--+--+
| 123 | 234 |
| 345 | 456 |
| 123 | 123 |
+--+--+

As you can see, the data is tabular. This is how it is stored in the table:

+--+----+----+
| Row | FieldName | FieldValue |
+--+----+----+
| 1 | a | 123 |
| 1 | b | 234 |
| 2 | a | 345 |
| 2 | b | 456 |
| 3 | a | 123 |
| 3 | b | 234 |
+--+----+----+

What I need is to delete all records having the same "Row" when there exists
the same set of records with a different (smaller, to be precise) "Row".
Using the example above, what I need to get is:

+--+----+----+
| Row | FieldName | FieldValue |
+--+----+----+
| 1 | a | 123 |
| 1 | b | 234 |
| 2 | a | 345 |
| 2 | b | 456 |
+--+----+----+

A slow way of doing this seem to be:

DELETE FROM X
WHERE Row IN
(SELECT DISTINCT Row FROM X x1
WHERE EXISTS
(SELECT * FROM X x2
WHERE x2.Row < x1.Row
AND NOT EXISTS
(SELECT * FROM X x3
WHERE x3.Row = x2.Row
AND x3.FieldName = x2.FieldName
AND x3.FieldValue <> x1.FieldValue)))

Can this be done faster, better, and cheaper?my knee-jerk reaction is:

Why is it important to optimize it? I think you should delete the
duplicates, then create a constraint that prevents them from recurring.

If, for some reason, you are unable to fix the application that creates
these duplicates, and creating a constraint causes errors in the application
that you can't tolerate, then I suppose an alternative would be to create a
trigger that deletes them upon entry. Having a composite index on the
columns that are being duplicated would enable such a trigger to run
quickly.

But looking at your query, I find it strangely complex.

Why not just:

DELETE FROM X
WHERE EXISTS (SELECT * FROM X x2
WHERE x2.Row < x.Row
AND X.FieldName = x2.FieldName
AND X.FieldValue = x2.FieldValue)

Am I missing something? Your NOT EXISTS has me a bit confused... I think it
might delete data in situations other than described.

Also, NOT EXISTS is generally slow.|||On 2004-07-15, Aaron W. West <tallpeak@.hotmail.NO.SPAM> wrote:
> Why is it important to optimize it? I think you should delete the
> duplicates, then create a constraint that prevents them from recurring.

Such constraint may not be created. This table is a temporary table, where
data from an input file is loaded. Duplicate sets of records must be
deleted because the data then goes into permanent tables. Those table have
constraints against duplicates.

> But looking at your query, I find it strangely complex.

Me too. I'm trying to improve it. Its complexity seems to hinder its
performance.

> Why not just:
> DELETE FROM X
> WHERE EXISTS (SELECT * FROM X x2
> WHERE x2.Row < x.Row
> AND X.FieldName = x2.FieldName
> AND X.FieldValue = x2.FieldValue)

This would delete records that should not be deleted. Here's an example:

+--+----+----+
| Row | FieldName | FieldValue |
+--+----+----+
| 1 | a | 123 |
| 1 | b | 234 |
| 2 | a | 345 |
| 2 | b | 456 |
| 3 | a | 123 |
| 3 | b | 666 |
+--+----+----+

Here the combination of values for "a" and "b" on every "Row" is
different. There are no duplicates here. The query that you proposed would
delete the second to last row

+--+----+----+
| 3 | a | 123 |
+--+----+----+

because it has the same FieldName and FieldValue as the first row.

Think of it the data this way:

+--+--+
| a | b |
+--+--+
| 123 | 234 |
| 345 | 456 |
| 123 | 666 |
+--+--+

No duplicate rows here.|||Hi

You could try only selecting the correct data when you move it into the
permanent tables. But the following may work better:

DELETE FROM X1
FROM X X1 JOIN X X2
ON x2.Row < x1.Row
AND x1.Fieldvalue = x2.Fieldvalue
AND x1.FieldName = x2.FieldName

John

"Alexander Anderson" <no@.spam.com> wrote in message
news:slrncfe0ft.mk1.alex@.Toronto-HSE-ppp3682122.sympatico.ca...
> I have a DELETE statement that deletes duplicate data from a table. It
> takes a long time to execute, so I thought I'd seek advice here. The
> structure of the table is little funny. The following is NOT the table,
> but the representation of the data in the table:
> +----+
> | a | b |
> +--+--+
> | 123 | 234 |
> | 345 | 456 |
> | 123 | 123 |
> +--+--+
> As you can see, the data is tabular. This is how it is stored in the
table:
> +--+----+----+
> | Row | FieldName | FieldValue |
> +--+----+----+
> | 1 | a | 123 |
> | 1 | b | 234 |
> | 2 | a | 345 |
> | 2 | b | 456 |
> | 3 | a | 123 |
> | 3 | b | 234 |
> +--+----+----+
> What I need is to delete all records having the same "Row" when there
exists
> the same set of records with a different (smaller, to be precise) "Row".
> Using the example above, what I need to get is:
> +--+----+----+
> | Row | FieldName | FieldValue |
> +--+----+----+
> | 1 | a | 123 |
> | 1 | b | 234 |
> | 2 | a | 345 |
> | 2 | b | 456 |
> +--+----+----+
> A slow way of doing this seem to be:
> DELETE FROM X
> WHERE Row IN
> (SELECT DISTINCT Row FROM X x1
> WHERE EXISTS
> (SELECT * FROM X x2
> WHERE x2.Row < x1.Row
> AND NOT EXISTS
> (SELECT * FROM X x3
> WHERE x3.Row = x2.Row
> AND x3.FieldName = x2.FieldName
> AND x3.FieldValue <> x1.FieldValue)))
> Can this be done faster, better, and cheaper?

Optimization Advice Please!!!

I apologize for the code being lengthy but it's a necessary evil at this point.

I was hoping someone could take a look and give me some pointers on optimizing it.

My first question, in T-SQL if the first ELSE IF is true, does it ignore the following ELSE IF's? If not, how do I escape out of the block?

Code Snippet

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

set NOCOUNT ON

GO

ALTERProcedure [dbo].[usp_DockCrewValidation]

@.WeekEnding assmalldatetime,

@.EquipLabor as varchar(20)

AS

DECLARE @.PO as varchar(20),

@.JobClass as varchar(20),

@.PayCode as varchar(20),

@.CostCenter as varchar(20),

@.EmpNum as varchar(20),

@.EquipOrLabor as varchar(20),

@.WorkDate assmalldatetime,

@.ChrgNum as varchar(20),

@.Hours asint,

@.ChrgAmt asmoney,

@.EquipCode as varchar(20),

@.InDCMS asbit,

@.ImportDate assmalldatetime,

@.BlanketNum as varchar(20),

@.EquipNum as varchar(30)

--Disable Update Trigger so it doesn't interfere

ALTERTABLE PManUser.DockCrewImportErrors

DISABLE TRIGGER trg_UpdateValidation

DECLARE @.Errors asbit

DECLARE DockCrewValidation CURSOR FAST_FORWARD FOR

SELECT PONum, Class, PayCode, CostCenter, EmpNum, EquipOrLabor, InDCMS, Hours, ChrgAmt, ChargeNum, EquipCode, WorkDate, ImportDate, BlanketNum, EquipNum

FROM PManUser.DockCrewImportErrors

WHERECONVERT(char(10), weekending, 101)= @.WeekEnding

AND EquipOrLabor = @.EquipLabor

OPEN DockCrewValidation

FETCH NEXT FROM DockCrewValidation

INTO @.PO, @.JobClass, @.PayCode, @.CostCenter, @.EmpNum, @.EquipOrLabor, @.InDCMS, @.Hours, @.ChrgAmt, @.ChrgNum, @.EquipCode, @.WorkDate, @.ImportDate, @.BlanketNum, @.EquipNum

WHILE@.@.FETCH_STATUS= 0

BEGIN

SET @.Errors = 0

IF(SELECT CancelDt FROM RCDCD10 WHERELEFT(JobNo, 8)=LEFT(REPLACE(@.ChrgNum,'-',''), 8))ISNOTNULL

BEGIN

UPDATE PManUser.DockCrewImportErrors

SET Reason ='Job Number Cancelled'

WHERE PONum = @.PO AND

ChargeNum = @.ChrgNum

SET @.Errors = 1

END

ELSEIF(SELECT zCodeDt FROM RCDCD10 WHERELEFT(JobNo, 8)=LEFT(REPLACE(@.ChrgNum,'-',''), 8))ISNOTNULL

BEGIN

UPDATE PManUser.DockCrewImportErrors

SET Reason ='Job Number Z-Coded'

WHERE PONum = @.PO AND

ChargeNum = @.ChrgNum

SET @.Errors = 1

END

ELSEIF(SELECT PO FROM PO WHERE PO = @.PO)=''

BEGIN

UPDATE PManUser.DockCrewImportErrors

SET Reason ='Invalid PO Number'

WHERE PONum = @.PO

SET @.Errors = 1

END

ELSEIF(SELECT JobClass FROM JobClass WHERE JobClass = @.JobClass)=''AND @.EquipOrLabor ='Labor'

BEGIN

UPDATE PManUser.DockCrewImportErrors

SET Reason ='Invalid Job Class'

WHERE PONum = @.PO AND

ChargeNum = @.ChrgNum AND

Class = @.JobClass

END

ELSEIF(SELECT PayCode FROM PayCodes WHERE PayCode = @.PayCode)=''AND @.EquipOrLabor ='Labor'

BEGIN

UPDATE PManUser.DockCrewImportErrors

SET Reason ='Invalid PayCode'

WHERE PONum = @.PO AND

ChargeNum = @.ChrgNum AND

Paycode = @.PayCode

SET @.Errors = 1

END

ELSEIF(SELECT CC_Num FROM CostCenter WHERE CC_Num = @.CostCenter)=''

BEGIN

UPDATE PManUser.DockCrewImportErrors

SET Reason ='Invalid Cost Center'

WHERE PONum = @.PO AND

ChargeNum = @.ChrgNum AND

CostCenter = @.CostCenter

SET @.Errors = 1

END

ELSEIF(SELECT EmpNum FROM Employees WHERE EmpNum = @.EmpNum)=''AND @.EquipOrLabor ='Labor'

BEGIN

UPDATE PManUser.DockCrewImportErrors

SET Reason ='Invalid Employee Number'

WHERE PONum = @.PO AND

ChargeNum = @.ChrgNum AND

EmpNum = @.EmpNum

SET @.Errors = 1

END

--End Labor Check

IF @.Errors = 0 --If there are no errors, insert into LaborEntrys Table

BEGIN

UPDATE PManUser.DockCrewImportErrors

SET Reason ='Valid Entry'

WHERE PONum = @.PO

AND EmpNum = @.EmpNum

AND Class = @.JobClass

AND PayCode = @.PayCode

AND CostCenter = @.CostCenter

AND EquipOrLabor = @.EquipOrLabor

AND InDCMS = @.InDCMS

--AND Reason NOT IN ('Job Number Cancelled', 'Job Number Z-Coded')

IF @.EquipORLabor ='Labor'AND @.InDCMS = 1 AND @.BlanketNum = 0

BEGIN

INSERTINTO LaborEntrys(PONum, EmpNum, WorkDate, ChrgNum, CostCenter, Hours, PayCode, Class, ChrgAmt, InDate, WeekEnding)

VALUES(@.PO, @.EmpNum, @.WorkDate, @.ChrgNum, @.CostCenter, @.Hours, @.PayCode, @.JobClass, @.ChrgAmt, @.ImportDate, @.WeekEnding)

END

IF @.EquipORLabor ='Equipment'AND @.InDCMS = 1 AND @.BlanketNum = 0

BEGIN

INSERTINTO EquipEntrys(PONum, WorkDate, ChrgNum, EquipCode, CostCenter, Hours, InDate, EquipNum, WeekEnding)

VALUES(@.PO, @.WorkDate, @.ChrgNum, @.EquipCode, @.CostCenter, @.Hours,GETDATE(), @.EquipNum, @.WeekEnding)

END

END

FETCH NEXT FROM DockCrewValidation

INTO @.PO, @.JobClass, @.PayCode, @.CostCenter, @.EmpNum, @.EquipOrLabor, @.InDCMS, @.Hours, @.ChrgAmt, @.ChrgNum, @.EquipCode, @.WorkDate, @.ImportDate, @.BlanketNum, @.EquipNum

END

CLOSE DockCrewValidation

DEALLOCATE DockCrewValidation

DELETEFROM PManUser.DockCrewImportErrors

WHERE Reason ='Valid Entry'AND InDCMS = 1

ALTERTABLE PManUser.DockCrewImportErrors

ENABLE TRIGGER trg_UpdateValidation

Thanks in advance,

Adamus

Without checking the procedure...

T-SQL does NOT have ELSEIF. Use nested IF...ELSE, or CASE structures.

Do you want to revise and start over?

Sorry Adam, I just had to jerk your chain a little ...

Since you have nested the IF statements, you are the one to determine if the code will flow as you intended.

Good and rigorous formatting usually helps follow the flow...

I think that this can be revised using CASE statements seveal other ways to enhance performance and readibility. I'll get back to you shortly with some suggestions.

Code Snippet

IF (SELECT CancelDt FROM RCDCD10 WHERE LEFT(JobNo, 8) = LEFT(REPLACE(@.ChrgNum, '-',''), 8)) IS NOT NULL
BEGIN
UPDATE PManUser.DockCrewImportErrors
SET Reason = 'Job Number Cancelled'
WHERE PONum = @.PO AND
ChargeNum = @.ChrgNum
SET @.Errors = 1
END
ELSE
IF (SELECT zCodeDt FROM RCDCD10 WHERE LEFT(JobNo, 8) = LEFT(REPLACE(@.ChrgNum, '-',''), 8)) IS NOT NULL
BEGIN
UPDATE PManUser.DockCrewImportErrors
SET Reason = 'Job Number Z-Coded'
WHERE PONum = @.PO AND
ChargeNum = @.ChrgNum
SET @.Errors = 1
END
ELSE
IF (SELECT PO FROM PO WHERE PO = @.PO) = ''
BEGIN
UPDATE PManUser.DockCrewImportErrors
SET Reason = 'Invalid PO Number'
WHERE PONum = @.PO
SET @.Errors = 1
END
ELSE
IF (SELECT JobClass FROM JobClass WHERE JobClass = @.JobClass) = '' AND @.EquipOrLabor = 'Labor'
BEGIN
UPDATE PManUser.DockCrewImportErrors
SET Reason = 'Invalid Job Class'
WHERE PONum = @.PO AND
ChargeNum = @.ChrgNum AND
Class = @.JobClass
END
ELSE
IF (SELECT PayCode FROM PayCodes WHERE PayCode = @.PayCode) = '' AND @.EquipOrLabor = 'Labor'
BEGIN
UPDATE PManUser.DockCrewImportErrors
SET Reason = 'Invalid PayCode'
WHERE PONum = @.PO AND
ChargeNum = @.ChrgNum AND
Paycode = @.PayCode
SET @.Errors = 1
END
ELSE
IF (SELECT CC_Num FROM CostCenter WHERE CC_Num = @.CostCenter) = ''
BEGIN
UPDATE PManUser.DockCrewImportErrors
SET Reason = 'Invalid Cost Center'
WHERE PONum = @.PO AND
ChargeNum = @.ChrgNum AND
CostCenter = @.CostCenter
SET @.Errors = 1
END
ELSE
IF (SELECT EmpNum FROM Employees WHERE EmpNum = @.EmpNum) = '' AND @.EquipOrLabor = 'Labor'
BEGIN
UPDATE PManUser.DockCrewImportErrors
SET Reason = 'Invalid Employee Number'
WHERE PONum = @.PO AND
ChargeNum = @.ChrgNum AND
EmpNum = @.EmpNum
SET @.Errors = 1
END

|||

Arnie Rowland wrote:

Without checking the procedure...

T-SQL does NOT have ELSEIF. Use nested IF...ELSE, or CASE structures.

Do you want to revise and start over?

Sorry Adam, I just had to jerk your chain a little ...

Since you have nested the IF statements, you are the one to determine if the code will flow as you intended.

Good and rigorous formatting usually helps follow the flow...

Also, in seveal places, you have a condition test in the form of

IF @.a = @.b IS NOT NULL

It needs to be either @.a = @.b or @.a IS NOT NULL. You can't combine the conditions in this manner, you need to use AND/OR.

I did the best I could to format the code inside that little code window but it should read:

IF (SELECT @.a FROM myTable WHERE @.a = @.b) IS NOT NULL

Also, if I could take a different approach that would optimize performance I'm all ears.

Let me explain a little more.

This stored procedure is called after records are imported into table from Excel. All records begin as errors until proven innocent. It basically grabs one record at a time (hence the cursor) and validates the record through the given code. If the record passes all interrogations, it's inserted into the appropriate table. If it fails, the Reason field is updated and the record remains in the error table.

In all actuality, the first import and query takes around 1 minute to run. The second import and query takes about 2 1/2 minutes to run. This is acceptable, but I was hoping to get it down to under a minute if possible.

Adamus

|||What you posted is about as good as it will get. Yes, SQL will NOT process the other ELSE statements after it hits one which is true.

I would change one minor thing, instead of running UPDATE on every error, I would create a var with the error and do:

IF ..........
BEGIN
SET @.msg = 'Invalid Employee Number'
SET @.ERROR = 1
END
ELSE IF....

IF @.ERROR=0
.....
ELSE
BEGIN

UPDATE PManUser.DockCrewImportErrors

SET Reason = @.msg

END

You could do it without a cursor by doing:

UPDATE

PManUser.DockCrewImportErrors

SET Reason = 'Job Number Cancelled'

FROM PManUser.DockCrewImportErrors

JOIN RCDCD10 WHERELEFT(JobNo, 8)=LEFT(REPLACE(@.ChrgNum,'-',''), 8)

WHERECONVERT(char(10), weekending, 101)= @.WeekEnding

AND EquipOrLabor = @.EquipLabor

AND Reason IS NULL

AND CancelDT IS NOT NULL

Do this logic again for the next result. This way you are ONLY processing the records which do not have a Reason code AND have the error you are looking for.

|||

Code Snippet

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
set NOCOUNT ON
GO
ALTER Procedure [dbo].[usp_DockCrewValidation]
@.WeekEnding as smalldatetime,
@.EquipLabor as varchar(20)
AS
BEGIN
DECLARE
@.PO as varchar(20),
@.JobClass as varchar(20),
@.PayCode as varchar(20),
@.CostCenter as varchar(20),
@.EmpNum as varchar(20),
@.EquipOrLabor as varchar(20),
@.WorkDate as smalldatetime,
@.ChrgNum as varchar(20),
@.Hours as int,
@.ChrgAmt as money,
@.EquipCode as varchar(20),
@.InDCMS as bit,
@.ImportDate as smalldatetime,
@.BlanketNum as varchar(20),
@.EquipNum as varchar(30)

declare @.ImportErrors table
(
PONum varchar(20),
Class varchar(20),
PayCode varchar(20),
CostCenter varchar(20),
EmpNum varchar(20),
EquipOrLabor varchar(20),
InDCMS bit,
Hours int,
ChrgAmt money,
ChargeNum varchar(20),
EquipCode varchar(20),
WorkDate datetime,
ImportDate datetime,
BlanketNum varchar(20),
EquipNum varchar(30)
)
--Disable Update Trigger so it doesn't interfere

ALTER TABLE PManUser.DockCrewImportErrors
DISABLE TRIGGER trg_UpdateValidation

DECLARE @.Errors as bit

DECLARE DockCrewValidation CURSOR FAST_FORWARD FOR
select
PONum, Class, PayCode, CostCenter, EmpNum, EquipOrLabor, InDCMS, Hours,
ChrgAmt, ChargeNum, EquipCode, WorkDate, ImportDate, BlanketNum, EquipNum
From
@.ImportErrors

insert into @.ImportErrors
(PONum, Class, PayCode, CostCenter, EmpNum, EquipOrLabor, InDCMS, Hours,
ChrgAmt, ChargeNum, EquipCode, WorkDate, ImportDate, BlanketNum, EquipNum)
SELECT
PONum, Class, PayCode, CostCenter, EmpNum, EquipOrLabor, InDCMS, Hours,
ChrgAmt, ChargeNum, EquipCode, WorkDate, ImportDate, BlanketNum, EquipNum
FROM
PManUser.DockCrewImportErrors
WHERE
CONVERT(char(10), weekending, 101) = @.WeekEnding
AND EquipOrLabor = @.EquipLabor

OPEN DockCrewValidation
FETCH NEXT FROM DockCrewValidation
INTO
@.PO, @.JobClass, @.PayCode, @.CostCenter, @.EmpNum, @.EquipOrLabor,
@.InDCMS, @.Hours, @.ChrgAmt, @.ChrgNum, @.EquipCode, @.WorkDate,
@.ImportDate, @.BlanketNum, @.EquipNum

WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Errors = 0

IF (SELECT CancelDt FROM RCDCD10 WHERE LEFT(JobNo, 8) = LEFT(REPLACE(@.ChrgNum, '-',''), 8)) IS NOT NULL
BEGIN
UPDATE @.ImportErrors
SET Reason = 'Job Number Cancelled'
WHERE PONum = @.PO AND
ChargeNum = @.ChrgNum
SET @.Errors = 1
END
ELSE IF (SELECT zCodeDt FROM RCDCD10 WHERE LEFT(JobNo, 8) = LEFT(REPLACE(@.ChrgNum, '-',''), 8)) IS NOT NULL
BEGIN
UPDATE @.ImportErrors
SET Reason = 'Job Number Z-Coded'
WHERE PONum = @.PO AND
ChargeNum = @.ChrgNum
SET @.Errors = 1
END
ELSE IF (SELECT PO FROM PO WHERE PO = @.PO) = ''
BEGIN
UPDATE @.ImportErrors
SET Reason = 'Invalid PO Number'
WHERE PONum = @.PO
SET @.Errors = 1
END
ELSE IF exists (SELECT 'x' FROM JobClass WHERE JobClass = @.JobClass and JobClass = '') AND @.EquipOrLabor = 'Labor'
BEGIN
UPDATE @.ImportErrors
SET Reason = 'Invalid Job Class'
WHERE PONum = @.PO
AND ChargeNum = @.ChrgNum
AND Class = @.JobClass
END
ELSE IF (SELECT PayCode FROM PayCodes WHERE PayCode = @.PayCode) = '' AND @.EquipOrLabor = 'Labor'
BEGIN
UPDATE @.ImportErrors
SET Reason = 'Invalid PayCode'
WHERE PONum = @.PO AND
ChargeNum = @.ChrgNum AND
Paycode = @.PayCode
SET @.Errors = 1
END
ELSE IF (SELECT CC_Num FROM CostCenter WHERE CC_Num = @.CostCenter) = ''
BEGIN
UPDATE @.ImportErrors
SET Reason = 'Invalid Cost Center'
WHERE PONum = @.PO AND
ChargeNum = @.ChrgNum AND
CostCenter = @.CostCenter

SET @.Errors = 1
END

ELSE IF (SELECT EmpNum FROM Employees WHERE EmpNum = @.EmpNum) = '' AND @.EquipOrLabor = 'Labor'
BEGIN
UPDATE
@.ImportErrors
SET
Reason = 'Invalid Employee Number'
WHERE
PONum = @.PO AND
ChargeNum = @.ChrgNum AND
EmpNum = @.EmpNum

SET @.Errors = 1

END

--End Labor Check

IF @.Errors = 0 --If there are no errors, insert into LaborEntrys Table
BEGIN
UPDATE @.ImportErrors
SET Reason = 'Valid Entry'
WHERE PONum = @.PO
AND EmpNum = @.EmpNum
AND Class = @.JobClass
AND PayCode = @.PayCode
AND CostCenter = @.CostCenter
AND EquipOrLabor = @.EquipOrLabor
AND InDCMS = @.InDCMS

--AND Reason NOT IN ('Job Number Cancelled', 'Job Number Z-Coded')

IF @.EquipORLabor = 'Labor' AND @.InDCMS = 1 AND @.BlanketNum = 0
BEGIN
INSERT INTO LaborEntrys(PONum, EmpNum, WorkDate, ChrgNum, CostCenter, Hours, PayCode, Class, ChrgAmt, InDate, WeekEnding)
VALUES(@.PO, @.EmpNum, @.WorkDate, @.ChrgNum, @.CostCenter, @.Hours, @.PayCode, @.JobClass, @.ChrgAmt, @.ImportDate, @.WeekEnding)
END

IF @.EquipORLabor = 'Equipment' AND @.InDCMS = 1 AND @.BlanketNum = 0
BEGIN
INSERT INTO EquipEntrys(PONum, WorkDate, ChrgNum, EquipCode, CostCenter, Hours, InDate, EquipNum, WeekEnding)
VALUES(@.PO, @.WorkDate, @.ChrgNum, @.EquipCode, @.CostCenter, @.Hours, GETDATE(), @.EquipNum, @.WeekEnding)
END
END

FETCH NEXT FROM DockCrewValidation
INTO
@.PO, @.JobClass, @.PayCode, @.CostCenter, @.EmpNum, @.EquipOrLabor,
@.InDCMS, @.Hours, @.ChrgAmt, @.ChrgNum, @.EquipCode, @.WorkDate,
@.ImportDate, @.BlanketNum, @.EquipNum

END
CLOSE DockCrewValidation
DEALLOCATE DockCrewValidation
Update
b
Set
Reason = a.Reason
From
PManUser.DockCrewImportErrors b
Join
@.ImportErrors a
on
b.PONum = a.PONum

DELETE FROM PManUser.DockCrewImportErrors
WHERE
Reason = 'Valid Entry'
AND InDCMS = 1

ALTER TABLE PManUser.DockCrewImportErrors
ENABLE TRIGGER trg_UpdateValidation
END

-- I appreciate if revert back with your comments.
-- I request your to bear with the compilations error(if any)
-- But it would definitely improve the performance
-- I have assumed the PONUM is unique in PManUser.DockCrewImportErrors table. If not please ignore this.

|||

PONum has duplicates. The uniqueness is fairly concatenated as you'll notice in the final UPDATE:

UPDATE @.ImportErrors
SET Reason = 'Valid Entry'
WHERE PONum = @.PO
AND EmpNum = @.EmpNum
AND Class = @.JobClass
AND PayCode = @.PayCode
AND CostCenter = @.CostCenter
AND EquipOrLabor = @.EquipOrLabor
AND InDCMS = @.InDCMS

I will test this on Monday, but I like what I see.

I will definately post a response.

Thank you all,

Adamus

|||

Tom,

I'm not sure I could avoid the cursor considering each check is unique in that it bounces checks from different tables. I do like the idea of segregating the error messages for readability though.

Thanks for your input,

Adamus

|||

Adam,

I'm going to offer a 'best guess' about set based operations. I'm handicapped by not having the DDL and complete understanding of your objectives, but I think you just might be able to handle this with a series of carefully created update queries -and completely avoid the CURSOR. If it could work, the speed difference should be substaintial.

So look this over and tell me what I missed in handling the UPDATE for just these two cases. (Imagine a single statement handling all rows that meet a set of criteria -in one action.)

If you think this has possibilites, I'll help you put the rest together.

Code Snippet

UPDATE PManUser.DockCrewImportErrors
SET Reason = 'Job Number Cancelled'
FROM PManUser.DockCrewImportErrors d
JOIN RCDCD10 r
ON ( left(r.JobNo, 8) = LEFT(REPLACE(d.ChrgNum, '-',''), 8)
AND r.CancelDt IS NOT NULL
)
WHERE ( convert(char(10), weekending, 101) = @.WeekEnding
AND EquipOrLabor = @.EquipLabor
)

IF ( @.@.ROWCOUNT > 0 )
SET @.Errors = 1

UPDATE PManUser.DockCrewImportErrors
SET Reason = 'Job Number Z-Coded'
FROM PManUser.DockCrewImportErrors d
JOIN RCDCD10 r
ON ( left(r.JobNo, 8) = LEFT(REPLACE(d.ChrgNum, '-',''), 8)
AND r.zCodeDt IS NOT NULL
)
WHERE ( convert(char(10), weekending, 101) = @.WeekEnding
AND EquipOrLabor = @.EquipLabor
)

IF ( @.@.ROWCOUNT > 0 )
SET @.Errors = 1

|||

Arnie, it looks like you've struck oil with this one. Let's bring the troops home. We no longer need to import. Let the Suni's and Shiites fight away. lol

I could very well code it in this linear fashion and avoid the cursor altogether. I'm not sure why I was so stubborn to begin with.

The JOIN on the two above will work. The additional blocks will need some tweaks but I believe a LEFT or RIGHT JOIN on the table being bounced should work beautifully.

I'll let you know on Monday.

Thanks again,

Adamus

|||

Arnie,

I worked beautifully.

Both imports and queries took 1.5 minutes and 2.5 minutes. Removing the cursor and using linear updates takes under 1 second.

Code Snippet

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

ALTERProcedure [dbo].[usp_DockCrewValidation]

@.WeekEnding assmalldatetime,

@.EquipLabor as varchar(20)

AS

--Disable Update Trigger so it doesn't interfere

ALTERTABLE PManUser.DockCrewImportErrors

DISABLE TRIGGER trg_UpdateValidation

DECLARE @.Errors asbit

SET @.Errors = 0

UPDATE dc

SET dc.InDCMS = 1

FROM PManUser.DockCrewImportErrors dc

JOIN RCDCD10 r ONLEFT(r.JobNo, 8)=LEFT(REPLACE(dc.ChargeNum,'-',''), 8)

WHERE WeekEnding = @.WeekEnding AND EquipOrLabor = @.EquipLabor

UPDATE dc

SET dc.Reason ='Job Number Cancelled'

FROM PManUser.DockCrewImportErrors dc

JOIN RCDCD10 r ONLEFT(r.JobNo, 8)=LEFT(REPLACE(dc.ChargeNum,'-',''), 8)

AND r.CancelDt ISNOTNULL

WHERE WeekEnding = @.WeekEnding AND EquipOrLabor = @.EquipLabor

IF(@.@.ROWCOUNT> 0 )

SET @.Errors = 1

UPDATE dc

SET dc.Reason ='Job Number Z-Coded'

FROM PManUser.DockCrewImportErrors dc

JOIN RCDCD10 r ONLEFT(r.JobNo, 8)=LEFT(REPLACE(dc.ChargeNum,'-',''), 8)

AND r.ZCodeDt ISNOTNULL

WHERE WeekEnding = @.WeekEnding AND EquipOrLabor = @.EquipLabor

IF(@.@.ROWCOUNT> 0 )

SET @.Errors = 1

UPDATE dc

SET dc.Reason ='Invalid PO Number'

FROM PManUser.DockCrewImportErrors dc

LEFTJOIN PO ON dc.PONum = PO.PO

WHERE PO.PO =''

AND WeekEnding = @.WeekEnding

AND EquipOrLabor = @.EquipLabor

IF(@.@.ROWCOUNT> 0 )

SET @.Errors = 1

UPDATE dc

SET dc.Reason ='Invalid Job Class'

FROM PManUser.DockCrewImportErrors dc

LEFTJOIN JobClass jc ON dc.Class = jc.JobClass

WHERE jc.JobClass =''

AND dc.EquipOrLabor = @.EquipLabor

AND dc.Weekending = @.WeekEnding

IF(@.@.ROWCOUNT> 0 )

SET @.Errors = 1

UPDATE dc

SET dc.Reason ='Invalid PayCode'

FROM PManUser.DockCrewImportErrors dc

LEFTJOIN PayCodes p ON dc.Paycode = p.Paycode

WHERE p.PayCode =''

AND dc.EquipOrLabor = @.EquipLabor

AND dc.Weekending = @.WeekEnding

IF(@.@.ROWCOUNT> 0 )

SET @.Errors = 1

UPDATE dc

SET dc.Reason ='Invalid Cost Center'

FROM PManUser.DockCrewImportErrors dc

LEFTJOIN CostCenter c ON dc.CostCenter = c.CC_Num

WHERE c.CC_Num =''

AND dc.EquipOrLabor = @.EquipLabor

AND dc.Weekending = @.WeekEnding

IF(@.@.ROWCOUNT> 0 )

SET @.Errors = 1

UPDATE dc

SET dc.Reason ='Invalid Employee Number'

FROM PManUser.DockCrewImportErrors dc

LEFTJOIN Employees e ON dc.EmpNum = e.EmpNum

WHERE e.EmpNum =''

AND dc.EquipOrLabor = @.EquipLabor

AND dc.Weekending = @.WeekEnding

IF(@.@.ROWCOUNT> 0 )

SET @.Errors = 1

-End Validation Check-

IF @.Errors = 0 --If there are no errors, insert into associated Table

BEGIN

UPDATE PManUser.DockCrewImportErrors

SET Reason ='Valid Entry'

WHERE Reason =''

INSERTINTO LaborEntrys(PONum, EmpNum, WorkDate, ChrgNum, CostCenter, Hours, PayCode, Class, ChrgAmt, InDate, WeekEnding)

SELECT PONum, Empnum, WorkDate, ChargeNum, CostCenter, Hours, Paycode, Class, ChrgAmt, ImportDate, @.WeekEnding

FROM PManUser.DockCrewImportErrors

WHERE Reason ='Valid Entry'

AND EquipOrLabor ='Labor'

AND InDCMS = 1

AND BlanketNum = 0

INSERTINTO EquipEntrys(PONum, WorkDate, ChrgNum, EquipCode, CostCenter, Hours, InDate, EquipNum, WeekEnding)

SELECT PONum, WorkDate, ChargeNum, EquipCode, CostCenter, Hours, ImportDate, EquipNum, @.Weekending

FROM PManUser.DockCrewImportError

WHERE Reason ='Valid Entry'

AND EquipOrLabor ='Equipment'

AND InDCMS = 1

AND BlanketNum = 0

END

DELETEFROM PManUser.DockCrewImportErrors

WHERE Reason ='Valid Entry'AND InDCMS = 1

ALTERTABLE PManUser.DockCrewImportErrors

ENABLE TRIGGER trg_UpdateValidation

Thanks again,

Adamus

|||

I'm glad it worked out for the better -and I'm glad that I could help.

Set based operations are almost always orders of magnitude faster than using CURSORs. But it takes revising our thinking -so much of our application development experience has been centered around handling a single row of data at a time. I know it took me awhile to get to be comfortable with using Set based operations as my first line of thought.

Optimization Advice (CPU time = 15203 ms, elapsed time = 8114 ms.)!

Ok, so I have some horribly convuluted SQL that I would love to optomize. I'm not happy leaving it in it's current state, that's for sure!

I'm currently working on our test bed servers, so obviously my stats are out because of the "crap-ness" (yes, that's the technical term) of the hardware, but still, it should NEVER need to take this long!!

Basically, the issue arises in the nasty join to the career table (one employee can have multiple career lines). Just to make things complicated, employees can have any number of career records on any given date, these can even be input for future career events. The following SQL picks out the latest-current career date for each employee based on the career_date being <= GetDate() and the date of entry for this date being the greatest.

E.g.
career_date | datetime_created
2009-01-01 | 2006-05-05 13:55:21.000
2007-01-01 | 2006-05-05 13:54:18.000
2007-01-01 | 2006-05-05 13:52:55.000

From the above we want to return
2007-01-01 | 2006-05-05 13:54:18.000

SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT a.sAMAccountName As 'sAMAccountName'
, a.userPrincipalName As 'userPrincipalName'
, 'TRUE' As 'Modify'
, RTRIM(e.unique_identifier) As 'employeeID'
, RTRIM(e.employee_number) As 'employeeNumber'
, RTRIM(e.known_as)
+ CASE WHEN RTRIM(e.surname) IS NOT NULL THEN
' ' + RTRIM(e.surname) ELSE NULL END As 'displayName'
, RTRIM(e.known_as) As 'givenName'
, RTRIM(e.surname) As 'sn'
, RTRIM(c.job_title) As 'title'
, RTRIM(c.division) As 'company'
, RTRIM(c.department) As 'department'
, RTRIM(l.description) As 'physicalDeliveryOfficeName'
, RTRIM(REPLACE(am.dn,'\\','\')) As 'manager'
, t.full_mobile
+ CASE WHEN RTRIM(t.mobile_number) IS NOT NULL THEN
' (DD: ' + RTRIM(t.mobile_number) + ')' ELSE NULL END
As 'mobile'
, t.mobile_number As 'otherMobile'
, ad.address_ad_country As 'c'
, ad.address_ad_address1
+ CASE WHEN ad.address_ad_address2 IS NOT NULL THEN
', ' + ad.address_ad_address2 ELSE NULL END
+ CASE WHEN ad.address_ad_address3 IS NOT NULL THEN
', ' + ad.address_ad_address3 ELSE NULL END
+ CASE WHEN ad.address_ad_address4 IS NOT NULL THEN
', ' + ad.address_ad_address4 ELSE NULL END
+ CASE WHEN ad.address_ad_address5 IS NOT NULL THEN
', ' + ad.address_ad_address5 ELSE NULL END As 'streetAddress'
, ad.address_ad_pobox As 'postOfficeBox'
, ad.address_ad_city As 'l'
, ad.address_ad_County As 'st'
, ad.address_ad_postcode As 'postalCode'
, RTRIM(ad.address_ad_telephone) +
CASE WHEN RTRIM(a.othertelephone) IS NOT NULL
AND RTRIM(ad.address_ad_telephone) IS NOT NULL THEN
' (Ext: ' + RTRIM(a.othertelephone) + ')'
ELSE
CASE WHEN RTRIM(a.othertelephone) IS NOT NULL
AND RTRIM(ad.address_ad_telephone) IS NULL THEN
'Ext: ' + RTRIM(a.othertelephone)
ELSE NULL
END
END As 'telephoneNumber'
FROM employee e
LEFT
JOIN career c
ON c.parent_identifier = e.unique_identifier
AND c.career_date =(
SELECT max(c2.career_date)
FROM pwa_master.career c2
WHERE c2.parent_identifier = c.parent_identifier
AND c2.career_date <= GetDate()
)
AND c.datetime_created =(
SELECT max(c3.datetime_created)
FROM pwa_master.career c3
WHERE c3.parent_identifier = c.parent_identifier
AND c3.career_date = c.career_date
)
LEFT
OUTER
JOIN AD_Import am
ON am.employeeNumber = c.manager_number
INNER
JOIN AD_Import a
ON a.employeeID = e.unique_identifier
LEFT
JOIN AD_Telephone t
ON t.unique_identifier = e.unique_identifier
LEFT
JOIN AD_Address ad
ON ad.address_pwa_location = e.location
LEFT
JOIN xlocat l
ON l.code = c.location
WHERE (a.employeeNumber IS NOT NULL
OR a.employeeID IS NOT NULL)

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

(1706 row(s) affected)

Table 'AD_Import'. Scan count 4, logical reads 106, physical reads 0, read-ahead reads 0.
Table 'AD_Address'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
Table 'AD_Telephone'. Scan count 2, logical reads 10, physical reads 0, read-ahead reads 0.
Table 'Worktable'. Scan count 868, logical reads 956, physical reads 0, read-ahead reads 0.
Table 'xlocat'. Scan count 2, logical reads 8, physical reads 0, read-ahead reads 0.
Table 'career'. Scan count 5088, logical reads 2564843, physical reads 0, read-ahead reads 0.
Table 'people'. Scan count 1697, logical reads 5253, physical reads 0, read-ahead reads 0.
Table 'Worktable'. Scan count 826, logical reads 914, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:
CPU time = 15203 ms, elapsed time = 8114 ms.

Any advice on what I can do to optomize?

Oh judt to point out that "employee" is a view on the "Table 'people'."
EDIT: I know it's pointing out the obvious, but I'm pulling out the managers "DN" from AD_Import based on the manager_number and employeeNumber matching.Use a derived table rather thsan corrolate on two columns (that means double the scans...)
--.........
FROM employee e
LEFT OUTER
JOIN--"last" career record per person
(SELECT--Various columns from career
FROM career
INNER JOIN
(SELECT parent_identifier
,max(career_date)
,max(datetime_created)
FROM pwa_master.career
WHERE career_date <=GetDate())AS last_career_record
ON last_career_record.parent_identifier = career.parent_identifier)AS last_career_record
ON last_career_record.parent_identifier = e.unique_identifier
--.........
Formatting has not transferred as usual.|||Also it is spelled optimise. I plan to write wrappers for all my .NET objects to correct the spelling of words like colour.|||Also it is spelled optimise. I plan to write wrappers for all my .NET objects to correct the spelling of words like colour.
Apologies - I was just spelling it as I says it (which is no excuse ;))!

I'll go have a toy with your suggested code on the test bed now and let you know how I get on later.

Appreciate it Poots, thanks!|||Also -
WHERE (a.employeeNumber IS NOT NULL
OR a.employeeID IS NOT NULL)can be
WHERE a.employeeID IS NOT NULLI doubt the engine would get caught out but the inner join will sort out null employee numbers. You could try both types and see if the stats differ. I would guess they won't.|||That's one freaky JOIN...is it even a theta join?

Can you explain the business reason for it?|||That's one freaky JOIN...is it even a theta join?

Can you explain the business reason for it?Nope - cause it is not an inequality join.

Am I missing something? It is simply getting the "last" record per person from career.|||The current record per person from career.
So the top record whose date is <= GetDate().
But there is more criteria, because a person can have more than one record for that day, you have to pick up the top one of those based on the datetime_created.
Confusing as hell, eh?

I told my manager that I had the thing working but I was not happy to put it on the production box (I don't care if it only runs 3 times a week) because it was so, well, crap!

He responded with "How long does it take? ONLY 15 seconds!? Ha, leave it George and go work on the next bit!"

:mad:

This is the guy that told me that I have what it takes to become a DBA...|||As an old manager once told me "good enough is good enough". A culminative run time of 45 seconds per week is nothing and I think I would agree with him. Annoying if you are a perfectionist of course but I do not bear that particular burdon :)|||The current record per person from career.
So the top record whose date is <= GetDate().
But there is more criteria, because a person can have more than one record for that day, you have to pick up the top one of those based on the datetime_created.
Confusing as hell, eh?Actually that is very like the stuff I work with most of my day. We're rewriting legacy procedural code acting on highly temporal data in hierarchical databases. As such "get the 'last' this" and "return the 'first' that" is very second nature to me ATM.|||As an old manager once told me "good enough is good enough". A culminative run time of 45 seconds per week is nothing and I think I would agree with him. Annoying if you are a perfectionist of course but I do not bear that particular burdon :)

is it spelled "burdon" in the UK? over here we spell it burden. or maybe you meant "burbon"

:)|||Is "burbon" anything like Bourbon?|||Bourbon in the UK is a biscuit ;)

Alas, I do have a perfectionist streak (it was a "weakness" I named in my original interview - it went down well!)... I'm not happy leaving it with such a "long" running time, but he is right - it works, produces the right results and only has to run 3 times a week overnight...

Thanks for the help again Poots.|||Using SQL Server 2005? Then make use of ROW_NUMBER() function. It is more efficient than "derived table with max" thingy.|||I'm using 6.5 ;)
Not even a TOP for me!|||I'm using 6.5 ;)
Not even a TOP for me!You so need to sort that out. I know people on other boards that are mocked for using 7.0.

I didn't know the OVER() clause was better than MAX() but I do know Peter so I will believe it.

And no it is spelt burden here too :rolleyes:|||On large datasets windowed functions are faster and more efficient.
On smaller datasets they are sometimes equal in speed and sometimes the MAX thingy is faster for small datasets!|||SELECT a.sAMAccountName As 'sAMAccountName',
a.userPrincipalName As 'userPrincipalName',
'TRUE' As 'Modify',
RTRIM(e.unique_identifier) As 'employeeID',
RTRIM(e.employee_number) As 'employeeNumber',
RTRIM(e.known_as)
+ CASE
WHEN RTRIM(e.surname) IS NULL THEN ''
ELSE ' ' + RTRIM(e.surname)
END As 'displayName',
RTRIM(e.known_as) As 'givenName',
RTRIM(e.surname) As 'sn',
RTRIM(c.job_title) As 'title',
RTRIM(c.division) As 'company',
RTRIM(c.department) As 'department',
RTRIM(l.description) As 'physicalDeliveryOfficeName',
RTRIM(REPLACE(am.dn, '\\', '\')) As 'manager',
t.full_mobile
+ CASE
WHEN RTRIM(t.mobile_number) IS NULL THEN ''
ELSE ' (DD: ' + RTRIM(t.mobile_number) + ')'
END As 'mobile',
t.mobile_number As 'otherMobile',
ad.address_ad_country As 'c',
ad.address_ad_address1
+ CASE
WHEN ad.address_ad_address2 IS NULL THEN ''
ELSE ', ' + ad.address_ad_address2
END
+ CASE
WHEN ad.address_ad_address3 IS NULL THEN ''
ELSE ', ' + ad.address_ad_address3
END
+ CASE
WHEN ad.address_ad_address4 IS NULL THEN ''
ELSE ', ' + ad.address_ad_address4
END
+ CASE
WHEN ad.address_ad_address5 IS NULL THEN ''
ELSE ', ' + ad.address_ad_address5
END As 'streetAddress',
ad.address_ad_pobox As 'postOfficeBox',
ad.address_ad_city As 'l',
ad.address_ad_County As 'st',
ad.address_ad_postcode As 'postalCode',
RTRIM(ad.address_ad_telephone)
+ CASE
WHEN RTRIM(a.othertelephone) IS NOT NULL AND RTRIM(ad.address_ad_telephone) IS NOT NULL THEN ' (Ext: ' + RTRIM(a.othertelephone) + ')'
WHEN RTRIM(a.othertelephone)IS NOT NULL AND RTRIM(ad.address_ad_telephone) IS NULL THEN 'Ext: ' + RTRIM(a.othertelephone)
ELSE ''
END As 'telephoneNumber'
FROM employee e
INNER JOIN AD_Import a ON a.employeeID = e.unique_identifier
LEFT JOIN AD_Telephone t ON t.unique_identifier = e.unique_identifier
LEFT JOIN AD_Address ad ON ad.address_pwa_location = e.location
LEFT JOIN career c ON c.parent_identifier = e.unique_identifier
AND CONVERT(CHAR(19), career_date, 120) + CONVERT(CHAR(19), datetime_created, 120) = (
SELECT c2.parent_identifier,
MAX(CONVERT(CHAR(19), c2.career_date, 120) + CONVERT(CHAR(19), c2.datetime_created, 120))
FROM pwa_master.career AS c2
WHERE c2.career_date <= GetDate()
AND c2.parent_identifier = c.parent_identifier
)
LEFT JOIN AD_Import am ON am.employeeNumber = c.manager_number
LEFT JOIN xlocat l ON l.code = c.location
WHERE a.employeeNumber IS NOT NULL|||Thanks for the attempt Peso, but unfortunately when I run it (after a wee bit of tweaking) I'm missing over 400 records.

I've decided to stick with my original, I figure that when it is put on the production server it will take less than half the time... The test servers are, how to hang an air freshener on this; crap

Mmmm, pine-fresh ;)

Thanks for your help everyone

Optimization advice

I am looking for information, books, websites, etc that will help me figure
out the following things in MSSQL and Oracle:

1. Optimizing the database configuration itself.
2. Optimal table and index design.
3. Optimizing SQL statement lookups.
4. Anything else that might help to speed up our database applications.

Any advice at all would be useful.

Thanks alot.

Andy Reynolds
ajreynolds@.san.no.spam.rr.com (remove the no.spam to send email)"Andy Reynolds" <ajreynolds@.san.removethis.rr.removethis.com> wrote in
message news:Xtt2d.7869$XW.206@.twister.socal.rr.com...
> I am looking for information, books, websites, etc that will help me
figure
> out the following things in MSSQL and Oracle:
> 1. Optimizing the database configuration itself.
> 2. Optimal table and index design.
> 3. Optimizing SQL statement lookups.
> 4. Anything else that might help to speed up our database applications.
> Any advice at all would be useful.
> Thanks alot.
> Andy Reynolds
> ajreynolds@.san.no.spam.rr.com (remove the no.spam to send email)
>
Optimize the application.
Use bind variables
Get data efficiently, use array interface.
Avoid unnecessary parsing.
write efficient queries.

If you don't do the above then the rest is useless or of little value.
Jim|||Jim Kennedy wrote:

> "Andy Reynolds" <ajreynolds@.san.removethis.rr.removethis.com> wrote in
> message news:Xtt2d.7869$XW.206@.twister.socal.rr.com...
>> I am looking for information, books, websites, etc that will help me
> figure
>> out the following things in MSSQL and Oracle:
>>
>> 1. Optimizing the database configuration itself.
>> 2. Optimal table and index design.
>> 3. Optimizing SQL statement lookups.
>> 4. Anything else that might help to speed up our database applications.
>>
>> Any advice at all would be useful.
>>
>> Thanks alot.
>>
>> Andy Reynolds
>> ajreynolds@.san.no.spam.rr.com (remove the no.spam to send email)
>>
>>
> Optimize the application.
> Use bind variables
> Get data efficiently, use array interface.
> Avoid unnecessary parsing.
> write efficient queries.
> If you don't do the above then the rest is useless or of little value.
> Jim

Your list being in context of Oracle. Supporting your list ...

... as described in Jonathan Lewis' "Practical Oracle8i" (and valuable even
now with 10g), Thomas Kyte's "Effective Oracle by Design", other books by
OakTable members (http://www.oaktable.org), and in the Oracle documents
such as "Application Developer's Guide - Fundamentals" at
http://docs.oracle.com

And adding to your list ...

Don't use DDL (creates, drops, alters) at run time
Learn Oracle's Global Temp Tables before diving into the Temp Table mill
Don't commit in loops
Don't use procedures where set operations will do
Do use stored procedures
Don't reinvent the wheel. Consider using Oracle's built-in
- workflow
- message queueing
- geospatial (Locator), Document (Text), multimedia (Intermedia)
- security (Row Level Security & Policy-based statement re-write)
- auditing
- direct http interaction (frequently replaces ASP, Perl)
- direct file, tcp interaction
- job scheduler
- external procedures
- external tables
- and so on
Don't use same development techniques for Oracle and MS SqlServer
- due to different locking internals, different techniques MUST be used!

(BTW - the comp.database.oracle.* heirarchy and the group
comp.databases.oracle are rogue - please help bring the lost souls back to
comp.databases.oracle.* per the charters copied at http://orafaq.com)

/Hans|||Andy Reynolds wrote:
> I am looking for information, books, websites, etc that will help me figure
> out the following things in MSSQL and Oracle:
> 1. Optimizing the database configuration itself.
> 2. Optimal table and index design.
> 3. Optimizing SQL statement lookups.
> 4. Anything else that might help to speed up our database applications.
> Any advice at all would be useful.
> Thanks alot.
> Andy Reynolds
> ajreynolds@.san.no.spam.rr.com (remove the no.spam to send email)
>
Try http://www.oracle.com/technology//index.html for info on Oracle.
Registration is free.

Optimization

We're building a company wide network monitoring system
in Java, and need some advice on the database design and
tuning.

The application will need to concurrently INSERT,
DELETE, and SELECT from our EVENT table as efficiently as
possible. We plan to implement an INSERT thread, a DELETE
thread, and a SELECT thread within our Java program.

The EVENT table will have several hundred million records
in it at any given time. We will prune, using DELETE, about
every five seconds to keep the active record set down to
a user controlled size. And one of the three queries will
be executed about every twenty seconds. Finally, we'll
INSERT as fast as we can in the INSERT thread.

Being new to MSSQL, we need advice on

1) Server Tuning - Memory allocations, etc.
2) Table Tuning - Field types
3) Index Tuning - Are the indexes right
4) Query Tuning - Hints, etc.
5) Process Tuning - Better ways to INSERT and DELETE, etc.

Thanks, in advance, for any suggestions you can make :-)

The table is

// CREATE TABLE EVENT (
// ID INT PRIMARY KEY NOT NULL,
// IPSOURCE INT NOT NULL,
// IPDEST INT NOT NULL,
// UNIXTIME BIGINT NOT NULL,
// TYPE TINYINT NOT NULL,
// DEVICEID SMALLINT NOT NULL,
// PROTOCOL TINYINT NOT NULL
// )
//
// CREATE INDEX INDEX_SRC_DEST_TYPE
// ON EVENT (
// IPSOURCE,IPDEST,TYPE
// )

The SELECTS are

private static String QueryString1 =
"SELECT ID,IPSOURCE,IPDEST,TYPE "+
"FROM EVENT "+
"WHERE ID >= ? "+
" AND ID <= ?";

private static String QueryString2 =
"SELECT COUNT(*),IPSOURCE "+
"FROM EVENT "+
"GROUP BY IPSOURCE "+
"ORDER BY 1 DESC";

private static String QueryString3 =
"SELECT COUNT(*),IPDEST "+
"FROM EVENT "+
"WHERE IPSOURCE = ? "+
" AND TYPE = ? "+
"GROUP BY IPDEST "+
"ORDER BY 1 DESC";

The DELETE is

private static String DeleteIDString =
"DELETE FROM EVENT "+
"WHERE ID < ?";Hi,

Refer www.SQL-Server-Performance.com web site for SQL Server Performance issues.
Almost all yours queries id answered in perfect articles and example wise.

Thanks, Amit

pwhittington@.nitrodata.com (rocky) wrote in message news:<3961374c.0307041440.4ebb73ef@.posting.google.com>...
> We're building a company wide network monitoring system
> in Java, and need some advice on the database design and
> tuning.
> The application will need to concurrently INSERT,
> DELETE, and SELECT from our EVENT table as efficiently as
> possible. We plan to implement an INSERT thread, a DELETE
> thread, and a SELECT thread within our Java program.
> The EVENT table will have several hundred million records
> in it at any given time. We will prune, using DELETE, about
> every five seconds to keep the active record set down to
> a user controlled size. And one of the three queries will
> be executed about every twenty seconds. Finally, we'll
> INSERT as fast as we can in the INSERT thread.
> Being new to MSSQL, we need advice on
> 1) Server Tuning - Memory allocations, etc.
> 2) Table Tuning - Field types
> 3) Index Tuning - Are the indexes right
> 4) Query Tuning - Hints, etc.
> 5) Process Tuning - Better ways to INSERT and DELETE, etc.
> Thanks, in advance, for any suggestions you can make :-)
>
> The table is
> // CREATE TABLE EVENT (
> // ID INT PRIMARY KEY NOT NULL,
> // IPSOURCE INT NOT NULL,
> // IPDEST INT NOT NULL,
> // UNIXTIME BIGINT NOT NULL,
> // TYPE TINYINT NOT NULL,
> // DEVICEID SMALLINT NOT NULL,
> // PROTOCOL TINYINT NOT NULL
> // )
> //
> // CREATE INDEX INDEX_SRC_DEST_TYPE
> // ON EVENT (
> // IPSOURCE,IPDEST,TYPE
> // )
> The SELECTS are
> private static String QueryString1 =
> "SELECT ID,IPSOURCE,IPDEST,TYPE "+
> "FROM EVENT "+
> "WHERE ID >= ? "+
> " AND ID <= ?";
> private static String QueryString2 =
> "SELECT COUNT(*),IPSOURCE "+
> "FROM EVENT "+
> "GROUP BY IPSOURCE "+
> "ORDER BY 1 DESC";
> private static String QueryString3 =
> "SELECT COUNT(*),IPDEST "+
> "FROM EVENT "+
> "WHERE IPSOURCE = ? "+
> " AND TYPE = ? "+
> "GROUP BY IPDEST "+
> "ORDER BY 1 DESC";
> The DELETE is
> private static String DeleteIDString =
> "DELETE FROM EVENT "+
> "WHERE ID < ?";|||Some mixed advice:

1) Make sure that you have plenty of memory in the server. Query2 will
always generate a full index scan, and you need to be able to fit all of the
data for that index in memory to avoid excessive disk I/O. 100 million rows
* 13 bytes = 1.3 GB of memory. I would suggest having at least 2GB memory in
the server.

2) Make sure you have a disk subsystem with good performance for both
reading and writing. Use several striped disks for the data (not RAID5), and
a separate disk for log. Use a disk controller with a battery-backed write
cache. The disk performance is important even if you have plenty of memory,
but it is absolutely vital if you can not fit all the data in memory.

3) Add a NOLOCK hint to all SELECT queries. Otherwise, all inserts will be
blocked while executing the SELECTs.

4) Change the index to IPSOURCE,TYPE,IPDEST to optimize Query3

5) Make sure that you batch inserts. Have the INSERT thread generate batches
of inserts and send them all to the server in a single batch as a single
transaction. Having a transaction for each inserted row will kill
performance unless you have a really good disk controller.

/SG

"rocky" <pwhittington@.nitrodata.com> wrote in message
news:3961374c.0307041440.4ebb73ef@.posting.google.c om...
> We're building a company wide network monitoring system
> in Java, and need some advice on the database design and
> tuning.
> The application will need to concurrently INSERT,
> DELETE, and SELECT from our EVENT table as efficiently as
> possible. We plan to implement an INSERT thread, a DELETE
> thread, and a SELECT thread within our Java program.
> The EVENT table will have several hundred million records
> in it at any given time. We will prune, using DELETE, about
> every five seconds to keep the active record set down to
> a user controlled size. And one of the three queries will
> be executed about every twenty seconds. Finally, we'll
> INSERT as fast as we can in the INSERT thread.
> Being new to MSSQL, we need advice on
> 1) Server Tuning - Memory allocations, etc.
> 2) Table Tuning - Field types
> 3) Index Tuning - Are the indexes right
> 4) Query Tuning - Hints, etc.
> 5) Process Tuning - Better ways to INSERT and DELETE, etc.
> Thanks, in advance, for any suggestions you can make :-)
>
> The table is
> // CREATE TABLE EVENT (
> // ID INT PRIMARY KEY NOT NULL,
> // IPSOURCE INT NOT NULL,
> // IPDEST INT NOT NULL,
> // UNIXTIME BIGINT NOT NULL,
> // TYPE TINYINT NOT NULL,
> // DEVICEID SMALLINT NOT NULL,
> // PROTOCOL TINYINT NOT NULL
> // )
> //
> // CREATE INDEX INDEX_SRC_DEST_TYPE
> // ON EVENT (
> // IPSOURCE,IPDEST,TYPE
> // )
> The SELECTS are
> private static String QueryString1 =
> "SELECT ID,IPSOURCE,IPDEST,TYPE "+
> "FROM EVENT "+
> "WHERE ID >= ? "+
> " AND ID <= ?";
> private static String QueryString2 =
> "SELECT COUNT(*),IPSOURCE "+
> "FROM EVENT "+
> "GROUP BY IPSOURCE "+
> "ORDER BY 1 DESC";
> private static String QueryString3 =
> "SELECT COUNT(*),IPDEST "+
> "FROM EVENT "+
> "WHERE IPSOURCE = ? "+
> " AND TYPE = ? "+
> "GROUP BY IPDEST "+
> "ORDER BY 1 DESC";
> The DELETE is
> private static String DeleteIDString =
> "DELETE FROM EVENT "+
> "WHERE ID < ?";

Optimising Query based on Views

I would be grateful for some advice.
I have a query which selects from a view which, in turn, is based on three
other view. I want to optimise the query.
The query is built dynamically so cannot be made into a stored procedure. Is
it worth my while making the main view into a user-defined function so that
I can select from it. (I understand that you can't do "SELECT * FROM
SP_MYPROC GROUP BY etc.", whereas you can do "SELECT * FROM UDF_MYFUNC GROUP
BY etc." where SP_MYPROC is a stored procedure and UDF_MYFUNC is a
user-defined function.) I am suggesting this because I have the impression
that stored procedures and user-defined functions are pre-compiled with an
execution plan by SQL server, whereas this is not possible for views.
Many thanks in advance,
Richard Cox.Yes and no. Views are totally transparent to the optimizer and are only
useful as an abstraction layer and security feature.. The optimizer looks
at the underlying tables rather than the view, EXCEPT for partitioned views
which I will conveniently ignore here.
The big advantage to a stored procedure is query plan reuse. The optimizer
figures out its 'best' plan once and reuses it until it is no longer valid
or it is aged out of cache. If you call the procedure once a day, this
won't help much. On the other hand, even stored procedures have limits.
Temporary tables and dynamic SQL are two of the biggest reasons why a stored
procedure will be recompiled.
From your description, the result set and filter conditions may change from
execution to execution so the advantage of plan reuse just doesn't apply.
You may have to construct a few samples and see if the optimizer does what
you think it should. Use the 'View Estimated Execution Plan' button in
Query Analyzer to see what SQL will do with various combinations of your
query.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
CareerBuilder.com
"Richard Cox" <rpcox@.traqs.com> wrote in message
news:en6D1q54DHA.2736@.TK2MSFTNGP09.phx.gbl...

quote:

---

> I would be grateful for some advice.
> I have a query which selects from a view which, in turn, is based on three
> other view. I want to optimise the query.
> The query is built dynamically so cannot be made into a stored procedure.

---

Is

quote:

---

> it worth my while making the main view into a user-defined function so

---

that

quote:

---

> I can select from it. (I understand that you can't do "SELECT * FROM
> SP_MYPROC GROUP BY etc.", whereas you can do "SELECT * FROM UDF_MYFUNC

---

GROUP

quote:

---

> BY etc." where SP_MYPROC is a stored procedure and UDF_MYFUNC is a
> user-defined function.) I am suggesting this because I have the impression
> that stored procedures and user-defined functions are pre-compiled with an
> execution plan by SQL server, whereas this is not possible for views.
> Many thanks in advance,
> Richard Cox.
>
>

---

|||Thanks very much for your explanation, Geoff. Looks like there is nothing
much to be gained in this case then.
Richard.|||Richard
Query Optimyzer does not produce query plan for views. On other hand when
you create clustered index on view it is materialized and store in the same
way as store clusetred index created on the table. I have seen queries that
after adding clustered index have ran more faster.
"Richard Cox" <rpcox@.traqs.com> wrote in message
news:e8IESL$4DHA.1852@.TK2MSFTNGP10.phx.gbl...

quote:

---

> Thanks very much for your explanation, Geoff. Looks like there is nothing
> much to be gained in this case then.
> Richard.
>

---

Optimising Query based on Views

I would be grateful for some advice.
I have a query which selects from a view which, in turn, is based on three
other view. I want to optimise the query.
The query is built dynamically so cannot be made into a stored procedure. Is
it worth my while making the main view into a user-defined function so that
I can select from it. (I understand that you can't do "SELECT * FROM
SP_MYPROC GROUP BY etc.", whereas you can do "SELECT * FROM UDF_MYFUNC GROUP
BY etc." where SP_MYPROC is a stored procedure and UDF_MYFUNC is a
user-defined function.) I am suggesting this because I have the impression
that stored procedures and user-defined functions are pre-compiled with an
execution plan by SQL server, whereas this is not possible for views.
Many thanks in advance,
Richard Cox.Yes and no. Views are totally transparent to the optimizer and are only
useful as an abstraction layer and security feature.. The optimizer looks
at the underlying tables rather than the view, EXCEPT for partitioned views
which I will conveniently ignore here.
The big advantage to a stored procedure is query plan reuse. The optimizer
figures out its 'best' plan once and reuses it until it is no longer valid
or it is aged out of cache. If you call the procedure once a day, this
won't help much. On the other hand, even stored procedures have limits.
Temporary tables and dynamic SQL are two of the biggest reasons why a stored
procedure will be recompiled.
From your description, the result set and filter conditions may change from
execution to execution so the advantage of plan reuse just doesn't apply.
You may have to construct a few samples and see if the optimizer does what
you think it should. Use the 'View Estimated Execution Plan' button in
Query Analyzer to see what SQL will do with various combinations of your
query.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
CareerBuilder.com
"Richard Cox" <rpcox@.traqs.com> wrote in message
news:en6D1q54DHA.2736@.TK2MSFTNGP09.phx.gbl...
> I would be grateful for some advice.
> I have a query which selects from a view which, in turn, is based on three
> other view. I want to optimise the query.
> The query is built dynamically so cannot be made into a stored procedure.
Is
> it worth my while making the main view into a user-defined function so
that
> I can select from it. (I understand that you can't do "SELECT * FROM
> SP_MYPROC GROUP BY etc.", whereas you can do "SELECT * FROM UDF_MYFUNC
GROUP
> BY etc." where SP_MYPROC is a stored procedure and UDF_MYFUNC is a
> user-defined function.) I am suggesting this because I have the impression
> that stored procedures and user-defined functions are pre-compiled with an
> execution plan by SQL server, whereas this is not possible for views.
> Many thanks in advance,
> Richard Cox.
>
>|||Thanks very much for your explanation, Geoff. Looks like there is nothing
much to be gained in this case then.
Richard.|||Richard
Query Optimyzer does not produce query plan for views. On other hand when
you create clustered index on view it is materialized and store in the same
way as store clusetred index created on the table. I have seen queries that
after adding clustered index have ran more faster.
"Richard Cox" <rpcox@.traqs.com> wrote in message
news:e8IESL$4DHA.1852@.TK2MSFTNGP10.phx.gbl...
> Thanks very much for your explanation, Geoff. Looks like there is nothing
> much to be gained in this case then.
> Richard.
>