SQL Query Load Times

Feb 10, 2006

I have an VB.NET web app which performs a fairly complicated SQL query. It seems in the morning, the 1st time the page is loaded (and query executed) it takes up to 10-15 seconds to complete loading. Sometimes it even times out. However anytime after that, the page loads up (even from another computer) in about 4-5 seconds. Can someone explain the reason for this and how I might fix the load times in the morning?

Thanks

View 1 Replies


ADVERTISEMENT

Performance - Slow Load Times For SSIS Packages

Feb 13, 2006

Is there any information around what the SSIS packages are doing in the first 5-10 seconds of execution, and ways to speed this process up?

View 3 Replies View Related

Query - Between To Dates And Between To Times

Jul 24, 2001

Hi there, I'm a little bit stumped on this one.

I have a column in a table that records when the date and time of an event took place.

Table Name: Chronicle
Column Name: Created (of type DateTime)

I would like to select the Chronicle records that are between two dates.
(e.g. 1 May 2001 and 20 May 2001)
And I would like to select those records that are between two times.
(e.g. 6:00am and 1:00pm)

Does anyone know how to do this or have any pointers for me?
I can see it would be easier if I had the date in one column and the time in the other.
Can it be done without doing that?

cheers,
Hamish

Hamish Norton
hamish@liftrider.com

View 1 Replies View Related

Excessive Query Execution Times

Mar 16, 1999

We recently upgraded from SQL 6.5 to SQL 7. I have a few .sql files that were each running around 5 - 8 minutes under 6.5. These same files now each take over 30 minutes to run. Has anybody had problems with their queries taking longer to run under 7.0? These files are quite large and are comprised of 3 - 4 batches with several queries in each batch. If anybody has any thoughts on the cause please let me know.

Thanks in advance.

View 1 Replies View Related

Sql Query Fails At Specific Times

Dec 11, 2003

Hi,

I'm in an unfortunate situation. We are posting information using a stored procedure to an outside SQL server connected to through a System DSN on our server (win 2003 server) using php's ODBC functions (we never had any luck connecting directly to the SQL server using php's mssql functions).

Everything is working fine, we can connect, send querys, etc ... but between 1am and 10am we recieve errors when trying to execute queries (though we can connect fine).


Whoops ... forgot to get the error returned before it turned 10am ... I can post it tomorrow


I think the database is being locked, but unfortunately I know very little about MS SQL server

The people who's database we are connecting to are not being helpful ... I was hoping I could get some suggestions on what would be the cause.

Anything you can suggest would be a huge help! Thanks!
- Joe

View 2 Replies View Related

Query Analyzer Cannot Connect, Times Out

Nov 19, 2004

We are having a problem with Query Analyzer not connecting to SQL Server anymore. Sometimes it will, sometimes it won't. Sometimes when it does, then you click on the databases drop-down, it may take a long time to return. Likewise, it may take a long time to open the object browser, or it may open without the database info but with just the "Common Objects" info.

All of this used to work just fine.

HELP

View 2 Replies View Related

Baffling Query Return Times

Feb 11, 2008

Simple version first:

The basic question is:

what would cause? this query takes minutes or times out:

select xx,xx,xx,xx
from view_xxx
where field in
(

select distinct xxx from table inner join table.....
)


the inner query
select distinct xxx from table inner join table..... takes less than a second

then if I take the results of that query and do this:

select xx,xx,xx,xx
from view_xxx
where field in
(

52193,23523,25233... and so on
)

this later query takes 2 seconds... What is the difference? For more details see the detailed version.






-Robert


now a more details version:



On a SQL2000 machine...

What would cause this query to take forever, or even timeout:


Select distinct VTR.Control_ID from View_ToolResults VTR

Where VTR.Control_ID in

(

------ NOTE INNER QUERY TAKES LESS THAN 1 SECOND
SELECT ORD.CONTROL_ID

FROM Orders ORD

INNER JOIN Providers PR ON PR.SUBJECT_ID = ORD.SUBJECT_ID and PR.SV_OFFICE_ID = ORD.SV_OFFICE_ID and PR.Source_ID = ORD.Source_ID

INNER JOIN VisitTypes VT ON ORD.VISIT_TYPE_ID = VT.VISIT_TYPE_ID and ORD.Source_ID = VT.Source_ID

INNER JOIN HealthPlans HP ON HP.MARKET_ID = ORD.MARKET_ID and HP.Source_ID = ORD.Source_ID

INNER JOIN HPGroups HPG ON HP.MARKET_ID = HPG.MARKET_ID AND HP.SOURCE_ID = HPG.SOURCE_ID WHERE (ORD.Status is not null AND ORD.Status <>'E')

AND HPG.GR_ID in

(

SELECT GR_ID from UserHPGroups Where User_ID= 7

)

AND ORD.RELEASED_DATE between '01/01/2001' and '02/11/2008'

and ORD.RECEIVED_DATE between '07/01/2007' and

'12/31/2007' and ORD.MARKET_ID= 2699431


)

as mentioned above the inner query takes less than 1 second. So if I take the results from that query and do a select like this:


Select distinct VTR.Control_ID from View_ToolResults VTR

Where VTR.Control_ID in
(55836
,55631
,55331
,50992
,51101
,51196
,51500
,52269
,52366
,52384
,52429
,52518
,53117
,53993
,53994
,53995
,54027
,54128
,54192
,54264
,54291
,54292
,54324
,54379
,54578
,54706
,54917
,52622
,54354
,55965
,55410
,55461)

It takes 2 seconds.....

WHAT!!!!!>???!?!?! i don't understand... what is the difference?

The field in all cases is declared as Numeric(10,0)... I've tried to convert to int. I've tried to do temp tables... it doesn't help.

View 10 Replies View Related

SQL Datasource Running The Same Query Mutliple Times

Jan 22, 2008

I have multiple SQLDatasources on multiple pages. When I call the Select command for the datasource the query is run multiple times. I was wondering if anyone had any problems like this. The data is being read into listboxes. If you need any more info or have any specific questions, feel free to ask.

View 6 Replies View Related

Employee Attendance With In/out Times (was Query Help Needed...)

Apr 8, 2006

Dear Friends,
I am in problem & have to solve one query.
I have a one table with the employee time in & time out data, employee can go out & come in fequently in a day.

I want to know that how much time every emp have attend in the company per day.

Kindly, do reply as soon as possible.

I am enclosing data defination in txt file along with the data in the MS Excel file.

Thanx ....

View 7 Replies View Related

How To Run Insert Query For Required No.of Times With Different Parameters...?

Apr 22, 2008

Hi All,

I have two tables in my database.
I want to insert date and no.of hours worked on that day for a particular project.
So in a week if end user enters 7 dates and hrs for each day......
i have to insert those values into the table against one project only.
Can any one please help me out how to run insert query for 7 times (in a week) with different parameters

Thanks,
Praveen

View 5 Replies View Related

Transact SQL :: Running Query For Multiple Times Through The Day?

Jun 12, 2015

I want  the below query to run 24 hours ..once the insert is complete, run again , so on for 24 hours .

there is a way to run every second in as job but i want to run only after run complete ..is there a way to run the query after every complete run ? and keep in job 
 
INSERT INTO [dbo].[Audit_Active]
([SPID],[LoginName],[HostName],[ProgramName],[Command],[LastQuery],[DBName],[ServerName])
 SELECT   
--DISTINCT  
p.SPID,
p.LogiName,
p.HostName,   

[Code] ....

View 8 Replies View Related

SQL Server 2008 :: Iterate Query Using A Loop As Many As 5 Times Max?

Mar 20, 2015

If exists (select fieldID from #tmploginfo where status <> 0
group by fieldID
having count(*) > 0)
begin
backup log rdb to disk = N'C:
db1.trn'
End

I want to iterate this query using a loop as many as 5 times max.

View 3 Replies View Related

InSQL 7.1 - Count How Many Times A Tag Equals 1 Over A Specified Time Period Query....

Aug 2, 2007

Need help to create a query to count how many times over a specified time the tag(s) equal the value of 1.

Thanks

Gary

View 3 Replies View Related

SQL Server 2008 :: Left Joins And Query Plan Compile Times

Mar 8, 2015

We have a view with many left joins. The original creators of this view might have been lazy or sloppy, I don't know. I have rewritten the query to proper inner joins where required and also nested left joins.

So rather then the following exemplary fragment

select <many items>
from A
left join B on B.id_A = A.id
left join C on C.id_B = B.idthis now looks like
select <many items>
from A
left join (B
join C on C.id_B = B.id
) on B.id_A = A.id

Compilation time of the original view was 18s, of the new rewritten view 4s. The performance of execution is also better (not counting the compile of course). The results of the query are identical. There are about 30 left joins in the original view.

I can imagine that the optimizer has difficulty with all these left joins. But 14s is quite a big difference. I haven't looked into detail in the execution plans yet. I noticed that in both cases the Reason for Early Termination of Statement Optimization was Time Out.

View 9 Replies View Related

Same Statement Executes 10 Times Faster As Raw Sql In Query Analyzer Then In A Stored Proc

Aug 15, 2007



Hi,


I apologize for the long post but I am trying to give as much information as I can about the steps I've taken to troubleshoot this.


We have a stored procedure that builds a sql statement and executes it using the Execute command. When I execute the stored procedure through query analyzer it takes close to 5 seconds to execute. When I print out the exact same statement and execute it directly in query analyzer as "raw sql", it takes 0.5 seconds - meaning it takes 10 times longer for the code to execute in the stored proc. I altered the stored proc to execute the printed sql instead of building but it still takes the full 5 seconds and there were no changes in the execution plan. This makes me confident that the issue is not caused by the dynamic sql. I've used with recompile to make sure that the stored procedure caches the most recent execution plan. When I compare the execution plans, the stored proc uses a nested loop whereas the raw sql statement uses a hash join. Seeing that, I added the hash hint to the stored proc and doing so brought down the execution time down from 5 secs to 2 secs but still the raw sql statement uses a clustered index whereas the stored proc uses a non-clustered index and that makes the statement 4 times slower. This proves how efficient clustered indexes are over non-clustered ones, but it doesn't help me since, as far as I know, I can't force SQL Server to use the clustered index.


Does anyone know why sql server is generating such an inefficient execution plan for the stored proc compared to the execution plan that it generates when executing the raw sql statement? The only thing I can think of is that some stats are not updated and that somehow throws off the stored proc. But then again, shouldn't it affect the raw sql statement?


Thank you,


Michael Tzoanos

View 4 Replies View Related

Data Load Query

Sep 21, 2005

Hi,I'm extracting data from a mainframe application with a view to loadingit into a MS SQL database. I'm trying to determine the most efficientway to format the mainframe extract file to make loading into thedatabase easier.The problem I have is that the existing record structure includes anarray that can vary between 1 to 50. If I include this array in asingle record the table I use to import the data would need 50 columnsthough not all these would be populated. There is a field in the recordto identify how many occurances of the array there are.Current Record Structure :Account NumberAccount NameOther Account DetailsTotalNumberOfArrayFieldsPopulatedArray :Value1Value2Value3....up to Value50 (if required)i.e.12344,Mr Agent,$29.95,2,BX123,BX12412345,Mr Jones,$14.95,3,XX123,XX124,XX12512345,Mr Jones,$14.00,1,XY12312345,Mr Jones,$15.95,2,XZ124,XZ12512346,Mr Smith,$19.95,3,AX123,AX124,AX12512346,Mr Smith,$19.00,1,BY12312347,Mr Acant,$99.95,7,CX123,CX124,CX125,CX126,CX127,CX128 ,CX129There may be up to 3 records created for each Account Number withdifferent values in the array fields.Am I better to break this file into two files .. one with the corecustomer information and a second file with a row for each array valuewhich has a link to the customer information file.OrIs there a way to efficiently process the original file once it isloaded into the staging tables in the database ?i.e.File 1 - Core Customer Information====================================Current Record Structure :Record NumberAccount NumberAccount NameOther Account DetailsTotalNumberOfArrayFieldsPopulatedFile 2 - Array Information====================================Record NumberArray :Value1Value2Value3....up to Value50 (if required)File 1========================12344,Mr Agent,$29.95,212345,Mr Jones,$14.95,312345,Mr Jones,$14.00,112345,Mr Jones,$15.95,212346,Mr Smith,$19.95,312346,Mr Smith,$19.00,112347,Mr Acant,$99.95,7File 2========================12344,BX12312344,BX12412345,XX12312345,XX12412345,XX12512345,XY12312345,XZ12412345,XZ12512346,AX12312346,AX12412346,AX12512346,BY12312347,CX12312347,CX12412347,CX12512347,CX12612347,CX12712347,CX12812347,CX129At times the individual array values will be used for look ups thoughessentially the Customer Information record will be the primary lookupdata.I'm leaning toward changing my COBOL code and creating the 2nd outputunless someone can suggest a simple way to process the information onceloaded into the table.Any help that could be suggested would be greatly appreciated.

View 1 Replies View Related

Query Load Balancing

Apr 10, 2006

Hi,

We have a massive database that users run complex queries on. The queries are never the same so caching cannot be used too much effect. When many queries are run at one time by the various users the system slows down.

What we are trying to achieve is maybe have a primary SQL Server 2005 machine and multiple (4-5) SQL Server 2005 secondary machines. The primary machine would get the query and would decide based on loads as to which secondary machine would fulfill the query.

We are not worried about failover and clustering and all that fun stuff. Our primary concern is to achieve the quickest execution of the query. We were wondering if SQL Server 2005 has any feature that enables query load balancing of some kind.

Thanks

View 1 Replies View Related

SQL 2012 :: Incremental Load Of CDC Using Query

Jun 6, 2014

I required query for Incremental Load of CDC using Query

View 1 Replies View Related

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 View Related

Why Is DTS Faster Than A Query To Load A Remote Table?

Apr 23, 2007

Hello

DTS is notoriously faster than making the following statement:

insert into synonym_MyRemoteTable
select * from myLocalTable

Why is it so?

Thanks a lot.

View 6 Replies View Related

Linked Server Query - Timeout Error Under Load

Apr 23, 2002

Hello,

I am researching alternatives to a stored procedure that uses a linked server query that my ASP page uses to retrieve records to the page.

Should I:

Use replication (Data is constantly updated, so a snapshot won't do. Merge replication?)

Break up the stored procedure into subqueries?

Use a view on the remote server instead of the actual tables?

I am trying to reduce the amount of table scans, what is the best way to do this?

Here is the stored procedure I am trying to tune:
(@startDate and @endDate are parameters passed from the web page):

CREATE PROCEDURE dbo.spELRMCcardXtionByDate @dcid nvarchar(255), @startDate datetime, @endDate datetime
AS
SELECT Store.[Str#], Store.[Dcid#], E.card_number, E.program_number
, E.start_date, E.end_date, E.card_number, E.event_number
, E.status, E.budget, E.scheduled_date, P.tx_time, P.purchase_amount
, L.merchant_name
FROM (Store INNER JOIN Event E ON Store.[DemoID#] = E.event_number)
LEFT JOIN (Location L RIGHT JOIN POS_TX P ON L.location_number = P.location_number)
ON E.event_number = P.event_number
WHERE (((Store.[Dcid#])= @dcid)) AND E.scheduled_date BETWEEN @startDate AND @endDate
ORDER BY Store.[Str#]
GO

Any suggestions on my sp or anything else that can implement would be greatly appreciated.

TIA,

Bruce Johnston
Programmer/Analyst

View 1 Replies View Related

Not Able To Load The Application In Case Web Farm Garden When We Load Data Through Background Thread.

Dec 14, 2007

Hi,

Here I will describe my problem.
1. We are loading large amount of data from database on background thread which is starting on Application_start event in global.aspx.cs file.The data is later cached for subsquent request to improve the performance.
2. Now when we put the application on web farm garden, it is not able to load the application.
3. We are sending the request the servers through Router kind of application.
4 This application is working fine on single server enviornment.

Please help us.

Ajay Kumar Dwivedi

View 1 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

Load A SSIS Package Via Web Service: The Package Failed To Load Due To Error 0xC0011008 Error Loading From XML.WHAT IS THAT?

May 19, 2006

Hello,

I have a big problem and i'm not able to find any hint on the Network.

I have a window2000 pc, VS2005,II5 and SQLServer 2005(dev edition)

I created an SSIS Package (query to DB and the result is loaded into an Excel file) that works fine.

I imported the dtsx file inside my "Stored Packages".

I would like to load and run the package programmatically on a Remote Scenario using the web services.

I created a solution with web service and web page that invoke the web service.

When my code execute:
Microsoft.SqlServer.Dts.Runtime.Application.LoadFromDtsServer(packagePath, ".", Nothing)

I got the Error:
Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: The package failed to load due to error 0xC0011008 "Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.". This occurs when CPackage::LoadFromXML fails.

The error message doesn't help so much and there is nothing on the www to give me and advice....

Is it a SSIS problem???

Thank you for any help!!

Marina B.



View 10 Replies View Related

Times

Feb 12, 2008

I have set up an sql server which has a column called times. This column is to hold race times however when i enter times such as 22:20:89 with the 89 being milliseconds i am not bale to input this it will only allow me to put times in where milliseconds are below 60. This is a problem especially for the shorter races where half a second is a big difference.
 Thanks Mike

View 2 Replies View Related

EM Times Out

Apr 29, 2002

When trying to connect to a server on a slow connection (satelite), Enterprise Manager times out. I am able to connect with Query Analyzer. Tried to change 'remote login timeout', but that did'nt help, EM seems to timeout long before this value is reached. Any ideas ?

Brgds,
Odd Oren

View 1 Replies View Related

Add Times

Jun 11, 2007

SQL Statement.

Adding times together.



I am trying to add a list of times together retrieved from my database.

They are from seperate records in the same table.



They are in a date format e.g. 01-Jan-2006 03:45:00, what I want to do is ignore the 01-Jan-2006 part as well as the :00 AM part and focus on the hours and minutes. I wish to add these up e.g. 03:45 + 04:45 = 08:30. Is there an easy way of doing this?



Below is the SQL statement of getting times from my database if its any help.




SQL StatementSELECT LENGTH_OF_VISIT FROM NOV.MAIN;




Returned Results

30-Apr-2007 02:00:00 AM

30-Apr-2007 01:00:00 AM

01-Jan-2006 02:30:00 PM

01-Jan-2006 03:10:00 PM

30-Apr-2007 01:00:00 AM

View 1 Replies View Related

Selecting Between Two Times

Feb 19, 2007

Dear All,
 I need to select records between two datetimes.
My Database Structure and sample values are as below
Fromdate                                                        ToDate
2007-02-20 09:00:00.000                                 2007-02-20 12:00:00.0002007-02-20 08:00:00.000                                 2007-02-20 12:00:00.0002007-02-20 06:00:00.000                                 2007-02-20 13:00:00.000
Query i used
select * from tablename where ((fromdate between Convert(datetime,'2007-19-2 10:00',103) and Convert(datetime,'2007-19-2 11:00',103)) or (todate between Convert(datetime,'2007-19-2 10:00',103) and Convert(datetime,'2007-19-2 11:00',103))) 
My query is not returning values when i am querying between '2007-19-2 10:00' and '2007-19-2 11:00'
Please help
Thanks
 
 

View 5 Replies View Related

SQL Transaction Times Out

Dec 20, 2007

Hello everyone,
I have an odd issue and I can't seem to figure it out.  I am using SQL Server 2005 and I am using Transactions in combination with Try/Catch blocks.  My query (executing the stored procedure) never seems to finish the query in query analyzer.  I have removed all statements that exectute or select, etc upon the database except for a  simple Select 'test' as test so I would have something in my Begin/End blocks. Below is my stored proc:
ALTER PROCEDURE [dbo].[ar_mainCheckRecon]
@tripID int,
@payment money,
@adjustment money,
@serviceType tinyint,
@checkTpId int,
@checkTlId int,
@checkNbr varchar(20)
AS
BEGINSET NOCOUNT ON;
BEGIN TRY
BEGIN TRAN select 'test' as test
 
 
END TRY
BEGIN CATCH
RAISERROR('Error commiting transaction', 16,1)
 ROLLBACK TRAN
SELECT '0' as success
END CATCH
 
COMMIT TRANSELECT '0' as errorCode
 
END
I call upon the sproc by running this line in query analyzer:
exec ar_mainCheckRecon 433636, 0, 500, 0, 500, 0, 23
Is there something I am overlooking as to why the query keeps running and never stops? My current execution time is at 3 mins accoring to query analyzer!
Thanks,John
 

View 3 Replies View Related

Inserting Just Times

Jan 20, 2008

When using a SQL Server DateTime column to store just times, e.g. '6:00 PM' I've noticed some weird inconsistency.
If I insert a time in a SQL statement, such as... INSERT INTO myTable (StartTime) VALUES ('6:00 PM')
It winds up going in as  '1/1/1900 6:00 PM'.  This is okay.  This is in fact what I want (unless of course the base day for sql server is in fact 12/30/1899, which is my next question).
If, on the other hand, I insert a time value by hand, using SQL Server Management Studio, then after I insert '6:00 PM' it winds up as '<Today's Date> 6:00 PM', i.e. '1/20/2008 6:00 PM'.
Also, if I insert it using a SQL Parameter, such as...
da.InsertCommand.Parameters.Add("@StartTime", SqlDbType.DateTime, 8, "StartTime")
...Then, if StartTime is 6:00 PM again, it also winds up going in as '<Today's Date> 6:00 PM'. 
Is there a way to control this behavior (other than to programmatically append a base date to the time I'm inserting)? 
Finally, what is the base day for SQL Server 2005?  Is it 1/1/1900 or 12/30/1899?  I think I've seen both. 
Thanks!
Aaron

View 3 Replies View Related

Dates Without Times

Jul 27, 1998

Anyone know of an easy way to work with datetime columns ignoring the time part.

I want to "group by" the date so it is vital that the time part goes.

But I still want to work with the resulting date as a date (i.e. use date functions) so I would prefer to keep the datetime data type.

View 1 Replies View Related

Name Lookup 5 Times

Dec 17, 2004

Hi

I'm new in SQL. I have a big problem

Database: MSSQL 2000

I have two tables: (results) one with numeric results and one with names (importance_scale)

SELECT * FROM results

name i1 i2 i3 i4 i5
---- ---- ---- ---- ----
john 2 1 2 2 2
anna 0 2 2 2 2
phil 2 2 2 2 2
dave 1 0 2 2 2

SELECT * FROM importance_scale

imp_value imp_name
--------- ------------
2 very important
1 important
0 not important

i1,i2 etc. value it is a imp_value from table 'importance_scale'

I would like to make report which will show all results in this format (in example only first record):
name i1 i2 i3 i4 i5 etc.
----- --- --- --- --- ---
john very important important very important very important very important etc.

I was thinking about using CASE I don't want use cursor. What is the best way?

Thanks for your help

View 2 Replies View Related

Times In Sql Server

Aug 29, 2004

I want to enter times for running event performances (like 10:26.35 for a person's 2-mile time, for instance). I am using sql server 2000. This is an ongoing concern for me since I do a lot of work with running events. Can someone give me some direction as to how I can best deal with this?

The datetime data types do a lot of converting to 'time-of-day' and that is not what I want. For instance, if I enter a 2-mile time of 10:26.35 (a pretty typical 2-mile time) my db converts it to 10:26:35 AM.

If I use varchar data type there is a ton of code to write when I want to sort the data.

Thanks!

View 3 Replies View Related







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