Data Conversion && Saving Takes Time
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
ADVERTISEMENT
Feb 12, 2008
Hello,
I am wondering what conversion rules apply, when a string, which contains a number, is saved to a SQL Server 2005 into a column of type decimal.
This is the code I€™m using (C++):
CString cValue = "0.75"
_variant_t vtFieldValue;
vtFieldValue = _variant_t(cValue)
pRecordSet->Fields->Item["MyColumn"]->Value = vtFieldValue;
"pRecordSet" is an ADO recordset. The database column "MyColumn" is of type "decimal(19,10)".
The most important question for me is, if the regional settings of the database server or the regional settings of the client PC are considered during the conversion from the string to the decimal value. For example in standard French regional settings the "." would not be recognized as decimal separator.
I am also wondering if the language of the database instance, in which this data is saved, is considered during this conversion or any other settings of this database instance.
So my general question is: Does anybody know exactly what rules apply during the above mentioned conversion?
Thank you for your help.
Regards,
Volker
View 2 Replies
View Related
Jul 25, 2007
Can anyone help me on this...
when i select data from table using select statement it takes huge amount of time....The table contains 7 million entries and when i select by mentioning a criteria it takes around 45 secs..The system has 4GB RAM and Dual Processing CPU. The select statement does not contain any grouping and all..
Will it take this much time to retrieve data.?.
The table does include an indexed field,
So can anyone help me on the different things i can do to make the retrieval faster?
Andy
View 5 Replies
View Related
Oct 26, 2015
Last weekend many of our severs had a failed job "collection_set_3_upload". The error that occured is: "Violation of PRIMARY KEY constraint 'PK_ active_ sessions_ and_requests'. Cannot insert duplicate key in object 'snapshots.active_sessions_and_requests'. The duplicate key value is (2824333, 2015-10-25 02:54:49.7630000 +02:00, 1)."Last weekend we happened to go from summer time to winter time. i.e. the clock passed 02:00 - 3:00 two times during this night.
I.e. there is a bug in the Data Collector component that collects data for the Management Data Warehouse: it uses local time instead of UTC. I've created a Connect item to report it to Microsoft.URL...how do you get your process running again? the job will no longer run because it will every 5 minutes keep on trying to upload the conflicting data for the 2nd 2:00 - 3:00 period. I've only found one solution: get rid of all data collected but not yet uploaded.
You do this by stopping the Collection set (in SSMS go to Object Explorer -> <the server you want to fix> -> Management -> Data Collection -> System Data Collection Sets. Right click "Query Statistics" and select "Stop Data Collection Set").Then you delete the cached results from the sql server machine's harddisk. These cached results are in files located in a Temp folder on the sql machine itself, inside the AppData folder for the service account SQL Server Agent is running under. Usually it will be something like: c:Users<sql agent service account>AppDataLocalTemp.
Inside this folder delete all files that have 'QueryActivity' in their name. You'll loose all data collected since the start of wintertime, but at least your data collection process will work again.After this you can start the Collection set again by right clicking it and select "Start Data Collection Set". Every 5 minutes the data will be summarised and uploaded into your management data warehouse.
Posting Data Etiquette - Jeff Moden
Posting Performance Based Questions - Gail Shaw
Hidden RBAR - Jeff Moden
Cross Tabs and Pivots - Jeff Moden
Catch-all queries - Gail Shaw
View 0 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
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
Jul 15, 2014
All source and target date fields are defined as data type "smalldatetime". The "select" executes without error though when used with "insert into" it fails with the error:
Msg 295, Level 16, State 3, Line 25: Conversion failed when converting character string to small date-time data type..I am converting from a character string to smalldatetime since the source and target date columns are "smalldatetime". All other columns for the source and target are nvarchar(255). I assume there is an implicit conversion that I don't understand. In a test, I validated that all dates selected evaluate ISDATE() to 1.
USE [SCIR_DataMart_FromProd_06_20_2014]
GO
IF OBJECT_ID ('[SCIR_DataMart_FromProd_06_20_2014].[dbo].[IdentifierLookup]', 'U') IS NOT NULL
DROP TABLE [SCIR_DataMart_FromProd_06_20_2014].[dbo].[IdentifierLookup]
[code]....
View 9 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 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
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
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
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
Nov 16, 2007
Hi,
I have problem with JDBC 2005 (1.1) running against SQL 2005 Express edition (SP2). Sometimes, the statement takes long time (more than 10 seconds). Sometimes, the same statement takes just a few seconds. It is very unpredictable.
The query that we have problem is most of the time is join sql statement.
Does anyone see this problem?
Thanks,
View 2 Replies
View Related
Aug 9, 2015
We are importing xer formats through the wizard to sqlserver database and It takes upto 35-45 mins for each import (single project), any option to reduce the time.Is they any other import options - which can give us faster results.
View 0 Replies
View Related
