Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Query is outputting slow only when using transferspreadsheet method or append query

Posted on 2014-01-21
7
929 Views
Last Modified: 2014-01-24
I have a query that runs fast enough in if I'm just viewing it. It's returning about 29K records. in 20-30 seconds. I can view any record, etc. Tables have indexes. My problem is when I try to output it using transfer spreadsheet or make the query an append query it's taking 30+ minutes to run. I haven't had this problem before. I've done more complex queries and they haven't given me this trouble. Could bitlocker be causing this problem?
0
Comment
Question by:stopher2475
  • 4
  • 2
7 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 39797028
If you move the application to another machine that does not have BitLocker, does the issue go away? If so, then you could perhaps conclude that BitLocker is the culprit.

Is this a split application - that is, you have a Backend (Tables only) and a FrontEnd (everything else)? If it is, try moving the Backend to the local machine temporarily and see it that speeds up your process. If it does, then the network is the bottleneck and you'll need to take steps to improve the speed and reliability.

Converting to an Append query often causes performance issues since Access needs to do quite a bit of work behind the scenes to insure the query can be executed. Essentially, Access checks all Indexes and Foreign Key fields to insure they comply with all "rules", and also checks things like Defaults, field lengths/types etc before running the append.

Can you give more information about the table? Number/type of fields, for example? Any Memo, Attachment or OLE fields?
0
 
LVL 2

Author Comment

by:stopher2475
ID: 39797074
I moved the backend to my desktop. I'll try it on another machine when I get home tonight. I was going to bring in my personal machine and try that. No memo or attachment fields I see but I will take your suggestion and look through the tables. I might have missed something.
0
 
LVL 84
ID: 39797764
I moved the backend to my desktop.
Any change after you did that?
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 2

Author Comment

by:stopher2475
ID: 39797952
No it's still real slow. The odd thing is I can view the query without much trouble. It only takes a long time when I try to output it to excel with copyrecordset or I use an append version of the query. Target table only has 2 indexed fields. Could the indexes be corrupted or something. I tried compacting and repair. I'm trying to copy it to a personal laptop now because my home desktop has a newer 64bit version of office that isn't playing nicely. May have to install access first. Will update on progress soon.
0
 
LVL 2

Author Comment

by:stopher2475
ID: 39799034
Tried to run it on my personal laptop. Still really slow. Not sure why. It's not a very complicated query.
I finally go it runnable by doing updates on 2 of the tables for some lookups rather than joins. That let me remove 2 tables from the query and now the thing outputs to excel in 10 seconds rather than 30 minutes. Here's something I've noticed. When it outputs to excel I have a whole lot of the columns that are numbers stored as text. Would that have an effect on output speed to excel?
I also had a join field getting truncated so I had 29K records with no match which I think slowed it down.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 39801505
I recommend that you instantiate an Excel.Application object and use the CopyFromRecordset Range method to transfer your data.  There are other methods I've detailed in my Fast Data Push to Excel article (http:A_2253.html ), but most people find the recordset approach the simplest.
0
 
LVL 2

Author Comment

by:stopher2475
ID: 39806565
I am using the CopyFromRecordset method. Sorry I had put TransferSpreadsheet by mistake.
My final solution was to load the add blank fields to the table I was joining two tables to in the final query and doing updates rather than joins. It runs in a minute or so and doesn't hang for45 min. I guess it's just more efficient than 2 outer joins.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question