CF_SQL_INTEGER v/s CF_SQL_NUMERIC ?

[sonu_agarwal]

Hello everyone,

Two months back, in my project I had changed CF_SQL_INTEGER to CF_SQL_NUMERIC for entire application. Now I am noticing significant perfromance degradation. Page are taking more time to load than before.

Any idea/information on this will really help me a lot.

Thanks in advance !

Sonu

[Adam Cameron.]

in my project I had changed CF_SQL_INTEGER to CF_SQL_NUMERIC for entire application.

Why'd you do that? You didn't change all your data from using ints to using floats/decimals, did you?

Without basis for saying so, I would think that integers would perform a lot faster than floats or decimals if for no other reason than they're simpler data types, and require a lot less monkeying around to get them into a state the computer can work with (ie: binary).

--

Adam

[sonu_agarwal]

Hi Adam,

Thanks for your quick response. Actually sql type was changed because there was a high possibility that  data could extend the integer limit in CF. I assumed that cf_sql_numeric would behave exactly as cf_sql_integer with more larger value limit.

Sonu

[Adam Cameron. · Accepted solution]

I think you'll find JDBC might be converting your integers to decimals or floats, passing it to the DB and then the DB's gonna be coercing it back to an integer before using it. Which will probably add unnecessary overhead.

Did you check whether CF_SQL_BIGINT would work? It seems to support up to 9223372036854776832 (which is 2^63 + 2 ^10, which is a weird number), which is quite a lot...

--

Adam

[sonu_agarwal]

Hey Adam,

I think you are right. It is very likely that there can be a overhead while converting the types. I will certainly try replacing cf_sql_numeric  to cf_sql_integer and cf_sql_bigint(wherever required) and ask my performance monitoring team to oversee the overall performance.

I really appreciated your help on this. Thanks again.

Sonu

[Adam Cameron.]

Nice one. Report back with your results, because this'd be good info for people to know.

Take it easy.

--

Adam

[Biswajit]

Hi Adam,

Thanks for the info. This is really very helpful and informative.