Many Thanks

I'll have a look at making those changes.

"Erland Sommarskog" <sommar@algonet.se> wrote in message
news:Xns93E5F2C02272EYazorman@127.0.0.1...
> Steve Thorpe (stephenthorpe@nospam.hotmail.com) writes:
> > I am trying to write a report that calculates the average number of
> > sales over 7, 14, 31 and 365 days for each hourly period of the day.
> > the problem is it takes over 4 minutes to run.
>
>
> Troubleshooting performance problems over newsgroups is often difficult
> because, without access to the database it's not possible to test
> various scenarios. And without complete knowledge about the tables
> involved it is even more difficult. Just seeing the procedure code,
> may sometimes be sufficient, but not always.
>
> Anyway, I have two suggestions for your procedure, although none of
> them are likely to improve performance radically.
>
> The first is that you use a global temptable. Change this to a local
> temp table. This avoids problems if two users run this procedure
> simultaneously.
>
> The other is that you rewrite the procedure to not use dynamic SQL.
> As far as I can see, the only reason that you use dynamic SQL, is
> that you intended to have a comma-separated list in @Wholesalers.
>
> I would suggest that you handle the list like this:
>
> CREATE TABLE #wholesalers (id int NOT NULL)
> INSERT #wholesalers (id)
> SELECT number FROM iter_intlist_to_table(@wholesalers)
>
> You find the code for iter_intlist_to_table at
> http://www.algonet.se/~sommar/arrays...st-of-integers.
>
> Armed with this temp table, you can, as far as I can see, rip out the
> dynamic SQL and replace it with static. This may give some performance
> benefit, but only some single second.
>
> However, once you have rewritten the code into static SQL, it will be
> more pleasant to take a look at it.
>
> It also helps if you post CREATE TABLE and CREATE INDEX statements
> for the involved table. Some hints about data sizes is also good.
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@algonet.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp