.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Make a T-SQL Query Sleep for a certain amount of time

Posted By: Syed Shakeer Hussain     Posted Date: September 14, 2010    Points: 2   Category :Sql Server
At times, in test scenarios, you may want to emulate that a transaction or a T-SQL query takes 'x' amount of time. Well that is possible (although not with precision) in SQL Server (2005/2008) using the WAITFOR command.

View Complete Post

More Related Resource Links

Slow page load during a list query one time during the day


We have a monitoring tool set to check to see if the home pages for our 3 web apps load in under 60 seconds every 10 minutes.  All 3 web apps load in under 3 seconds on every 10 minute check except for one exception.  One check every day one of the web app's home page takes longer than 60 seconds to load.  This happens at 11:45PM when there is very little user usage on the environment.  The characteristics of this page are as follows: The only thing on the page is a list view web part which was added by the browser.  The page has not been customized with SPD or code at all.  The list that it pulls is a simple links list that has 281 items on it.  The view pulls all 281 items and displays them in sets of 100.  I cannot find any associated event in the server events and/or SharePoint ULS logs nor are there any daily sharepoint timer jobs running at that time.  Our full index happens at midnight with incremental happening hourly.  Our enviroment is 2 WFEs, 1 App/indexer and we have a separate SQL cluster backend.  

Could someone lead me possibly in the direction I should take next in my troubleshooting?   

WCF - First call to Callback instance from Server takes considerable time to make it to the client.

Hello. There's a performance problem we are facing that we are unable to fix using DuplexChannels. The first call made by the client to the server reaches the server instantly. The first call made by the server to the first client using a Callback proxy will take several seconds to reach the client. All subsequent calls to the client (as well as other clients) will reach and execute instantly. The client connects to the Server using a proxy to IServiceProvision, supplies an instance to an implementation of IClientCallback and calls IServiceProvision.Start(). The server instantly processes the call and makes an asynchronous call to IClientCallback.DoWork(), which takes several seconds to enter the client's DoWork() implementation. The remainder of the execution then runs smoothly. Also, the server will experience such a slowdown only for the very first call of the first client that connects to it. Is it normal for the Callback interface to take so much time to warmup, only on the first call to the first client? Below is the full source code. Thanks for any information you may provide. Cheers Mike Shared Class Library using System.ServiceModel; namespace Test { [ServiceContract(Namespace = "http://test/IService", CallbackContract = typeof(IClientCallback))] public interface IServiceProvision { [OperationContract] void Start(); [OperationContract] void

Query Time out error?

Iam getting the querey time out error, when processing dimension in SSAS? The error Details is: OLE DB or ODBC error: Query timeout expired; HYT00; Unknown token received from SQL Server; HY000.\ can any one help me on this....how to resolve this?

SQLDataAdapter/SQLDataReader takes lot of time for executing a query

All, I have a webpage with 6 drop down lists. User can start with selecting any drop down list first, and then proceed in any order.  1. Assume when the 1st dropdown is selected first, rest 5 drop downs (except 1st) are filtered/updated based on the selected value in the 1st dropdown. 2. Assume user selects the 4th dropdown second, rest 5 drop downs (except 4th) will be filtered/updated but the condition would be based on both 1st drop down and 4th drop down. But while filtering the first drop down, only the 4th drop down is used as filter (self-filter will be ignored). The code works fine but I see a very bad performance hit in time when the user selects the fourth dropdown after selecting three drop downs. I tried using both SQLDataAdapter and SQLDataReader but neither used to solve the issue. The code takes a lot of time on one line in either of the code (which is highlighted below). When I run the query in SQLServer Mgmt Studio, the query completes in a few seconds.Using SQLDataReader: SqlConnection conn = new SqlConnection(CONNECTION); SqlCommand ObjCmd = new SqlCommand(query, conn); ObjCmd.CommandTimeout = 600; conn.Open(); SqlDataReader daSelTools = ObjCmd.ExecuteReader(); //This line takes a lot of time ddl.Items.Clear(); // Call Read before accessing data. while (daSelTools.Read()) { ddl.Items.Add(new ListItem(String.Format("{0}",daSelTools[0])));

Get Query execution time

I need to find out the query execution time from the front end .Where should I insert the code for that.Please help.. I am using the bleow query: OracleConnection con = new OracleConnection(ConnStr); con.Open();  OracleCommand cmd = new OracleCommand("Stored_Proc",con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(); .................... ................ OracleDataAdapter oda = new OracleDataAdapter (cmd);  

MDX Query Measure with time period

Hi! How to set up the period in MDX? I need the measure what would be seted up by default 4, 29… days from today. For axample Amount from current day (02.09.2010) till 4 days from today is 06.09.2010. As Result I have to get a report: Buy form Vendor Amount today 02.09.2010 Amount +4days 02-06.09.2010 Amount +29days 02.09-30.09.2010 A 10000 250000 333333 B 150000 222222 555555 C 666666 444444 1222222 Sincerely, Milena

i can not make query for any information in arabic language

HI,   i work on Sql Developer Edition when i select for any Company name in arabic it does not get any data ,why? although my data are inserted by arabic language,please help me asap,thank youand my code like this use   todayselect CatID from dbo.tbl_Categories where CatName like '%???????%' 

Why the Same sql query taking much more time in other server.

Hi All,   I have a sql query.. which is running absoulutely fine in the production environment. I restored the Prod DB to another server.. same hardware and software configuration as Prod server. But in that server the same query taking ____ lot of time.. What could be the reason ??SD

Convert the Sql query to LINQ .. can anyone please help me I need it ASAP as no time to learn and im

 SELECT DISTINCT HP.PartNumber, HP.MIRevision, HPEXData.InstructionImage, EXAttr.PlacementImage from dbo.HardwareParts HP   INNER JOIN dbo.HardwarePartEXData HPEXData     ON HP.HardwarePartID = HPEXData.HardwarePartID   LEFT JOIN dbo.EXMIAttributes EXAttr    ON EXAttr.EXTypeID = HPEXData.EXTypeID   WHERE    HP.PartNumber = @PartNumber     AND REPLACE(SUBSTRING(HP.MIRevision, 1, 13),'.','') >= REPLACE(SUBSTRING(ISNULL(@PartRevision,''), 1, 13),'.','')    AND ISNULL(HP.ProjectID,'') = ISNULL(@SINumber,'')    AND (@ChassisIdentifier IS NULL OR HPEXData.EXMI_Type NOT IN ('Add In Card','Storage') OR EXAttr.ChassisIdentifier = @ChassisIdentifier)   END;anji

Getting Query Time Out Problem in Particular Dimension?

We are getting query time out problem in particular dimension in SSAS. can any one help on this?  

Can't make parameterized query work in data flow task


I have an OLE DB source in a data flow task.  The connection is pointing to a SQL Server 2008 instance.  My SQL works fine when I click the preview button.  Now I need to make it parameterized by adding a question mark in my where clause.  After doing so & clicking the Parameters button, I get the error described in this post .  When I try to do what's described therein to correct the problem (click the "build query" button), I get the error "The PIVOT SQL construct or statement is not supported."

I can't make this query an expression; it's simply too complex to maintain that way (besides, just pasting it into an expression builder window and trying to evaluate as is doesn't work - can't evaluate).

So the question is, how do I parameterize a query?  And why does SQL here work so radically differently from SQL in an execute SQL task?

T-SQL query, average of daily time periods over a date range



I'm building a report in Crystal Reports using a SQL command against a T-SQL 2005 telephony database.

I need to be able to run the query across a given datetime range, 6 months for example and bring back a 5 day display (Mon-Fri) with a group for every 15 minute interval in each day.

The group figures need to contain an average of the amount of calls presented for each 15 minute interval on any day across the whole datetime range, so for example the Monday 10:00 - 10:15 figure would be an average of all calls presented in every 10:00-10:15 range on each of the Mondays that fall within the datetime range.

I've got a query built now that gives total presented figures grouped by these intervals across one week but I can't figure out how to do this average function across a range.

Does anyone have any idea how I'd go about accomplishing this? I'm pretty new to SQL but keen to learn so any pointers on functions to research etc would be very much appreciated.

Thanks alot in advance, Andy.

Query for detail view across one week included below for table/fields etc...



count(DISTINCT Calls.SessionID) as Presented, min(Calls.startDateTime) as DateTime

INNER JOIN QueueDetail
ON Calls.sessionID =  Queues.sessionID
AND Calls.sessionSeqNum =  QueueDet

Query execution time from 1 second in SQL 2000 to 20 Seconds in SQL 2008 R2


When I ran this query from SQL 2000 it takes 1 second. now in SQL 2008 R2  the same query is 20 times slower.

This is the query and the numer of records and the time in SQL 2008 R2:

SELECT * FROM dbo.CxP_Saldos 
0 seconds, 923 Rows
SELECT * FROM S_Conta.Cuentas
0 seconds, 662 Rows
SELECT * FROM S_Conta.Cuentas c INNER JOIN dbo.CxP_Saldos s ON c.id = s.CtaProv
18 seconds, 923 Rows

how can I submit the actual execution Plan?

How to make the application install 1 time only



I have a need to create an applicaiton that can be installed 1 time only be a user. The user may or may not be and administrator.

How can I do this (without registry alteration if possible)?

Thanks much.


How to make the windows forms application install only 1 time and be used only 1 time only



I have a need to create an applicaiton that can be installed 1 time only be a user. The application can also be used only 1 time. (on a given PC)

The user may or may not be and administrator.

How can I do this (without registry alteration if possible)?

Thanks much.


Decrease Query execution time

Hello friends,
I am facing problem since last 5-6 month in sql server in query executing it takes 10-12 mins to execute even if database is on local pc.
I want to decrease it and want to make it execte withing 30-40 sec..
It is getting data of whole month with subquery of 1-30 date and each joining 3-5 tables for 300-500 persons.
so any ideas....
Confused D'Oh! 
One thing that i can do some process in background and insert fetched data to the some other table and at the time of executing above query fetch data from that table.
if its possible then how can i do it?Is there any other way to solve this problem?

Thanks in advance.. Smile 

By any chance can we able to optimize the below query.. It is running for long time..


By any chance, can we optimize the below query..This is running for 2 hrs..As of now, i really don't have any execution plans. 

If i replace "IN" with "Exists", i am saving few minutes. Can someone please optimize the query little more?
SELECT pm.pm_no,
eam_od_pm_tb pm, 
eam_od_location_tb loc
pm.site_id in ('ALF','BRF', 'COF','CUF','GAF','JOF','JSF','KIF','SHF','WCF', 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend