?
Solved

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

Posted on 2014-01-21
7
Medium Priority
?
993 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 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1500 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 85
ID: 39797764
I moved the backend to my desktop.
Any change after you did that?
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
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 46

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

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

749 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