Question

Locked

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

By naveenaraj ·
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

This conversation is currently closed to new comments.

2 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

Hard to know where to start with that

by Tony Hopkinson In reply to In the crysatl report i a ...

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.

Back to Web Development Forum
2 total posts (Page 1 of 1)  

Related Discussions

Related Forums