Hide Records that Were Printed

[codrutza]

Hi
I need help to write/modify a SP/script.
I have:
ALTER PROCEDURE [dbo].[test]
@Book varchar(8)

AS
set nocount on;
SELECT
Jobs.Number FJJobNo,
Jobs.UnitNo FJUnitNo,
Movs.Via FMVia,
Movs.DepDate,
Movs.DepLocation,
Movs.UnitNo FMUnitNo,
JobsMovs.MovNumber FJMMoNo,
MovsStatus.Status,
[Log].Event as LogEvent,
[Log].[Key] as LogKey

FROM
((([Log] WITH(READUNCOMMITTED)
INNER JOIN JobsMovs WITH(READUNCOMMITTED)
ON JobsMovs.MovNumber=[Log].[Key])
INNER JOIN Jobs WITH(READUNCOMMITTED)
ON Jobs.Number =JobsMovs.JobNumber)
INNER JOIN Movs WITH(READUNCOMMITTED)
ON JobsMovs.MovementNumber= Movs.Number)
INNER JOIN MovsStatus WITH(READUNCOMMITTED)
ON MovsStatus.MovNumber=Movs.Number

WHERE
@Book=Jobs.Comp
AND (year(Jobs.RepDate))>=2011
AND Jobs.Via IN ('Air','Sea')
AND Movs.Via IN ('Air','Sea')
AND MovsStatus.Status='C'

I have a report (Crystal Report) based on this, grouped by JobsMovs.MovNumber. In the report I hide all the records who have logevent in (doc1 print, doc2 print, doc3 print) for a JobsMovs.MovNumber. I write in the formula @docprint: if LogEvent in (‘doc1 print’, ‘doc2 print’, ‘doc3 print’) then 1 else 0, and I suppress the section Sum ({@doc print}, {JobsMovs.MovNumber })>=1. (doc1, doc2, doc3 are basically the same document, but in time, received another name in the table)

The report is working fine, but it’s slow.
I wonder if it’s possible to write in the stored procedure/script all this or there is a better solution to accomplish this.

 

[gmmastros]

@docprint: if LogEvent in (‘doc1 print’, ‘doc2 print’, ‘doc3 print’) then 1 else 0

You can put this logic in the stored procedure like this:

ALTER PROCEDURE [dbo].[test]
@Book varchar(8)

AS 
set nocount on;
SELECT 
Jobs.Number FJJobNo, 
Jobs.UnitNo FJUnitNo,
Movs.Via FMVia, 
Movs.DepDate, 
Movs.DepLocation, 
Movs.UnitNo FMUnitNo,
JobsMovs.MovNumber FJMMoNo,
MovsStatus.Status,
[Log].Event as LogEvent,
[Log].[Key] as LogKey[!],
Convert(Bit, Case When [Log].Event In ('doc1 print','doc2 print','doc3 print') 
                  Then 1 Else 0 End) As DocPrint[/!]

FROM 
((([Log] WITH(READUNCOMMITTED)
INNER JOIN JobsMovs WITH(READUNCOMMITTED)
ON JobsMovs.MovNumber=[Log].[Key])
INNER JOIN Jobs WITH(READUNCOMMITTED)
ON Jobs.Number =JobsMovs.JobNumber)
INNER JOIN Movs WITH(READUNCOMMITTED)
ON JobsMovs.MovementNumber= Movs.Number)
INNER JOIN MovsStatus WITH(READUNCOMMITTED)
ON MovsStatus.MovNumber=Movs.Number

WHERE 
@Book=Jobs.Comp
AND (year(Jobs.RepDate))>=2011 
AND Jobs.Via IN ('Air','Sea') 
AND Movs.Via IN ('Air','Sea') 
AND MovsStatus.Status='C'

I notice that you are using read uncommited on your tables. Are you having performance/locking problems?



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[codrutza]

Hi George, I was busy doing some other reports, so I haven't time to test it yet. The programmers who built the application told me that I have to use read uncommitted in the SPs that I write for the reports. I don't remember why; I think running the reports slowed down the application - for the other users; (or the reports slowed down themselves??)

 

[gmmastros]

I usually get a little nervous when I see nolock or read uncommitted. You do understand the implications of this, right?

Basically, if another process is in the middle of updating data (including inserts or deletes), your query will get the data even before the transaction is committed. This means that you could be reporting on data that never actually gets saved (committed).

Anyway.... to save yourself some aggravation, you could do this instead:

ALTER PROCEDURE [dbo].[test]
@Book varchar(8)

AS 
set nocount on;
[!]SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED[/!]

SELECT Jobs.Number FJJobNo, 
       Jobs.UnitNo FJUnitNo,
       Movs.Via FMVia, 
       Movs.DepDate, 
       Movs.DepLocation, 
       Movs.UnitNo FMUnitNo,
       JobsMovs.MovNumber FJMMoNo,
       MovsStatus.Status,
       [Log].Event as LogEvent,
       [Log].[Key] as LogKey,
       Convert(Bit, Case When [Log].Event In ('doc1 print','doc2 print','doc3 print') 
                         Then 1 Else 0 End) As DocPrint
FROM   [Log]
       INNER JOIN JobsMovs
         ON JobsMovs.MovNumber=[Log].[Key]
       INNER JOIN Jobs 
         ON Jobs.Number =JobsMovs.JobNumber
       INNER JOIN Movs 
         ON JobsMovs.MovementNumber= Movs.Number
       INNER JOIN MovsStatus 
         ON MovsStatus.MovNumber=Movs.Number

WHERE  @Book=Jobs.Comp
       AND (year(Jobs.RepDate))>=2011 
       AND Jobs.Via IN ('Air','Sea') 
       AND Movs.Via IN ('Air','Sea') 
       AND MovsStatus.Status='C'

By setting the transaction isolation level at the top of the procedure, it affects all of the queries in the procedure. Basically, it prevents you from having to include it on each table.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[codrutza]

1. No, I don’t understand. Thank you for the tip with readuncommitted. So are you saying if a job – let’s say from China - is updated being from Japan, in this moment, my report will still retrieve China, and only AFTER the user who updates SAVE the job, retrieves Japan?
So if I write SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED, I won’t have any problem?

2. I have a group on mov. Now the report prints:

mov1
Updated False
New False

mov2
Updated False
New False

mov3
doc1 print True
New False
Updated False

mov4
New False
Updated False

and I have to print only

mov1
Updated False
New False

mov2
Updated False
New False

mov4
New False
Updated False

So the mov in which I have just one time doc1 print, has to disappear completely. (The mov that has a doc printed – the log event). I’m sorry if I don’t explain right.