Looking for a slow query... yes slow

Question

Post reply

Looking for a slow query... yes slow

Joshua-531562

SSC Enthusiast

Points: 104
More actions
October 29, 2008 at 12:38 pm

#200615

Hello,
This is unusual I know. I have a small database with very little data. I need to test come timeouts (ADO Timeout) with my java app using this database. How do I make a query that will take a minute or two to execute with very little data in my database? It's too fast now with the queries I have tried. I have the option of using SQL Server or Oracle but I prefer Oracle. In SQL Server, I tried WAITFOR DELAY, but that caused a delay prior to the query being executed. The timeout doesn't start until the query begins execution. Therefore, I can't test my timeout. Thank you.
~ Joshua

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Write a query using a CROSS JOIN or two against you biggest tables. That may slow your query down enough.

george sibbald SSC Guru Points: 104210 More actions · Answer 2

use a cursor (there I said it) or a loop to trawl through you biggest table one row at a time, updating something, If thats not slow enough on its own, put a waitfor delay in the loop as well to really slow it down.

Then package it up and sell as an app to some poor unsuspecting sap. You wouldn't be the first. 😀

---------------------------------------------------------------------

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 3

my choice would be a Full Table Scan in your larger table, then pack the predicate with as many inline views as you can, all of them doing Full Table Scan on your very same larger table. That would do it. 😉

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 4

while 1=1

print 'test'

Madhivanan

Failing to plan is Planning to fail

Joshua-531562 SSC Enthusiast Points: 104 More actions · Answer 5

Thanks to all who responded. The responses were helpful and varied. 🙂

~ Joshua

arbarnhart-780010 Old Hand Points: 318 More actions · Answer 6

Even though you have a few, I figured I would let you know what I have done. I didn't want to chage the production code but test what happened when it times out, so I left my main application and database as is and just opened a query window on it and then did a begin trans. This causes any SQL statement submitted by any appliaction against the same database that alters anything to wait until the transaction is commited or rolled back.

gints.plivna Ten Centuries Points: 1256 More actions · Answer 7

SQL> create or replace function sleep (i in number)

2 return number is

3 begin

4 dbms_lock.sleep(i);

5 return 1;

6 end;

7 /

Function created.

SQL> set timing on

SQL> select sleep(5) from dual;

SLEEP(5)

----------

1

Elapsed: 00:00:05.01

However you have to directly grant execute on dbms_lock to your particular user, or better create this function in some privileged user's schema and then grant to necessary user.

Gints Plivna
http://www.gplivna.eu