Ygthkb

Strange Sign on Lab Door

What does Cypher mean when he says Neo is "gonna pop"?

A starship is travelling at 0.9c and collides with a small rock. Will it leave a clean hole through, or will more happen?

What's a good word to describe a public place that looks like it wouldn't be rough?

figures in a grid with multiple line of texts

Slow moving projectiles from a hand-held weapon - how do they reach the target?

Why does a metal block make a shrill sound but not a wooden block upon hammering?

Why would the Pakistan airspace closure cancel flights not headed to Pakistan itself?

Broken patches on a road

How would one buy a used TIE Fighter or X-Wing?

Groups acting on trees

What creature do these Alchemical Humonculus actions target?

How to prevent users from executing commands through browser URL

Using only 1s, make 29 with the minimum number of digits

Why did this image turn out darker?

Can you combine War Caster, whip, and Warlock Features to Eldritch Blast enemies with reach?

Checking for the existence of multiple directories

Does Improved Divine Strike trigger when a paladin makes an unarmed strike?

Contest math problem about crossing out numbers in the table

We are very unlucky in my court

How would a Dictatorship make a country more successful?

Would these multi-classing house rules cause unintended problems?

Can a person refuse a presidential pardon?

Dilemma of explaining to interviewer that he is the reason for declining second interview

Query is slow with JDBC parameters, fast with concatenated SQL

Install JDBC driver 4.0 for SQL ServerWhat do these parameters mean at the beginning of my SQL Server query plan?Viewing execution plans for queries with parametersDealing with single quotes in a dynamic queryQuery SQL Server from Excel with parameters linked to cellsPostgreSQL query very slow when subquery addedCan I set PostgreSQL “stringtype=unspecified” behavior as default?Poor Query performance when scalar variables used in QueryWhy does my query run fast in Environment A, but slow in Environment B?SQL JDBC upgrade from v6.0 to v7.2 causes java.lang.NullPointerException in *some* stored procedure returned recordsets

0

I have an application that can be run with either mysql or SQL Server. There is a query that runs in a reasonable time on mysql, but is very slow on SQL Server. My application uses hibernate, but this particular query is coded in raw SQL so I know exactly what SQL is being executed. If I run the same query on SQL Server directly in a database client, it runs quickly. It's only slow when run in my application.

I discovered that if I take out the use of JDBC parameters and just concatenate the values into the SQL string, it runs much much faster. However this isn't a good solution; it doesn't guard against SQL injection or handle characters that might need escaping.

I have found recommendations to use sendStringParametersAsUnicode=false on the connection, but this isn't appropriate for my case because the columns are all nvarchar. In any case, I did try this and it didn't make anything faster and made some things slower. I also found info that suggested the jtds driver is faster, but it's not compatible with hibernate so I have to use the Microsoft driver.

My slow-running code looks like this:

    String sql = " SELECT DISTINCT FOO.BAR_ID_ "

            [big join here across multiple tables]

            "WHERE FOO.ID_= :fooId " +

            " AND BAZ.NAME_ LIKE :likeParameter ";

    Query query = getSession().createSQLQuery(sql)

            .setParameter("fooId", fooId)

            .setParameter("likeParameter", likeParameter);

    return query.list();

It runs quickly if I change it to this:

String sql = " SELECT DISTINCT FOO.BAR_ID_ "

            [big join here across multiple tables]

            " WHERE FOO.ID_= " + fooId  +

            " AND BAZ.NAME_ LIKE '" + likeParameter + "'";

    Query query = getSession().createSQLQuery(sql);

    return query.list();

The LIKE parameter has wildcards on both ends, which I would expect to prevent the use of an index on that column, but that would be true for the concatenation case as well, and it runs quickly. Clearly there is some other issue with SQL Server and JDBC parameters besides the Unicode issue. What can I do to get around this?

sql-server query-performance jdbc

share

asked 2 mins ago

DanaDana

1011

New contributor

Dana is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

0

I have an application that can be run with either mysql or SQL Server. There is a query that runs in a reasonable time on mysql, but is very slow on SQL Server. My application uses hibernate, but this particular query is coded in raw SQL so I know exactly what SQL is being executed. If I run the same query on SQL Server directly in a database client, it runs quickly. It's only slow when run in my application.

I discovered that if I take out the use of JDBC parameters and just concatenate the values into the SQL string, it runs much much faster. However this isn't a good solution; it doesn't guard against SQL injection or handle characters that might need escaping.

I have found recommendations to use sendStringParametersAsUnicode=false on the connection, but this isn't appropriate for my case because the columns are all nvarchar. In any case, I did try this and it didn't make anything faster and made some things slower. I also found info that suggested the jtds driver is faster, but it's not compatible with hibernate so I have to use the Microsoft driver.

My slow-running code looks like this:

    String sql = " SELECT DISTINCT FOO.BAR_ID_ "

            [big join here across multiple tables]

            "WHERE FOO.ID_= :fooId " +

            " AND BAZ.NAME_ LIKE :likeParameter ";

    Query query = getSession().createSQLQuery(sql)

            .setParameter("fooId", fooId)

            .setParameter("likeParameter", likeParameter);

    return query.list();

It runs quickly if I change it to this:

String sql = " SELECT DISTINCT FOO.BAR_ID_ "

            [big join here across multiple tables]

            " WHERE FOO.ID_= " + fooId  +

            " AND BAZ.NAME_ LIKE '" + likeParameter + "'";

    Query query = getSession().createSQLQuery(sql);

    return query.list();

The LIKE parameter has wildcards on both ends, which I would expect to prevent the use of an index on that column, but that would be true for the concatenation case as well, and it runs quickly. Clearly there is some other issue with SQL Server and JDBC parameters besides the Unicode issue. What can I do to get around this?

sql-server query-performance jdbc

share

asked 2 mins ago

DanaDana

1011

New contributor

Dana is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

I have an application that can be run with either mysql or SQL Server. There is a query that runs in a reasonable time on mysql, but is very slow on SQL Server. My application uses hibernate, but this particular query is coded in raw SQL so I know exactly what SQL is being executed. If I run the same query on SQL Server directly in a database client, it runs quickly. It's only slow when run in my application.

I discovered that if I take out the use of JDBC parameters and just concatenate the values into the SQL string, it runs much much faster. However this isn't a good solution; it doesn't guard against SQL injection or handle characters that might need escaping.

I have found recommendations to use sendStringParametersAsUnicode=false on the connection, but this isn't appropriate for my case because the columns are all nvarchar. In any case, I did try this and it didn't make anything faster and made some things slower. I also found info that suggested the jtds driver is faster, but it's not compatible with hibernate so I have to use the Microsoft driver.

My slow-running code looks like this:

    String sql = " SELECT DISTINCT FOO.BAR_ID_ "

            [big join here across multiple tables]

            "WHERE FOO.ID_= :fooId " +

            " AND BAZ.NAME_ LIKE :likeParameter ";

    Query query = getSession().createSQLQuery(sql)

            .setParameter("fooId", fooId)

            .setParameter("likeParameter", likeParameter);

    return query.list();

It runs quickly if I change it to this:

String sql = " SELECT DISTINCT FOO.BAR_ID_ "

            [big join here across multiple tables]

            " WHERE FOO.ID_= " + fooId  +

            " AND BAZ.NAME_ LIKE '" + likeParameter + "'";

    Query query = getSession().createSQLQuery(sql);

    return query.list();

The LIKE parameter has wildcards on both ends, which I would expect to prevent the use of an index on that column, but that would be true for the concatenation case as well, and it runs quickly. Clearly there is some other issue with SQL Server and JDBC parameters besides the Unicode issue. What can I do to get around this?

sql-server query-performance jdbc

share

asked 2 mins ago

DanaDana

1011

New contributor

Dana is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

    String sql = " SELECT DISTINCT FOO.BAR_ID_ "

            [big join here across multiple tables]

            "WHERE FOO.ID_= :fooId " +

            " AND BAZ.NAME_ LIKE :likeParameter ";

    Query query = getSession().createSQLQuery(sql)

            .setParameter("fooId", fooId)

            .setParameter("likeParameter", likeParameter);

    return query.list();

String sql = " SELECT DISTINCT FOO.BAR_ID_ "

            [big join here across multiple tables]

            " WHERE FOO.ID_= " + fooId  +

            " AND BAZ.NAME_ LIKE '" + likeParameter + "'";

    Query query = getSession().createSQLQuery(sql);

    return query.list();

share

asked 2 mins ago

DanaDana

1011

New contributor

Dana is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share

asked 2 mins ago

DanaDana

1011

New contributor

Dana is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 2 mins ago

DanaDana

1011

New contributor

Dana is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 2 mins ago

DanaDana

1011