Performance difference in queries

[former_member390193]

Hi,

Can somebody put some light on this:

I have two queries which look the same , the first one is taking hours to return a result in the Production system having loads of data

1. SELECT COUNT (DISTINCT PRODUCT_ID) FROM "_SYS_BIC".CV_ABC"   

And the second query is getting me the same result within fraction of seconds.(1 second or so)

2. SELECT COUNT(*) FROM  (SELECT DISTINCT PRODUCT__ID FROM "_SYS_BIC"."CV_ABC")

Though they look the same performance wise, what is the thing in the first query which is taking so long to execute?

Kindly help me understanding this...

With Regards,

Mohammad Irfan.

[lbreddemann]

Well actually they look quite different performance wise, don't they?

To answer your question, it will be required to see what the database actually does in order to deliver the results.

You might want to check the explain plan and the plan viz for both queries.

Also, since you're using a distinct count with a calculation view, you should check whether you actually retrieve the same and correct result. Please see the modelling guide and the respective SAP notes on count(*) and calc views.

[Former Member]

Hi Irfan,

The problem in with count() function.

The reason why first query with COUNT (DISTINCT PRODUCT_ID) takes time is because,

it has to first search all the data than check if there is no NULL and duplicate columns, exclude them and display the count.

Second query with COUNT(*) does nothing but displays the count of resulted from the in sql statment.

In the first query Count function has to perform few extra work/processing whereas in second it has nothing to check on and display the count.

Regards,

Prithviraj.