Toad World® Forums

Resource considerations


#1

I’m new to the Optimizer and I’ve been doing a bit of experimenting to get familiar with it. In some of my experiments I’ve run the same SQL in different databases for comparison.

In some cases, the scenario recommended from the execution in the development database is not the same as the one recommended when executed in the production database.

The databases are identical in structure, the only difference is in the size. That tells me that for this to be useful, I need to execute it in our production database. With serious SQL, the optimizer can run for a long time, but I don’t know what it is doing on the server. Should I be concerned about the resources used by the optimizer? I don’t want to drag down performance.


#2

Hi Roddy,

It is true that optimizing SQL sometimes can be challenging. In particular, you would need to execute a SQL to tell the actual performance of it.

One suggestion is to have a development database that can simulate as much as possible the production database. Some lucky users they have the resource to create a development database from the full backup of the production database. However, most users would be like you whose database size is smaller that the production database. If this is the case, it is important to have proportional data volume in the development database. For example, if you have 1,000,000 and 1,000 records in TableA and TableB in your production database, then it would be better to have 100,000 and 100 records in TableA and TableB in the development database than to have 100,000 records in both tables. Keeping the data volume proportional helps getting consistent performance across production and development databases.

Once you have a development database that is “like” the production database, you would be able to use it in the first round to test run all alternatives and find out a few potentially better SQL. Then you can execute those better SQL in a second round using the production database. In this way, you would be able to reduce the number of SQL executed against your production database.

Lastly, there are different termination criteria that you can choose from the Options. These criteria would help you terminate a SQL when it is running too long. One of my favorite options is to terminate by the fastest SQL. Using this option would allow you to test all SQL using the shortest time. When a faster SQL is found, the product will use this new time to terminate the next SQL, hence shortening the overall time spent on testing.

I hope this information help. Please feel free to let me know if you have further questions.

Thanks,
Alex


#3

Alex,

I like that advice, thanks. Pick the best from development to make a final pick in production. That will work well for me. If I’m still concerned, I can run it on the weekend when usage is lighter.

Thanks again,
Roddy


#4

Hi Roddy,

You are welcome. Please feel free to post questions if you have any. We are always delighted to hear from users how they use the product.

Thanks,
Alex