SQL Server Performance

Order placing application taking time to place an order

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by sekharabcs, Sep 13, 2009.

  1. sekharabcs New Member

    Hi All,
    Our client has an order placing system which is used by customer services to place an order. Client is reporting that the application (developed in vb6) is taking long time to add an order.
    In SQL point of view, many procedures are called when an order is placed. Around 10-15 stored procedures called depending on the type of order placed. For ex. to get a price for order, carriage charge several procedures are called. Data is retrieved and inserted from/to many tables.
    I have checked the data in master tables and found that the data is stored since 2007. There are around 3 lakh records in each master table.
    Could anyone please help me in solving the performance issues. Currently I am analysing the code for the SPs called during adding an order. I am getting the list of SPs called by running the profiler and trying to add an order in test system.
    I have an idea of archiving the data to a datawarehouse, however, I am not proficient in DWH and hence It would be great if some one shows me the approaches to accomplish this.
    If we archive the data, then we should also look into the feature of retrieving the archived data as Customer Services will be using the application for Querying very frequently. How to handle this from front-end if we archive the old data to a new database?
    Please suggest me.
  2. FrankKalis Moderator

    I think you may need to give more information on your problem. Right now it could be anything from application programming, network, storage system, server programming, database schema...
    The amount of data you store is hardly the reason. That's nothing for a proper system.
    First thing I would probably do this run a trace capturing long running queries, maybe filtered to thos procedures you suspect to be the culprits.
  3. ghemant Moderator

    I second Frank's suggestion, its really not easy to say the exact culprit. Looking at the number of records I don't think it would create a problem; would suggest you to see the execution plan for the SPs involving. If I am not wrong there will be a select query to inventory table to check if it has suffice stock to execute the order, check the execution plan of those queries as well.
    If you give precise information it would be easy to give you the possible work around, but at this point of time it could be a problem with network, particular system or server hardware.
  4. sekharabcs New Member

    Thanks Frank and Hemant, I will analyse the queries in Stored Procedures and execution plans. I will also need to look into network related things like hardware, network traffic. Many thanks for your suggestions.
  5. satya Moderator

    Have you taken care of database sizing and usage for next 3 or 5 years?
    It is better now to act and cater the hardware as per the plan, otherwise no matter what kind of code you execute will have serious performance implications.
  6. sekharabcs New Member

    Thanks for your reply Satya. Currently we do not have any data warehouse and also we have all applications/servrices running on a single server (Intel® Xeon® 5140 CPU 2.33 GHz and 4 GB RAM). We are planning to keep purely SQL on a new server and reporting services on the existing server.
    Could you please suggest me a feasible hardware specs for my current situation. Is it feasible to keep reporting services on the existing server. We currently have IIS, Reporting, SQL on a single server.
    Many thanks in advance...!
  7. ghemant Moderator

    From security and performance perspective its not good to have IIS and SQL Server both on single server. Before purchasing a new hardware you have to carefully estimate your database growth/capacity planning for next 4~5 years. And what would be the operations performed within application, and based on that you can have a RAID configuration for your H/W. If your application is write intensive recommendation is to use RAID10 and if it is read intensive use RAID5. For OS, Binaries(executables),T-Logs can be kept on RAID1, you can have tempdb on RAID1*
    *provided you have budget constraint and/or you frequently using tempdb to have your staging tables :)

Share This Page