Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

stored procedures

ShadovvShadovv Member Posts: 31
I'm trying to create a procedure to limit the dataset I have before doing an actual query from a web base application. (PHP).

I'm trying to write a stored procedure to limit the dataset by a timestamp.

For instance I have thousands of tuples in the database, each of which has a field to represent the time (Date datatype).
In a query I would simply do a:
SELECT * FROM mytable WHERE timestamp BETWEEN (sysdate - 1) AND sysdate


This works fine and simple in a query, however when I start getting more and more tuples (hundreds of thousands) it starts to lag a bit, especially with more conditions added on top of this query.

So I want to put this query into a stored procedure to limit by dataset base on the timestamp before I do the additional queries.

I've tried this using oracle "views" and it works fine. However I need it to be a stored procedure I can call. I'm quite new to this stored procedure in oracle can someone show me how to port this simple query into a stored procedure in oracle?

Comments

  • infidelinfidel Member Posts: 2,900
    : I'm trying to create a procedure to limit the dataset I have before doing an actual query from a web base application. (PHP).
    :
    : I'm trying to write a stored procedure to limit the dataset by a timestamp.
    :
    : For instance I have thousands of tuples in the database, each of which has a field to represent the time (Date datatype).
    : In a query I would simply do a:
    : SELECT * FROM mytable WHERE timestamp BETWEEN (sysdate - 1) AND sysdate
    :
    :
    : This works fine and simple in a query, however when I start getting more and more tuples (hundreds of thousands) it starts to lag a bit, especially with more conditions added on top of this query.
    :
    : So I want to put this query into a stored procedure to limit by dataset base on the timestamp before I do the additional queries.
    :
    : I've tried this using oracle "views" and it works fine. However I need it to be a stored procedure I can call. I'm quite new to this stored procedure in oracle can someone show me how to port this simple query into a stored procedure in oracle?

    A stored procedure doesn't "prefetch" any data, it just performs a block of code when you call it, just like a function in any other language. A stored procedure doesn't hold some dataset in memory for you to then query a reduced set of tuples. What I'm saying is that you can't "query a query" to save yourself execution speed. What you need to do is tune your query and perhaps create one or more indexes on the queried tables.


    [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]

    [code]
    $ select * from users where clue > 0
    no rows returned
    [/code]

Sign In or Register to comment.