/****** Object: Stored Procedure dbo.BENESP_JournalEntrySearch Script
Date: 4/23/2004 11:45:30 AM ******/
CREATE PROCEDURE [DBO].[BSP_JournalEntrySearch]
@.Type int = null,
@.User varchar(50) = null,
@.EntryState int = 2, -- 1 open, 0 closed, 2 either
@.DateEnteredType int = 0, -- 0 both, 1 entrys only, 2 events only
@.DateEnteredStart datetime = null,
@.DateEnteredEnd datetime = null,
@.DateDueStart datetime = null,
@.DateDueEnd datetime = null,
@.AccountFor int = null,
@.messageText varchar(4000) = null,
@.loginname varchar(50) = '', -- Required login name for user perfoming
search
@.IncludeRouted bit = null,
@.enrollee int = null
AS
-- REMEMBER TO REMOVE TIME FROM DATES SO WE CAN CHECK THE DATE ONLY
-- DBO.RemoveTimeFromDate function removes time from datetime's
SET NOCOUNT ON
-- get message list and join it to its initial message from the events table
based on most recent message
select a.EventMessageText,JETR.* from
(select DISTINCT
People.InformalName,Accounts.AccountName,JournalEntryTypes.TypeName,JournalEntrySu
bTypes.[Name],JournalEntryTypes.AssociatedFormIDCode,journalentries.*,
(select count(*) from JournalEntryJunStoredFiles where JournalEntryID =
journalentries.JournalEntryID) as AttachmentCount
from JournalEntries left outer join JournalEvents on
JournalEntries.JournalEntryID = JournalEvents.JournalEntryID
left outer join JournalEntrySubTypes on JournalEntries.JETSubTypeID =
JournalEntrySubTypes.JETSubTypeID
left outer join JournalEntryTypes on JournalEntrySubTypes.JETypeID =
JournalEntryTypes.JETypeID
left outer join JournalEntryTypePermissions on
JournalEntryTypes.JETypeID = JournalEntryTypePermissions.JETypeID
left outer join Accounts on JournalEntries.RelatedAccountID =
Accounts.AccountID
left outer join People on JournalEntries.Enrollee = People.PersonID
where 1=1 and (journalentries.creator = @.loginname or
JournalEntries.JournalEntryID in (select
JournalEntryJunJournalRoutingClass.JournalEntryID
from (JournalEntryJunJournalRoutingClass left outer join
JournalRoutingClasses on JournalEntryJunJournalRoutingClass.JRCID =
JournalRoutingClasses.JRCID)
left outer join JRCJunUsers on JournalRoutingClasses.JRCID
= JRCJunUsers.JRCID
where JRCJunUsers.loginname = @.loginname))
-- search criteria
AND (@.messageText IS NULL OR FREETEXT(JournalEvents.*,@.messagetext)) --
TEXT SEARCH
AND (@.enrollee IS NULL OR JournalEntries.Enrollee = @.enrollee)
AND (@.IncludeRouted IS NULL OR 1=1) -- is routed to user or their
own messages
AND (@.Type IS NULL OR JournalEntrySubTypes.JETSubTypeID = @.Type)
AND (@.AccountFor IS NULL OR JournalEntries.RelatedAccountID = @.AccountFor)
AND ((@.DateDueStart IS NULL AND @.DateDueEnd IS NULL) OR
DBO.RemoveTimeFromDate(JournalEntries.DueDate) between
DBO.RemoveTimeFromDate(@.DateDueStart) AND
DBO.RemoveTimeFromDate(@.DateDueEnd))
AND (@.User IS NULL OR (JournalEntries.Creator = @.user or
JournalEvents.Creator = @.user))
AND ((@.entrystate = 0 and JournalEntries.EntryClosed = 1)
OR (@.entrystate = 1 and JournalEntries.EntryClosed = 0)
OR (@.entrystate = 2 and (JournalEntries.EntryClosed = 0 or
JournalEntries.EntryClosed = 1)))
-- date range entry check stuff
AND (@.DateEnteredStart IS NULL OR ((@.DateEnteredType = 0 OR
@.DateEnteredType = 1) AND DBO.RemoveTimeFromDate(JournalEntries.EntryDate)
>= @.dateEnteredStart) OR ((@.DateEnteredType = 0 OR @.DateEnteredType = 2) AND
DBO.RemoveTimeFromDate(JournalEvents.EventDate) >=
DBO.RemoveTimeFromDate(@.DateEnteredStart)))
AND (@.DateEnteredEnd IS NULL OR ((@.DateEnteredType = 0 OR @.DateEnteredType
= 1) AND DBO.RemoveTimeFromDate(JournalEntries.EntryDate) <=
@.DateEnteredEnd) OR ((@.DateEnteredType = 0 OR @.DateEnteredType = 2) AND
DBO.RemoveTimeFromDate(JournalEvents.EventDate) <=
DBO.RemoveTimeFromDate(@.DateEnteredEnd)))
AND permissionid in (select permissionid from
bene_users.dbo.UsersPermissions where loginname = @.loginname)) JETR
-- gets initial message for the entry after we know what messages we have
searched for
left outer join (SELECT JournalEntryID,EventMessageText FROM JournalEvents
je1 WHERE je1.EventDate = (SELECT MIN(je2.EventDate ) FROM JournalEvents
je2 WHERE je1.JournalEntryID = je2.JournalEntryID)) a on JETR.JournalEntryID
= a.JournalEntryID
GO
which takes about 14 seconds to run when I have the last left outer join on
it to get the initial text from the JouranlEvents table... without it it
takes under 1 second to run on a table of thousands of entries in the
journalentries table...
here is my trace on it
SET STATISTICS PROFILE ON
SQL:StmtCompleted 0 0 0 0 SET NOCOUNT ON -- get message list and join
it to its initial message from the events table based on most recent message
SP:StmtCompleted 0 0 0 0 select a.EventMessageText,JETR.* from (select
DISTINCT
People.InformalName,Accounts.AccountName,JournalEntryTypes.TypeName,JournalEntrySu
bTypes.[Name],JournalEntryTypes.AssociatedFormIDCode,journalentries.*,
(select count(*) f
SP:StmtCompleted 153 34 91 0 exec BENESP_JournalEntrySearch
@.loginname='brian_henry'
SQL:StmtCompleted 169 50 97 0 SET STATISTICS PROFILE OFF
SQL:StmtCompleted 0 0 0 0
statistics on it
Application Profile Statistics
Timer resolution (milliseconds)
0 0 Number of INSERT, UPDATE, DELETE statements
0 0 Rows effected by INSERT, UPDATE, DELETE statements
0 0 Number of SELECT statements
2 2 Rows effected by SELECT statements
100 100 Number of user transactions
5 5 Average fetch time
0 0 Cumulative fetch time
0 0 Number of fetches
0 0 Number of open statement handles
0 0 Max number of opened statement handles
0 0 Cumulative number of statement handles
0 0
Network Statistics
Number of server roundtrips
3 3 Number of TDS packets sent
3 3 Number of TDS packets received
209 209 Number of bytes sent
252 252 Number of bytes received
832650 832650
Time Statistics
Cumulative client processing time
0 0 Cumulative wait time on server replies
2.75185e+007 2.75185e+007
any idea how to speed up that last join?! i need that message appended
server side because it takes even more time client side to do it
programmatically in the application
thanks for any help!
here is the basic DDL for this too... i didnt include relations or keys in
it though...
=====================================
CREATE TABLE [dbo].[JRCJunUsers] (
[JRCID] [int] NOT NULL ,
[loginName] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[JournalEntries] (
[JournalEntryID] [int] IDENTITY (1, 1) NOT NULL ,
[RelatedAccountID] [int] NULL ,
[DueDate] [datetime] NULL ,
[Creator] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AutoCreation] [bit] NOT NULL ,
[EntryDate] [datetime] NOT NULL ,
[CloseDate] [datetime] NULL ,
[LastUpdatedDate] [datetime] NULL ,
[LastModifiedBy] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EntryClosed] [bit] NOT NULL ,
[JETSubTypeID] [int] NOT NULL ,
[Enrollee] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[JournalEntryJunJournalRoutingClass] (
[JournalEntryID] [int] NOT NULL ,
[JRCID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[JournalEntryJunStoredFiles] (
[JournalEntryID] [int] NOT NULL ,
[FileID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[JournalEntryJunUsersStatus] (
[loginName] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[JournalEntryID] [int] NOT NULL ,
[IsClosed] [bit] NOT NULL ,
[Notes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateUpdated] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[JournalEntrySubTypes] (
[JETSubTypeID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[JETypeID] [int] NOT NULL ,
[DefaultJRC] [int] NULL ,
[Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[JournalEntryTypePermissions] (
[JETypeID] [int] NOT NULL ,
[PermissionID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[JournalEntryTypes] (
[JETypeID] [int] IDENTITY (1, 1) NOT NULL ,
[TypeName] [char] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AssociatedFormIDCode] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[JournalEventActions] (
[EventActionID] [int] IDENTITY (1, 1) NOT NULL ,
[EventActionName] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[ContacteeRequired] [bit] NOT NULL ,
[ActionPerformed] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IsAvailable] [bit] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[JournalEvents] (
[EventID] [int] IDENTITY (1, 1) NOT NULL ,
[JournalEntryID] [int] NOT NULL ,
[EventDate] [datetime] NOT NULL ,
[EventActionID] [int] NOT NULL ,
[Contactee] [int] NULL ,
[ContacteeHandEntered] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Creator] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[EventMessageText] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IssueResolved] [bit] NOT NULL ,
[ActionRequired] [bit] NOT NULL ,
[EventHappenedDate] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[JournalRoutingClasses] (
[JRCID] [int] IDENTITY (1, 1) NOT NULL ,
[JRCName] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Creator] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[IsPublic] [bit] NOT NULL ,
[Active] [bit] NOT NULL ,
[SystemClass] [bit] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Accounts] (
[AccountID] [int] IDENTITY (1, 1) NOT NULL ,
[AccountName] [char] (70) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AccountIndustry] [int] NULL ,
[AccountSubIndustry] [int] NULL ,
[RGCompanyID] [int] NULL ,
[BrokerOfRecordsID] [int] NULL ,
[AccountOwner] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Inactive] [bit] NOT NULL ,
[SICCode] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AccountDirector] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AccountBA] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AccountCSA] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AccountWeBill] [bit] NOT NULL ,
[AccountBillingAssociate] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[AccountExecutive] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AccountType] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[People] (
[PersonID] [int] IDENTITY (1, 1) NOT NULL ,
[FirstName] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MiddleInitial] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Suffix] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Prefix] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SSN] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DOB] [datetime] NULL ,
[Gender] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[JobTitle] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[InformalName] AS (rtrim([firstname]) + ' ' + rtrim([lastname]))
) ON [PRIMARY]
GOYes, the query is hidious :-)
A few remarks:
1) you should consider using Dynamic SQL instead of all the optional
parameter handling. The current query will not be able to use any index
on the columns mentioned in the WHERE clause
2) you should use inner joins instead of outer joins when you can. For
example, the query contains the subquery
select JournalEntryJunJournalRoutingClass.JournalEntryID
from JournalEntryJunJournalRoutingClass
left outer join JournalRoutingClasses
on JournalEntryJunJournalRoutingClass.JRCID =
JournalRoutingClasses.JRCID
left outer join JRCJunUsers
on JournalRoutingClasses.JRCID = JRCJunUsers.JRCID
where JRCJunUsers.loginname = @.loginname
The WHERE clause of this query will remove all rows of "left outer"
table JournalEntryJunJournalRoutingClass without related rows in
JournalRoutingClasses and JRCJunUsers. IOW, you can change the two "left
outer join"s to "inner join"s. This will give the optimizer more freedom
in its access path analysis.
3) The resultset "a" will contain just one row for each JournalEntryID.
This means, that you could rewrite the current statement from:
select a.EventMessageText,JETR.*
from (
select DISTINCT
People.InformalName
,Accounts.AccountName
..
,(
select count(*)
from JournalEntryJunStoredFiles
where JournalEntryID = journalentries.JournalEntryID
) as AttachmentCount
from JournalEntries
..
) JETR
left outer join (
SELECT JournalEntryID,EventMessageText
FROM JournalEvents je1
WHERE je1.EventDate = (
SELECT MIN(je2.EventDate)
FROM JournalEvents je2
WHERE je1.JournalEntryID = je2.JournalEntryID
)
) a on JETR.JournalEntryID = a.JournalEntryID
To:
select DISTINCT
People.InformalName
,Accounts.AccountName
..
,(
select count(*)
from JournalEntryJunStoredFiles
where JournalEntryID = journalentries.JournalEntryID
) as AttachmentCount
,(
SELECT EventMessageText
FROM JournalEvents je1
WHERE je1.JournalEntryID = journalentries.JournalEntryID
AND je1.EventDate = (
SELECT MIN(je2.EventDate)
FROM JournalEvents je2
WHERE je2.JournalEntryID = journalentries.JournalEntryID
)
) as EventMessageText
from JournalEntries
..
4) Remove the DISTINCT keyword if it is not necessary.
Hope this helps,
Gert-Jan
Brian Henry wrote:
> I have the following query...
> /****** Object: Stored Procedure dbo.BENESP_JournalEntrySearch Script
> Date: 4/23/2004 11:45:30 AM ******/
> CREATE PROCEDURE [DBO].[BSP_JournalEntrySearch]
> @.Type int = null,
> @.User varchar(50) = null,
> @.EntryState int = 2, -- 1 open, 0 closed, 2 either
> @.DateEnteredType int = 0, -- 0 both, 1 entrys only, 2 events only
> @.DateEnteredStart datetime = null,
> @.DateEnteredEnd datetime = null,
> @.DateDueStart datetime = null,
> @.DateDueEnd datetime = null,
> @.AccountFor int = null,
> @.messageText varchar(4000) = null,
> @.loginname varchar(50) = '', -- Required login name for user perfoming
> search
> @.IncludeRouted bit = null,
> @.enrollee int = null
> AS
> -- REMEMBER TO REMOVE TIME FROM DATES SO WE CAN CHECK THE DATE ONLY
> -- DBO.RemoveTimeFromDate function removes time from datetime's
> SET NOCOUNT ON
> -- get message list and join it to its initial message from the events tab
le
> based on most recent message
> select a.EventMessageText,JETR.* from
> (select DISTINCT
> People.InformalName,Accounts.AccountName,JournalEntryTypes.TypeName,JournalEntry
SubTypes.[Name],JournalEntryTypes.AssociatedFormIDCode,journalentries.*,
> (select count(*) from JournalEntryJunStoredFiles where JournalEntryID =
> journalentries.JournalEntryID) as AttachmentCount
> from JournalEntries left outer join JournalEvents on
> JournalEntries.JournalEntryID = JournalEvents.JournalEntryID
> left outer join JournalEntrySubTypes on JournalEntries.JETSubTypeID =
> JournalEntrySubTypes.JETSubTypeID
> left outer join JournalEntryTypes on JournalEntrySubTypes.JETypeID =
> JournalEntryTypes.JETypeID
> left outer join JournalEntryTypePermissions on
> JournalEntryTypes.JETypeID = JournalEntryTypePermissions.JETypeID
> left outer join Accounts on JournalEntries.RelatedAccountID =
> Accounts.AccountID
> left outer join People on JournalEntries.Enrollee = People.PersonID
> where 1=1 and (journalentries.creator = @.loginname or
> JournalEntries.JournalEntryID in (select
> JournalEntryJunJournalRoutingClass.JournalEntryID
> from (JournalEntryJunJournalRoutingClass left outer join
> JournalRoutingClasses on JournalEntryJunJournalRoutingClass.JRCID =
> JournalRoutingClasses.JRCID)
> left outer join JRCJunUsers on JournalRoutingClasses.JRC
ID
> = JRCJunUsers.JRCID
> where JRCJunUsers.loginname = @.loginname))
> -- search criteria
> AND (@.messageText IS NULL OR FREETEXT(JournalEvents.*,@.messagetext)) -
-
> TEXT SEARCH
> AND (@.enrollee IS NULL OR JournalEntries.Enrollee = @.enrollee)
> AND (@.IncludeRouted IS NULL OR 1=1) -- is routed to user or their
> own messages
> AND (@.Type IS NULL OR JournalEntrySubTypes.JETSubTypeID = @.Type)
> AND (@.AccountFor IS NULL OR JournalEntries.RelatedAccountID = @.AccountFo
r)
> AND ((@.DateDueStart IS NULL AND @.DateDueEnd IS NULL) OR
> DBO.RemoveTimeFromDate(JournalEntries.DueDate) between
> DBO.RemoveTimeFromDate(@.DateDueStart) AND
> DBO.RemoveTimeFromDate(@.DateDueEnd))
> AND (@.User IS NULL OR (JournalEntries.Creator = @.user or
> JournalEvents.Creator = @.user))
> AND ((@.entrystate = 0 and JournalEntries.EntryClosed = 1)
> OR (@.entrystate = 1 and JournalEntries.EntryClosed = 0)
> OR (@.entrystate = 2 and (JournalEntries.EntryClosed = 0 or
> JournalEntries.EntryClosed = 1)))
> -- date range entry check stuff
> AND (@.DateEnteredStart IS NULL OR ((@.DateEnteredType = 0 OR
> @.DateEnteredType = 1) AND DBO.RemoveTimeFromDate(JournalEntries.EntryDate)
> DBO.RemoveTimeFromDate(JournalEvents.EventDate) >=
> DBO.RemoveTimeFromDate(@.DateEnteredStart)))
> AND (@.DateEnteredEnd IS NULL OR ((@.DateEnteredType = 0 OR @.DateEnteredTy
pe
> = 1) AND DBO.RemoveTimeFromDate(JournalEntries.EntryDate) <=
> @.DateEnteredEnd) OR ((@.DateEnteredType = 0 OR @.DateEnteredType = 2) AND
> DBO.RemoveTimeFromDate(JournalEvents.EventDate) <=
> DBO.RemoveTimeFromDate(@.DateEnteredEnd)))
> AND permissionid in (select permissionid from
> bene_users.dbo.UsersPermissions where loginname = @.loginname)) JETR
> -- gets initial message for the entry after we know what messages we have
> searched for
> left outer join (SELECT JournalEntryID,EventMessageText FROM JournalEvents
> je1 WHERE je1.EventDate = (SELECT MIN(je2.EventDate ) FROM JournalEvents
> je2 WHERE je1.JournalEntryID = je2.JournalEntryID)) a on JETR.JournalEntry
ID
> = a.JournalEntryID
> GO
> which takes about 14 seconds to run when I have the last left outer join o
n
> it to get the initial text from the JouranlEvents table... without it it
> takes under 1 second to run on a table of thousands of entries in the
> journalentries table...
> here is my trace on it
> SET STATISTICS PROFILE ON
> SQL:StmtCompleted 0 0 0 0 SET NOCOUNT ON -- get message list and join
> it to its initial message from the events table based on most recent messa
ge
> SP:StmtCompleted 0 0 0 0 select a.EventMessageText,JETR.* from (select
> DISTINCT
> People.InformalName,Accounts.AccountName,JournalEntryTypes.TypeName,JournalEntry
SubTypes.[Name],JournalEntryTypes.AssociatedFormIDCode,journalentries.*,
> (select count(*) f
> SP:StmtCompleted 153 34 91 0 exec BENESP_JournalEntrySearch
> @.loginname='brian_henry'
> SQL:StmtCompleted 169 50 97 0 SET STATISTICS PROFILE OFF
> SQL:StmtCompleted 0 0 0 0
> statistics on it
> Application Profile Statistics
> Timer resolution (milliseconds)
> 0 0 Number of INSERT, UPDATE, DELETE statements
> 0 0 Rows effected by INSERT, UPDATE, DELETE statements
> 0 0 Number of SELECT statements
> 2 2 Rows effected by SELECT statements
> 100 100 Number of user transactions
> 5 5 Average fetch time
> 0 0 Cumulative fetch time
> 0 0 Number of fetches
> 0 0 Number of open statement handles
> 0 0 Max number of opened statement handles
> 0 0 Cumulative number of statement handles
> 0 0
> Network Statistics
> Number of server roundtrips
> 3 3 Number of TDS packets sent
> 3 3 Number of TDS packets received
> 209 209 Number of bytes sent
> 252 252 Number of bytes received
> 832650 832650
> Time Statistics
> Cumulative client processing time
> 0 0 Cumulative wait time on server replies
> 2.75185e+007 2.75185e+007
> any idea how to speed up that last join?! i need that message appended
> server side because it takes even more time client side to do it
> programmatically in the application
> thanks for any help!
> here is the basic DDL for this too... i didnt include relations or keys in
> it though...
[snip]|||for security reasons dynamic SQL is not an option, but thanks for the other
ideas, no one gets select persmission on any tables, just stored procedure
execute permissions
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:435D5DE8.704A3C31@.toomuchspamalready.nl...
> Yes, the query is hidious :-)
> A few remarks:
> 1) you should consider using Dynamic SQL instead of all the optional
> parameter handling. The current query will not be able to use any index
> on the columns mentioned in the WHERE clause
> 2) you should use inner joins instead of outer joins when you can. For
> example, the query contains the subquery
> select JournalEntryJunJournalRoutingClass.JournalEntryID
> from JournalEntryJunJournalRoutingClass
> left outer join JournalRoutingClasses
> on JournalEntryJunJournalRoutingClass.JRCID =
> JournalRoutingClasses.JRCID
> left outer join JRCJunUsers
> on JournalRoutingClasses.JRCID = JRCJunUsers.JRCID
> where JRCJunUsers.loginname = @.loginname
> The WHERE clause of this query will remove all rows of "left outer"
> table JournalEntryJunJournalRoutingClass without related rows in
> JournalRoutingClasses and JRCJunUsers. IOW, you can change the two "left
> outer join"s to "inner join"s. This will give the optimizer more freedom
> in its access path analysis.
> 3) The resultset "a" will contain just one row for each JournalEntryID.
> This means, that you could rewrite the current statement from:
> select a.EventMessageText,JETR.*
> from (
> select DISTINCT
> People.InformalName
> ,Accounts.AccountName
> ...
> ,(
> select count(*)
> from JournalEntryJunStoredFiles
> where JournalEntryID = journalentries.JournalEntryID
> ) as AttachmentCount
> from JournalEntries
> ...
> ) JETR
> left outer join (
> SELECT JournalEntryID,EventMessageText
> FROM JournalEvents je1
> WHERE je1.EventDate = (
> SELECT MIN(je2.EventDate)
> FROM JournalEvents je2
> WHERE je1.JournalEntryID = je2.JournalEntryID
> )
> ) a on JETR.JournalEntryID = a.JournalEntryID
> To:
> select DISTINCT
> People.InformalName
> ,Accounts.AccountName
> ...
> ,(
> select count(*)
> from JournalEntryJunStoredFiles
> where JournalEntryID = journalentries.JournalEntryID
> ) as AttachmentCount
> ,(
> SELECT EventMessageText
> FROM JournalEvents je1
> WHERE je1.JournalEntryID = journalentries.JournalEntryID
> AND je1.EventDate = (
> SELECT MIN(je2.EventDate)
> FROM JournalEvents je2
> WHERE je2.JournalEntryID = journalentries.JournalEntryID
> )
> ) as EventMessageText
> from JournalEntries
> ...
> 4) Remove the DISTINCT keyword if it is not necessary.
> Hope this helps,
> Gert-Jan
> Brian Henry wrote:
> [snip]|||I respect that security policy. Please note that the stored procedure
could be the one that is generating and executing the dynamic SQL. Maybe
that is within the limits of the security policy.
Good luck,
Gert-Jan
Brian Henry wrote:
> for security reasons dynamic SQL is not an option, but thanks for the othe
r
> ideas, no one gets select persmission on any tables, just stored procedure
> execute permissions
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:435D5DE8.704A3C31@.toomuchspamalready.nl...|||thanks! forgot to check the joins... must of been in a daze... been sick for
a few w
s now... trying to code while sick with mono... which is resultingin some very hidious SQL... just the last join alone being an outer join
which should of been an inner join reduced it from 14 seconds of execution
time (it created over 5 million rows server side and filtered it to 500)
with the outer join... to the inner join which now takes under 1 second to
execute and only created 600 rows max for the 500 it displays server side
(baseing on the trace of the execution plan and how many rows are being
moved where and such) thanks for the help
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:435E6ED8.89722775@.toomuchspamalready.nl...
>I respect that security policy. Please note that the stored procedure
> could be the one that is generating and executing the dynamic SQL. Maybe
> that is within the limits of the security policy.
> Good luck,
> Gert-Jan
>
> Brian Henry wrote:sql
No comments:
Post a Comment