February 8, 2011 at 12:25 am
we have a application to sqlserver, app write by java.
there are some performance issue.
i monitor it by sqlserver profile, i found some system procedure cost much time.
i don't konw why, and how to optimize it on db side or APP side
would you help me .
thanks very much
when app call the sp, the three system sp execute every time
sp_unprepare
exec sp_prepexec
exec sp_sproc_columns (this sp cost much time)
February 9, 2011 at 5:36 am
lovelanjunping (2/8/2011)
we have a application to sqlserver, app write by java.there are some performance issue.
i monitor it by sqlserver profile, i found some system procedure cost much time.
i don't konw why, and how to optimize it on db side or APP side
would you help me .
thanks very much
when app call the sp, the three system sp execute every time
sp_unprepare
exec sp_prepexec
exec sp_sproc_columns (this sp cost much time)
JDBC SUCKS and their ain't much you can do about it I think. There may be a setting about whether to prep a statement or not, but I think that is up to the devs - you don't WANT a global setting for that because you DO want to prep things that are gonna be reused.
The design patterns used by developers and database architect are usually a much better place to look to start improving performance. Some of the stuff the devs do FORCE those ugly preps and metadata grabs.
What you REALLY need to do is hire a good development DBA to make sure you use best practices and to help tune complex stuff. Hiring a performance tuning professional for a short term engagement is a distant second as far as helping you out.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply