September 6, 2009 at 8:49 pm
Hi All,
I use VBscript to run my script in Active directory daily and i receive this.
Set oConn = CreateObject("ADODB.Connection")
oConn.ConnectionTimeout = 3600
oConn.Open "Provider=SQLOLEDB; Server=[My server IP];
Database=[My database]; Uid=[My user id] ; Pwd=[My password]
oConn.Execute "DELETE FROM Table1 WHERE CONVERT(varchar(10), Dt_update, 101) = '" & strServerDate2 & "' and Servercode ='" & strCom & "' "
After this i receive Time out expired.
Can anyone help me on this?
September 6, 2009 at 10:33 pm
Well...
First, you might wanna try and replace the 'DELETE' WITH 'SELECT COUNT(*)' so you know how many records are going to be affected.
Second, you set the connection timeout but that's only for the connection itself. You shuold set the execution time itself to a higher number to allow for the query to run completely.
Do not forget that this is a logged option probably, so server saves rollback information which slows your action under heavy delete operations.
If you post the total count I might help you in some way.
September 6, 2009 at 11:09 pm
Hi Benyos,
Thanks for your reply, the first part i agree with that now how about the Second suggestion you mention about the "execution time to itsef and set to higher number" ? How can i possibly set that ?
September 6, 2009 at 11:28 pm
Norman-Jet.O-Calub (9/6/2009)
oConn.Execute "DELETE FROM Table1 WHERE CONVERT(varchar(10), Dt_update, 101) = '" & strServerDate2 & "' and Servercode ='" & strCom & "' "After this i receive Time out expired.
Expressing the query this way means the SQL Server has to* perform the CONVERT (presumably to remove the time component) on every row in the table in order to compare the result with strServerDate2. If a useful index exists on Dt_Update (and preferably ServerCode), the following will perform many, many times faster, and your time out problem should go away:
-- Pseudo-code
DELETE dbo.Table1 WHERE dt_update >= {strServerDate2 midnight} AND dt_update = '2009-09-02 00:00:00.000' -- Note greater than or equal
AND dt_update < '2009-09-03 00:00:00.000'; -- Less than
Paul
* unless an indexed computed column exists to match the function call - but let's keep it simple 🙂
September 7, 2009 at 4:43 am
locking may be the problem use fast firs row to solve
(delete from tb with (fastfirstrow) where ....)
if run this command is critical, set commandTimeOut = 0 (unlimit)
September 7, 2009 at 11:38 am
Norman-Jet.O-Calub (9/6/2009)
Hi Benyos,Thanks for your reply, the first part i agree with that now how about the Second suggestion you mention about the "execution time to itsef and set to higher number" ? How can i possibly set that ?
Set the following:
oConn.CommandTimeout = 120
That's in seconds
September 7, 2009 at 7:49 pm
Hi All,
Thanks for all your advice and i will perform it by phases
For Phase 1
=====================================================
Set oConn = CreateObject("ADODB.Connection")
Set oRs3 = CreateObject("ADODB.Recordset")
If oRs3.state =1 then oRs3.close
oRs3.CursorLocation = adUseClient
oRs3.open "Select * From Table1 where CONVERT(varchar(10),
Dt_update, 101) = '" & strServerDate2 & "' and Servercode ='" &
strCom & "' ",oConn,1,3
wscript.echo "Count Record " & oRs3.RecordCount
oConn.Execute "DELETE FROM Table1 WHERE
CONVERT(varchar(10), Dt_update, 101) = '" & strServerDate2
& "' and Servercode ='" & strCom & "' ",oRs3.Recordcount
So far the result today is extremely good and
i didn't receive any time out expired problem.
September 7, 2009 at 8:57 pm
OK, now we'll step into the optimization phase:
Initially, you had 2 problems: Delete action took too long + command action allowed only short term command to run. Some tried to help you with the first problem. Some tried the other.
Now that your command timeout is solved, let's look at the other problem:The sole purpose of asking you what the count is was to try and suggest alternative ways of deletion, like Deleting in batches, dropping indexes temporarily, adding 'deleted' status column to the record, and planty more. The count was intended for us to help you.
Since you didn't post it here, I'm still unable to suggest anything that will outperform plain 'DELETE FROM WHERE'.
However, two things for better coding:
1. If you want to get the count for your own logging purposes, you should replace your first code block with somehting like this:
oRs3.open "Select Count(*) as CountOfRecords From Table1 where CONVERT(varchar(10),
Dt_update, 101) = '" & strServerDate2 & "' and Servercode ='" &
strCom & "' ",oConn,1,3
wscript.echo "Count Record " & oRs3("CountOfRecords").value
This will prevent your SQL server from running this expensive query just to return one number
2. You should be able to get this number anyway after your deletion operation through records affected mechanism and without using the first "count" block. If it returns -1 as the count, try the folloing:
oRs3.open "SET NOCOUNT ON; DELETE FROM From Table1 where CONVERT(varchar(10),
Dt_update, 101) = '" & strServerDate2 & "' and Servercode ='" & strCom & "'; SELECT @@ROWCOUNT AS CountOfRecords",oConn,1,3
wscript.echo "Count Record " & oRs3.NextRecordset.Fields("CountOfRecords").value
And finally, a better approach would be to write your own stored procedure that does all that...
September 7, 2009 at 10:25 pm
This really needs to be in a stored procedure, and you should convert the query to a SARGable form.
See my previous post.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply