CTE Query - Long Load Time

Dec 5, 2014

I have a CTE query that is used to fill in nulls on a history table. The With statement executes just fine. sub 2 seconds on 974 records, however the main query is what's turning the whole query into a turtle. I know that it's the looping that it's doing there that is causing the slow down, but I'm just not sure how to fix it. I've tried inserting it into a temp table, refactored the code a hundred times, but nothing seems to be working.

Code is below and the execution plan is attached.
Server Version: 12.0.2342.0
Enterprise: 64bit

;WITH BuildTable
AS ( SELECT [GEGTH].[ID]
, [GEGTH].[Changed By]
, CAST( [dbo].[GetWeekStarting] ([GEGTH].[Changed Date] , 2 ) AS DATE) AS WeekOf
, [GEGT].[Title]

[code]....

View 6 Replies


ADVERTISEMENT

Dm Query Taking Long Time

May 16, 2007

I'm running a query (see below) on my development server and its taking around 45 seconds. It hosts 18 user databases ranging from 3 MB to 400 MB. The production server, which is very similar but with only 1 25 MB user database, runs the query in less than 1 second. Both servers have been running on VMWare for almost 1 year with no problems. However last week I applied SP 2 to the development server, and yesterday I applied Critical Update KB934458. The production server is still running SQL Server 2005 Standard SP 1. Other than that, both servers are identical and running Windows 2003 Server Standard SP 1. I'm not seeing this discrepancy with other queries running against user databases.



use MyDatabase

GO

select db_name(database_id) as 'Database', o.name as 'Table',

s.index_id, index_type_desc, alloc_unit_type_desc, index_level, i.name as 'Index Name',

avg_fragmentation_in_percent, fragment_count, avg_fragment_size_in_pages,

page_count, avg_page_space_used_in_percent, record_count,

ghost_record_count, min_record_size_in_bytes, avg_record_size_in_bytes, forwarded_record_count,

schema_id, create_date, modify_date from sys.dm_db_index_physical_stats (null, null, null, null, 'DETAILED') s

join sys.objects o on s.object_id = o.object_id

join sys.indexes i on i.object_id = s.object_id and i.index_id = s.index_id

where db_name(database_id) = 'MyDatabase'

order by avg_fragmentation_in_percent desc

--order by avg_fragment_size_in_pages desc

--order by page_count desc

--order by record_count desc

--order by avg_record_size_in_bytes desc

View 4 Replies View Related

EXtremely Long Time In Execution Query

Mar 21, 2007

Hi all,

I have a query, rather complex one to deal with more than 1 million rows, used to run 40 minutes in SQL Server 2000 in query analyzer. Now, it has been 10 hours in SQL Server 2005 in management studio. And still has not finished yet! Anything can go wrong here. Basically nothing changes, except for I have my server upgrade from SQL Server 2000 to SQL Server 2005. Seems something is wrong crazy in SQL Server 2005. Any suggestions?

Thanks,

Ning

View 3 Replies View Related

Simple Select Query Takes A Very Long Time

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

SQL 2005 Express Edition SP2 - Query Takes A Long Time To Run(sometimes)

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

Load All Data Without Knowing Old One Was Load In The Previous Time???

Apr 27, 2007

I just have done the SSIS example in the tutorial document included when install SQL 2005 ENT. I have a problem that whenever I test to run, the service load all data from source with out noticing about the data (I mean it load all the data to the destination), I do it several time and it continue to load all without checking. That mean the data is dublicated when the schedule run???



I think there should be a paramete or something like that to help the engine just load the new data to the destination. Could you help please?



Thank

View 3 Replies View Related

It Takes A Long Time To Insert The First Record Each Time When The Program Start

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

MS Time Series - Quick To Process The Model But Takes Very Long Time To Open Mining Model Viewer

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

First Time SSIS Long Time DTS

May 21, 2008

A few pointers would be appreciated.

I am looking at building multiple SSIS packages. There will be some similarities. Flexibility is of highest importance. The main packages will need to connect to SQL Server1 as a source and SQL Server2 as a destination to transfer over dimenion data from multiple databases. (other SSIS packages may need to use SQL Server2 as a source and SQL Server1 as a destination)

For a single dimension table containing column dim_id on the target server (SQLServer2). I need to pass the results of the following SQL and insert into SQLServer2.database.dim_table

select dim.id
from SQLServer1.database08.dim_table
union
select dim.id
from SQLServer1.database07.dim_table
union
select dim.id
from SQLServer1.database06.dim_table


Now next year the names of the databases on SQLServer1 will be database09,database08,database07!

Now so far my best thought is creating views in my destination SQL Server. So I need some way of dropping and recreating the views. Previously in DTS I would expect to see SQL Server connection that I could use as source and destination. Now I can see SQL Server destination but not source? Also How do I just use SSIS to run some SQL. i.e execute a stored procedure, drop and creat views?

Many thanks,
Ells
p.s Flexibility is the key, in the last three months all the ip and server names have changed more than once so need to be as flexible as possible.

View 2 Replies View Related

SQL SP Takes Long Time To Run From VB.NET App

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

Long Time Database Processing In ASP.NET ?

Aug 2, 2007

 I made a website in ASP.net and using sql server 2005 as database. There is sometime processing data that need long time processing ( about 20 minutes ) and big data. It works fine in dev box, but when I place on shared hosting, and some people access it  crashed. The website can not be accessed. Hosting support told me maybe I need to reprogram my code. So anybody has solution for this problem ? Should I create new thread ?  

View 3 Replies View Related

Long Time To Run A Stored Procedure

Feb 18, 2000

Hi There,
We have developed a application in VB and connected to SQL Server 6.5, we have some stored procedures where it brings the data from SQL Server 6.5, this application is running since some months, when we run this application it usually take only one minute to generate the report but since couple of days it is taking 25 Minutes to generate the report, even when I run that stored procedure at backend in Query analyzer at Server it is taking 15-20 Minutes to give the result.
please can any one help in identifying the problem, What all the things I need to check to identify it.
Give me the solution.

Thanks in Advance,

Bye,
Madhuker.

View 1 Replies View Related

Help: Scheduled Job Running For Long Time

Jun 5, 2001

Hi there!,

Problem: I schedule a job that calls a stored procedure which loads around 1.5 million records. The Job takes 19 hrs to complete. However, if i run that stored procedure manually in Query Analyser it takes only 45 minutes..

Did anyone faced this problem? Is this known problem..Any suggestions/recommendations?

thank you.
regards,
Nana

View 6 Replies View Related

Database Search Time -too Long

Jun 2, 2008

I had a database of electronic resources which had 28000 records earlies and was working fine. Now we have added a whole bunch to make it 800K records which has increased the search time to 14-22 seconds which is not acceptable. I have all the tables indexed.

Please help me how to solve this problem. Let me know what other information I should put up here to make my problem undestandable.
Thanks in advance,
Archana

View 2 Replies View Related

Long Time To Establish A Connection

Jul 23, 2005

When I login using QA to my SQL Server database, it takes 15-20 secondsto establish a connection and open a query window. Drilling into adatabase via Enterprise Manager is similar. Once the connection isestablished, the server runs plenty fast however.Can someone tell me why it could take a long time for a connection tobe established?This behavior occurs when I am local on the box.Thanks,John

View 1 Replies View Related

Inserting Nothing Takes A Long Time

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

ExecuteQuery Taking A Long Time

Sep 4, 2007

Hi:

I have a query which returns approximately 50000 records, I am using a linked server to connect to two databases and retrieve data. For some reason it is taking a liitle more than hour to execute the query, but on MS Sql Server query window it comes after few minutes but the query runs for a long time.

How can expediate my query execution process.

Environment details

Database: MS Sql Server 64bit 2005
MS Sql jar file: sqljdbc_1.2.jar
OS: Windows both server and client.

Connect String in java code:

jdbcqlserver://sample_server:1433;databaseName=sample_db;user=admin_user;password=admin_pwd

and use PreparedStatement and ResultSet.

Regards
Arup

View 2 Replies View Related

Long Execution Time When Using Parameter

Dec 20, 2007

Hi all,

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



SELECT *

FROM dbo.ICParameter

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

ORDER BY LogTime


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

Could anyone shed some light on this?

Thanks in advance.

View 19 Replies View Related

Restore Taking Very Long Time

May 31, 2007

i have sql 2000 db of about 120 GB. its taking about 10 -12 hours to restore on the same disk as new database.



server configuration is good.



when i try to restore another db of about 10 GB size, its restoring in about 5 minutes.

View 2 Replies View Related

Why Insert Takes A Long Time?

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

Queries Take A Long Time After Sp2 Installation

Jun 5, 2007

Has anyone had thsi issue, if so what was the fix?



We installed service pack2, the day after 2 of our production jobs started taking a long time to complete and causing a ton of blocking.



it went from running in 2 minutes to now taking 3 hours and 29 minutes to run. Can someone help?

View 5 Replies View Related

Package Run Takes A Long.......... Time!

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

SSIS Long Validation Time

Feb 1, 2008



I would like to know how to disable valiation of SSIS when the SSIS runs in server.
The SSIS takes about 20% of time to validate the environment.

Are there any configuration in SSIS to tell SSIS the environment is stable and no validation is required?

Thanks.

View 4 Replies View Related

C# Hanging For A Very Long Time When Working With SQL

Apr 6, 2007

I am designing a project using C#, SQL Express and VSTO.



Every time I change something in the database XSD display i.e add a query or create a new table adapter or configure an existing query, or just move the position of an existing table adapter, Visual Studio locks intself into a never ending loop with 100% processor activity (or 50% with dual processors). It stays in this state for about 4-5 hours and then come back to life.



Is there an option that I need to disable/enable to stop the program from doing this. I will appreciate your help.



Thanks,

Hammad

View 1 Replies View Related

Help-take A Long Time Run This Stored Procedure 15 Second

May 3, 2008

help-take a long time run this stored procedure 15 second



Code Snippet

DECLARE @WantedDate SMALLDATETIME, -- Should be a parameter for SP

@BaseDate SMALLDATETIME,@NumDays TINYINT,@myNum TINYINT

set @myNum=4

SELECT @WantedDate = CAST(CAST(YEAR(GETDATE()) AS nvarchar)+ '-' +CAST(1 AS nvarchar)+ '-' +CAST(@myNum AS nvarchar) AS SMALLDATETIME), -- User supplied parameter value

@BaseDate = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @WantedDate), '19000101'),

@NumDays = DATEDIFF(DAY, @BaseDate, DATEADD(MONTH, 1, @BaseDate))

select @WantedDate

IF @NumDays = 28

BEGIN

SELECT dbo.v_hasot_all.empID, dbo.v_hasot_all.Fname, dbo.v_hasot_all.day1A, dbo.v_hasot_all.nameday, dbo.tb_mis_hsaot.mishs_txt, CONVERT(datetime,

CAST(dbo.v_hasot_all.nameday AS nvarchar) + '/' + CAST(@myNum AS nvarchar) + '/' + CAST(YEAR(GETDATE()) AS nvarchar), 103) AS date_mis

FROM dbo.v_hasot_all INNER JOIN

dbo.tb_mis_hsaot ON dbo.v_hasot_all.day1A = dbo.tb_mis_hsaot.mishs_int

WHERE (NOT (dbo.v_hasot_all.nameday IN (29, 30, 31)))



END

ELSE IF @Numdays = 29

BEGIN

SELECT ...............

WHERE (NOT (dbo.v_hasot_all.nameday IN ( 30, 31)))



END

ELSE IF @Numdays = 30

BEGIN

SELECT ......................

WHERE (NOT (dbo.v_hasot_all.nameday IN (31)))



END

ELSE IF @Numdays = 31

BEGIN

SELECT dbo.v_hasot_all.empID, dbo.v_hasot_all.Fname, dbo.v_hasot_all.day1A, dbo.v_hasot_all.nameday, dbo.tb_mis_hsaot.mishs_txt, CONVERT(datetime,

CAST(dbo.v_hasot_all.nameday AS nvarchar) + '/' + CAST(@myNum AS nvarchar) + '/' + CAST(YEAR(GETDATE()) AS nvarchar), 103) AS date_mis

FROM dbo.v_hasot_all INNER JOIN

dbo.tb_mis_hsaot ON dbo.v_hasot_all.day1A = dbo.tb_mis_hsaot.mishs_int

END
and whan i run my code separately it run fast
i think it is the ELSE IF @Numdays

TNX

View 5 Replies View Related

MSSQLSeverOLAPServieces Is Taking A Long Time To Start

Oct 13, 2003

Sometime is necessary to stop MSSQLSeverOLAPServieces to do a full backup in my OLAP Server disks. After backup had finished and I tried to star MSSQLSeverOLAPServieces but it takes almost 30 minutes to the services starts.
What can it be causing that?

Paulo

View 5 Replies View Related

Stored Proc Taking A Very Long Time

Aug 2, 1999

I have a stored procedure that normally takes about 5 hours to complete:
DELETE tblX WHERE PROC_DT < dateadd(day, -93 , getdate())

tblX has about 55 million records and has an index on PROC_DT.

I have this running as a scheduled task. Over the weekend, the task executed and it is still running 56+ hours later. Does anybody have any ideas as to where I should look for the problem? I am afraid to kill the process because of the rollback time.

View 4 Replies View Related

Update Taking Long Time In 2000 Then SQL 7.0

Mar 7, 2002

Hi,
I have a table with 48 million rows,when i executed following update query it is taking 10 HOURS in SQL SERVER 2000 with SP1.
Where as when i executed same query in SQL SERVER7.0 with same table then it is taking 13 MINUTES. Comming to Machine...SQL 2000 Server has more processors and greater memory than SQL 7.0 m/c.
It looks strange but this is true.Does any one faced such problem..is there any bug in SQL 2000?????

Here is Query::

update cus_pay_jan_dist set univ_regdate = b.dayid
from cus_pay_jan_dist a with (nolock), tm_dayids b with (nolock)
where a.univ_regdate = b.dayidnum and a.univ_regdate like '2001%'


Thanks
Ananth

View 6 Replies View Related

MS Access && MS SQL Server: Long Time Answers

Jan 12, 2004

I use MS ACCESS and tables linked to MS SQL SERVER tables.
There are indexes defined (they are visible in MS ACCESS too).
Sometimes DLOOKUP functions are taking long time.
Why?

View 3 Replies View Related

Sleeping Processes Take A Long Time To Kill-

Jul 23, 2005

Hello all. I have many connections that are sleeping. I can rightclick, kill, up to 2 at a time. It takes about 20 seconds to kill it.Is that normal? I tried to make a stored procedure to kill them but itate all the power of the server, (all users yelled at me at the sametime :o) )Is this length of time normal for killing these processes?ThanksJeff

View 6 Replies View Related

Update Takes Long Time To Complete!?

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

Taking Too Long Time For Insert Statement

Sep 19, 2007

Hi All,

Scenario:

There are two applications running on different server say ServerA and ServerB. Both applications are using same database server SQL Server 2005 say ServerB. Called the application as ApplicationA and ApplicationB with respect to Server names

It means for ServerA the database is remote and for ServerB, database is local.

Both the applications are Java application and using datasource to connect to the database. The driver used are SQL Server 2000 driver (which includes 3 jars). This can be a question that why 2000 driver is used for 2005. The reason is, application on ServerA is getting error while using SQL Server 2005 as Driver not proper.

Problem Area:

When ApplicationB (local to database) is doing some DB operations (which includes select and then batch insert), ApplicationA (remote) is trying to insert a record which is taking too long time (around 40 sec.). This is causing timed out in ApplicationA.

ApplicationA is inserting the data into the same table from where ApplicationB is selecting the data.

Any help????

Cheers
Nitin

View 2 Replies View Related

Excel Export Takes Long Time

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







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