Ado connection to retrieve data and store in an array.

[Jack]

I am an IT manager and one of my responsibilities is to have a Client server
application developed. I have a VB programmer using An Ado connection to
hit a Sybase SQL anywhere database. The programmer is capturing data that
is retrieved form the server in an array. The retrieval of data is very slow,
anywhere from 30 sec. to 1 minute. This isn't the problem. The problem is
we are using the data to generate multiple reports. But once another report
is picked, using the existing data, the application is taking another 30
sec. to 1 minute to be displayed. I think this must be, because the programmer
is making another round trip to the server to get the data. If this is so,
can the array be stored in some sort of session variable or is the array
considered somewhat a session variable? From what I have found it looks like
arrays should be stored in memory for as long as needed, and can be ended
with a deallocation. I am looking for someone to confirm this or en-lighten
me. Also, Is there some simple information or code on how to create an array
and end the array? And finally, I am not at liberty to use something other
than Sybase SQL anywhere 5.0.4 is there anyway to speed up the initial retrieval
from the server? I know this is lengthy, but I felt I must give out all the
information as to secure the best possible solution. Any and all help would
be greatly appreciated.

[Norm Turnbull]

Rather than an array, how about using a disconnected ADO recordset (after
retreiving the recordset the first time, set recordset.ActiveConnection=Nothing)?
Then, put the recordset in a session variable for use by subsequent code.

Also, 30 seconds is an awfully long time to be retreiving data for a browser
app (I assume it's a browser because of your use of the word seesion). Is
this being run on a slow dial-up? There are so many questions to be asked
in this area, I wouldn't venture an "answer" but things like cartesian products
(missing join clause in the SQL) and retreiving much more data than what
is necessary (failing to put sufficient criteria in the WHERE or ON clauses)
come to mind.

Best regards,
Norm

[Ashish Desai]

you can disconnect the recordset object and keep it in memory.
Doing this can increase the amount of RAM your program consumes so
be careful as to how many recordsets you keep in memory.

You can also have the recordset object persist its contents to a file
on the local machine, and read back from a file. This allows you to "cache"
to the local disk.

If you have modify rights to the database you might consider creating an
index
on the object that you are using in your "where" clause. Indexes can
significantly
speed up the time it takes to run a query.

"Jack" <RDDELERY@DIALNET.NET> wrote in message
news:39930e82$1@news.devx.com...
>
> I am an IT manager and one of my responsibilities is to have a Client
server
> application developed. I have a VB programmer using An Ado connection to
> hit a Sybase SQL anywhere database. The programmer is capturing data that
> is retrieved form the server in an array. The retrieval of data is very
slow,
> anywhere from 30 sec. to 1 minute. This isn't the problem. The problem is
> we are using the data to generate multiple reports. But once another
report
> is picked, using the existing data, the application is taking another 30
> sec. to 1 minute to be displayed. I think this must be, because the
programmer
> is making another round trip to the server to get the data. If this is so,
> can the array be stored in some sort of session variable or is the array
> considered somewhat a session variable? From what I have found it looks
like
> arrays should be stored in memory for as long as needed, and can be ended
> with a deallocation. I am looking for someone to confirm this or
en-lighten
> me. Also, Is there some simple information or code on how to create an
array
> and end the array? And finally, I am not at liberty to use something other
> than Sybase SQL anywhere 5.0.4 is there anyway to speed up the initial
retrieval
> from the server? I know this is lengthy, but I felt I must give out all
the
> information as to secure the best possible solution. Any and all help
would
> be greatly appreciated.