Solved

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

Posted on 2014-01-21
7
918 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

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.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

770 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