introducing delay of milliseconds or microseconds in stored procedure

Question

introducing delay of milliseconds or microseconds in stored procedure

vijinarav

SSC-Addicted

Points: 426
More actions
December 1, 2008 at 5:44 am

#97003

Hi all,
I need to introduce microseconds and milliseconds delay inbetween two insert statements in a stored procedure.Is there any function or way to do it without affecting the functionalility. i am not allowed to introduce new table or column in our current system for this specific task.
thanks in advance

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Have a look at WAITFOR in BOL, it's exactly what you need.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

Might I ask why you want to purposely slow down an application?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

vijinarav SSC-Addicted Points: 426 More actions · Answer 3

we are testing the server performance like both disk's read-write performance while using sql server with the load, so to get differrent load (throughput) all we need to change is simply the delay. To determine how many separate or addtional disk required for the server.

Jeff Moden SSC Guru Points: 1004648 More actions · Answer 4

As previously posted, WAITFOR DELAY will get you to the nearest 3 milliseconds. If you want microseconds, just to a couple of PRINT statements.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

vijinarav (12/1/2008)
we are testing the server performance like both disk's read-write performance while using sql server with the load, so to get differrent load (throughput) all we need to change is simply the delay. To determine how many separate or addtional disk required for the server.

FYI, this method is not able to accurately model the effect of different disk performances on a system, including a SUT (System Under Test).

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004648 More actions · Answer 6

vijinarav (12/1/2008)
we are testing the server performance like both disk's read-write performance while using sql server with the load, so to get differrent load (throughput) all we need to change is simply the delay. To determine how many separate or addtional disk required for the server.

I understand your intent... but it seems a bit contradictory to slow down a process to do performance testing... seems like you'd want to run it flat out to do this. You know... something like having 20 or 30 desktops all waiting for the same time to start and then do something like a million row CSV split each or have them all try to insert new rows as fast as they can into the same table that has an indexed view on it or all do some sort of nasty dynamic cross tab on a couple of huge joined tables with a star-schema that went from here to the middle of next week for lookups.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)