Creating a more efficient query

[Chopstik]

I have a query that is now taking several minutes to run and, as a result, is timing out when attempting to run in ASP. It is deleting records in two related tables (parent-child relationship) which currently contain about 30K (parent) and 60K (child) records. It was ok until the last several months with the addition of new records, but I am not expert enough at SQL to see a way to make this query more efficient (and thus shorter running) at the moment. Anyone have any thoughts? Thanks!

CREATE PROCEDURE dbo.DEL_CK_RUN @thisDate datetime
AS
BEGIN TRANSACTION
DELETE T_PAY_DTL FROM T_PAY_DTL PD
JOIN T_PAY P ON PD.pay_id = P.pay_id
WHERE (CONVERT(char(10),P.creat_dt,112) =
CONVERT(char(10),CONVERT(datetime,@thisDate),112)) AND
P.tnnt_id IS NULL
IF @@ERROR <> 0 BEGIN RAISERROR
('There is a problem with T_PAY_DTL delete',16,1)
WITH NOWAIT
ROLLBACK TRAN
END
ELSE BEGIN
DELETE T_PAY
WHERE (CONVERT(char(10),creat_dt,112) =
CONVERT(char(10),CONVERT(datetime,@thisDate),112)) AND
tnnt_id IS NULL
IF @@ERROR <> 0 BEGIN RAISERROR
('There is a problem with T_PAY delete',16,1)
WITH NOWAIT
ROLLBACK TRAN
END
END
COMMIT TRAN

------------------------------------------------------------------------------------------------------------------------
"I am not young enough to know everything."
Oscar Wilde (1854-1900)

 

[cfw2]

are the fields you are using in the criteria indexed?
( T_PAY.creat_dt, t_pay.tnnt_id )
if they are not, indexing them should help speed things up for you.

 

[JamesLean]

The only obvious thing that might help is to get rid of the function on the date field filter to enable any index on that field to be used.

I'm assuming that you will pass in a datetime value with a zero (00:00:00) time portion and you want to delete all records for that entire day? Try this and see if it helps:

CREATE PROCEDURE dbo.DEL_CK_RUN
	@thisDate datetime
AS

BEGIN TRANSACTION

DELETE T_PAY_DTL
FROM T_PAY_DTL PD
	JOIN T_PAY P ON PD.pay_id = P.pay_id
WHERE P.creat_dt >= @thisDate
	AND P.creat_dt < @thisDate + 1
	AND P.tnnt_id IS NULL

IF @@ERROR <> 0
BEGIN
	ROLLBACK TRAN
	RAISERROR('There is a problem with T_PAY_DTL delete', 16, 1) WITH NOWAIT
	RETURN 1
END

DELETE T_PAY
WHERE creat_dt >= @thisDate
	AND creat_dt < @thisDate + 1
	AND tnnt_id IS NULL

IF @@ERROR <> 0
BEGIN
	ROLLBACK TRAN
	RAISERROR('There is a problem with T_PAY delete', 16, 1) WITH NOWAIT
	RETURN 1
END

COMMIT TRAN
RETURN 0
GO

--James

 

[Chopstik]

Thanks for the responses. There are no indexes on either of those two fields, but I'll speak to our DBA about them and see what can be done.

I will try JamesLean's suggestion and see what happens. One other thought that was just suggested to me offline was to create a cursor and see if that shortens things down. I've something of an aversion to cursors as a general rule, but I think it may be worth a try just to see if it alleviates the problem. Thoughts are welcome.

Thanks again.

------------------------------------------------------------------------------------------------------------------------
"I am not young enough to know everything."
Oscar Wilde (1854-1900)

 

[JamesLean]

I can pretty much guarantee that a cursor will not improve the performance of this query! Adding indexes may well improve things though - I would try that first of all.

--James

 

[Chopstik]

Thank you, James. That was my first plan of action. Though it may be a while, I'll try to post back with some results once I can complete some testing. Thanks!

------------------------------------------------------------------------------------------------------------------------
"I am not young enough to know everything."
Oscar Wilde (1854-1900)