February 9, 2011 at 9:18 am
Hi,
we insertion data it taken time too long .. pls help me.. how to find out performance problem
February 9, 2011 at 10:11 am
can you provide a little more information than that? How about table layouts, how much data are you inserting, indexes on tables, how is data being inserted (Bulk Insert, SSIS, etc...), are you going across the network or are you on the same server, etc.... See the first link in my signature to see how to post.
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 9, 2011 at 10:47 am
Data insertion through application using procedure ( but i inserted data 2000 rows on ssms it taken only 2 seconds .. )
February 9, 2011 at 10:56 am
When you inserted 2000 rows with SSMS, did you use the proc? What is the application doing? Is it opening and closing a transaction each time or are all 2000 inserts in one transaction? Is it running parallel and causing locking issues? How long does the application take to insert the same amount of rows? What is the isolation level the application is using? There's many things that it could be, but it's hard to tell with the infor you have provided. Have you run a trace to see what exactly is going on during the inserts?
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 9, 2011 at 11:07 am
I have insert data directly into SSMS it taken 2 seconds for 2000 rows , i am used while loop and insert command
But we got that problem data inserted through application it taken time for 10 min .. my thought this not dba problem , this problem impact with application coding , connection etc ........
I ran trace but i could not see long time inserting data. cpu time very less..
How to do perfomance check and follow the steps ?
February 9, 2011 at 11:14 am
exec sp_executesql N'INSERT INTO schd_det2
(schd_date,schd_discr_qty,schd_interval,schd_nbr,schd_line,schd_rlse_id,schd_reference,schd_type,schd_domain,USERNAME,DATESTAMP,DOMAIN) VALUES
(@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,''QAD_DATAMART'',''2011-02-09'',@P10)',N'@P1 datetime,@P2 float,@P3 varchar(1),@P4 varchar(8),@P5 int,@P6 varchar(12),@P7 varchar(1),@P8
int,@P9 varchar(7),@P10 varchar(7)','2011-09-05 00:00:00:000',0,'M','P1000189',27,'20101213-001','',5,'1101020','1101020'
this is insert query statement
February 9, 2011 at 12:33 pm
From what you've supplied, it's hard to be sure where the problem is occurring. You're saying that the query is a loop? That could be the problem right there. I think the thing you need to check is to see if there are waiting processes while this is running. If so, what are they waiting on. I'd suggest taking a look at the DMV sys.dm_exec_requests. This will show which processes are operating and what they are waiting on. You can then use the various other DMVs to pull more data out of the system.
Or, if you don't want to muck about learning the ins & outs of DMVs you could check out Adam Machanics Who Is Active script That will certainly help.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply