JDBC Driver issue: slow

Showing posts with label slow. Show all posts

Wednesday, March 7, 2012

Job activity = slow, non job = fast

I have created an integration services package with a script-source reading data from Active directory. Pretty much data is read and written into a sql2005 database.

I have notised that when I run the package via right-clicking the package (Under stored packages\MSDB) and selecting "Run Package" it takes about 45 minutes for it to complete successfully. The same goes if I run the package from inside Visual Studio. However, when I create a job and put this package as one of it's tasks the task takes about 1h 40min. This is more then double the time! In neither case the server is occupied with soething else. And I have tried it several times so it wasn't just an "accident".

Any ida of what this depends on?

regards Andreas

I don't know the answer but I have exactly the same happening for a package I've just created that just copies data from an Ingres database to SQL Server.

Visual studio 45 mins, scheduled job 2 hrs 5 mins

Shocking!

|||

You both have conflicting results, so here are some points to think about.

Visual Studio includes debugging, which is slower. You can run without, Ctrl+F5. This should be faster than the default F5 execution behaviour. Debugging can have a high overhead due to all the extra messages. Excessive logging can also have an impact, this is similar to debugging in many resects due to the extra chatter going on to give progress information.

Unless the Visual Studio session and the SQL Server Agent used for the job are on the same local machine, there is a change in execution location between the two methods. This can have a significant impact on performance depending on your source and destination locations. Data may have to travel over the network to the server or workstation, and this can vary greatly.

|||

Wicket wrote:

I have created an integration services package with a script-source reading data from Active directory. Pretty much data is read and written into a sql2005 database.
I have notised that when I run the package via right-clicking the package (Under stored packages\MSDB) and selecting "Run Package" it takes about 45 minutes for it to complete successfully. The same goes if I run the package from inside Visual Studio. However, when I create a job and put this package as one of it's tasks the task takes about 1h 40min. This is more then double the time! In neither case the server is occupied with soething else. And I have tried it several times so it wasn't just an "accident".
Any ida of what this depends on?
regards Andreas

That sounds strange. What happens if you execute the package using dtexec.exe?

If it works quicker using dtexec (which I would expect) then use a command-line sub-step in the Agent job rather than the SSIS sub-step. In fact you should be doing this anyway because you get more debugging information. There's an article about this on wiki.sqlis.com but that site is down at the moment so I can't link to it.

-Jamie

|||

Is this the article you mean-

Comparing Overhead On The Execution Methods
(http://www.sqlis.com/default.aspx?84)

I think the main site and wiki are both up, although with my internet connection quality today they may was well not be.

|||

I'm running Visual Studio on the server itself, not a workstation, so I'm not hopping round the network when running the package from VS but that's beside the point isn't it?

I've stored the package in msdb, scheduled it to run using an agent job using the SQL Server service acount and it takes nearly 3 times as long, I'd expected it to run slow in VS but not as a scheduled job.

|||

DarrenSQLIS wrote:

Is this the article you mean-
Comparing Overhead On The Execution Methods
(http://www.sqlis.com/default.aspx?84)
I think the main site and wiki are both up, although with my internet connection quality today they may was well not be.

No. I mean this one: http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html (site is up now)

-Jamie

|||

bobbins wrote:

I'm running Visual Studio on the server itself, not a workstation, so I'm not hopping round the network when running the package from VS but that's beside the point isn't it?
I've stored the package in msdb, scheduled it to run using an agent job using the SQL Server service acount and it takes nearly 3 times as long, I'd expected it to run slow in VS but not as a scheduled job.

Did you try running it using dtexec?

Is SQL Agent running as a different user?

-Jamie

|||

Jamie Thomson wrote:

Did you try running it using dtexec?
Is SQL Agent running as a different user?
-Jamie

No, I don't know how to but I'll find out and give it a try.

Yes it is.

|||

I'm running the package on the server in each scenario, so no extra networktraffic should take place in any case I think. And the same package (non debug) is used all the time. However, I do run the job as a different user. Should this have any effect?

I'd love to run the package as a command line step, but when I try to do that, I can't seem to get it to read the config-file I'm giving in the command line. Instead an error message complains about that the configuration file cannot be found at the original location (the location given in the settings inside the package). Shouldn't this setting be overridden by the one on the command-line?

regards Andreas

Friday, February 24, 2012

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.

Wednesday, March 7, 2012

Job activity = slow, non job = fast

Friday, February 24, 2012

JDBC slow in the morning?!

JDBC slow in the morning?!

JDBC slow in the morning?!

JDBC Driver issue

Blog Archive

About Me