May 25, 2007 at 12:50 am
I have a scenario in which I have to transfer data from one schema to another schema, for that purpose I have created a SP which perform this task. Initially, time required to transfer data from one schema to another schema, was in seconds. But now said SP takes more than 2 hours and after that gives the error message of "Timeout Expired".
Environment which I have, is as follow
1- Database size is 10 GB.
2- Processor: Pantium 3 GHrz
3- RAM: 1 GB
I was thinking that the problem I am facing just because of tempdb but when I saw the size of tempdb it was just 10 MB. I don't what is the problem at my end but this problem is really critical for me, any help will be highly appreciated.
Thanks in advnace
May 25, 2007 at 1:20 am
Could you pos the proc please?
Things to check - how much space is free in the DB? If it has to grow to accomodate the data, you will get slow downs
What does SQL show as a wait while the process is running?
What else is occuring while you're running this?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 25, 2007 at 3:49 am
1- It is showing me total database size is 3809.25 MB
2- It is showing the available size 192.40 MB
3- Autogrowth property is enabled by 10 % of both log file and data file.
4- I could not get your question "What does SQL show as a wait while the process is running?" how can I check what sql is showing while running a SPs.
5- Nothing else is running while transferring data from one schema to another.
6- Following is the code of stored procedure
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [utility].[proc_ShiftUpdatedMauzaRecords]
@param_mauza_id UNIQUEIDENTIFIER
AS
-- DECLARATION FOR KHEWAT
DECLARE @new_khewat_id UNIQUEIDENTIFIER
DECLARE @khewat_id UNIQUEIDENTIFIER
DECLARE @mauza_id UNIQUEIDENTIFIER
DECLARE @taraf_id UNIQUEIDENTIFIER
DECLARE @patti_id UNIQUEIDENTIFIER
DECLARE @khewat_no INT
DECLARE @old_khewatno VARCHAR(8)
DECLARE @maalia VARCHAR(50)
DECLARE @user_id UNIQUEIDENTIFIER
DECLARE @access_datetime DATETIME
-- DECLARATION FOR KHATUNI
DECLARE @new_khatuni_id UNIQUEIDENTIFIER
DECLARE @khatuni_id UNIQUEIDENTIFIER
DECLARE @kha_khewat_id UNIQUEIDENTIFIER
DECLARE @khatuni_no INT
DECLARE @khatuni_type_id UNIQUEIDENTIFIER
DECLARE @khatuni_description NVARCHAR(2000)
DECLARE @laagan NVARCHAR(250)
DECLARE @kha_user_id UNIQUEIDENTIFIER
DECLARE @kha_access_datetime DATETIME
-- DECLARATION FOR REMARKS
DECLARE @new_remarks_id UNIQUEIDENTIFIER
DECLARE @remarks_id UNIQUEIDENTIFIER
DECLARE @rmk_khewat_id UNIQUEIDENTIFIER
DECLARE @rmk_khatuni_id UNIQUEIDENTIFIER
DECLARE @rmk_khasra_id UNIQUEIDENTIFIER
DECLARE @description NVARCHAR(100)
DECLARE @remarks_type NVARCHAR(50)
DECLARE @rmk_user_id UNIQUEIDENTIFIER
DECLARE @rmk_access_datetime DATETIME
-- DECLARATION FOR KHASRA
DECLARE @new_khasra_id UNIQUEIDENTIFIER
DECLARE @khasra_id UNIQUEIDENTIFIER
DECLARE @khasra_no NVARCHAR(50)
DECLARE @old_khasra_no NVARCHAR(50)
DECLARE @land_type_id UNIQUEIDENTIFIER
DECLARE @irrigation_source_id UNIQUEIDENTIFIER
DECLARE @khasra_area VARCHAR(15)
DECLARE @khasra_dimension NVARCHAR(50)
DECLARE @sequence_no INT
DECLARE @khs_user_id UNIQUEIDENTIFIER
--BEGIN TRY
--BEGIN TRANSACTION
-- ******************************************************************************************************
-- DELETE RECORDS FROM RHZ SCEHMA OF A PARTICULAR MAUZA SO THAT UPDATED RECORDS CAN BE OVERWRITTEN TO RHZ
-- ******************************************************************************************************
DELETE FROM transactions.Gardawri WHERE khasra_id IN
(SELECT khasra_id FROM updated.Khasra INNER JOIN updated.Khatuni ON updated.Khatuni.khatuni_id = updated.Khasra.khatuni_id
INNER JOIN updated.Khewat ON updated.Khewat.khewat_id = updated.Khatuni.khewat_id
WHERE updated.Khewat.mauza_id = @param_mauza_id)
DELETE FROM transactions.FardBadrKhasra WHERE fardbadr_id IN
(SELECT fardbadr_id FROM transactions.FardBadr WHERE mauza_id = @param_mauza_id)
DELETE FROM transactions.FardBadrLogicalPortion WHERE fardbadr_id IN
(SELECT fardbadr_id FROM transactions.FardBadr WHERE mauza_id = @param_mauza_id)
DELETE FROM transactions.FardBadrPerson WHERE fardbadr_id IN
(SELECT fardbadr_id FROM transactions.FardBadr WHERE mauza_id = @param_mauza_id)
DELETE FROM transactions.FardBadr
WHERE mauza_id = @param_mauza_id
DELETE FROM transactions.TaghayarCultivator WHERE taghayar_id IN
(SELECT taghayar_id FROM transactions.Taghayar INNER JOIN updated.Khasra ON transactions.Taghayar.khasra_id = updated.Khasra.khasra_id
INNER JOIN updated.Khatuni ON updated.Khatuni.khatuni_id = updated.Khasra.khatuni_id
INNER JOIN updated.Khewat ON updated.Khewat.khewat_id = updated.Khatuni.khewat_id
WHERE updated.Khewat.mauza_id = @param_mauza_id)
DELETE FROM transactions.Taghayar WHERE khasra_id IN
(SELECT khasra_id FROM updated.Khasra INNER JOIN updated.Khatuni ON updated.Khatuni.khatuni_id = updated.Khasra.khatuni_id
INNER JOIN updated.Khewat ON updated.Khewat.khewat_id = updated.Khatuni.khewat_id
WHERE updated.Khewat.mauza_id = @param_mauza_id)
DELETE FROM transactions.IntiqalLogicalPartition WHERE khatuni_id IN
(SELECT khatuni_id FROM updated.Khatuni INNER JOIN updated.Khewat ON updated.Khewat.khewat_id = updated.Khatuni.khewat_id
WHERE updated.Khewat.mauza_id = @param_mauza_id)
DELETE FROM transactions.IntiqalLogicalPartition
WHERE khewat_id IN ( SELECT khewat_id FROM updated.Khewat WHERE mauza_id = @param_mauza_id)
DELETE FROM updated.GenealogicalTree WHERE mauza_id = @param_mauza_id
DELETE FROM updated.RemarksDetail WHERE remarks_id IN
(SELECT remarks_id FROM updated.Remarks INNER JOIN updated.Khewat ON updated.Khewat.khewat_id = updated.Remarks.khewat_id
WHERE updated.Khewat.mauza_id = @param_mauza_id)
DELETE FROM updated.Remarks WHERE khatuni_id IN
(SELECT khatuni_id FROM updated.Khatuni INNER JOIN updated.Khewat ON updated.Khewat.khewat_id = updated.Khatuni.khewat_id
WHERE updated.Khewat.mauza_id = @param_mauza_id)
DELETE FROM updated.Remarks WHERE khewat_id IN ( SELECT khewat_id FROM updated.Khewat WHERE mauza_id = @param_mauza_id)
DELETE FROM updated.Remarks WHERE khasra_id IN
(SELECT khasra_id FROM updated.Khasra INNER JOIN updated.Khatuni ON updated.Khatuni.khatuni_id = updated.Khasra.khatuni_id
INNER JOIN updated.Khewat ON updated.Khewat.khewat_id = updated.Khatuni.khewat_id
WHERE updated.Khewat.mauza_id = @param_mauza_id)
DELETE FROM updated.Khasra WHERE khatuni_id IN
(SELECT khatuni_id FROM updated.Khatuni INNER JOIN updated.Khewat ON updated.Khewat.khewat_id = updated.Khatuni.khewat_id
WHERE updated.Khewat.mauza_id = @param_mauza_id)
DELETE FROM updated.PersonLandRecord WHERE khatuni_id IN
(SELECT khatuni_id FROM updated.Khatuni INNER JOIN updated.Khewat ON updated.Khewat.khewat_id = updated.Khatuni.khewat_id
WHERE updated.Khewat.mauza_id = @param_mauza_id)
DELETE FROM updated.PersonLogicalPartition WHERE khatuni_id IN
(SELECT khatuni_id FROM updated.Khatuni INNER JOIN updated.Khewat ON updated.Khewat.khewat_id = updated.Khatuni.khewat_id
WHERE updated.Khewat.mauza_id = @param_mauza_id)
DELETE FROM updated.Khatuni WHERE khewat_id IN (SELECT khewat_id FROM updated.Khewat WHERE mauza_id = @param_mauza_id)
DELETE FROM updated.PersonLogicalPartition WHERE khewat_id IN (SELECT khewat_id FROM updated.Khewat WHERE mauza_id = @param_mauza_id)
DELETE FROM updated.PersonLandRecord WHERE khewat_id IN (SELECT khewat_id FROM updated.Khewat WHERE mauza_id = @param_mauza_id)
DELETE FROM updated.Khewat WHERE mauza_id = @param_mauza_id
-- ***************************************************
--END
-- ***************************************************
-- INSERTING RECORDS IN GENEALOGICAL TREE
INSERT INTO updated.GenealogicalTree(genealogical_id, family_id, person_id, mauza_id,taraf_id,patti_id, relation_id, dep_person_id, intiqal_id, live_status, is_numberdar, person_no, remarks, user_id, access_datetime )
SELECT newid(),family_id,person_id,mauza_id,taraf_id,patti_id,relation_id,dep_person_id,'00000000-0000-0000-0000-000000000000',
live_status,is_numberdar,person_no,remarks,user_id,GETDATE() FROM shajra.GenealogicalTree WHERE mauza_id = @param_mauza_id
--****************************
--CURSOR FOR KHEWAT
--****************************
DECLARE CUR_KHEWAT CURSOR FOR
SELECT newid(),khewat_id,mauza_id,taraf_id,patti_id,khewat_no,old_khewatno,maalia,user_id,GETDATE()
FROM rhz.Khewat WHERE mauza_id = @param_mauza_id
OPEN CUR_KHEWAT
FETCH NEXT FROM CUR_KHEWAT INTO @new_khewat_id,@khewat_id,@mauza_id,@taraf_id,@patti_id,@khewat_no,@old_khewatno,@maalia,@user_id,@access_datetime
WHILE @@FETCH_STATUS =0
BEGIN
-- RECORD INSERTION IN KHEWAT
INSERT INTO updated.Khewat
VALUES(@new_khewat_id,@khewat_id,@mauza_id,@taraf_id,@patti_id,@khewat_no,@old_khewatno,@maalia,@user_id,@access_datetime)
-- RECORD INSERTION IN PERSONLANDRECORD AGAINST KHEWAT
INSERT INTO updated.PersonLandRecord
SELECT newid(),sequence_no,@new_khewat_id,khatuni_id,person_id,person_status_id,person_reference_id,
person_share,person_area,is_nomore,user_id,GETDATE() FROM rhz.PersonLandRecord
WHERE khewat_id = @khewat_id AND khatuni_id IS NULL
-- RECORD INSERTION IN PERSONLOGICALPARTITION
INSERT INTO updated.PersonLogicalPartition
SELECT newid(),person_id,@new_khewat_id,khatuni_id FROM rhz.PersonLogicalPartition WHERE khewat_id = @khewat_id
--*********************
-- CURSOR FOR REMARKS
--*********************
DECLARE CUR_REMARKS CURSOR FOR
SELECT remarks_id,khewat_id,khatuni_id,khasra_id,description,remarks_type,user_id,access_datetime
FROM rhz.Remarks WHERE khewat_id = @khewat_id
OPEN CUR_REMARKS
FETCH NEXT FROM CUR_REMARKS INTO @remarks_id,@rmk_khewat_id,@rmk_khatuni_id,@rmk_khasra_id,@description,
@remarks_type,@rmk_user_id,@rmk_access_datetime
WHILE @@FETCH_STATUS = 0
BEGIN
-- SETTING NEW REMARKS ID
SET @new_remarks_id = NEWID()
-- INSERTING RECORDS INTO REMARKS
INSERT INTO updated.Remarks (remarks_id,khewat_id,khatuni_id,khasra_id,description,remarks_type,user_id,access_datetime)
VALUES(@new_remarks_id,@new_khewat_id,@rmk_khatuni_id,@rmk_khasra_id,@description,
@remarks_type,@rmk_user_id,GETDATE())
-- INSERTING RECORDS INTO REMARKSDETAIL
--INSERT INTO updated.RemarksDetail(remarks_id,person_id)
--SELECT @new_remarks_id,person_id FROM rhz.RemarksDetail WHERE remarks_id = @remarks_id
FETCH NEXT FROM CUR_REMARKS INTO @remarks_id,@rmk_khewat_id,@rmk_khatuni_id,@rmk_khasra_id,
@description,@remarks_type,@rmk_user_id,@rmk_access_datetime
END
CLOSE CUR_REMARKS
DEALLOCATE CUR_REMARKS
--****************************
--CURSOR FOR KHATUNI
--****************************
DECLARE CUR_KHATUNI CURSOR FOR
SELECT newid(),khatuni_id,@new_khewat_id,khatuni_no,khatuni_type_id,khatuni_description,laagan,
user_id,GETDATE() FROM rhz.Khatuni WHERE khewat_id = @khewat_id
OPEN CUR_KHATUNI
-- FETCHING FIRST RECORD FROM CUR_KHATUNI
FETCH NEXT FROM CUR_KHATUNI INTO @new_khatuni_id,@khatuni_id,@kha_khewat_id,@khatuni_no,@khatuni_type_id,@khatuni_description,@laagan,
@kha_user_id,@kha_access_datetime
WHILE @@FETCH_STATUS = 0
BEGIN
-- RECORD INSERTION IN KHATUNI
INSERT INTO updated.Khatuni
VALUES(@new_khatuni_id,@khatuni_id,@kha_khewat_id,@khatuni_no,@khatuni_type_id,@khatuni_description,
@laagan,@kha_user_id,@kha_access_datetime)
-- UPDATING KHATUNI ID IN REMARKS TABLE
UPDATE updated.Remarks
SET khatuni_id = @new_khatuni_id
WHERE khatuni_id = @khatuni_id
-- RECORD INSERTION IN PERSONLANDRECORD AGAINST KHATUNI
INSERT INTO updated.PersonLandRecord
SELECT newid(),sequence_no,NULL,@new_khatuni_id,person_id,person_status_id,person_reference_id,
person_share,person_area,is_nomore,user_id,GETDATE() FROM rhz.PersonLandRecord WHERE khatuni_id = @khatuni_id
-- UPDATING RECORDS IN PERSNOLOGICALPARTITION
UPDATE updated.PersonLogicalPartition
SET khatuni_id = @new_khatuni_id
WHERE khatuni_id = @khatuni_id
--***************************
-- CURSOR FOR KHASRA
--***************************
DECLARE CUR_KHASRA CURSOR FOR
SELECT newid(),khasra_id,khasra_no,old_khasra_no,land_type_id,irrigation_source_id,khasra_area,
khasra_dimension,sequence_no,user_id FROM rhz.Khasra WHERE khatuni_id = @khatuni_id
OPEN CUR_KHASRA
FETCH NEXT FROM CUR_KHASRA INTO @new_khasra_id,@khasra_id,@khasra_no,@old_khasra_no,@land_type_id,@irrigation_source_id,
@khasra_area,@khasra_dimension,@sequence_no,@khs_user_id
WHILE @@FETCH_STATUS = 0
BEGIN
-- INSERTING RECORDS IN KHASRA
INSERT INTO updated.Khasra
VALUES(@new_khasra_id,@khasra_id,@khasra_no,@old_khasra_no,@new_khatuni_id,@land_type_id,@irrigation_source_id,
@khasra_area,@khasra_dimension,@sequence_no,@khs_user_id,GETDATE())
-- UPDATING KHASRA IN REMARKS
UPDATE updated.Remarks
SET khasra_id = @new_khasra_id
WHERE khasra_id = @khasra_id
FETCH NEXT FROM CUR_KHASRA INTO @new_khasra_id,@khasra_id,@khasra_no,@old_khasra_no,@land_type_id,@irrigation_source_id,
@khasra_area,@khasra_dimension,@sequence_no,@khs_user_id
END
CLOSE CUR_KHASRA
DEALLOCATE CUR_KHASRA
-- UPDATING KHASRA ID IN REMARKS TABLE
UPDATE updated.Remarks
SET khatuni_id = @new_khatuni_id
WHERE khatuni_id = @khatuni_id
-- FETCHING NEXT RECORD FROM CUR_KHATUNI
FETCH NEXT FROM CUR_KHATUNI INTO @new_khatuni_id,@khatuni_id,@kha_khewat_id,@khatuni_no,@khatuni_type_id,@khatuni_description,@laagan,
@kha_user_id,@kha_access_datetime
END
CLOSE CUR_KHATUNI
DEALLOCATE CUR_KHATUNI
-- FETCHING NEXT RECORD FROM CUR_KHEWAT
FETCH NEXT FROM CUR_KHEWAT INTO @new_khewat_id,@khewat_id,@mauza_id,@taraf_id,@patti_id,@khewat_no,@old_khewatno,@maalia,@user_id,@access_datetime
END
CLOSE CUR_KHEWAT
DEALLOCATE CUR_KHEWAT
--COMMIT TRANSACTION
--END TRY
--BEGIN CATCH
--ROLLBACK TRANSACTION
--END CATCH
May 25, 2007 at 4:25 am
To find out what a proc is waiting on, open another query analyser/management studio window and run
SELECT spid, waittime, lastwaittype from master..sysprocesses
You'll have to look through the processes to find the one running the prod. The LastWaitType will tell you what the proc is waiting on, the waittime will tell you, in ms, how long it's being waiting.
From a very quick look at your proc, those cursors are unnecessary. plus they appear to be nested, which could very easily lead to very very slow performance.
If I get chance later, I'll see if I can write you a set-based replacement for a part of that proc.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 3, 2007 at 7:00 pm
If the stored proc is degrading over time, can u then just recompile it using sp_recompile and then check the results. There must be lot of chsnge in data in your tables.
November 4, 2007 at 7:17 am
- I'd suspect page splits (clustering index) and file autogrowth.
- work with transactions
- avoid the cursors, do a direct insert in stead !
- insert rows sorted according to the clustering index
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 5, 2007 at 7:56 am
The degrading performance may be due to more data on your system. If so, then a re-think on the stored proc may be required.
An easy thing to check - are your statistics up to date? See BOL -
USE AdventureWorks;
GO
SELECT 'Index Name' = i.name, 'Statistics Date' = STATS_DATE(i.object_id, i.index_id)
FROM sys.objects o
JOIN sys.indexes i ON o.name = 'Address' AND o.object_id = i.object_id;
GO
November 5, 2007 at 8:02 am
In addition to what others said:
1) Can't you use JOINS instead of IN clauses? Could be more efficient.
2) Are all necessary fields (IN fields for example) indexed to provide potential index seeks for all those deletes?
3) Reiterating: CURSORS ARE TERRIBLY INEFFICIENT! 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 6, 2007 at 2:35 pm
Also, you have a 4G DB with 1G of RAM. How big are the tables? Profile the job to see where it is slowing down. I expect that you are probably hitting a memory bottleneck. Have you checked you Cache hit ratio?
November 6, 2007 at 2:43 pm
Lots of cursors here too. I would be very surprised if you aren't getting blocking issues while stepping through the cursors.
I might suggest going with temp tables and get rid of the cursors.
November 6, 2007 at 2:46 pm
Even better would be bcp.exe and bulk import
November 6, 2007 at 3:39 pm
what kind of shape is the tempDB in? how big, how many fragments, growth factors, etc...?
Do you have autoshrink on anywhere?
and - why, why, why all of the row by row inserting? It's like buying a maserati for the windshield wipers....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply