I have done some digging around on the forums but can't find an example that would work.

Could you give me an idea on how to do this?

Thanks

Apologies, my mistake on the SSIS part.

Data is loaded via a SQL Import wizard each day, but will ultimately be done via multiple daily XML imports.

The count data will only...

http://www.sqlservercentral.com/Forums/Topic1568938-3412-1.aspx

This above thread explains why i am going down the trigger route.

"I had a same problem as you and I solved the problem in SSIS using 'Data access...

Jeff,

Many thanks for your time, i really appreciate your knowledgeable advice!

Ian

Jeff Moden (5/10/2014)

---

If you want real performance, I'd recommend that you stop counting altogether. Indexed views have an overhead on inserts in that they have to recalculate the totals...

It seems the jury is still out on the best way to do this.

http://stackoverflow.com/questions/7160411/is-it-better-to-maintain-a-seperate-count-table-vs-running-count-query-every-tim

http://stackoverflow.com/questions/3498646/select-count-vs-keep-a-counter

Thanks Jeff.

Regarding the Ebay or Amazon concept, they may have lots or servers but don't have a one to one relationship between server and user.

At the moment i do, and...

Thanks again Eirikur!

I think i found part of my problem.

"On average, this query takes 600.4 milliseconds on my system. We can speed it up using a nonclustered index:

CREATE NONCLUSTERED...

Eirikur,

I ran your sample query on my data and it took 284 ms vs my 0 ms using a SQL Indexed View.

I will research the book for some windowing function...

I have attached the plan, it seems like the clustered indexes would be faster if they were sql indexed views ?

My bad, the total count is 600k, records returned is only 10.

Eirikur,

Thanks for your suggestion and code sample.

Heavy duty stuff, my brain hurts just looking at it 🙂

Is that the same method used by Ebay and Amazon?

BTW I did...

Thanks for the code sample.

I will try to get some data, and table samples tomorrow.

Sadly it takes 'CPU time = 811 ms, elapsed time = 817 ms' for ONE query with nine items.

It is also a pain to type in the items names...

Thanks