Union join slows system right down

Results 1 to 2 of 2

Thread: Union join slows system right down

  1. #1
    Join Date
    Dec 1969

    Default Union join slows system right down

    Hi, I have two problems with an access database I am querying using SQL commands.<BR><BR>1) I need to insert some constant text and an auto number into one of my database fields(have a combination of text and autonumber for primary key),<BR>e.g.<BR><BR>CAT_NO - field name<BR><BR>DANCE1<BR>DANCE2<BR>DANCE3....etc<BR>< BR>if you know what I mean? All my database tables have been converted from Excel worksheets, would it be easier to insert the field into the worksheet before I import it into Access??If so, how?<BR><BR>2) If I perform a union join between two tables, it really slows the system down, and it never actually finds the records which I know are there. The statement is part of a search engine for a database of music records. &#039searchby&#039 and &#039searchfor&#039 are both passed in from a form on the previous page. This is the SQL statement;<BR><BR>"SELECT artist,title,remarks<BR> FROM dance_vinyl <BR> UNION SELECT artist,title,remarks<BR> FROM dance_cd<BR> WHERE "&searchby" = &#039"&searchfor&"&#039 <BR> ORDER BY artist"<BR><BR>The search works fine when only searching one table, but takes approx 20 seconds until it&#039s done when I perform the join and even then doesn&#039t return any records.<BR><BR>Many thanks in advance<BR><BR>Dominic<BR>

  2. #2
    Join Date
    Dec 1969

    Default RE: Union join slows system right down

    1. The only way create a true Text + Autonumber would be to have two Fields. If the new records would be inserted from a Access Form, it would be easy to concatenate "Dance" & Num. But if possible just use the AutoNumber. Possibly sort the Excel data first, then the data will be assigned the Autonum in order ie: 1,2,3 unless you use random auto. Other Excel tables you could get new ID using database lookup.<BR><BR>2. Union queries are hard to optimize, as opposed to Joins. I suggest trying a named query. Access creates its own Index for Named queries, then either filter the Recordset after it is opened, or use the Named query in the SQL as SELECT * FROM unqryArtists WHERE "&searchby" = "&#039&searchfor&&#039".<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts