Timeout-Expired error in VB with SQL Server

seakay81
04-20-2004, 01:54 AM
Hi!
I have been facing the following problem:
My application times out when it tries to run the following lines:

sSql = "select isnull(max(de_no),0) from dis_temp where de_no like '" & Trim(DENO) & "%'"
Set adoResDisTemp = New ADODB.Recordset
adoResDisTemp.Open sSql, adoCn

This used to run earlier without any problem. THhs problem has developed suddenly. The table dis_temp is a very large table and has a clustered primary index on de_no. Sql server version 7 is being used. The code was done on vb6. It has been in use for the past 4 years. I have tried running DBCC on the index.

I am not able to resolve this. I would be very thankful to you if you could help me.
seakay

PWNettle
04-20-2004, 02:59 AM
It'd seem that even on a large table a MAX should be reasonably quick.

You could try increasing the CommandTimeout property of your connection object and see if that helps.

From MSDN on CommandTimeout:
Sets or returns a Long value that indicates, in seconds, how long to wait for a command to execute. Default is 30.

Perhaps 30 seconds isn't enough for your query.

Good luck,
Paul

seakay81
04-20-2004, 03:38 AM
It'd seem that even on a large table a MAX should be reasonably quick.

You could try increasing the CommandTimeout property of your connection object and see if that helps.

From MSDN on CommandTimeout:


Perhaps 30 seconds isn't enough for your query.

Good luck,
Paul

Thanks Paul for the reply. The connection query Timeout in the sql server is 0 which means infinite isn't it. So will this not be taken into account, if no other definition has been given in the code?

Just one more doubt..I have placed all indexes in a separate filegroup on a different drive recently. Could this affect the performance?
Thanks,
Seakay

Mandelbrot
04-20-2004, 04:17 AM
Can the timeout be set to 0? I know this works for Access, and gives infinite time-out, but I don't know about SQL Server.


Paul.

TheDutch IceMan
04-20-2004, 04:22 AM
I think it has indeed something to do with the indexes on the other drive.

Does version 7 has the Index Tuning Wizard? With that you can try to find out where your problem could be resolved....as long as it's in the indexes ;)

As you told us before, i think it has nothing to do with the timeout settings. You've worked with it for 4 years without any problems. Nothing has changed on the network settings or how is the network/situation created?

seakay81
04-20-2004, 05:29 AM
I think it has indeed something to do with the indexes on the other drive.

Does version 7 has the Index Tuning Wizard? With that you can try to find out where your problem could be resolved....as long as it's in the indexes ;)

As you told us before, i think it has nothing to do with the timeout settings. You've worked with it for 4 years without any problems. Nothing has changed on the network settings or how is the network/situation created?

Yes Paul..We can set query timeout to 0 in SQL Server, in the Enterprise manager.
Thanks Richard for the reply. Here the criteria being specified is on the primary key. So I am assuming that there should not be a problem in the index. However maybe the Index Tuning Wizard could be run as per your suggestion.
Actually I shifted the indexes to a separate drive so that the performance gets enhanced, under the assumption that when data is being picked up from one drive, the index can be simulataneously be picked up from the other drive. This used to be true with Oracle with which I had worked earlier. Is it not so with SQL Server?
Thanks,
Seakay

seakay81
04-20-2004, 05:35 AM
Addenum to the earlier reply to asnwer your question Richard,...Network settings were not changed. The system was working okay after shifting the indexes for sometime. However there was a massive increase in the data suddenly. I am still exploring what the other causes could be as the sytem is in the client's location where I visit only occasionally.
Seakay

Mandelbrot
04-20-2004, 05:41 AM
Could it be a simple case of the Index has been corrupted? (I'm speaking here as a cheesed off user of Access, so you can slap me down if that's not possible with SQL Server! :p). Alternatively, have you tried dropping and rebuilding the index as opposed to simply moving it?

seakay81
04-20-2004, 05:53 AM
Ofcourse it is possible for the index to get corrupted in SQL Server :). Thats why I ran DBCC command to check its consistency of the index. Maybe I could try your suggestion of dropping and rebuilding the index. Thanks for the reply.

TheDutch IceMan
04-20-2004, 06:21 AM
Addenum to the earlier reply to asnwer your question Richard,...Network settings were not changed. The system was working okay after shifting the indexes for sometime. However there was a massive increase in the data suddenly. I am still exploring what the other causes could be as the sytem is in the client's location where I visit only occasionally.
Seakay

for more information on the indexes and slow performance of queries...take a look at this link from microsoft (http://support.microsoft.com/default.aspx?scid=kb;EN-US;243589) or this link here (http://www.sql-server-performance.com/best_sql_server_performance_tips.asp) or something from sql-server-performance.com here..... (http://www.sql-server-performance.com/sql_server_performance_audit7.asp)

I hope you can do something with those links and information!

seakay81
04-20-2004, 12:00 PM
for more information on the indexes and slow performance of queries...take a look at this link from microsoft (http://support.microsoft.com/default.aspx?scid=kb;EN-US;243589) or this link here (http://www.sql-server-performance.com/best_sql_server_performance_tips.asp) or something from sql-server-performance.com here..... (http://www.sql-server-performance.com/sql_server_performance_audit7.asp)

I hope you can do something with those links and information!

Thanks Richard, for the info.
Regards,
Seakay

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum