473,837 Members | 1,595 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Large table/slow query/ can performance be improved?

I am having performance issues on a SQL query in Access. My query is
accessing and joining several tables (one very large one). The tables are
linked ODBC. The client submits the query to the server, separated by
several states. It appears the query is retrieving gigs of data from the
table and processing the joins on the client. Is there away to perform more
of the work on the server there by minimizing the amount of extraneous table
data moving across the network and improving performance (woefully slow
about 6 hours)?
Jul 20 '05 #1
3 23633

"Robert" <st*******@whit ehouse.gov> wrote in message
news:HL******** @news.boeing.co m...
I am having performance issues on a SQL query in Access. My query is
accessing and joining several tables (one very large one). The tables are
linked ODBC. The client submits the query to the server, separated by
several states. It appears the query is retrieving gigs of data from the
table and processing the joins on the client. Is there away to perform more of the work on the server there by minimizing the amount of extraneous table data moving across the network and improving performance (woefully slow
about 6 hours)?
Hmm, I haven't touched Access in years, but I recall it supporting what I
think it called Pass Thru Queries?

Or another option (perhaps ultimately your best one) is rewrite it as a
stored proc on the server.

If it's taking 6 hours, unless you're joining multigig table and doing
something like outer joins, I've got to imagine you're right, it's Access
trying to process things locally.


Jul 20 '05 #2
Hi,

These are some tips from my side.

1. Put indexes on column that is frequently used in query. This will
help database engine to search data quickly.
2. If the Recordset has less than 500 records, then the snapshot
Recordset will be faster than the dynaset Recordset.
3. Make query selection/criteria so as resultset will be small.
Finally, can you elaborate/post what exactly u r trying to do and
achive.

Amit


"Robert" <st*******@whit ehouse.gov> wrote in message news:<HL******* *@news.boeing.c om>...
I am having performance issues on a SQL query in Access. My query is
accessing and joining several tables (one very large one). The tables are
linked ODBC. The client submits the query to the server, separated by
several states. It appears the query is retrieving gigs of data from the
table and processing the joins on the client. Is there away to perform more
of the work on the server there by minimizing the amount of extraneous table
data moving across the network and improving performance (woefully slow
about 6 hours)?

Jul 20 '05 #3
As long as you're not using any client-side functions (Access or user-defined),
you should be able to make this into a Pass-Through query.

1) Copy the SQL Statement to the clipboard (or better yet, to Notepad)

2) In Access, make a new query, but don't choose any tables.

3) Choose Query/SQL Specific>Pass-Through

4) Paste the SQL Statement in there

5) Choose View/Properties

6) Set the Connect string (and ODBC Timeout!) appropriately

Run the query.

I have never seen an Access query take 6 hours to run...and I have an Access
database linked to over 68 MILLION records. Admittedly, I would never even WAIT
that long to find out if it could ever complete!

The slowest Access queries I've seen are the ones that I call "Query of a query
of a query...etc." with all records included the whole time, and a criteria at
the end; especially if Access or user-defined (VBA) function are invovled!

You really should learn the advantages of SQL Server's "SQL langauange" vs
Access. My favorite example is the FULL OUTER JOIN...with one statement in SQL
Server you get both sets of records from the eaither side of join whether or
not they match...can't do that in Access without making three queries! (OUTER,
Non-Match OUTER, then UNION)

Althogh the IIF in Access is convenient, it insists on evaluating both
outcomes...SQL Server's "CASE" construct makes much more sense.

If your query DOES use Access and/or user-defined VBA Functions, then you
should have the whole process converted to a stored procedure, and the use a
Pass-Through query to call the Stored Procedure. In all seriousness, EVERY
query you need should be converted to paramaterized Stored Procedures, and
called via Pass-Through....this is the best way to optimize performance.

CAVEAT: Pass-Through queries cannot be used as record sources for
linkchild/master situations (That's a JET-only feature), but you can always
program the same functionality yourself!

I'd be interested in seeing the SQL Statement of the 6-hr query.
Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
22543
by: Matt Liverance | last post by:
I REALLY dont want to switch to oracle :( but I cant get these tables working any faster. I've got 2 dedicated servers, each with a slave, all run 32gig 15k rpm raid 5 on u320 perc raid cards, dell 2600/4600's with single channel backplanes (new ones will have dual channel) All have 2 gig of ram, but I've never seen mysql use more than 300mb of ram.
4
10438
by: M Wells | last post by:
Hi All, I have a table that currently contains approx. 8 million records. I'm running a SELECT query against this table that in some circumstances is either very quick (ie results returned in Query Analyzer almost instantaneously), or very slow (ie 30 to 40 seconds to return results), and I'm trying to work out how I improve performance. Essentially the query I'm running is nothing more complex than:
1
1684
by: Gary Wales | last post by:
We have two main query types running against a table of some 2 million rows and have gotten query response down to well under a second by using the right indexes. Problem is that we are running an advertising campaign that brings a concentrated block of users to the site. When this happens one of the queries which relies on a particluar index comes severely of the rails and can take up to 2 minutes filling the slow query log for 15 to 20...
2
489
by: Dave Weaver | last post by:
I'm having severe performance issues with a conceptually simple database. The database has one table, containing weather observations. The table currently has about 13.5 million rows, and is being updated constantly. The database is running on a dual 550MHz PIII with 512MB RAM. On the whole, queries are of the form: SELECT ? FROM obs WHERE station = ? AND valid_time < ? AND valid_time > ? or:
8
3276
by: Együd Csaba | last post by:
Hi All, how can I improve the query performance in the following situation: I have a big (4.5+ million rows) table. One query takes approx. 9 sec to finish resulting ~10000 rows. But if I run simultaneously 4 similar queries it takes nearly 5 minutes instead of 4 times 9 seconds or something near of that. here is a sample query: select mertido, fomeazon, ertektipus, mertertek from t_me30 where fomeazon in (select distinct fomeazon...
1
2062
by: Mats Kling | last post by:
Hi all, We are logging approx. 3 million records every day into a history table. Last week we ran into the 64 GB limit in UDB 8 so we recreated the table with 8 k pagesize to get some breathingroom before we hit the 128 GB limit. We are considering partitioning and I just wanted to check with you that our proposal is the best one:
29
5519
by: wizofaus | last post by:
I previously posted about a problem where it seemed that changing the case of the word "BY" in a SELECT query was causing it to run much much faster. Now I've hit the same thing again, where basically almost any change I make to how the query is executed (so that it still performs the same function) causes the performance to jump from a dismal 7 or 8 seconds to instantaneous. It's a very simple query of the form: SELECT Min(MyValue)...
1
2107
by: clarke | last post by:
Hi all, am fairly new to DB's and wondering if anyone can help me out. I am writing a web app that requires a frequent query that is variable in a large database. The table has 2.6 million rows. Columns | A | B | C | D | E | F |
0
9682
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10562
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10617
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10263
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9391
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7803
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6989
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5666
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
4036
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.