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??
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
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 ]
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.
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)
You showed up just in time for the waffles! And this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop