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?
database : MS SQL server ver 6.50.201 problem: server startup / server time out details SQL server shows usage of 100 % CPU utilization, if started automatically / manually. and does not proceed / hangs up(server time out), but win nt operates extremely slow.
The background of the situation in brief is as stated below. The errorlog was found to have the messages warning : OPEN OBJECTS parameter may be too low. Attempt was made to free up descriptors in localdes() Run sp_configure to increase parameter value.
Error : 644, severity : 21, state; state 1 the non clustered leaf row entry for page 2 row 1 was not found in index page 40 indexid 2 database 'tempdb'
Error : 2620 severity 21 state 3 the offset of the row number at offset 32 does not match the entry in the offset table of the follwoing page : page pointer = 0x1395800, page no= 40, status = 0x2, objectid = 1, indexid = 2
Action taken I used the sp_option to increase the open objects from default 500 to 1000, and the LE threshold maximum value to 400 from the default 200. after which I used reconfigure go and restarted the computer to take the effect. The following did work fine and server was working ok. But then from yesterday I am having the problem that the MS SQL sever is utilizing the cpu to 98 - 99 % and I am not able to connect to the server. I tried to start the server with the minimal configuration by specifying the -f option in the service manager start up options, it showed the following message 00/12/07 10:40:49.73 ods Unable to connect. The maximum number of '5' configured user connections are already connected. System Administrator can configure to a higher value with sp_configure. 00/12/07 10:40:50.02 ods Unable to connect. The maximum number of '5' configured user connections are already connected. System Administrator can configure to a higher value with sp_configure.
afterwhich I tried with the option of starting the sql server with the option -c -f which in the event detail of the win nt shows "mesg 18109: Recovery dbid 6 ckpt(55813,8) oldest tras= (55813,0)"
The open procedure for service "MSSQLServer" in DLL "SQLCTR60.DLL" has taken longer than the established wait time to complete. The wait time in milliseconds is shown in the data. DB-LIBRARY error - SQL Server connection timed out.
The dbid is / was 6 for both the instances, the database which is having this dbid is database name "AM" and is having above 1300 tables (approx) what could be the problem and solution for it? kindly help me out of this situation.
If any more information is needed please contact me on devendrakulkarni@yahoo.com / devendra@me.iitb.ernet.in
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)
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!!!
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).
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
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?
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.
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.
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
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
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"
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.
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)
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
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.
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.
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.
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.
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.
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
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
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.
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.
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.
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.
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.
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
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.
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?
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.
I was just wondering if anybody came across this behaviour where closing a Fast Forward Read only cursor takes abnormally long time to close. I am running SQL Server 2005 standard edition.
