Solved

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

Posted on 2014-01-21
7
891 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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now