As a matter of fact, I heard of the cube thing and it sounded pretty complicated to me. I'll have a deeper look then.

Thanks a lot.

Stephane

I see...

Do you think there's something to do to get rid of the table spool?

Thanks a lot for your help, it's really appreciated

Stephane

There still a table spool and the query take 3 seconds with 1000 rows. It's pretty much slow. Here's the new code:

IF OBJECT_ID('TempDB..#tbl_logs','U') IS NOT NULL

...

Hi,

I did some tests and the results are the same, but it's not quicker. There's a Table Spool in the query execution plan that takes 47%.

Here's the table and...

thanks!

That's what I thought... So it seems useless to create a view and to index it on the same fields that are already indexed?

Stephane

No... How do you do that?

Thanks

Stephane

Hi,

Thanks for your response.

There are around 100 M rows in the table. Without the TOP, it would return around 2 M rows. Without the order by, the query runs in...

Hi,

Thanks for your answers.

I forgot to tell that the primary key is fk_tbl_websites_id and date. Without the order by clause, it executes in a flash. With it, the clustered index...

You're right, it might be confusing!

It's not an entry per day, but an entry for each web page visited. So in a single day, we could have 100 000 or...

Hum... I think I miss a thing here.

If I have an indexed view, should I get rid of any indexes on the table? Is the performance cost on the insert...

No, I didn't consider that actually.

Can an indexed view be faster than an index on the table itself?

Thanks

Stephane

Good idea.

I tried and it says « Estimated Improvement 0% »... That's a dead end I think! 😀

Hum, of course, the user doesn't need a million of rows. The query is about getting the count! 😉

select count(distinct fk_tbl_visits_id) as nb from tbl_logs

where fk_tbl_users_id = 10203 and dateadded...

Hi,

Finally, after a lots of tests, this is the fastest index (note the include column). It executes the query in 2 seconds.

CREATE NONCLUSTERED INDEX [idx_date] ON [dbo].[tbl_logs]

(

[fk_tbl_users_id] ASC,

[dateAdded] ASC

)

INCLUDE...

Here it is. Some objects name might be different (tbl_tmp vs tbl_logs) but it's the same result.

There are only two indexes, those I gave the definition.

Thanks

Stephane