Re: Stored procedure/package with DateTime parameters need help

MrHelpMe wrote:
> Mark D Powell wrote:
> > On Jan 16, 9:40 am, "MrHelpMe" <clintto..._at_hotmail.com> wrote:
> > > Hello experts,
> > >
> > > Is there some who could help me with this. I have a number of
> > > packages/stored procedures created in oracle 9.2 that need to accept
> > > parameters. The parameters I have having trouble with are a StartDate
> > > and EndDate. If I have a package/SP with a start and end date how do I
> > > pass these in my where clause and how do I allow the user to either
> > > select NULL(so that the recordset returns all data regardless of Start
> > > and End Date) or a Start and End Date is they are looking for a range
> > > of values in the recordset. Please note that this SP will be hooiked
> > > upto Crystal Reports and the start and end date will be passed to the
> > > report in ASP.
> > > i.e
> > >
> > > [CODE]
> > > WHERE a.DateTime BETWEEN StartDate and EndDate...this is the part I am
> > > unsure of
> > > [CODE]
> > >
> > > Thanks everyone.
> >
> > What is the front-end tool written in?
> >
> > The SQL is fine as written though depending on where the data is coming
> > from you are likely going to need to use to_date to convert the input
> > parameters to date values. You can either check the input parameters
> > for being NULL or have the front-end substitute sysdate for EndDate
> > when the parameter is NULL. For StartDate you can just substitute a
> > point in time well in the past.
> >
> > Assuming a usuable index on the date column exits then for the case
> > when the entire date range is requested it would probably be better if
> > the BETWEEN cause was not part of the SQL statement. This could mean
> > that you want to code two separate SQL statements in the procedure and
> > execute the one that fits the requested data. The reason being that if
> > the index is chosen it will be beneficial for short range scans but
> > will be inefficient for selecting all the data.
> >
> > A little performance testing may be called for using various
> > percentages of the total date range to determine if any special
> > handling is required.
> >
> > HTH -- Mark D Powell -
>
>
> Mark,
>
> Thanks for the reply. The Front end will be written in ASP. Oh I
> think I see what you are saying. So rather then use BETWEEN I could
> say
> [code]
> WHERE a.DateTime >= StartDate and a.DateTime <=EndDate
> [/code]
>
> Is this what you mean. Also, could you see anything wrong with this
> beacuse I really don't
>
> [code]
> WHERE a.DATETIME BETWEEN NVL(StartDate,'1000-01-01') And
> NVL(EndDate,'9999-01-01')
>
> or like you said
> [code]
> WHERE a.DATETIME >= NVL(StartDate,'1000-01-01') And
> a.DATETIME <= NVL(EndDate,'9999-01-01')
> [/code]
>
> Thanks again

What I want you to consider is if you are going to read the entire possible range of dates then your are going to read the entire table and you will not want the CBO to do it by using an index on the date column. In this case a full table scan would be much more efficient. But your code is probably going to have variable names in the date value locations. With a bind variable the optimizer is not going to know what percentage of the table is going to be read and will devise a plan based on averages an assumed size of the amount of data being scanned. With bind variable peeking it may peek at a single day query but the very next use might scan the entire possible date range. To prevent using the index for the full data or to force use of the index for small ranges you might have to resort to some special logic.

That is why I suggested testing your procedure with a series of range sizes: EndDate - StartDate for 1 day, 15 day, 30 days, 90 days, 180 days, 365 days, 730 days, etc.... depending on what the possible ranges are.

The test results will let you know how well your procedure is going to work.

HTH -- Mark D Powell -- Received on Tue Jan 16 2007 - 19:21:45 CST