SQL Question


Performance and scalability: SQL Question

  1. SQL Question (8 messages)

    Hello there I have an application that I am working on that is building the SQL on the fly (I have pseudo written below): StringBuffer sql = new StringBuffer(); sql.append("Select order, policy, date from"); if (orderType.equals("POLICY"){ sql.append(" table1"); } else if (orderType.equals("somethingElse") { sql.append(" table2"); } sql.append("where ") if (!isEmpty(SomeBean.getPolicy())) { sql.append("policy = " + SomeBean.getPolicy(); } Now my question is I have been told to use parameterized SQL, does that sound correct? This SQL is built on the fly and the table names and the where clause are all based on what the user enters from the screen. I thought I read a while back that if you have this sort of SQL then using a prepared statement is not effective. I would like to know what others are doing to accomplish this goal. My one thing is that this has to be fast we are talking a multi-terrabyte database and the users want everything to happen under a few seconds. Any and all help would be greatly appreciated.

    Threaded Messages (8)

  2. Re: SQL Question[ Go to top ]

    It is not useful to use prepared statements if there are no bind parameters. Also, I have seen people use prepared statements and close it within the same method. This is of no use either. The usefulness of prepared statements comes when you re-use it. That is, prepare the stmt once, bind different values to the same prepared stmt object and just execute. If it a complex query that you are constructing and executing against a large db, you might be better off using stored procedures instead.
  3. Re: SQL Question[ Go to top ]

    It is not useful to use prepared statements if there are no bind parameters.
    Not true. Prepared statements can still give benefits if the SQL statement contains joins between tables, for example, because it gives the database server a chance to prepare a reusable access plan for the join. Or, you might have a fixed WHERE clause (SELECT name, salary FROM Employees WHERE type = 'PERMANENT'). This would still be a good case to use a prepared statement if the query was being executed multiple times and you could not cache the results and you did not want to use a view, for whatever reason.
    Also, I have seen people use prepared statements and close it within the same method. This is of no use either.
    Not true. This is a perfectly valid way of avoiding SQL injection attacks, for example. It also avoids problems with having to escape characters such as apostrophes in strings when constructing dynamic SQL. That can be quite a challenge, particularly if you want to make sure you are not tying your code to any particular SQL dialect.
  4. Re: SQL Question[ Go to top ]

    If the SQL is as variable as you seem to suggest in your post, then you may struggle to get the sort of performance you're looking for. You are going to have to accept the trade off between performance and flexibility which everyone comes up against sooner or later. Prepared statements won't help because the table and the where clause are different every time. There is no way the database can reuse anything from the previous query if it's being asked to select different data from a different table based on different criteria. You have a similar problem even if you move the SQL down into a stored proc. Because the WHERE clause is not known in advance, the database server is very limited as to what sort of upfront optimisation it can perform. If you have a large database, the key point is to make sure you have the right indexes in place to support whatever selection criteria your users may choose. That's likely to make a bigger difference than worrying about how to fit this into a PreparedStatement. Even if you manage to get the SQL hard-coded in a stored proc, if there's no suitable index available then the database is going to trawl through the table row by row because it has no other option and on a large database that will take a long time.
  5. Re: SQL Question[ Go to top ]

    Thank you very much for the help, this is what I thought but I have a person here yelling at me to do it the other way.
  6. Re: SQL Question[ Go to top ]

    Prefer using PreparedStatement here. Find out what are the parameters that are not empty i.e. those that have to go with where clause. And instead of appending the parameter value directly to the query string, append just a '?' and add the actual value to a array. finally, if the array length is more than 0, then start ps.setObject for all those parameters. As it was said earlier, this avoids the SQL injection problem. Also, since prepared statements are cached and reused, assume a situation where there query is executed with same kind of where clause, in which case the query is not compiled every time. There is definitely an advantage of using prepared statement, than appending strings and building a query.
  7. Re: SQL Question[ Go to top ]

    assume a situation where there query is executed with same kind of where clause, in which case the query is not compiled every time.
    The whole point of the original question is that the query is NOT executed with the same where clause each time, in which case PreparedStatements are of little value. Consider this example: First query: SELECT * FROM Table1 WHERE ColA = ? Second query: SELECT * FROM Table2 WHERE ColA = ? Third query: SELECT * FROM Table1 WHERE ColB = ? The only option you have there is to build up three different statements because you cannot do this with a single resuable PreparedStatement. I suppose you could put together some sort of fancy mechanism that builds up a cache of PreparedStatements that represent the various combinations of tables and where clauses being run, but unless the number of combinations is limited, this will get very complicated very quickly. Or you could do some sort of monitoring to see what queries are actually being run. I suspect that 80% of the queries would be the same two or three options, with a handful of non-standard ones now and again. Then after you've monitored the profile in live use for a while you could use PreparedStatements for the 80% and dynamic sql for the 20% "oddballs". Or you could do it with LIKE selections and include every possible column and substitute a % where the column has not been selected by the user so that you get all values, but that would cripple the performance because there's not a DBMS on the planet that can sort out that kind of query and come up with a sensible reusable access plan. (Believe it or not, I have seen this done in a real application.) Bottom line: if you want flexibility, you are going to have to compromise on something else, and that is almost always going to be either performance or maintainability.
  8. Re: SQL Question[ Go to top ]

    I found a really good solution. I am using Oracle Named Parameters. This is really good instead of the ? you put in a name :CITY and then ps.setStringAtName("CITY", dto.getCity()); This worked so great
  9. Re: SQL Question[ Go to top ]

    When you build the where caluse try to use indexed column first, it makes it fast. - Chetan Khandar