Suddenly Stored Procedure Takes Too Much Time?
Jul 17, 2015
I have a sp that was taking very little time (about 34 sec). But suddenly is stacked. It is running and running and running but not LOCKED neither SUSPENDED. It is always RUNNABLE. I have made Index and statistics optimization but nothing. I looked into execution plan but everything seems ok. All the time is in 3 indexes that are Index Seek and not Table Scan!!! So why is stacked... I do not know how much time it takes because I have to stop it. (SQL SERVER 2008 R2, the database was migrated from SQL SERVER 2000)
View 6 Replies
ADVERTISEMENT
Jun 25, 2007
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
View 3 Replies
View Related
Sep 21, 2007
I have a Stored Procedure that has a query in it and it take 0 second and then a stored procedure that takes 16 seconds. From what I can tell they shoul be the same.
It doesn't recompile when i run the stored procedure, I checked that.
View 8 Replies
View Related
Jan 21, 2014
On SQL 2012 (64bit) I have a CLR stored procedure that calls another, T-SQL stored procedure.
The CLR procedure passes a sizeable amount of data via a user defined table type resp.table values parameter. It passes about 12,000 rows with 3 columns each.
For some reason the call of the procedure is verz very slow. I mean just the call, not the procedure.
I changed the procdure to do nothing (return 1 in first line).
So with all parameters set from
command.ExecuteNonQuery()to
create proc usp_Proc1
@myTable myTable read only
begin
return 1
end
it takes 8 seconds.I measured all other steps (creating the data table in CLR, creating the SQL Param, adding it to the command, executing the stored procedure) and all of them work fine and very fast.
When I trace the procedure call in SQL Profiler I get a line like this for each line of the data table (12,000)
SP:StmtCompleted -- Encrypted Text.
As I said, not the procedure or the creation of the data table takes so long, really only the passing of the data table to the procedure.
View 5 Replies
View Related
Mar 3, 2005
Hi there... I wrote a SP to check for different types of exceptions in a few database tables. When I was writing the scripts, everything seemed to execute fairly quickly and I was satisfied with the performance. When I completed the scripts and compiled them into a stored procedure and ran it (using Exec), it took a lot longer to run than I thought it would. So I went through each section of the script and ran each portion individually to see which part was taking so long.... but all the scripts ran very quickly. The individual scripts, run separately, took a combined total of 0:26 to run.... but the SP was taking 1:30 to run. (????) So then I took ALL the script contained in the SP and ran it by itself in the Query Analyzer.... it took 0:27 to run. (??????)
So basically... the script that I wrote takes 27 seconds to execute, when run by itself in the Query Analyzer... but when I take that very same script and turn it into a Store Procedure and run it, it takes a minute and a half.
Any ideas why?? I thought SP's were supposed to run faster because they're pre-compiled.
WATYF
View 1 Replies
View Related
Jan 7, 2004
Hello -
I need to call a SQL Server stored procedure, which takes over five hours to run, from an asp.NET web page. This procedure then calls a DTS package which is what takes 5+ hours to run. I need the user to be able to click on the 'run' button and have the page kick off the stored procedure (or the DTS Package if that will work instead) and display a message saying the load has begun and to check a 'status' link.
Right now I get a page timeout because it's waiting for results.
Any help would be greatly appreciated!
Thanks,
cat72
View 1 Replies
View Related
Sep 17, 2007
I used a stored procedure in my report. If I run the sp in Management Studio (on my pc, database is on a sql server) it takes only several minutes; but from reporting services (also on pc) I put it in the data tab and execute it, it takes forever, actually never finish. I want to know why it's taking so long to execute it from reporting services while it returns data instantly from Mgt Studio. There is cursor in the sp. I don't know whether this is the culprit. Anyone knows why? Thanks!
Below is the sp.
--------------------------------------------------------------------
create proc [dbo].[p_national_by_week]
as
set nocount on
declare @s1 nvarchar(2000), @parmdefinition nvarchar(300), @rangestart smalldatetime, @rangeend smalldatetime
, @price_low money, @price_high money, @weekdate smalldatetime
declare c1 cursor for
--- GG change for reg dates.
select weekdate from vtRealEstate_RealtorListing_WeekDates
open c1
fetch from c1 into @weekdate
while @@fetch_status =0
begin
select @rangeend = @weekdate+7, @rangestart=@weekdate
select @s1 = N'
declare @mlsid_count int, @avg_price money, @avg_day_on_market int, @median_price money, @c1 int
select @mlsid_count=count(*), @avg_price=avg(CurrentPricefilter),
@avg_day_on_market=avg(datediff(dd, FirstListedDate, LastModifiedDate))
from vtRealEstate_RealtorListings
where ((FirstListedDate <= @rangeStart and LastModifiedDate >= @rangeStart) or
(FirstListedDate >= @rangeStart and FirstListedDate < @rangeEnd)
)
and currentpricefilter is not null
and mlsidfilter is not null
select @c1=@mlsid_count/2
set rowcount @c1
select @median_price = CurrentPricefilter from vtRealEstate_RealtorListings
where
((FirstListedDate <= @rangeStart and LastModifiedDate >= @rangeStart) or
(FirstListedDate >= @rangeStart and FirstListedDate < @rangeEnd)
)
and currentpricefilter is not null
and mlsidfilter is not null
order by currentpricefilter
insert report_detail_test (weekdate, mlsid_count, avg_price, median_price
, avg_day_on_market)
values(@weekdate, @mlsid_count, @avg_price, @median_price, @avg_day_on_market)
', @parmdefinition=N'@rangestart smalldatetime, @rangeend smalldatetime, @weekdate smalldatetime'
exec sp_executesql @s1, @parmdefinition, @rangestart=@rangestart, @rangeend=@rangeend
, @weekdate = @weekdate
fetch from c1 into @weekdate
end
select weekdate
, mlsid_count
, avg_price
, median_price
, avg_day_on_market
from report_detail_test
order by WeekDate
View 2 Replies
View Related
Dec 15, 2006
I am using VS2005 (VB) to develop a PPC WM5.0 Program. And I am using SQLCE 3.0. My PPC Hardware is in 400MHz.
The question is when the program try to insert the first record into sdf database after each time the program started. It takes a long time. Does anyone know why and how can I fix it?
I will load the whole database into a dataset when the program start and do all the "Insert", "Update", "Delete" in this dataset and fill it into database after each action.
cn.Open()
sda = New SqlCeDataAdapter(SQL, cn) 'SQL = Select * From Table
scb = New SqlCeCommandBuilder(sda)
sda.Update(dataset)
cn.Close()
I check the sda.update(), it takes about 0.08s for filling one record into database normally. But:
1. Start the PPC Program
2. Load DB into dataset
3. Create a ONE new record in dataset
4. Fill back to DB
When I take this four steps everytime, the filling time is almost 1s or even more!
Actually, 0.08s is just a normal case. Sometimes, it still takes over 1s to filling back a dataset which only inserted one record when the program is running. (Even all inserted records are exactly the same in data jsut different in the integer key)
However, when I give up the dataset and using the following code:
cn.Open()
Dim cmd As New SqlCeCommand(SQL, cn) ' I have build the insert SQL before (Insert Into Table values(XXXXXXXXXXXXXXX All field)
cmd.CommandType = CommandType.Text
cmd.ExecuteNonQuery()
cn.Close()
StartTime = Environment.TickCount
I found that it is still the same that the first inserted record takes more time, but just about 0.2s. And the normal insert time is around 0.02s. It is 4 times faster!!!
View 1 Replies
View Related
Nov 12, 2007
Hi,
We need to select rows from the database that have been recently inserted/updated. We have a main primary table (COMMIT_TEST) and a second update table (COMMIT_TEST_UPDATE). The update table contains the primary key and a LAST_UPDATE field which is a datetime (to tell us when an update occurred). Triggers on the primary table are used to populate the update table.
If we insert or update the primary table in a transaction, we would expect that the datetime of the insert/update would be at the commit, however it seems that the insert/update statement is cached and getdate() is executed at the time of the cache instead of the commit. This causes problems as we select rows based on LAST_UPDATE and a commit may occur later but the earlier insert timestamp is saved to the database and we miss that update.
We would like to know if there is anyway to tell the SQL Server to not execute the function getdate() until the commit, or any other way to get the commit to create the correct timestamp.
We are using default isolation level. We have tried using getdate(), current_timestamp and even {fn Now()} with the same results. SQL Queries that reproduce the problem are provided below:
/* Different functions to get current timestamp €“ all have been tested to produce the same results */
/*
SELECT GETDATE()
GO
SELECT CURRENT_TIMESTAMP
GO
SELECT {fn Now()}
GO
*/
/* Use these statements to delete the tables to allow recreate of the tables */
/*
DROP TABLE COMMIT_TEST
DROP TABLE COMMIT_TEST_UPDATE
*/
/* Create a primary table and an UPDATE table to store the date/time when the primary table is modified */
CREATE TABLE dbo.COMMIT_TEST (PKEY int PRIMARY KEY, timestamp) /* ROW_VERSION rowversion */
GO
CREATE TABLE dbo.COMMIT_TEST_UPDATE (PKEY int PRIMARY KEY, LAST_UPDATE datetime, timestamp ) /* ROW_VERSION rowversion */
GO
/* Use these statements to delete the triggers to allow reinsert */
/*
drop trigger LOG_COMMIT_TEST_INSERT
drop trigger LOG_COMMIT_TEST_UPDATE
drop trigger LOG_COMMIT_TEST_DELETE
*/
/* Create insert, update and delete triggers */
create trigger LOG_COMMIT_TEST_INSERT on COMMIT_TEST for INSERT as
begin
declare @time datetime
select @time = getdate()
insert into COMMIT_TEST_UPDATE (PKEY,LAST_UPDATE)
select PKEY, getdate()
from inserted
end
GO
create trigger LOG_COMMIT_TEST_UPDATE on COMMIT_TEST for UPDATE as
begin
declare @time datetime
select @time = getdate()
update COMMIT_TEST_UPDATE
set LAST_UPDATE = getdate()
from COMMIT_TEST_UPDATE, deleted, inserted
where COMMIT_TEST_UPDATE.PKEY = deleted.PKEY
end
GO
/* In our application deletes should never occur so we don€™t log when they get modified we just delete them from the UPDATE table */
create trigger LOG_COMMIT_TEST_DELETE on COMMIT_TEST for DELETE as
begin
if ( select count(*) from deleted ) > 0
begin
delete COMMIT_TEST_UPDATE
from COMMIT_TEST_UPDATE, deleted
where COMMIT_TEST_UPDATE.PKEY = deleted.PKEY
end
end
GO
/* Delete any previous inserted record to avoid errors when inserting */
DELETE COMMIT_TEST WHERE PKEY = 1
GO
/* What is the current date/time */
SELECT GETDATE()
GO
BEGIN TRANSACTION
GO
/* Insert a record into the primary table */
INSERT COMMIT_TEST (PKEY) VALUES (1)
GO
/* Simulate additional processing within this transaction */
WAITFOR DELAY '00:00:10'
GO
/* We expect at this point that the date is written to the database (or at least we need some way for this to happen) */
COMMIT TRANSACTION
GO
/* get the current date to show us what date/time should have been committed to the database */
SELECT GETDATE()
GO
/* Select results from the table €“ we see that the timestamp is 10 seconds older than the commit, in other words it was evaluated at */
/* the insert statement, even though the row could not be read with a SELECT as it was uncommitted */
SELECT * FROM COMMIT_TEST
GO
SELECT * FROM COMMIT_TEST_UPDATE
Any help would be appreciated, we understand we could make changes to the application/database to approximate what we need, but all the solutions have identified suffer from possible performance issues, or could still lead to missing deals (assuming the commit time is larger than some artifical time window).
Regards,
Mark
View 8 Replies
View Related
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
May 15, 2008
Hello All,
Below carry takes too much time while execution
Select
'PIT_ID' = CASE WHEN Best_BID_DATA.PIT_ID IS NOT NULL THEN Best_BID_DATA.PIT_ID ELSE Best_OFFER_DATA.PIT_ID END,
Best_Bid_Data.Bid_Customer,
Best_Bid_Data.Bid_Size,
Best_Bid_Data.Bid_Price,
Best_Bid_Data.Bid_Order_Id,
Best_Bid_Data.Bid_Order_Version,
Best_Bid_Data.Bid_ProductId,
Best_Bid_Data.Bid_TraderId,
Best_Bid_Data.Bid_BrokerId,
Best_Bid_Data.Bid_Reference,
Best_Bid_Data.Bid_Indicative,
Best_Bid_Data.Bid_Park,
Best_Offer_Data.Offer_Customer,
Best_Offer_Data.Offer_Size,
Best_Offer_Data.Offer_Price,
Best_Offer_Data.Offer_Order_Id,
Best_Offer_Data.Offer_Order_Version,
Best_Offer_Data.Offer_ProductId,
Best_Offer_Data.Offer_TraderId,
Best_Offer_Data.Offer_BrokerId,
Best_Offer_Data.Offer_Reference,
Best_Offer_Data.Offer_Indicative,
Best_Offer_Data.Offer_Park
from
(
Select PITID PIT_ID, CustomerId Bid_Customer, Size Bid_Size, Price Bid_Price, orderid Bid_Order_Id, Version Bid_Order_Version,
ProductId Bid_ProductId, TraderId Bid_TraderId, BrokerId Bid_BrokerId,
Reference Bid_Reference, Indicative Bid_Indicative, Park Bid_Park
From OrderTable C
Where
version = (select max(version) from OrderTable where orderid = c.orderid)
and BuySell = 'B'
and Status <> 'D'
and Park <> 1
and PitId in (select distinct pitid from MarketViewDef Where MktViewId = 4)
and Price =
( Select max(Price) From OrderTable cc
where version = (select max(version) from OrderTable where orderid = cc.orderid)
and PitId = c.PitId
and BuySell = 'B'
and Status <> 'D'
and Park <> 1
)
and Orderdate =
( Select min(Orderdate) From OrderTable dd
where version = (select max(version) from OrderTable where orderid = dd.orderid)
and PitId = c.PitId
and BuySell = 'B'
and Status <> 'D'
and Price = c.Price
and Park <> 1
)
and OrderId = (select top 1 OrderId from OrderTable ff
Where version = (select max(version) from OrderTable where orderid = ff.orderid)
and orderid = ff.orderid
and PitId = c.PitId
and BuySell = 'B'
and Status <> 'D'
and Price = c.Price
and Orderdate = c.Orderdate
and Park <> 1
)
) Best_Bid_Data
full outer join
(
Select PITID PIT_ID, CustomerId Offer_Customer, Size Offer_Size, Price Offer_Price, orderid Offer_Order_Id, Version Offer_Order_Version,
ProductId Offer_ProductId, TraderId Offer_TraderId, BrokerId Offer_BrokerId,
Reference Offer_Reference, Indicative Offer_Indicative, Park Offer_Park
From OrderTable C
Where
version = (select max(version) from OrderTable where orderid = c.orderid)
and BuySell = 'S'
and Status <> 'D'
and Park <> 1
and PitId in (select distinct pitid from MarketViewDef Where MktViewId = 4)
and Price =
( Select min(Price) From OrderTable cc
where version = (select max(version) from OrderTable where orderid = cc.orderid)
and PitId = c.PitId
and BuySell = 'S'
and Status <> 'D'
and Park <> 1
)
and Orderdate =
( Select min(Orderdate) From OrderTable dd
where version = (select max(version) from OrderTable where orderid = dd.orderid)
and PitId = c.PitId
and BuySell = 'S'
and Status <> 'D'
and Price = c.Price
and Park <> 1
)
and OrderId = (select top 1 OrderId from OrderTable ff
Where version = (select max(version) from OrderTable where orderid = ff.orderid)
and orderid = ff.orderid
and PitId = c.PitId
and BuySell = 'S'
and Status <> 'D'
and Price = c.Price
and Orderdate = c.Orderdate
and Park <> 1
)
) Best_Offer_Data
ON Best_Bid_Data.Pit_Id = Best_Offer_Data.Pit_Id
Can any one please help me?
Thanks
Prashant
View 2 Replies
View Related
Oct 27, 2007
Hi all,
I have MS Time Seeries model using a database of over a thousand products each of which has hundreds of cases. It amazingly takes only a few minutes to finish processing the model, but when I click Mining Model Viewer to view the models, it takes many hours to show up. Once the window is open, I can choose model for different products almost instantly. Is this normal?
View 1 Replies
View Related
Feb 28, 2006
i have a table which contains a text field
and basically i have to check if a text or phrase exist in that text
select count(*) from MYtable where TxtField like '%MYPHRASE%'
there are 700,000 records in that table and whenever i query it takes 9 seconds to give me the recordcount.
what i am doing wrong
View 3 Replies
View Related
Nov 22, 2007
Hi all,
I have a stored procedure that is called from a VB.NET application that takes an enormously long time to execute. In the QA it only takes 10sec but in the application it takes ages. The stored procedure is as follows:
PROCEDURE NAME IS SPTOPTWENTYUSERS
SELECT TOP 20 STRUSERNAME,SUM(INTBYTESRECVD) AS INTDOWNLOAD FROM TBLISAWEBLOGS
WHERE DTELOGDATE BETWEEN @BEGINDATE AND @ENDDATE
GROUP BY STRUSERNAME
ORDER BY INTDOWNLOAD DESC
The code that runs it is as follows:
sSQLString = SPTOPTWENTYUSERS
Using cnn As New SqlConnection(GetPath)
Try
Dim cmd As New SqlCommand(sSQLString, cnn)
Dim dr As SqlDataReader
With cmd
.CommandType = CommandType.StoredProcedure
.CommandTimeout = 0
.Parameters.Add("@BEGINDATE", SqlDbType.DateTime)
.Parameters.Add("@ENDDATE", SqlDbType.DateTime)
.Parameters("@BEGINDATE").Value = dtpStartDate.Value
.Parameters("@ENDDATE").Value = dtpEndDate.Value
End With
cnn.Open()
dr = cmd.ExecuteReader
Any help on why this happens would be much appreciated.
thanks
View 1 Replies
View Related
Jul 20, 2005
We have a re-indexing all DBs schedule job in our SQL 2000 box,normally it took 7 hours to complete but all of the sudden now ittakes more than 20 hours.What do you think it cause this problem? We have no clue.
View 2 Replies
View Related
Jan 31, 2007
Hi,
cube processing is taking more time in a new server while same cubes takes less time in another server.
the cubes are processed through DTS package
can anybody help finding out the possible reasons for this.
Regards
Naseem
View 5 Replies
View Related
Jul 23, 2005
HelloI have these tables:CREATE TABLE [dbo].[COREAttribute] ([oid] [uniqueidentifier] NOT NULL ,[CLSID] [uniqueidentifier] NOT NULL) ON [PRIMARY]CREATE UNIQUE CLUSTERED INDEX [COREAttributeOidIndex] ON[dbo].[COREAttribute]([oid], [CLSID]) WITH FILLFACTOR = 90 ON[PRIMARY]CREATE TABLE [dbo].[COREBstrAttribute] ([oid] [uniqueidentifier] NOT NULL ,[iid] [uniqueidentifier] NOT NULL ,[dispid] [int] NOT NULL ,[value] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]CREATE CLUSTERED INDEX [COREBstrAttributeOidIndex] ON[dbo].[COREBstrAttribute]([oid]) WITH FILLFACTOR = 90 ON [PRIMARY]Now when I try this query, it's taking 8-10mins.Declare @t TABLE (oid uniqueidentifier primary key,[Description] nvarchar(1024) NULL,[Name] nvarchar(1024) NULL,[UID] nvarchar(1024) NULL)DECLARE @COREBSTRAttribute TABLE (oid uniqueidentifier, dispid intNULL, value nvarchar(1024) NULL)INSERT INTO @COREBSTRAttribute select oid, dispid, valueFROM dbo.COREBSTRAttributeWHERE iid ='{1449DB20-DB97-11D6-A551-00B0D021E10A}'INSERT @tSELECT distinctc0.oid,c1.Value,c2.Value,c3.ValueFROM(SELECT oid FROM dbo.COREAttributeWHERE CLSID IN ('{1449DB2B-DB97-11D6-A551-00B0D021E10A}','{1449DB2D-DB97-11D6-A551-00B0D021E10A}','{1449DB2F-DB97-11D6-A551-00B0D021E10A}','{1449DB31-DB97-11D6-A551-00B0D021E10A}','{1449DB33-DB97-11D6-A551-00B0D021E10A}','{1449DB35-DB97-11D6-A551-00B0D021E10A}','{1449DB37-DB97-11D6-A551-00B0D021E10A}','{1449DB39-DB97-11D6-A551-00B0D021E10A}','{1449DB3B-DB97-11D6-A551-00B0D021E10A}','{1449DB3D-DB97-11D6-A551-00B0D021E10A}','{1449DB3F-DB97-11D6-A551-00B0D021E10A}','{1449DB43-DB97-11D6-A551-00B0D021E10A}','{1449DB45-DB97-11D6-A551-00B0D021E10A}','{1449DB47-DB97-11D6-A551-00B0D021E10A}','{1449DB49-DB97-11D6-A551-00B0D021E10A}','{1449DB4B-DB97-11D6-A551-00B0D021E10A}','{1449DB4D-DB97-11D6-A551-00B0D021E10A}','{1449DB51-DB97-11D6-A551-00B0D021E10A}','{DAA598D9-E7B5-4155-ABB7-0C2C24466740}','{6921DAC3-5F91-4188-95B9-0FCE04D3A04D}','{128F17D4-2014-480A-96C6-370599F32F67}','{9F3A64C9-28F3-440B-B694-3E341471ED8E}','{2E3AB438-7652-4656-9A18-4F9C1DC27E8C}','{B69E74A7-0E48-4BA2-B4B7-5D9FFEDC2D97}','{2BB836D3-2DC1-4899-9406-6A495ED395C3}','{9CFFDC3A-5DF5-4AD8-B067-6EF5A9736681}','{E18E470B-B297-43D2-B9CD-71AF65654970}','{9BDCDA97-1171-409D-B3AB-71DA08B1E6D3}','{0E91AC62-7929-4B42-B771-7A6399A9E3B0}','{C8BAE335-CCB7-4F1D-8E9D-85C301188BE2}','{97E6E186-8F32-42E6-B81C-8E2E0D7C5ABA}','{BE5B6233-D4E7-4EF6-B5FC-91EA52128723}','{4ECDAAE1-828A-4C43-8A66-A7AB6966F368}','{19082B90-EF02-45CC-B037-AFD0CF91D69E}','{6F76CEF7-EBC0-48C6-8B78-C5330324C019}','{18492042-B22A-4370-BFA3-D0481800BBC7}','{A71343AD-CC09-4033-A224-D2D8C300904A}','{EC10BD0A-FDE3-4484-BEA6-D5A2E456256C}','{F7F8A4E1-651A-4A48-B55A-E8DA59D401B2}','{A923226F-B920-4CFA-9B0D-F422D1C36902}','{A95ACA6A-16AC-47E4-A9A6-F530D50A475A}','{C31DB61A-5221-42CF-9A73-FE76D5158647}')) AS c0LEFT JOIN @COREBSTRAttribute AS c1ON (c0.oid = c1.oid)AND c1.dispid = 28LEFT JOIN @COREBSTRAttribute AS c2ON (c0.oid = c2.oid)AND c2.dispid = 112LEFT JOIN @COREBSTRAttribute AS c3ON (c0.oid = c3.oid)AND c3.dispid = 192Any help is greatly appreciated.thanksSunit
View 4 Replies
View Related
Mar 21, 2008
Hi,I've a strange problem with a INSERT query. It's taking a long time toexecute. The format is like this :INSERT INTO table1SELECT ..FROM table2Executing the SELECT .. FROM table2 is taking 30 seconds. The resultis nothing: no records are selected.When i include the INSERT part it will take 12 hours to completeINSERT INTO table1SELECT ..FROM table2There's is an index on the table and when i delete it, it gives stillthe problem.Keh?Greetz,Hennie
View 1 Replies
View Related
Oct 3, 2006
Hi,
I have SQL Server 2005 Standard edition with SP1. Whenever I start the Management Studio, it takes for ever to come up. It will take atleast 30 Sec to 1 min every time I start SQL Server. Is this some thing we have to live with in SQL Server 2005 or do I need to tweak any thing? Pls let me know.
One more thing, in the SQL Server logs I see the following message every day -> Source - spid36s, Message - Starting up database 'dbname'. Why do I get this message?
Thanks
View 7 Replies
View Related
Apr 7, 2008
Hi,
It seems inserting records takes a relatively long time. My guess is it needs time to allocate disk space for the extra space needed. Assuming this is true, are there any DB settings that allow auto space allocation in bigger chunk? I am looking for something like "DB growth factor" or " Table growth factor"
Thanks for any info.
View 6 Replies
View Related
Nov 9, 2007
Dear friends,
Our package which at the time of normal execution takes 2-2:30 mins for fetching data on VPN with some select queries. But some times its job runs for hours and hours. What could be the exact reason behind it? I guess its queries are stuch somewhere, but not when we run from the BIDS or run the job manually.
Please help. Thanks.
View 6 Replies
View Related
Feb 13, 2001
Has anybody come across situations where queries take longer to execute the second time? The server is a dedicated sql server box with 1gb memory.
Thanks in advance.
Praveena
View 2 Replies
View Related
Jul 20, 2005
Hi There,I have an update statement to update a field of a table (~15,000,000records). It took me around 3 hours to finish 2 weeks ago. After thatno one touched the server and no configuration changed. Untilyesterday, I re-ran it again and it took me more than 18hrs and stillnot yet finished!!!What's wrong with it? I can ran it successfully before. I have triedtwo times but the result was still the same.My SQL statement is:update [all_sales] aset a.accounting_month = b.accounting_monthfrom date_map bwhere a.sales_date >= b.start_date and a.sales_date < b.end_date;An index on [all_sales].sales_date is built successfully.A composite index on ([date_map].start_date, [date_map].end_date) isbuilt successfully.My server config is:SQL Server 2000 with Service Pack 3Windows 2000 with Service Pack 4DELL PowerEdge 6650 ServerDUAL XEON 1900MHz Processors2G RAM2G Page File on Drive C2G Page File on Drive DDELL Diagnostics on all SCSI harddisks were all PASSED.Any experts could simly give me a help????Thanks x 1,000,000,000
View 4 Replies
View Related
Nov 14, 2007
Hi,
Its a common issue with reporting services, exporting a report with huge data to excel takes long time to render. Im facing the same issue. Trying to export a SSRS 2005 report to Excel 2003 takes very long time.
Problem 1:
The time taken to generate the excel report is pretty long (about 10 minutes) as the report runs to hundreds of pages. (The excel has about 30,000 rows and is ~15 MB)
Problem 2:
Once I open the excel and close it the size reduces to half of it. This is understood because of lot of characters values in the report that can be represented as single byte string, that€™s probably where Excel is making a difference. In Reporting Services it always write strings as 2-byte Unicode, but Excel will always try to compress to single byte when possible.
Am majorly concerned about the Problem 1. Any solutions would be highly appreciated. Thanks in advance.
View 1 Replies
View Related
Jan 19, 2007
I have a report which is fairly simple but takes a very long time..
It involves the incidents being counted by categories hence it has several Union All.
Also the report numbers are generetd through 2 tables hence within every Union All tehre is a left or an Inner join.
sample code:
SELECT
1 Sort_Order,
COUNT(*) AS Call_Count,
'Incident Resolved at Level 1' AS Count_Type
FROM HOUAPPS237.CallsAndIncidents.dbo.PROBSUMMARYM1 T1
INNER JOIN HOUAPPS237.CallsAndIncidents.dbo.PROBSUMMARYM2 T2
ON T1.NUMBERPRGN = T2.NUMBERPRGN
WHERE PROBLEM_STATUS = 'closed'
AND T2.THIRD_ASSIGNEE IS NULL
AND T2.THIRD_ASSIGNMENT IS NULL
AND T1.SECONDARY_ASSIGNEE IS NULL
AND HAL_FIRST_RES='t'
AND DATEPART(mm, DATEADD(hour, -@offset, CAST(T1.OPEN_TIME AS DATETIME))) = @MONTH
AND DATEPART(yy, DATEADD(hour, -@offset, CAST(T1.OPEN_TIME AS DATETIME))) = @YEAR
AND T1.OTI_ORIGINATOR IN (SELECT Userid FROM HOUAPPS286.HALServiceDesk.dbo.ServiceCenterAgents)
UNION ALL
-- Calls RESOLVED BY L2
SELECT
2 Sort_Order,
COUNT(*) AS Call_Count,
'Incidents Resolved at Level 2 or 3' AS Count_Type
FROM HOUAPPS237.CallsAndIncidents.dbo.PROBSUMMARYM1 T1
LEFT JOIN HOUAPPS237.CallsAndIncidents.dbo.PROBSUMMARYM2 T2
ON T1.NUMBERPRGN = T2.NUMBERPRGN
WHERE (HAL_FIRST_RES<>'t' OR HAL_FIRST_RES IS NULL)
AND PROBLEM_STATUS = 'closed'
AND DATEPART(mm, DATEADD(hour, -@offset, CAST(T1.OPEN_TIME AS DATETIME))) = @MONTH
AND DATEPART(yy, DATEADD(hour, -@offset, CAST(T1.OPEN_TIME AS DATETIME))) = @YEAR
AND T1.OTI_ORIGINATOR IN (SELECT Userid FROM HOUAPPS286.HALServiceDesk.dbo.ServiceCenterAgents)
UNION ALL
could you suggest what might be the reason why teh report churns for so long.
thanks,
kiran.
View 2 Replies
View Related
Jul 25, 2006
I have a vb.net application using report services that has a big delay when I set the parameters with which to call the report.
I create a new reporting.reportviewer.
I set the ReportServerCredentials.NetworkCredentials, ReportServerUrl, ProcessingModem, ReportPath and everything is fine.
When I call SetParameters with a very simple parameter set, I get a delay of between 0.5 and 2.5 seconds. That delay is very noticible to the users. Below is an extract of a sql profiler trace to a database showing the start time, end time, event class and data text of the sql. I've marked the area with the delay in red.
I have no idea what is happening at that time, but Is there anything I can do to get rid of that delay?
It seems that it could be the first time the my application has had to interface with reporting services.
21/07/2006 13:29:48 363 21/07/2006 13:29:48 363 10 exec ReadChunkPortion @ChunkPointer=0xFDFF126D000000006804000001000400,@IsPermanentSnapshot=1,@DataIndex=4148,@Length=8
21/07/2006 13:29:48 363 21/07/2006 13:29:48 363 10 exec ReadChunkPortion @ChunkPointer=0xFDFF126D000000006804000001000400,@IsPermanentSnapshot=1,@DataIndex=8,@Length=4100
21/07/2006 13:29:48 363 21/07/2006 13:29:48 363 10 exec sp_reset_connection
21/07/2006 13:29:48 363 21/07/2006 13:29:48 363 10 exec CreateSession @SessionID='5zz5gh2wgwfs2hf0qb0gh155',@ReportPath=N'/Symphony Reporting/report3',@Timeout=600,@AutoRefreshSeconds=0,@OwnerSid=0x010500000000000515000000F13F1E839A1DDABDC36D00302B060000,@OwnerName=N'MCR-SYSTEMS
hutchinson',@AuthType=1,@DataSourceInfo=0x0001000000FFFFFFFF01000000000000000C020000006F4D6963726F736F66742E5265706F7274696E6753657276696365732E50726F63657373696E67436F72652C2056657273696F6E3D392E302E3234322E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D3839383435646364383038306363393105010000004A4D6963726F736F66742E5265706F7274696E6753657276696365732E44617461457874656E73696F6E732E52756E74696D6544617461536F75726365496E666F436F6C6C656374696F6E03000000106D5F636F6C6C656374696F6E42794944146D5F636F6C6C656374696F6E42795265706F7274146D5F636F6C6C656374696F6E427950726F6D70740303041C53797374656D2E436F6C6C656374696F6E732E486173687461626C651C53797374656D2E436F6C6C656374696F6E732E486173687461626C653D4D6963726F736F66742E5265706F7274696E6753657276696365732E44617461457874656E73696F6E732E436F6C6C656374696F6E427950726F6D7074020000000200000009030000000A0A04030000001C53797374656D2E436F6C6C656374696F6E732E486173687461626C65070000000A4C6F6164466163746F720756657273696F6E08436F6D70617265721048617368436F646550726F7669646572084861736853697A65044B6579730656616C756573000003030005050B081C53797374656D2E436F6C6C656374696F6E732E49436F6D70617265722453797374656D2E436F6C6C656374696F6E732E4948617368436F646550726F766964657208EC51383F010000000A0A0B000000090400000009050000001004000000010000000906000000100500000001000000090700000004060000000B53797374656D2E477569640B000000025F61025F62025F63025F64025F65025F66025F67025F68025F69025F6A025F6B0000000000000000000000080707020202020202020232D4E1DE4550ED4AB904FB9304E177480507000000394D6963726F736F66742E5265706F7274696E6753657276696365732E44617461457874656E73696F6E732E44617461536F75726365496E666F13000000046D5F6964066D5F6E616D650E6D5F6F726967696E616C4E616D650B6D5F657874656E73696F6E1B6D5F636F6E6E656374696F6E537472696E67456E63727970746564236D5F6F726967696E616C436F6E6E656374696F6E537472696E67456E63727970746564266D5F6F726967696E616C436F6E6E656374537472696E6745787072657373696F6E4261736564156D5F64617461536F757263655265666572656E6365086D5F6C696E6B49441D6D5F44617461536F757263655769746843726564656E7469616C734964096D5F73656344657363166D5F63726564656E7469616C7352657472696576616C086D5F70726F6D7074136D5F757365724E616D65456E63727970746564136D5F70617373776F7264456E63727970746564076D5F666C616773096D5F6D6F64656C4944136D5F6973456D626564646564496E4D6F64656C156D5F69734D6F64656C536563757269747955736564030101010707000103030704010707040300000B53797374656D2E477569640202010B53797374656D2E477569640B53797374656D2E4775696402544D6963726F736F66742E5265706F7274696E6753657276696365732E44617461457874656E73696F6E732E44617461536F75726365496E666F2B43726564656E7469616C7352657472696576616C4F7074696F6E020000000202494D6963726F736F66742E5265706F7274696E6753657276696365732E44617461457874656E73696F6E732E44617461536F75726365496E666F2B44617461536F75726365466C616773020000000B53797374656D2E4775696401010200000001F8FFFFFF0600000032D4E1DE4550ED4AB904FB9304E1774806090000000441525453060A0000000441525453060B0000000353514C090C0000000A00060D000000182F53796D70686F6E79205265706F7274696E672F4152545301F2FFFFFF06000000A814BB258EC9884888698BC39F85117601F1FFFFFF060000007C8BA03B04528840BACCDE1CC6E465BD091000000005EFFFFFFF544D6963726F736F66742E5265706F7274696E6753657276696365732E44617461457874656E73696F6E732E44617461536F75726365496E666F2B43726564656E7469616C7352657472696576616C4F7074696F6E010000000776616C75655F5F00080200000003000000061200000039456E74657220612075736572206E616D6520616E642070617373776F726420746F2061636365737320746865206461746120736F757263653A0A0A05EDFFFFFF494D6963726F736F66742E5265706F7274696E6753657276696365732E44617461457874656E73696F6E732E44617461536F75726365496E666F2B44617461536F75726365466C616773010000000776616C75655F5F0008020000000300000001ECFFFFFF060000000000000000000000000000000000000000000F0C00000098000000024E923E067F7AAB8735076784BE58B5E7FB39D61C8C5A39887A49C8F639783D2A0E4883F7901E6FE948DD535C1E2A84707D3071F31B5FDD7FCFC51278114BC810B48C8EC63D000F395FA8C28BFD18401221C78DDF7DB335425960CBB69E5823B62A418D46AE7120F1CCF3FCD3E3335E78DB72188BCF64457DB622592A9615775FC5C03758D12948BE507CCD16B2201C66A092A41F12B8D1D00F10000000190200000206050054000000020100048034000000440000000000000014000000020020000100000000041800FF00060001020000000000052000000020020000010200000000000520000000200200000102000000000005200000002002000054000000030100048034000000440000000000000014000000020020000100000000041800FFFF3F00010200000000000520000000200200000102000000000005200000002002000001020000000000052000000020020000540000000401000480340000004400000000000000140000000200200001000000000418001D000000010200000000000520000000200200000102000000000005200000002002000001020000000000052000000020020000540000000501000480340000004400000000000000140000000200200001000000000418001F000600010200000000000520000000200200000102000000000005200000002002000001020000000000052000000020020000540000000601000480340000004400000000000000140000000200200001000000000418001F00060001020000000000052000000020020000010200000000000520000000200200000102000000000005200000002002000054000000070100048034000000440000000000000014000000020020000100000000041800FF0106000102000000000005200000002002000001020000000000052000000020020000010200000000000520000000200200000B,@EffectiveParams=N'<Parameters>
<Parameter>
<Name>DataSource</Name>
<Type>Integer</Type>
<Nullable>False</Nullable>
<AllowBlank>False</AllowBlank>
<MultiValue>False</MultiValue>
<UsedInQuery>False</UsedInQuery>
<State>MissingValidValue</State>
<Prompt />
<PromptUser>True</PromptUser>
</Parameter>
<Parameter>
<Name>AuthLvl</Name>
<Type>Integer</Type>
<Nullable>False</Nullable>
<AllowBlank>False</AllowBlank>
<MultiValue>False</MultiValue>
<UsedInQuery>False</UsedInQuery>
<State>MissingValidValue</State>
<Prompt />
<PromptUser>True</PromptUser>
</Parameter>
<Parameter>
<Name>Home</Name>
<Type>Integer</Type>
<Nullable>False</Nullable>
<AllowBlank>False</AllowBlank>
<MultiValue>False</MultiValue>
<UsedInQuery>False</UsedInQuery>
<State>MissingValidValue</State>
<Prompt />
<PromptUser>True</PromptUser>
</Parameter>
<Parameter>
<Name>SiteFilter</Name>
<Type>Integer</Type>
<Nullable>False</Nullable>
<AllowBlank>False</AllowBlank>
<MultiValue>True</MultiValue>
<UsedInQuery>False</UsedInQuery>
<State>MissingValidValue</State>
<Prompt>Select Required Site(s)</Prompt>
<PromptUser>True</PromptUser>
</Parameter>
<Parameter>
<Name>DateFilter</Name>
<Type>DateTime</Type>
<Nullable>False</Nullable>
<AllowBlank>False</AllowBlank>
<MultiValue>True</MultiValue>
<UsedInQuery>False</UsedInQuery>
<State>MissingValidValue</State>
<Prompt>Select Required Date(s)</Prompt>
<PromptUser>True</PromptUser>
</Parameter>
</Parameters>'
21/07/2006 13:29:48 377 21/07/2006 13:29:48 377 10 exec sp_reset_connection
21/07/2006 13:29:48 377 21/07/2006 13:29:48 377 10 exec ObjectExists @Path=N'/Symphony Reporting/report3',@AuthType=1
21/07/2006 13:29:48 847 21/07/2006 13:29:48 847 10 exec sp_reset_connection
21/07/2006 13:29:48 847 21/07/2006 13:29:48 847 10 exec GetSessionData @SessionID='5zz5gh2wgwfs2hf0qb0gh155',@OwnerSid=0x010500000000000515000000F13F1E839A1DDABDC36D00302B060000,@OwnerName=N'MCR-SYSTEMS
hutchinson',@AuthType=1,@SnapshotTimeoutMinutes=1440
21/07/2006 13:29:48 847 21/07/2006 13:29:48 847 10 exec sp_reset_connection
View 2 Replies
View Related
Jan 31, 2008
The following T-SQL code is run in a vb.net (2003) module.
I am acquiring data from an OPC server into an array of data type object. It is necessary to declare the array as an object for the OPC server to return data. The OPC returns the data in 15 mS.
I now need to save this data to a table in SQL 2005 running on a 2003 server.
The table for saving the data has already been created and saving the data is actually an 'update ... set ... where' statement.
The statement is
For i as short = 1 to itemCount
sql_command = "Update [IO Log].[dbo].[IO Log] Set TagValue = " & itemValue(i) & " where TagName =' " & tagName(i)
sql_command.executeNonQuery
Next
The TagValue field datatype is decimal(18,6)
The itemValue(i) datatype is object / variant. The itemValue array contains 95% values of type Single and the rest are Integers.
The TagName datatype is varchar(50)
The tagName(i) datatype is string
When I run the loop (255 iterations) with the above query it takes 1500 mS.
If I force a conversion by changing the query to
sql_command = "Update [IO Log].[dbo].[IO Log] Set TagValue = Convert(decimal, " & itemValue(i) & ") where TagName =' " & tagName(i)
it takes 900 mS to execute for 255 iterations.
I tried one more variation
sql_command = "Update [IO Log].[dbo].[IO Log] Set TagValue = Convert(decimal(18,6), " & itemValue(i) & ") where TagName =' " & tagName(i)
which takes 1200 mS to execute for 255 iterations.
If I use the following code
dim tempValue as decimal = 123456789012.123456D
sql_command = "Update [IO Log].[dbo].[IO Log] Set TagValue = " & tempValue & " where TagName =' " & tagName(i)
it excutes in under 100 mS for 255 iterations.
There seems to be a problem during implicit / explicit conversion of type object/variant to decimal.
I need to save the data received from the OPC (255 tags) in under 150 mS.
Could somebody help?
View 18 Replies
View Related
May 2, 2000
I wrote a stored procedure (see below), which within a database create a new column (Ordering)and order rows that have the same OID, DECISION_DATE and give a to column ORDERING value 1 to the highest VOTES_REQUIRED VALUE, VALUE 2 to the second Hignest value and so On.
In Other words I am creating a sort on Index (ORDERING Column) on subsets of the database which have same OID and DECISION_DATE based on the value of VOTES_REQUIRED.
The stored procedure is working and I get the right results, but IT TAKES 3 HOURS 30 MINUTES to go through 38,000 rows. I have to run this stored procedure for a database with 200,000 rows (it may take tens of hours...).
Is there any idea to improve the following code to make it run efficiently.
Thank you in Advance.
**************************************************
CREATE PROCEDURE SP_ORDERING_TEST AS
declare @oldoid varchar(12)
declare @olddecision_date varchar(75)
declare @oid varchar(12)
declare @decision_date varchar(75)
declare @ordering varchar(1)
declare @ordering_count int
declare @votes_required varchar(12)
set @oldoid = 'space'
set @olddecision_date = 'space'
set @oid = 'space'
set @decision_date = 'space'
set @votes_required='space'
set @Decision_id = 'space'
set @ordering = '0'
set @ordering_count = 0
declare review_test_cursor cursor for
select oid,decision_date,votes_required,ordering,decision _id from MYTABLE
open review_test_cursor
fetch review_test_cursor into @oid,@decision_date,@votes_required,
@ordering,@Decision_id
while (@@fetch_status = 0 )
begin
if @oldoid <> @oid or @olddecision_date <> @decision_date
begin
set @oldoid = @oid
set @olddecision_date = @decision_date
set @ordering_count=0
end
update MYTABLE
set ordering = CAST ((@ordering_count + 1) as VARCHAR)
where decision_id = @Decision_id
set @ordering_count = @ordering_count + 1
fetch review_test_cursor into @oid,@decision_date,@votes_required, @ordering,@Decision_id
end
close review_test_cursor
deallocate review_test_cursor
*************************
View 1 Replies
View Related
Oct 11, 2006
I have a table tblCustTrans which contains
custid int
transid int
startdate datetime
value int
the custid, transid and startid are composite primary key.
the table contains more than 10 million records. Now i want to fetch record for
select * from tblcusttrans where startdate > = 10/10/2006 10:00:000 and startdate <= 10/10/2006 11:00:000
This statement is taking more than 2 hours to fetch the data. is there a way to fetch the record with less time
Regards
View 4 Replies
View Related
Dec 17, 2007
Hi All,
I'm trying to rebuild my master database for sql server 2000. The process of rebuilding stared fine. But it is almost 4 hours since it got started. Performing it on a test system. Got doubtful and started the same on another test system. Issue is same and it is almost 2 hours. The Db size is less than 100 MB in both cases. IS IT NORMAL? I've tried the same for SQL SERVER 2005 and it got finished in couple of minutes. Please advise.
View 5 Replies
View Related
Dec 28, 2006
I broke up my cube into 24 partitions. There are about 630M total fact rows in that cube.
When I open the cube to browse in BIDS or SQL Management Studio it takes very long time to open (I think 30 minutes).
Profiler does not show that it's running a query, but messages like this keep appearing throughout the time it's opening to browse:
Progress Report Begin, 14- Query, Started reading data from the 'p0' partition.
Progress Report End, 14- Query, Finished reading data from the 'p0' partition.
Progress Report Begin, 14- Query, Started reading data from the 'p10' partition.
Progress Report End, 14- Query, Finished reading data from the 'p10' partition.
and goes on like that....
View 13 Replies
View Related
May 26, 2007
I execute a pretty big sql query which joins multiple tables and I reviewed indexes on all these tables. I am happy with the result when I run the query using SSMS in the server locally i.e., where my SQL Server database is installed. It takes 4 seconds to get around 17000 records. If I run the same query in a network or from my desktop using SSMS i.e., i connect to the above mentioned SQL Server using SSMS, it takes more than 60 seconds. Not sure how to solve this. If someone could help me, it will be of great help.
Thanks.
View 1 Replies
View Related
May 26, 2008
Hi,
The scenario is the data comes from various sources and its staged into staging database. From this staging database it goes into data warehouse database. Everyday this staging database is truncated and repopulated from various sources.
I've a dimension table called DimCustomers which consists of around 300,000 rows and has lots of different types of SCD columns. It takes around 4-5 hours to load data from staging to this dimension table. Currently I'm using a For Loop container which uses a store proc to extract 15000 rows each time and populate my dimension tables. First couple of loops it goes off quickly but as and when the number reaches half of the count it slows down and hence it takes around 4-5 hours to load data.
What would be the best approach to populate this kind of dimension table.
Thanks
View 7 Replies
View Related
