A Procedure Runs Slow As A Job But Runs Fast Executed In A Query Window
Apr 23, 2008
Performance issue.
I have a very complex Stored Procedure called by a Job that is Scheduled to run every night.
It's execution takes sometimes 1 or 2 hours and sometimes 7 hours or more.
So, if it is running for more than 4 hours I stop the Job and I run the procedure from a Query Window and it never takes more than 2 hours.
Can anyone help me identify the problem ? I want to run from the Job and not to worry about it.
Some more information:
- It is SQL 2000 Enterprise with SP4 in a Cluster (It happens the same way in any node).
- The SQL Server and SQL Agent services run using a Domain Account that have full Administrative access.
- When I connect to a Query Window I also use a Windows Account.
- There is no locks or process bloking or being blocked while the job is running.
- Using the Task Manager the processor activity is ok, no more than 30 % in any processor.
I am able to run a query which runs FAst in QA but slow in theapplication.It takes about 16 m in QA but 1000 ms on theApplication.What I wanted to know is why would the query take a longtime in the application when it runs fast on SQL server?How should we try debugging it?Ajay
Ok, I'll admit right off the bat that I never suspected that I'd ever raise this complaint, much less worry about how to fix the "problem" associated with it!
We're preparing to take a large set of changes (projects) to PeopleSoft Financials from development to test. The code is still somewhat rough, but it has been "desk checked" to ensure that it does what the developers think that it ought to do, and they've blessed it at that point. The code is now moving into the test phase, and the QA team is finding locking/blocking issues that we've never seen in this code before... Sort of a "lock avalanche" where no one process locks for very long, but many of them block one another to the point where applications actually "freeze" while almost never hitting a deadlock.
My solution was to create a "blitzkrieg" query / stored procedure that would periodically sample master.dbo.sysprocesses, master.dbo.sysdatabases, and apply one of the dm_ functions to gather information on locking, blocking, and deadlocking. My procedure runs nicely (it never hangs) and gets about 99.3% of the data that I want.
The problem is that the blasted query / stored procedure runs either too fast or too slow, depending on how you look at it. Because the dm_ function takes a few ms to run, there can be a situation where either a row appears as a false positive or as a missing row because of timing... Either the culprit shows up as a blocker, but by the time the victim spid is evaluated the block has cleared, or the row is skipped and by the time the victim is evaluated the block has occured.
The whole process runs in well under 100 ms when there is nothing to report, and I've never seen it run 200 ms yet under the worst conditions it has faced, so the code is fast... The problem is that I really don't want to try to enforce any kind of locking to resolve the issue, because that locking would impact performance and that is EXACTLY what I do NOT want to do.
When i execute a stored procedure it generally takes about half a second to run but sometimes it takes 20 to 30 seconds. I am the only one using the server so I know it is not due to other traffic. I have looked at Profiler and nothing looks out of the ordinary. Another observation is that the slow ones are always near eachother. I will have about 10 fast executions and then 3 slow ones and then back to fast ones. Has anyone seen anything like this before?
I have a stored proceedure (which I will tag on at the end for those interested) which is taking at least 15 minutes to run when executed, but completes in 1 minute when the tsql statement is run in Query Analyser. Why is this?
I suspect that it may be connected to table indexing, but why then is this bypassed when QA is used?
Any advice appreciated.
Derek
************************************************** *********************** IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'dbo.sp_ValidateAIGL') AND OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE dbo.sp_ValidateAIGL GO
-- *** Global Non Program Specific Data Errors *** -- CHECK - that there are records in the DEB_IGL_PAYROLL_OUTPUT file.....none and the routine failed... IF NOT EXISTS(SELECT * FROM tbl_OUT_Payroll WHERE IGLProgramID = @IGLProgramID) INSERT INTO #IGLErrors SELECT NULL, 100, 'No records were processed by the IGL run!'
SELECT * FROM #IGLErrors
-- CHECK - search for any records where the employee's EXPENSE_CODE is NULL INSERT INTO #IGLErrors SELECT DISTINCT NULLIF(EmpNo, ''), 2, 'Employee "' + COALESCE(NULLIF(RTRIM(EmpNo), ''), '<Missing Employee>') + '" (Organisation Unit - ' + COALESCE(RTRIM(OrgUnitCode), '<No Organisation Unit>') + ') does not have a EXPENSE_CODE Code.' FROM tbl_OUT_Payroll WHERE NULLIF(ExpenseCode, '') IS NULL ANDIGLProgramID = @IGLProgramID
SELECT * FROM #IGLErrors -- CHECK - check that the BALANCE of DEBITs match the balance of CREDITs IF (SELECT SUM(Cash) FROM tbl_OUT_Payroll WHERE IsCredit = 1 AND IGLProgramID = @IGLProgramID) <> (SELECT SUM(Cash) FROM tbl_OUT_Payroll WHERE IsCredit = 0 AND IGLProgramID = @IGLProgramID) INSERT INTO #IGLErrors SELECT NULL, 3, 'The total cash value for DEBIT elements does not match the total cash for CREDIT elements.'
SELECT * FROM #IGLErrors -- *** Program 1 and 2 errors *** IF @IGLProgramID IN (1, 2) BEGIN -- CHECK - search for any records where the employee's COST_CENTRE is NULL INSERT INTO #IGLErrors SELECT DISTINCT NULLIF(EmpNo, ''), 1, 'Employee "' + NULLIF(RTRIM(EmpNo), '') + '" (Organisation Unit = ' + RTRIM(OrgUnitCode) + ') does not have a COST_CENTRE Code.' FROM tbl_OUT_Payroll WHERE NULLIF(CostCenter, '') IS NULL ANDIGLProgramID = @IGLProgramID
SELECT * FROM #IGLErrors
-- Check for EMPLOYEEs that were not transfered to the PAYROLL output (usually caused by missing ORG_UNITs or not picked up in -- the DEB_VIEW_APPOINTEE view...) INSERT INTO #IGLErrors SELECT DISTINCT EMP_NO, 11, 'Employee "' + RTRIM(EMP_NO) + '" was excluded from the summary. Check their Organisation Unit codes!' FROM PSELive.dbo.COSTING_OUTPUT WHERENOT EMP_NO IN (SELECT DISTINCT EmpNo FROM tbl_OUT_Payroll WHERE IGLProgramID = @IGLProgramID) ANDPERIOD_NO = @PeriodNo ANDTAX_YEAR = @TaxYear
SELECT * FROM #IGLErrors
-- Check that there are no ELEMENTS in the COSTING_OUTPUT table that don't exist in the tbl_IGLElements table INSERT INTO #IGLErrors SELECT DISTINCT ELEMENT, 12, 'Element "' + RTRIM(ELEMENT) + '" does not exist in the IGL Interface Elements table!' FROM PSELive.dbo.COSTING_OUTPUT WHERE ELEMENT NOT IN ( SELECT DISTINCT Element FROM tbl_IGLElements ) ANDPERIOD_NO = @PeriodNo
SELECT * FROM #IGLErrors
END
-- *** Add a error to indicate the number of errors *** IF EXISTS (SELECT * FROM #IGLErrors) INSERT INTO #IGLErrors SELECT 0, 0, 'Warning, there are ' + CAST(Count(*) AS VarChar(5)) + ' recorded errors!' FROM#IGLErrors
-- Transfer the records to the ErrorsLog table ready for the user to view... DELETE FROM tbl_SYSErrorsLog INSERT INTO tbl_SYSErrorsLog (IGLProgramID, OutputLogID, KeyField, ErrorID, Description) SELECT@ProgramLogID, @IGLPeriodID, KeyField, ErrorID, Description FROM #IGLErrors ORDER BY ErrorID
DROP TABLE #IGLErrors
SELECT * FROM tbl_SYSErrorsLog ORDER BY ErrorID
--SET NOCOUNT OFF
GO GRANT EXECUTE ON dbo.sp_ValidateAIGL TO Public GO
Some users complain that there computers run slow around noon. later it is fine. Anybody can tell me what is the problem? We have entivirus software installed on. Also i am runing back up database on the server. Many thanks.
The query below runs in sub second time if I don't call it as a stored procedure. I have looked at the execution plan for both the query and the query as a stored procedure and they are the same. When I put the query into a stored procedure it takes over 2 minutes to run. All feedback (even the ugly stuff) is more than welcome. I want to master this issue and forever put it behind me. This is the sql when I just execute it outright:1 DECLARE 2 @WebUserID nvarchar(20) 3 ,@DocumentTypeID int 4 ,@RouteID nvarchar(10) 5 ,@CustomerID nvarchar(15) 6 ,@DocumentIDPrefix nvarchar(20) 7 ,@StartDate datetime 8 ,@EndDate datetime 9 ,@OversoldOnly bit 10 ,@DexCustomersOnly bit 11 ,@DeviationsOnly bit 12 ,@CashNoPaymentOnly bit 13 ,@SignatureName nvarchar(45) 14 ,@SortExpression varchar(200) 15 ,@StartRowIndex int 16 ,@MaximumRows int 17 18 SET @WebUserID = 'manager' 19 SET @DocumentTypeID = 0 20 SET @DocumentIDPrefix = '%' 21 SET @StartDate = '04/17/2007' 22 SET @EndDate = '04/19/2007' 23 SET @OversoldOnly = 0 24 SET @DexCustomersOnly = 0 25 SET @DeviationsOnly = 0 26 SET @CashNoPaymentOnly = 0 27 SET @SortExpression = '' 28 SET @StartRowIndex = 0 29 SET @MaximumRows = 20; 30 31 WITH OrderedDocumentHistory AS 32 ( 33 SELECT 34 dh.DocumentHistoryID 35 ,dh.DocumentID 36 ,dh.DocumentTypeID 37 ,dh.DocumentTypeDesc 38 ,dh.RouteID 39 ,dh.RouteDesc 40 ,dh.CustomerID 41 ,dh.CustomerName 42 ,dh.DocDate 43 ,ISNULL(dc.HasReceipt, 0) AS 'HasReceipt' 44 ,ddt.Description AS 'SignatureReason' 45 ,a.Amount 46 ,ROW_NUMBER() OVER (ORDER BY dh.DocDate DESC) AS 'RowNumber' 47 FROM 48 DocumentHistory dh 49 INNER JOIN Customers c ON dh.CustomerID = c.CustomerID 50 INNER JOIN DeviationTypes ddt ON dh.DriverDeviationTypeID = ddt.DeviationTypeID 51 INNER JOIN 52 ( 53 SELECT 54 DocumentHistoryID 55 ,(COALESCE(SUM((CONVERT(INT, Units + DeviationUnits)) * (UnitPrice - UnitDiscount)) + SUM((CONVERT(INT, Cases + DeviationCases)) * (CasePrice - CaseDiscount)), 0.0)) AS Amount 56 FROM 57 DocumentHistoryItems dhia 58 GROUP BY 59 dhia.DocumentHistoryID 60 ) AS a ON a.DocumentHistoryID = dh.DocumentHistoryID 61 LEFT OUTER JOIN 62 ( 63 SELECT DISTINCT 64 dca.DocumentID 65 ,1 AS 'HasReceipt' 66 FROM 67 DocumentCollections dca 68 ) AS dc ON dh.DocumentID = dc.DocumentID 69 WHERE 70 dh.DocDate BETWEEN @StartDate AND @EndDate 71 AND (dh.DocumentTypeID = @DocumentTypeID OR @DocumentTypeID IS NULL) 72 AND (dh.RouteID = @RouteID OR @RouteID IS NULL) 73 AND (dh.CustomerID = @CustomerID OR @CustomerID IS NULL) 74 AND dh.DocumentID LIKE @DocumentIDPrefix 75 AND CASE WHEN @OversoldOnly = 1 THEN ISNULL( (SELECT TOP 1 (dhio.DeviationUnits + dhio.DeviationCases) FROM DocumentHistoryItems dhio WHERE dh.DocumentHistoryID = dhio.DocumentHistoryID AND (dhio.DeviationUnits > 0 OR dhio.DeviationCases > 0)), 0) ELSE 1 END > 0 76 AND CASE WHEN @DexCustomersOnly = 1 THEN c.DEXEnable ELSE 'Y' END = 'Y' 77 AND CASE WHEN @DeviationsOnly = 1 THEN ISNULL( (SELECT TOP 1 (dhio.DeviationUnits + dhio.DeviationCases) FROM DocumentHistoryItems dhio WHERE dh.DocumentHistoryID = dhio.DocumentHistoryID AND (dhio.DeviationUnits != 0 OR dhio.DeviationCases != 0)), 0) ELSE 1 END != 0 78 AND CASE WHEN @CashNoPaymentOnly = 1 THEN dh.Terms ELSE 'CHECK/CASH' END = 'CHECK/CASH' 79 AND CASE WHEN @CashNoPaymentOnly = 1 THEN (SELECT MAX(dhio.AlcoholPct) FROM DocumentHistoryItems dhio WHERE dhio.DocumentHistoryID = dh.DocumentHistoryID) ELSE 1 END > 0 80 AND CASE WHEN @CashNoPaymentOnly = 1 THEN ISNULL(dc.HasReceipt, 0) ELSE 0 END = 0 81 AND (dh.SigName = @SignatureName OR @SignatureName IS NULL) 82 AND (c.WarehouseID IN (SELECT WarehouseID FROM WebUserWarehouses WHERE WebUserID = @WebUserID) 83 OR @WebUserID IS NULL) 84 ) 85 86 SELECT 87 DocumentHistoryID 88 ,DocumentID 89 ,DocumentTypeDesc 90 ,RouteID 91 ,RouteDesc 92 ,CustomerID 93 ,CustomerName 94 ,DocDate 95 ,Amount 96 ,HasReceipt 97 ,SignatureReason 98 FROM 99 OrderedDocumentHistory 100 WHERE 101 RowNumber BETWEEN (@StartRowIndex + 1) AND (@StartRowIndex + @MaximumRows) Here is the sql for creating the stored procedure. 1 CREATE Procedure w_DocumentHistory_Select 2 ( 3 @WebUserID nvarchar(20) 4 ,@DocumentTypeID int 5 ,@RouteID nvarchar(10) 6 ,@CustomerID nvarchar(15) 7 ,@DocumentIDPrefix nvarchar(20) 8 ,@StartDate datetime 9 ,@EndDate datetime 10 ,@OversoldOnly bit 11 ,@DexCustomersOnly bit 12 ,@DeviationsOnly bit 13 ,@CashNoPaymentOnly bit 14 ,@SignatureName nvarchar(45) 15 ,@SortExpression varchar(200) 16 ,@StartRowIndex int 17 ,@MaximumRows int 18 ) 19 AS 20 SET NOCOUNT ON 21 22 IF LEN(@SortExpression) = 0 OR @SortExpression IS NULL 23 SET @SortExpression = 'Number DESC' 24 25 IF @StartRowIndex IS NULL 26 SET @StartRowIndex = 0 27 28 IF @MaximumRows IS NULL 29 SELECT 30 @MaximumRows = COUNT(dh.DocumentHistoryID) 31 FROM 32 DocumentHistory dh; 33 34 WITH OrderedDocumentHistory AS 35 ( 36 SELECT 37 dh.DocumentHistoryID 38 ,dh.DocumentID 39 ,dh.DocumentTypeID 40 ,dh.DocumentTypeDesc 41 ,dh.RouteID 42 ,dh.RouteDesc 43 ,dh.CustomerID 44 ,dh.CustomerName 45 ,dh.DocDate 46 ,ISNULL(dc.HasReceipt, 0) AS 'HasReceipt' 47 ,ddt.Description AS 'SignatureReason' 48 ,a.Amount 49 ,CASE 50 WHEN @SortExpression = 'Number DESC' THEN (ROW_NUMBER() OVER (ORDER BY dh.DocumentID DESC)) 51 WHEN @SortExpression = 'Number ASC' THEN (ROW_NUMBER() OVER (ORDER BY dh.DocumentID ASC)) 52 WHEN @SortExpression = 'CustomerName DESC' THEN (ROW_NUMBER() OVER (ORDER BY dh.CustomerName DESC)) 53 WHEN @SortExpression = 'CustomerName ASC' THEN (ROW_NUMBER() OVER (ORDER BY dh.CustomerName ASC)) 54 WHEN @SortExpression = 'CompletedDate DESC' THEN (ROW_NUMBER() OVER (ORDER BY dh.DocDate DESC)) 55 WHEN @SortExpression = 'CompletedDate ASC' THEN (ROW_NUMBER() OVER (ORDER BY dh.DocDate ASC)) 56 WHEN @SortExpression = 'RouteDescription DESC' THEN (ROW_NUMBER() OVER (ORDER BY dh.RouteDesc DESC)) 57 WHEN @SortExpression = 'RouteDescription ASC' THEN (ROW_NUMBER() OVER (ORDER BY dh.RouteDesc ASC)) 58 END AS 'RowNumber' 59 FROM 60 DocumentHistory dh 61 INNER JOIN Customers c ON dh.CustomerID = c.CustomerID 62 INNER JOIN DeviationTypes ddt ON dh.DriverDeviationTypeID = ddt.DeviationTypeID 63 INNER JOIN 64 ( 65 SELECT 66 DocumentHistoryID 67 ,(COALESCE(SUM((CONVERT(INT, Units + DeviationUnits)) * (UnitPrice - UnitDiscount)) + SUM((CONVERT(INT, Cases + DeviationCases)) * (CasePrice - CaseDiscount)), 0.0)) AS Amount 68 FROM 69 DocumentHistoryItems dhia 70 GROUP BY 71 dhia.DocumentHistoryID 72 ) AS a ON a.DocumentHistoryID = dh.DocumentHistoryID 73 LEFT OUTER JOIN 74 ( 75 SELECT DISTINCT 76 dca.DocumentID 77 ,1 AS 'HasReceipt' 78 FROM 79 DocumentCollections dca 80 ) AS dc ON dh.DocumentID = dc.DocumentID 81 WHERE 82 dh.DocDate BETWEEN @StartDate AND @EndDate 83 AND (dh.DocumentTypeID = @DocumentTypeID OR @DocumentTypeID IS NULL) 84 AND (dh.RouteID = @RouteID OR @RouteID IS NULL) 85 AND (dh.CustomerID = @CustomerID OR @CustomerID IS NULL) 86 AND dh.DocumentID LIKE @DocumentIDPrefix 87 AND CASE WHEN @OversoldOnly = 1 THEN ISNULL( (SELECT TOP 1 (dhio.DeviationUnits + dhio.DeviationCases) FROM DocumentHistoryItems dhio WHERE dh.DocumentHistoryID = dhio.DocumentHistoryID AND (dhio.DeviationUnits > 0 OR dhio.DeviationCases > 0)), 0) ELSE 1 END > 0 88 AND CASE WHEN @DexCustomersOnly = 1 THEN c.DEXEnable ELSE 'Y' END = 'Y' 89 AND CASE WHEN @DeviationsOnly = 1 THEN ISNULL((SELECT TOP 1 (dhio.DeviationUnits + dhio.DeviationCases) FROM DocumentHistoryItems dhio WHERE dh.DocumentHistoryID = dhio.DocumentHistoryID AND (dhio.DeviationUnits != 0 OR dhio.DeviationCases != 0)), 0) ELSE 1 END != 0 90 AND CASE WHEN @CashNoPaymentOnly = 1 THEN dh.Terms ELSE 'CHECK/CASH' END = 'CHECK/CASH' 91 AND CASE WHEN @CashNoPaymentOnly = 1 THEN (SELECT MAX(dhio.AlcoholPct) FROM DocumentHistoryItems dhio WHERE dhio.DocumentHistoryID = dh.DocumentHistoryID) ELSE 1 END > 0 92 AND CASE WHEN @CashNoPaymentOnly = 1 THEN ISNULL(dc.HasReceipt, 0) ELSE 0 END = 0 93 AND (dh.SigName = @SignatureName OR @SignatureName IS NULL) 94 AND (c.WarehouseID IN (SELECT WarehouseID FROM WebUserWarehouses WHERE WebUserID = @WebUserID) 95 OR @WebUserID IS NULL) 96 ) 97 SELECT 98 DocumentHistoryID 99 ,DocumentID 100 ,DocumentTypeDesc 101 ,RouteID 102 ,RouteDesc 103 ,CustomerID 104 ,CustomerName 105 ,DocDate 106 ,Amount 107 ,HasReceipt 108 ,SignatureReason 109 FROM 110 OrderedDocumentHistory 111 WHERE 112 RowNumber BETWEEN (@StartRowIndex + 1) AND (@StartRowIndex + @MaximumRows)
Here is the code for calling the stored procedure:1 DECLARE @RC int 2 DECLARE @WebUserID nvarchar(20) 3 DECLARE @DocumentTypeID int 4 DECLARE @RouteID nvarchar(10) 5 DECLARE @CustomerID nvarchar(15) 6 DECLARE @DocumentIDPrefix nvarchar(20) 7 DECLARE @StartDate datetime 8 DECLARE @EndDate datetime 9 DECLARE @OversoldOnly bit 10 DECLARE @DexCustomersOnly bit 11 DECLARE @DeviationsOnly bit 12 DECLARE @CashNoPaymentOnly bit 13 DECLARE @SignatureName nvarchar(45) 14 DECLARE @SortExpression varchar(200) 15 DECLARE @StartRowIndex int 16 DECLARE @MaximumRows int 17 18 SET @WebUserID = 'manager' 19 SET @DocumentTypeID = 0 20 SET @DocumentIDPrefix = '%' 21 SET @StartDate = '04/17/2007' 22 SET @EndDate = '04/19/2007' 23 SET @OversoldOnly = 0 24 SET @DexCustomersOnly = 0 25 SET @DeviationsOnly = 0 26 SET @CashNoPaymentOnly = 0 27 SET @SortExpression = '' 28 SET @StartRowIndex = 0 29 SET @MaximumRows = 20; 30 31 EXECUTE @RC = [Odom].[dbo].[w_DocumentHistory_Select] 32 @WebUserID 33 ,@DocumentTypeID 34 ,@RouteID 35 ,@CustomerID 36 ,@DocumentIDPrefix 37 ,@StartDate 38 ,@EndDate 39 ,@OversoldOnly 40 ,@DexCustomersOnly 41 ,@DeviationsOnly 42 ,@CashNoPaymentOnly 43 ,@SignatureName 44 ,@SortExpression 45 ,@StartRowIndex 46 ,@MaximumRows
Hello , I'm a newbie,I programmed a store procedure,and when it runs in query analyzer, I get results very fast,but when I add it in a job,then this job run very very slow, (I add records and delete records in the SP), WHY?
We are having issues w/ a stored proc call in our application. When I run the proc through a query window in Mgmt Studio, it comes back in .3 seconds. However, when the application runs the proc (w/ the same parameters), it takes 30 seconds for the proc to complete. When I run a trace with the proc call through Mgmt Studio, it says 4000 read, but through the app, it says 4 million reads. What is happening?? (app uses Hibernate 3.2/ Java 1.5/ JDBC)
I have SQL Server and OLAP Server running on an NT Server box, and a pc running NT Workstation which has OLAP Manager installed on it. I can register the OLAP Server just fine in OLAP Manager, but it takes about 30sec to a minute for the pc to actually connect to the server.
We've also installed SQL Server and OLAP Server on a Win 2000 machine, and the OLAP connection is faster.
Could this be related to a network routing issue, OLAP or SQL server install issue?
I am using SSIS packages for data transfer, When i run the package on virtual server it takes more time as when run on a PC. After analysing i found that Package when run on Virtual server takes time in startup around (50 sec) or so.Could anyone help me with a little bit of detail description as to why it runs slow.
When I launch Outlook, it takes forever for the program to finally open. With any inbound email, it stops processing whatever is underway at the time....and frequently there is a 2-3 second lag between keyboard input and what appears on the screen. SQLserver is usually consuming upwards of 1-gb of memory....help. Mike
We have a static class that makes an HTTPWebRequest to get XML data from one of our vendors. We use this as input to a stored proc in SQLServer2005. When I compile this class and call it from a console application in visual studio it executes in milliseconds, everytime. When I compile it, create the assembly and clr function and execute it in SQLServer, it takes around 14 seconds to execute the first time, then on subsequent requests it is again really fast, until I wait for 10 seconds and re-execute, once again it is slow the first time and then fast on subsequent requests. We do not see this behavior when executing outside SQLServer. Makes me think that some sort of authentication is perhaps taking place the first time the function is run in SQLServer? I have no idea how to debug this further. Anyone seen this before or have any ideas?
Here is the class:
Code Snippet
using System; using System.Collections.Generic; using System.Text; using System.Net; using System.IO;
namespace Predict.Services { public static class Foo { public static string GetIntradayQuote(string symbol) { string returnQuote = "";
HttpWebRequest request = (HttpWebRequest)(WebRequest.Create("http://data.predict.com/predictws/detailed_quote.html?syms=" + symbol + "&fields=1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,28,30"));
StreamReader streamReader = new StreamReader(response.GetResponseStream());
returnQuote = streamReader.ReadToEnd();
streamReader.Close(); response.Close();
return returnQuote; } } }
When I run call it from a console app it is fine.
I compile it into a dll and then create the assembly and function as follows:
Code Snippet
drop function fnTestGetIntradayQuoteXML_SJS
go
drop assembly TestGetIntradayQuoteXML_SJS
go
create ASSEMBLY TestGetIntradayQuoteXML_SJS from 'c:DataBackupsCLRLibrariesTestGetIntradayQuote_SJS.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS
go
CREATE FUNCTION fnTestGetIntradayQuoteXML_SJS(@SymbolList nvarchar(max)) RETURNS nvarchar(max) AS EXTERNAL NAME TestGetIntradayQuoteXML_SJS.[Predict.Services.Foo].GetIntraDayQuote
go
declare @testing nvarchar(max)
set @testing = dbo.fnTestGetIntradayQuoteXML_SJS('goog')
print @testing
When I execute the function as above, again, really slow the first time, then fast on subsequent calls. Could there be something wrong with the code, or some headers that need to be set differently to operate from the CLR in SQLServer?
My procedure compliles and runs. l 'm running it as 'exec Statement' it should prompt me for the start and end date but it does not. Where have l gone wrong in my logic l've declared all the variables need. Please advice
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Statement]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[Statement] GO
SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO
Create Procedure Statement As Begin
declare @Startdate As datetime declare @Enddate As datetime
declare @Customer_No As char(15) declare @loanno As char(15)
declare @transaction_date As datetime declare @transaction_type As char(3) declare @reference As varchar(20) declare @notes As varchar(255) declare @transaction_amount As decimal (9,2) declare @transaction_description As varchar(50)
declare @debit_amount As decimal (9,2) declare @credit_amount As decimal (9,2) declare @counter As int declare @balance As decimal (9,2) declare @user_changed As char(8)
declare c2 CURSOR FOR SELECT loan_no FROM loan where customer_no = @Customer_No ORDER BY loan_no
OPEN c2 FETCH NEXT FROM c2 INTO @loanno
-- Check @@FETCH_STATUS to see if there are any more rows to fetch. WHILE @@FETCH_STATUS = 0 BEGIN
declare c1 CURSOR FOR SELECT transaction_record.loan_no, transaction_record.transaction_date, transaction_record.transaction_type, transaction_record.reference_no, transaction_record.notes, transaction_record.transaction_amount, transaction_type.[description] FROM transaction_record inner join transaction_type on transaction_type.transaction_type = transaction_record.transaction_type where loan_no = @loanno and transaction_Date between @startdate and @enddate and transaction_amount <> 0 ORDER BY transaction_date
OPEN c1 FETCH NEXT FROM c1 INTO @transaction_date, @transaction_type, @reference, @notes, @transaction_amount, @transaction_description
-- Check @@FETCH_STATUS to see if there are any more rows to fetch. WHILE @@FETCH_STATUS = 0 BEGIN If (@transaction_amount < 0) Begin set @credit_amount = @transaction_amount set @debit_amount = 0 End Else Begin set @debit_amount = @transaction_amount set @credit_amount = 0 End
If (@counter = 0) Begin set @balance = @transaction_amount End Else Begin set @balance = @balance + @transaction_amount End
FETCH NEXT FROM c1 INTO @transaction_date, @transaction_type, @reference, @notes, @transaction_amount, @transaction_description End CLOSE c1 DEALLOCATE c1
FETCH NEXT FROM c2 INTO @loanno End CLOSE c2 DEALLOCATE c2 END; Go
This is my procedure and the error is incorrect syntax near '01'
DECLARE @returnDay int
--Looking at current date, SELECT @returnDay = DatePart(day,GetDate()) --If is the 7th of the current moth then If @returnDay = 24
EXEC master.dbo.xp_sendmail @query = 'SELECT a.HospitalName,a.HospitalCode,c.ProductName,b.UnitsDiscarded,d.FateOfProducts,b. DateEntered, b.DateCompleted,b.CompiledBy FROM test.dbo.Units b inner join Hospitals a ON (a.HospitalID = b.HospitalID) inner join Products c ON (b.ProductID = c.ProductID) inner join FateOfProducts d ON (d.FateID = b.FateID) where b. DateEntered = DateAdd(month, -1, Convert(CHAR(8), GetDate(), 121) + '01') order by a.HospitalID', @recipients=test@hotmail.com', @message='Submitting Results for the previous month', @subject=' results for previous month', @attach_results = 'true', @separator = '/s'
SELECT @@ERROR As ErrorNumber
What am I missing here now, I am quite new to stored procedures
We have a customer table and an address table. Each customer can have 1 or more addresses in the address table.
The task is to synchronize customer information from 2 entirely separate systems. I have a stored procedure that reads a text file (exported from the 'master' system) and updates all the customer records in the second system (which is in the sql server db). The queries themselves work. The data is accurate. My issue is with how long it takes the stored procedure to run. With over 11,000 records in the text file, the stored procedure sometimes takes over 3 hours to run.
That seems excessive to me. (and probably to those of you snickering in the back)
As an overview: my stored procedure does the following.
1) creates a temp table 2) dumps the data from the text file into the temp table 3) updates the address table with data from the temp table (records already there) 4) inserts records into the address table from the temp table (records not already there) 5) updates the customer table with an address id 6) drops the temp table
Any help/suggestions is appreciated. If you need more info, let me know.
I have a stored procedure that syncs 2 databases. It runs fine if I go into the stored procedures and execute it manually, there are a few errors but it doesnt choke on them it completes with the desired end result. However if I create a job to run this dbo, it fails, The error log shows this:
Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted 10/03/2007 16:06:00,sync,Error,0,MACHINENAME,sync,(Job outcome),,The job failed. The Job was invoked by User MACHINENAMEAdministrator. The last step to run was step 1 (sync).,00:00:19,0,0,,,,0 10/03/2007 16:06:00,sync,Error,1,MACHINENAME,sync,sync,,Executed as user: companyname. ... 0) aspnet Member Exists [SQLSTATE 01000] (Message 0) DNN User Exists [SQLSTATE 01000] (Message 0) Updaing Roles __noelle(5281) [SQLSTATE 01000] (Message 0) aspnet User Exists [SQLSTATE 01000] (Message 0) aspnet Member Exists [SQLSTATE 01000] (Message 0) DNN User Exists [SQLSTATE 01000] (Message 0) Updaing Roles 0(5036) [SQLSTATE 01000] (Message 0) aspnet User Exists [SQLSTATE 01000] (Message 0) aspnet Member Exists [SQLSTATE 01000] (Message 0) New DNN User [SQLSTATE 01000] (Message 0) aspnet User Exists [SQLSTATE 01000] (Message 0) aspnet Member Exists [SQLSTATE 01000] (Message 0) DNN User Exists [SQLSTATE 01000] (Message 0) Updaing Roles 0000000(3795) [SQLSTATE 01000] (Message 0) aspnet User Exists [SQLSTATE 01000] (Message 0) aspnet Member Exists [SQLSTATE 01000] (Message 0) DNN User Exists [SQLSTATE 01000] (Message 0) Updaing Roles 007(8) [SQLSTATE 01000] (Message 0) aspnet User Exists [SQLSTATE 01000] (Message 0) aspnet Member Exis... The step failed.,00:00:19,14,3621,,,,0
So the error is 3621, and the severity is 14 which as I understand it means Insufficient Permissions.
I have already tried this: Making the Job Agent service logon as MACHINeNAME/Administrator instead of System, giving the user here (companyname) more permissions, but I am at a loss as to why this runs ok when manually executed but fails when run as a job. I have created the job by scripting the dbo "Execute to a file" and having the job run that file as a step, I have also tried copying and pasting the code from the stored procedure into the job step. It always fails for the same reason.
I must say I am very new to SQL, I am a network admin and have inherited this database and server from another client and have to get this sync task to run daily at certain intervals.
Please can anyone shed light on this issue? Thanks,
Good afternoon, i'm new to Functions on the SQL server
I'm trying to create a dynamic query that would select the the column passed to the function from a certain table
my table called selected_Date, and has StartDate, and EndDate columns
when the user select for example "StartDate", i pass this as a variable to the function which runs the query. but i always gets back the passed string as a result..
I am running the following MDX query through a DataReader and a Ado.Net Connection.
SELECT {[Measures].[Deuda Total Nacional], [Measures].[Deuda Total Nacional Maximo], [Measures].[Cupo Nacional], [Measures].[Porcentaje Utilizacion Maximo], [Measures].[Pago Minimo Estado Cuenta], [Measures].[Deuda Ultima Facturacion], [Measures].[Dias Mora], [Measures].[Dias Mora Maximo], } ON COLUMNS ,[Cuenta].[Cuenta].[Cuenta]*[Cuenta].[Rut].[Rut]*[Cuenta].[Dv].[Dv] ON ROWS
FROM [Bd Rtd] WHERE [Tiempo].[Mes].&[2007-09-01T00:00:00]
The thing is, when I have about 10 thousand rows It runs in about 50 seconds which is good, but when I run this query and I have processed the cube with 100 thousand rows it runs out of memory and crashes.
I'm working in a shared development server with 1GB of memory for my project.
Is there any way to make it run anyway?? I mean even if it has to swap.
thanks
By the way when this thing goes into production it will have 1.5 million rows
I am writing a sales managing software using C# in .NET 2005. The program was used to work properly with sql server 2000. I decided to write a "FILE Version" of sofware using sql server express 2005 , and detached db from 2000 , attaching it to 2005 using AttachDBFileName clause in connection string.
But the problem is when program executes something like this: EXEC [AddOrder] ... two rows inserted instead of one.and when I use server explorer of VS2005 to launch SP, it works fine. I should mention again that same code works correctly when I change connection string and force it to use sqlserver 2000.
I have a rather complex sp that runs for 4 minutes in SQL2000. It computes some messy oil and gas revenue/cost transactions. It involves lots of calls to functions that return single values of all types and also returns recordsets. There are all kinds of joins with temp and memory tables, etc. Just a mess, but it works. However, in SQL2005, it runs and apparently hangs, as it never ends.
I have run the Upgrade Advisor and otherwise, have not found any information that tells me that there are issues with functions or SQL-specific functions, tables, etc. that might cause this. Does anyone on this forum have some pointers on where I might look for assistance on this matter? Surely someone knows something about things working differently in 2005.
I have a query which joins multiple tables. This query has suddenly begun to take up to 2 minutes to run (vs. 5-10 seconds previously).
No major change in number of records in the tables (currently about 220,000). When I remove the PK from one of the tables which then forces a tablescan, the query returns to running in 5-10 seconds. If I add the PK back, performance is back to 2 minutes.
I have two servers: one production and one development. There is a third party query that runs on both servers. Yes, the query is poorly tuned - I cannot change it. In production, it runs in 54 minutes. In development, I have tried to let it run for days and it never completes.
Here's what I have tried so far:
Compared the settings of production vs. development. The settings are very similar - the development box is larger, 4 times more memory.
Max degree of parallelism is the same on both boxes.
No compression on both boxes.
The production server is fairly busy, the development server is empty - this is the only process running on it.
Plenty of free disk space.
Updated all statistics on all databases touched by the query on dev.
Indexing is the same on both boxes.
The development box is running MSSQL2012.
The production box is running MSSQL2008R2.
What I've noticed:
The query consumes a massive amount of CPU time.
HUGE number of reads (16 million reads for 10 writes according to sp_WhoIsActive)
Largest wait types are CXPACKET, SOS_SCHEDULER_YIELD and TRACEWRITE respectively. wait_typesum_wait_time_mspct_wait_timesum_waiting_tasksavg_wait_time_ms CXPACKET4142765580.7176307423.5 SOS_SCHEDULER_YIELD24146944.7532725530.0 TRACEWRITE19856343.914831338.9
I would like to create a query to find what user owns the job. It probably is in the master db, but I wouldn't know where to begin other than that. Telling me how to either change the job owner or create a job through t-sql would also help. Thanks -Kyle
Recently we started using Pass throughs to perofmr large inserts, however we have started to notice that some of these pass throughs are executing twice, and therefore duplicating data.
Is this a known bug, and if not has anyone got any advice on what could be causing it?
We're connecting from Access 2002 (SP6) to SQL Server 2000 Enterprise.
The conenction string / code is as follows :-
Dim cmd As ADODB.Command Set cmd = New ADODB.Command Set cmd.ActiveConnection = CurrentProject.Connection cmd.Properties("Jet OLEDBDBC Pass-Through Statement") = True cmd.Properties _ ("Jet OLEDB*** Through Query Connect String") = _ "ODBC;DSN=" & myDatabaseShort & ";DATABASE=" & myDatabase & ";UID=sa;PWD=" & Left(myDatabaseShort, 4) & ";"
It connects fine. My sql string is a straightforward Insert statement that only executes once via SQL Query Analyzer.
I'm calling the pass through using the following lines of code:-
cmd.CommandText = mysql cmd.Execute
Can anyone see anything obvious that I'm doing wrong, or is this a known issue?
I have a query I developed and optimised as well as possible beforeconverting to a view.When running in query analyser the query runs in 15 to 18 seconds -which is acceptable.When "converting" into a view ( This is necessary for operationalreasons) and running with the same parameter it runs in 3 to 4minutes.Is there something I am unaware of ( well of course there is !!) -I was wondering why this occurs and how I can avoid / correct theissue.All advice gratefully received.Dave ( Still learning stuff about SQL Server every day!!)
A colleague of mine has a query which fails to run under SQLAgentbatch with the following error:The conversion of a char data type to a datetime data type resulted inan out-of-range datetime value. [SQLSTATE 22007] (Error 242) Thestatement has been terminated. [SQLSTATE 01000] (Error 3621). Thestep failed.He can run the same query sucessfully via query analyzer (i.e. noerrors, and it does what he wants)If I try to run the same query through Query Analyzer on myworkstation, I get a different error altogether:Server: Msg 242, Level 16, State 3, Procedure DateForGrouping, Line 11The conversion of a char data type to a datetime data type resulted inan out-of-range datetime value.Any idea what might be causing these differences in behaviourdepending on how and/or where the query is run from?The (working) statement in question is:================================================== ======================insert into Summary_ReferrerSalesselect DateCreated,ReferrerID,ReferrerIDCount,PUIDCount,ReferrerDescription,0 as TotalOrderValue,0 as TotalOrderLinesfrom vw_ReferrerPopularityWarning: Null value is eliminated by an aggregate or other SEToperation.(11996 row(s) affected)================================================== ====================And the table / view / function definitions (I take no responsibilityfor the view definition!) are:CREATE TABLE [Summary_ReferrerSales] ([DateCreated] [datetime] NULL ,[ReferrerID] [char] (2) NULL ,[ReferrerIDCount] [int] NULL ,[PUIDCount] [int] NULL ,[ReferrerDescription] [nchar] (100) NULL ,[TotalOrderValue] [numeric](18, 0) NULL ,[TotalOrderLines] [numeric](18, 0) NULL) ON [PRIMARY]CREATE VIEW dbo.vw_ReferrerPopularityASSELECT TOP 100 PERCENT COUNT(dbo.LogReferrerID.Referrer) ASReferrerIDCount,COUNT(DISTINCT dbo.LogReferrerID.PUID) AS PUIDCount,dbo.DateForGrouping(dbo.LogReferrerID.DateUsed)AS DateCreated,dbo.LogReferrerID.Referrer AS ReferrerID,dbo.LookupReferrerID.ReferrerDescriptionFROMdbo.LogReferrerID INNER JOIN dbo.LookupReferrerIDON dbo.LogReferrerID.Referrer = dbo.LookupReferrerID.ReferrerIDWHERE (dbo.LogReferrerID.DateUsed > CONVERT(DATETIME, '2003-09-0100:00:00', 102))GROUP BY dbo.LogReferrerID.Referrer,dbo.DateForGrouping(dbo.LogReferrerID.DateUsed),dbo.LookupReferrerID.ReferrerDescriptionHAVING (dbo.LogReferrerID.Referrer <> 'WS')AND (COUNT(dbo.LogReferrerID.Referrer) IS NOT NULL)AND (dbo.LookupReferrerID.ReferrerDescription IS NOT NULL)AND (dbo.DateForGrouping(dbo.LogReferrerID.DateUsed) >CONVERT(DATETIME, '2003-09-01 00:00:00', 102))AND (COUNT(DISTINCT dbo.LogReferrerID.PUID) IS NOT NULL)ORDER BY dbo.DateForGrouping(dbo.LogReferrerID.DateUsed) DESCCREATE TABLE [LogReferrerID] ([LogReferrerID] [int] NULL ,[Referrer] [varchar] (2) NULL ,[DateUsed] [smalldatetime] NULL ,[HTTPReferrer] [varchar] (1000) NULL ,[TargetURL] [varchar] (1000) NULL ,[QueryString] [varchar] (1000) NULL ,[PUID] [varchar] (50) NULL) ON [PRIMARY]CREATE TABLE [LookupReferrerID] ([ReferrerID] [char] (2) NOT NULL ,[ReferrerDescription] [varchar] (100) NOT NULL ,CONSTRAINT [PK_Lookup_ReferrerID] PRIMARY KEY CLUSTERED([ReferrerID]) ON [PRIMARY]) ON [PRIMARY]CREATE FUNCTION dbo.DateForGrouping (@RowDate datetime)RETURNS datetime ASBEGINdeclare @datestring char(10)set @datestring=cast(datepart(dd,@RowDate) as char(2)) + '/' +cast(datepart(mm,@RowDate) as char(2)) + '/' +cast(datepart(yyyy,@RowDate) as char(4))return cast(@datestring as datetime)END
This is in ASP.NET 1.1. I have a performance problem with a webform. The form contains several bound fields and a couple datagrids. I fill the grids by creating a data adapter, then I use the adapter to fill a dataset, then I set the grid datasource to the dataset. The query to fill one of the grids is getting a SQL timeout when running from my application (it takes about 40 seconds to complete). When I run the same SQL code from SQL Query Analyzer it runs in less than 1 second. (it is embedded sql in the app, not a stored procedure). I suspect that something else is being requested from SQL during the postback that is causing a blocking issue or something but I can't tell exactly what is happening. I've tried tracing through the code and all that I can tell is that the timeout occurs during the dataadapter.fill command. Has anyone else seen something similar? Is there a good way for me to see what SQL commands are being executed from ASP.NET? Any advice on debugging this would be much appreciated.
We're on SQL 2005 SP1, IIS6, ASP.NET 2.0Data access is 3 tier (.xsd, BLL) similar to the data tutorials.From SQL Profiler:ASP.NETexec dbo.SpGetCatalogCategories @Show='Children',@ServiceId=31016,@BrandId=NULL,@CategoryId=NULL,@ParentId=10028,@ApplicationId=NULL,@ShowUniversal=1Event Class: RPC:Completed CPU: 5,109Reads: 613,174Writes: 0Duration: 5,133Management Studioexec dbo.SpGetCatalogCategories @Show='Children',@ServiceId=31016,@BrandId=NULL,@CategoryId=NULL,@ParentId=10028,@ApplicationId=NULL,@ShowUniversal=1Event Class: SQL:BatchCompletedCPU: 407Reads: 2,182Writes: 0Duration: 409I can post the SP if it will help.This is in a production enviroment and any help is greatly appreciated.kyle
The following query runs fine on sql 7.0 but it kind of hangs/keeps running without any output on sql server 2000 --------------------- Set @cmd = 'Update ABCD Set '+@day+'_LB = IsNull(LB,0), '+@day+'_UT = IsNull(UNIT,0) From tempData as T Where T.STORE_NUM = ABCD.STORE_NUM And T.ITM_ID = ABCD.UPC' execute (@cmd) ------------------------------------ But if we hard code the @day parameter and run the query like this it runs very fast on sql 2000....
Update ABCD Set THIRD_LB = IsNull(LB,0), THIRD_UT = IsNull(UNIT,0) From tempData as T Where T.STORE_NUM = ABCD.STORE_NUM And T.ITM_ID = ABCD.UPC ------------------------------
