Hello,

I have an application that is using ODBC drivers that are eating VERY Large
amounts to computer resources when performing queries against a database.
When the application first starts the query is performed at an acceptable
rate, but after the first query if another one is performed it can take up
to 30 minutes to return 31,000 records.

The ODBC Connection and Objects are being closed and/or destroyed each time
a query is finished. When I look at the Driver Log File I see that about
100 or more files and multiple processes are attached to the driver when it
starts.

Is there a way, that when the user selects a new search criteria, that the
application terminates and then restarts itself?

If so, is there a way in VB.NET code (in a click event) to cause this to
happen?


Thanks,

Chuck

What is the user doing with 31,000 rows?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
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.
__________________________________

"Charles A. Lackman" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Hello,
>
> I have an application that is using ODBC drivers that are eating VERY
> Large
> amounts to computer resources when performing queries against a database.
> When the application first starts the query is performed at an acceptable
> rate, but after the first query if another one is performed it can take up
> to 30 minutes to return 31,000 records.
>
> The ODBC Connection and Objects are being closed and/or destroyed each
> time
> a query is finished. When I look at the Driver Log File I see that about
> 100 or more files and multiple processes are attached to the driver when
> it
> starts.
>
> Is there a way, that when the user selects a new search criteria, that the
> application terminates and then restarts itself?
>
> If so, is there a way in VB.NET code (in a click event) to cause this to
> happen?
>
>
> Thanks,
>
> Chuck
>
>

Hello,

The interface that they are using displays all the rows from the database in
a datagrid and also allows for sorting, and individual record selection
(from the grid and also an interface which allows for type ahead searches on
Last Name, Phone Number, etc also, the entries in the database are all
capital and I format the returned data after I retrieve it - which takes
about 2 seconds). The salesmanager wants to see everything, but the salemen
only see what belongs to them. The problem occurs when they see ALL the
records then refines them based on a single salesman then wants ALL of them
again (A view would speed this up, but there is still the problem with the 3
minutes wait to retrieve them). Under normal circumstances they will only
see theirs and never need to see ALL of them.

The issue is waiting 3 minutes to get ALL the records. When retrieved by
salesman number only takes about 15 seconds. If the Salesmanager sends
multiple queries they will have to wait over 30 minutes to get the records
from the database because of resources.

IBM told me that I was getting the records in the most efficient way
possible (after 2 weeks of consulting them), but that each query uses
massive resources from the computer. Most of the salesmen are using
notebook computers.

Anyway, I answered my own question, but if you are familuar with a better
way of making this work (faster). I would be very interested in hearing
your suggestions.

Thank you for your time.

Chuck


"William (Bill) Vaughn" <(E-Mail Removed)> wrote in message
news:eq3SG%(E-Mail Removed)...
What is the user doing with 31,000 rows?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
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.
__________________________________

"Charles A. Lackman" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Hello,
>
> I have an application that is using ODBC drivers that are eating VERY
> Large
> amounts to computer resources when performing queries against a database.
> When the application first starts the query is performed at an acceptable
> rate, but after the first query if another one is performed it can take up
> to 30 minutes to return 31,000 records.
>
> The ODBC Connection and Objects are being closed and/or destroyed each
> time
> a query is finished. When I look at the Driver Log File I see that about
> 100 or more files and multiple processes are attached to the driver when
> it
> starts.
>
> Is there a way, that when the user selects a new search criteria, that the
> application terminates and then restarts itself?
>
> If so, is there a way in VB.NET code (in a click event) to cause this to
> happen?
>
>
> Thanks,
>
> Chuck
>
>

I doubt if the Sales Manager wants to "see" all of these rows at once, but
would rather perform operations that encompassed all (or many or selected)
rows. He/she might want to scroll through a list of all rows but would
settle for waiting .02 seconds between fetches of the next page--especially
if it was done behind the scenes. When we perform operations on a lot of
rows, we use the server--not cached client-side data. For example, if they
want to sort by a couple of fields, we perform the sort on the server and
page through the results. We might build a server-side cursor to do this,
but with servers being as fast as they are now those are often unneeded. If
they want totals and subtotals we now can draw on a reporting solution that
does it for us without a lot of code. Reporting Services can perform many of
these operations very quickly and let the manager see the data in a variety
of ways--without bringing a single row to the client. Generally, we build
applications that don't return the database contents to the client. We
design the indexes and fetch mechanisms to leverage the server's ability to
quickly fetch smaller sets of rows. We use the right tool for the job.

hth
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
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.
__________________________________

"Charles A. Lackman" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hello,
>
> The interface that they are using displays all the rows from the database
> in
> a datagrid and also allows for sorting, and individual record selection
> (from the grid and also an interface which allows for type ahead searches
> on
> Last Name, Phone Number, etc also, the entries in the database are all
> capital and I format the returned data after I retrieve it - which takes
> about 2 seconds). The salesmanager wants to see everything, but the
> salemen
> only see what belongs to them. The problem occurs when they see ALL the
> records then refines them based on a single salesman then wants ALL of
> them
> again (A view would speed this up, but there is still the problem with the
> 3
> minutes wait to retrieve them). Under normal circumstances they will only
> see theirs and never need to see ALL of them.
>
> The issue is waiting 3 minutes to get ALL the records. When retrieved by
> salesman number only takes about 15 seconds. If the Salesmanager sends
> multiple queries they will have to wait over 30 minutes to get the records
> from the database because of resources.
>
> IBM told me that I was getting the records in the most efficient way
> possible (after 2 weeks of consulting them), but that each query uses
> massive resources from the computer. Most of the salesmen are using
> notebook computers.
>
> Anyway, I answered my own question, but if you are familuar with a better
> way of making this work (faster). I would be very interested in hearing
> your suggestions.
>
> Thank you for your time.
>
> Chuck
>
>
> "William (Bill) Vaughn" <(E-Mail Removed)> wrote in message
> news:eq3SG%(E-Mail Removed)...
> What is the user doing with 31,000 rows?
>
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> www.betav.com/blog/billva
> 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.
> __________________________________
>
> "Charles A. Lackman" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> Hello,
>>
>> I have an application that is using ODBC drivers that are eating VERY
>> Large
>> amounts to computer resources when performing queries against a database.
>> When the application first starts the query is performed at an acceptable
>> rate, but after the first query if another one is performed it can take
>> up
>> to 30 minutes to return 31,000 records.
>>
>> The ODBC Connection and Objects are being closed and/or destroyed each
>> time
>> a query is finished. When I look at the Driver Log File I see that about
>> 100 or more files and multiple processes are attached to the driver when
>> it
>> starts.
>>
>> Is there a way, that when the user selects a new search criteria, that
>> the
>> application terminates and then restarts itself?
>>
>> If so, is there a way in VB.NET code (in a click event) to cause this to
>> happen?
>>
>>
>> Thanks,
>>
>> Chuck
>>
>>
>
>
>