Make Stored Proc Faster

Jun 17, 2004

Anyone got any alternatives to the SQL Statement below that would make it run a bit faster:

ALTER PROCEDURE sproc_ReturnAvailability

@ExtractDate DateTime,
@DateFrom DateTime,
@DateTo DateTime,
@96hrPlusFlag int,
@AppointmentsCount int OUTPUT

AS

IF @96hrPlusFlag = 0

BEGIN
SELECT @AppointmentsCount = COUNT(tbl_SurgerySlot.SurgerySlotKey)
FROM tbl_SurgerySlot
INNER JOIN tbl_SurgerySlotDescription ON (tbl_SurgerySlot.Label = tbl_SurgerySlotDescription.Label AND tbl_SurgerySlot.PracticeCode = tbl_SurgerySlotDescription.PracticeCode)
AND tbl_SurgerySlot.ExtractDate = @ExtractDate
AND tbl_SurgerySlot.StartTime BETWEEN @DateFrom AND @DateTo
AND tbl_SurgerySlotDescription.NormalBookable = 1
AND tbl_SurgerySlot.SurgerySlotKey NOT IN(
SELECT tbl_Appointment.SurgerySlotKey
FROM tbl_Appointment
WHERE tbl_Appointment.ExtractDate = @ExtractDate
AND tbl_Appointment.Deleted = 0
AND tbl_Appointment.Cancelled = 0
)
END
ELSE

BEGIN
IF @96hrPlusFlag = 1

SELECT @AppointmentsCount = COUNT(tbl_SurgerySlot.SurgerySlotKey)
FROM tbl_SurgerySlot
INNER JOIN tbl_SurgerySlotDescription ON (tbl_SurgerySlot.Label = tbl_SurgerySlotDescription.Label AND tbl_SurgerySlot.PracticeCode = tbl_SurgerySlotDescription.PracticeCode)
AND tbl_SurgerySlot.ExtractDate = @ExtractDate
AND tbl_SurgerySlot.StartTime >@DateTo
AND tbl_SurgerySlotDescription.NormalBookable = 1
AND tbl_SurgerySlot.SurgerySlotKey NOT IN(
SELECT tbl_Appointment.SurgerySlotKey
FROM tbl_Appointment
WHERE tbl_Appointment.ExtractDate = @ExtractDate
AND tbl_Appointment.Deleted = 0
AND tbl_Appointment.Cancelled = 0
)

END

Cheers...

View 1 Replies


ADVERTISEMENT

AdHoc Query Faster Than Stored Proc?

Aug 28, 2004

Yesterday i face a strange SQL Server 2000 behaviour :-(

I had a query that was wrapped inside a stored procedure, as usual.
Suddenly, the stored procedure execution time raised from 9 secs to 80.

So to understand where the problem was i cut and pasted the sp body's into a new query analyzer window an then executed it again. Speed back to 9 secs.
Tried stored procedure again, and speed again set to 80 secs.

Tried to recompile sp. Nothing. Tried to restart SQL Server. Nothing. Tried to DROP & RE-CREATE sp. Done! Speed again at 9 secs.

My collegue asked me "why?", but i had no words. :confused: Do you have any explanation?

View 5 Replies View Related

Same Statement Executes 10 Times Faster As Raw Sql In Query Analyzer Then In A Stored Proc

Aug 15, 2007



Hi,


I apologize for the long post but I am trying to give as much information as I can about the steps I've taken to troubleshoot this.


We have a stored procedure that builds a sql statement and executes it using the Execute command. When I execute the stored procedure through query analyzer it takes close to 5 seconds to execute. When I print out the exact same statement and execute it directly in query analyzer as "raw sql", it takes 0.5 seconds - meaning it takes 10 times longer for the code to execute in the stored proc. I altered the stored proc to execute the printed sql instead of building but it still takes the full 5 seconds and there were no changes in the execution plan. This makes me confident that the issue is not caused by the dynamic sql. I've used with recompile to make sure that the stored procedure caches the most recent execution plan. When I compare the execution plans, the stored proc uses a nested loop whereas the raw sql statement uses a hash join. Seeing that, I added the hash hint to the stored proc and doing so brought down the execution time down from 5 secs to 2 secs but still the raw sql statement uses a clustered index whereas the stored proc uses a non-clustered index and that makes the statement 4 times slower. This proves how efficient clustered indexes are over non-clustered ones, but it doesn't help me since, as far as I know, I can't force SQL Server to use the clustered index.


Does anyone know why sql server is generating such an inefficient execution plan for the stored proc compared to the execution plan that it generates when executing the raw sql statement? The only thing I can think of is that some stats are not updated and that somehow throws off the stored proc. But then again, shouldn't it affect the raw sql statement?


Thank you,


Michael Tzoanos

View 4 Replies View Related

How To Make Stored Proc Changes

May 18, 2007

Hi,
Just moved to SQL2005 from SQL 2000. Thought I would drop some bad habits along the move. With SQL 2000, when I had stored proc changes to take to the client, I would open EnterpriseManager on my laptop while plugged into their network. I would open their database on their network, and the database on my laptop that had their changes, and cut and post the changes. Really want to stop that.

Now, with SQL2005, I see it wants me to modify the SP, then run it as a script.

What is the best way for me to take stored proc changes from my SQL2005 server in the office, and get them to the clients SQL2005 server?

Thanks

View 5 Replies View Related

Make And Save A Blob On The Fly In Stored Proc

Jul 7, 2006

Is there a way to run query, make file out of query (txt,csv or xls -for example) and save it as a blob into the table column typeImage/Text?Any thoughts would be greatly appreciated.Thanks!

View 5 Replies View Related

How Can I Make It Faster?

Apr 17, 2008

I recently installed SQL2005 Express on a Dell Precision workstation to be accessed by 6 users. The PC has XP Pro, 2GB memory, and 2.0GHz core2due CPU. This PC is dedicated for the database only.
I also set XP Performance options in System Properties to Background services and System cache.
Now, the users are experiencing slow response from the database.
How can I make the database response faster?
Thanks for your input.

View 4 Replies View Related

How To Make Insert Faster

Jan 26, 2001

Hi.We have stored procedure update specific table
Each time it run it delete 5000- 6000 rows
from table then insert 5000- 6000 rows with different information.
It take up to 1 1/2 min execute.

1.Can force Sql server do not make entry for each insert and if yes would it increase speed of procedure ?
2. Is any other way increase speed of insert?

View 2 Replies View Related

Help Make Query Run Better (faster)

Mar 20, 2007

I need help makeing the following query run more efficently.


Code:


SELECT t1.ID,t1.firstName,t1.lastName,t1.address,t1.city,t1.state, t1.zip,t1.locationAddress,t1.locationCity,t1.locationState,t1.locationZip
FROM Landlord_tbl t1
left outer join Mail_tbl t2
ON t2.potentialSitesID = t1.potentialSitesID
WHERE t2.mailed_out_date is null and NOT(t1.firstName+t1.lastName) is Null
GROUP BY t1.ID,t1.firstName,t1.lastName,t1.address,t1.city,t1.state, t1.zip,t1.locationAddress,t1.locationCity,t1.locationState,t1.locationZip
ORDER BY t1.firstName, t1.lastName, t1.city, t1.state

View 3 Replies View Related

Can I Make Subreport Run Faster?

Nov 29, 2007



Hi:

I have thousands of rows that I have to retrieve for my report. When I run my main report it returns my data as a reasonable time.

When I run my sub report (by its own) again data are returned in reasonable time.

When I build the sub report in my main report it takes forever (I have to stop). Are there any tricks that I can use to make this run faster?





Thank you in advance

Bahram

View 3 Replies View Related

Would Split Make It Faster ??

Aug 5, 2007

Hi,
Here is the typical processing I do in Data Flow
1) read data file (data source)
2) do something with the data (e.g. derived column, script component, etc)
3) pass data as parameters into OLE DB command (stored proc) to take care of the rest.

Would it be faster if
I split the data into say 10 "chunks" (using split transform). Each chunk will go to its own OLE DB command (10 OLE DB command). Now I will have 10 concurrent processes running the stored proc ?

Am i misunderstanding concurrent process here ?

Thanks

View 1 Replies View Related

Any Clue To Make This Query Run Faster

Oct 3, 2006

Any help would be really appreciated....
My stored procedure...




CREATE PROCEDURE business3rd7
@Fromdate DATETIME,
@ToDate DATETIME
AS



select distinct CONVERT(VARCHAR(10),Receipts.Companynumber1)+CONVE RT(VARCHAR(10),Receipts.Companynumber2) as co ,
Receipts.Premium1+Receipts.Premium2 as Premium,
"CAN"=case when Receipts.transactiontype='CAN'
then (receipts.premium1+receipts.premium2)
else 0
end,
"NET"=Receipts.Premium1+Receipts.Premium2-case when Receipts.transactiontype='CAN'
then (receipts.premium1+receipts.premium2)
else 0
end,

"#NEW"=case when Receipts.transactiontype='NEW' then count(Receipts.policynumber) else
0 end,

-------
"$NEW"=
case when Receipts.transactiontype='NEW' then (Receipts.Premium1+Receipts.premium2)
else 0 end,
"#REN"=case when Receipts.transactiontype='REN' then count(Receipts.policynumber) else
0 end,
"$RENEW"=
case when Receipts.transactiontype='REN' then (Receipts.Premium1+Receipts.premium2)
else 0 end,
"#AP"=case when Receipts.transactiontype='AP' then count(Receipts.policynumber) else 0
end,
"$AP"=
case when Receipts.transactiontype='AP' then (Receipts.Premium1+Receipts.premium2) else
0 end,
"#SENT"=case when policy.Renewalofferdate between @Fromdate AND @ToDate then
count(policy.policynumber) end,

"%"=case when case when Receipts.transactiontype='REN' then count(Receipts.policynumber)else 0
end =0 then 0
when case when policy.Renewalofferdate between @Fromdate AND @ToDate then
count(policy.policynumber) else 0 end=0 then 0
else
case when Receipts.transactiontype='REN' then count(Receipts.policynumber)else 0
end /case when policy.Renewalofferdate between @Fromdate AND @ToDate then
count(policy.policynumber) else 0 end end,


"Current Year"= case when policy.Renewalofferdate between @Fromdate AND @ToDate then
count(clubamount) end,
"Previous Year"=case when policy.Renewalofferdate between DateAdd(year, -1, @Fromdate)
AND DateAdd(year, -1, @ToDate) then count(clubamount) end,
"#AA"=count(receipts.clubamount),
"$AA"=sum(receipts.clubamount)

FROM Receipts,Policy
where Receipts.Agencyid=Policy.Agentid
group by
Receipts.CompanyNumber1,Receipts.CompanyNumber2,
Receipts.Premium1,Receipts.Premium2,
Receipts.TransactionType,policy.Renewalofferdate,
Receipts.Agencyid



GO


Query plan...

----------------------Query Plan

|--Sort(DISTINCT ORDER BY:([Expr1008] ASC, [Expr1009] ASC, [Expr1010] ASC, [Expr1011] ASC, [Expr1012] ASC, [Expr1013] ASC, [Expr1014] ASC, [Expr1015] ASC, [Expr1016] ASC, [Expr1017] ASC, [Expr1018] ASC, [Expr1019] ASC, [Expr1020] ASC, [Expr1021] ASC, [Ex
|--Compute Scalar(DEFINE:([Expr1008]=Convert([Receipts].[CompanyNumber1])+Convert([Receipts].[CompanyNumber2]), [Expr1009]=[Receipts].[Premium1]+[Receipts].[Premium2], [Expr1010]=If ([Receipts].[TransactionType]='CAN') then ([Receipts].[Premium1]+[R
|--Compute Scalar(DEFINE:([Expr1004]=Convert([Expr1076]), [Expr1005]=Convert([Expr1077]), [Expr1006]=Convert([Expr1078]), [Expr1007]=If ([Expr1078]=0) then NULL else [Expr1079]))
|--Stream Aggregate(GROUP BY:([Receipts].[CompanyNumber1], [Receipts].[CompanyNumber2], [Receipts].[Premium1], [Receipts].[Premium2], [Receipts].[TransactionType], [Policy].[RenewalOfferDate], [Receipts].[AgencyID]) DEFINE:([Expr1076]=COUN
|--Sort(ORDER BY:([Receipts].[CompanyNumber1] ASC, [Receipts].[CompanyNumber2] ASC, [Receipts].[Premium1] ASC, [Receipts].[Premium2] ASC, [Receipts].[TransactionType] ASC, [Policy].[RenewalOfferDate] ASC, [Receipts].[AgencyID] ASC))
|--Hash Match(Inner Join, HASH:([Policy].[AgentID])=([Receipts].[AgencyID]), RESIDUAL:([Policy].[AgentID]=[Receipts].[AgencyID]))
|--Table Scan(OBJECT:([gasInquiry].[dbo].[Policy]))
|--Table Scan(OBJECT:([gasInquiry].[dbo].[Receipts]))



The two tables has number of records as 13349 and 97032.It taking more than 30 mins...
Any way to make it faster...

View 14 Replies View Related

How To Make You Reports Load Faster ?

Sep 3, 2007

Any ideas how to make reports faster when returning lots fo rows?
I know you would need to work on your sql query etc..
Or maybe cache it.
But i'm thinking of having a kind of middle tier thing that sits between your sql database and the reports itself.
Any ideas would be appreciated

View 2 Replies View Related

Indexing SL CE To Make Search Faster. How?

Jan 11, 2006

Dear VB coders!

I have 8.000 or 16.000 products on my list in an sql table. It takes 8 or 16 secs to find the last item.

How could I make the search faster? Indexing? But how?

I created:
createTable1.CommandText = "Create TABLE products (ID int IDENTITY(0,1) PRIMARY KEY, name ntext)"
 
and I use simple WHERE to search.
 
Thank you!

View 3 Replies View Related

How Do I Make 30 Sec Running Query (select C1 Sum(x) From T1 Where C1 &&> 1000 Group By C1) Run Faster?

Aug 10, 2007

It seems when I run the query with the set staticts IO on then statistic reports back with the 'work table', and the query takes 30+ sec. if the worktable is ommited(whatever the reason?) the query take less 1 sec.


Here is my take, I believe work table is created in tempdb...and if not then whole query is using the cached page, am I right?

if I am right then the theory is, if I increase the (via sp_configure) server min memory setting and min query memory, the query ought use the cached page and return in less 1 sec. (specially there is absolutely no one but me on the server), so far I can't make it go faster...what setting am I missing to make it run faster?


Another question is if the query can not avoid but use the tempdb, is it going to always be 30 sec+ time? why is tempdb involvement make it go so much slower?


Thanks in for you help in advance

View 1 Replies View Related

Can You Trace Into A Stored Proc? Also Does RAISERROR Terminate The Stored Proc Execution.

Feb 13, 2008

I am working with a large application and am trying to track down a bug. I believe an error that occurs in the stored procedure isbubbling back up to the application and is causing the application not to run. Don't ask why, but we do not have some of the sourcecode that was used to build the application, so I am not able to trace into the code.
So basically I want to examine the stored procedure. If I run the stored procedure through Query Analyzer, I get the following error message:
Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74RAISERROR could not locate entry for error 60002 in sysmessages.
(1 row(s) affected)
(1 row(s) affected)
I don't know if the error message is sufficient enough to cause the application from not running? Does anyone know? If the RAISERROR occursmdiway through the stored procedure, does the stored procedure terminate execution?
Also, Is there a way to trace into a stored procedure through Query Analyzer?
-------------------------------------------As a side note, below is a small portion of my stored proc where the error is being raised:
SELECT  @PortalPermissionValue = isnull(max(PermissionValue),0)FROM Permission, PermissionType, #GroupsWHERE Permission.ResourceId = @PortalIdAND  Permission.PartyId = #Groups.PartyIdAND Permission.PermissionTypeId = PermissionType.PermissionTypeId
IF @PortalPermissionValue = 0BEGIN RAISERROR (60002, 16, 1) return -3END 
 

View 3 Replies View Related

Stored Proc - Calling A Remote Stored Proc

Aug 24, 2006

I am having trouble executing a stored procedure on a remote server. On my
local server, I have a linked server setup as follows:
Server1.abcd.myserver.comSQLServer2005,1563

This works fine on my local server:

Select * From [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.dbo.TableName

This does not work (Attempting to execute a remote stored proc named 'Data_Add':

Exec [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.Data_Add 1,'Hello Moto'

When I attempt to run the above, I get the following error:
Could not locate entry in sysdatabases for database 'Server1.abcd.myserver.comSQLServer2005,1563'.
No entry found with that name. Make sure that the name is entered correctly.

Could anyone shed some light on what I need to do to get this to work?

Thanks - Amos.

View 3 Replies View Related

Stored Proc Question : Why If Exisits...Drop...Create Proc?

Jun 15, 2006

Hi All,Quick question, I have always heard it best practice to check for exist, ifso, drop, then create the proc. I just wanted to know why that's a bestpractice. I am trying to put that theory in place at my work, but they areasking for a good reason to do this before actually implementing. All Icould think of was that so when you're creating a proc you won't get anerror if the procedure already exists, but doesn't it also have to do withCompilation and perhaps Execution. Does anyone have a good argument fordoing stored procs this way? All feedback is appreciated.TIA,~CK

View 3 Replies View Related

ASP Cannot Run Stored Proc Until The Web User Has Run The Proc In Query Analyzer

Feb 23, 2007

I have an ASP that has been working fine for several months, but itsuddenly broke. I wonder if windows update has installed some securitypatch that is causing it.The problem is that I am calling a stored procedure via an ASP(classic, not .NET) , but nothing happens. The procedure doesn't work,and I don't get any error messages.I've tried dropping and re-creating the user and permissions, to noavail. If it was a permissions problem, there would be an errormessage. I trace the calls in Profiler, and it has no complaints. Thedatabase is getting the stored proc call.I finally got it to work again, but this is not a viable solution forour production environment:1. response.write the SQL call to the stored procedure from the ASPand copy the text to the clipboard.2. log in to QueryAnalyzer using the same user as used by the ASP.3. paste and run the SQL call to the stored proc in query analyzer.After I have done this, it not only works in Query Analyzer, but thenthe ASP works too. It continues to work, even after I reboot themachine. This is truly bizzare and has us stumped. My hunch is thatwindows update installed something that has created this issue, but Ihave not been able to track it down.

View 1 Replies View Related

Sp_executeSQL Perform Faster Than Stored Procedure, Why?

Dec 24, 2007



Hi,

I Have a stored procedure named pPatientFindByID that return one patient record from the Client.Patient table. The stored procedure has one parameter @patientID uniqueidentifier. i have set the NOCOUNT ON before running the SQL statement and making it OFF after it finishes execution. The table has a primary key ( clustered ) on patientID. The table has 66840 records in it. The index IS 0 (ZERO) percent fragmented and 3 level depth.

The stored proc returnes null or 1 record maximum. My problem is that the stored procedure takes about 2268 microsecond (2 millisecond) on average. while i execute the same SQL using sp_executeSQL it takes about 710 microsecond (< 1 millisecond). That is 3 time faster than stored procedure.

The SET OPTION are same for both the statement.

To troubleshoot the problem i have enabled the trace and captured the events. there were no recompile for both the stored proc and sp_executeSQL and not even the statement level recompilation.

I am trying to figure out why my stored procedure is taking longer than sp_executesql. Both the (stored proc and sp_executeSQL) has the same execution plan.


This is affecting our application performance. Does anyone know about this or have experience the same problem ever? your help would be greatly appriciated.

Data


1. Stored Procedure


/****** Object: StoredProcedure [dbo].[pPatientFindByID] Script Date: 12/24/2007 11:19:58 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[pPatientFindByID]

@patientID uniqueidentifier

WITH EXECUTE AS OWNER

AS

BEGIN

SET NOCOUNT ON



SELECT

[patient].[AVINumber] AS 'AVINumber',

[patient].[BirthDate] AS 'BirthDate',

[patient].[BreedID] AS 'BreedID',

[patient].[ChangeDate] AS 'ChangeDate',

[patient].[ChangeWorkstationID] AS 'ChangeWorkstationID',

[patient].[ClientID] AS 'ClientID',

[patient].[Color] AS 'Color',

[patient].[County] AS 'County',

[patient].[CountyRabiesTagNumber] AS 'CountyRabiesTagNumber',

[patient].[CreateDate] AS 'CreateDate',

[patient].[CreateWorkstationID] AS 'CreateWorkstationID',

[patient].[DeathDate] AS 'DeathDate',

[patient].[DeathReasonCode] AS 'DeathReasonCode',

[patient].[FirstVisitDate] AS 'FirstVisitDate',

[patient].[HasAllergy] AS 'HasAllergy',

[patient].[HasBitesOrScratch] AS 'HasBitesOrScratch',

[patient].[HasChronicCondition] AS 'HasChronicCondition',

[patient].[HasDeclinedPreventiveCare] AS 'HasDeclinedPreventiveCare',

[patient].[HasMedicalAllergy] AS 'HasMedicalAllergy',

[patient].[HospitalID] AS 'HospitalID',

[patient].[IsBites] AS 'IsBites',

[patient].[IsBitesInFear] AS 'IsBitesInFear',

[patient].[IsClinicalStudy] AS 'IsClinicalStudy',

[patient].[IsLitters] AS 'IsLitters',

[patient].[IsMixBreed] AS 'IsMixBreed',

[patient].[IsScratches] AS 'IsScratches',

[patient].[LastVisitDate] AS 'LastVisitDate',

[patient].[ManufacturerNameOfAVIChip] AS 'ManufacturerNameOfAVIChip',

[patient].[ManufacturerNameOfMicrochip] AS 'ManufacturerNameOfMicrochip',

[patient].[MicrochipNumber] AS 'MicrochipNumber',

[patient].[Name] AS 'Name',

[patient].[Note] AS 'Note',

[patient].[PatientID] AS 'PatientID',

[patient].[RabiesTagNumber] AS 'RabiesTagNumber',

[patient].[SexCode] AS 'SexCode',

[patient].[SpeciesID] AS 'SpeciesID',

[patient].[Status] AS 'Status',

[patient].[SystemLog] AS 'SystemLog',

[patient].[Weight] AS 'Weight',

[patient].[WellnessplanLevel] AS 'WellnessplanLevel',

[patient].[WellnessplanValue] AS 'WellnessplanValue'

FROM

[Client].[Patient] [patient]

WHERE

[PatientID] = @patientID

SET NOCOUNT OFF

END


2. sp_executeSQL


set nocount on

execute

sp_executeSQL N'

SELECT [patient].[AVINumber]

,[patient].[BirthDate]

,[patient].[BreedID]

, [patient].[ChangeDate]

, [patient].[ChangeWorkstationID]

, [patient].[ClientID]

, [patient].[Color]

, [patient].[County]

, [patient].[CountyRabiesTagNumber]

, [patient].[CreateDate]

, [patient].[CreateWorkstationID]

, [patient].[DeathDate]

, [patient].[DeathReasonCode]

, [patient].[FirstVisitDate]

, [patient].[HasAllergy]

, [patient].[HasBitesOrScratch]

, [patient].[HasChronicCondition]

, [patient].[HasDeclinedPreventiveCare]

, [patient].[HasMedicalAllergy]

, [patient].[HospitalID]

, [patient].[IsBites]

, [patient].[IsBitesInFear]

, [patient].[IsClinicalStudy]

, [patient].[IsLitters]

, [patient].[IsMixBreed]

, [patient].[IsScratches]

, [patient].[LastVisitDate]

, [patient].[ManufacturerNameOfAVIChip]

, [patient].[ManufacturerNameOfMicrochip]

, [patient].[MicrochipNumber]

, [patient].[Name]

, [patient].[Note]

, [patient].[PatientID]

, [patient].[RabiesTagNumber]

, [patient].[SexCode]

, [patient].[SpeciesID]

, [patient].[Status]

, [patient].[SystemLog]

, [patient].[Weight]

, [patient].[WellnessplanLevel]

, [patient].[WellnessplanValue]

FROM [Client].[Patient] [patient] WHERE [PatientID] = @patientID

',N'@patientID uniqueidentifier'

,'B1ABC1EA-D4AB-DB11-921E-00087434402F'





set nocount off

3- Trace Data



Name Event TextData Duration
SQL:BatchCompleted 12 exec dbo.pPatientFindByID @patientID='B1ABC1EA-D4AB-DB11-921E- 00087434402F' 2268
SQL:BatchCompleted 12 set nocount on execute sp_executeSQL N' SELECT [patient].[AVINumber] 710





DBA

View 8 Replies View Related

Why Does A Query Runs Considerably Faster Than Stored Procedure?

Dec 29, 2003

I have an stored procedure that takes 23 seconds to execute. However, the same query is executed in 4 seconds.

All the tests where made using the Query Analizer.

What could be wrong?

Thanks a lot in advance.

View 13 Replies View Related

Urgent: Whichj One Is Faster : SSIS Or Stored Procedure

Dec 17, 2007



Hi
My PM and me are on a discussion. He wants SSIS to simply upload the source excel files in staging and do rest of the ETL in SQl server stored procedure which I feel is primitive.
I have suggested him to use SSIS for whole ETL and not Stored procedure( method which indistry has discarded)
He sayd Stored procedure are more efficient than SSIS.

Please guide with some facts.

View 12 Replies View Related

Calling A Stored Proc From Within Another Stored Proc

Feb 20, 2003

I have seen this done by viewing code done by a SQL expert and would like to learn this myself. Does anyone have any examples that might help.

I guess I should state my question to the forum !

Is there a way to call a stored proc from within another stored proc?

Thanks In Advance.

Tony

View 1 Replies View Related

Stored Proc Calls Another Stored Proc

Jan 13, 2006

Hi all,

I have a stored procedure "uspX" that calls another stored procedure "uspY" and I need to retrieve the return value from uspY and use it within uspX. Does anyone know the syntax for this?

Thanks for your help!
Cat

View 5 Replies View Related

Calling Stored Proc B From Stored Proc A

Jan 20, 2004

Hi all

I have about 5 stored procedures that, among other things, execute exactly the same SELECT statement

Instead of copying the SELECT statement 5 times, I'd like each stored proc to call a single stored proc that executes the SELECT statement and returns the resultset to the calling stored proc

The SELECT statement in question retrieves a single row from a table containing 10 columns.

Is there a way for a stored proc to call another stored proc and gain access to the resultset of the called stored proc?

I know about stored proc return values and about output parameters, but I think I am looking for something different.

Thanks

View 14 Replies View Related

Calling T SQL Stored Proc From CLR Stored Proc

Aug 30, 2007

I would like to know if the following is possible/permissible:

myCLRstoredproc (or some C# stored proc)
{
//call some T SQL stored procedure spSQL and get the result set here to work with

INSERT INTO #tmpCLR EXECUTE spSQL
}

spSQL
(

INSERT INTO #tmpABC EXECUTE spSQL2
)


spSQL2
(
// some other t-sql stored proc
)


Can we do that? I know that doing this in SQL server would throw (nested EXECUTE not allowed). I dont want to go re-writing the spSQL in C# again, I just want to get whatever spSQL returns and then work with the result set to do row-level computations, thereby avoiding to use cursors in spSQL.

View 2 Replies View Related

Execute Stored Procedure Y Asynchronously From Stored Proc X Using SQL Server 2000

Oct 14, 2007

I am calling a stored procedure (say X) and from that stored procedure (i mean X) i want to call another stored procedure (say Y)asynchoronoulsy. Once stored procedure X is completed then i want to return execution to main program. In background, Stored procedure Y will contiue his work. Please let me know how to do that using SQL Server 2000 and ASP.NET 2.

View 3 Replies View Related

How Can I Call One Or More Stored Procedures Into Perticular One Stored Proc ?

Apr 23, 2008

Hello friends......How are you ? I want to ask you all that how can I do the following ?
I want to now that how many ways are there to do this ?



How can I call one or more stored procedures into perticular one Stored Proc ? in MS SQL Server 2000/05.

View 1 Replies View Related

Calling A Stored Procedure From Within A Stored Proc

Dec 18, 2007

Hi Peeps
I have a SP that returns xml
I have writen another stored proc in which I want to do something like this:Select FieldOne, FieldTwo, ( exec sp_that_returns_xml ( @a, @b) ), FieldThree from TableName
But it seems that I cant call the proc from within a select.
I have also tried
declare @v xml
set @v = exec sp_that_returns_xml ( @a, @b)
But this again doesn't work
I have tried changing the statements syntax i.e. brackets and no brackets etc...,
The only way Ive got it to work is to create a temp table, insert the result from the xml proc into it and then set @v as a select from the temp table -
Which to be frank is god awful way to do it.
 Any and all help appreciated.
Kal

View 3 Replies View Related

Make A Stored Procedure

Jan 7, 2007

Hi

In my table i have a datetime field
now i want to delete all records there are more than 1 hour old
can someone help me with this

Alvin

View 1 Replies View Related

Make A Call To AS/400 Stored Procedure From DTS

Feb 15, 2000

Can DTS make a call to a stored procedure on an AS/400 and accept data from that call. I need to access the AS/400 through OLE/DB for AS/400, execute the call to a stored procedure (the AS/400 stored procedure gets the data from DB2/400, executes some business logic, then presents the record set), and grab the record set returned and dump it into a SQL 7.0 table.

View 1 Replies View Related

How To Make BAT File Of Stored Procedure

Mar 29, 2013

I have a bulk insert script. I have a stored procedure. I saved the stored procedure as a .sql file in another folder too. I have another program that can "run external program" and the only files it will run is a .bat or .exe. I want my other program to be able to trigger the stored procedure to run. I think this means I need a .bat file.

Here is my stored procedure:

USE [EricaTraining]
GO
/****** Object: StoredProcedure [dbo].[LoadDailyAdjReport] Script Date: 03/29/2013 10:56:42 ******/
SET ANSI_NULLS ON
GO

[code]....

View 10 Replies View Related

Make The Stored Procedure To Run Daily

Jan 22, 2008



Hi,

I have created a stored procedure that will read the content of the text files of a particular folder. I need to make the stored procedure to run daily so that it will read the new files that is present in that folder. I have written a stored procedure to make the process of reading the file. But i need to know how to make the stored procedure to run daily so that it will automatically read all the files. I have got the information that it can be made possible using dts package. As i dont have any knowledge about dts package can anyone help me how to make this possible.

Thanks in advance for any help.

Regards,
Sangeetha

View 6 Replies View Related

Stored Procedure To Make Backup Of Database...

Dec 11, 2006

Can anyone point me to the right direction with the stored procedure on making a backup of the database. I am not looking for a scheduled backup. I'm looking for when the stored procedure get executed, the backup start right away. I believe it also require a username and password as well.

Thanks...

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved