Delfi ebusiness sqlServer application
-
I was hired to do a performance tuning on this "bag of ...", but haven't been able to do much. I'll give a brief overview of some things I've found to see if anyone has any possible solution.
Application written in delfi, uses BDE ( Borland database engine :crazy
Seems not to be any good. Actually the server has to be rebooted 2,3... more times a day because it stops working. If anyone has any experience with this, even though it's not part of the contract let me know...all tables have a clustered index with companyid, counter. The counter table has a companyid,counterid,starnumber,stopnumber,current,last updated.
Besides the obvious defragmenting and indexes...
Multicompany application
over 3,000 tables
2 tables over 100,000,000 records.
1 table with counters
very high number of trans/sec
apparently every insert transaction (handled in code, no sp ) has to consult the counters table, getid, updateid, then insert record. The large issue here is that usually every insert( sale ) involves at least 50 inserts, and are done sequentially.
Some ideas to improve some what the process, are better storage infrastructure, log, counter table, data on separate controllers. Thought I think it’ll real not improve performance because of the application architecture. Though because of the clustered indexes not really sure how to avoid high fragmentation on tables.
Because of the number of sql statements being received, improve CPU speed. And increase memory on server to be able to maintain large table indexes on ram.
Thanks,
Juan
-
This was removed by the editor as SPAM
-
Ether break up the 100,000,000 record tables into a partitioned view or create new files and filegroups and rebuild the tables on to them to split up the I/O accross more drives and files. More files will allow SQL to multithread index rebuilds as well. I would look at disk subsystems for any possible improvement. Check out SQL Server 2000 Performance Tuning it has a ton of good stuff on hardware and disk systems.
Wes
-
check the settings on the BDE(Borland Database Engine) which often affects how the database processes data.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply