VB6 to .NET conversion and CommandTimeout

[MMock]

I am working on a conversion project moving a VB6 application to .NET. Some of this conversion was begun previously by another consultant no longer here.

Everything went fine in test. When we moved to a production environment and started querying a large database, we got a query timeout.

I did some digging and I found in VB6 when the ADODB.Connection object was instantiated, its CommandTimeout property was set to 0. So every single query the application runs can take however long it wants. This timeout was not converted to .NET so every query is limited to the default of 30 seconds.

In .NET we are using an OleDbConnection which doesn't have a CommandTimeout property. I don't want to put a CommandTimeout on every single command object before I run a query - that would be a long coding change (but of course doable if that's the best solution). This website says you can put a command timeout in your connectionstring, so I am wondering if that one change would be the best place.

Thanks.

(And you will probably say further analysis should be done to determine only the queries that need an exceedingly long time to run, but because we are converting a "working" VB6 application I don't think I can justify the time to do that analysis. If it works it VB6 with an infinite timeout, it will work in .NET with an infinite timeout.).

[GaryMazzone]

My bigger question is why is it taking that long in the first place. I have queryied large tables and got back results in less then a second. Does the query itself need tuning? Are there missing indexes? Where is the time being spent, CPU issue? Memory issue? I/O? I would look at that first

[MMock]

Right, but like I said it works and no one cares about that at this point. Sorry, but that decision was made long before I got here.

[techgnome]

I did a quick google search on "connectionstring command timeout" ... got back a lot of results. Mostly of people asking how to do it, and the replies being one of two kinds: 1) It can't be done. 2) Suggesting setting the CommandTimeout on the command object. some suggested that adding "Command Timeout = XXX" to the connection string should work, but it couldn't be confirmed.

-tg

side note - if the vb6 app is working, and it sounds like they don't want to put any effort into a proper rewrite, why bother in the first place?

[MMock]

I guess I should've read the fine print. The site I cited in post #1 is for MySql, now that I look at it more closely. That's what I wanted, confirmation. Or at least to know there is a lack of it. So thanks.

[abhijit]

I agree with Mazz. I believe you should get the DBA to check why the application doesn't perform in the production environment. Fixing the command timeout might be a temporary fix.

[MMock]

I agree too - that's why I said it up front. But that is not what I can do right now!!!

[techgnome]

What ARE you connecting to? If it's SQL Server, then the SQLConnection object DOES have a CommandTimeout
http://social.msdn.microsoft.com/Sea...ndtimeout&ac=8

-tg

[MMock]

It's DB2. I've bitten the bullet and made the change on every command. Thanks. I'll close this when my customer can run it without timing out - to be sure it works.

[MMock]

I am not timing out anymore, but it is long-running. It is on a WAN when my customers test it. When I was testing, it was all local (I know you are all saying "I told you so" - remember, I agree!). So now I'd like to ask how expensive the connect to the database is? Every time I run a query, I connect, run the query and disconnect. That is not how it was in VB6. In VB6, it connected once, ran all the queries it needed to, and disconnected. Could all these connects be causing the performance degradation compare to VB6? The VB.NET code was rewritten prior to my arrival, so I'm not sure if that was a bad decision (changing from how VB6 connected) that should be undone. Thanks.

[abhijit]

Look up connection pooling for ASP applications.

[MMock]

It's Windows.

[techgnome]

I don't know if DB2 allows connection pooling or not.... something tells me it doesn't.

That aside, whether the the connect, query, disconnect operations are expensive or not, depends.
If it's like this:
connect
query
disconnect
connect again
another query
disconnect
do something with the data.

Then, I'd see that as a problem.
If it's like this:
connect
query
another query
disconnect
do something with the data

That would be more optimal and fits within the generally accepted practice of having connections open only long enough to run queries.

-tg

[MMock]

Well let me describe what the application really does. It is a loader. I am never actually "doing something with the data" after reading it from the database. I read an input record from a file. I insert it into the database. I read another input record; I insert it, etc. Input files are rather large and come in either quarterly or monthly (which I guess is why they put up with a long load time. They don't do it daily). So keeping in line with your philosophy "having connections open only long enough to run queries" - if I know I will be running 1000 insert queries, should I disconnect after each one or stay connected?

Thanks.

[techgnome]

Stay connected.... open the file, open the connection, start reading from the file, insert data... loop until EOF, close file, close connection.

OR....
Create a datatable that mimics the table def in your database... open the file, read from the file & insert into the datatable... loop until EOF... THEN ... create an INSERT command, then use a table adaptor to shove the new records into the database for you. I'd probably would create a typed datatable for the table... give it a sql squery to use to do the inserts... map the columns (all easily done right in the wizard)... then just create an object of that datatable type... add records to it, then call the .Update method to send the changes to the database.

Given time and simplicity... open the connection, issue your insert queries, close the connection.

-tg

[MMock]

Stay connected.... open the file, open the connection, start reading from the file, insert data... loop until EOF, close file, close connection.
...

Given time and simplicity... open the connection, issue your insert queries, close the connection.

-tg

I agree! Thanks for the validation. I'll tell you how it goes.

[MMock]

I am going to do this now, I just have one question. Exception handling isn't the greatest in this application. If there's an exception, most of the time, an End instruction is executed after telling the user what happened. What will happen to my database connection if I open it, leave it open while I am inserting, then encounter some type of exception (like trying to insert a null into a column that doesn't allow it) which ends the program? Will the connection be closed by the operating system? I will be running on a production box which I don't know much about (as far as what else is on it) and I don't want to be the cause of a hang or out-of-resources situation that will impact others. Thanks.

[techgnome]

"I don't want to be the cause of a hang or out-of-resources situation that will impact others. "
I'd close the connection if you can (as long as it's open) ... before dumping out (really? the app just ends like that on an error? Doesn't make any attempt at recovery? yikes!) otherwise that connection will hold longer than you really want it to and when it (if) it gets returned to the pool, there's no telling what state it's in. And if there's a lock or a transaction involved... there's no guarantee it gets released. We've had that happen to us a few times. It's never any fun.

-tg

[abhijit]

This really depends on the database settings. In our Oracle database, any session that stays inactive for more than 45 minutes is automatically killed with a rollback.

[MMock]

"I don't want to be the cause of a hang or out-of-resources situation that will impact others. "
I'd close the connection if you can (as long as it's open) ... before dumping out (really? the app just ends like that on an error? Doesn't make any attempt at recovery? yikes!) otherwise that connection will hold longer than you really want it to and when it (if) it gets returned to the pool, there's no telling what state it's in. And if there's a lock or a transaction involved... there's no guarantee it gets released. We've had that happen to us a few times. It's never any fun.

-tg

Yeah, that's what I was afraid of. Yes, the app just Ends - in many places. The application is in the hands of few users, and they are technical. Remember, this is a monthly/quarterly database load application. If they are loading the database and there is an error (usually a data problem), they will delete anything loaded up to the error for that monthly run, fix the error, and reload. It's kind of a manual rollback. There is no transaction-based processing.

So, to be thorough and correct, I would have to find every End statement and close the connection there - yes? Instead of leaving it up to the O/S to know what to do and do it right.

[techgnome]

if the general architecture can't be changed.... then yes.. that would be the safe way to go about doing it.

One thing you might want to consider is a Transaction Scope:
http://msdn.microsoft.com/en-us/libr...52(VS.90).aspx
http://msdn.microsoft.com/en-us/libr...tionscope.aspx
http://msdn.microsoft.com/en-us/libr....complete.aspx

take the manual rollback out of the equation...
And I realize I'm preaching to the choir here, but I'll ask anyways... wouldn't it be easier to validate the data and check for data problems before the data is imported into the database? Anyways...

-tg

[MMock]

if the general architecture can't be changed.... then yes.. that would be the safe way to go about doing it.
-tg

Okay...then that's what I'll do.

... wouldn't it be easier to validate the data and check for data problems before the data is imported into the database? -tg

Yes - and for some of the data we do that. It's not like the application exceptions 10x for every load and that is normal. Most of the time it doesn't exception. Maybe that is why they figured on the rare occasions there's an exception, just end the program and we'll deal with it and rerun.

Thanks for you help. And I will look at those links, too.

[abhijit]

take the manual rollback out of the equation...
And I realize I'm preaching to the choir here, but I'll ask anyways... wouldn't it be easier to validate the data and check for data problems before the data is imported into the database? Anyways...
-tg

tg,
The answer is "IT DEPENDS". The smart thing to do is profile your data.
If you have too many exceptions, it is easier to write validations for each element. If there are only a couple of exceptions, it is easier to upload the data into the database and then peform the validations.

Like most things in IT, there are two schools of thoughts.

[techgnome]

abhijit - No... not really.. there isn't a "it depends" in this case. When importing the data, either you validate it, or you don't. And I guess I should clarify my statement with - by "importing into the the database" I mean putting it it in the final tables where the data resides.

Ideally this is how I like to mass imports:
1) Read the file/records
2) dump every thing into a staging table
3) validate everything. check for out of bounds data, know bad values, etc
4) Only when the data has been cleaned.... move the CLEANED data to the final production tables
5) Allow the user or someone to manually scrub the data that's left
6) Repeat steps 4 & 5 as often as needed

There really shouldn't be a "it depends" ... you should always validate your data before it gets into its final resting place. It sounds like from MMock's response though that this is a 90% solution, and they don't mind the work on the other 10%. It makes me nervous, but then I don't have to deal with it.

-tg

[MMock]

tg - So you can be less nervous...now that you mention it, there are staging tables and the data get moved out of stage and into prod when they are validated (I believe, though I am not familiar with that step). I am just here for this VB6 to .NET conversion project and since my first round of changes are just now going into production I am learning about the environment as it goes. But you are right - they don't load directly into production!

[abhijit]

abhijit - No... not really.. there isn't a "it depends" in this case. When importing the data, either you validate it, or you don't. And I guess I should clarify my statement with - by "importing into the the database" I mean putting it it in the final tables where the data resides.

Ideally this is how I like to mass imports:
1) Read the file/records
2) dump every thing into a staging table
3) validate everything. check for out of bounds data, know bad values, etc
4) Only when the data has been cleaned.... move the CLEANED data to the final production tables
5) Allow the user or someone to manually scrub the data that's left
6) Repeat steps 4 & 5 as often as needed

There really shouldn't be a "it depends" ... you should always validate your data before it gets into its final resting place. It sounds like from MMock's response though that this is a 90% solution, and they don't mind the work on the other 10%. It makes me nervous, but then I don't have to deal with it.

-tg

tg,
I will have to come up with a better scenario. I do agree on one thing. The invalidated data should never reach the final database.

What we differ on the mechanism to test the invalid data.

[MMock]

I agree, too, and my data won't!

[techgnome]

"What we differ on the mechanism to test the invalid data." - now there is where the "it depends" comes in... for me, I'll load the file into a datatable and use SQL Server Management Objects (SMO) to do a bulk insert to get the data into staging tables as fast as I can. From there, run an sproc to validate the data... typically, with the kind of data I've dealt with, that's the easiest/fastest way to do it. I'm used to dealing with imports that are on the order of tens of thousands of records in a file with anywhere from a dozen to files that number in the triple digits.

@MMock - thanks... that does sound a lot nicer than I was originally thinking.

-tg