It Works on SQL Server But Slow for Report

[codrutza]

thread183-1719654


I wrote it wrong, sorry.

I tested it on SQL Server and it works fine, but when I want to base a report on it, takes too much time - not responding for 20 minutes, and I cancelled it. The report is Crystal Reports.

Any ideas, I appreciate.

 

[gmmastros]

Is this the query you're talking about?

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'

If so, please run the following queries in SQL Server Management studio and post the results.

sp_helpindex 'Log'
sp_helpindex 'JobsMov'
sp_helpindex 'Jobs'
sp_helpindex 'Movs'
sp_helpindex 'MovStatus'

Also.... approximately how many rows are in each of those tables?

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

Thank you, George.

The report it running faster now - 1 minute.

The log table has very many records. It records any action on a job: printing, updating, accounting,... so a job could have over 20 records in the table Log.

In SQL Server DocPrint is 1 or 0, but when I run the report DocPrint is false/true. (Crystal Reports - I think - doesn't convert the Boolean from false/true in 0/1, so I created a formula for this).

I don't know how to run
sp_helpindex 'Log'
sp_helpindex 'JobsMov'
sp_helpindex 'Jobs'
sp_helpindex 'Movs'
sp_helpindex 'MovStatus'

I don't know where to write these statements.

 

[codrutza]

I found how to execute the statements, but I'm not sure I am allowed to give the result. Sorry.