How to accelerate query processing?

[Guest]

Hi,

Are there any way to accelerate a query-processing? In my case, i will run a
query and in this query there is another query?

Best regards,

Pat

 

[Guest]

I don't think any one can help you without knowing hoe many tables involved,
the relationship between them, how many records in each table, what do you
want to filter, are the table created in Access or SQL.
There are many ways to improve your query,
1. connection - usually inner joins are quicker then left or right join.
2. query within query sometimes make it quicker.
3. Local tables instead of attached table
etc

 

[Dirk Goldgar]

I don't think any one can help you without knowing hoe many tables
involved, the relationship between them, how many records in each
table, what do you want to filter, are the table created in Access or
SQL.
There are many ways to improve your query,
1. connection - usually inner joins are quicker then left or right
join.
2. query within query sometimes make it quicker.
3. Local tables instead of attached table
etc

And don't forget indexing the fields that are being joined, sorted, or
filtered on!

 

[Guest]

Hi Ofer,

Thanks for your tips!I think i already done what you mentioned.
My situation is I use a 'MS Access' frontend, it connect to a 'SQL-database'
via ODBC-connection.

The first query consist 10 tables, five of them have not more than 100
records, 2 with about 15000 records and 2 with about 70000 records. Most of
the joins between the tables is inner join( 1 to 1 relationship) and they all
have a primary-key. This query also has 4 selection criteria's.( They spread
out in four different tables) The output of the query include 15-20
fields.(One is the primary key for table-X.)

The second query is actually the same as the first-query without the
selection-criteria's. Important for second query is that for one of the
field(primary key of table X) i added the following criteria. (Not in select
from Query1). I think this affect the performance of the query.

What i already try to do is for query1, i just slect one fields as output.
But it doesn't seemed to help.

I hope the situation is clear now, and hope that anyone can help me!

Best regards,

Patrick

 

[Guest]

Hi Patrick, sorry for the delay
SQL tables is another story, if you havn't use it, then you should tern the
quries into Pass Through queries.
Its quries that placed and build in your MDB but operate on your SQL server,
and return only the record necessary for you, less trafic on your network,
just like views.

Its actualy views in your MDB.
If you interested I'll give you more hints about it.
You can't use Access tables in it, they have to be places on your SQL.

 

[Guest]

Hi Olfer,

Thanks for your reply, but I actually need to keep the query in my database.
only the data will be stored in the sql-server. And no views,
stored-procedure or user-defined function.

Have you get any other idea to accelerate the query-processing?

Best regards,

Pat

 

[Rick Brandt]

Hi Olfer,

Thanks for your reply, but I actually need to keep the query in my
database. only the data will be stored in the sql-server. And no
views, stored-procedure or user-defined function.

Have you get any other idea to accelerate the query-processing?

Best regards,

A pass-through query _is_ stored in your database. A "View" or Stored Procedure
are things you would create on the server, but a pass-through is just a special
kind of Access query.

 

[Guest]

Hi Ofer,

Can you give me some hints for "Pass Through" query? Any example?

Best regards,

Patrick

 

[Guest]

did you try it?
its an access query, but works like a view, its much quicker.

build first as an access query, use the name of the table as tehy apear in
your mdb.
or you can build it in your SQL as view, dont save it, just copy the syntax
to access.
if you have few enviroment as develope qa production, you have to remember
changing the server this queries attach to.

try it, it's worth it