SQL Server Performance

How to improve the performance of a SP

Discussion in 'Performance Tuning for DBAs' started by pallavi, Dec 7, 2005.

  1. pallavi New Member

    Hello

    I have written a sotred procedure, which transfters the data from one Database to another database.

    The source databse has approx 2 lac records which are transfered to 7 different tables in the Destination.

    But the problem is that it takes more than 5 hrs to execute it.

    How can i improve the performance of this proceure.

    The procedure goes like this

    1.Cursor to fetch each row
    2.Check whether the record already exists in the destination DB
    3.if not then insert the records in other table and get the FK's
    4.Insert the record in the Main table "Equipment"
    5.now insert the record in the table attched to it "Equipment component"
    (for transfering this attached table we take the we again have to access the Source db.)
    another procedure is written to transfer this table.
    we just give a call to the procedure
    6.If the record already exists then check whether the record in the Source is updated
    or not, using the Dateofupdate.
    7.if the record is updated then update the values in the resp tables
    8.also check for the updation in the "equipment component"
    9.if there is any change in "Equipment component" then update that table also

    end of procedure


    We have written small procedures to fetch the reecords from other tables.
    for e.g
    we need Equipment Group as GroupId in the Equipment table which comes from "EquipmentGroups" table
    So we have written a stored procedure GetEquipGroupId
    this procedure check whether the group is present or not if not it adds the group and returns the ID, if the group is present then it just returns the id.

    this way we are refering 6 tables. for each row.

    Pls can any one tell me what should be done to improve the performance of this procedure

    thanks in advance

    Pallavi

  2. Luis Martin Moderator

    Did you take a look of execution plan?


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  3. Madhivanan Moderator

    Why did you use Cursor?

    Use this approach which is more faster

    Insert into DB1.dbo.Table(columns) Select columns from DB2.dbo.Table

    Madhivanan

    Failing to plan is Planning to fail
  4. ranjitjain New Member

    Performance Improvement question arises due to following:
    1>Bad query design
    2>Reasonable Hardware Configuration and Netwok
    3>Bad Application Design

    You can check for these step by step.
    To start with The application Tuning is the main component which ca cause Performance Hurdle.
    So start with that.
    If everything looks ok then you can look into QA and can change or Modify the way data is accessed.
    Check for the where clause if you have proepr index being used and no scans there in execution plan.

    You can even search the articles here in SSP on how to avoid using cursors as it creates most overhead on SQL Server. So better avoid using them. There are articles on how to perform row by row operation without using cursor.
    Check this:
    http://www.sql-server-performance.com/dp_no_cursors.asp

    Also check for execution plans to change the way Joins are used by modifying Join order or join operations or both.
    You can also check with JOin HINTS.

    You can even read about SQL Server Tuning or else let SQL decide best configuration for your machine.
    Finally check for Hardware Tuning.
  5. pallavi New Member

    Hello all

    thanks for the reply

    Well we have to use cursor as the values from the SourceDB.Equipment are going in different tables
    Like DestiDB. EquipmentGroup
    DestiDb.Attributes
    DestiDb.Structure etc
    and then id's from this table are inserted in the DestiDb.Equipment
    So for this the cursor is needed

    As far as the Application tuning and other things are concened, for other tables we the performance is good. We have in total 30 tables other tables have avg 10,000 records per table. other procedures execute quiet fast. it takes approx same time to run a single procedure for Equipment and all other procedures.


    Also the atricle abt fetching rows without cursor can not be implemented in our procedure, as in the SoerceDb.Equipment there is no id like column

    also for selecting the rows from SourceDB.Equipment
    we are using Select distinct * from SourceDB.dbo.Equipment
    we have also tried this query by specifying all the fieldnames but it doesnot improve the performance

    If u can suggest anything to imporve the query performance then pls do let me know.
    thanks again

    Pallavi
  6. ranjitjain New Member

    As you are inserting rows into a table row by row then you can try using transactions and complete the whole lot in a batch which can save time i feel.
    You can give a Try using transactions in sp if not there.

    Even usinf select DISTINCT * from table can take long if you have huge rowcount.
    even if you specify field names wont affect.
    You need to filter using dummy where in select * then only it can be improved.
  7. surendrakalekar New Member

    My experience in exporting data from one database to another is totally different.
    Instead of using SP I will use DTS package.



    Surendra Kalekar

  8. Madhivanan Moderator

    Did you look at what I specified?

    Insert into DB1.dbo.Table(columns) Select columns from DB2.dbo.Table


    Madhivanan

    Failing to plan is Planning to fail
  9. pallavi New Member

    Hello all

    thanks for all those replies

    well Madhivanan
    this approach doesnot work in my case

    surendra you were telling to use DTS packages.
    but in the DTS package how can I insert records in from one table to more than one tables and get their reference.

    If u know how to do it pls do let me know.

    currently I have minimized the execution time a little bit, by doing everything in the same procedure, perviuosly i was doing this thing by making the code modular, so there were more calls to other procedures, now i am doing all the operations in the same procedure.
    due to this the performance is a bit better. now it takes aroung 4.15 hrs to execute

    thanks to all

    Pallavi

  10. Madhivanan Moderator

    >>this approach doesnot work in my case

    Why?

    Madhivanan

    Failing to plan is Planning to fail
  11. pallavi New Member

    Hi Madhivanan

    the source table is having
    Equip(companyname, Equipid, Equipcode, Name, Position, group, Model, Application, Status, class, Section, diaLable1, dia1, dialeble2, dia2)

    Destination table
    Equipment(id, Equipcode, Name, Position, sectionid, attrid, application, model ,StatusId, classid, companyid)

    while transfering from source to destination,
    1 get the companyid from company name
    2 get the groupid from equipmentgroups, if the record is not present then insert the record in that table and get id
    3 similar for section , class status,
    4 add the attributes and get their id
    5 now using these id insert record into equipment
    6 using the equipcode get records from another table sourceDB.equipmenfuel and add the record in the destinationdB.fuel table

    So the approach u told is not helpful for me

    well taanks for it, coz i will be needing it next

    thanks
    Pallavi
  12. PAMUR New Member

    Hi,

    Use DTS as Suggested by Surendra, Its is very good tool and I have migrated data from Access, Sql Server 6.5 and Fox pro to SQL Server 2000 using DTS. I have constructed a package which will be used for n number of migrations. Have migrated 10 GB of data.

    USe VB script with the Package to transform data. I can Guid you please post the mapping of fields and tables.

    For a start set up an intermediate database(with simple Log) with the destination tables without any constraints or relationships. While Designing the package use the Data Driven Query Task and Look up fields and Script to perform conditional mapping.



    Usha Rani
  13. pallavi New Member

    Hi Usha Rani

    Actually i am transfering 28 tables and a master stored procedure is written to do this task. so creating a DTS package for one table is not possible, also the client dont want to change the current structure.

    but soon i will be needing more help on creation of DTS packages.

    Thanks a lot

    Pallavi
  14. PAMUR New Member

    So then you want to just tune your stored proc? I don't get you.

    Usha Rani
  15. surendrakalekar New Member

    quote:Originally posted by pallavi

    Hi Madhivanan

    the source table is having
    Equip(companyname, Equipid, Equipcode, Name, Position, group, Model, Application, Status, class, Section, diaLable1, dia1, dialeble2, dia2)

    Destination table
    Equipment(id, Equipcode, Name, Position, sectionid, attrid, application, model ,StatusId, classid, companyid)

    while transfering from source to destination,
    1 get the companyid from company name (use Function/SP for this)
    2 get the groupid from equipmentgroups, if the record is not present then insert the record in that table and get id (use Function/SP for this)
    3 similar for section , class status, (use Function/SP for this)
    4 add the attributes and get their id (use Function/SP for this)
    5 now using these id insert record into equipment (use Function/SP for this)
    6 using the equipcode get records from another table sourceDB.equipmenfuel and add the record in the destinationdB.fuel table

    So the approach u told is not helpful for me

    well taanks for it, coz i will be needing it next

    thanks
    Pallavi

    quote:Originally posted by pallavi

    Hi Usha Rani

    Actually i am transfering 28 tables and a master stored procedure is written to do this task. so creating a DTS package for one table is not possible, also the client dont want to change the current structure.

    but soon i will be needing more help on creation of DTS packages.

    Thanks a lot
    Pallavi

    Ok. If you can change the structure and can not use DTS, then you can split the task into multiple. By doing this you will be able to tack which particual fucnion/sp is performaing slower and the use of indexes are proper or not. So it will be easy for you to optimize those and this will help you in future while creating the DTS package.


    Surendra Kalekar

  16. dtipton New Member

    <<we are using Select distinct * from SourceDB.dbo.Equipment >>

    Why is the distinct necessary? Do you have duplicate rows in your table?
  17. mmarovic Active Member

    quote:Originally posted by pallavi

    Hi Madhivanan

    the source table is having
    Equip(companyname, Equipid, Equipcode, Name, Position, group, Model, Application, Status, class, Section, diaLable1, dia1, dialeble2, dia2)

    Destination table
    Equipment(id, Equipcode, Name, Position, sectionid, attrid, application, model ,StatusId, classid, companyid)

    while transfering from source to destination,
    1 get the companyid from company name
    2 get the groupid from equipmentgroups, if the record is not present then insert the record in that table and get id
    3 similar for section , class status,
    4 add the attributes and get their id
    5 now using these id insert record into equipment
    6 using the equipcode get records from another table sourceDB.equipmenfuel and add the record in the destinationdB.fuel table

    So the approach u told is not helpful for me
    I think it is. You would just need to use joins and to change your row by row logic into the dataset one. If you are going to use dts you would do the same anyway.

Share This Page