Solved

Improve Query Performance

Posted on 2011-03-09
6
624 Views
Last Modified: 2012-06-21
I have a a Excel 2007 spreadsheet that using SQL queries from an Oracle database. I then use MS Access 2007 linked tables to retrive data from this workbook and to create further queries. I then use Crystal Reports 8 to report on the Access database using a simpe filter and sub reports. As you would imagine this query is quite slow.

What is the best method\ syntax to add indexes to either or both the Excel spreadsheet and MS Access database to improve performance. I do not have update capability on the Oracle database.
0
Comment
Question by:JayceW
[X]
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
6 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 350 total points
ID: 35083688
"As you would imagine this query is quite slow. "

Which query are you referring to?


Subreports in crystal can be slow in some cases.
0
 

Author Comment

by:JayceW
ID: 35083787
I am referring to the Crystal report.

As you say I appreciate that they can be slow. This one however takes over 3 hours to complete so I am looking at potential ways to accelerate the query at the Worksheet\ MS Access database level.

0
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 350 total points
ID: 35083867
Can you post the sql being used by crystal .
(Database> show sql query)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35084232
Why does Excel need to be involved in this process?
0
 
LVL 101

Assisted Solution

by:mlmcc
mlmcc earned 150 total points
ID: 35084920
Can you use Crystal directly against the Oracle database?

mlmcc
0
 

Author Closing Comment

by:JayceW
ID: 35094176
An Error in my joni criteria solved the issue but the comments were useful
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

705 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