Question Locked

In the crysatl report i am writing a query like these its taking more than

0 helpful
2 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

n the crysatl report i am writing a query like these its taking more than hour to exectue query is follows:
SELECT 'Average Daily Traffic Volume',TD.TIMESTAMP,(SUM(TDV.vehicle_volume)/(TO_NUMBER(TO_CHAR(LAST_DAY(td.TIMESTAMP),'dd')))) AverageVolume
FROM TRAFFIC_DATA_VOLUME TDV,TRAFFIC_DATA TD
WHERE TDV.traffic_Data_id = TD.ID AND (TO_NUMBER(TO_CHAR(td.TIMESTAMP,'MM'))
BETWEEN {?Month1}-2 AND {?Month1})
AND TO_NUMBER(TO_CHAR(td.TIMESTAMP,'YYYY'))={?Year1}
GROUP BY TD.TIMESTAMP

please suggest to modify the query
data base having more than million records

2 replies

by tony hopkinson

Look up the datepart function. Get rid of all that tonumber tochar drivel.

I'll bet a small sum of money you don't want it grouped by TD.TimeStamp.
I suspect you wanted the month of the date.

PS what happens if you pass Month1 = 1 to this query

It will find January, 2 will Jan and feb
and to do december you'd have to pass in 13...

If this is only for one month decalre a couple of datetimes and build startdate and enddate.

e.g.
Select @StartDate = Convert(DateTime,Convert(Char(4),?Year1) + Replicate('0',2-datalength(convert(VarChar(2),?Month1)) + Convert(VarChar(2),?Month1) + "01")

td.TimeStamp Between @StartDate and @EndDate is going to be shed loads quicker, becvause the conversions which are very expensive are only run once.

I suggest you test up a bit of test data and get you query right befotre you look at optimising, because that group by don't look right to me, from here.

This discussion is closed to new replies.