How could i limit and slow down my local SQL Express for test purpose?

Question

I'm making a query in c# with ADO to select one record from Microsoft SQL Express database. for some test purpose I need to make a delay about 300ms with in SQL Express so that I could simulate my required situation. There is a limitations that I can not use any store procedures.

Any clue would be appreciated.

score 5 · Accepted Answer · edited May 23 '17 at 12:25

5

You could possibly inject a WAITFOR into your SQL:

See this SO QA for more info Sleep Command in T-SQL?

Given a command sent to the database:

SELECT * FROM MyTable

Embedded in a SqlCommand:

using (var conn = new SqlConnection("connection string"))
using (var comm = new SqlCommand("", conn))
{
    conn.Open();
    comm.CommandText = "SELECT * FROM MyTable";
    comm.ExecuteNonQuery();
}

Change it to something like this:

    using (var conn = new SqlConnection("connection string"))
    using (var comm = new SqlCommand("", conn))
    {
        conn.Open();
#if DEBUG
        comm.CommandText = "WAITFOR DELAY '00:00:01.000'; SELECT * FROM MyTable";
#else
        comm.CommandText = "SELECT * FROM MyTable";
#endif
        comm.ExecuteNonQuery();
    }

To embed a 1 second wait.

Ideally you don't want to do this in such an ugly and questionable way, but for the purposes of demonstration it will suffice.

edited May 23 '17 at 12:25

Community

1
1

answered Jul 19 '11 at 08:23

Adam Houldsworth

63,413
11
150
187

Are you sure that i could use this without getting involved with Store Procedures in SQL, cues as i mentioned using SP is a limitation. – Ehsan Zargar Ershadi Jul 19 '11 at 09:22
1

At some point you are sending SQL commands to the database, likely straight from code. In debug mode, add some conditional code that also injects a WAITFOR command - stored procedures aren't a requirement for it. – Adam Houldsworth Jul 19 '11 at 09:23
1

@Ehsan: Just run `WAITFOR DELAY '00:00:00.300'; SELECT ... ;` – ypercubeᵀᴹ Jul 19 '11 at 09:41
I'm relay stuck with this, have no idea how to join my select command with the WAITFOR in one T-SQL command !! I think I need more Help. – Ehsan Zargar Ershadi Jul 19 '11 at 09:44
Thanks ypercube. That was great. – Ehsan Zargar Ershadi Jul 19 '11 at 09:45
@Eshan I've provided an example, let me know if this helps. – Adam Houldsworth Jul 19 '11 at 09:48

score 2 · Answer 2 · answered Jul 19 '11 at 08:21

2

You could include a WAITFOR command in your SQL batch.

Or, you could add a lot of data, and query over non-indexed columns, that's sure to make it slow.

answered Jul 19 '11 at 08:21

driis

161,458
45
265
341

How could i limit and slow down my local SQL Express for test purpose?

2 Answers2