select count very slow on large table

[terra_frugum]

I have a table with 3 million records.
I do just a simple

select count(RecId) from my_table;

and the result comes back in about 80(!) seconds.

If I do a SELECT COUNT(*) FROM my_table in SQL Server, I get the results in
less than 2 seconds.

Anyone knows why the discrepancies? How to make the AX query faster?
Thanks.

[luegisdorf]

Hi terra

I have a table which keeps > 8 million records and have 5 min to
execute select count.

Have you compared the times when you execute direct in the statement
in SQL-Studio?

Regards
Patrick

On 11 Dez., 17:15, terra_frugum

[Vanya Kashperuk Ivan at dot nospam]

Compare the SQL plans generated directly from SQL and from AX

--
Kashperuk Ivan (Vanya), SDET, Inventory management, Microsoft Dynamics AX
My blog - http://kashperuk.blogspot.com
Download MorphX IT in Russian - http://www.lulu.com/content/723888

[terra_frugum]

Hi luegisdorf,

When I did the direct SELECT in SQL Server it finished in 2 seconds.

As per Ivan's suggestion we did a SQL plans comparison and the only
difference was that AX added a WHERE DataAreaId = ... clause.

"luegisdorf" wrote:

> .
>

[Luegisdorf]

Well, the dataAreaId is core ...

But if you need that count for the specific table all the time, you could
probably create a view with count aggregate.

I'm not quite sure, but to select a view could be faster. But you have to
check.

Regards
Patrick