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>...