Want To Reduce Execution Time...

Apr 2, 2008

Hello all,

Mine below function takes much time at every execution. It takes 0.18 sec to retrive 984 rows.

Can any one help me, how to reduce execution time?

"Create function [dbo].[Fn_Get_Consensus_Curve_41_Data]
(@p_Location_Code nvarchar(10), @p_Sector_Id int, @p_Match_Date DateTime ,@p_UserID int , @p_CustId int)

RETURNS @Temp_Curve_Submission_Data table
(
Location_Codenvarchar(10),
Sector_Idint,
MatchDatedatetime ,
EntityIdint ,
CustomerIdint,
MaturityDatedatetime ,
Cust_Pricefloat ,
Bid_Pricefloat ,
Offer_Pricefloat ,
Consensus_Mid_Price float ,
Tickernvarchar(20) ,
Cust_Mnemonicnvarchar(50) ,
Currency_Idint
)
as
BEGIN
/*
GO

IF (EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'Fn_Get_Consensus_Curve_41_Data')
AND ((type = 'P') OR (type = 'IF') OR (type = 'TF') OR (type = 'FN'))))
DROP FUNCTION [dbo].Fn_Get_Consensus_Curve_41_Data


GO

*/
declare @p_ENTITYID INT
declare @p_CUSTOMERID INT


Declare @p_Login_Type int
Declare @p_Result_Status int
set @p_Login_Type = (SELECT DBO.GET_USER_LOGIN_TYPE_ID(@p_UserID))

If @p_Login_Type=1 and not (@p_CustId is null or @p_CustId='')
Set @p_Result_Status = 1
Else if @p_Login_Type > 1
Set @p_Result_Status = 2
Else
Set @p_Result_Status = 0

If @p_Result_Status > 0 -- if user is valid and given enough parameters than
Begin
If @p_Result_Status = 1 -- if User is trader and gives customer id
Begin
Declare Cur_Fetch_Curve_Cust_Data cursor for
Select Distinct Customerid
From PricesRR PRR
Where
Convert(Nvarchar,Matchdate,101) = Convert(Nvarchar,@p_Match_Date,101) And
Sector_Id = @p_Sector_Id And
Location_Code = @p_Location_Code And
CustomerID = @p_CustId And
--CustomerID <> 0
--CustomerID not in (0, -1, -2, -3, -100, -200)
CustomerId Not In (Select CustomerId From Fn_Get_PricesRR_Not_To_Include_Cust_Id('V'))
and isnull(PRR.Record_Last_Action,'N') <> 'D'
and Version = dbo.GET_PRICESRR_MAX_VERSION(@p_Location_Code, @p_Sector_Id, @p_Match_Date, PRR.EntityID, @p_CustId, PRR.Date)

Declare Cur_Fetch_Curve_Entity_Data cursor for
Select Distinct EntityID
From PricesRR PRR
Where
Convert(Nvarchar,Matchdate,101) = Convert(Nvarchar,@p_Match_Date,101) And
Sector_Id = @p_Sector_Id And
Location_Code = @p_Location_Code
AND EntityId IN ( Select Distinct Entity_Id from Fn_Get_Allowed_Entity_List(@p_Location_Code , @p_Sector_Id , @p_Match_Date ,@p_UserID ))
and isnull(PRR.Record_Last_Action,'N') <> 'D'
and Version = dbo.GET_PRICESRR_MAX_VERSION(@p_Location_Code, @p_Sector_Id, @p_Match_Date, PRR.EntityID, @p_CustId, PRR.Date)

End
Else If @p_Result_Status = 2 -- if User is higher than trader.. means broker or higher
Begin
Declare Cur_Fetch_Curve_Cust_Data cursor for
Select Distinct Customerid
From PricesRR PRR
Where
Convert(Nvarchar,Matchdate,101) = Convert(Nvarchar,@p_Match_Date,101) And
Sector_Id = @p_Sector_Id And
Location_Code = @p_Location_Code And
--CustomerID <> 0
--CustomerID not in (0, -1, -2, -3, -100, -200)
CustomerId Not In (Select CustomerId From Fn_Get_PricesRR_Not_To_Include_Cust_Id('V'))
and isnull(PRR.Record_Last_Action,'N') <> 'D'
--and Version = dbo.GET_PRICESRR_MAX_VERSION(@p_Location_Code, @p_Sector_Id, @p_Match_Date, PRR.EntityID, @p_CustId, PRR.Date)

Declare Cur_Fetch_Curve_Entity_Data cursor for
Select Distinct EntityID
From PricesRR PRR
Where
Convert(Nvarchar,Matchdate,101) = Convert(Nvarchar,@p_Match_Date,101) And
Sector_Id = @p_Sector_Id And
Location_Code = @p_Location_Code
and isnull(PRR.Record_Last_Action,'N') <> 'D'
--and Version = dbo.GET_PRICESRR_MAX_VERSION(@p_Location_Code, @p_Sector_Id, @p_Match_Date, PRR.EntityID, @p_CustId, PRR.Date)

End
delete from @Temp_Curve_Submission_Data



-----------------------
-----------------------

Open Cur_Fetch_Curve_Cust_Data
fetch next from Cur_Fetch_Curve_Cust_Data
into @p_CUSTOMERID
WHILE @@FETCH_STATUS = 0
BEGIN

IF @@FETCH_STATUS <> 0 break
BEGIN
-----------------------
-----------------------
Open Cur_Fetch_Curve_Entity_Data
fetch next from Cur_Fetch_Curve_Entity_Data
into @p_ENTITYID
WHILE @@FETCH_STATUS = 0
BEGIN

IF @@FETCH_STATUS <> 0 break
-----------------------
Insert Into @Temp_Curve_Submission_Data
(
Location_Code ,
Sector_Id,
MatchDate ,
EntityId ,
CustomerId ,
MaturityDate ,
Cust_Price ,
Bid_Price,
Offer_Price,
Consensus_Mid_Price ,
Ticker ,
Cust_Mnemonic ,
Currency_Id
)

select
@p_Location_CodeLocation_Code,
@p_Sector_IdSector_Id,
X.MatchDateMatch_Date,
X.EntityIdEntity_Id ,
X.CustomerIdCustomer_Id,
X.MaturityDateMaturity_Date,
X.PriceCust_Price,
X.BidValueBid_Price,
X.OfferValueOffer_Price,
DBO.GET_Consensus_MID ('V',@p_Location_Code , @p_Sector_Id , @p_Match_Date, @p_ENTITYID ,x.MaturityDate) Consensus_Mid_Price,
--DBO.GET_Consensus_MID ('B1',@p_Location_Code , @p_Sector_Id , @p_Match_Date, @p_ENTITYID ,x.MaturityDate) Consensus_Mid_Price,
X.TickerTicker ,
X.MnemonicCust_Mnemonic,
X.Currency_Id
from
(
SELECT
row_number() over (order by maturitydate) Line_No,
a.* ,
b.ticker,
c.mnemonic
from Fn_Get_Tot_Curve_41_Date(@p_Location_Code, @p_Sector_Id, @p_Match_Date, @p_ENTITYID , @p_CUSTOMERID ,@p_UserID ) a,
referenceentity b,
(
select customerid, mnemonic
from customersrr
group by customerid,mnemonic
) c
where
a.customerid = c.customerid and
a.EntityID=b.CMAID
--order by maturitydate
) X

-----------------------
Fetch Next From Cur_Fetch_Curve_Entity_Data
Into @p_ENTITYID

END
CLOSE Cur_Fetch_Curve_Entity_Data

END
-----------------------
-----------------------

Fetch Next From Cur_Fetch_Curve_Cust_Data
Into @p_CUSTOMERID

END
deallocate Cur_Fetch_Curve_Entity_Data
CLOSE Cur_Fetch_Curve_Cust_Data
deallocate Cur_Fetch_Curve_Cust_Data

End

return
end
"

Prashant Hirani

View 1 Replies


ADVERTISEMENT

T-SQL (SS2K8) :: How To Reduce Execution Time Of Median Calculations

Apr 7, 2015

I wrote a procedure to calculate median:

============================
ALTER proc [dbo].[sp_CalculateMedianTimeInDepartmentMinutes]

@StartDate date
,@EndDate date
as
--== Check if count is even or odd
declare @modulo int
select @modulo = (Select COUNT(*)%2 from ED_data where AdmitDateTime between @StartDate and @EndDate )
--=== Get Median

[Code] ....

My fellow developer is using this code to calcuate a madians in many columns (see below). The problem is that it takes about 2 minutes to execute this code. Is there a way to reduce the time of execution?

I attach also a sample of the view

==============
ALTER PROCEDURE [dbo].[sp_ED_Measures]
@StartDate date,
@EndDate date,
@Hospital varchar(5)
AS
BEGIN
SET NOCOUNT ON;

[Code] ......

View 5 Replies View Related

How To Reduce The Time When Using A Variable

Jun 9, 2006

Hi,

I have a table with column value like '123 345 678 143 648' like that. What I need to do is I have to take each code value and put it as a new record in another table. So, if I say 'Select substring(column_name,1,3) from table' then it is very fast (fraction of second). But since I need to take each code and the # of codes in each record may vary, I am using a while loop to take each code and so I delclared a variable @i and now my select statement is like this: 'Select substring(column_name,@i,3) from table'. Interesting now this select statement is taking almost 2 mins for each iteration.

Why it is like this? Is there any way I can reduce the time taken to execute each iteration?

Thanks.

View 6 Replies View Related

How To Reduce Time In Taking BACKUP

Dec 6, 1999

Hi guys.

I am having trouble in time issues while backuping my database.

My database size is around 50GB. It is taking around 5hrs.

Is there any way to reduce the 5 hr backup time to 3 or less.

Thanks in advance
MAK

View 1 Replies View Related

Reduce Time For Search Query

Feb 26, 2008

Hi,I have a task at hand to reduce the time taken for search query toexecute. The query fetches records which will have to sorted bydegrees away from the logged in user. I have a function whichcalculates the degrees, but using this in the search query slows theexecution and takes about 10 secs to complete which is unacceptable.Please advice. Your help is much appreciatedFor more details plz see:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=97021ThanksIsfaar

View 6 Replies View Related

Transact SQL :: Create Index On Temp Table To Reduce Run Time Of Update Query

Apr 29, 2015

I want to create index for hash table (#TEMPJOIN2) to reduce the update query run time. But I am getting "Warning!

The maximum key length is 900 bytes. The index 'R5IDX_TMP' has maximum length of 1013 bytes. For some combination of large values, the insert/update operation will fail". What is the right way to create index on temporary table.

Update query is running(without index) for 6 hours 30 minutes. My aim to reduce the run time by creating index. 

And also I am not sure, whether creating index in more columns will create issue or not.

Attached the update query and index query.

CREATE NONCLUSTERED INDEX [R5IDX_TMP] ON #TEMPJOIN2
(
[PART] ASC,
[ORG] ASC,
[SPLRNAME] ASC,
[REPITEM] ASC,
[RFQ] ASC, 

[Code] ....

View 7 Replies View Related

SQL Server 2014 :: Gathering Stored Procedure Execution Time In Real Time?

Jun 11, 2015

Is there a way to keep track in real time on how long a stored procedure is running for? So what I want to do is fire off a trace in a stored procedure if that stored procedure is running for over like 5 minutes.

View 5 Replies View Related

Data Access :: How To Reduce Record On The Basis Of Time If Minimum 2 Minutes Duration In Server

Sep 8, 2015

I have a table with following data

(Id, date ,time)

11 2015/8/1
12:20:00

11
2015/8/1 12:21:00

11 2015/8/1
18:05:20

12 2015/8/1
11:20:00

12 2015/8/1
11:21:00

12 2015/8/1
18:10:20

I need the table with following record only

(Id, date ,time)

11 2015/8/1
12:20:00

11 2015/8/1
18:05:20

12 2015/8/1
11:20:00

12 2015/8/1
18:10:20

View 7 Replies View Related

Execution Time

Jan 10, 2008

Hello Anybody !
I want to get the execution time of a query, I mean I will run the one sql statement like this " SELECT * FROM tblname WHERE field1 = '009' and then I want to get from my program execution time of this query. I think I just keep the sys time before run it and compare with sys time when finished it. But I don't like this one, So, can I get the execution time from sql server by running their sys s-procedure or something like.
 Thanks.
 

View 4 Replies View Related

Execution Time Of An Sp.

Jan 18, 2002

Is it possible that a stored procedure runs slower when called by an application,and runs faster when executed as 'exec xxxxx' on query analyzer?
It's actually happening to us.Any clue??
thanks.
Di.

View 1 Replies View Related

Job Execution Time

Aug 11, 2006

i observed a strange problem in my production setup. i have a job which updates usage metrics (for reporting) which is scheduled to run once in a day. (the job invokes an sp to do this. the sp refers two tables to retrieve/update information, say TableA and TableB).

the job normally takes an average of 25 seconds to complete. all of a sudden the job execution time increased to 6 minutes and 52 seconds. now, the average job execution time is 8 minutes. there is no table/sp change in the DB

the only thing i observed is that one of the tables referred by the sp has 30,000 records added to it, on the day from which the job execution time increaed to 6 minutes.

i have updated the statistics on the Table, but the execution time remains unchanged. can any one suggest any possible causes for such a scenario.

i expect a few hints with which i can explore my production DB and find out the causes for the increased execution time for the sp.

Pl discuss...

Thanks in advance

View 1 Replies View Related

Execution Time

Feb 10, 2008

Hello,
What is the built-in-function to check the Query execution time in milli seconds.
thanks

View 2 Replies View Related

Execution Time

Nov 23, 2007

When I manually run the ssis package i.e. by clicking the run button) it takes about a second to complete.
This package is scheduled to run as a job every two minutes. In the history window of jobs in sql server 2005, I see that each time the job takes about 31 seconds. Do you know why it takes 31 seconds where it should take about 1 second to complete?
Thanks

View 3 Replies View Related

Execution TIme Problem

Sep 21, 2007

I am trying to run a SQL Server procedure from a program in ASP.Net 2005. This procedure is to insert around 500 records(can exceed every month) in a table with 4 columns and is also containing another small procedure also. When this procedure is executed from online server, it shows timeout message as:
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
 But when the same procedure is run from SQL Query Anayser it excute within seconds. How can i solve this problem , i need this solution urgently too.
Hope to get ur response soon.
 

View 6 Replies View Related

Execution Time Of Scheduled Job

Dec 18, 2000

Hi,

I want to know if it's possible to retrieve by programmation the time that it took to run a scheduled job.

Thank

Martin

View 2 Replies View Related

Automatic Execution Of A SP At A Given Time

Oct 24, 2000

Is there a way to have an SP execute at a designated time? It should run every day at the same time.

TIA,
Arthur

View 2 Replies View Related

Query Execution Time

Mar 21, 2007

A query that runs in a second or so in Query Analyzer requires 20 seconds in a linked Access Project.

What's the secret of MS_Access poor performance, and can it be improved?

Tom Stuart

View 2 Replies View Related

View Execution Time-out

Apr 26, 2007

I have a query that is taking 30-40sec to execute in a SQL Server 2005 Standard Edition database. However, when I use that same query to create a named view, and then try to open the view, I get the following error (eventually) after I attempt to open the view:



Executed SQL Statement: select ....

Error Source: .net sql data provider

Error Message: Timeout expired. The timeout period elapsed prior to completion of the operation, or the server is not responding.



Is there a server or set parameter that I can adjust that will allow my view to complete execution?

View 2 Replies View Related

Query Execution Time?

Nov 23, 2007

How to get the execution time of a query in sql server 2000?

Thanks in advance

View 2 Replies View Related

Estimating Execution Time Of A Query

Jun 8, 2000

Hi All

Is there anyway you can estimate the execution time/cost of a query prior to actually executing it?

Steve
EDF Man International
sfarmer@edfman.com

View 1 Replies View Related

Getting Actual Execution Time Of MDX Query

Nov 11, 2012

how can I get the execution time in millisecond of an MDX query in SSAS?For sql we can get it by:

Code:
set statistics time on
--query----

set statistics time off.But I am not getting anything for MDX.

View 6 Replies View Related

Findout Query Execution Time

Jan 24, 2006

hello friends,
how can we findout the query execuetion time in mili seconds.

for sample

select * from tabelname;

how much time it will take to retrive result.
thanks.

View 7 Replies View Related

Find Querey Execution Time

Nov 22, 2007

Hi!
How can i find the querey execution time in sql 2000.
If u have any article or books online please suggest me.

Thanks!

View 9 Replies View Related

How To Optimize Following Query For Execution Time

Mar 18, 2008

Hell All,
Following query takes 7 minutes to execute while using search criteria as shown below in blue text(ie. IN(2006,2007)
if criteria changes to =2006 as shown in 2),this takes 2minutes

But I want expected output as in query 1) in less time.
How to optimize following query for execution time?

1)select sum(PB.CONSN_QTY)Consumption,Count(*),PB.BillPro_Year
from tbtrans_prowaterbill PB
INNER JOIN MIDC_AREA MA
ON PB.Area_cd = MA.Area_cd INNER JOIN MIDC_Division MD ON MA.Div_CD = MD.Division_CD
INNER JOIN MIDC_Circle MC ON MD.Circle_CD = MC.Circle_CD
INNER JOIN TBMST_SubDiv TS ON MA.SubDiv_CD = TS.SubDiv_CD
INNER JOIN MIDC_Zone MZ ON MD.Zone_CD = MZ.Zone_CD
INNER JOIN tbmst_consumer TC ON PB.cons_no = TC.Cons_No
INNER JOIN TBMST_CONSTYPE TCT ON TCT.Cons_Type = TC.Cons_Type
where pb.billpro_year IN('2006','2007') and MTR_Size = 15 and TCT.Cons_Type = '1A2'
and MZ.Zone_Name = 'MUMBAI' and MC.Circle_NAME = 'MMR' and MD.Division_Name = 'Dombivli' and TS.SubDiv_DESC = 'THANE DIVISION STAFF'
group by PB.BillPro_Year





2)select sum(PB.CONSN_QTY)Consumption,Count(*),PB.BillPro_Year
from tbtrans_prowaterbill PB
INNER JOIN MIDC_AREA MA
ON PB.Area_cd = MA.Area_cd INNER JOIN MIDC_Division MD ON MA.Div_CD = MD.Division_CD
INNER JOIN MIDC_Circle MC ON MD.Circle_CD = MC.Circle_CD
INNER JOIN TBMST_SubDiv TS ON MA.SubDiv_CD = TS.SubDiv_CD
INNER JOIN MIDC_Zone MZ ON MD.Zone_CD = MZ.Zone_CD
INNER JOIN tbmst_consumer TC ON PB.cons_no = TC.Cons_No
INNER JOIN TBMST_CONSTYPE TCT ON TCT.Cons_Type = TC.Cons_Type
where pb.billpro_year = '2006' and MTR_Size = 15 and TCT.Cons_Type = '1A2'
and MZ.Zone_Name = 'MUMBAI' and MC.Circle_NAME = 'MMR' and MD.Division_Name = 'Dombivli' and TS.SubDiv_DESC = 'THANE DIVISION STAFF'
group by PB.BillPro_Year

View 3 Replies View Related

SQL Server - Script Execution Time

Nov 23, 2005

Hello All.We have tested following SQL script from query analyzer:-- Script beginDECLARE @I int;SET @I = 1;WHILE @I < 10000000 BEGINSET @I = @I + 1;END-- Script endThe script was tested on the folowing PCs with following results:PC 1:Pentium 4:CPU: 1.6 MhzRAM: 256 MbHDD: 80 Gb (IDE)OS: Windows Advanced Server SP4SQL: Developer Edition SP4Result: Script execution time - 0:54PC 2:IBM X445, 4 XEON 3.0 Ghz, 4 Mb L3 CacheRAM: 8 GbRAID 5 with (SCSI) 15k disksOS: Windows 2000 Advanced server SP4SQL: Enterprise Edition SP 4 + FixResult: Script execution time - 2:19PC 3:DELL Power edge 1600, 2 XEON 2.40 Ghz,RAM: 1 GbRAID 1 with (SCSI) 10k disksOS: Windows 2003 Enterprise Edition SP1SQL: Enterprise Edition SP 4 + FixResult: Script execution time - 1:16NOTE 1:All PCs have latest hardware updatesNOTE 2:PC 1 - is local pc, with no external connectionsPC 2 - is server with some number of other, external connections;PC 3 - is server with a few external connections;I have following question: Is the above mentioned execution time isnormal for such script ?Also it would be very nice of You to run the script on Your SQL serversand inform me about execution time resultsThank You beforehand

View 3 Replies View Related

Limit Query Execution Time

Aug 15, 2007

Hello,I ran a query that I thought would take an hour, but instead took 14hours to run. The consequence was it bogged down our data warehouseand the overnight build was adversely impacted.Is there a local setting I can set to limit the execution time myquery will take? I dont want to have a server setting and impact otherqueries, just the one I am running.I know there will be people asking about the 14 hour build and what isit doing and so forth. I will address that but I also look to thesesituations as a learning opportunity.Thanks in advance.Rob

View 3 Replies View Related

Execution Time-out In View Designer?

Jun 12, 2015

Currently I have around 4 million records. I have normalized the table the best I know how. I am using SQL Server 2008 r2 express running on a local instance. I am trying to create a view in the View designer. I run the query in query editor it takes just under a minute to run. When I run it in the view designer it times out in 30 sec. 

View 3 Replies View Related

Long Execution Time When Using Parameter

Dec 20, 2007

Hi all,

If I use the following query for a Dataset and the execution takes a few seconds to show results



SELECT *

FROM dbo.ICParameter

WHERE (PatientID = @ID ) AND (LogTime > DATEADD(day, -1, GETDATE()))

ORDER BY LogTime


If I replace '99010200101' with @ID and enter '99010200101' when prompted for ID, the execution takes forever. Actually I have never got any results even after waiting for 10 minutes.

Could anyone shed some light on this?

Thanks in advance.

View 19 Replies View Related

Stored Procedure Execution Time

Mar 7, 2008

Hi all,I have a problem with a stored procedure.This stored procedure inserts around bout 500,000 records but when it is executed it takes about 15-16 hours to do so.The stored procedure is using a temporary table to do this and is also calling a function.Please let me know if there is a way to reduce the execution time.will a cursor help?
Thanks,
Anne.

View 19 Replies View Related

Tracking Execution Time Of Each Step?

Jul 17, 2007

hi,



i am currently in the process of moving a bunch of jobs into SSIS from another ETL tool. I would like to benchmark the two products against each other by comparing how long each step of an ETL process would take.



I see no way to do this in SSIS, there is the Progress tab but it doesnt list start/time and end/time. Plus I having loops and things which I want to know how long each iteration takes.



Is there a way to track all this?



Thanks

View 5 Replies View Related

How To Catch Stored Procedure Execution Time?

Dec 6, 2007

Hello, everyone:

For performance issue, I need to catch the stored procedure execution time. Any suggestion will be appreciated. Thanks.

ZYT

View 14 Replies View Related

SQL 2012 :: Removing Joins And Execution Time?

Jun 13, 2014

I have a query which contains 12 left outer join. I remove some of the joins that don't have parameters. The result is coming same but usually when we remove joining it should take less exec time but for me it is taking more time. What could be the reason?

I am getting same result set.

View 6 Replies View Related

T-SQL (SS2K8) :: Finding Total Execution Time?

Oct 30, 2014

I have a SP SPone. i have optimized that and kept it as SPone_Optimized. i would like to test the both SP's execution time to find out how best the optimized one fares.

i planned to test it as follows

declare @starttime datetime,@endtime datetime
declare @count int=0
select @starttime=getdate()
while(@i<10000)
begin
execute SPone_optimized @param='value1'
end
select @endtime=getdate()
select datediff(ms,@stattime,@endtime) 'total_exec_time'

----- for the SP that is before optimize

declare @starttime datetime,@endtime datetime
declare @count int=0
select @starttime=getdate()
while(@i<10000)
begin
execute SPone @param='value1'
end
select @endtime=getdate()
select datediff(ms,@stattime,@endtime) 'total_exec_time'

View 9 Replies View Related







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