Getting data in ASPDb using existing database connection objects

[Avnish Kumar]

Hi,

I have an existing database connection object in every ASP page I use. I want to send that object instead of a DSN to the ASPDb object. This would reduce extra database connections to every ASPDb table on my page.

Is it possible and how??

Thanks

Avnish

[John]

Hi Avnish,

Well, you *can* pass a recordset to ASPdb and let it display the data... BUT...

If you do, you'll give up the ability to do column sorting, and filtering and editing don't work well either. It's not our fault, but a limitation of the fact that we don't have access to your SQL statement and therefore can't add to it for filtering and sorting, etc.

You should know however, that Microsoft specifically recommends NOT to open a database connection and keep it open across pages. First of all, you never know when the user "clicks away" and leaves that connection open until timing out up to 20 minutes later. It ties up valuable resources on the server and database engine too. Finally, it opens up a can of worms for things like transaction rollbacks, simultaneous record updates by multiple users, record locking and more. In short - you better have a great reason to do it! If you think you're helping performance, you're not.

ASP-db was designed from the ground up to very quickly open, read/write, and close the data connections each time a page is displayed. The first access may take a few seconds, but subsequent accesses are almost always sub-second and the overhead on the server is extremely low. We've stress tested it with over a hundred simultaneous users hitting the databases multiple times per second and it comes through with flying colors. We've reviewed our strategy with the big honchos at Microsoft and they gave us thumbs up across the board that this is the RIGHT way to do it.

So, the bottom line is that we do allow you to pass us a recordset and we'll do what we can to display it, etc., but you should verify that there's an actual benefit to the alternative of letting ASP-db do the whole thing for you instead!

Hope this helps,
John


------------
Avnish Kumar at 6/14/01 12:45:16 PM


Hi,

I have an existing database connection object in every ASP page I use. I want to send that object instead of a DSN to the ASPDb object. This would reduce extra database connections to every ASPDb table on my page.

Is it possible and how??

Thanks

Avnish

[Avnish Kumar]

Thanks for the reply John.

I dont want to keep the connection open over multiple pages. I am talking of a single ASP page have 3-4 tables and some of my own SQL queries. I open the connection for my query. The 3-4 tables make individual connections of their own. I thought if I could send the same ADO connection object to your component it would save a lot of connections made by the same page.

Avnish


------------
John at 6/15/01 4:40:37 AM

Hi Avnish,

Well, you *can* pass a recordset to ASPdb and let it display the data... BUT...

If you do, you'll give up the ability to do column sorting, and filtering and editing don't work well either. It's not our fault, but a limitation of the fact that we don't have access to your SQL statement and therefore can't add to it for filtering and sorting, etc.

You should know however, that Microsoft specifically recommends NOT to open a database connection and keep it open across pages. First of all, you never know when the user "clicks away" and leaves that connection open until timing out up to 20 minutes later. It ties up valuable resources on the server and database engine too. Finally, it opens up a can of worms for things like transaction rollbacks, simultaneous record updates by multiple users, record locking and more. In short - you better have a great reason to do it! If you think you're helping performance, you're not.

ASP-db was designed from the ground up to very quickly open, read/write, and close the data connections each time a page is displayed. The first access may take a few seconds, but subsequent accesses are almost always sub-second and the overhead on the server is extremely low. We've stress tested it with over a hundred simultaneous users hitting the databases multiple times per second and it comes through with flying colors. We've reviewed our strategy with the big honchos at Microsoft and they gave us thumbs up across the board that this is the RIGHT way to do it.

So, the bottom line is that we do allow you to pass us a recordset and we'll do what we can to display it, etc., but you should verify that there's an actual benefit to the alternative of letting ASP-db do the whole thing for you instead!

Hope this helps,
John


------------
Avnish Kumar at 6/14/01 12:45:16 PM


Hi,

I have an existing database connection object in every ASP page I use. I want to send that object instead of a DSN to the ASPDb object. This would reduce extra database connections to every ASPDb table on my page.

Is it possible and how??

Thanks

Avnish

[John]

Hi Avnish,

Well, ASP-db will still do an open/read/close on each module. By the way, if you change your package type from Server to Application, you will have no "overhead" of that first time startup and your typical open/read/close should be less than a second - often around 50 miliseconds.

I'd try it before I went to another method.

Thanks,
John


------------
Avnish Kumar at 6/15/01 10:16:36 AM

Thanks for the reply John.

I dont want to keep the connection open over multiple pages. I am talking of a single ASP page have 3-4 tables and some of my own SQL queries. I open the connection for my query. The 3-4 tables make individual connections of their own. I thought if I could send the same ADO connection object to your component it would save a lot of connections made by the same page.

Avnish


------------
John at 6/15/01 4:40:37 AM

Hi Avnish,

Well, you *can* pass a recordset to ASPdb and let it display the data... BUT...

If you do, you'll give up the ability to do column sorting, and filtering and editing don't work well either. It's not our fault, but a limitation of the fact that we don't have access to your SQL statement and therefore can't add to it for filtering and sorting, etc.

You should know however, that Microsoft specifically recommends NOT to open a database connection and keep it open across pages. First of all, you never know when the user "clicks away" and leaves that connection open until timing out up to 20 minutes later. It ties up valuable resources on the server and database engine too. Finally, it opens up a can of worms for things like transaction rollbacks, simultaneous record updates by multiple users, record locking and more. In short - you better have a great reason to do it! If you think you're helping performance, you're not.

ASP-db was designed from the ground up to very quickly open, read/write, and close the data connections each time a page is displayed. The first access may take a few seconds, but subsequent accesses are almost always sub-second and the overhead on the server is extremely low. We've stress tested it with over a hundred simultaneous users hitting the databases multiple times per second and it comes through with flying colors. We've reviewed our strategy with the big honchos at Microsoft and they gave us thumbs up across the board that this is the RIGHT way to do it.

So, the bottom line is that we do allow you to pass us a recordset and we'll do what we can to display it, etc., but you should verify that there's an actual benefit to the alternative of letting ASP-db do the whole thing for you instead!

Hope this helps,
John


------------
Avnish Kumar at 6/14/01 12:45:16 PM


Hi,

I have an existing database connection object in every ASP page I use. I want to send that object instead of a DSN to the ASPDb object. This would reduce extra database connections to every ASPDb table on my page.

Is it possible and how??

Thanks

Avnish

[Frank Kwong]

I guess this is why MS implemented the Connection pool after ODBC 2.?. It looks like the components are on the same page and looks close to you but actually depends on what the component does, it might be not as close in the sense of the CPU... for example, one would open connection, get RS, format RS, print RS and close connection or one wouild open connection, get RS, close connection, format RS and Print RS. This first approach would jam up a lot of traffic in between.

So I think the best result an implementation like this is a "Controlled Crash". If you forgot to close the connection outside of ASP-db then it'll leak and eventually crash.

BTW, ASP-db usesd to have an option called "KeepConnectionOpen". The first encounter with MS in the 1st ASP show, MS advised us to take this one off.

FK
------------
John at 6/15/01 1:20:15 PM

Hi Avnish,

Well, ASP-db will still do an open/read/close on each module. By the way, if you change your package type from Server to Application, you will have no "overhead" of that first time startup and your typical open/read/close should be less than a second - often around 50 miliseconds.

I'd try it before I went to another method.

Thanks,
John


------------
Avnish Kumar at 6/15/01 10:16:36 AM

Thanks for the reply John.

I dont want to keep the connection open over multiple pages. I am talking of a single ASP page have 3-4 tables and some of my own SQL queries. I open the connection for my query. The 3-4 tables make individual connections of their own. I thought if I could send the same ADO connection object to your component it would save a lot of connections made by the same page.

Avnish


------------
John at 6/15/01 4:40:37 AM

Hi Avnish,

Well, you *can* pass a recordset to ASPdb and let it display the data... BUT...

If you do, you'll give up the ability to do column sorting, and filtering and editing don't work well either. It's not our fault, but a limitation of the fact that we don't have access to your SQL statement and therefore can't add to it for filtering and sorting, etc.

You should know however, that Microsoft specifically recommends NOT to open a database connection and keep it open across pages. First of all, you never know when the user "clicks away" and leaves that connection open until timing out up to 20 minutes later. It ties up valuable resources on the server and database engine too. Finally, it opens up a can of worms for things like transaction rollbacks, simultaneous record updates by multiple users, record locking and more. In short - you better have a great reason to do it! If you think you're helping performance, you're not.

ASP-db was designed from the ground up to very quickly open, read/write, and close the data connections each time a page is displayed. The first access may take a few seconds, but subsequent accesses are almost always sub-second and the overhead on the server is extremely low. We've stress tested it with over a hundred simultaneous users hitting the databases multiple times per second and it comes through with flying colors. We've reviewed our strategy with the big honchos at Microsoft and they gave us thumbs up across the board that this is the RIGHT way to do it.

So, the bottom line is that we do allow you to pass us a recordset and we'll do what we can to display it, etc., but you should verify that there's an actual benefit to the alternative of letting ASP-db do the whole thing for you instead!

Hope this helps,
John


------------
Avnish Kumar at 6/14/01 12:45:16 PM


Hi,

I have an existing database connection object in every ASP page I use. I want to send that object instead of a DSN to the ASPDb object. This would reduce extra database connections to every ASPDb table on my page.

Is it possible and how??

Thanks

Avnish