Multiple connections to fetch hierarchy from database

[Henke]

I have a common Customer-Orders-OrderRows-Products hierarchy in my database.
Let's say I'd like to fetch a customer and all it's orders, rows and
products. Today we have methods on different DataAccessLogicComponents to
retrieve the data i.e:
CustomerDALC.GetCustomer(int id) - Gets the customer details
OrderDALC.GetOrders(Customer customer) - Gets the orders for the specified
customer and so on.
In the Get-methods I use SqlCommand.ExecuteReader and a SqlDataReader.

What is the best way to retrieve data stored in a hierarchy I mean in a
performance point of view?
When I used the DALCs, every Get-method creates a new connection to the
database which seems like reducing performance.

Thanks in advance!
/Henke

 

[MattC]

As you are running against SQL Server, someone on this board pointed out to
me that the connection gets reused so the performance hit is minimal.

MattC

 

[Miha Markic [MVP C#]]

Hi Henke,

As Matt pointed out, the connections are reused and thus hit is minimal.
Also, this method is thread safe from connection perspective.
Just make sure that you close the connections ASAP otherwise you'll see
connection pool increasing abnormaly.

 

[Henke]

So you mean that even though each DALC creates a new connection, it's reused
in the database?
/Henke

 

[Miha Markic [MVP C#]]

Hi,

No, there is connection pool at the client side (reused at client side, not
in database) - depends on settings, but normally it is turned on.

 

[Henke]

Hi, I made a test yesterday.
I was fetching customers from the CustomerTable and the customer types from
the CustomerTypeTable.
Th Customer-class looks like this:
public class Customer
{
private int id;
private CustomerType customerType;
...
}
I made three test:
In the first test I use one connection to get the customers, in this loop i
open a new connection (and a new dataReader) for each customer to be used to
get the customer type.

In the second test I use two connections, one for the customer table and one
for the customer types. I create a new reader for every customer type.

In the last test I joined the two tables, and used the same connection and
reader for every customer and customer type.

Here are the results to fetch 1000 customers and it associated type:
Test 1: 25,0 seconds
Test 2: 6,8 seconds
Test 3: 0,2 seconds

This shows that it takes a lot of time opening and closing the connections,
any comments?
/Henke

Hi,

No, there is connection pool at the client side (reused at client side,
not in database) - depends on settings, but normally it is turned on.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

So you mean that even though each DALC creates a new connection, it's
reused in the database?
/Henke

 

[Miha Markic [MVP C#]]

Hi, I made a test yesterday.
I was fetching customers from the CustomerTable and the customer types
from the CustomerTypeTable.
Th Customer-class looks like this:
public class Customer
{
private int id;
private CustomerType customerType;
...
}
I made three test:
In the first test I use one connection to get the customers, in this loop
i open a new connection (and a new dataReader) for each customer to be
used to get the customer type.

In the second test I use two connections, one for the customer table and
one for the customer types. I create a new reader for every customer type.

In the last test I joined the two tables, and used the same connection and
reader for every customer and customer type.

Here are the results to fetch 1000 customers and it associated type:
Test 1: 25,0 seconds
Test 2: 6,8 seconds
Test 3: 0,2 seconds

This shows that it takes a lot of time opening and closing the
connections, any comments?

I am not sure how you are doing the actual tests so I really can't comment
on the results.
Mind that there is also difference if you fire one select or fire more
selects.

 

[William \(Bill\) Vaughn]

So? The first time you connect, the connection pooling mechanism has to load
the netlib DLLs, build a new connection, get it open on the server and
return it to you. The next time since the channel is open and the DLLs are
loaded it does not take as long. The next time it can use an existing pooled
connection. Make sense?

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Hi, I made a test yesterday.
I was fetching customers from the CustomerTable and the customer types
from the CustomerTypeTable.
Th Customer-class looks like this:
public class Customer
{
private int id;
private CustomerType customerType;
...
}
I made three test:
In the first test I use one connection to get the customers, in this loop
i open a new connection (and a new dataReader) for each customer to be
used to get the customer type.

In the second test I use two connections, one for the customer table and
one for the customer types. I create a new reader for every customer type.

In the last test I joined the two tables, and used the same connection and
reader for every customer and customer type.

Here are the results to fetch 1000 customers and it associated type:
Test 1: 25,0 seconds
Test 2: 6,8 seconds
Test 3: 0,2 seconds

This shows that it takes a lot of time opening and closing the
connections, any comments?
/Henke

Hi,

No, there is connection pool at the client side (reused at client side,
not in database) - depends on settings, but normally it is turned on.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

So you mean that even though each DALC creates a new connection, it's
reused in the database?
/Henke

"Henke" <[email protected]> skrev i meddelandet
I have a common Customer-Orders-OrderRows-Products hierarchy in my
database.
Let's say I'd like to fetch a customer and all it's orders, rows and
products. Today we have methods on different DataAccessLogicComponents
to retrieve the data i.e:
CustomerDALC.GetCustomer(int id) - Gets the customer details
OrderDALC.GetOrders(Customer customer) - Gets the orders for the
specified customer and so on.
In the Get-methods I use SqlCommand.ExecuteReader and a SqlDataReader.

What is the best way to retrieve data stored in a hierarchy I mean in a
performance point of view?
When I used the DALCs, every Get-method creates a new connection to the
database which seems like reducing performance.

Thanks in advance!
/Henke

 

[q]

I would be interested to see the SQL statements.
Are they in code? Are you using views or stored procs in SQLServer?

Hi, I made a test yesterday.
I was fetching customers from the CustomerTable and the customer types from
the CustomerTypeTable.
Th Customer-class looks like this:
public class Customer
{
private int id;
private CustomerType customerType;
...
}
I made three test:
In the first test I use one connection to get the customers, in this loop i
open a new connection (and a new dataReader) for each customer to be used to
get the customer type.

In the second test I use two connections, one for the customer table and one
for the customer types. I create a new reader for every customer type.

In the last test I joined the two tables, and used the same connection and
reader for every customer and customer type.

Here are the results to fetch 1000 customers and it associated type:
Test 1: 25,0 seconds
Test 2: 6,8 seconds
Test 3: 0,2 seconds

This shows that it takes a lot of time opening and closing the connections,
any comments?
/Henke

Hi,

No, there is connection pool at the client side (reused at client side,
not in database) - depends on settings, but normally it is turned on.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

So you mean that even though each DALC creates a new connection, it's
reused in the database?
/Henke

"Henke" <[email protected]> skrev i meddelandet

I have a common Customer-Orders-OrderRows-Products hierarchy in my
database.
Let's say I'd like to fetch a customer and all it's orders, rows and
products. Today we have methods on different DataAccessLogicComponents
to retrieve the data i.e:
CustomerDALC.GetCustomer(int id) - Gets the customer details
OrderDALC.GetOrders(Customer customer) - Gets the orders for the
specified customer and so on.
In the Get-methods I use SqlCommand.ExecuteReader and a SqlDataReader.

What is the best way to retrieve data stored in a hierarchy I mean in a
performance point of view?
When I used the DALCs, every Get-method creates a new connection to the
database which seems like reducing performance.

Thanks in advance!
/Henke