469,090 Members | 1,122 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,090 developers. It's quick & easy.

Need advice on speeding up a query

I have about 20K records that result from the following query. Front end for the
database is ACCESS97 and pulling up 20K records makes a huge performance hit.
For the form in question I am using PASSTHROUGH type query (the one that just
passes everything straight to server without ODBC).
NOTE: souce_for_inquiries_form is the TEMP table and is searchable in the from
(it feeds a pull-down list).

SELECT inquiries.inquiry_id, inquiries.contact_id, inquiries.indiv_contact_id,
inquiries.phone, inquiries.fax, inquiries.agent_id, inquiries.inquiry_date,
inquiries.event_type, inquiries.letter_type, inquiries.event_date,
inquiries.event_date_general, inquiries.event_location, inquiries.guests,
inquiries.hours, inquiries.budget, inquiries.event_description,
inquiries.talent_description, inquiries.past_use, inquiries.referred_by,
inquiries.date_sent, inquiries.end_user, inquiries.event_id, inquiries.notes,
source_for_inquiries_form.organization,
source_for_inquiries_form.mailing_address_1,
source_for_inquiries_form.mailing_address_2, source_for_inquiries_form.city,
source_for_inquiries_form.state, source_for_inquiries_form.zip,
source_for_inquiries_form.contact_type, individual_contacts.contact_name_first,
individual_contacts.contact_name_last, individual_contacts.contact_prefix,
individual_contacts.contact_title, individual_contacts.email
FROM inquiries
LEFT JOIN individual_contacts ON inquiries.indiv_contact_id =
individual_contacts.indiv_contact_id
INNER JOIN contacts AS source_for_inquiries_form ON inquiries.contact_id =
source_for_inquiries_form.contact_id
ORDER BY inquiries.inquiry_id DESC;
I want to hear all the suggestions. Tired of ACCESS97 front end, but at the same
time so many of the forms have VB code in it and have sub-forms, that so far no
one gave me any good ideas how to move the front end into PHP or something else,
or combination of something. I run Linux for my servers and I don't plan to use
Win product anymore (whatever licenses we have, we will just live with them as
long as we can and then Linux here we come for desktops).

Apolinaras "Apollo" Sinkevicius
Carmel Music & Entertainment, LLC
web-site: http://carmelme.com
________________________________
Having an event in Chicago, or would you like to bring Chicago entertainment
to your event? Give Carmel Music & Entertainment a call for the finest
entertainment available in Chicago.

-------------------------------------------------
Visit CARMEL MUSIC & ENTERTAINMENT website http://carmelme.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/my***********...ie.nctu.edu.tw

Jul 19 '05 #1
3 1770
Apolinarus -
usually when the speed starts to suck I tend to look at the columns utilized
in any selection criteria .

you have these columns used in joins:
inquiries.indiv_contact_id
individual_contacts.indiv_contact_id
contacts.contact_id
inquiries.contact_id

are they indexed ??? if not - index them and try again ?
should see a significant drop in return time if you did originally have
these columns indexed.
Lemme know your thoughts..
mondo regards [Bill]
--
William Sanders / Electronic Filing Group Remove the DOT BOB to reply via
email.
FREE LONG DISTANCE -> mailto:ex********@efgroup.net
mySql / VFP / MS-SQL
""Apollo (Carmel Entertainment)"" <li***@carmelme.com> wrote in message
news:bh***********@FreeBSD.csie.NCTU.edu.tw...
I have about 20K records that result from the following query. Front end for the database is ACCESS97 and pulling up 20K records makes a huge performance hit. For the form in question I am using PASSTHROUGH type query (the one that just passes everything straight to server without ODBC).
NOTE: souce_for_inquiries_form is the TEMP table and is searchable in the from (it feeds a pull-down list).

[snip]
Jul 19 '05 #2
Apolinarus -
usually when the speed starts to suck I tend to look at the columns utilized
in any selection criteria .

you have these columns used in joins:
inquiries.indiv_contact_id
individual_contacts.indiv_contact_id
contacts.contact_id
inquiries.contact_id

are they indexed ??? if not - index them and try again ?
should see a significant drop in return time if you did originally have
these columns indexed.
Lemme know your thoughts..
mondo regards [Bill]
--
William Sanders / Electronic Filing Group Remove the DOT BOB to reply via
email.
FREE LONG DISTANCE -> mailto:ex********@efgroup.net
mySql / VFP / MS-SQL
""Apollo (Carmel Entertainment)"" <li***@carmelme.com> wrote in message
news:bh***********@FreeBSD.csie.NCTU.edu.tw...
I have about 20K records that result from the following query. Front end for the database is ACCESS97 and pulling up 20K records makes a huge performance hit. For the form in question I am using PASSTHROUGH type query (the one that just passes everything straight to server without ODBC).
NOTE: souce_for_inquiries_form is the TEMP table and is searchable in the from (it feeds a pull-down list).

[snip]
Jul 19 '05 #3
Apolinarus -
usually when the speed starts to suck I tend to look at the columns utilized
in any selection criteria .

you have these columns used in joins:
inquiries.indiv_contact_id
individual_contacts.indiv_contact_id
contacts.contact_id
inquiries.contact_id

are they indexed ??? if not - index them and try again ?
should see a significant drop in return time if you did originally have
these columns indexed.
Lemme know your thoughts..
mondo regards [Bill]
--
William Sanders / Electronic Filing Group Remove the DOT BOB to reply via
email.
FREE LONG DISTANCE -> mailto:ex********@efgroup.net
mySql / VFP / MS-SQL
""Apollo (Carmel Entertainment)"" <li***@carmelme.com> wrote in message
news:bh***********@FreeBSD.csie.NCTU.edu.tw...
I have about 20K records that result from the following query. Front end for the database is ACCESS97 and pulling up 20K records makes a huge performance hit. For the form in question I am using PASSTHROUGH type query (the one that just passes everything straight to server without ODBC).
NOTE: souce_for_inquiries_form is the TEMP table and is searchable in the from (it feeds a pull-down list).

[snip]
Jul 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Bennett Haselton | last post: by
reply views Thread by Apollo | last post: by
3 posts views Thread by David | last post: by
2 posts views Thread by Wayne | last post: by
reply views Thread by Tiga | last post: by
2 posts views Thread by simon_w3 | last post: by
11 posts views Thread by Dan Sugalski | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.