1. If using MS Access as your query tool, always use Pass-Through (BB5746) queries as this will always be the fastest method to query the database.
2. Use the RODBA SQL Query Tool plug-in to help tune the queries by using the Plan tab to see how the query will be executed. Although it may take a while to get familiar with reading plans it is fairly easy to understand.
- If it says, Scan 'TableName' sequentially it is going to be slow if the table that is being queried on is big.
- If it says Scan 'TableName' using index 'IndexName' or using foreign key 'FKName' the query will be faster.
The plan describes the series of nested operations that will be done for each table in the query to see if it meets the criteria. Ideally the outer most operation will take advantage of an index or foreign key. To tell which fields are indexed look at the Tables tab in the plug-in and then click on the Indexes tab for a given table.
3. Avoid using OR in the WHERE clause. If the OR causes the plan to not use an index, then consider using a union query instead. For example, instead of:
WHERE GIFT.DTE = '1/1/2000' OR GIFT.DTE= '1/2/2000';
is equivalent to
Select * from REWIN.GIFT
WHERE GIFT.DTE = '1/1/2000'
WHERE GIFT.DTE = '1/2/2000' ;
Now this is not a great example because you can see from the plan that the first SQL is still able to take advantage of the index and will still be fast. In other words the ASA optimizer worked good. But for some more complex queries with Ors this tip can sometimes help
4. Include only the fields that you need in the
SELECT clause. Don't use
SELECT * if you just need a couple of fields.
5. Use the built in RE:Query designer to build a similar query and use the View SQL menu option to see the SQL and plan that it built.
6. Consider pulling down (importing) a local subset of data (such as into a Jet [Microsoft Access] table) and then doing more complex operations there.
7. Use an inner join rather than an outer join whenever possible. For example, if there is a where clause that includes criteria for a table on the right hand side of the join then a left join is probably not needed because there will never be NULL rows in the right hand table that meet the criteria.
8. Rebuilding the database indexes can improve query performance. (BB120081)
9. Review online articles on how to improve the speed of SQL queries (https://encrypted.google.com/#q=how+to+improve+speed+of+microsoft+sql+query&spell=1)
Disclaimer: Blackbaud provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes you are familiar with Structured Query Language and the tools used to create and modify SQL statements and Crystal Reports. Blackbaud Customer Support may help explain the functionality of a particular procedure, but we will not modify, or assist you with modifying, these examples to provide additional functionality.
Note: We provide links to third-party websites in an effort to help you resolve your issue. We are not responsible for the information on third-party websites, and we cannot assist with implementing resolutions from these websites.