• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Prepared statement, Execution plan and Indexes

 
Ranch Hand
Posts: 502
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Jayesh,
The execution plan definitely contains which indexes to use.
 
Jayesh Lalwani
Ranch Hand
Posts: 502
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 502
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Which RDBMS are you running Oracle ?

If yes tell youre DBA he should run "analyze table" for the tables used in the sql statement.
 
Ranch Hand
Posts: 1646
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 502
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 502
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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)
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic