Why Is Execution Of A Storedprocedure In QueryAnalyzer Faster Thanexecuting This SP As Scheduled Job?
Jul 23, 2005
Hi NG!
I wrote a stored procedure which at first deletes 100.000 rows and then
inserts 100.000 new rows.
There is a huge difference between executing this SP in the
query-analyzer (runtime approx. 2 minutes), and scheduling this SP as an
Job (runtime > 30 minutes!!!).
Why? Whats the issue?
And how can i improve the "job"-runtime?
The INSERT-Statement gets the data from a joined and grouped SELECT -
here is the code (exemplary):
INSERT INTO [Table1]
(Field1, Field2,... )
SELECT DISTINCT Field1, Count(field2), (Count(field2) * (-1))
FROM Table2 T2
INNER JOIN Table3 T3 ON T2.[ID] = T3.[ID]
INNER JOIN Table4 T4 ON T2.[DID] = T4.[ID]
INNER JOIN Table5 T5 ON T2.[SID] = T5.[ID]
GROUP BY field1, field2...
I have noticed something strange: After executing the SP the
queryanalyzer outputs "1 Row(s) affected." line by line - 100.000
times... Obviously every line is inserted one by one.
Any idea?
Would it help, to create a temporary-table with SELECT INTO, and then
just INSERT this data (w/o aggregation) into the target table?
Any help is greatly appreciated, tia and best regards
Here's my case, I have written a stored procedure which will perform the following: 1. Grab data from a table using cursor, 2. Process data, 3. Write the result into another table
If I execute the stored procedure directly (thru VS.NET, or Query Analyser), it will run, but when I tried to execute it via a scheduled job, it fails.
I used the same record, same parameters, and the same statements to call the stored procedure.
In SQL 7.0 jobs that have been scheduled start correctly, but jobs will not start when requested manually. All services are running. The only way to fix this problem has been to reboot the server. Does anybody have any ideas what might be causing this situation.
I am new to service broker but I heard that maybe can be useful for my needs. I have to install my DB to many different machines with SQL Server 2005 express edition and I need a scheduled execution of a procedure (each night ay 4 AM). Due to the "express" I don't have the server agent and due to the kind of the application I can't use task scheduler in order to execute a command by sqlcmd. So, service broker (included inside the DB instance, correct?!?) can helps me?
My DTS Package work fine if I Execute it manually, but I need to do it automatically just after midnight. I defined my schedule and made sure the job was present in the SQL Server Agent>Jobs, but it fails and the Job History shows the following error:
DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string: [Microsoft][ODBC Microsoft Access Driver] Cannot start your application. The workgroup information file is missing or opened exclusively by another user. Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 1901 (76D) Error string: [Microsoft][ODBC Microsoft Access Driver] Cannot start your application. The workgroup information file is missing or opened exclusively by another user. Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.
My DTS package performs the following: 1. 4 transformations to transform data from Sybase tables A,B,C,D to the temp tables in MSSQL tmpA, tmpB,tmpC,tmpD 2. Next, I have a task to run 4 stored procedures to load the 4 tmp* tables to actual tables A,B,C,D (so the task is "exec spA, exec spB, exec spC, exec spD". 3. There are 9/26/280/10000 records in the tables A,B,C,D 4. each stored procedure basically checks whether the record in the tmp* tables exist in the actual table baesd on the primary key and then perform an insert/update.
The strange thing is: 1. All 9 records in the tmpA is loaded to A. Only 17 records from tmpB is loaded to B 2. The same codes "exec spA, exec spB, exec spC, exec spD", if copied to query analyzer, they all run to completion, ie all 10000+ records are loaded. so there is no pbm with the data. 3. If I "split" the task such that task1 loads A and task2 loads B,C,D (and task2 runs after task1) now again all data for A is loaded, but 25 records from B is loaded 4. I tried to catch the @@error in the stored procedure for the insert/update statements but there's no error. Most importantly, the stored procedures run fine in query analyzer
Is there some sort of timeout or buffer issue here that is causing this strange behaviour.
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
Hi all, I want to learn about sp with examples,so can any one give me the best urls regarding this.and i want to write single sp for all like (insert,delete and update)in a single sp.Please guide me. Thank You
Hi I use the next StoredProcedure in Access, Inserts Users to tblUsers: INSERT INTO tblUsers ( UserName, Password, RetypePassword, Email, Comments ) VALUES (@UserName, @Password, @RetypePassword, @Email,@Comments);
I am New to Sql Server. Now i have to write two stored procedures.
Here are my requirements. If any one help please.
1)People often ask me to do this as well... change the name of the underwriter. For Mortgage Network underwriters (different than MGIC underwriters) all you need to do is:
Update mnetwork..unw_Nola set underwriter='<underwriters username>' where LoanID='<LoanID>'
So, I need you to write a stored procedure that will do exactly that. If you don't enter a loanID or username, the stored procedure should tell you that it can't complete the task and why. Also, the list of underwriters can be found in:
select LoginName from mnetwork..unw_LoginLookup where UnderwriterName = '<Name on the email>'
So for this one, you would select where underwriterName='John Brennan'.
For most usernames, it's just first initial last name (jbrennan in this case). You can make the stored procedure to both, if you want. If you enter an underwriter name, then it will translate to the loginname. If you enter the login name, it will just use that. You don't have to do all of that if you don't want. Just make sure the procedure verifies that the username is correct (in the table) and that is enough.
I am using SQL Server 2005 now and I have a table with following columns. ID, FirstName, LastName, Email "ID" is the primary key (int) and is set auto generated (1 increment) I have a StoredProcedure to insert a new record. CREATE PROCEDURE Candidate_Create @FName nvarchar(255), @LName nvarchar(255), @Email nvarchar(255)ASINSERT INTO Candidate (FirstName, LastName, Email)VALUES (@FName, @LName, @Email)GO I want the ID to be returned as the same time when a new record is inserted, how can I do it ? Is it possible ?
Hi,I'm wodering if it's possible (and the correct syntax) to make a JOIN between a Table and a SP's result. This is my code, but it goes in error in the EXEC:1 SET ANSI_NULLS ON 2 GO 3 SET QUOTED_IDENTIFIER ON 4 GO 5 -- ============================================= 6 -- Author:Luca de Angelis 7 -- Create date: 22/08/2007 8 -- Description:Inserimento dei dati contabili nella tabella di log 9 -- ============================================= 10 CREATE PROCEDURE dbo.InsertIntoLog_dati_contabili 11 -- Add the parameters for the stored procedure here 12 @id_gestore tinyint 13 AS 14 SET NOCOUNT ON 15 BEGIN TRANSACTION 16 INSERT INTO CEL_log_dati_contabili(numero_telefonico, anno, mese, id_gestore, id_tipo_log) 17 SELECT CEL_traffico_temp.numero_telefonico 18 , CEL_traffico_temp.anno 19 , CEL_traffico_temp.mese 20 , @id_gestore as id_gestore 21 , 1 22 FROM CEL_traffico_temp 23 INNER JOIN 24 (EXEC dbo.CEL_SimConGestoreNoFilePeriodo @id_gestore, CEL_traffico_temp.anno + CEL_traffico_temp.mese) AS tabella 25 ON CEL_traffico_temp.numero_telefonico = tabella.numero_telefonico 26 27 INSERT INTO CEL_log_dati_contabili(numero_telefonico, anno, mese, id_gestore, id_tipo_log) 28 SELECT CEL_traffico_temp.numero_telefonico 29 , CEL_traffico_temp.anno 30 , CEL_traffico_temp.mese 31 , @id_gestore as id_gestore 32 , 2 33 FROM CEL_traffico_temp 34 INNER JOIN 35 (EXEC CEL_SimNelFileNoGestore @id_gestore) AS tabella 36 ON CEL_traffico_temp.numero_telefonico = tabella.numero_telefonico 37 38 INSERT INTO CEL_log_dati_contabili(numero_telefonico, anno, mese, id_gestore, id_tipo_log) 39 SELECT CEL_traffico_temp.numero_telefonico 40 , CEL_traffico_temp.anno 41 , CEL_traffico_temp.mese 42 , @id_gestore as id_gestore 43 , 3 44 FROM CEL_traffico_temp 45 INNER JOIN 46 EXEC CEL_SimNelFileNoUtente @id_gestore AS tabella 47 ON CEL_traffico_temp.numero_telefonico = tabella.numero_telefonico 48 49 IF @@error <> 0 50 BEGIN 51 ROLLBACK TRANSACTION 52 END 53 ELSE 54 BEGIN 55 COMMIT TRANSACTION 56 END Help me please...
Visual Studio 2008 Code VB I'm trying to create a stored procedure that will update a database table. I want to make sure that duplicate records are not inserted into the Database Table, so I used IF NOT EXISTS . With the below code I can update the table, however, you can not add additional rows to the table. Could someone tell me what is wrong, or how to fix it?
Thanks! losssoc ALTER PROCEDURE dbo.CaseDataInsert
AS BEGIN IF NOT EXISTS(SELECT ReportType,CreatedBy,OpenDate,Territory,Region,StoreNumber,StoreAddress,TiplineID,Status,CaseType,Offense FROM CaseData)INSERT CaseData(ReportType, CreatedBy,OpenDate,Territory,Region,StoreNumber,StoreAddress,TiplineID, Status,CaseType,Offense)VALUES(@ReportType,@CreatedBy,@OpenDate,@Territory,@Region,@StoreNumber,@StoreAddress,@TiplineID, @Status,@CaseType,@Offense)
To all, I looked at the MS-SQL pubs sample database and execute the example stored procedure reptq2 and I got 17 results set back. Where can I find an example using Visual Studio DataGrid or any means to get all these results from this SP.
AS BEGIN IF NOT EXISTS (SELECT * FROM CustomerDetails WHERE Name = @Name AND Number = @Number) BEGIN UPDATE CustomerDetails SET Number = @Number,Name = @Name,City=@City,SSN = @SSN where CustomerID = @CustomerID END ELSE BEGIN print 'CANNOT UPDATE' END END
This my storedproc. My problem is when i select customerID = 1 to update and if the same row having name = @name and number =@number Then the update should take place.
but if any other row other than CustomerID=1 having name=@name and number=@number Then the update is should not take place.
but The above stored procedure is not working like that.
so please some one help me with this. Thankyou Ramya.
Hi everyone!I tried to set my SqlDataSource's SelectCommandType to be a stored procedure. However the SqlDataSource failed to cache it. But if I just copy paste my stored procedure's content to my SqlDataSource's SelectCommand property, the cache just works. Is "StoredProcedure" as the "SelectCommandType" is not supported when caching the data? Or am I missing something here? Please help.
I am trying to write a function for some source to make a call out to and fill a RadioButtonList. I am running into a few problems though that I need assistance on. (I am new to DataSets) Here is the function to fill the RBL: 1 Private Function GetDataSet(ByVal QuestionID As Integer, ByVal QuestionType As Integer, ByVal LocaleID As Integer, ByVal GroupingNum As Integer) As DataSet 2 Dim cnn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString) 3 Dim cmd As New SqlCommand 4 cmd.CommandText = "usp_responses_sel" 5 cmd.CommandType = Data.CommandType.StoredProcedure 6 7 ' Fill usp_ with Parameters 8 cmd.Parameters.AddWithValue("QuestionID", QuestionID) 9 cmd.Parameters.AddWithValue("LocaleID", LocaleID) 10 cmd.Parameters.AddWithValue("GroupingNum", GroupingNum) 11 12 Dim da As New SqlDataAdapter 13 da.SelectCommand = cmd 14 Dim ds As New DataSet 15 da.Fill(ds, "response") 16 Return ds 17 End Function
So my issue is with line 15 [da.Fill(ds, "response")]. I pulled this function from somewhere else and am trying to tailor it to my needs. However, I do not understand what I need to do with this line and it keeps bombing out. I thought this references the DB Table but in my case, the SP has several tables joined together. Is this how I reference it from the calling source code? Please assist. Also, I am having problems understanding the binding process from the calling source. Here is my code that calls the function:1 Dim ds As DataSet = GetDataSet(CType(e.Item.DataItem("question_id").ToString, Integer), QuestionTypeID.Value, intLocale, 2) 2 rblResponses2.DataSource = ds 3 rblResponses2.DataBind()
What do I need to do with it from here and how can I work with it after it's bound? Thanks
hi i have a stored procedure witch Returns 0 or 1 dependig if exists some rows how can i acces that value in code behind? i tryied all, command.ExecuteScalar(), command.ExecuteNonQuery() but none works i need something like if(command.GetReurnValue) .... else .... thanks in advance
Can I know the best method to connect the sql server 2008 with Visual studio 2008, it will be helpful if I know how to access the stored procedures in the sqlserver via LINQ.
I need to execute stored procedure which is suppose to return GUID to my IF statement and if it is Nothing I execute other Stored procedures else some other procedures. My problem is that even though by looking at the data I know that after the execution of the procedure it should return some guid value it doesn't anybody who had the same issue??? That is the code block where I am trying to return guid from my stored procedure: getGroupID.Parameters("@GroupName").Value = dr.Item("Group ID").ToString() If getGroupID.ExecuteScalar() = Nothing Then 'Find Group by IP address if input Data Table doesn't have group getGroupIDByIP.Parameters("@IP").Value = dr.Item("IP").ToString() If getGroupIDByIP.ExecuteScalar() = Nothing Then insertGroup.Parameters("@GroupID").Value = Guid.NewGuid insertGroup.Parameters("@Group").Value = dr.Item("Group ID") insertGroup.Parameters("@ACCID").Value = getAccID.ExecuteScalar() insertGroup.ExecuteNonQuery() command.Parameters("@Group_ID").Value = getGroupID.ExecuteScalar() Else command.Parameters("@Group_ID").Value = getGroupIDByIP.ExecuteScalar() End If Else command.Parameters("@Group_ID").Value = getGroupID.ExecuteScalar() End If Thank you
Hello everyone,I am developing forums (Discussion Board) in C#.net 2005 with SqlServer. Right now i am having problem in fetching data from two tables.Here are the tables from which i want to fetch data.Topics Table Threads TableTopicID ThreadIDForumID TopicIDTopicName SubjectTopicDescription Replies UserID LastPostDatenow i want to fetch all the topic talbe data as well as total no of threads,Lastpostdate,UserID per topic.I am able to fetch topic table data and Total no of threads per topic through the following query.SELECT TopicID, ForumID, TopicName, TopicDescription,(SELECT COUNT(ThreadID) FROM Portal_Threads WHERE (TopicID = Portal_Topics.TopicID)) AS Threads FROM Portal_Topicsbut i am not able to fetch anoter two detail with subquery as i am getting error likeonly one expression can be specified in the select listorsubquery returned more than one value.can anyone tell me how can i fetch these two values per topic. should i use stored procedure and create temporary table and after fetching these values i can store it in temporary table and i can fetch values from that temporary table...please provide code snippet if possible as i've never used sqlserver before..Thanks in advance...Regards,Nil
I have a DTS package (AdIns) that inserts to an administrative table. The Administrative table utilizes the "with ignore_dup_key" option on the index. There are other admin jobs in the DTS that are based on the return code of a parent package.
The "3604:duplicate key ignored" is an expected result of the parent package, yet it sends an failure return code to the dependent (AdIns) package, causing erroneous entries to the final audit table.
How can I reset the return code from the parent package?
I need to create a StoredProcedure to calculate a field auto-inc. Who helps me? I tried many code, but i didn't have sucess.
My sample:
CREATE PROCEDURE SP_CT_ITEM AS
DECLARE @CONTADOR NUMERIC(008)
SET NOCOUNT ON
BEGIN TRAN
SELECT @CONTADOR = CAST(NM1_PARAMETRO AS NUMERIC(008)) FROM PARAMETRO (UPDLOCK) WHERE SIG_PARAMETRO = 'CT' AND GRU_PARAMETRO = '001' AND COD_PARAMETRO = 'ITEM'
UPDATE PARAMETRO SET NM1_PARAMETRO = @CONTADOR + 1 WHERE SIG_PARAMETRO = 'CT' AND GRU_PARAMETRO = '001' AND COD_PARAMETRO = 'ITEM'
I have to write a stored procedure.I have a search page having four fields.Giving any of the field should fetch the whole record and display in the gridview. My trouble starts here I have a button field in gridview1 . when i click on the button there should be another gridview which displays refunds of particular customer i.e from another table.There is only one common colum in the two tables. based on that colum value we have to fetch from second table. now my question is : how to capture the colum value of first select statement and give it as input to second select staement.
my code is here :
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go
ALTER PROCEDURE [dbo].[search] (@val1 varchar(225), @val2 varchar(50), @val3 varchar(50), @val4 varchar(50)) AS BEGIN Select*From customer where ((@val1 IS NULL) or (CNo = @val1)) AND ((@val2 IS NULL) or(LastName = @val2)) AND ((@val3 IS NULL) or(FirstName = @val3)) AND ((@val4 IS NULL) or(PhoneNumber = @val4)) Select * From refunds where CNo = @val1 END
here if i fill CNo text box in my search page its giving the value but all the time user may not give CNo.He may search customers based firstname ,lastname etc. so what should i do to capture CNo from first select statement and give it as input to second select statements
In a DataFlowTask with several OLE DB Destinations, how can I "last", before ending this DFT execute a storedProcedure?
This storedprocedure is used for saving metadata (taskname, rowcounts etc) regarding this DFT and I dont want to add an ExecuteSQLTask after the DFT in the Control Flow
I am developing ASP.NET 2.0 website. I need to know some about using stored procedure. I searched through google. But could now find a favourable repLy. Here is ..
Which way is efficient, using SQL inside the code or as SRORED PROCEDRE, which one to use with ASP.NET? Is the Stored procedure must be created withing the server or from my application?Can anyone please give some practicle explaination about this? My advance thanks for all...
after moving off VS debugger and into management studio to exercise our SQLCLR sp, we notice that the 2nd execution gets an error suggesting that our static SqlCommand object is getting reused from the 1st execution (of the sp under mgt studio). If this is expected behavior, we have no problem limiting our statics to only completely reusable objects but would first like to know if this is expected? Is the fact that debugger doesnt show this behavior also expected?
