Adam
2007-05-24 09:26:02 UTC
i'm working on a database(sql server 2005) which collect a lot of data
everyday and process this data everynight (so that it won't affect
daily querys).
recently i wrote a stored procedure and add it into the nightly batch
processing to get a summary of some data, and another procedure to
query these summary data. i also made a excel documents calling the
query with vba so that it can got these data and put them into a
chart. this all goes perfect at first, but after some time, a strange
problems appears: the excel runs very slow at sometime, the query runs
very slow; but when i try to run the same procedure with exactly the
same parameters in sql server management studio, the result comes out
in very short time(less than 10 sec). and this problem not happens
everyday, sometime it happens and when i run the same procedure with
the same parameter from excel vba in the next day, the result goes out
in seconds. i can't find out anything may cause this problem, so i
come here for help
following is a piece of my vba script and i have made tests to ensure
it do trigger the problem:
'-----code begin----
Sub test()
Set conn = CreateObject("ADODB.Connection")
conn.CommandTimeout = 300
conn.Open "Provider=SQLOLEDB;
Server=aaa.bbb.ccc.ddd;Database=dbname;User Id=uid;Password=pwd;"
Set rs = CreateObject("ADODB.RecordSet")
Sql = "exec procedure_name param1, param2"
rs.Open Sql, conn '<<< it halts at here
sometime..
ActiveSheet.Range("A1").CopyFromRecordset rs
rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing
End Sub
'----code end----
the results comes out in seconds whenever i call the same procedure
with same parameters from sql server management studio..
AdamH
everyday and process this data everynight (so that it won't affect
daily querys).
recently i wrote a stored procedure and add it into the nightly batch
processing to get a summary of some data, and another procedure to
query these summary data. i also made a excel documents calling the
query with vba so that it can got these data and put them into a
chart. this all goes perfect at first, but after some time, a strange
problems appears: the excel runs very slow at sometime, the query runs
very slow; but when i try to run the same procedure with exactly the
same parameters in sql server management studio, the result comes out
in very short time(less than 10 sec). and this problem not happens
everyday, sometime it happens and when i run the same procedure with
the same parameter from excel vba in the next day, the result goes out
in seconds. i can't find out anything may cause this problem, so i
come here for help
following is a piece of my vba script and i have made tests to ensure
it do trigger the problem:
'-----code begin----
Sub test()
Set conn = CreateObject("ADODB.Connection")
conn.CommandTimeout = 300
conn.Open "Provider=SQLOLEDB;
Server=aaa.bbb.ccc.ddd;Database=dbname;User Id=uid;Password=pwd;"
Set rs = CreateObject("ADODB.RecordSet")
Sql = "exec procedure_name param1, param2"
rs.Open Sql, conn '<<< it halts at here
sometime..
ActiveSheet.Range("A1").CopyFromRecordset rs
rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing
End Sub
'----code end----
the results comes out in seconds whenever i call the same procedure
with same parameters from sql server management studio..
AdamH