Thanks for the reply.

Here's the link:

http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx

The slowness WAS due to Parameter Sniffing. I tried some of the techniques to overcome that and it solved the problem. Thanks for your suggestions.

Thanks. The link was very useful. It solved the problem.

Ray,

Thanks for your reply. Clearing the procedure cache definitely helped. It seems to me that for the same stored procedure, there is one cached execution plan for Query Analyzer and...

Remi,

Good solution. Using a table function definitely improved the performance. It takes 10 sec compared to 5 sec without using a function at all (I guess that's expected) but definitely...

but the timespan can be large, ie. from 2005/01/01 to 2005/01/03 AND from 12:00am to 11:59pm is also a possibility

the former case ... from 2005/01/01 to 2005/01/03 AND only from 12:30 to 15:55 for each day ...

My final query reads like this. I'm not using any functions and the BETWEEN clauses should filter out most of the records from the large table making it run faster.

CREATE...

I already have the date limitation in place, which is how we got the query to run in 5 sec as the table is pretty big with about 300 million...

Remi,

Thanks for your response. The actual condition I'm using goes something like this ..

WHERE DATEPART(HOUR, CurDate) > @StartHour

AND DATEPART(MINUTE, CurDate) > @StartMinute

AND DATEPART(HOUR, CurDate) < @EndHour

AND DATEPART(MINUTE, CurDate) > @EndMinute

CurDate is one...

Remi,

Thanks for your suggestion. Could you please post an example of using a view versus using a table function for this purpose?

I guess my example should have been better. My query could also look like this:

    IF @a < @b-2

         select Number from table_1

         where MY_CONDITION

    ELSE...

Thanks to both of you for your valuable suggestions. We are going to carry out some tests based on them and see what we get. Shall post it here if...

I accept that normalizing the tables would solve many problems. But I have this other table ShopPopulation that contains the number of people of different age groups that are present...