I have linked three SQL Servers together, and have written a stored proc that has a cursor that joined three tables from one of the linked servers. When I pull the SQL out of the cursor definition and run it in a query window it runs fine, but when I run the stored proc that simply steps through the same select result set it is too slow for words. It also throws a warning about serial isolation levels. Is there any way I can fix this.
David
I have a sub procedure that gets call about 2M times. It's taking nearly 20 minutes to complete. If I remark out the following sub, the job runs in about 2 minutes. I tried remarking just the INSERT INTO, but that didn't make a difference, so it's not that, it's the damn FETCH, and a bit of the SET statement just prior to it. Any ideas what I can do to speed it up?
Thanks,
Carl
ALTER PROCEDURE [dbo].[proc_SAHSGenSnroDetail] @currHumpDtm DATETIME, @pos INT, @humpRate SMALLINT, @combID INT AS
DECLARE @carInit CHAR(4)
DECLARE @carNbr CHAR(10)
DECLARE @lstHumpDtm DATETIME
DECLARE @snroScr SMALLINT
DECLARE curSnroDetail CURSOR FOR
SELECT CAR_INIT, CAR_NBR, LST_HMP_DTM FROM TSA_HS_OBJ_TRN
WHERE WKLD_ID = @pos ORDER BY TRK_SEQ_NBR
OPEN curSnroDetail
FETCH NEXT FROM curSnroDetail INTO @carInit, @carNbr, @lstHumpDtm
WHILE @@FETCH_STATUS = 0
BEGIN
IF @currHumpDtm <= @lstHumpDtm
BEGIN
SET @snroScr = 1
END
ELSE
BEGIN
SET @snroScr = 0
END
INSERT INTO TSA_HS_SNRO_DTL (COMB_ID, WKLD_ID, CAR_INIT, CAR_NBR, SNRO_HMP_DTM, SNRO_SCR)
VALUES (@combID, @pos, @carInit, @carNbr, @currHumpDtm, @snroScr)
SET @currHumpDtm = DATEADD(s, (CONVERT(DECIMAL,1)/CONVERT(DECIMAL,@humpRate))* 3600, @currHumpDtm)
FETCH NEXT FROM curSnroDetail INTO @carInit, @carNbr, @lstHumpDtm
END
CLOSE curSnroDetail
DEALLOCATE curSnroDetail
Here's the source table for the cursor
CREATE TABLE [dbo].[TSA_HS_OBJ_TRN] (
[WKLD_ID] [int] NOT NULL ,
[TRK_SEQ_NBR] [int] NOT NULL ,
[CAR_INIT] [char] (4) COLLATE Latin1_General_CI_AI NOT NULL ,
[CAR_NBR] [char] (10) COLLATE Latin1_General_CI_AI NOT NULL ,
[OS_LGTH_FT] [int] NULL ,
[GRS_TON] [int] NULL ,
[YBLK] [char] (6) COLLATE Latin1_General_CI_AI NULL ,
[TA_DTM] [datetime] NULL ,
[OBJ_DEP_TRN] [char] (10) COLLATE Latin1_General_CI_AI NULL ,
[LST_HMP_DTM] [datetime] NULL
) ON [PRIMARY]
And here's the table that the insert goes into
CREATE TABLE [dbo].[TSA_HS_SNRO_DTL] (
[COMB_ID] [int] NULL ,
[WKLD_ID] [int] NULL ,
[CAR_INIT] [char] (4) COLLATE Latin1_General_CI_AI NULL ,
[CAR_NBR] [char] (10) COLLATE Latin1_General_CI_AI NULL ,
[SNRO_HMP_DTM] [datetime] NULL ,
[SNRO_SCR] [smallint] NULL ,
[CAR_CNT] [smallint] NULL
) ON [PRIMARY]
Is there a way to speed up this query using two cursors? I need the result set from the history cursor to change on each pass of the loop. I tried to just open and close the cursor each loop but it didn't work so I delclared it and dealocated each loop, which works but at a very slow pace. Is there a command to refresh the result set? or a way to do this as a batch?
set nocount on
Declare @entry bigint, @mobile int, @fid int, @rid int, @pin char(10), @commission tinyint,
@barred tinyint, @barred_acse tinyint, @les char(10), @mobile1 int, @fid1 int,
@rid1 int, @pin1 char(10), @commission1 tinyint, @barred1 tinyint, @barred_acse1 tinyint,
@les1 char(10)
DECLARE changes CURSOR
FOR
select mobile, fid, rid, pin, commission, barred, barred_acse, les
from mobile_changes
OPEN changes
FETCH NEXT from changes
INTO @mobile, @fid, @rid, @pin, @commission, @barred, @barred_acse, @les
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE history CURSOR
FOR
select max(entry), mobile, fid, rid, pin, commission, barred, barred_acse, les
from mobile_history
where mobile = @mobile
group by mobile, fid, rid, pin, commission, barred, barred_acse, les
OPEN history
FETCH NEXT FROM history
INTO @entry, @mobile1, @fid1, @rid1, @pin1, @commission1, @barred1, @barred_acse1, @les1
IF (
@mobile = @mobile1 and @fid = @fid1 and @rid = @rid1 and @pin = @pin1 and
@commission = @commission1 and @barred = @barred1 and @barred_acse = @barred_acse1 and
@les = @les1)
BEGIN
DELETE FROM mobile_changes where mobile = @mobile
END
CLOSE history
deallocate history
FETCH NEXT FROM changes
INTO @mobile, @fid, @rid, @pin, @commission, @barred, @barred_acse, @les
END
close changes
deallocate changes
I know with SQL cursors have been cursed with being slow, i'm wondering what is the best alternative, is it still this one:
http://www.sql-server-performance.com/dp_no_cursors.asp
or has someone thought of anything else...
I just wanna know a simple alternative in general i know it matter which application but im curious in general...
or should a temp table be used...?
I've got the following piece of code in a stored procedure, and despite the tables only having about 25K records, its dreadfully slow.
DECLARE Perfer CURSOR FOR
SELECT PerformerID
FROM PAMRA_tbl_navnmatch (NOLOCK)
DECLARE @test as int
OPEN Perfer
FETCH NEXT FROM Perfer
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Perfer into @test
UPDATE PAMRA_tbl_navnmatch
SET PAMRA_tbl_navnmatch.Søgenavn = convert(char(50),NAMEMATCH_vw_memberdata.Søgenavn) ,
PAMRA_tbl_navnmatch.Medlemsnavn = convert(char(50),NAMEMATCH_vw_memberdata.Medlemsna vn),
PAMRA_tbl_navnmatch.Medlemsnavn2 = convert(char(50),NAMEMATCH_vw_memberdata.[Medlemsnavn 2]),
PAMRA_tbl_navnmatch.Medlemsnummer = convert(int, NAMEMATCH_vw_memberdata.Medlemsnummer),
PAMRA_tbl_navnmatch.Nationalitet = convert(char(10), NAMEMATCH_vw_memberdata.Nationalitet),
PAMRA_tbl_navnmatch.Organisationsnummer = convert(char(10),NAMEMATCH_vw_memberdata.Organisat ionsnummer),
PAMRA_tbl_navnmatch.Medlemskab = convert(char(20), NAMEMATCH_vw_memberdata.Medlemsskab),
PAMRA_tbl_navnmatch.IPDnummer = convert(int, NAMEMATCH_vw_memberdata.[IPD Nummer]),
PAMRA_tbl_navnmatch.IPDroll = convert(char(20), NAMEMATCH_vw_memberdata.IPDrolle),
PAMRA_tbl_navnmatch.Franavision = 1
FROM PAMRA_tbl_navnmatch INNER JOIN NAMEMATCH_vw_memberdata ON ltrim(rtrim(PAMRA_tbl_navnmatch.Matchfelt)) = ltrim(rtrim(NAMEMATCH_vw_memberdata.[Søgenavn]))
WHERE PAMRA_tbl_navnmatch.PerformerID = @test
END
CLOSE Perfer
DEALLOCATE Perfer
GO
Is there any way to speed things up? I mean, its been running for more than 45 minutes now. I can track the progress, and it does move forward, BUT yawn its slow.
Its even run on a dual xeon 3.2 server with 4 gigs of memory, only other acticity is a few simple selects on other databases. No locks or anything.
Whats amiss? or is the comparison between char fields just dreadded?
Hi all,
I got a problem. I am working on DTS package. The last step is updating a table field. I wrote a stored procedure as below:
CREATE PROCEDURE [Update_product_manufacturer] AS
Declare @product_id int
Declare @supplier_name VarChar (255)
Declare ValueCursor Cursor For
select product.product_id, [P21_SUPPLIER_id_name_ke].[supplier_name]
from [VARIANT],[P21_INV_MAST_uid_itenID_weight_ke],[product],
[P21_INVENTORY_SUPPLIER_uid_supplierID_price_cost_k e],[P21_SUPPLIER_id_name_ke]
where
[product].product_id = [VARIANT].[product_id]
and
[P21_INV_MAST_uid_itenID_weight_ke].[item_id]=[VARIANT].[SKU]
AND
[P21_INV_MAST_uid_itenID_weight_ke].[inv_mast_uid]=[P21_INVENTORY_SUPPLIER_uid_supplierID_price_cost_k e].[inv_mast_uid]
AND
[P21_SUPPLIER_id_name_ke].[supplier_id]=[P21_INVENTORY_SUPPLIER_uid_supplierID_price_cost_k e].[supplier_id]
order by [product].[product_id]
for read only
Open ValueCursor
while (0 = 0)
begin
fetch next
from ValueCursor
Into @product_id, @supplier_name
update product
set manufacturer = @supplier_name
where product_id = @product_id
end
close ValueCursor
Deallocate ValueCursor
Notes: Table: Product has 28,000 rows, other tables with 28,000 - 56,000 rows
it's been 2 hours, the job is still working.
Who has this kind of experience? How can I make updating quickly?
Thanks,
Kevin Zhang
I have a dts job set up to transfer 550,000 records from a dbf file into a sql server. If I just let it run, there is a 9-10 minute delay, then it starts. If I try to schedule a job, it fails completely. I looked up ways to get it to execute quicker, mainly going to the advanced tab of the transform arrow and making the inserts 1000 at a time, the table locked, turning constraints off. Any advice on how to speed it up or why the job is failing?
View 7 Replies View RelatedHi All
Last week, we upgraded to sql2000 from 6.5. Everything went on fine. we re-compiled all the procedures. When i try to run a procedure, its running for long time - more than 10 hours (in sql 6.5 it runs for 50 mins). do i need to set any procedure cache?.
Also, the server CPU usuage is constantly high - more than 80%. It was fine till last week.
Any suggestions?
Thanks in advance
Jaya
i have written a SP, but it eventually runs slower. i have to run this SP 500 times.
do you know what is causing that?
when i turn on my pc, it takes about 10 minutes from turning on to actually be able to use.
View 4 Replies View Relatedso async cursor population is supposed to create the cursor and return the cursor id quickly, while the server works on async populating the results. For a keyset-driven cursor, SQL Server stores the key sets in tempdb, which it then uses to fetch data for cursor results. Anyway, this works fine for smaller tables, but I'm finding for large result sets, the async cursor population is very slow and indeed seems to approximate synchronous time. The wait stat I get while it is running (supposedly asynchronously) is TRANSACTION_MUTEX.
Example:
--enable async cursor
exec dbo.sp_configure 'cursor threshold', 0; reconfigure;
declare @cursor int, @stmt nvarchar(max), @scrollopt int, @ccopt int, @rowcount int;
--example of giant result set
set @stmt = 'select * from sys.all_objects o1, sys.all_objects o1';
[code]...
Note that using the SQL "select * from sys.all_objects o1" is much faster than "select * from sys.all_objects o1, sys.all_objects o2". However, if cursor population is async, I'd expect the time to return a cursor id to be similar between the two.
Our server is running. There are no locks, and server has been rebooted but the problem is still there. This has been going on for some time now. I intend to restart the server. Does anybody have a quick solution, please help. Thanks for your assistance!!
View 4 Replies View RelatedPlease what do I look out for 6.5 if I want to troubleshoot for slow running.
Thanks.
Hi,
When first time I start my sql server is running faster. After 10 to 15 days later, sql sever performance is very slow. After I restart SQL service, to become normal.
Thanks
Mohan
Hi list,
I'm a long time lurker on this list and really enjoy the discussions, although I rarely get a chance to participate.
Here is my situation: We are importing chunks of data (500 records at a time) from a C++ interface. The records have to be transformed before inserting into the target table which I am doing using a stored proc which is working fine. The records are in memory in C++ and the programmer is looping through the records building inserts into a temp table through ADO (which my proc picks up). The server business object is using the connection.execute method which is inserting one record at a time. That part of the process is taking over 15 seconds for 500 records which is the bulk of the total time.
My question is: Using ADO is there a better way to insert these records into the temp table? I see mention of a recordset interface but my programmers are new to ADO and since I am the DBA and have never used ADO, I am not sure what to tell them.
Any insight would be greatly appreciated.
shawn
Hi,
Our main production server has started running slow, it is a dual zeon thingy with plenty of ram so hardware is not an issue.
Basically a service connects to the database and executes a few stored procs, the only way I can get the system up to speed again is to recompile one of the SPs but that is only a temporary fix.
Anyone had a similar thing?
Can anyone give me help on performance tuning in SQL server 2000.
Thanks
Hi guys.
I have a DTS package ON SQL 2000 which transfer data from AS400 to SQL 2000 using an ODBC Client Access 5.1 (The DSN was configured by a sysdmin on the AS400 so it is configured properly).
When i execute the package manualy (by right click and "execute package") the package runns fine and ruterns data in no time (Eproximatly 30000 rows in 15 sec).
The problem starts when a job executes the same packagee!!!
When i start the job, the DTS package is running Very Very Slow!!!!
sometime it takes Hours to return a few rows! and it seems that it is stuck.
The SQLAgent is running as a NT Account with Administrator rights, and has full access to the AS400!! so the problem is not the Agent.
by monitoring the AS400, i have noticed that the job/DTS is retreaving the first fetch quickly , and then it is in a waiting status
i have tried everything and cant seem to get this problem fixed
Does anyone know what could be the problem?
I Need Help Quick!!!
Thank You
Gil
Hello All,
I have two procedures being run one after the other.
when I run proc1 it runs for about 15 min.
Now the proc2 is dependent on proc1, when I run proc2 it runs for 45 min.
If I run both the proc's simultaneously through .net code it takes more than 1 hour. Can anyone of you tell me where would be the problem.
Thanks in Advance.
Hi, when the following function is used within the where statement of a select statement it runs very slowly, anyone know why?
ALTER FUNCTION [dbo].[fn_GetLastDayOfLVPeriod] ( @pInputDate DATETIME )
RETURNS DATETIME
BEGIN
DECLARE @vLVDPeriod varchar(4)
DECLARE @vLVDYear varchar(4)
DECLARE @vOutputDate DATETIME
SELECT @vLVDPeriod = LVDPeriod
FROM dbo.tblMIDates
WHERE CONVERT(varchar(8), Date, 112) = CONVERT(varchar(8), @pInputDate, 112)
SELECT @vLVDyear = LVDYear
FROM dbo.tblMIDates
WHERE CONVERT(varchar(8), Date, 112) = CONVERT(varchar(8), @pInputDate, 112)
SELECT @vOutputDate = MIN(Date)
FROM dbo.tblMIDates
WHERE LVDPeriod = @vLVDPeriod
AND LVDYear = @vLVDyear
RETURN @vOutputDate
END
hi
if suppose one query is running slow than what steps we can follow to optimize it...
this question is asked me in interview.
thanx
I have two tables. ID table that has these fields
Code_id,YearPeriod, ReachedDate fields. And table
Amount that has Code_id,Amount,Date fields. What I
need to do is write a cursor that when the
amount.Amount adds up to $100.00 it Updates the
ReachedDate in the ID table to the amount.date fields
value of that record. Any help would be greatly appreciated.
I have two tables. ID table that has these fields
Code_id,YearPeriod, ReachedDate fields. And table
Amount that has Code_id,Amount,Date fields. What I
need to do is write a cursor that when the
amount.Amount adds up to $100.00 it Updates the
ReachedDate in the ID table to the amount.date fields
value of that record. Any help would be greatly appreciated.
Hi,
I have SQLServer 6.5 SP5a update running on Windows NT 4.0 SP6
with 4 gig RAM and 4 processor.
Suddenly the SQL 6.5 jobs running on the production server started running very very slow. A job that suppose to run in 30 minutes are running like 2 hours and completing successfully.
(I suspect the after the Norton Anti virus automatic live update may be the reason but not the Second Vulnerability as mentioned by Microsoft Bulletin last week)
I check the SQLServer, ran the performance monitor, checked pagefiles, disk space, databases,memory, tempdb. Everything seems to be normal.
I rebooted the server, checked any other process making that slow. But no use.
Please help me out with this issue as this is a production and the CRM applications from the clients uses the database server.
Thanks in advance,
Anu
All,
I have been tapped to help with fixing a reporting tool. We have a Sql Server database/crystal reports(10) setup. I havent had the chance to look at the tables in the DB yet, but I was told that aggregate tables were used. In my past experience with crystal reports, we used database views to feed crystal reports (in Oracle). I was thinking that I could somehow use views instead of tables and then try to re-index the base tables and compile satitics (if theres such a thing with Sql Server). I was also going to look into bottlenecking and locking (table locking as opposed to row or page locking for the lookup tables...to reduce overhead on the main tables) but, I'm not sure if it'll make a difference since this is just a demo server with no major traffic hitting it yet.
The question is, does anyone have any experience with crystal reports running slow with Sql Server, what should I look out for??
'Wale
Hi all,
I'm having what you might call an optimisation issue - but I'm also not sure if my approach to this problem is right. I've spent the whole day trying various methods but none seem to be performing as admirably as I'd hoped.
Basically, here's the scenario:
1. Log files are being inserted into a SQL table via Log Parser 2.2.
2. I have a lookup table of ip address ranges to countries and other geographic data.
3. Once the log row has been inserted from Log Parser, I want to update the same log table with data from the lookup table.
The main problem seems to be the updating (the initial insert from Log Parser is lightning quick).
I initially wrote a trigger on AFTER INSERT on the log table that converted the actual user's IP address into IPNumber format using a simple algorithm, then pumped the IPNumber into a quick select query which retrieved the geolocation data. Then, in the same trigger, there was an update statement which basically said:
update dbo.Logs
set [Country] = @Country
where [IpNumber] = @IpNumber and [Country] = Null
Therein lies the problem. The update statement slows everything down to almost a standstill and also seems to obtain a lock on the table.
Critical factors:
1. The log table must remain readable.
2. The query must execute in seconds -- not over half hour :)
I've experimented with various combinations of indexing, so far to no avail.
Any suggestions would be very much appreciated.
Regards
Hi all,
I am having a problem ,SQL server is running very slow.This is happening some days only.For example my stored procedure ususally runs less than 2 minutes, some days will take 13minutes.I dont understand the problem.All the stored procedure having the same problem.Sorry, I am not a DBA,basically a devloper.Daily morning we are taking the DB backup and indexes already applied.DB size 10461.06 MB,RAM 4GB,CPU usage is less than 50%,This is a Cinema Database,so lot of users are accessing at same time(Web,IVR,cinema ticket counters etc).We are using SQL reports.Because of the stored procedure running slow,can not view the reports.pls advice..
please help me..If you need some more information please ask ..
Thanks in advance.
Hi friends
after working visual studio (on my report model project) few minutes it runs too slow. i mean clicking on entities ,attributes takes ages to finish. I opened task manager i see "devenv.exe" is taking more than 800,000 k !!
am using sql server 2005 standard edition.
have you seen similar problem.
Thanks for your help.
We have a stored procedure which is running fine on a SQL server 2000 from Query Analyzer. However, when we try to execute the same stored procedure from ADO.NET in an executable, the execution is hung or takes extremely long. Does anyone have any ideas or suggestions about how it could happen and how to fix. thanks
View 22 Replies View RelatedI support a website (ASP.NET 2.0) where recently users have been unable to insert data due to timeout issues. The functionality executes a query that inserts a single row into a SQL Server 2005 db table. I tried running this query from the backend, and it took 4:48 to insert a single row! Interestingly, after that initial agony any similar inserts I tried took no time whatsoever. I have checked the execution plan, but unfortunately don't really know what I'm looking for with inserts, as most of my experience with execution plans is with select statements. Any resources anyone could point me to for troubleshooting this would be much appreciated.
Thanks,
-Dave
I have a SELECT TOP query in order to return x number of top records from a table which has the indexing service enabled on it, such as this :
SELECT TOP(15) * FROM [TableName] ORDER BY [ColumnName]
and also there are not that many records(MAX 100 rows) kept in the table at the moment however, it will grow later.
The issue stems out from the ORDER BY [ColumnName] part of the syntax that changes the TOP selection order which makes the query to run very slow as I have also analyzed in the SQL SERVER QUERY ANALYZER.
Anyhow, I need to have the ORDER BY statement to show the data based on different columns either ascending or descending.
There might we workarounds to achieve the same goal that I am not aware of.
Any thoughts is appreciated.
I am running a Stored procedure which select from a table and returns approx 800000 records. When calling from any client machine it takes long time to return the result (90 sec). It waits for ASYNC_NETWORK_IO which is pushing the result to client. If select statement is used with TOP operator to return less number of records it executes faster. When calling from the server the stored proc returns data in 13 sec with all records. In another machine of identical HW and configuration this problem is not there. Can anyone help how to improve ASYNC_NETWORK_IO issue?
Environment
SQL-2005 SP1 64 bit Standard on Active/Passive cluster
Windows -2003 Ent.
Thanks
-Ashis
Hi,
We are having SQL2000 Advance Server.
4 processor with hypherthreading, Memory 4 GB and it is a high transactional OLTP server. We are also running Transaction Replication on that server.
We recently bought Double Take and implemented on the server with File Difference with block check sum option for Disaster Recovery Purpose.
The Queue and the Log file folder is on local drive where the system doesn't use that folder except double take.
We are replicating from Source to Taget thru the WAN (DS3).We are replicating approx. 200 Gig of Data but just the difference.
Now the CPU usage is normal,Memory utilization is normal, but the network is a major problem as the Applications connecting to the Server timesout and the applications running very slow.
We have set just like the Tech support recommended.
I would appreciate, if someone give us some recommendations to run the double take without any problem.
Thanks in advance.
Anu
The following basic UPDATE SQL statement has been running for 16 hours and counting. I need to get this done ASAP.
UPDATE Recipients SET UndeliverableTime = getdate()
FROM Recipients
INNER JOIN Domains ON (Recipients.DomainID = Domains.ID)
INNER JOIN Undeliverables ON (
Recipients.UserName + '@' + Domains.Domain =
Undeliverables.EmailAddress)
Is there any way I can see how far this has gone and how long it will take to finish? Will this take another hour to finish or another week?
Both tables (Recipients and Undeliverables) have approximately 80 million records
I did a nearly identical operation with another table that had only 7 million records and it took 10.5 hours. I hope this doesn't scale linearly to 115 hours.
I am tempted to cancel, retune, and rerun but that may be trigger a really expensive rollback operation that could take days. Any ideas?
thanks!