slow sql - select

[aiden]

Hi,

I generate several reports on a network based database with 5 tables. The reports are all based on select queries with inner joins to connect the tables. The main table has only about 20,000 records. Whichever query is run first takes several minutes to run. Any subsequent queries take seconds. When I launch my application I open the database and the 5 tables but this doesn't seem to affect the response time on these queries. I'm sure some of the delay may be network related but outside of servers,routers and switches, how can I cut the 1st response rate from minutes to the 2nd response rate of seconds?

Thanks in advance.

 

[chpicker]

My first thought is that you aren't using Rushmore-optimized Join conditions and/or WHERE clauses. If you post your SQL statement, maybe we can help a little more. In any case, here are some pointers for phrasing:

1) Make sure your WHERE clause or JOIN condition is on an index key in the table(s) involved.

2) Make sure your expressions match the index key EXACTLY. For example, if your index is on UPPER(myField), your WHERE or JOIN clause must also use UPPER(myField).

Ian