Hi,
In a job, as one of the last steps, I want to execute an application.
Can someone please let me know the command (cmdexec) to be put
Right now, I have put "E:\Program Files\Postscript\gsview\gsview32.exe" which is not working
I know for dts packages, the command is
dtsrun /FE:\temp\Let-Stat MIS\Letters_Import.dts /NLetters_Import
Thanks and regards
AbhiUse the xp_cmdshell procedure to execute commands from a system prompt.
blindmansql
Showing posts with label application. Show all posts
Showing posts with label application. Show all posts
Wednesday, March 28, 2012
Monday, March 19, 2012
Job Frequency
Anyone know a tricky way to get a job to fire more frequent than every minute?
I would like to have an action, trigger the job, but the application login does not own the job and am having problems. Here's what you run into if the application login tries to do this:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q201021&VOTE=1
I am running SQL 7 SP1 on NT 4 SP6a.
ThanksYou can create a stored procedure that uses a WAITFOR DELAY and set it up with a delay of what ever seconds you want. Then use the stored procedure sp_procoption to set the new procedure you created to startup automatically when SQL Server starts. I don't know if this is what you are looking for, but I'd be hesitant on the CPU utilization of the job.
I would like to have an action, trigger the job, but the application login does not own the job and am having problems. Here's what you run into if the application login tries to do this:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q201021&VOTE=1
I am running SQL 7 SP1 on NT 4 SP6a.
ThanksYou can create a stored procedure that uses a WAITFOR DELAY and set it up with a delay of what ever seconds you want. Then use the stored procedure sp_procoption to set the new procedure you created to startup automatically when SQL Server starts. I don't know if this is what you are looking for, but I'd be hesitant on the CPU utilization of the job.
Friday, March 9, 2012
Job Execution
I've got a scheduled job that runs a package every couple of minutes, and I also have a method in an application that calls a stored proc that can also execute the same scheduled job. My question is what will happen if I call the stored proc to execute the job at the same time that the job is executing on its regular schedule. Is it possible that the an instance of the package will execute in parallel?
SQL Agent jobs won't run multiple instances simultaneously, as long as you are calling the same job.
Wednesday, March 7, 2012
Job Already Started Error
Hi, I have a problem with my application. Its in VB6 and it prints different reports to different printers. When I print the fist set of reports it works fine, but if I try to print another set, I get Job Already Started error. I have done everything in order to resolve this issue, but no success. I cannot disable the form while it is printing and then enable it again; I need the reports printed at any time, even if the previous report did not finished printing, so it will be waiting on the printers print queue.
Please take a time to help me with this issue; there is no answer on the crystal reports site or anywhere.
Thank you very much;
Celso Demendoncahttp://groups.google.com/group/microsoft.public.vb.crystal/browse_frm/thread/c6ad89366e85a75/9ae5cd1228234893?lnk=st&q=Printer+%22Job+Already+Started%22&rnum=4&hl=en#9ae5cd1228234893
Please take a time to help me with this issue; there is no answer on the crystal reports site or anywhere.
Thank you very much;
Celso Demendoncahttp://groups.google.com/group/microsoft.public.vb.crystal/browse_frm/thread/c6ad89366e85a75/9ae5cd1228234893?lnk=st&q=Printer+%22Job+Already+Started%22&rnum=4&hl=en#9ae5cd1228234893
Friday, February 24, 2012
JDBC with mulitple databases
Dear all,
Our application needs a bit of database redundancy.
Our application only accesses database for reading purposes.
We want to have two databases in separate machines. Incase one database
server dies the application should automatically extract data from the
other server.
Is there any JDBC driver available so that it can detect failure in the
main database server and then tries to extract data from the standby
database server?
Kind regards
--
Posted via http://dbforums.com
gbhatti wrote:
> Dear all,
> Our application needs a bit of database redundancy.
> Our application only accesses database for reading purposes.
> We want to have two databases in separate machines. Incase one database
> server dies the application should automatically extract data from the
> other server.
> Is there any JDBC driver available so that it can detect failure in the
> main database server and then tries to extract data from the standby
> database server?
> Kind regards
Not for SQLServer, but oracle has something like that if you have
oracle clustered DBMSes. However, it's not hard to write code
that will react to connectivity failure with one DBMS by connecting
to an alternate...
Joe Weinstein at BEA
> --
> Posted via http://dbforums.com|||"gbhatti" <member43992@.dbforums.com> wrote in message
news:3477901.1066098913@.dbforums.com...
> Dear all,
>
> Our application needs a bit of database redundancy.
>
> Our application only accesses database for reading purposes.
>
> We want to have two databases in separate machines. Incase one database
> server dies the application should automatically extract data from the
> other server.
>
> Is there any JDBC driver available so that it can detect failure in the
> main database server and then tries to extract data from the standby
> database server?
Not that I'm aware of. Typically this is done with SQL Enterprise in a
clustered format.
It is possible to encode it at the client level though also. But I'm not
aware of any JDBC driver that does it.
>
> Kind regards
>
> --
> Posted via http://dbforums.com
jdbc type 4 driver error
Mr Mugundan.......
My web application is running in jdk1.4, tomcat4, ms sql 2005 in win 2003 environment. I am using jdbc type 4 driver for sql server 2005 (com.microsoft.sqlserver.jdbc.SQLServerDriver, sqljdbc_1.2)database connection this gives an error even gives error in both sqljdbc1.1 and 1.2 versions...
please see the error message...
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
tomcat server error message begin
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1SELECT costct from costctmain where costct not in (select distinct mainct from costcts) 1
pageName : GENERAL_ERROR_PAGE
select count(*) from ( select P1.type, SUM(CASE P1.costct WHEN '11234578' THEN P1.total ELSE 0 END) AS a11234578, sum(P1.total)
w.project as type, e.costct, sum(t.tshours) as total from tswork t right outer join emp e on t.eid = e.eid join workmap w on t
ct pp on w.project = pp.pno where tsstatus in ('appr','subt') and tsupload = 'y' and e.costct in (11234578) and tsdate >= (sel
od where period = 'APR' and periodyear = 2007) group by e.costct, w.project union select PP.* from ( select t.jno as type, e.c
s total from tsjob t right outer join emp e on t.eid = e.eid where tsstatus in ('appr','subt') and tsupload = 'y' and e.costct
te >= (select startdate from period where period = 'APR' and periodyear = 2007) group by e.costct, t.jno ) as PP where type !=
y P1.type ) as P3
Error in executing query
com.microsoft.sqlserver.jdbc.SQLServerException: The IOBuffer.process operation returned an unknown packet type:-120. Index:70.
DS_DONE(-3) TDS_DONEPROC(-2) TDS_DONEINPROC(-1)
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.IOBuffer.processPackets(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.buildNextRowset(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StatementExecutionRequest.executeStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.CancelableRequest.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(Unknown Source)
at eTime.DBConnectManager.execQuery(DBConnectManager.java:198)
at reports.UtilisationReport.getData(UtilisationReport.java:333)
at reports.UtilisationReport.generateReport(UtilisationReport.java:109)
at reports.DivUtilisationReportServlet.getReportData(DivUtilisationReportServlet.java:230)
at reports.DivUtilisationReportServlet.doGet(DivUtilisationReportServlet.java:62)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:696)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:809)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:198)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:144)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:209)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:138)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2459)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:132)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:118)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:593)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:116)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:593)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:126)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:152)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Thread.java:534)
java.lang.NullPointerException
at reports.UtilisationReport.getData(UtilisationReport.java:335)
at reports.UtilisationReport.generateReport(UtilisationReport.java:109)
at reports.DivUtilisationReportServlet.getReportData(DivUtilisationReportServlet.java:230)
at reports.DivUtilisationReportServlet.doGet(DivUtilisationReportServlet.java:62)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:696)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:809)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:198)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:144)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:209)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:138)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2459)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:132)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:118)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:593)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:116)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:593)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:126)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:152)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Thread.java:534)
select count(*) from ( select aatype as type, SUM(CASE P2.costct WHEN '11234578'THEN P2.total ELSE 0 END) AS a11234578, SUM(P2.t
select aatype, costct, sum(tshours) as total from tsnr t join emp e on t.eid = e.eid where tsstatus in ('appr','subt') and tsup
in ('11234578') and tsdate >= (select startdate from period where period = 'APR' and periodyear = 2007) group by aatype, costct
) as P3
Error in executing query
com.microsoft.sqlserver.jdbc.SQLServerException: The IOBuffer.process operation returned an unknown packet type:-120. Index:70.
DS_DONE(-3) TDS_DONEPROC(-2) TDS_DONEINPROC(-1)
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.IOBuffer.processPackets(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.buildNextRowset(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StatementExecutionRequest.executeStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.CancelableRequest.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(Unknown Source)
at eTime.DBConnectManager.execQuery(DBConnectManager.java:198)
at reports.UtilisationReport.getData(UtilisationReport.java:333)
at reports.UtilisationReport.generateReport(UtilisationReport.java:133)
at reports.DivUtilisationReportServlet.getReportData(DivUtilisationReportServlet.java:230)
at reports.DivUtilisationReportServlet.doGet(DivUtilisationReportServlet.java:62)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:696)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:809)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:198)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:144)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:209)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:138)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2459)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:132)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:118)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:593)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:116)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:593)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:126)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:152)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Thread.java:534)
java.lang.NullPointerException
at reports.UtilisationReport.getData(UtilisationReport.java:335)
at reports.UtilisationReport.generateReport(UtilisationReport.java:133)
at reports.DivUtilisationReportServlet.getReportData(DivUtilisationReportServlet.java:230)
at reports.DivUtilisationReportServlet.doGet(DivUtilisationReportServlet.java:62)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:696)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:809)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:198)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:144)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:209)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:138)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2459)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:132)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:118)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:593)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:116)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:593)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:126)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:152)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Thread.java:534)
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
end of tomcat server error ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
but when i use sql server 2000 driver jdbc ("sun.jdbc.odbc.JdbcOdbcDriver"), It works fine but i need to use type 4 driver please help me to fix this issue.
advance many thanks.....
Could you provide me with the call stack for our 1.2 driver CTP? Is there any possibility for a finest level trace or an isolated repro we can debug this.|||Dear Sir,
Thanks for your reply,
My observation about this issue is ..........the following query is ok with "sun.jdbc.odbc.JdbcOdbcDriver" driver but no ok with "com.microsoft.sqlserver.jdbc.SQLServerDriver" (ms sql 2005 server).
The error is compute clause. If i remove the compute from the query it works fine, no issue. But if i use compute clause it gives error. I dnt know why. please help me.
"select P3.* from ( select P1.type, SUM(CASE P1.costct WHEN
'1015168' THEN P1.total ELSE 0 END) AS a1015168, sum(P1.total) as total from ( select w.project as t
ype, e.costct, sum(t.tshours) as total from tswork t right outer join emp e on t.eid = e.eid join w
orkmap w on t.wno = w.wbs join project pp on w.project = pp.pno where tsstatus in ('appr','subt') an
d tsupload = 'y' and e.costct in ('1015168') and tsdate >= (select startdate from period where perio
d = 'JAN' and periodyear = 2006) and tsdate <= (select enddate from period where period = 'JAN' and
periodyear = 2006) group by e.costct, w.project union select PP.* from ( select t.jno as type, e.co
stct, sum(t.tshours) as total from tsjob t right outer join emp e on t.eid = e.eid where tsstatus in
('appr','subt') and tsupload = 'y' and e.costct in ('1015168') and tsdate >= (select startdate from
period where period = 'JAN' and periodyear = 2006) and tsdate <= (select enddate from period where
period = 'JAN' and periodyear = 2006) group by e.costct, t.jno ) as PP where type != 'NULL' ) as P1
group by P1.type ) as P3 order by P3.type compute sum(a1015168), sum(Total)"
But feel that "sun.jdbc.odbc.JdbcOdbcDriver" is better than type 4 driver. Is any way to modify this.
thanks,
Baker07
|||
Thanks a lot for reporting this and your detailed information. I can repro this locally, we will aim to fix the issue in our next release. It looks like we are not handling the token coming from a compute call. We will aim to add this in our next release. Again thank for your efforts to report this issue.
|||Mr Mugund,
Is it possible to provide me a batch file to handle this error.
If possible... you can provide me...else i can wait for the next release.
thanks
|||One thing we are interested knowning is that have you been using some other driver or technology that supports the compute clause. How do they expose the result from the compute clause?
Thanks
Mugunthan
jdbc type 4 driver error
Mr Mugundan.......
My web application is running in jdk1.4, tomcat4, ms sql 2005 in win 2003 environment. I am using jdbc type 4 driver for sql server 2005 (com.microsoft.sqlserver.jdbc.SQLServerDriver, sqljdbc_1.2)database connection this gives an error even gives error in both sqljdbc1.1 and 1.2 versions...
please see the error message...
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
tomcat server error message begin
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1SELECT costct from costctmain where costct not in (select distinct mainct from costcts) 1
pageName : GENERAL_ERROR_PAGE
select count(*) from ( select P1.type, SUM(CASE P1.costct WHEN '11234578' THEN P1.total ELSE 0 END) AS a11234578, sum(P1.total)
w.project as type, e.costct, sum(t.tshours) as total from tswork t right outer join emp e on t.eid = e.eid join workmap w on t
ct pp on w.project = pp.pno where tsstatus in ('appr','subt') and tsupload = 'y' and e.costct in (11234578) and tsdate >= (sel
od where period = 'APR' and periodyear = 2007) group by e.costct, w.project union select PP.* from ( select t.jno as type, e.c
s total from tsjob t right outer join emp e on t.eid = e.eid where tsstatus in ('appr','subt') and tsupload = 'y' and e.costct
te >= (select startdate from period where period = 'APR' and periodyear = 2007) group by e.costct, t.jno ) as PP where type !=
y P1.type ) as P3
Error in executing query
com.microsoft.sqlserver.jdbc.SQLServerException: The IOBuffer.process operation returned an unknown packet type:-120. Index:70.
DS_DONE(-3) TDS_DONEPROC(-2) TDS_DONEINPROC(-1)
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.IOBuffer.processPackets(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.buildNextRowset(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StatementExecutionRequest.executeStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.CancelableRequest.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(Unknown Source)
at eTime.DBConnectManager.execQuery(DBConnectManager.java:198)
at reports.UtilisationReport.getData(UtilisationReport.java:333)
at reports.UtilisationReport.generateReport(UtilisationReport.java:109)
at reports.DivUtilisationReportServlet.getReportData(DivUtilisationReportServlet.java:230)
at reports.DivUtilisationReportServlet.doGet(DivUtilisationReportServlet.java:62)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:696)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:809)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:198)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:144)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:209)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:138)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2459)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:132)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:118)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:593)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:116)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:593)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:126)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:152)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Thread.java:534)
java.lang.NullPointerException
at reports.UtilisationReport.getData(UtilisationReport.java:335)
at reports.UtilisationReport.generateReport(UtilisationReport.java:109)
at reports.DivUtilisationReportServlet.getReportData(DivUtilisationReportServlet.java:230)
at reports.DivUtilisationReportServlet.doGet(DivUtilisationReportServlet.java:62)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:696)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:809)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:198)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:144)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:209)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:138)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2459)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:132)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:118)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:593)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:116)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:593)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:126)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:152)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Thread.java:534)
select count(*) from ( select aatype as type, SUM(CASE P2.costct WHEN '11234578'THEN P2.total ELSE 0 END) AS a11234578, SUM(P2.t
select aatype, costct, sum(tshours) as total from tsnr t join emp e on t.eid = e.eid where tsstatus in ('appr','subt') and tsup
in ('11234578') and tsdate >= (select startdate from period where period = 'APR' and periodyear = 2007) group by aatype, costct
) as P3
Error in executing query
com.microsoft.sqlserver.jdbc.SQLServerException: The IOBuffer.process operation returned an unknown packet type:-120. Index:70.
DS_DONE(-3) TDS_DONEPROC(-2) TDS_DONEINPROC(-1)
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.IOBuffer.processPackets(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.buildNextRowset(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StatementExecutionRequest.executeStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.CancelableRequest.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(Unknown Source)
at eTime.DBConnectManager.execQuery(DBConnectManager.java:198)
at reports.UtilisationReport.getData(UtilisationReport.java:333)
at reports.UtilisationReport.generateReport(UtilisationReport.java:133)
at reports.DivUtilisationReportServlet.getReportData(DivUtilisationReportServlet.java:230)
at reports.DivUtilisationReportServlet.doGet(DivUtilisationReportServlet.java:62)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:696)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:809)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:198)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:144)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:209)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:138)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2459)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:132)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:118)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:593)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:116)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:593)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:126)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:152)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Thread.java:534)
java.lang.NullPointerException
at reports.UtilisationReport.getData(UtilisationReport.java:335)
at reports.UtilisationReport.generateReport(UtilisationReport.java:133)
at reports.DivUtilisationReportServlet.getReportData(DivUtilisationReportServlet.java:230)
at reports.DivUtilisationReportServlet.doGet(DivUtilisationReportServlet.java:62)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:696)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:809)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:198)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:144)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:209)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:138)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2459)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:132)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:118)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:593)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:116)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:593)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:126)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:595)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:432)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:954)
at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:152)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Thread.java:534)
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
end of tomcat server error ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
but when i use sql server 2000 driver jdbc ("sun.jdbc.odbc.JdbcOdbcDriver"), It works fine but i need to use type 4 driver please help me to fix this issue.
advance many thanks.....
Could you provide me with the call stack for our 1.2 driver CTP? Is there any possibility for a finest level trace or an isolated repro we can debug this.|||Dear Sir,
Thanks for your reply,
My observation about this issue is ..........the following query is ok with "sun.jdbc.odbc.JdbcOdbcDriver" driver but no ok with "com.microsoft.sqlserver.jdbc.SQLServerDriver" (ms sql 2005 server).
The error is compute clause. If i remove the compute from the query it works fine, no issue. But if i use compute clause it gives error. I dnt know why. please help me.
"select P3.* from ( select P1.type, SUM(CASE P1.costct WHEN
'1015168' THEN P1.total ELSE 0 END) AS a1015168, sum(P1.total) as total from ( select w.project as t
ype, e.costct, sum(t.tshours) as total from tswork t right outer join emp e on t.eid = e.eid join w
orkmap w on t.wno = w.wbs join project pp on w.project = pp.pno where tsstatus in ('appr','subt') an
d tsupload = 'y' and e.costct in ('1015168') and tsdate >= (select startdate from period where perio
d = 'JAN' and periodyear = 2006) and tsdate <= (select enddate from period where period = 'JAN' and
periodyear = 2006) group by e.costct, w.project union select PP.* from ( select t.jno as type, e.co
stct, sum(t.tshours) as total from tsjob t right outer join emp e on t.eid = e.eid where tsstatus in
('appr','subt') and tsupload = 'y' and e.costct in ('1015168') and tsdate >= (select startdate from
period where period = 'JAN' and periodyear = 2006) and tsdate <= (select enddate from period where
period = 'JAN' and periodyear = 2006) group by e.costct, t.jno ) as PP where type != 'NULL' ) as P1
group by P1.type ) as P3 order by P3.type compute sum(a1015168), sum(Total)"
But feel that "sun.jdbc.odbc.JdbcOdbcDriver" is better than type 4 driver. Is any way to modify this.
thanks,
Baker07
|||
Thanks a lot for reporting this and your detailed information. I can repro this locally, we will aim to fix the issue in our next release. It looks like we are not handling the token coming from a compute call. We will aim to add this in our next release. Again thank for your efforts to report this issue.
|||Mr Mugund,
Is it possible to provide me a batch file to handle this error.
If possible... you can provide me...else i can wait for the next release.
thanks
|||One thing we are interested knowning is that have you been using some other driver or technology that supports the compute clause. How do they expose the result from the compute clause?
Thanks
Mugunthan
JDBC slow in the morning?!
Hello,
I have a strange problem...
I manage a web application on Tomcat, Struts, SQLServer with the last
jdbc driver from Microsoft.
So, each morning one stored proc is very slow (around 20 sec to
execute) and around 10:00 AM, the stored proc accelerate (around 500
ms)!!
This stored proc is executing one query, and if necessary, insert
records for caching in a cache table.
Here is my code...
/*******************************************************************
* DocumentsOfEDMChanelForUser -
EXEC DocumentsOfEDMChanelForUser 10, 4, 576, 3
TRUNCATE TABLE cache_EDMRights
********************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DocumentsOfEDMChanelForUser]') and
OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DocumentsOfEDMChanelForUser]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE DocumentsOfEDMChanelForUser
@.TopCount int
,@.LanguageOID int
,@.SecurityUserOID int
,@.ChanelOID int
AS
SET NOCOUNT ON
SELECT DISTINCT
Document.DOCUMENT_PK AS OID,
dbo.getLexiconText(Document.DisplayName, @.LanguageOID, '') AS
DocumentName,
dbo.getLexiconText(Document.Description, @.LanguageOID, '') AS
Description,
Document.LastModify AS CreationDate,
dbo.getFolderPath(Document.Folder, @.LanguageOID, '/') AS
FolderPath,
dbo.getIsDocumentReadedByUser(Document.DOCUMENT_PK,
@.SecurityUserOID) AS IsReaded,
dbo.getDocumentRights(@.SecurityUserOID, Document.DOCUMENT_PK,
0, 1) AS Rights
INTO
#ResultSet
FROM
Document Document INNER JOIN
EDMChanel EDMChanel ON Document.EDMChanel = EDMChanel.EDMCHANEL_CHANEL_IPK INNER JOIN
CodeBasis CodeBasis ON EDMChanel.PeriodType = CodeBasis.CODEBASIS_PK
WHERE
Document.EDMChanel = @.ChanelOID AND
Document.Active = 1 AND
CASE
WHEN CodeBasis.Code = 'PeriodType.Day' THEN DATEADD(day,
EDMChanel.Period, Document.LastModify)
WHEN CodeBasis.Code = 'PeriodType.Week' THEN DATEADD(week,
EDMChanel.Period, Document.LastModify)
WHEN CodeBasis.Code = 'PeriodType.Month' THEN
DATEADD(month, EDMChanel.Period, Document.LastModify)
WHEN CodeBasis.Code = 'PeriodType.Year' THEN DATEADD(year,
EDMChanel.Period, Document.LastModify)
END >= getdate()
--ORDER BY
-- CreationDate DESC
IF EXISTS(SELECT 1 FROM #ResultSet WHERE (Rights & 65536) > 0)
BEGIN
-- LEFT JOIN with cache because there are index violation with
insert
INSERT INTO cache_EDMRights
SELECT
0, @.SecurityUserOID, R.OID, R.Rights ^ 65536
FROM
#ResultSet R LEFT OUTER JOIN
cache_EDMRights C ON IsFolder = 0 AND C.SecurityUser = @.SecurityUserOID AND C.OID = R.OID
WHERE
C.OID IS NULL AND
(R.Rights & 65536) > 0
UPDATE #ResultSet SET Rights = Rights ^ 65536 WHERE (Rights &
65536) > 0
END
DECLARE @.SQL varchar(5000)
SET @.SQL = 'SELECT TOP ' + cast(@.TopCount as varchar(10)) + ' *
FROM #ResultSet WHERE Rights > 0 ORDER BY CreationDate DESC'
EXEC(@.SQL)
DROP TABLE #ResultSet
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
I'm not understanding?!
Anyone has idea?
Thanks for help.Hi
How do you know that it's JDBC issue?
Have you ran SQL Server Profiler to track down what is going on during the
execution?
Also ,I'd create a temporary table by CREATE TABLE #Temp and not by SELECT *
INTO ...
Another point, it might be a bad idea to use UDF with a large set data,
because it performs row-by-row process similar how cursors work.
"crabouif" <pascal_fluck@.hotmail.com> wrote in message
news:109014db.0411240054.4fc04476@.posting.google.com...
> Hello,
> I have a strange problem...
> I manage a web application on Tomcat, Struts, SQLServer with the last
> jdbc driver from Microsoft.
> So, each morning one stored proc is very slow (around 20 sec to
> execute) and around 10:00 AM, the stored proc accelerate (around 500
> ms)!!
> This stored proc is executing one query, and if necessary, insert
> records for caching in a cache table.
> Here is my code...
> /*******************************************************************
> * DocumentsOfEDMChanelForUser -
> EXEC DocumentsOfEDMChanelForUser 10, 4, 576, 3
> TRUNCATE TABLE cache_EDMRights
> ********************************************************************/
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[DocumentsOfEDMChanelForUser]') and
> OBJECTPROPERTY(id, N'IsProcedure') = 1)
> drop procedure [dbo].[DocumentsOfEDMChanelForUser]
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
>
> CREATE PROCEDURE DocumentsOfEDMChanelForUser
> @.TopCount int
> ,@.LanguageOID int
> ,@.SecurityUserOID int
> ,@.ChanelOID int
> AS
> SET NOCOUNT ON
> SELECT DISTINCT
> Document.DOCUMENT_PK AS OID,
> dbo.getLexiconText(Document.DisplayName, @.LanguageOID, '') AS
> DocumentName,
> dbo.getLexiconText(Document.Description, @.LanguageOID, '') AS
> Description,
> Document.LastModify AS CreationDate,
> dbo.getFolderPath(Document.Folder, @.LanguageOID, '/') AS
> FolderPath,
> dbo.getIsDocumentReadedByUser(Document.DOCUMENT_PK,
> @.SecurityUserOID) AS IsReaded,
> dbo.getDocumentRights(@.SecurityUserOID, Document.DOCUMENT_PK,
> 0, 1) AS Rights
> INTO
> #ResultSet
> FROM
> Document Document INNER JOIN
> EDMChanel EDMChanel ON Document.EDMChanel => EDMChanel.EDMCHANEL_CHANEL_IPK INNER JOIN
> CodeBasis CodeBasis ON EDMChanel.PeriodType => CodeBasis.CODEBASIS_PK
> WHERE
> Document.EDMChanel = @.ChanelOID AND
> Document.Active = 1 AND
> CASE
> WHEN CodeBasis.Code = 'PeriodType.Day' THEN DATEADD(day,
> EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Week' THEN DATEADD(week,
> EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Month' THEN
> DATEADD(month, EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Year' THEN DATEADD(year,
> EDMChanel.Period, Document.LastModify)
> END >= getdate()
> --ORDER BY
> -- CreationDate DESC
> IF EXISTS(SELECT 1 FROM #ResultSet WHERE (Rights & 65536) > 0)
> BEGIN
> -- LEFT JOIN with cache because there are index violation with
> insert
> INSERT INTO cache_EDMRights
> SELECT
> 0, @.SecurityUserOID, R.OID, R.Rights ^ 65536
> FROM
> #ResultSet R LEFT OUTER JOIN
> cache_EDMRights C ON IsFolder = 0 AND C.SecurityUser => @.SecurityUserOID AND C.OID = R.OID
> WHERE
> C.OID IS NULL AND
> (R.Rights & 65536) > 0
> UPDATE #ResultSet SET Rights = Rights ^ 65536 WHERE (Rights &
> 65536) > 0
> END
> DECLARE @.SQL varchar(5000)
> SET @.SQL = 'SELECT TOP ' + cast(@.TopCount as varchar(10)) + ' *
> FROM #ResultSet WHERE Rights > 0 ORDER BY CreationDate DESC'
> EXEC(@.SQL)
> DROP TABLE #ResultSet
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> I'm not understanding?!
> Anyone has idea?
> Thanks for help.|||As Uri already indicated, you may not want to assume it's
just a JDBC issue. In addition to profiler, you would want
to check the activity on the server (using PerfMon) as well
as the activity in SQL Server. When something runs slow at
particular times, it's often due to other activities on the
server or in SQL Server that are using a lot of resources.
-Sue
On 24 Nov 2004 00:54:00 -0800, pascal_fluck@.hotmail.com
(crabouif) wrote:
>Hello,
>I have a strange problem...
>I manage a web application on Tomcat, Struts, SQLServer with the last
>jdbc driver from Microsoft.
>So, each morning one stored proc is very slow (around 20 sec to
>execute) and around 10:00 AM, the stored proc accelerate (around 500
>ms)!!
>This stored proc is executing one query, and if necessary, insert
>records for caching in a cache table.
>Here is my code...
>/*******************************************************************
>* DocumentsOfEDMChanelForUser -
>EXEC DocumentsOfEDMChanelForUser 10, 4, 576, 3
>TRUNCATE TABLE cache_EDMRights
>********************************************************************/
>if exists (select * from dbo.sysobjects where id =>object_id(N'[dbo].[DocumentsOfEDMChanelForUser]') and
>OBJECTPROPERTY(id, N'IsProcedure') = 1)
>drop procedure [dbo].[DocumentsOfEDMChanelForUser]
>GO
>SET QUOTED_IDENTIFIER ON
>GO
>SET ANSI_NULLS ON
>GO
>
>CREATE PROCEDURE DocumentsOfEDMChanelForUser
> @.TopCount int
> ,@.LanguageOID int
> ,@.SecurityUserOID int
> ,@.ChanelOID int
>AS
> SET NOCOUNT ON
> SELECT DISTINCT
> Document.DOCUMENT_PK AS OID,
> dbo.getLexiconText(Document.DisplayName, @.LanguageOID, '') AS
>DocumentName,
> dbo.getLexiconText(Document.Description, @.LanguageOID, '') AS
>Description,
> Document.LastModify AS CreationDate,
> dbo.getFolderPath(Document.Folder, @.LanguageOID, '/') AS
>FolderPath,
> dbo.getIsDocumentReadedByUser(Document.DOCUMENT_PK,
>@.SecurityUserOID) AS IsReaded,
> dbo.getDocumentRights(@.SecurityUserOID, Document.DOCUMENT_PK,
>0, 1) AS Rights
> INTO
> #ResultSet
> FROM
> Document Document INNER JOIN
> EDMChanel EDMChanel ON Document.EDMChanel =>EDMChanel.EDMCHANEL_CHANEL_IPK INNER JOIN
> CodeBasis CodeBasis ON EDMChanel.PeriodType =>CodeBasis.CODEBASIS_PK
> WHERE
> Document.EDMChanel = @.ChanelOID AND
> Document.Active = 1 AND
> CASE
> WHEN CodeBasis.Code = 'PeriodType.Day' THEN DATEADD(day,
>EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Week' THEN DATEADD(week,
>EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Month' THEN
>DATEADD(month, EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Year' THEN DATEADD(year,
>EDMChanel.Period, Document.LastModify)
> END >= getdate()
> --ORDER BY
> -- CreationDate DESC
> IF EXISTS(SELECT 1 FROM #ResultSet WHERE (Rights & 65536) > 0)
> BEGIN
> -- LEFT JOIN with cache because there are index violation with
>insert
> INSERT INTO cache_EDMRights
> SELECT
> 0, @.SecurityUserOID, R.OID, R.Rights ^ 65536
> FROM
> #ResultSet R LEFT OUTER JOIN
> cache_EDMRights C ON IsFolder = 0 AND C.SecurityUser =>@.SecurityUserOID AND C.OID = R.OID
> WHERE
> C.OID IS NULL AND
> (R.Rights & 65536) > 0
> UPDATE #ResultSet SET Rights = Rights ^ 65536 WHERE (Rights &
>65536) > 0
> END
> DECLARE @.SQL varchar(5000)
> SET @.SQL = 'SELECT TOP ' + cast(@.TopCount as varchar(10)) + ' *
>FROM #ResultSet WHERE Rights > 0 ORDER BY CreationDate DESC'
> EXEC(@.SQL)
> DROP TABLE #ResultSet
>GO
>SET QUOTED_IDENTIFIER OFF
>GO
>SET ANSI_NULLS ON
>GO
>I'm not understanding?!
>Anyone has idea?
>Thanks for help.|||Well, I endly solve my problem.
So I check that I had an index is breaked. And in 10:00 AM I had an
agent who reindex it.
Thanks for your idees, I solve that problem with profiler...
But what I dont explain is why with JDBC (respectively my application
and Aqua Data Studio) it was slow, and with MS Query Analyser it was
quick...
Thanks one more for you help.
Pascal
Sue Hoegemeier <Sue_H@.nomail.please> wrote in message news:<sn99q0p7d6ii61m735s2mdt4eed5h23q18@.4ax.com>...
> As Uri already indicated, you may not want to assume it's
> just a JDBC issue. In addition to profiler, you would want
> to check the activity on the server (using PerfMon) as well
> as the activity in SQL Server. When something runs slow at
> particular times, it's often due to other activities on the
> server or in SQL Server that are using a lot of resources.
> -Sue
> On 24 Nov 2004 00:54:00 -0800, pascal_fluck@.hotmail.com
> (crabouif) wrote:
> >Hello,
> >
> >I have a strange problem...
> >
> >I manage a web application on Tomcat, Struts, SQLServer with the last
> >jdbc driver from Microsoft.
> >
> >So, each morning one stored proc is very slow (around 20 sec to
> >execute) and around 10:00 AM, the stored proc accelerate (around 500
> >ms)!!
> >
> >This stored proc is executing one query, and if necessary, insert
> >records for caching in a cache table.
> >
> >Here is my code...
> >
> >/*******************************************************************
> >* DocumentsOfEDMChanelForUser -
> >EXEC DocumentsOfEDMChanelForUser 10, 4, 576, 3
> >TRUNCATE TABLE cache_EDMRights
> >********************************************************************/
> >if exists (select * from dbo.sysobjects where id => >object_id(N'[dbo].[DocumentsOfEDMChanelForUser]') and
> >OBJECTPROPERTY(id, N'IsProcedure') = 1)
> >drop procedure [dbo].[DocumentsOfEDMChanelForUser]
> >GO
> >
> >SET QUOTED_IDENTIFIER ON
> >GO
> >SET ANSI_NULLS ON
> >GO
> >
> >
> >CREATE PROCEDURE DocumentsOfEDMChanelForUser
> > @.TopCount int
> > ,@.LanguageOID int
> > ,@.SecurityUserOID int
> > ,@.ChanelOID int
> >AS
> > SET NOCOUNT ON
> >
> > SELECT DISTINCT
> > Document.DOCUMENT_PK AS OID,
> > dbo.getLexiconText(Document.DisplayName, @.LanguageOID, '') AS
> >DocumentName,
> > dbo.getLexiconText(Document.Description, @.LanguageOID, '') AS
> >Description,
> > Document.LastModify AS CreationDate,
> > dbo.getFolderPath(Document.Folder, @.LanguageOID, '/') AS
> >FolderPath,
> > dbo.getIsDocumentReadedByUser(Document.DOCUMENT_PK,
> >@.SecurityUserOID) AS IsReaded,
> > dbo.getDocumentRights(@.SecurityUserOID, Document.DOCUMENT_PK,
> >0, 1) AS Rights
> > INTO
> > #ResultSet
> > FROM
> > Document Document INNER JOIN
> > EDMChanel EDMChanel ON Document.EDMChanel => >EDMChanel.EDMCHANEL_CHANEL_IPK INNER JOIN
> > CodeBasis CodeBasis ON EDMChanel.PeriodType => >CodeBasis.CODEBASIS_PK
> > WHERE
> > Document.EDMChanel = @.ChanelOID AND
> > Document.Active = 1 AND
> > CASE
> > WHEN CodeBasis.Code = 'PeriodType.Day' THEN DATEADD(day,
> >EDMChanel.Period, Document.LastModify)
> > WHEN CodeBasis.Code = 'PeriodType.Week' THEN DATEADD(week,
> >EDMChanel.Period, Document.LastModify)
> > WHEN CodeBasis.Code = 'PeriodType.Month' THEN
> >DATEADD(month, EDMChanel.Period, Document.LastModify)
> > WHEN CodeBasis.Code = 'PeriodType.Year' THEN DATEADD(year,
> >EDMChanel.Period, Document.LastModify)
> > END >= getdate()
> > --ORDER BY
> > -- CreationDate DESC
> >
> > IF EXISTS(SELECT 1 FROM #ResultSet WHERE (Rights & 65536) > 0)
> > BEGIN
> > -- LEFT JOIN with cache because there are index violation with
> >insert
> > INSERT INTO cache_EDMRights
> > SELECT
> > 0, @.SecurityUserOID, R.OID, R.Rights ^ 65536
> > FROM
> > #ResultSet R LEFT OUTER JOIN
> > cache_EDMRights C ON IsFolder = 0 AND C.SecurityUser => >@.SecurityUserOID AND C.OID = R.OID
> > WHERE
> > C.OID IS NULL AND
> > (R.Rights & 65536) > 0
> > UPDATE #ResultSet SET Rights = Rights ^ 65536 WHERE (Rights &
> >65536) > 0
> > END
> >
> > DECLARE @.SQL varchar(5000)
> > SET @.SQL = 'SELECT TOP ' + cast(@.TopCount as varchar(10)) + ' *
> >FROM #ResultSet WHERE Rights > 0 ORDER BY CreationDate DESC'
> > EXEC(@.SQL)
> > DROP TABLE #ResultSet
> >GO
> >SET QUOTED_IDENTIFIER OFF
> >GO
> >SET ANSI_NULLS ON
> >GO
> >
> >I'm not understanding?!
> >
> >Anyone has idea?
> >
> >Thanks for help.|||One thing is that you could have been seeing the effects of
caching and in combination with calling the stored procedure
differently in the two scenarios. You can monitor the
caching - cache hits, cache misses, etc. using Profiler.
-Sue
On 25 Nov 2004 22:58:40 -0800, pascal_fluck@.hotmail.com
(crabouif) wrote:
>Well, I endly solve my problem.
>So I check that I had an index is breaked. And in 10:00 AM I had an
>agent who reindex it.
>Thanks for your idees, I solve that problem with profiler...
>But what I dont explain is why with JDBC (respectively my application
>and Aqua Data Studio) it was slow, and with MS Query Analyser it was
>quick...
>Thanks one more for you help.
> Pascal
>
>Sue Hoegemeier <Sue_H@.nomail.please> wrote in message news:<sn99q0p7d6ii61m735s2mdt4eed5h23q18@.4ax.com>...
>> As Uri already indicated, you may not want to assume it's
>> just a JDBC issue. In addition to profiler, you would want
>> to check the activity on the server (using PerfMon) as well
>> as the activity in SQL Server. When something runs slow at
>> particular times, it's often due to other activities on the
>> server or in SQL Server that are using a lot of resources.
>> -Sue
>> On 24 Nov 2004 00:54:00 -0800, pascal_fluck@.hotmail.com
>> (crabouif) wrote:
>> >Hello,
>> >
>> >I have a strange problem...
>> >
>> >I manage a web application on Tomcat, Struts, SQLServer with the last
>> >jdbc driver from Microsoft.
>> >
>> >So, each morning one stored proc is very slow (around 20 sec to
>> >execute) and around 10:00 AM, the stored proc accelerate (around 500
>> >ms)!!
>> >
>> >This stored proc is executing one query, and if necessary, insert
>> >records for caching in a cache table.
>> >
>> >Here is my code...
>> >
>> >/*******************************************************************
>> >* DocumentsOfEDMChanelForUser -
>> >EXEC DocumentsOfEDMChanelForUser 10, 4, 576, 3
>> >TRUNCATE TABLE cache_EDMRights
>> >********************************************************************/
>> >if exists (select * from dbo.sysobjects where id =>> >object_id(N'[dbo].[DocumentsOfEDMChanelForUser]') and
>> >OBJECTPROPERTY(id, N'IsProcedure') = 1)
>> >drop procedure [dbo].[DocumentsOfEDMChanelForUser]
>> >GO
>> >
>> >SET QUOTED_IDENTIFIER ON
>> >GO
>> >SET ANSI_NULLS ON
>> >GO
>> >
>> >
>> >CREATE PROCEDURE DocumentsOfEDMChanelForUser
>> > @.TopCount int
>> > ,@.LanguageOID int
>> > ,@.SecurityUserOID int
>> > ,@.ChanelOID int
>> >AS
>> > SET NOCOUNT ON
>> >
>> > SELECT DISTINCT
>> > Document.DOCUMENT_PK AS OID,
>> > dbo.getLexiconText(Document.DisplayName, @.LanguageOID, '') AS
>> >DocumentName,
>> > dbo.getLexiconText(Document.Description, @.LanguageOID, '') AS
>> >Description,
>> > Document.LastModify AS CreationDate,
>> > dbo.getFolderPath(Document.Folder, @.LanguageOID, '/') AS
>> >FolderPath,
>> > dbo.getIsDocumentReadedByUser(Document.DOCUMENT_PK,
>> >@.SecurityUserOID) AS IsReaded,
>> > dbo.getDocumentRights(@.SecurityUserOID, Document.DOCUMENT_PK,
>> >0, 1) AS Rights
>> > INTO
>> > #ResultSet
>> > FROM
>> > Document Document INNER JOIN
>> > EDMChanel EDMChanel ON Document.EDMChanel =>> >EDMChanel.EDMCHANEL_CHANEL_IPK INNER JOIN
>> > CodeBasis CodeBasis ON EDMChanel.PeriodType =>> >CodeBasis.CODEBASIS_PK
>> > WHERE
>> > Document.EDMChanel = @.ChanelOID AND
>> > Document.Active = 1 AND
>> > CASE
>> > WHEN CodeBasis.Code = 'PeriodType.Day' THEN DATEADD(day,
>> >EDMChanel.Period, Document.LastModify)
>> > WHEN CodeBasis.Code = 'PeriodType.Week' THEN DATEADD(week,
>> >EDMChanel.Period, Document.LastModify)
>> > WHEN CodeBasis.Code = 'PeriodType.Month' THEN
>> >DATEADD(month, EDMChanel.Period, Document.LastModify)
>> > WHEN CodeBasis.Code = 'PeriodType.Year' THEN DATEADD(year,
>> >EDMChanel.Period, Document.LastModify)
>> > END >= getdate()
>> > --ORDER BY
>> > -- CreationDate DESC
>> >
>> > IF EXISTS(SELECT 1 FROM #ResultSet WHERE (Rights & 65536) > 0)
>> > BEGIN
>> > -- LEFT JOIN with cache because there are index violation with
>> >insert
>> > INSERT INTO cache_EDMRights
>> > SELECT
>> > 0, @.SecurityUserOID, R.OID, R.Rights ^ 65536
>> > FROM
>> > #ResultSet R LEFT OUTER JOIN
>> > cache_EDMRights C ON IsFolder = 0 AND C.SecurityUser =>> >@.SecurityUserOID AND C.OID = R.OID
>> > WHERE
>> > C.OID IS NULL AND
>> > (R.Rights & 65536) > 0
>> > UPDATE #ResultSet SET Rights = Rights ^ 65536 WHERE (Rights &
>> >65536) > 0
>> > END
>> >
>> > DECLARE @.SQL varchar(5000)
>> > SET @.SQL = 'SELECT TOP ' + cast(@.TopCount as varchar(10)) + ' *
>> >FROM #ResultSet WHERE Rights > 0 ORDER BY CreationDate DESC'
>> > EXEC(@.SQL)
>> > DROP TABLE #ResultSet
>> >GO
>> >SET QUOTED_IDENTIFIER OFF
>> >GO
>> >SET ANSI_NULLS ON
>> >GO
>> >
>> >I'm not understanding?!
>> >
>> >Anyone has idea?
>> >
>> >Thanks for help.
I have a strange problem...
I manage a web application on Tomcat, Struts, SQLServer with the last
jdbc driver from Microsoft.
So, each morning one stored proc is very slow (around 20 sec to
execute) and around 10:00 AM, the stored proc accelerate (around 500
ms)!!
This stored proc is executing one query, and if necessary, insert
records for caching in a cache table.
Here is my code...
/*******************************************************************
* DocumentsOfEDMChanelForUser -
EXEC DocumentsOfEDMChanelForUser 10, 4, 576, 3
TRUNCATE TABLE cache_EDMRights
********************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DocumentsOfEDMChanelForUser]') and
OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DocumentsOfEDMChanelForUser]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE DocumentsOfEDMChanelForUser
@.TopCount int
,@.LanguageOID int
,@.SecurityUserOID int
,@.ChanelOID int
AS
SET NOCOUNT ON
SELECT DISTINCT
Document.DOCUMENT_PK AS OID,
dbo.getLexiconText(Document.DisplayName, @.LanguageOID, '') AS
DocumentName,
dbo.getLexiconText(Document.Description, @.LanguageOID, '') AS
Description,
Document.LastModify AS CreationDate,
dbo.getFolderPath(Document.Folder, @.LanguageOID, '/') AS
FolderPath,
dbo.getIsDocumentReadedByUser(Document.DOCUMENT_PK,
@.SecurityUserOID) AS IsReaded,
dbo.getDocumentRights(@.SecurityUserOID, Document.DOCUMENT_PK,
0, 1) AS Rights
INTO
#ResultSet
FROM
Document Document INNER JOIN
EDMChanel EDMChanel ON Document.EDMChanel = EDMChanel.EDMCHANEL_CHANEL_IPK INNER JOIN
CodeBasis CodeBasis ON EDMChanel.PeriodType = CodeBasis.CODEBASIS_PK
WHERE
Document.EDMChanel = @.ChanelOID AND
Document.Active = 1 AND
CASE
WHEN CodeBasis.Code = 'PeriodType.Day' THEN DATEADD(day,
EDMChanel.Period, Document.LastModify)
WHEN CodeBasis.Code = 'PeriodType.Week' THEN DATEADD(week,
EDMChanel.Period, Document.LastModify)
WHEN CodeBasis.Code = 'PeriodType.Month' THEN
DATEADD(month, EDMChanel.Period, Document.LastModify)
WHEN CodeBasis.Code = 'PeriodType.Year' THEN DATEADD(year,
EDMChanel.Period, Document.LastModify)
END >= getdate()
--ORDER BY
-- CreationDate DESC
IF EXISTS(SELECT 1 FROM #ResultSet WHERE (Rights & 65536) > 0)
BEGIN
-- LEFT JOIN with cache because there are index violation with
insert
INSERT INTO cache_EDMRights
SELECT
0, @.SecurityUserOID, R.OID, R.Rights ^ 65536
FROM
#ResultSet R LEFT OUTER JOIN
cache_EDMRights C ON IsFolder = 0 AND C.SecurityUser = @.SecurityUserOID AND C.OID = R.OID
WHERE
C.OID IS NULL AND
(R.Rights & 65536) > 0
UPDATE #ResultSet SET Rights = Rights ^ 65536 WHERE (Rights &
65536) > 0
END
DECLARE @.SQL varchar(5000)
SET @.SQL = 'SELECT TOP ' + cast(@.TopCount as varchar(10)) + ' *
FROM #ResultSet WHERE Rights > 0 ORDER BY CreationDate DESC'
EXEC(@.SQL)
DROP TABLE #ResultSet
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
I'm not understanding?!
Anyone has idea?
Thanks for help.Hi
How do you know that it's JDBC issue?
Have you ran SQL Server Profiler to track down what is going on during the
execution?
Also ,I'd create a temporary table by CREATE TABLE #Temp and not by SELECT *
INTO ...
Another point, it might be a bad idea to use UDF with a large set data,
because it performs row-by-row process similar how cursors work.
"crabouif" <pascal_fluck@.hotmail.com> wrote in message
news:109014db.0411240054.4fc04476@.posting.google.com...
> Hello,
> I have a strange problem...
> I manage a web application on Tomcat, Struts, SQLServer with the last
> jdbc driver from Microsoft.
> So, each morning one stored proc is very slow (around 20 sec to
> execute) and around 10:00 AM, the stored proc accelerate (around 500
> ms)!!
> This stored proc is executing one query, and if necessary, insert
> records for caching in a cache table.
> Here is my code...
> /*******************************************************************
> * DocumentsOfEDMChanelForUser -
> EXEC DocumentsOfEDMChanelForUser 10, 4, 576, 3
> TRUNCATE TABLE cache_EDMRights
> ********************************************************************/
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[DocumentsOfEDMChanelForUser]') and
> OBJECTPROPERTY(id, N'IsProcedure') = 1)
> drop procedure [dbo].[DocumentsOfEDMChanelForUser]
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
>
> CREATE PROCEDURE DocumentsOfEDMChanelForUser
> @.TopCount int
> ,@.LanguageOID int
> ,@.SecurityUserOID int
> ,@.ChanelOID int
> AS
> SET NOCOUNT ON
> SELECT DISTINCT
> Document.DOCUMENT_PK AS OID,
> dbo.getLexiconText(Document.DisplayName, @.LanguageOID, '') AS
> DocumentName,
> dbo.getLexiconText(Document.Description, @.LanguageOID, '') AS
> Description,
> Document.LastModify AS CreationDate,
> dbo.getFolderPath(Document.Folder, @.LanguageOID, '/') AS
> FolderPath,
> dbo.getIsDocumentReadedByUser(Document.DOCUMENT_PK,
> @.SecurityUserOID) AS IsReaded,
> dbo.getDocumentRights(@.SecurityUserOID, Document.DOCUMENT_PK,
> 0, 1) AS Rights
> INTO
> #ResultSet
> FROM
> Document Document INNER JOIN
> EDMChanel EDMChanel ON Document.EDMChanel => EDMChanel.EDMCHANEL_CHANEL_IPK INNER JOIN
> CodeBasis CodeBasis ON EDMChanel.PeriodType => CodeBasis.CODEBASIS_PK
> WHERE
> Document.EDMChanel = @.ChanelOID AND
> Document.Active = 1 AND
> CASE
> WHEN CodeBasis.Code = 'PeriodType.Day' THEN DATEADD(day,
> EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Week' THEN DATEADD(week,
> EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Month' THEN
> DATEADD(month, EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Year' THEN DATEADD(year,
> EDMChanel.Period, Document.LastModify)
> END >= getdate()
> --ORDER BY
> -- CreationDate DESC
> IF EXISTS(SELECT 1 FROM #ResultSet WHERE (Rights & 65536) > 0)
> BEGIN
> -- LEFT JOIN with cache because there are index violation with
> insert
> INSERT INTO cache_EDMRights
> SELECT
> 0, @.SecurityUserOID, R.OID, R.Rights ^ 65536
> FROM
> #ResultSet R LEFT OUTER JOIN
> cache_EDMRights C ON IsFolder = 0 AND C.SecurityUser => @.SecurityUserOID AND C.OID = R.OID
> WHERE
> C.OID IS NULL AND
> (R.Rights & 65536) > 0
> UPDATE #ResultSet SET Rights = Rights ^ 65536 WHERE (Rights &
> 65536) > 0
> END
> DECLARE @.SQL varchar(5000)
> SET @.SQL = 'SELECT TOP ' + cast(@.TopCount as varchar(10)) + ' *
> FROM #ResultSet WHERE Rights > 0 ORDER BY CreationDate DESC'
> EXEC(@.SQL)
> DROP TABLE #ResultSet
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> I'm not understanding?!
> Anyone has idea?
> Thanks for help.|||As Uri already indicated, you may not want to assume it's
just a JDBC issue. In addition to profiler, you would want
to check the activity on the server (using PerfMon) as well
as the activity in SQL Server. When something runs slow at
particular times, it's often due to other activities on the
server or in SQL Server that are using a lot of resources.
-Sue
On 24 Nov 2004 00:54:00 -0800, pascal_fluck@.hotmail.com
(crabouif) wrote:
>Hello,
>I have a strange problem...
>I manage a web application on Tomcat, Struts, SQLServer with the last
>jdbc driver from Microsoft.
>So, each morning one stored proc is very slow (around 20 sec to
>execute) and around 10:00 AM, the stored proc accelerate (around 500
>ms)!!
>This stored proc is executing one query, and if necessary, insert
>records for caching in a cache table.
>Here is my code...
>/*******************************************************************
>* DocumentsOfEDMChanelForUser -
>EXEC DocumentsOfEDMChanelForUser 10, 4, 576, 3
>TRUNCATE TABLE cache_EDMRights
>********************************************************************/
>if exists (select * from dbo.sysobjects where id =>object_id(N'[dbo].[DocumentsOfEDMChanelForUser]') and
>OBJECTPROPERTY(id, N'IsProcedure') = 1)
>drop procedure [dbo].[DocumentsOfEDMChanelForUser]
>GO
>SET QUOTED_IDENTIFIER ON
>GO
>SET ANSI_NULLS ON
>GO
>
>CREATE PROCEDURE DocumentsOfEDMChanelForUser
> @.TopCount int
> ,@.LanguageOID int
> ,@.SecurityUserOID int
> ,@.ChanelOID int
>AS
> SET NOCOUNT ON
> SELECT DISTINCT
> Document.DOCUMENT_PK AS OID,
> dbo.getLexiconText(Document.DisplayName, @.LanguageOID, '') AS
>DocumentName,
> dbo.getLexiconText(Document.Description, @.LanguageOID, '') AS
>Description,
> Document.LastModify AS CreationDate,
> dbo.getFolderPath(Document.Folder, @.LanguageOID, '/') AS
>FolderPath,
> dbo.getIsDocumentReadedByUser(Document.DOCUMENT_PK,
>@.SecurityUserOID) AS IsReaded,
> dbo.getDocumentRights(@.SecurityUserOID, Document.DOCUMENT_PK,
>0, 1) AS Rights
> INTO
> #ResultSet
> FROM
> Document Document INNER JOIN
> EDMChanel EDMChanel ON Document.EDMChanel =>EDMChanel.EDMCHANEL_CHANEL_IPK INNER JOIN
> CodeBasis CodeBasis ON EDMChanel.PeriodType =>CodeBasis.CODEBASIS_PK
> WHERE
> Document.EDMChanel = @.ChanelOID AND
> Document.Active = 1 AND
> CASE
> WHEN CodeBasis.Code = 'PeriodType.Day' THEN DATEADD(day,
>EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Week' THEN DATEADD(week,
>EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Month' THEN
>DATEADD(month, EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Year' THEN DATEADD(year,
>EDMChanel.Period, Document.LastModify)
> END >= getdate()
> --ORDER BY
> -- CreationDate DESC
> IF EXISTS(SELECT 1 FROM #ResultSet WHERE (Rights & 65536) > 0)
> BEGIN
> -- LEFT JOIN with cache because there are index violation with
>insert
> INSERT INTO cache_EDMRights
> SELECT
> 0, @.SecurityUserOID, R.OID, R.Rights ^ 65536
> FROM
> #ResultSet R LEFT OUTER JOIN
> cache_EDMRights C ON IsFolder = 0 AND C.SecurityUser =>@.SecurityUserOID AND C.OID = R.OID
> WHERE
> C.OID IS NULL AND
> (R.Rights & 65536) > 0
> UPDATE #ResultSet SET Rights = Rights ^ 65536 WHERE (Rights &
>65536) > 0
> END
> DECLARE @.SQL varchar(5000)
> SET @.SQL = 'SELECT TOP ' + cast(@.TopCount as varchar(10)) + ' *
>FROM #ResultSet WHERE Rights > 0 ORDER BY CreationDate DESC'
> EXEC(@.SQL)
> DROP TABLE #ResultSet
>GO
>SET QUOTED_IDENTIFIER OFF
>GO
>SET ANSI_NULLS ON
>GO
>I'm not understanding?!
>Anyone has idea?
>Thanks for help.|||Well, I endly solve my problem.
So I check that I had an index is breaked. And in 10:00 AM I had an
agent who reindex it.
Thanks for your idees, I solve that problem with profiler...
But what I dont explain is why with JDBC (respectively my application
and Aqua Data Studio) it was slow, and with MS Query Analyser it was
quick...
Thanks one more for you help.
Pascal
Sue Hoegemeier <Sue_H@.nomail.please> wrote in message news:<sn99q0p7d6ii61m735s2mdt4eed5h23q18@.4ax.com>...
> As Uri already indicated, you may not want to assume it's
> just a JDBC issue. In addition to profiler, you would want
> to check the activity on the server (using PerfMon) as well
> as the activity in SQL Server. When something runs slow at
> particular times, it's often due to other activities on the
> server or in SQL Server that are using a lot of resources.
> -Sue
> On 24 Nov 2004 00:54:00 -0800, pascal_fluck@.hotmail.com
> (crabouif) wrote:
> >Hello,
> >
> >I have a strange problem...
> >
> >I manage a web application on Tomcat, Struts, SQLServer with the last
> >jdbc driver from Microsoft.
> >
> >So, each morning one stored proc is very slow (around 20 sec to
> >execute) and around 10:00 AM, the stored proc accelerate (around 500
> >ms)!!
> >
> >This stored proc is executing one query, and if necessary, insert
> >records for caching in a cache table.
> >
> >Here is my code...
> >
> >/*******************************************************************
> >* DocumentsOfEDMChanelForUser -
> >EXEC DocumentsOfEDMChanelForUser 10, 4, 576, 3
> >TRUNCATE TABLE cache_EDMRights
> >********************************************************************/
> >if exists (select * from dbo.sysobjects where id => >object_id(N'[dbo].[DocumentsOfEDMChanelForUser]') and
> >OBJECTPROPERTY(id, N'IsProcedure') = 1)
> >drop procedure [dbo].[DocumentsOfEDMChanelForUser]
> >GO
> >
> >SET QUOTED_IDENTIFIER ON
> >GO
> >SET ANSI_NULLS ON
> >GO
> >
> >
> >CREATE PROCEDURE DocumentsOfEDMChanelForUser
> > @.TopCount int
> > ,@.LanguageOID int
> > ,@.SecurityUserOID int
> > ,@.ChanelOID int
> >AS
> > SET NOCOUNT ON
> >
> > SELECT DISTINCT
> > Document.DOCUMENT_PK AS OID,
> > dbo.getLexiconText(Document.DisplayName, @.LanguageOID, '') AS
> >DocumentName,
> > dbo.getLexiconText(Document.Description, @.LanguageOID, '') AS
> >Description,
> > Document.LastModify AS CreationDate,
> > dbo.getFolderPath(Document.Folder, @.LanguageOID, '/') AS
> >FolderPath,
> > dbo.getIsDocumentReadedByUser(Document.DOCUMENT_PK,
> >@.SecurityUserOID) AS IsReaded,
> > dbo.getDocumentRights(@.SecurityUserOID, Document.DOCUMENT_PK,
> >0, 1) AS Rights
> > INTO
> > #ResultSet
> > FROM
> > Document Document INNER JOIN
> > EDMChanel EDMChanel ON Document.EDMChanel => >EDMChanel.EDMCHANEL_CHANEL_IPK INNER JOIN
> > CodeBasis CodeBasis ON EDMChanel.PeriodType => >CodeBasis.CODEBASIS_PK
> > WHERE
> > Document.EDMChanel = @.ChanelOID AND
> > Document.Active = 1 AND
> > CASE
> > WHEN CodeBasis.Code = 'PeriodType.Day' THEN DATEADD(day,
> >EDMChanel.Period, Document.LastModify)
> > WHEN CodeBasis.Code = 'PeriodType.Week' THEN DATEADD(week,
> >EDMChanel.Period, Document.LastModify)
> > WHEN CodeBasis.Code = 'PeriodType.Month' THEN
> >DATEADD(month, EDMChanel.Period, Document.LastModify)
> > WHEN CodeBasis.Code = 'PeriodType.Year' THEN DATEADD(year,
> >EDMChanel.Period, Document.LastModify)
> > END >= getdate()
> > --ORDER BY
> > -- CreationDate DESC
> >
> > IF EXISTS(SELECT 1 FROM #ResultSet WHERE (Rights & 65536) > 0)
> > BEGIN
> > -- LEFT JOIN with cache because there are index violation with
> >insert
> > INSERT INTO cache_EDMRights
> > SELECT
> > 0, @.SecurityUserOID, R.OID, R.Rights ^ 65536
> > FROM
> > #ResultSet R LEFT OUTER JOIN
> > cache_EDMRights C ON IsFolder = 0 AND C.SecurityUser => >@.SecurityUserOID AND C.OID = R.OID
> > WHERE
> > C.OID IS NULL AND
> > (R.Rights & 65536) > 0
> > UPDATE #ResultSet SET Rights = Rights ^ 65536 WHERE (Rights &
> >65536) > 0
> > END
> >
> > DECLARE @.SQL varchar(5000)
> > SET @.SQL = 'SELECT TOP ' + cast(@.TopCount as varchar(10)) + ' *
> >FROM #ResultSet WHERE Rights > 0 ORDER BY CreationDate DESC'
> > EXEC(@.SQL)
> > DROP TABLE #ResultSet
> >GO
> >SET QUOTED_IDENTIFIER OFF
> >GO
> >SET ANSI_NULLS ON
> >GO
> >
> >I'm not understanding?!
> >
> >Anyone has idea?
> >
> >Thanks for help.|||One thing is that you could have been seeing the effects of
caching and in combination with calling the stored procedure
differently in the two scenarios. You can monitor the
caching - cache hits, cache misses, etc. using Profiler.
-Sue
On 25 Nov 2004 22:58:40 -0800, pascal_fluck@.hotmail.com
(crabouif) wrote:
>Well, I endly solve my problem.
>So I check that I had an index is breaked. And in 10:00 AM I had an
>agent who reindex it.
>Thanks for your idees, I solve that problem with profiler...
>But what I dont explain is why with JDBC (respectively my application
>and Aqua Data Studio) it was slow, and with MS Query Analyser it was
>quick...
>Thanks one more for you help.
> Pascal
>
>Sue Hoegemeier <Sue_H@.nomail.please> wrote in message news:<sn99q0p7d6ii61m735s2mdt4eed5h23q18@.4ax.com>...
>> As Uri already indicated, you may not want to assume it's
>> just a JDBC issue. In addition to profiler, you would want
>> to check the activity on the server (using PerfMon) as well
>> as the activity in SQL Server. When something runs slow at
>> particular times, it's often due to other activities on the
>> server or in SQL Server that are using a lot of resources.
>> -Sue
>> On 24 Nov 2004 00:54:00 -0800, pascal_fluck@.hotmail.com
>> (crabouif) wrote:
>> >Hello,
>> >
>> >I have a strange problem...
>> >
>> >I manage a web application on Tomcat, Struts, SQLServer with the last
>> >jdbc driver from Microsoft.
>> >
>> >So, each morning one stored proc is very slow (around 20 sec to
>> >execute) and around 10:00 AM, the stored proc accelerate (around 500
>> >ms)!!
>> >
>> >This stored proc is executing one query, and if necessary, insert
>> >records for caching in a cache table.
>> >
>> >Here is my code...
>> >
>> >/*******************************************************************
>> >* DocumentsOfEDMChanelForUser -
>> >EXEC DocumentsOfEDMChanelForUser 10, 4, 576, 3
>> >TRUNCATE TABLE cache_EDMRights
>> >********************************************************************/
>> >if exists (select * from dbo.sysobjects where id =>> >object_id(N'[dbo].[DocumentsOfEDMChanelForUser]') and
>> >OBJECTPROPERTY(id, N'IsProcedure') = 1)
>> >drop procedure [dbo].[DocumentsOfEDMChanelForUser]
>> >GO
>> >
>> >SET QUOTED_IDENTIFIER ON
>> >GO
>> >SET ANSI_NULLS ON
>> >GO
>> >
>> >
>> >CREATE PROCEDURE DocumentsOfEDMChanelForUser
>> > @.TopCount int
>> > ,@.LanguageOID int
>> > ,@.SecurityUserOID int
>> > ,@.ChanelOID int
>> >AS
>> > SET NOCOUNT ON
>> >
>> > SELECT DISTINCT
>> > Document.DOCUMENT_PK AS OID,
>> > dbo.getLexiconText(Document.DisplayName, @.LanguageOID, '') AS
>> >DocumentName,
>> > dbo.getLexiconText(Document.Description, @.LanguageOID, '') AS
>> >Description,
>> > Document.LastModify AS CreationDate,
>> > dbo.getFolderPath(Document.Folder, @.LanguageOID, '/') AS
>> >FolderPath,
>> > dbo.getIsDocumentReadedByUser(Document.DOCUMENT_PK,
>> >@.SecurityUserOID) AS IsReaded,
>> > dbo.getDocumentRights(@.SecurityUserOID, Document.DOCUMENT_PK,
>> >0, 1) AS Rights
>> > INTO
>> > #ResultSet
>> > FROM
>> > Document Document INNER JOIN
>> > EDMChanel EDMChanel ON Document.EDMChanel =>> >EDMChanel.EDMCHANEL_CHANEL_IPK INNER JOIN
>> > CodeBasis CodeBasis ON EDMChanel.PeriodType =>> >CodeBasis.CODEBASIS_PK
>> > WHERE
>> > Document.EDMChanel = @.ChanelOID AND
>> > Document.Active = 1 AND
>> > CASE
>> > WHEN CodeBasis.Code = 'PeriodType.Day' THEN DATEADD(day,
>> >EDMChanel.Period, Document.LastModify)
>> > WHEN CodeBasis.Code = 'PeriodType.Week' THEN DATEADD(week,
>> >EDMChanel.Period, Document.LastModify)
>> > WHEN CodeBasis.Code = 'PeriodType.Month' THEN
>> >DATEADD(month, EDMChanel.Period, Document.LastModify)
>> > WHEN CodeBasis.Code = 'PeriodType.Year' THEN DATEADD(year,
>> >EDMChanel.Period, Document.LastModify)
>> > END >= getdate()
>> > --ORDER BY
>> > -- CreationDate DESC
>> >
>> > IF EXISTS(SELECT 1 FROM #ResultSet WHERE (Rights & 65536) > 0)
>> > BEGIN
>> > -- LEFT JOIN with cache because there are index violation with
>> >insert
>> > INSERT INTO cache_EDMRights
>> > SELECT
>> > 0, @.SecurityUserOID, R.OID, R.Rights ^ 65536
>> > FROM
>> > #ResultSet R LEFT OUTER JOIN
>> > cache_EDMRights C ON IsFolder = 0 AND C.SecurityUser =>> >@.SecurityUserOID AND C.OID = R.OID
>> > WHERE
>> > C.OID IS NULL AND
>> > (R.Rights & 65536) > 0
>> > UPDATE #ResultSet SET Rights = Rights ^ 65536 WHERE (Rights &
>> >65536) > 0
>> > END
>> >
>> > DECLARE @.SQL varchar(5000)
>> > SET @.SQL = 'SELECT TOP ' + cast(@.TopCount as varchar(10)) + ' *
>> >FROM #ResultSet WHERE Rights > 0 ORDER BY CreationDate DESC'
>> > EXEC(@.SQL)
>> > DROP TABLE #ResultSet
>> >GO
>> >SET QUOTED_IDENTIFIER OFF
>> >GO
>> >SET ANSI_NULLS ON
>> >GO
>> >
>> >I'm not understanding?!
>> >
>> >Anyone has idea?
>> >
>> >Thanks for help.
JDBC slow in the morning?!
Hello,
I have a strange problem...
I manage a web application on Tomcat, Struts, SQLServer with the last
jdbc driver from Microsoft.
So, each morning one stored proc is very slow (around 20 sec to
execute) and around 10:00 AM, the stored proc accelerate (around 500
ms)!!
This stored proc is executing one query, and if necessary, insert
records for caching in a cache table.
Here is my code...
/************************************************** *****************
* DocumentsOfEDMChanelForUser -
EXEC DocumentsOfEDMChanelForUser 10, 4, 576, 3
TRUNCATE TABLE cache_EDMRights
************************************************** ******************/
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[DocumentsOfEDMChanelForUser]') and
OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DocumentsOfEDMChanelForUser]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE DocumentsOfEDMChanelForUser
@.TopCount int
,@.LanguageOID int
,@.SecurityUserOID int
,@.ChanelOID int
AS
SET NOCOUNT ON
SELECT DISTINCT
Document.DOCUMENT_PK AS OID,
dbo.getLexiconText(Document.DisplayName, @.LanguageOID, '') AS
DocumentName,
dbo.getLexiconText(Document.Description, @.LanguageOID, '') AS
Description,
Document.LastModify AS CreationDate,
dbo.getFolderPath(Document.Folder, @.LanguageOID, '/') AS
FolderPath,
dbo.getIsDocumentReadedByUser(Document.DOCUMENT_PK ,
@.SecurityUserOID) AS IsReaded,
dbo.getDocumentRights(@.SecurityUserOID, Document.DOCUMENT_PK,
0, 1) AS Rights
INTO
#ResultSet
FROM
Document Document INNER JOIN
EDMChanel EDMChanel ON Document.EDMChanel =
EDMChanel.EDMCHANEL_CHANEL_IPK INNER JOIN
CodeBasis CodeBasis ON EDMChanel.PeriodType =
CodeBasis.CODEBASIS_PK
WHERE
Document.EDMChanel = @.ChanelOID AND
Document.Active = 1 AND
CASE
WHEN CodeBasis.Code = 'PeriodType.Day' THEN DATEADD(day,
EDMChanel.Period, Document.LastModify)
WHEN CodeBasis.Code = 'PeriodType.Week' THEN DATEADD(week,
EDMChanel.Period, Document.LastModify)
WHEN CodeBasis.Code = 'PeriodType.Month' THEN
DATEADD(month, EDMChanel.Period, Document.LastModify)
WHEN CodeBasis.Code = 'PeriodType.Year' THEN DATEADD(year,
EDMChanel.Period, Document.LastModify)
END >= getdate()
--ORDER BY
-- CreationDate DESC
IF EXISTS(SELECT 1 FROM #ResultSet WHERE (Rights & 65536) > 0)
BEGIN
-- LEFT JOIN with cache because there are index violation with
insert
INSERT INTO cache_EDMRights
SELECT
0, @.SecurityUserOID, R.OID, R.Rights ^ 65536
FROM
#ResultSet R LEFT OUTER JOIN
cache_EDMRights C ON IsFolder = 0 AND C.SecurityUser =
@.SecurityUserOID AND C.OID = R.OID
WHERE
C.OID IS NULL AND
(R.Rights & 65536) > 0
UPDATE #ResultSet SET Rights = Rights ^ 65536 WHERE (Rights &
65536) > 0
END
DECLARE @.SQL varchar(5000)
SET @.SQL = 'SELECT TOP ' + cast(@.TopCount as varchar(10)) + ' *
FROM #ResultSet WHERE Rights > 0 ORDER BY CreationDate DESC'
EXEC(@.SQL)
DROP TABLE #ResultSet
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
I'm not understanding?!
Anyone has idea?
Thanks for help.
Hi
How do you know that it's JDBC issue?
Have you ran SQL Server Profiler to track down what is going on during the
execution?
Also ,I'd create a temporary table by CREATE TABLE #Temp and not by SELECT *
INTO ...
Another point, it might be a bad idea to use UDF with a large set data,
because it performs row-by-row process similar how cursors work.
"crabouif" <pascal_fluck@.hotmail.com> wrote in message
news:109014db.0411240054.4fc04476@.posting.google.c om...
> Hello,
> I have a strange problem...
> I manage a web application on Tomcat, Struts, SQLServer with the last
> jdbc driver from Microsoft.
> So, each morning one stored proc is very slow (around 20 sec to
> execute) and around 10:00 AM, the stored proc accelerate (around 500
> ms)!!
> This stored proc is executing one query, and if necessary, insert
> records for caching in a cache table.
> Here is my code...
> /************************************************** *****************
> * DocumentsOfEDMChanelForUser -
> EXEC DocumentsOfEDMChanelForUser 10, 4, 576, 3
> TRUNCATE TABLE cache_EDMRights
> ************************************************** ******************/
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[DocumentsOfEDMChanelForUser]') and
> OBJECTPROPERTY(id, N'IsProcedure') = 1)
> drop procedure [dbo].[DocumentsOfEDMChanelForUser]
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
>
> CREATE PROCEDURE DocumentsOfEDMChanelForUser
> @.TopCount int
> ,@.LanguageOID int
> ,@.SecurityUserOID int
> ,@.ChanelOID int
> AS
> SET NOCOUNT ON
> SELECT DISTINCT
> Document.DOCUMENT_PK AS OID,
> dbo.getLexiconText(Document.DisplayName, @.LanguageOID, '') AS
> DocumentName,
> dbo.getLexiconText(Document.Description, @.LanguageOID, '') AS
> Description,
> Document.LastModify AS CreationDate,
> dbo.getFolderPath(Document.Folder, @.LanguageOID, '/') AS
> FolderPath,
> dbo.getIsDocumentReadedByUser(Document.DOCUMENT_PK ,
> @.SecurityUserOID) AS IsReaded,
> dbo.getDocumentRights(@.SecurityUserOID, Document.DOCUMENT_PK,
> 0, 1) AS Rights
> INTO
> #ResultSet
> FROM
> Document Document INNER JOIN
> EDMChanel EDMChanel ON Document.EDMChanel =
> EDMChanel.EDMCHANEL_CHANEL_IPK INNER JOIN
> CodeBasis CodeBasis ON EDMChanel.PeriodType =
> CodeBasis.CODEBASIS_PK
> WHERE
> Document.EDMChanel = @.ChanelOID AND
> Document.Active = 1 AND
> CASE
> WHEN CodeBasis.Code = 'PeriodType.Day' THEN DATEADD(day,
> EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Week' THEN DATEADD(week,
> EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Month' THEN
> DATEADD(month, EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Year' THEN DATEADD(year,
> EDMChanel.Period, Document.LastModify)
> END >= getdate()
> --ORDER BY
> -- CreationDate DESC
> IF EXISTS(SELECT 1 FROM #ResultSet WHERE (Rights & 65536) > 0)
> BEGIN
> -- LEFT JOIN with cache because there are index violation with
> insert
> INSERT INTO cache_EDMRights
> SELECT
> 0, @.SecurityUserOID, R.OID, R.Rights ^ 65536
> FROM
> #ResultSet R LEFT OUTER JOIN
> cache_EDMRights C ON IsFolder = 0 AND C.SecurityUser =
> @.SecurityUserOID AND C.OID = R.OID
> WHERE
> C.OID IS NULL AND
> (R.Rights & 65536) > 0
> UPDATE #ResultSet SET Rights = Rights ^ 65536 WHERE (Rights &
> 65536) > 0
> END
> DECLARE @.SQL varchar(5000)
> SET @.SQL = 'SELECT TOP ' + cast(@.TopCount as varchar(10)) + ' *
> FROM #ResultSet WHERE Rights > 0 ORDER BY CreationDate DESC'
> EXEC(@.SQL)
> DROP TABLE #ResultSet
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> I'm not understanding?!
> Anyone has idea?
> Thanks for help.
|||As Uri already indicated, you may not want to assume it's
just a JDBC issue. In addition to profiler, you would want
to check the activity on the server (using PerfMon) as well
as the activity in SQL Server. When something runs slow at
particular times, it's often due to other activities on the
server or in SQL Server that are using a lot of resources.
-Sue
On 24 Nov 2004 00:54:00 -0800, pascal_fluck@.hotmail.com
(crabouif) wrote:
>Hello,
>I have a strange problem...
>I manage a web application on Tomcat, Struts, SQLServer with the last
>jdbc driver from Microsoft.
>So, each morning one stored proc is very slow (around 20 sec to
>execute) and around 10:00 AM, the stored proc accelerate (around 500
>ms)!!
>This stored proc is executing one query, and if necessary, insert
>records for caching in a cache table.
>Here is my code...
>/************************************************** *****************
>* DocumentsOfEDMChanelForUser -
>EXEC DocumentsOfEDMChanelForUser 10, 4, 576, 3
>TRUNCATE TABLE cache_EDMRights
>************************************************* *******************/
>if exists (select * from dbo.sysobjects where id =
>object_id(N'[dbo].[DocumentsOfEDMChanelForUser]') and
>OBJECTPROPERTY(id, N'IsProcedure') = 1)
>drop procedure [dbo].[DocumentsOfEDMChanelForUser]
>GO
>SET QUOTED_IDENTIFIER ON
>GO
>SET ANSI_NULLS ON
>GO
>
>CREATE PROCEDURE DocumentsOfEDMChanelForUser
>@.TopCount int
>,@.LanguageOID int
>,@.SecurityUserOID int
>,@.ChanelOID int
>AS
> SET NOCOUNT ON
> SELECT DISTINCT
> Document.DOCUMENT_PK AS OID,
> dbo.getLexiconText(Document.DisplayName, @.LanguageOID, '') AS
>DocumentName,
> dbo.getLexiconText(Document.Description, @.LanguageOID, '') AS
>Description,
> Document.LastModify AS CreationDate,
> dbo.getFolderPath(Document.Folder, @.LanguageOID, '/') AS
>FolderPath,
> dbo.getIsDocumentReadedByUser(Document.DOCUMENT_PK ,
>@.SecurityUserOID) AS IsReaded,
> dbo.getDocumentRights(@.SecurityUserOID, Document.DOCUMENT_PK,
>0, 1) AS Rights
> INTO
> #ResultSet
> FROM
> Document Document INNER JOIN
> EDMChanel EDMChanel ON Document.EDMChanel =
>EDMChanel.EDMCHANEL_CHANEL_IPK INNER JOIN
> CodeBasis CodeBasis ON EDMChanel.PeriodType =
>CodeBasis.CODEBASIS_PK
> WHERE
> Document.EDMChanel = @.ChanelOID AND
> Document.Active = 1 AND
> CASE
> WHEN CodeBasis.Code = 'PeriodType.Day' THEN DATEADD(day,
>EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Week' THEN DATEADD(week,
>EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Month' THEN
>DATEADD(month, EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Year' THEN DATEADD(year,
>EDMChanel.Period, Document.LastModify)
> END >= getdate()
> --ORDER BY
> -- CreationDate DESC
> IF EXISTS(SELECT 1 FROM #ResultSet WHERE (Rights & 65536) > 0)
> BEGIN
> -- LEFT JOIN with cache because there are index violation with
>insert
> INSERT INTO cache_EDMRights
> SELECT
> 0, @.SecurityUserOID, R.OID, R.Rights ^ 65536
> FROM
> #ResultSet R LEFT OUTER JOIN
> cache_EDMRights C ON IsFolder = 0 AND C.SecurityUser =
>@.SecurityUserOID AND C.OID = R.OID
> WHERE
> C.OID IS NULL AND
> (R.Rights & 65536) > 0
> UPDATE #ResultSet SET Rights = Rights ^ 65536 WHERE (Rights &
>65536) > 0
> END
> DECLARE @.SQL varchar(5000)
> SET @.SQL = 'SELECT TOP ' + cast(@.TopCount as varchar(10)) + ' *
>FROM #ResultSet WHERE Rights > 0 ORDER BY CreationDate DESC'
> EXEC(@.SQL)
> DROP TABLE #ResultSet
>GO
>SET QUOTED_IDENTIFIER OFF
>GO
>SET ANSI_NULLS ON
>GO
>I'm not understanding?!
>Anyone has idea?
>Thanks for help.
|||Well, I endly solve my problem.
So I check that I had an index is breaked. And in 10:00 AM I had an
agent who reindex it.
Thanks for your idees, I solve that problem with profiler...
But what I dont explain is why with JDBC (respectively my application
and Aqua Data Studio) it was slow, and with MS Query Analyser it was
quick...
Thanks one more for you help.
Pascal
Sue Hoegemeier <Sue_H@.nomail.please> wrote in message news:<sn99q0p7d6ii61m735s2mdt4eed5h23q18@.4ax.com>. ..[vbcol=seagreen]
> As Uri already indicated, you may not want to assume it's
> just a JDBC issue. In addition to profiler, you would want
> to check the activity on the server (using PerfMon) as well
> as the activity in SQL Server. When something runs slow at
> particular times, it's often due to other activities on the
> server or in SQL Server that are using a lot of resources.
> -Sue
> On 24 Nov 2004 00:54:00 -0800, pascal_fluck@.hotmail.com
> (crabouif) wrote:
|||One thing is that you could have been seeing the effects of
caching and in combination with calling the stored procedure
differently in the two scenarios. You can monitor the
caching - cache hits, cache misses, etc. using Profiler.
-Sue
On 25 Nov 2004 22:58:40 -0800, pascal_fluck@.hotmail.com
(crabouif) wrote:
[vbcol=seagreen]
>Well, I endly solve my problem.
>So I check that I had an index is breaked. And in 10:00 AM I had an
>agent who reindex it.
>Thanks for your idees, I solve that problem with profiler...
>But what I dont explain is why with JDBC (respectively my application
>and Aqua Data Studio) it was slow, and with MS Query Analyser it was
>quick...
>Thanks one more for you help.
> Pascal
>
>Sue Hoegemeier <Sue_H@.nomail.please> wrote in message news:<sn99q0p7d6ii61m735s2mdt4eed5h23q18@.4ax.com>. ..
I have a strange problem...
I manage a web application on Tomcat, Struts, SQLServer with the last
jdbc driver from Microsoft.
So, each morning one stored proc is very slow (around 20 sec to
execute) and around 10:00 AM, the stored proc accelerate (around 500
ms)!!
This stored proc is executing one query, and if necessary, insert
records for caching in a cache table.
Here is my code...
/************************************************** *****************
* DocumentsOfEDMChanelForUser -
EXEC DocumentsOfEDMChanelForUser 10, 4, 576, 3
TRUNCATE TABLE cache_EDMRights
************************************************** ******************/
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[DocumentsOfEDMChanelForUser]') and
OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DocumentsOfEDMChanelForUser]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE DocumentsOfEDMChanelForUser
@.TopCount int
,@.LanguageOID int
,@.SecurityUserOID int
,@.ChanelOID int
AS
SET NOCOUNT ON
SELECT DISTINCT
Document.DOCUMENT_PK AS OID,
dbo.getLexiconText(Document.DisplayName, @.LanguageOID, '') AS
DocumentName,
dbo.getLexiconText(Document.Description, @.LanguageOID, '') AS
Description,
Document.LastModify AS CreationDate,
dbo.getFolderPath(Document.Folder, @.LanguageOID, '/') AS
FolderPath,
dbo.getIsDocumentReadedByUser(Document.DOCUMENT_PK ,
@.SecurityUserOID) AS IsReaded,
dbo.getDocumentRights(@.SecurityUserOID, Document.DOCUMENT_PK,
0, 1) AS Rights
INTO
#ResultSet
FROM
Document Document INNER JOIN
EDMChanel EDMChanel ON Document.EDMChanel =
EDMChanel.EDMCHANEL_CHANEL_IPK INNER JOIN
CodeBasis CodeBasis ON EDMChanel.PeriodType =
CodeBasis.CODEBASIS_PK
WHERE
Document.EDMChanel = @.ChanelOID AND
Document.Active = 1 AND
CASE
WHEN CodeBasis.Code = 'PeriodType.Day' THEN DATEADD(day,
EDMChanel.Period, Document.LastModify)
WHEN CodeBasis.Code = 'PeriodType.Week' THEN DATEADD(week,
EDMChanel.Period, Document.LastModify)
WHEN CodeBasis.Code = 'PeriodType.Month' THEN
DATEADD(month, EDMChanel.Period, Document.LastModify)
WHEN CodeBasis.Code = 'PeriodType.Year' THEN DATEADD(year,
EDMChanel.Period, Document.LastModify)
END >= getdate()
--ORDER BY
-- CreationDate DESC
IF EXISTS(SELECT 1 FROM #ResultSet WHERE (Rights & 65536) > 0)
BEGIN
-- LEFT JOIN with cache because there are index violation with
insert
INSERT INTO cache_EDMRights
SELECT
0, @.SecurityUserOID, R.OID, R.Rights ^ 65536
FROM
#ResultSet R LEFT OUTER JOIN
cache_EDMRights C ON IsFolder = 0 AND C.SecurityUser =
@.SecurityUserOID AND C.OID = R.OID
WHERE
C.OID IS NULL AND
(R.Rights & 65536) > 0
UPDATE #ResultSet SET Rights = Rights ^ 65536 WHERE (Rights &
65536) > 0
END
DECLARE @.SQL varchar(5000)
SET @.SQL = 'SELECT TOP ' + cast(@.TopCount as varchar(10)) + ' *
FROM #ResultSet WHERE Rights > 0 ORDER BY CreationDate DESC'
EXEC(@.SQL)
DROP TABLE #ResultSet
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
I'm not understanding?!
Anyone has idea?
Thanks for help.
Hi
How do you know that it's JDBC issue?
Have you ran SQL Server Profiler to track down what is going on during the
execution?
Also ,I'd create a temporary table by CREATE TABLE #Temp and not by SELECT *
INTO ...
Another point, it might be a bad idea to use UDF with a large set data,
because it performs row-by-row process similar how cursors work.
"crabouif" <pascal_fluck@.hotmail.com> wrote in message
news:109014db.0411240054.4fc04476@.posting.google.c om...
> Hello,
> I have a strange problem...
> I manage a web application on Tomcat, Struts, SQLServer with the last
> jdbc driver from Microsoft.
> So, each morning one stored proc is very slow (around 20 sec to
> execute) and around 10:00 AM, the stored proc accelerate (around 500
> ms)!!
> This stored proc is executing one query, and if necessary, insert
> records for caching in a cache table.
> Here is my code...
> /************************************************** *****************
> * DocumentsOfEDMChanelForUser -
> EXEC DocumentsOfEDMChanelForUser 10, 4, 576, 3
> TRUNCATE TABLE cache_EDMRights
> ************************************************** ******************/
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[DocumentsOfEDMChanelForUser]') and
> OBJECTPROPERTY(id, N'IsProcedure') = 1)
> drop procedure [dbo].[DocumentsOfEDMChanelForUser]
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
>
> CREATE PROCEDURE DocumentsOfEDMChanelForUser
> @.TopCount int
> ,@.LanguageOID int
> ,@.SecurityUserOID int
> ,@.ChanelOID int
> AS
> SET NOCOUNT ON
> SELECT DISTINCT
> Document.DOCUMENT_PK AS OID,
> dbo.getLexiconText(Document.DisplayName, @.LanguageOID, '') AS
> DocumentName,
> dbo.getLexiconText(Document.Description, @.LanguageOID, '') AS
> Description,
> Document.LastModify AS CreationDate,
> dbo.getFolderPath(Document.Folder, @.LanguageOID, '/') AS
> FolderPath,
> dbo.getIsDocumentReadedByUser(Document.DOCUMENT_PK ,
> @.SecurityUserOID) AS IsReaded,
> dbo.getDocumentRights(@.SecurityUserOID, Document.DOCUMENT_PK,
> 0, 1) AS Rights
> INTO
> #ResultSet
> FROM
> Document Document INNER JOIN
> EDMChanel EDMChanel ON Document.EDMChanel =
> EDMChanel.EDMCHANEL_CHANEL_IPK INNER JOIN
> CodeBasis CodeBasis ON EDMChanel.PeriodType =
> CodeBasis.CODEBASIS_PK
> WHERE
> Document.EDMChanel = @.ChanelOID AND
> Document.Active = 1 AND
> CASE
> WHEN CodeBasis.Code = 'PeriodType.Day' THEN DATEADD(day,
> EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Week' THEN DATEADD(week,
> EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Month' THEN
> DATEADD(month, EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Year' THEN DATEADD(year,
> EDMChanel.Period, Document.LastModify)
> END >= getdate()
> --ORDER BY
> -- CreationDate DESC
> IF EXISTS(SELECT 1 FROM #ResultSet WHERE (Rights & 65536) > 0)
> BEGIN
> -- LEFT JOIN with cache because there are index violation with
> insert
> INSERT INTO cache_EDMRights
> SELECT
> 0, @.SecurityUserOID, R.OID, R.Rights ^ 65536
> FROM
> #ResultSet R LEFT OUTER JOIN
> cache_EDMRights C ON IsFolder = 0 AND C.SecurityUser =
> @.SecurityUserOID AND C.OID = R.OID
> WHERE
> C.OID IS NULL AND
> (R.Rights & 65536) > 0
> UPDATE #ResultSet SET Rights = Rights ^ 65536 WHERE (Rights &
> 65536) > 0
> END
> DECLARE @.SQL varchar(5000)
> SET @.SQL = 'SELECT TOP ' + cast(@.TopCount as varchar(10)) + ' *
> FROM #ResultSet WHERE Rights > 0 ORDER BY CreationDate DESC'
> EXEC(@.SQL)
> DROP TABLE #ResultSet
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> I'm not understanding?!
> Anyone has idea?
> Thanks for help.
|||As Uri already indicated, you may not want to assume it's
just a JDBC issue. In addition to profiler, you would want
to check the activity on the server (using PerfMon) as well
as the activity in SQL Server. When something runs slow at
particular times, it's often due to other activities on the
server or in SQL Server that are using a lot of resources.
-Sue
On 24 Nov 2004 00:54:00 -0800, pascal_fluck@.hotmail.com
(crabouif) wrote:
>Hello,
>I have a strange problem...
>I manage a web application on Tomcat, Struts, SQLServer with the last
>jdbc driver from Microsoft.
>So, each morning one stored proc is very slow (around 20 sec to
>execute) and around 10:00 AM, the stored proc accelerate (around 500
>ms)!!
>This stored proc is executing one query, and if necessary, insert
>records for caching in a cache table.
>Here is my code...
>/************************************************** *****************
>* DocumentsOfEDMChanelForUser -
>EXEC DocumentsOfEDMChanelForUser 10, 4, 576, 3
>TRUNCATE TABLE cache_EDMRights
>************************************************* *******************/
>if exists (select * from dbo.sysobjects where id =
>object_id(N'[dbo].[DocumentsOfEDMChanelForUser]') and
>OBJECTPROPERTY(id, N'IsProcedure') = 1)
>drop procedure [dbo].[DocumentsOfEDMChanelForUser]
>GO
>SET QUOTED_IDENTIFIER ON
>GO
>SET ANSI_NULLS ON
>GO
>
>CREATE PROCEDURE DocumentsOfEDMChanelForUser
>@.TopCount int
>,@.LanguageOID int
>,@.SecurityUserOID int
>,@.ChanelOID int
>AS
> SET NOCOUNT ON
> SELECT DISTINCT
> Document.DOCUMENT_PK AS OID,
> dbo.getLexiconText(Document.DisplayName, @.LanguageOID, '') AS
>DocumentName,
> dbo.getLexiconText(Document.Description, @.LanguageOID, '') AS
>Description,
> Document.LastModify AS CreationDate,
> dbo.getFolderPath(Document.Folder, @.LanguageOID, '/') AS
>FolderPath,
> dbo.getIsDocumentReadedByUser(Document.DOCUMENT_PK ,
>@.SecurityUserOID) AS IsReaded,
> dbo.getDocumentRights(@.SecurityUserOID, Document.DOCUMENT_PK,
>0, 1) AS Rights
> INTO
> #ResultSet
> FROM
> Document Document INNER JOIN
> EDMChanel EDMChanel ON Document.EDMChanel =
>EDMChanel.EDMCHANEL_CHANEL_IPK INNER JOIN
> CodeBasis CodeBasis ON EDMChanel.PeriodType =
>CodeBasis.CODEBASIS_PK
> WHERE
> Document.EDMChanel = @.ChanelOID AND
> Document.Active = 1 AND
> CASE
> WHEN CodeBasis.Code = 'PeriodType.Day' THEN DATEADD(day,
>EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Week' THEN DATEADD(week,
>EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Month' THEN
>DATEADD(month, EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Year' THEN DATEADD(year,
>EDMChanel.Period, Document.LastModify)
> END >= getdate()
> --ORDER BY
> -- CreationDate DESC
> IF EXISTS(SELECT 1 FROM #ResultSet WHERE (Rights & 65536) > 0)
> BEGIN
> -- LEFT JOIN with cache because there are index violation with
>insert
> INSERT INTO cache_EDMRights
> SELECT
> 0, @.SecurityUserOID, R.OID, R.Rights ^ 65536
> FROM
> #ResultSet R LEFT OUTER JOIN
> cache_EDMRights C ON IsFolder = 0 AND C.SecurityUser =
>@.SecurityUserOID AND C.OID = R.OID
> WHERE
> C.OID IS NULL AND
> (R.Rights & 65536) > 0
> UPDATE #ResultSet SET Rights = Rights ^ 65536 WHERE (Rights &
>65536) > 0
> END
> DECLARE @.SQL varchar(5000)
> SET @.SQL = 'SELECT TOP ' + cast(@.TopCount as varchar(10)) + ' *
>FROM #ResultSet WHERE Rights > 0 ORDER BY CreationDate DESC'
> EXEC(@.SQL)
> DROP TABLE #ResultSet
>GO
>SET QUOTED_IDENTIFIER OFF
>GO
>SET ANSI_NULLS ON
>GO
>I'm not understanding?!
>Anyone has idea?
>Thanks for help.
|||Well, I endly solve my problem.
So I check that I had an index is breaked. And in 10:00 AM I had an
agent who reindex it.
Thanks for your idees, I solve that problem with profiler...
But what I dont explain is why with JDBC (respectively my application
and Aqua Data Studio) it was slow, and with MS Query Analyser it was
quick...
Thanks one more for you help.
Pascal
Sue Hoegemeier <Sue_H@.nomail.please> wrote in message news:<sn99q0p7d6ii61m735s2mdt4eed5h23q18@.4ax.com>. ..[vbcol=seagreen]
> As Uri already indicated, you may not want to assume it's
> just a JDBC issue. In addition to profiler, you would want
> to check the activity on the server (using PerfMon) as well
> as the activity in SQL Server. When something runs slow at
> particular times, it's often due to other activities on the
> server or in SQL Server that are using a lot of resources.
> -Sue
> On 24 Nov 2004 00:54:00 -0800, pascal_fluck@.hotmail.com
> (crabouif) wrote:
|||One thing is that you could have been seeing the effects of
caching and in combination with calling the stored procedure
differently in the two scenarios. You can monitor the
caching - cache hits, cache misses, etc. using Profiler.
-Sue
On 25 Nov 2004 22:58:40 -0800, pascal_fluck@.hotmail.com
(crabouif) wrote:
[vbcol=seagreen]
>Well, I endly solve my problem.
>So I check that I had an index is breaked. And in 10:00 AM I had an
>agent who reindex it.
>Thanks for your idees, I solve that problem with profiler...
>But what I dont explain is why with JDBC (respectively my application
>and Aqua Data Studio) it was slow, and with MS Query Analyser it was
>quick...
>Thanks one more for you help.
> Pascal
>
>Sue Hoegemeier <Sue_H@.nomail.please> wrote in message news:<sn99q0p7d6ii61m735s2mdt4eed5h23q18@.4ax.com>. ..
JDBC slow in the morning?!
Hello,
I have a strange problem...
I manage a web application on Tomcat, Struts, SQLServer with the last
jdbc driver from Microsoft.
So, each morning one stored proc is very slow (around 20 sec to
execute) and around 10:00 AM, the stored proc accelerate (around 500
ms)!!
This stored proc is executing one query, and if necessary, insert
records for caching in a cache table.
Here is my code...
/ ****************************************
***************************
* DocumentsOfEDMChanelForUser -
EXEC DocumentsOfEDMChanelForUser 10, 4, 576, 3
TRUNCATE TABLE cache_EDMRights
****************************************
****************************/
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[DocumentsOfEDMChanelForUser]') and
OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DocumentsOfEDMChanelForUser]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE DocumentsOfEDMChanelForUser
@.TopCount int
,@.LanguageOID int
,@.SecurityUserOID int
,@.ChanelOID int
AS
SET NOCOUNT ON
SELECT DISTINCT
Document.DOCUMENT_PK AS OID,
dbo.getLexiconText(Document.DisplayName, @.LanguageOID, '') AS
DocumentName,
dbo.getLexiconText(Document.Description, @.LanguageOID, '') AS
Description,
Document.LastModify AS CreationDate,
dbo.getFolderPath(Document.Folder, @.LanguageOID, '/') AS
FolderPath,
dbo.getIsDocumentReadedByUser(Document.DOCUMENT_PK,
@.SecurityUserOID) AS IsReaded,
dbo.getDocumentRights(@.SecurityUserOID, Document.DOCUMENT_PK,
0, 1) AS Rights
INTO
#ResultSet
FROM
Document Document INNER JOIN
EDMChanel EDMChanel ON Document.EDMChanel =
EDMChanel.EDMCHANEL_CHANEL_IPK INNER JOIN
CodeBasis CodeBasis ON EDMChanel.PeriodType =
CodeBasis.CODEBASIS_PK
WHERE
Document.EDMChanel = @.ChanelOID AND
Document.Active = 1 AND
CASE
WHEN CodeBasis.Code = 'PeriodType.Day' THEN DATEADD(day,
EDMChanel.Period, Document.LastModify)
WHEN CodeBasis.Code = 'PeriodType.Week' THEN DATEADD(week,
EDMChanel.Period, Document.LastModify)
WHEN CodeBasis.Code = 'PeriodType.Month' THEN
DATEADD(month, EDMChanel.Period, Document.LastModify)
WHEN CodeBasis.Code = 'PeriodType.Year' THEN DATEADD(year,
EDMChanel.Period, Document.LastModify)
END >= getdate()
--ORDER BY
-- CreationDate DESC
IF EXISTS(SELECT 1 FROM #ResultSet WHERE (Rights & 65536) > 0)
BEGIN
-- LEFT JOIN with cache because there are index violation with
insert
INSERT INTO cache_EDMRights
SELECT
0, @.SecurityUserOID, R.OID, R.Rights ^ 65536
FROM
#ResultSet R LEFT OUTER JOIN
cache_EDMRights C ON IsFolder = 0 AND C.SecurityUser =
@.SecurityUserOID AND C.OID = R.OID
WHERE
C.OID IS NULL AND
(R.Rights & 65536) > 0
UPDATE #ResultSet SET Rights = Rights ^ 65536 WHERE (Rights &
65536) > 0
END
DECLARE @.SQL varchar(5000)
SET @.SQL = 'SELECT TOP ' + cast(@.TopCount as varchar(10)) + ' *
FROM #ResultSet WHERE Rights > 0 ORDER BY CreationDate DESC'
EXEC(@.SQL)
DROP TABLE #ResultSet
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
I'm not understanding?!
Anyone has idea?
Thanks for help.Hi
How do you know that it's JDBC issue?
Have you ran SQL Server Profiler to track down what is going on during the
execution?
Also ,I'd create a temporary table by CREATE TABLE #Temp and not by SELECT *
INTO ...
Another point, it might be a bad idea to use UDF with a large set data,
because it performs row-by-row process similar how cursors work.
"crabouif" <pascal_fluck@.hotmail.com> wrote in message
news:109014db.0411240054.4fc04476@.posting.google.com...
> Hello,
> I have a strange problem...
> I manage a web application on Tomcat, Struts, SQLServer with the last
> jdbc driver from Microsoft.
> So, each morning one stored proc is very slow (around 20 sec to
> execute) and around 10:00 AM, the stored proc accelerate (around 500
> ms)!!
> This stored proc is executing one query, and if necessary, insert
> records for caching in a cache table.
> Here is my code...
> / ****************************************
***************************
> * DocumentsOfEDMChanelForUser -
> EXEC DocumentsOfEDMChanelForUser 10, 4, 576, 3
> TRUNCATE TABLE cache_EDMRights
> ****************************************
****************************/
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[DocumentsOfEDMChanelForUser]') and
> OBJECTPROPERTY(id, N'IsProcedure') = 1)
> drop procedure [dbo].[DocumentsOfEDMChanelForUser]
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
>
> CREATE PROCEDURE DocumentsOfEDMChanelForUser
> @.TopCount int
> ,@.LanguageOID int
> ,@.SecurityUserOID int
> ,@.ChanelOID int
> AS
> SET NOCOUNT ON
> SELECT DISTINCT
> Document.DOCUMENT_PK AS OID,
> dbo.getLexiconText(Document.DisplayName, @.LanguageOID, '') AS
> DocumentName,
> dbo.getLexiconText(Document.Description, @.LanguageOID, '') AS
> Description,
> Document.LastModify AS CreationDate,
> dbo.getFolderPath(Document.Folder, @.LanguageOID, '/') AS
> FolderPath,
> dbo.getIsDocumentReadedByUser(Document.DOCUMENT_PK,
> @.SecurityUserOID) AS IsReaded,
> dbo.getDocumentRights(@.SecurityUserOID, Document.DOCUMENT_PK,
> 0, 1) AS Rights
> INTO
> #ResultSet
> FROM
> Document Document INNER JOIN
> EDMChanel EDMChanel ON Document.EDMChanel =
> EDMChanel.EDMCHANEL_CHANEL_IPK INNER JOIN
> CodeBasis CodeBasis ON EDMChanel.PeriodType =
> CodeBasis.CODEBASIS_PK
> WHERE
> Document.EDMChanel = @.ChanelOID AND
> Document.Active = 1 AND
> CASE
> WHEN CodeBasis.Code = 'PeriodType.Day' THEN DATEADD(day,
> EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Week' THEN DATEADD(week,
> EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Month' THEN
> DATEADD(month, EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Year' THEN DATEADD(year,
> EDMChanel.Period, Document.LastModify)
> END >= getdate()
> --ORDER BY
> -- CreationDate DESC
> IF EXISTS(SELECT 1 FROM #ResultSet WHERE (Rights & 65536) > 0)
> BEGIN
> -- LEFT JOIN with cache because there are index violation with
> insert
> INSERT INTO cache_EDMRights
> SELECT
> 0, @.SecurityUserOID, R.OID, R.Rights ^ 65536
> FROM
> #ResultSet R LEFT OUTER JOIN
> cache_EDMRights C ON IsFolder = 0 AND C.SecurityUser =
> @.SecurityUserOID AND C.OID = R.OID
> WHERE
> C.OID IS NULL AND
> (R.Rights & 65536) > 0
> UPDATE #ResultSet SET Rights = Rights ^ 65536 WHERE (Rights &
> 65536) > 0
> END
> DECLARE @.SQL varchar(5000)
> SET @.SQL = 'SELECT TOP ' + cast(@.TopCount as varchar(10)) + ' *
> FROM #ResultSet WHERE Rights > 0 ORDER BY CreationDate DESC'
> EXEC(@.SQL)
> DROP TABLE #ResultSet
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> I'm not understanding?!
> Anyone has idea?
> Thanks for help.|||As Uri already indicated, you may not want to assume it's
just a JDBC issue. In addition to profiler, you would want
to check the activity on the server (using PerfMon) as well
as the activity in SQL Server. When something runs slow at
particular times, it's often due to other activities on the
server or in SQL Server that are using a lot of resources.
-Sue
On 24 Nov 2004 00:54:00 -0800, pascal_fluck@.hotmail.com
(crabouif) wrote:
>Hello,
>I have a strange problem...
>I manage a web application on Tomcat, Struts, SQLServer with the last
>jdbc driver from Microsoft.
>So, each morning one stored proc is very slow (around 20 sec to
>execute) and around 10:00 AM, the stored proc accelerate (around 500
>ms)!!
>This stored proc is executing one query, and if necessary, insert
>records for caching in a cache table.
>Here is my code...
>/ ****************************************
***************************
>* DocumentsOfEDMChanelForUser -
>EXEC DocumentsOfEDMChanelForUser 10, 4, 576, 3
>TRUNCATE TABLE cache_EDMRights
> ****************************************
****************************/
>if exists (select * from dbo.sysobjects where id =
>object_id(N'[dbo].[DocumentsOfEDMChanelForUser]') and
>OBJECTPROPERTY(id, N'IsProcedure') = 1)
>drop procedure [dbo].[DocumentsOfEDMChanelForUser]
>GO
>SET QUOTED_IDENTIFIER ON
>GO
>SET ANSI_NULLS ON
>GO
>
>CREATE PROCEDURE DocumentsOfEDMChanelForUser
> @.TopCount int
> ,@.LanguageOID int
> ,@.SecurityUserOID int
> ,@.ChanelOID int
>AS
> SET NOCOUNT ON
> SELECT DISTINCT
> Document.DOCUMENT_PK AS OID,
> dbo.getLexiconText(Document.DisplayName, @.LanguageOID, '') AS
>DocumentName,
> dbo.getLexiconText(Document.Description, @.LanguageOID, '') AS
>Description,
> Document.LastModify AS CreationDate,
> dbo.getFolderPath(Document.Folder, @.LanguageOID, '/') AS
>FolderPath,
> dbo.getIsDocumentReadedByUser(Document.DOCUMENT_PK,
>@.SecurityUserOID) AS IsReaded,
> dbo.getDocumentRights(@.SecurityUserOID, Document.DOCUMENT_PK,
>0, 1) AS Rights
> INTO
> #ResultSet
> FROM
> Document Document INNER JOIN
> EDMChanel EDMChanel ON Document.EDMChanel =
>EDMChanel.EDMCHANEL_CHANEL_IPK INNER JOIN
> CodeBasis CodeBasis ON EDMChanel.PeriodType =
>CodeBasis.CODEBASIS_PK
> WHERE
> Document.EDMChanel = @.ChanelOID AND
> Document.Active = 1 AND
> CASE
> WHEN CodeBasis.Code = 'PeriodType.Day' THEN DATEADD(day,
>EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Week' THEN DATEADD(week,
>EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Month' THEN
>DATEADD(month, EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Year' THEN DATEADD(year,
>EDMChanel.Period, Document.LastModify)
> END >= getdate()
> --ORDER BY
> -- CreationDate DESC
> IF EXISTS(SELECT 1 FROM #ResultSet WHERE (Rights & 65536) > 0)
> BEGIN
> -- LEFT JOIN with cache because there are index violation with
>insert
> INSERT INTO cache_EDMRights
> SELECT
> 0, @.SecurityUserOID, R.OID, R.Rights ^ 65536
> FROM
> #ResultSet R LEFT OUTER JOIN
> cache_EDMRights C ON IsFolder = 0 AND C.SecurityUser =
>@.SecurityUserOID AND C.OID = R.OID
> WHERE
> C.OID IS NULL AND
> (R.Rights & 65536) > 0
> UPDATE #ResultSet SET Rights = Rights ^ 65536 WHERE (Rights &
>65536) > 0
> END
> DECLARE @.SQL varchar(5000)
> SET @.SQL = 'SELECT TOP ' + cast(@.TopCount as varchar(10)) + ' *
>FROM #ResultSet WHERE Rights > 0 ORDER BY CreationDate DESC'
> EXEC(@.SQL)
> DROP TABLE #ResultSet
>GO
>SET QUOTED_IDENTIFIER OFF
>GO
>SET ANSI_NULLS ON
>GO
>I'm not understanding?!
>Anyone has idea?
>Thanks for help.|||Well, I endly solve my problem.
So I check that I had an index is breaked. And in 10:00 AM I had an
agent who reindex it.
Thanks for your idees, I solve that problem with profiler...
But what I dont explain is why with JDBC (respectively my application
and Aqua Data Studio) it was slow, and with MS Query Analyser it was
quick...
Thanks one more for you help.
Pascal
Sue Hoegemeier <Sue_H@.nomail.please> wrote in message news:<sn99q0p7d6ii61m735s2mdt4eed5h23q
18@.4ax.com>...[vbcol=seagreen]
> As Uri already indicated, you may not want to assume it's
> just a JDBC issue. In addition to profiler, you would want
> to check the activity on the server (using PerfMon) as well
> as the activity in SQL Server. When something runs slow at
> particular times, it's often due to other activities on the
> server or in SQL Server that are using a lot of resources.
> -Sue
> On 24 Nov 2004 00:54:00 -0800, pascal_fluck@.hotmail.com
> (crabouif) wrote:
>|||One thing is that you could have been seeing the effects of
caching and in combination with calling the stored procedure
differently in the two scenarios. You can monitor the
caching - cache hits, cache misses, etc. using Profiler.
-Sue
On 25 Nov 2004 22:58:40 -0800, pascal_fluck@.hotmail.com
(crabouif) wrote:
[vbcol=seagreen]
>Well, I endly solve my problem.
>So I check that I had an index is breaked. And in 10:00 AM I had an
>agent who reindex it.
>Thanks for your idees, I solve that problem with profiler...
>But what I dont explain is why with JDBC (respectively my application
>and Aqua Data Studio) it was slow, and with MS Query Analyser it was
>quick...
>Thanks one more for you help.
> Pascal
>
>Sue Hoegemeier <Sue_H@.nomail.please> wrote in message news:<sn99q0p7d6ii61m
735s2mdt4eed5h23q18@.4ax.com>...
I have a strange problem...
I manage a web application on Tomcat, Struts, SQLServer with the last
jdbc driver from Microsoft.
So, each morning one stored proc is very slow (around 20 sec to
execute) and around 10:00 AM, the stored proc accelerate (around 500
ms)!!
This stored proc is executing one query, and if necessary, insert
records for caching in a cache table.
Here is my code...
/ ****************************************
***************************
* DocumentsOfEDMChanelForUser -
EXEC DocumentsOfEDMChanelForUser 10, 4, 576, 3
TRUNCATE TABLE cache_EDMRights
****************************************
****************************/
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[DocumentsOfEDMChanelForUser]') and
OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DocumentsOfEDMChanelForUser]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE DocumentsOfEDMChanelForUser
@.TopCount int
,@.LanguageOID int
,@.SecurityUserOID int
,@.ChanelOID int
AS
SET NOCOUNT ON
SELECT DISTINCT
Document.DOCUMENT_PK AS OID,
dbo.getLexiconText(Document.DisplayName, @.LanguageOID, '') AS
DocumentName,
dbo.getLexiconText(Document.Description, @.LanguageOID, '') AS
Description,
Document.LastModify AS CreationDate,
dbo.getFolderPath(Document.Folder, @.LanguageOID, '/') AS
FolderPath,
dbo.getIsDocumentReadedByUser(Document.DOCUMENT_PK,
@.SecurityUserOID) AS IsReaded,
dbo.getDocumentRights(@.SecurityUserOID, Document.DOCUMENT_PK,
0, 1) AS Rights
INTO
#ResultSet
FROM
Document Document INNER JOIN
EDMChanel EDMChanel ON Document.EDMChanel =
EDMChanel.EDMCHANEL_CHANEL_IPK INNER JOIN
CodeBasis CodeBasis ON EDMChanel.PeriodType =
CodeBasis.CODEBASIS_PK
WHERE
Document.EDMChanel = @.ChanelOID AND
Document.Active = 1 AND
CASE
WHEN CodeBasis.Code = 'PeriodType.Day' THEN DATEADD(day,
EDMChanel.Period, Document.LastModify)
WHEN CodeBasis.Code = 'PeriodType.Week' THEN DATEADD(week,
EDMChanel.Period, Document.LastModify)
WHEN CodeBasis.Code = 'PeriodType.Month' THEN
DATEADD(month, EDMChanel.Period, Document.LastModify)
WHEN CodeBasis.Code = 'PeriodType.Year' THEN DATEADD(year,
EDMChanel.Period, Document.LastModify)
END >= getdate()
--ORDER BY
-- CreationDate DESC
IF EXISTS(SELECT 1 FROM #ResultSet WHERE (Rights & 65536) > 0)
BEGIN
-- LEFT JOIN with cache because there are index violation with
insert
INSERT INTO cache_EDMRights
SELECT
0, @.SecurityUserOID, R.OID, R.Rights ^ 65536
FROM
#ResultSet R LEFT OUTER JOIN
cache_EDMRights C ON IsFolder = 0 AND C.SecurityUser =
@.SecurityUserOID AND C.OID = R.OID
WHERE
C.OID IS NULL AND
(R.Rights & 65536) > 0
UPDATE #ResultSet SET Rights = Rights ^ 65536 WHERE (Rights &
65536) > 0
END
DECLARE @.SQL varchar(5000)
SET @.SQL = 'SELECT TOP ' + cast(@.TopCount as varchar(10)) + ' *
FROM #ResultSet WHERE Rights > 0 ORDER BY CreationDate DESC'
EXEC(@.SQL)
DROP TABLE #ResultSet
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
I'm not understanding?!
Anyone has idea?
Thanks for help.Hi
How do you know that it's JDBC issue?
Have you ran SQL Server Profiler to track down what is going on during the
execution?
Also ,I'd create a temporary table by CREATE TABLE #Temp and not by SELECT *
INTO ...
Another point, it might be a bad idea to use UDF with a large set data,
because it performs row-by-row process similar how cursors work.
"crabouif" <pascal_fluck@.hotmail.com> wrote in message
news:109014db.0411240054.4fc04476@.posting.google.com...
> Hello,
> I have a strange problem...
> I manage a web application on Tomcat, Struts, SQLServer with the last
> jdbc driver from Microsoft.
> So, each morning one stored proc is very slow (around 20 sec to
> execute) and around 10:00 AM, the stored proc accelerate (around 500
> ms)!!
> This stored proc is executing one query, and if necessary, insert
> records for caching in a cache table.
> Here is my code...
> / ****************************************
***************************
> * DocumentsOfEDMChanelForUser -
> EXEC DocumentsOfEDMChanelForUser 10, 4, 576, 3
> TRUNCATE TABLE cache_EDMRights
> ****************************************
****************************/
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[DocumentsOfEDMChanelForUser]') and
> OBJECTPROPERTY(id, N'IsProcedure') = 1)
> drop procedure [dbo].[DocumentsOfEDMChanelForUser]
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
>
> CREATE PROCEDURE DocumentsOfEDMChanelForUser
> @.TopCount int
> ,@.LanguageOID int
> ,@.SecurityUserOID int
> ,@.ChanelOID int
> AS
> SET NOCOUNT ON
> SELECT DISTINCT
> Document.DOCUMENT_PK AS OID,
> dbo.getLexiconText(Document.DisplayName, @.LanguageOID, '') AS
> DocumentName,
> dbo.getLexiconText(Document.Description, @.LanguageOID, '') AS
> Description,
> Document.LastModify AS CreationDate,
> dbo.getFolderPath(Document.Folder, @.LanguageOID, '/') AS
> FolderPath,
> dbo.getIsDocumentReadedByUser(Document.DOCUMENT_PK,
> @.SecurityUserOID) AS IsReaded,
> dbo.getDocumentRights(@.SecurityUserOID, Document.DOCUMENT_PK,
> 0, 1) AS Rights
> INTO
> #ResultSet
> FROM
> Document Document INNER JOIN
> EDMChanel EDMChanel ON Document.EDMChanel =
> EDMChanel.EDMCHANEL_CHANEL_IPK INNER JOIN
> CodeBasis CodeBasis ON EDMChanel.PeriodType =
> CodeBasis.CODEBASIS_PK
> WHERE
> Document.EDMChanel = @.ChanelOID AND
> Document.Active = 1 AND
> CASE
> WHEN CodeBasis.Code = 'PeriodType.Day' THEN DATEADD(day,
> EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Week' THEN DATEADD(week,
> EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Month' THEN
> DATEADD(month, EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Year' THEN DATEADD(year,
> EDMChanel.Period, Document.LastModify)
> END >= getdate()
> --ORDER BY
> -- CreationDate DESC
> IF EXISTS(SELECT 1 FROM #ResultSet WHERE (Rights & 65536) > 0)
> BEGIN
> -- LEFT JOIN with cache because there are index violation with
> insert
> INSERT INTO cache_EDMRights
> SELECT
> 0, @.SecurityUserOID, R.OID, R.Rights ^ 65536
> FROM
> #ResultSet R LEFT OUTER JOIN
> cache_EDMRights C ON IsFolder = 0 AND C.SecurityUser =
> @.SecurityUserOID AND C.OID = R.OID
> WHERE
> C.OID IS NULL AND
> (R.Rights & 65536) > 0
> UPDATE #ResultSet SET Rights = Rights ^ 65536 WHERE (Rights &
> 65536) > 0
> END
> DECLARE @.SQL varchar(5000)
> SET @.SQL = 'SELECT TOP ' + cast(@.TopCount as varchar(10)) + ' *
> FROM #ResultSet WHERE Rights > 0 ORDER BY CreationDate DESC'
> EXEC(@.SQL)
> DROP TABLE #ResultSet
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> I'm not understanding?!
> Anyone has idea?
> Thanks for help.|||As Uri already indicated, you may not want to assume it's
just a JDBC issue. In addition to profiler, you would want
to check the activity on the server (using PerfMon) as well
as the activity in SQL Server. When something runs slow at
particular times, it's often due to other activities on the
server or in SQL Server that are using a lot of resources.
-Sue
On 24 Nov 2004 00:54:00 -0800, pascal_fluck@.hotmail.com
(crabouif) wrote:
>Hello,
>I have a strange problem...
>I manage a web application on Tomcat, Struts, SQLServer with the last
>jdbc driver from Microsoft.
>So, each morning one stored proc is very slow (around 20 sec to
>execute) and around 10:00 AM, the stored proc accelerate (around 500
>ms)!!
>This stored proc is executing one query, and if necessary, insert
>records for caching in a cache table.
>Here is my code...
>/ ****************************************
***************************
>* DocumentsOfEDMChanelForUser -
>EXEC DocumentsOfEDMChanelForUser 10, 4, 576, 3
>TRUNCATE TABLE cache_EDMRights
> ****************************************
****************************/
>if exists (select * from dbo.sysobjects where id =
>object_id(N'[dbo].[DocumentsOfEDMChanelForUser]') and
>OBJECTPROPERTY(id, N'IsProcedure') = 1)
>drop procedure [dbo].[DocumentsOfEDMChanelForUser]
>GO
>SET QUOTED_IDENTIFIER ON
>GO
>SET ANSI_NULLS ON
>GO
>
>CREATE PROCEDURE DocumentsOfEDMChanelForUser
> @.TopCount int
> ,@.LanguageOID int
> ,@.SecurityUserOID int
> ,@.ChanelOID int
>AS
> SET NOCOUNT ON
> SELECT DISTINCT
> Document.DOCUMENT_PK AS OID,
> dbo.getLexiconText(Document.DisplayName, @.LanguageOID, '') AS
>DocumentName,
> dbo.getLexiconText(Document.Description, @.LanguageOID, '') AS
>Description,
> Document.LastModify AS CreationDate,
> dbo.getFolderPath(Document.Folder, @.LanguageOID, '/') AS
>FolderPath,
> dbo.getIsDocumentReadedByUser(Document.DOCUMENT_PK,
>@.SecurityUserOID) AS IsReaded,
> dbo.getDocumentRights(@.SecurityUserOID, Document.DOCUMENT_PK,
>0, 1) AS Rights
> INTO
> #ResultSet
> FROM
> Document Document INNER JOIN
> EDMChanel EDMChanel ON Document.EDMChanel =
>EDMChanel.EDMCHANEL_CHANEL_IPK INNER JOIN
> CodeBasis CodeBasis ON EDMChanel.PeriodType =
>CodeBasis.CODEBASIS_PK
> WHERE
> Document.EDMChanel = @.ChanelOID AND
> Document.Active = 1 AND
> CASE
> WHEN CodeBasis.Code = 'PeriodType.Day' THEN DATEADD(day,
>EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Week' THEN DATEADD(week,
>EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Month' THEN
>DATEADD(month, EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Year' THEN DATEADD(year,
>EDMChanel.Period, Document.LastModify)
> END >= getdate()
> --ORDER BY
> -- CreationDate DESC
> IF EXISTS(SELECT 1 FROM #ResultSet WHERE (Rights & 65536) > 0)
> BEGIN
> -- LEFT JOIN with cache because there are index violation with
>insert
> INSERT INTO cache_EDMRights
> SELECT
> 0, @.SecurityUserOID, R.OID, R.Rights ^ 65536
> FROM
> #ResultSet R LEFT OUTER JOIN
> cache_EDMRights C ON IsFolder = 0 AND C.SecurityUser =
>@.SecurityUserOID AND C.OID = R.OID
> WHERE
> C.OID IS NULL AND
> (R.Rights & 65536) > 0
> UPDATE #ResultSet SET Rights = Rights ^ 65536 WHERE (Rights &
>65536) > 0
> END
> DECLARE @.SQL varchar(5000)
> SET @.SQL = 'SELECT TOP ' + cast(@.TopCount as varchar(10)) + ' *
>FROM #ResultSet WHERE Rights > 0 ORDER BY CreationDate DESC'
> EXEC(@.SQL)
> DROP TABLE #ResultSet
>GO
>SET QUOTED_IDENTIFIER OFF
>GO
>SET ANSI_NULLS ON
>GO
>I'm not understanding?!
>Anyone has idea?
>Thanks for help.|||Well, I endly solve my problem.
So I check that I had an index is breaked. And in 10:00 AM I had an
agent who reindex it.
Thanks for your idees, I solve that problem with profiler...
But what I dont explain is why with JDBC (respectively my application
and Aqua Data Studio) it was slow, and with MS Query Analyser it was
quick...
Thanks one more for you help.
Pascal
Sue Hoegemeier <Sue_H@.nomail.please> wrote in message news:<sn99q0p7d6ii61m735s2mdt4eed5h23q
18@.4ax.com>...[vbcol=seagreen]
> As Uri already indicated, you may not want to assume it's
> just a JDBC issue. In addition to profiler, you would want
> to check the activity on the server (using PerfMon) as well
> as the activity in SQL Server. When something runs slow at
> particular times, it's often due to other activities on the
> server or in SQL Server that are using a lot of resources.
> -Sue
> On 24 Nov 2004 00:54:00 -0800, pascal_fluck@.hotmail.com
> (crabouif) wrote:
>|||One thing is that you could have been seeing the effects of
caching and in combination with calling the stored procedure
differently in the two scenarios. You can monitor the
caching - cache hits, cache misses, etc. using Profiler.
-Sue
On 25 Nov 2004 22:58:40 -0800, pascal_fluck@.hotmail.com
(crabouif) wrote:
[vbcol=seagreen]
>Well, I endly solve my problem.
>So I check that I had an index is breaked. And in 10:00 AM I had an
>agent who reindex it.
>Thanks for your idees, I solve that problem with profiler...
>But what I dont explain is why with JDBC (respectively my application
>and Aqua Data Studio) it was slow, and with MS Query Analyser it was
>quick...
>Thanks one more for you help.
> Pascal
>
>Sue Hoegemeier <Sue_H@.nomail.please> wrote in message news:<sn99q0p7d6ii61m
735s2mdt4eed5h23q18@.4ax.com>...
Monday, February 20, 2012
JDBC Driver issues on Windows 2003
I'm running an application the is running on Weblogic 8.1.4 and uses MS SQL
Server 2000 as the db. I used the MS SQL JDBC driver SP3 and everything works
fine on a Windows 2000 box.
Now when I installed the same app on a Windows 2003 Enterprise Server, I get
the following errors,
<Jun 22, 2005 5:20:21 PM MDT> <Warning> <JDBC> <BEA-001129> <Received
exception
while creating connection for pool "BizLogicDBPool": [Microsoft][SQLServer
2000
Driver for JDBC][SQLServer]Cannot load the DLL sqljdbc.dll, or one of the
DLLs i
t references. Reason: 126(The specified module could not be found.).>
I tried adding sqljdbc.dll in the classpath, in the app startup script and
no use. Any ideas, pointers, help ? Thanks in advance for all your help.
Dallas Cowboy wrote:
> I'm running an application the is running on Weblogic 8.1.4 and uses MS SQL
> Server 2000 as the db. I used the MS SQL JDBC driver SP3 and everything works
> fine on a Windows 2000 box.
> Now when I installed the same app on a Windows 2003 Enterprise Server, I get
> the following errors,
> <Jun 22, 2005 5:20:21 PM MDT> <Warning> <JDBC> <BEA-001129> <Received
> exception
> while creating connection for pool "BizLogicDBPool": [Microsoft][SQLServer
> 2000
> Driver for JDBC][SQLServer]Cannot load the DLL sqljdbc.dll, or one of the
> DLLs i
> t references. Reason: 126(The specified module could not be found.).>
> I tried adding sqljdbc.dll in the classpath, in the app startup script and
> no use. Any ideas, pointers, help ? Thanks in advance for all your help.
Hi. In order to do XA calls, the driver requires that the sqljdbc.dll
be installed *at the DBMS*. It is accessed directly by the DBMS via
extended stored procedures which should be also installed in the DBMS.
Please see our documents on the MS SQLserver driver.
I hope this helps,
Joe Weinstein at BEA
Server 2000 as the db. I used the MS SQL JDBC driver SP3 and everything works
fine on a Windows 2000 box.
Now when I installed the same app on a Windows 2003 Enterprise Server, I get
the following errors,
<Jun 22, 2005 5:20:21 PM MDT> <Warning> <JDBC> <BEA-001129> <Received
exception
while creating connection for pool "BizLogicDBPool": [Microsoft][SQLServer
2000
Driver for JDBC][SQLServer]Cannot load the DLL sqljdbc.dll, or one of the
DLLs i
t references. Reason: 126(The specified module could not be found.).>
I tried adding sqljdbc.dll in the classpath, in the app startup script and
no use. Any ideas, pointers, help ? Thanks in advance for all your help.
Dallas Cowboy wrote:
> I'm running an application the is running on Weblogic 8.1.4 and uses MS SQL
> Server 2000 as the db. I used the MS SQL JDBC driver SP3 and everything works
> fine on a Windows 2000 box.
> Now when I installed the same app on a Windows 2003 Enterprise Server, I get
> the following errors,
> <Jun 22, 2005 5:20:21 PM MDT> <Warning> <JDBC> <BEA-001129> <Received
> exception
> while creating connection for pool "BizLogicDBPool": [Microsoft][SQLServer
> 2000
> Driver for JDBC][SQLServer]Cannot load the DLL sqljdbc.dll, or one of the
> DLLs i
> t references. Reason: 126(The specified module could not be found.).>
> I tried adding sqljdbc.dll in the classpath, in the app startup script and
> no use. Any ideas, pointers, help ? Thanks in advance for all your help.
Hi. In order to do XA calls, the driver requires that the sqljdbc.dll
be installed *at the DBMS*. It is accessed directly by the DBMS via
extended stored procedures which should be also installed in the DBMS.
Please see our documents on the MS SQLserver driver.
I hope this helps,
Joe Weinstein at BEA
Subscribe to:
Posts (Atom)