aspose file tools*
The moose likes JDBC and the fly likes Prepared statement, Execution plan and Indexes Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Prepared statement, Execution plan and Indexes" Watch "Prepared statement, Execution plan and Indexes" New topic
Author

Prepared statement, Execution plan and Indexes

Jayesh Lalwani
Ranch Hand

Joined: Nov 05, 2004
Posts: 502
We have a web application that uses JSP and Java to connect to a SQL Server database. Inside the application there is a module that does a batch import of data into the database. The data import reads data from a flat text file, and uses PreparedStatement to validate the data and insert records into the database.
For reasons unknown to me, the data import module wasnt performance tested. So, now we have this application installed at the client, and the client reports that the data import is very slow. We performed analysis on the import that is running at the client, and figured out some of the Select statements that do the validation are running very slow. Usually, they take 10-20 ms on an near-empty database and now they take more than 600ms. So, we figure that if we add a few indexes, the import would run faster. So, we add the indexes, and the performance doesnt improve. Please note that we didnt restart the batch import, and we added the index while the batch import was running. I checked the execution plan of the query in the SQL Query Analyzer, and the query should use the new index.

So, my question is if I am using PreparedStatement on the client, the server creates an Execution Plan for the statement. Does this Execution Plan include which indexes to use?? In other words, if I add indexes to the database, do I have to restart my app for the new indexes to be used??

Thanks in advance
Jayesh
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30762
    
156

Jayesh,
The execution plan definitely contains which indexes to use.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Jayesh Lalwani
Ranch Hand

Joined: Nov 05, 2004
Posts: 502
I thought so. Thanks

Now if I could convince the architect on my team. Do you know any sites or whitepaper that talks about this? If I show him a white paper then he would beleive it

Thanks
Jayesh
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30762
    
156

Jayesh,
Show him the execution plan itself!

I don't know of any sites/articles on the subject, but I imagine you could find one by searching on the web.
Jayesh Lalwani
Ranch Hand

Joined: Nov 05, 2004
Posts: 502
Originally posted by Jeanne Boyarsky:
Jayesh,
Show him the execution plan itself!

I don't know of any sites/articles on the subject, but I imagine you could find one by searching on the web.


But the execution plan that I get from SQL Query Analyzer uses the new indexes. There is no way for me to prove that the execution plan used by the app uses the old indexes
Anyways it's a moot point now. The data import finished yeterday(after running for more than a week Thanks for your help
Martin Locher
Greenhorn

Joined: Jan 30, 2005
Posts: 1
Which RDBMS are you running Oracle ?

If yes tell youre DBA he should run "analyze table" for the tables used in the sql statement.
David Harkness
Ranch Hand

Joined: Aug 07, 2003
Posts: 1646
Instead of restarting your application every so often, if you're using a connection pool you can see if it has a "time to live" parameter that will make it close and reopen any connection that is older than the time specified.

The new Oracle JDBC driver (dunno about SQL Server) shares the cached prepared statements across connections, so I don't know how to do it in that case. Maybe there's a method on the driver to flush all cached statements?

I must admit I never thought about this issue. However, I've never had large data feeds coming in -- only going out. But I suppose even in that case as users add data the choice of indexes could change over time. Maybe regular rolling restarts aren't such a bad idea...
[ January 30, 2005: Message edited by: David Harkness ]
Jayesh Lalwani
Ranch Hand

Joined: Nov 05, 2004
Posts: 502
I did some more research on the SQL Server, and it looks like SQL Server selects indexes based on the amount of data in your tables. Basically, it keeps statistics of the tables and the various indices to determine a) which index to use and b) which index to search though first. So, if you have 2 tables, say Student and Teacher, and your suppose you want to find teachers in Computer department that have A grade students, then your query might be like this

Select Teacher.name from Teacher Inner Join Student on Teacher.ID = Student.TeacherID where Student.grade='A' and teacher.dept = "Computer"

(I know I know.. bad design..Student will have multiple teachers, but let's assume that there is a one-to-many relationshp between Teacher and Student)

In this case, SQL server will try to determine whether it is easier to search through Student first or teacher first. Say, there are more students than teachers, and searching through Teacher by dept is faster than searching through Student, then SQL server will find all teachers in computer department, then do the inner join based on ID and then search by student's grade. If the inner join is going to be costly, then SQL Server will search in teacher, then search in patient and then do inner join.

So, basically, one part of the documentation says that SQL Server will change the execution plan at run-time, while other part of the documentation says that the execution plan is pre-computed for Prepared Statement Gotta love Microsoft, right!! I guess I will have to keep investigating this. I dont need this information right now, but it's good to know.
Jayesh Lalwani
Ranch Hand

Joined: Nov 05, 2004
Posts: 502
Originally posted by Martin Locher:
Which RDBMS are you running Oracle ?

If yes tell youre DBA he should run "analyze table" for the tables used in the sql statement.


We are using SQL Server.
I guess "Generate Statistics" in SQL Server is analogous to "Analyze table" in Oracle. I was doing "Generate statistics" after adding the indexes, but for some reason the application wasnt speeding up as much as we hoped. The thing is that SQL Server decides which index to use(or in some cases it might skip the indices and do a table scan)
 
jQuery in Action, 2nd edition
 
subject: Prepared statement, Execution plan and Indexes