Page 1 of 1

Two DB questions - Make that Three

Posted: Sat Apr 01, 2006 7:25 pm
by alex.barylski
1) What is the purpose of DSN??

I've searched Google and I read about it and understand it's basically just an easy to expedite connection to RDBMS but it doesn't seem to serve a purpose in a web application environment - especially on Linux???

When would I use DSN?

2) I know what persistent connections are, but I never used them in web application development.

When would one use persistent MySQL database connections?

3) Recordsets. I know what they are and have used them, but wonder if their really worth it? I mean is it that more difficult to work with an associative array, than a resultset object?

What is your opinion on recordsets in abstraction layers like AdoDB? Overkill or nice feature?

Thanks :)

Re: Two DB questions - Make that Three

Posted: Sat Apr 01, 2006 7:44 pm
by Christopher
Hockey wrote:1) What is the purpose of DSN??

I've searched Google and I read about it and understand it's basically just an easy to expedite connection to RDBMS but it doesn't seem to serve a purpose in a web application environment - especially on Linux???

When would I use DSN?
A DSN is not to expidite connections, it is the information needed to make the connection. It PHP it is usually something like:

Code: Select all

$dsn = array(
     'host' => 'localhost',
     'username' => 'myuser',
     'pasword' => 'mypass',
     'database' => 'mydb',
     'port' => '123',
     );
Hockey wrote:2) I know what persistent connections are, but I never used them in web application development.

When would one use persistent MySQL database connections?
Using the persistent connections provided by several of the database libraries can improve performance in some cases, but can also hurt performance in others. You would need to understand what is going on with them, and then tune the database configuration with them on/off to improve performance.
Hockey wrote:3) Recordsets. I know what they are and have used them, but wonder if their really worth it? I mean is it that more difficult to work with an associative array, than a resultset object?

What is your opinion on recordsets in abstraction layers like AdoDB? Overkill or nice feature?

Thanks :)
RecordSets are very handy because they encapsulate the database "handle" to the query result for SELECTs, plus information like the number of records in the result and error messages/numbers. It is the OO way to abstract a database SELECT query result.

Re: Two DB questions - Make that Three

Posted: Sat Apr 01, 2006 8:05 pm
by alex.barylski
arborint wrote:
Hockey wrote:1) What is the purpose of DSN??

I've searched Google and I read about it and understand it's basically just an easy to expedite connection to RDBMS but it doesn't seem to serve a purpose in a web application environment - especially on Linux???

When would I use DSN?
A DSN is not to expidite connections, it is the information needed to make the connection. It PHP it is usually something like:

Code: Select all

$dsn = array(
     'host' => 'localhost',
     'username' => 'myuser',
     'pasword' => 'mypass',
     'database' => 'mydb',
     'port' => '123',
     );
Hockey wrote:2) I know what persistent connections are, but I never used them in web application development.

When would one use persistent MySQL database connections?
Using the persistent connections provided by several of the database libraries can improve performance in some cases, but can also hurt performance in others. You would need to understand what is going on with them, and then tune the database configuration with them on/off to improve performance.
Hockey wrote:3) Recordsets. I know what they are and have used them, but wonder if their really worth it? I mean is it that more difficult to work with an associative array, than a resultset object?

What is your opinion on recordsets in abstraction layers like AdoDB? Overkill or nice feature?

Thanks :)
RecordSets are very handy because they encapsulate the database "handle" to the query result for SELECTs, plus information like the number of records in the result and error messages/numbers. It is the OO way to abstract a database SELECT query result.
Thanks for the speedy reply... :)

1) I didn't actually mean expedite connections in the technical sense, but rather prevents you from re-entering connection details, instead you just refer to DSN.

Where would they be handy in web development though? Why can't I just use an simple API call so at least in my code I know where i've connected to a database?

The only thing I can think of is if you stored connection details inside an XML file or something and wanted a uniform way of passing connection details to a DB connection function. I fail to see it's use though in web development?

2) Can you explain how using persistent connections in an web application actually boosts performance? As far as I know your connection is persistent (so to speak) until that variable goes out of scope or your script terminates. Or do persistent connection resources get stored differently allowing concurrent SQL statements to sooner figure out which resource to use??

3) So your a yes vote for recordsets eh?? :P

Thanks again :)

Re: Two DB questions - Make that Three

Posted: Sat Apr 01, 2006 8:20 pm
by Roja
Hockey wrote:1) What is the purpose of DSN??

When would I use DSN?
Its useful in a variety of situations. Its basically an alternative format for providing the information needed for opening database connections. Its nice in that it is widely used in a huge number of database types, so it can be a more consistent format than others.
Hockey wrote:2) I know what persistent connections are, but I never used them in web application development.

When would one use persistent MySQL database connections?
In the very specific situation where benchmarking shows that the process of building a db connection, connecting, and breaking down the connection is the largest performance cost item - and too impacting to ignore.

Even then, it is extremely likely to prove to be a net performance detriment, not improvement. Persistent connections is one of those "We gave you enough options that you can really hurt yourself if you dont know what you are doing" items. It *appears* helpful, but testing will often show just the opposite.

Use with caution. :)
Hockey wrote:3) Recordsets. I know what they are and have used them, but wonder if their really worth it? I mean is it that more difficult to work with an associative array, than a resultset object?
If you want to encapsulate objects cleanly, recordsets are wonderful. Sometimes you want that, for a cleaner design, while other times, you may prefer speed and simplicity.

Its a small part of the whole OOP v. Procedural concept, and your mileage may vary.

Just to mention, even in a mostly procedural program, they can be very useful. If you need a recordset, its very nice to have that available. :)
Hockey wrote:What is your opinion on recordsets in abstraction layers like AdoDB? Overkill or nice feature?
Questions like "Overkill?" are really great, in that it shows you are asking a key question: Is this something *I* need.

As a result, no one can answer that but you. Perhaps you find any abstraction or code that uses classes to be meaningless and wasteful. Maybe you prefer procedural because its "just easier to look at".

Or on the other hand, maybe you can't stand procedural code, and clean abstraction and design makes you happy.

Adodb has a large number of features, its true. Some of them are more useful than others for specific people. The only way to answer whether it is "Overkill", or bloated, is to work with it, and determine whether you use the features in question.

If you do, and there isn't an alternative that currently supports those features, then its useful.

It comes down to design choices, and personal needs.

Personally, I use over 80% of the features in adodb actively, and as a result, talk about specific features being "Overkill", or "Bloat", just sounds like complaining to me. It does what I need and want, and nothing else currently can. Makes it an easy choice.

That may change in time, at which point I may be asking the same question you are. :)

Posted: Sat Apr 01, 2006 8:27 pm
by Christopher
1) DSN are really configuration data in a form that databases will accept. They are used pretty much whenever databases are used.

2) Persistent connections in the PHP sense means that the driver is keeping a connection pool and maintaining your connection over several requests until some sort of timeout is reached and then the connection is closed. It is better not to use them unless you know they will benefit you.

Re: Two DB questions - Make that Three

Posted: Sat Apr 01, 2006 8:53 pm
by alex.barylski
Roja wrote:
Hockey wrote:1) What is the purpose of DSN??

When would I use DSN?
Its useful in a variety of situations. Its basically an alternative format for providing the information needed for opening database connections. Its nice in that it is widely used in a huge number of database types, so it can be a more consistent format than others.
Hockey wrote:2) I know what persistent connections are, but I never used them in web application development.

When would one use persistent MySQL database connections?
In the very specific situation where benchmarking shows that the process of building a db connection, connecting, and breaking down the connection is the largest performance cost item - and too impacting to ignore.

Even then, it is extremely likely to prove to be a net performance detriment, not improvement. Persistent connections is one of those "We gave you enough options that you can really hurt yourself if you dont know what you are doing" items. It *appears* helpful, but testing will often show just the opposite.

Use with caution. :)
Hockey wrote:3) Recordsets. I know what they are and have used them, but wonder if their really worth it? I mean is it that more difficult to work with an associative array, than a resultset object?
If you want to encapsulate objects cleanly, recordsets are wonderful. Sometimes you want that, for a cleaner design, while other times, you may prefer speed and simplicity.

Its a small part of the whole OOP v. Procedural concept, and your mileage may vary.

Just to mention, even in a mostly procedural program, they can be very useful. If you need a recordset, its very nice to have that available. :)
Hockey wrote:What is your opinion on recordsets in abstraction layers like AdoDB? Overkill or nice feature?
Questions like "Overkill?" are really great, in that it shows you are asking a key question: Is this something *I* need.

As a result, no one can answer that but you. Perhaps you find any abstraction or code that uses classes to be meaningless and wasteful. Maybe you prefer procedural because its "just easier to look at".

Or on the other hand, maybe you can't stand procedural code, and clean abstraction and design makes you happy.

Adodb has a large number of features, its true. Some of them are more useful than others for specific people. The only way to answer whether it is "Overkill", or bloated, is to work with it, and determine whether you use the features in question.

If you do, and there isn't an alternative that currently supports those features, then its useful.

It comes down to design choices, and personal needs.

Personally, I use over 80% of the features in adodb actively, and as a result, talk about specific features being "Overkill", or "Bloat", just sounds like complaining to me. It does what I need and want, and nothing else currently can. Makes it an easy choice.

That may change in time, at which point I may be asking the same question you are. :)
Cool, thanks for the feedback :)

I am pro OOP in most instances, as long as it makes sense...so yes, I use recordsets and AdoDB. However, if you haven't figured it out (by the context of questions I'm asking) I'm writing a DB abstraction layer and I will likley throw it out into public when it's finished and I have atleast one driver working (MySQL). To avoid public scrutiny - not that it bothers me, but you should always try to appeal to the masses...I figured I'd get a general synopsis as to what other felt was overkill, etc...

PEAR MDB2 as far as I understand, doesn't support recordsets (It's possible it was another popular abstraction layer - i've revied a few in the last day or so) because the developers opted for speed over OOP friendly I guess...

Made me sit back and re-think my stance :)

Anyways, thanks for the input :)

Re: Two DB questions - Make that Three

Posted: Sat Apr 01, 2006 9:09 pm
by AKA Panama Jack
Roja wrote:
Hockey wrote:1) What is the purpose of DSN??

When would I use DSN?
Its useful in a variety of situations. Its basically an alternative format for providing the information needed for opening database connections. Its nice in that it is widely used in a huge number of database types, so it can be a more consistent format than others.
Actually it is something that isn't used by many databases. It is mainly used for an ODBC driver. Most databases connecting through PHP do not use the DSN format. It is something that was basically added to ADOdb to be compatible with PEAR's DSN connection since ADOdb contains many PEAR compatible functions. I added it to ADOdb Lite just for compatibility sake as it is more of a redundant feature.

In reality using DSN on something like ADOdb/ADOdb Lite adds more execution overhead as it has to parse out the DSN string and store the components into separate variables. You would be much better off setting the appropriate connection variables and avoiding the preprocessing that is required.
Roja wrote:
Hockey wrote:2) I know what persistent connections are, but I never used them in web application development.

When would one use persistent MySQL database connections?
In the very specific situation where benchmarking shows that the process of building a db connection, connecting, and breaking down the connection is the largest performance cost item - and too impacting to ignore.

Even then, it is extremely likely to prove to be a net performance detriment, not improvement. Persistent connections is one of those "We gave you enough options that you can really hurt yourself if you don't know what you are doing" items. It *appears* helpful, but testing will often show just the opposite.

Use with caution. :)
This was definitely true if you were using older versions of PHP in conjunction with Mysql 3.x.x. Mysql 3 is very slow in creating new connections. But with the newer versions of PHP (4 & 5) and Mysql (4 & 5) using persistent connections can be a detriment to your server. Many people are recommending never using persistent connections with these versions of Mysql and PHP. I have performed many speed tests using both persistent and non-persistent connections and in most cases there isn't any difference in CPU load or speed of connection. Though if you are using persistent connections memory allocation tends to increase the longer the connection is open and reused. With enough persistent connections you will start using swap file memory on an active server the longer they stay open and in use. This problem doesn't happen with non-persistent connections as the memory space is deallocated when the php program finishes execution and automatically closes the mysql connection.

You may also notice that some database packages do not even support persistent connections.
Hockey wrote:3) Recordsets. I know what they are and have used them, but wonder if their really worth it? I mean is it that more difficult to work with an associative array, than a resultset object?
Hockey wrote:What is your opinion on recordsets in abstraction layers like AdoDB? Overkill or nice feature?
With ADOdb/ADOdb Lite the recordset is really just a container for all of the information returned by the executed query. It makes accessing and manipulating the data a little easier in some cases. If you have many groups of records from different queries they are easier to manage when they are contained in recordset objects. It prevents possible overlapping though sloppy programming. If you are using ADOdb/ADOdb Lite the result usually defaults to an associative array contained inside the resultset object. So can you manipulate the data the same way.

Code: Select all

$my_associative_array = $resultset->fields;
The above would populate the $my_associative_array variable with array data returned through the resultset from the query.

Also, the speed of the recordset object is going to depend upon what is contained in the object and how it was created. ADOdb is notoriously large in that area so it does take it extra time to create each recordset object. ADOdb Lite took a different approach and is considerably faster in this area.

Re: Two DB questions - Make that Three

Posted: Sat Apr 01, 2006 9:34 pm
by alex.barylski
AKA Panama Jack wrote:
Roja wrote:
Hockey wrote:1) What is the purpose of DSN??

When would I use DSN?
Its useful in a variety of situations. Its basically an alternative format for providing the information needed for opening database connections. Its nice in that it is widely used in a huge number of database types, so it can be a more consistent format than others.
Actually it is something that isn't used by many databases. It is mainly used for an ODBC driver. Most databases connecting through PHP do not use the DSN format. It is something that was basically added to ADOdb to be compatible with PEAR's DSN connection since ADOdb contains many PEAR compatible functions. I added it to ADOdb Lite just for compatibility sake as it is more of a redundant feature.

In reality using DSN on something like ADOdb/ADOdb Lite adds more execution overhead as it has to parse out the DSN string and store the components into separate variables. You would be much better off setting the appropriate connection variables and avoiding the preprocessing that is required.
Roja wrote:
Hockey wrote:2) I know what persistent connections are, but I never used them in web application development.

When would one use persistent MySQL database connections?
In the very specific situation where benchmarking shows that the process of building a db connection, connecting, and breaking down the connection is the largest performance cost item - and too impacting to ignore.

Even then, it is extremely likely to prove to be a net performance detriment, not improvement. Persistent connections is one of those "We gave you enough options that you can really hurt yourself if you don't know what you are doing" items. It *appears* helpful, but testing will often show just the opposite.

Use with caution. :)
This was definitely true if you were using older versions of PHP in conjunction with Mysql 3.x.x. Mysql 3 is very slow in creating new connections. But with the newer versions of PHP (4 & 5) and Mysql (4 & 5) using persistent connections can be a detriment to your server. Many people are recommending never using persistent connections with these versions of Mysql and PHP. I have performed many speed tests using both persistent and non-persistent connections and in most cases there isn't any difference in CPU load or speed of connection. Though if you are using persistent connections memory allocation tends to increase the longer the connection is open and reused. With enough persistent connections you will start using swap file memory on an active server the longer they stay open and in use. This problem doesn't happen with non-persistent connections as the memory space is deallocated when the php program finishes execution and automatically closes the mysql connection.

You may also notice that some database packages do not even support persistent connections.
Hockey wrote:3) Recordsets. I know what they are and have used them, but wonder if their really worth it? I mean is it that more difficult to work with an associative array, than a resultset object?
Hockey wrote:What is your opinion on recordsets in abstraction layers like AdoDB? Overkill or nice feature?
With ADOdb/ADOdb Lite the recordset is really just a container for all of the information returned by the executed query. It makes accessing and manipulating the data a little easier in some cases. If you have many groups of records from different queries they are easier to manage when they are contained in recordset objects. It prevents possible overlapping though sloppy programming. If you are using ADOdb/ADOdb Lite the result usually defaults to an associative array contained inside the resultset object. So can you manipulate the data the same way.

Code: Select all

$my_associative_array = $resultset->fields;
The above would populate the $my_associative_array variable with array data returned through the resultset from the query.

Also, the speed of the recordset object is going to depend upon what is contained in the object and how it was created. ADOdb is notoriously large in that area so it does take it extra time to create each recordset object. ADOdb Lite took a different approach and is considerably faster in this area.
In reality using DSN on something like ADOdb/ADOdb Lite adds more execution overhead as it has to parse out the DSN string and store the components into separate variables. You would be much better off setting the appropriate connection variables and avoiding the preprocessing that is required
Thats what I was thinking...good enough...thanks for that...I'm leaving it out of mine :)

And you've convinced me to keep recordsets :)

I appreciate your input :)

Re: Two DB questions - Make that Three

Posted: Sat Apr 01, 2006 11:45 pm
by Roja
AKA Panama Jack wrote:Actually it is something that isn't used by many databases. It is mainly used for an ODBC driver.
This highlights a substantial difference in definitions. Both Oracle and MS-SQL use DSN's, and in large businesses, thats most databases. Hence my comment.

Of course, extending that argument, it also works for over half the databases supported by Adodb, making it conveniently portable for (as I said) the majority of databases (presuming you use an appropriate abstraction library). In fact, the list of databases that you cannot connect to using DSN connections (via adodb) is rather small.

Re: Two DB questions - Make that Three

Posted: Sun Apr 02, 2006 5:27 am
by AKA Panama Jack
Roja wrote:
AKA Panama Jack wrote:Actually it is something that isn't used by many databases. It is mainly used for an ODBC driver.
This highlights a substantial difference in definitions. Both Oracle and MS-SQL use DSN's, and in large businesses, thats most databases. Hence my comment.

Of course, extending that argument, it also works for over half the databases supported by Adodb, making it conveniently portable for (as I said) the majority of databases (presuming you use an appropriate abstraction library). In fact, the list of databases that you cannot connect to using DSN connections (via adodb) is rather small.
Actually, any database supported by ADOdb or ADOdb Lite will be able to use the DSN because ADOdb or ADOdb Lite will DECODE the DSN and place the data into seperate variables inside the connection object. Those variables are then used in making the connection to the appropriate database. Most of the databases supported by ADOdb or ADOdb Lite CANNOT process a DSN without the abstraction layer first breaking the data apart.

In otherwords it is the abstraction layer that supports the DSN format and not the databases themselves.

And please do not be so obtuse. When a person says "most databases" they mean the different database systems available and not the total of a single database system. I think most people understand that.