How to improve the performance of a SP

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

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.

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

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.

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



Surendra Kalekar

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

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

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

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

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

Usha Rani

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

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

Why is the distinct necessary? Do you have duplicate rows in your table?

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.