Hi Our application has a function where it will calculate various totals on employee schedules. One very large group's schedule was taking about 40 seconds to process before a defrag, dump, load, defrag was performed. After the rebuild/defrag process the user reported that the process was taking about 35 seconds. Besides making sure that there is enough memory to cache the entire database, and the rebuild/defrag process described above, what are some of the other ways to speed up sqla database processing? I realize that we may have to look at redesigning the schedule totaling function, but I want to make sure that sqla is setup for its fastest possible performance.
asked 06 Jul '13, 18:24
We are using SQLA for intensive calculations (less data retrieval). Some pointers:
As Volker stated, every use-case is different, but you cannot go wrong with these steps (especially the first one). Good luck!
I would first make sure your database server and client run on the latest EBF patch and if possible the last release of SQL Anywhere (16). We've encountered several major performance increases when installing the latest patch (on version 11.0.1).
Indexes can really speed up things. I would check which queries are executed during your 35/40s period and try to optimize them by adding specific indexes. Remember that adding indexes also has drawbacks !
answered 08 Jul '13, 09:58
When you follow Liam's advice to use the procedure profiler, you may discover one or more slow SQL queries.
If you obtain representative and meaningful plans (Graphical Plan With Statistics) for those queries, maybe you can see where to apply Michael's advice to create an index.
You could also save and post the saplan files here.
...and/or run the Index Consultant.
...and/or run Database Tracing In 50 Easy Steps ( that's a last resort :)