Hi all, I'm sorry to bump this ... but it's become quite a crippling issue for my application so I really need to try and find an answer. I have figured out a little more about the problem. Although the table in question has a lot of rows, the slow performance is triggered by an insert of a single row with a column value matching one of the columns in the where clauses in the query. When it is inserted, it is the only row in the table with this value. The execution plan does not actually change, but the performance of it does dramatically.
The paradox is that if I stop the application (and database, since it is embedded) and restart it then the optimizer comes up with different values and query plan changes and everything is fast again. So it does this: 1. starts off fast using execution plan A 2. a single insert happens, query continues using execution plan A ( but it is now horrifically slow) 3. stop DB and start again, no change in database => now uses execution plan B (fast) So the question is, why does the optimizer suddenly get much smarter just because I restarted the database? Is it caching the execution plan perhaps? Can I make it be this smart all the time somehow? Thanks for any help! Simon
