We have an application that has been developed in Hibernate and the SQL’s are all inline from the app. There are no stored procedures. The problem I see is the way parameters are being passed in the queries that are formed in Hibernate. As an example, CustomerID is defined as INT column in the table. However, Hibernate passes the @CustomerId input parameter as BIGINT.Is that an issue on SQL Server end that I should be concerned about while troubleshooting performance problems ?
1 Answer
Is it a problem if BIGINT data type parameter gets passed into a table for join with INT data type?
It can be, from a performance perspective, yes. When there are different data types being compared in a predicate, the SQL Server Engine will implicitly cast the lower precedence data type to the higher precedence one. Here's a list of the data type precedences. The INT data type gets implicitly casted to BIGINT.
Implicit conversion can cause cardinality estimate issues which affect which operations (for example, scanning the entire table vs seeking to only a few rows) are chosen when the execution plan is being generated. With a poor enough cardinality estimate, less efficient operations may be chosen for the execution plan, causing the query to take longer to execute.
Additionally, a poor cardinality estimate could also cause the execution plan to under- or over-request hardware resources, such as Memory, also causing the query to take longer.
You should always aim to keep your data types consistent when they're predicated on.
