Re: Performance Issue with Database Connection

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: RB Smissaert (bartsmissaert_at_blueyonder.co.uk)
Date: 08/13/04 

Date: Fri, 13 Aug 2004 14:35:55 +0100

Your queries maybe inefficient or there
maybe something wrong with the VBA code.
If you post the code and queries you might get
a suggestion how to improve the speed.

RBS

"Ctal" <notme@nothere.com> wrote in message
news:eq_Sc.192518$tH1.9669985@twister.southeast.rr.com...
> I have an app that runs several queries depending on user input. The data
> source is an Access database that's right at the 2GB limit. I stored
> numerous queries in the database to cover all the possible user selections
> from the Excel app. I'd read that stored queries are a bit faster than
just
> sending the SQL script. Database resides on a LAN
>
> Average time on these varies from 2 1/2 mins to 5 mins. From listening to
> users (especially my boss) we need to improve this time.
>
> I've tried every way I could think of to run this and nothing shortens the
> time. 99.5% of the wait is while opening the recordsets.
>
> My question:
>
> Due to the size of this db (and its only one table but with 1.5M records)
> would moving this to a SQL table and querying off of that speed this up?
I
> do some other stuff against our main SQL tables and they are painfully
slow,
> but that's with lots of joins and far more records. This would just be
the
> one table.
>
>

Relevant Pages

  • Advice Requested - We have created a decent Database - Now we need some decent Queries!
    ... We only need help with the SQL 2000 queries. ... dynamically build the query, as we don't know exactly how the user will ... query the database,. ...
    (microsoft.public.sqlserver.programming)
  • RE: convert query into the VB code
    ... you database size issue really is a non issue. ... I would suggest you just create a function that executes all the queries. ... "Juli Intern" wrote: ... I don't really care either it SQL within VB or Recordset procedure. ...
    (microsoft.public.access.queries)
  • Re: Inconsistent results with ADO.NET
    ... and also to use Sql Profiler to see what commands are executed. ... Making the connection to the database and filling the dataset ... ADO datasets with data for both of the main database queries. ... while they both return data on my development machine. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Give a man a fish?
    ... You can actually learn much about SQL just by switching your queries to SQL view. ... Next, you will be learning about forms, and the various controls you can put on them The controls and the form both have events that fire when something happens. ... Functions such as the Spacein your example are part of the VBA code, but you can use them in contexts outside of code. ...
    (microsoft.public.access.reports)
  • =?windows-1252?Q?Re=3A_DBA_Guru=3A_=2832bit=2C_2005=2C_600DB=92s=2C_Memory_issues?= =?windows
    ...  The database supports 3rd party software that has about 650 ... that kicks off 30 or so queries all at the same time. ... SQL Server’s memory. ... and handing out too much memory. ...
    (microsoft.public.sqlserver.server)