Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2000-11-02 : 09:02:19
|
Bruce writes "Platform: MSSQL 7, Windows 2000, dual pentium server, raid 5 disk subsystem, 1 gig memory. All the latest service packs.
I have tried just about every optimizing/performance tip I can find to optimize the processing speed of stored procedures written in Transact SQL with very little success. I've used the profiler and index analyzer, I've optimized the queries, fine tuned the indexes, replaced as many declared cursors as I can with selects. Nothing seems to make much difference. I have a process that takes medical staff daily activities and creates medical charges to client's insurances. There are all kinds of checks that have to be made on the activities to make sure they are billable, the service is authorized for the client, the staff can bill to the client's particular insurances based on staff credentials, as well as calculating the amount of the charge for the service which can vary based on staff credentials etc, etc. There are so many checks and calculations involved that I have to use a declared cursor and row by row process the activities to create the charges.
The processing speed at this point makes the product virtually unusable. For comparison, 2000 staff services (rows) takes about 20 minutes to process with stored procedures in MSSQL 7.
Essentially the same process with basically the same data in an old DOS based Clipper app using Xbase files takes 2 minutes.
How do you achieve any thing approaching reasonable performance when processing data in MSSQL? Is there something I'm missing about stored procedures? The speed of entering/editing/reporting data is fine, the problem comes when trying to batch process data causing many updates/inserts to occur.
Any suggestions would be appreciated.
Thanks" |
|
|
|
|
|
|
|
Poor processing speed for Stored Procedures