|
 |
activex_data_objects thread: SQL 2000 + ADO + Frame Relay question
Message #1 by Matt.Brook@p... on Wed, 6 Feb 2002 22:22:23
|
|
I was wondering if someone could help me with a problem I am having with
an application.
Here is the development environment:
Visaul Basic 6.0 SP 5
ADO 2.6 SP 1
SQL Server 2000
I have created a Data Access Layer component that uses ADO for Data
Access. The application and the
Data Access Layer component are both installed on the client machine.
The problem occurs when I am trying to retrieve a recordset across the
WAN. Here are the ADO properties used:
Client-Side Cursor
Forward-Only Cursor
Read-Only Lock
CacheSize = 100
Network Library = Named Pipes
The table that is pulled over is 100,000 records and a single record is
158 bytes of storage
If I am not mistaken, a SQL 2000 page is 8K. This means that 50 records
can fit in one page,
which equals 2,000 pages, with 2 pages being cached at a time in memory.
Total size of the recordset
is 16M.
The network environment is a Frame Relay with a CIR of 256K.
I monitoring the WAN connection during 2 separate runs of the application.
and noticed a pattern that was in both
connections and I was wondering if someone could help explain some things
to me.
Here are some numbers:
*******************************************
Client to Server Bandwith
VALUE TIME
Client to Server
[A] Before Peak 48% 1:32 PM
[B] At Peak 190% 1:37 PM
[C] End of Peak 52% 1:47 PM
Diff [B] - [A] 142% 0:05
Diff [C] - [B] -138% 0:10
Diff [C] - [A] 4% 0:15
*******************************************
Server To Client Bandwith
VALUE TIME
[A] Before Peak 7% 1:17 PM
[B] Point B 19% 1:22 PM
[C] Point C 142% 1:26 PM
[D] Point D 96% 1:32 PM
[E] Point E 12% 1:36 PM
[F] After Peak 7% 1:42 PM
Diff [B] - [A] 12% 0:05
Diff [C] - [B] 123% 0:04
Diff [D] - [C] -46% 0:06
Diff [E] - [D] -84% 0:04
Diff [F] - [E] -5% 0:06
Diff [F] - [A] 0% 0:25
*********************************************
I also noticed that for some reason, during the ramp in the Client To
Server Bandwith, our numbers of
Discard Eligible (DE) Frames are in the same peak fomrat as the Client To
Server Bandwith Peak with
the DE Ranging from 3 Frames/SEC to 33 Frames/SEC. All of which is
costing us money.
I can understand the peak in the Server To Client Bandwith as the
recordset is pulled to the client machine.
What I can't understand is why is there a peak in the Client To Server
Bandwith halfway through the ramp down
of the Server To Client peak.
Does that mean that the Server sends the Client pointers and the Client To
Server Bandwith increase is due
to the actual data being pulled from the pointers?
Could someone explain to me what kind of communication is going on between
the Client and Server when a recordset
is being transfered?
One reccomendation I read was to use the TCP/IP network library for the
ADO components instead of the Named Pipes.
Isn't the communication across the WAN TCP/IP anyway? Do I really need to
Switch the library?
What would happen if I increased the CacheSize to 100,000?
I was thinking maybe if I pulled the recordset FOR XML and storing in an
ADO Stream and processe it on the
Client using the MSXML. But, the resulting size of the stream was 52M.
What is the best way for communcation between a Client and a Server over a
WAN, trying to keep the bandwith down
while using the applications?
Thank you in advance,
Matt
Message #2 by "Tomm Matthis" <matthis@b...> on Thu, 7 Feb 2002 06:17:26 -0500
|
|
I would say ditch Named Pipes and go for straight TCP/IP.
I saw a lot of "weird" things go away once I switched. This was back with SQL
7.0 and had been working with v6.5 and banging my head for a while.
-- Tomm
> -----Original Message-----
> From: Matt.Brook@p... [mailto:Matt.Brook@p...]
> Sent: Wednesday, February 06, 2002 10:22 PM
> To: ActiveX_Data_Objects
> Subject: [activex_data_objects] SQL 2000 + ADO + Frame Relay question
>
>
> I was wondering if someone could help me with a problem I am having with
> an application.
>
> Here is the development environment:
> Visaul Basic 6.0 SP 5
> ADO 2.6 SP 1
> SQL Server 2000
>
> I have created a Data Access Layer component that uses ADO for Data
> Access. The application and the
> Data Access Layer component are both installed on the client machine.
>
> The problem occurs when I am trying to retrieve a recordset across the
> WAN. Here are the ADO properties used:
> Client-Side Cursor
> Forward-Only Cursor
> Read-Only Lock
> CacheSize = 100
> Network Library = Named Pipes
>
> The table that is pulled over is 100,000 records and a single record is
> 158 bytes of storage
> If I am not mistaken, a SQL 2000 page is 8K. This means that 50 records
> can fit in one page,
> which equals 2,000 pages, with 2 pages being cached at a time in memory.
> Total size of the recordset
> is 16M.
>
> The network environment is a Frame Relay with a CIR of 256K.
>
> I monitoring the WAN connection during 2 separate runs of the application.
> and noticed a pattern that was in both
> connections and I was wondering if someone could help explain some things
> to me.
>
> Here are some numbers:
>
> *******************************************
> Client to Server Bandwith
>
> VALUE TIME
> Client to Server
> [A] Before Peak 48% 1:32 PM
> [B] At Peak 190% 1:37 PM
> [C] End of Peak 52% 1:47 PM
>
> Diff [B] - [A] 142% 0:05
> Diff [C] - [B] -138% 0:10
> Diff [C] - [A] 4% 0:15
>
> *******************************************
> Server To Client Bandwith
>
> VALUE TIME
> [A] Before Peak 7% 1:17 PM
> [B] Point B 19% 1:22 PM
> [C] Point C 142% 1:26 PM
> [D] Point D 96% 1:32 PM
> [E] Point E 12% 1:36 PM
> [F] After Peak 7% 1:42 PM
>
> Diff [B] - [A] 12% 0:05
> Diff [C] - [B] 123% 0:04
> Diff [D] - [C] -46% 0:06
> Diff [E] - [D] -84% 0:04
> Diff [F] - [E] -5% 0:06
> Diff [F] - [A] 0% 0:25
>
> *********************************************
>
> I also noticed that for some reason, during the ramp in the Client To
> Server Bandwith, our numbers of
> Discard Eligible (DE) Frames are in the same peak fomrat as the Client To
> Server Bandwith Peak with
> the DE Ranging from 3 Frames/SEC to 33 Frames/SEC. All of which is
> costing us money.
>
> I can understand the peak in the Server To Client Bandwith as the
> recordset is pulled to the client machine.
> What I can't understand is why is there a peak in the Client To Server
> Bandwith halfway through the ramp down
> of the Server To Client peak.
>
> Does that mean that the Server sends the Client pointers and the Client To
> Server Bandwith increase is due
> to the actual data being pulled from the pointers?
>
> Could someone explain to me what kind of communication is going on between
> the Client and Server when a recordset
> is being transfered?
>
> One reccomendation I read was to use the TCP/IP network library for the
> ADO components instead of the Named Pipes.
> Isn't the communication across the WAN TCP/IP anyway? Do I really need to
> Switch the library?
>
> What would happen if I increased the CacheSize to 100,000?
>
> I was thinking maybe if I pulled the recordset FOR XML and storing in an
> ADO Stream and processe it on the
> Client using the MSXML. But, the resulting size of the stream was 52M.
>
> What is the best way for communcation between a Client and a Server over a
> WAN, trying to keep the bandwith down
> while using the applications?
>
> Thank you in advance,
> Matt
>
> $subst('Email.Unsub').
Message #3 by "Pete Davis" <pdavis@q...> on Tue, 12 Feb 2002 15:08:13 -0500
|
|
The switch to TCP/IP will definitely help. The cache size of 100 is fine.
You really get diminishing returns after a cache size of 10 or so, anyway,
but with the quantity of data you're talking about, a size of 100 is good.
One possibility would be to perform the query on the server, export to XML
or even native format, then Zip the XML or native file (which will ZIP quite
well), and then send over the .ZIP file. That's probably going to give you
the least bandwidth, but obviously Zipping a large file is going to be time
consuming, but if you're more concerned about bandwidth than speed, then
that's probably your best bet.
Just stick the entire file into, say, a safe array of bytes, ship it across
the line, and then unzip it.
Pete
----- Original Message -----
From: <Matt.Brook@p...>
To: "ActiveX_Data_Objects" <activex_data_objects@p...>
Sent: Wednesday, February 06, 2002 10:22 PM
Subject: [activex_data_objects] SQL 2000 + ADO + Frame Relay question
> I was wondering if someone could help me with a problem I am having with
> an application.
>
> Here is the development environment:
> Visaul Basic 6.0 SP 5
> ADO 2.6 SP 1
> SQL Server 2000
>
> I have created a Data Access Layer component that uses ADO for Data
> Access. The application and the
> Data Access Layer component are both installed on the client machine.
>
> The problem occurs when I am trying to retrieve a recordset across the
> WAN. Here are the ADO properties used:
> Client-Side Cursor
> Forward-Only Cursor
> Read-Only Lock
> CacheSize = 100
> Network Library = Named Pipes
>
> The table that is pulled over is 100,000 records and a single record is
> 158 bytes of storage
> If I am not mistaken, a SQL 2000 page is 8K. This means that 50 records
> can fit in one page,
> which equals 2,000 pages, with 2 pages being cached at a time in memory.
> Total size of the recordset
> is 16M.
>
> The network environment is a Frame Relay with a CIR of 256K.
>
> I monitoring the WAN connection during 2 separate runs of the application.
> and noticed a pattern that was in both
> connections and I was wondering if someone could help explain some things
> to me.
>
> Here are some numbers:
>
> *******************************************
> Client to Server Bandwith
>
> VALUE TIME
> Client to Server
> [A] Before Peak 48% 1:32 PM
> [B] At Peak 190% 1:37 PM
> [C] End of Peak 52% 1:47 PM
>
> Diff [B] - [A] 142% 0:05
> Diff [C] - [B] -138% 0:10
> Diff [C] - [A] 4% 0:15
>
> *******************************************
> Server To Client Bandwith
>
> VALUE TIME
> [A] Before Peak 7% 1:17 PM
> [B] Point B 19% 1:22 PM
> [C] Point C 142% 1:26 PM
> [D] Point D 96% 1:32 PM
> [E] Point E 12% 1:36 PM
> [F] After Peak 7% 1:42 PM
>
> Diff [B] - [A] 12% 0:05
> Diff [C] - [B] 123% 0:04
> Diff [D] - [C] -46% 0:06
> Diff [E] - [D] -84% 0:04
> Diff [F] - [E] -5% 0:06
> Diff [F] - [A] 0% 0:25
>
> *********************************************
>
> I also noticed that for some reason, during the ramp in the Client To
> Server Bandwith, our numbers of
> Discard Eligible (DE) Frames are in the same peak fomrat as the Client To
> Server Bandwith Peak with
> the DE Ranging from 3 Frames/SEC to 33 Frames/SEC. All of which is
> costing us money.
>
> I can understand the peak in the Server To Client Bandwith as the
> recordset is pulled to the client machine.
> What I can't understand is why is there a peak in the Client To Server
> Bandwith halfway through the ramp down
> of the Server To Client peak.
>
> Does that mean that the Server sends the Client pointers and the Client To
> Server Bandwith increase is due
> to the actual data being pulled from the pointers?
>
> Could someone explain to me what kind of communication is going on between
> the Client and Server when a recordset
> is being transfered?
>
> One reccomendation I read was to use the TCP/IP network library for the
> ADO components instead of the Named Pipes.
> Isn't the communication across the WAN TCP/IP anyway? Do I really need to
> Switch the library?
>
> What would happen if I increased the CacheSize to 100,000?
>
> I was thinking maybe if I pulled the recordset FOR XML and storing in an
> ADO Stream and processe it on the
> Client using the MSXML. But, the resulting size of the stream was 52M.
>
> What is the best way for communcation between a Client and a Server over a
> WAN, trying to keep the bandwith down
> while using the applications?
>
> Thank you in advance,
> Matt
>
$subst('Email.Unsub').
|
|
|
View Cart
