Being rather new to creating SQL queries, is there a way to speed up this query I wrote?

There query is faster if you just select the field LD.LaborHrs (and GROUP BY it, of course)? You should compare the query execution plans for both variants, to see why. Probably the statistics are out of order, and the optimizer decides for a costly access path because of that, e.g. full table scan of millions of records, because the stats say there aren't that many, or not very unique for JobNum.

Please my ignorance on this matter but I'm not sure how to check the query execution plans for the variants..

If it helps, here is the other SQL statement that I am using in my sub report..

SELECT LD.JobNum,
LD.PayrollDate,
LD.EarnedHrs,
LD.LaborHrs,
LD.IndirectCode,
LD.ResourceGrpID,
LD.OpCode,
LD.ReWork,
LD.BurdenHrs,
LD.LaborType
FROM LaborDtl LD
WHERE LD.ResourceGrpID <> 'ACCT'
AND LD.ResourceGrpID <> 'Sales'
AND LD.ResourceGrpID <> 'Pur' AND
LD.LaborHedSeq > 0

Select allOpen in new window

I originally was thinking I would be adding more tables to this which is why I set the labordtl to LD.. FYI

Sorry.. I thought that is where it was supposed to be. Where should it go? I am creating a SQL Query in Crystal Reports.

OH! I'm not sure how it got in ASP.. Just looked up there and saw that.. It must have auto selected that somehow?

Have you created indexes for these tables? If not creating indexes on the foreign key fields might help (including the fields of the table that are selected).

e.g. :

CREATE NONCLUSTERED INDEX [IX_JobMtl_JobNum] ON [dbo].[JobMtl]
(
      [JobNum] ASC
)
INCLUDE (AssemblySeq, MtlSeq, PartNum, Description, RequiredQty, WIReqDate, IssuedQty)

(assuming jobnum is a foreign key field in JobMtl and primary key of JobAsmbl)

Thanks mlmcc..

Bart.. I have never done anything with indexes.. Do I create that in the crystal query with my other query?

Creating indexes is done once (in a while), and job of the DBA. It is no task you perform in a report, with very few exceptions. The index will remain there once created.

SeyerIT, I'm not familiar with Crystal Reports so I'm not sure if you can do it there.

Do you (or someone else) have direct access to the database itself (with SQL Server Management Studio for example)? If so you can create the indexes there. (you will need alter permission on the table to create the index, so a user that is owner of the database would come in handy)

Bart, I was thinking this was something that cannot be done through the Crystal Reports..  We don't have anyone who can access the database directly.. It is pretty much locked down through our ERP client.

I can just utilitze the sub report. I just thought maybe it would have been an easy fix to adjust my SQL query. No biggie. Thanks for the help though!

Have you tried builing the report based on just the query without the grouping and sums?
Put the groups you need in the report and do the sums as summaries.

mlmcc

Just tried that but the results were rather undesireable as it seems like it will create a lot more work for all my sums I need to create on the report based on the rest of the data.. (there are a lot of duplicates, which grouping in Crystal takes care of, but I will still need to make more complex formulas for all my counts and sums in Crystal.)  Not only that, but as soon as I add the Labordtl record back in the report chugs.. Its replicating the information which makes since cause its a seperate set of data all together..

Even if I Sum the LD.Laborhrs in the sql query and group everything out in Crystal it still seems like it would be more work having to add all the exceptions into the formulas to remove the duplicates..  Like I said, it did work with the above formula, it just wasn't efficient at all.. The sub report truely does seem to be the fastest/easiest method so far..

I definitely appreciate all your input though! And I did learn some other methods I can hopefully apply to other reports in all of this:)

I see that there is no WHERE clause in your original query.  Is there anyway you can restrict the number of rows that are taken into account for any of the tables?

Filtering out the unneccesary rows would result in less rows being joined together and thus performance increase.

No, unfortunately I need everything to come across and then the user selects which job they want the statistics for in the Crystal Report software.

How are they selecting?

The Crystal filter can be passed to the database.

mlmcc

Through a Crystal Report Parameter

How do you do that? I'm not familiar with this.. Do you just add it into the query somehow?

It doesn't seem to be working for some reason. I add the parameter in the Command Parameter dialog box and enter in the information and click ok. My data flashes a few times but the parameter doesn't end up showing up in the Parameter fields in the field explorer.. When I go back to the Command dialog box, the parameter is no longer listed. I tried deleting the original parameter that was in there as well just to be sure that wasn't causing any conflicts but it did the same thing..

Any ideas ?

Adding it in the Command allows Crystal to pass the parameter to the database for filtering.  Thus less data is passed to the reort so it should run faster.

mlmcc

AH! ok.. Well It prompts me to enter the job number but when I hit ok I get some error messages..

 
 
Here is the code as I have it with the parameter:

SELECT J.JobNum,
J.AssemblySeq,
J.MtlSeq,
J.PartNum,
J.Description,
J.RequiredQty,
J.WIReqDate,
J.IssuedQty,
JA.StartDate,
PD.PONum,
PD.OrderQty,
PR.PONum AS PONumToJob,
PR.XRelQty AS POOrderQtyToJob,
PR.OpenRelease,
JH.PartNum AS JHPartNum,
JH.PartDescription AS JHPartDescription,
JH.ProdQty AS JHProdQty,
JH.StartDate AS JHStartDate,
JH.DueDate AS JHDueDate,
JH.ReqDueDate AS JHReqDueDate,
SUM(P.OnHandQty) AS OnHandQty
FROM JobMtl AS J
LEFT JOIN JobAsmbl AS JA
ON J.JobNum = JA.JobNum AND
J.AssemblySeq = JA.AssemblySeq 
LEFT JOIN PartBin AS P 
ON J.PartNum = P.PartNum
LEFT JOIN PODetail AS PD
ON J.JobNum = PD.ShortChar01 AND
J.PartNum = PD.PartNum
LEFT JOIN JobHead AS JH
ON J.JobNum = JH.JobNum
LEFT JOIN PORel AS PR
ON PR.JobNum = J.JobNum AND
PR.AssemblySeq = J.AssemblySeq AND
PR.JobSeq = J.MtlSeq
GROUP BY  J.JobNum,
J.AssemblySeq,
J.MtlSeq,
J.PartNum,
J.Description,
J.RequiredQty,
J.WIReqDate,
J.IssuedQty,
JA.StartDate,
PD.PONum,
PD.OrderQty,
PR.PONum,
PR.XRelQty,
PR.OpenRelease,
JH.PartNum,
JH.PartDescription,
JH.ProdQty,
JH.StartDate,
JH.DueDate,
JH.ReqDueDate
HAVING J.Description NOT LIKE '%Rework%' AND
J.Description NOT LIKE '%Plan%' AND
J.Description NOT LIKE '%Freight%' AND
J.Description NOT LIKE '%Tooling%' AND
J.JobNum = {?JobNumber}

Select allOpen in new window

Oh boy! This is looking very promising! One other question with this now, is can I use this same method on my subreport which is searching through all of the data in the db as well? The difference in the sub report is that I need to add some characters to the filter..

Currently in Crystal the formula for filtering is:

{Command.JobNum} LIKE 'R'+{?Pm-Command.JobNum}+'*' or
{Command.JobNum} = {?Pm-Command.JobNum}

So can I translate this and use it in the SQL Query? I tried a few combinations so far but can only get the core jobs and not the jobs that start with R.

Here is the SQL Query at the moment..

SELECT LD.JobNum,
LD.PayrollDate,
LD.EarnedHrs,
LD.LaborHrs,
LD.IndirectCode,
LD.ResourceGrpID,
LD.OpCode,
LD.ReWork,
LD.BurdenHrs,
LD.LaborType
FROM LaborDtl LD
WHERE LD.ResourceGrpID <> 'ACCT'
AND LD.ResourceGrpID <> 'Sales'
AND LD.ResourceGrpID <> 'Pur' AND
LD.LaborHedSeq > 0 AND
[b](LD.JobNum LIKE 'R'+'{?PM-Command.JobNum}'+'*' OR
LD.JobNum = '{?PM-Command.JobNum}')[b/]

Select allOpen in new window

SELECT LD.JobNum,
LD.PayrollDate,
LD.EarnedHrs,
LD.LaborHrs,
LD.IndirectCode,
LD.ResourceGrpID,
LD.OpCode,
LD.ReWork,
LD.BurdenHrs,
LD.LaborType
FROM LaborDtl LD
WHERE LD.ResourceGrpID <> 'ACCT'
AND LD.ResourceGrpID <> 'Sales'
AND LD.ResourceGrpID <> 'Pur' AND
LD.LaborHedSeq > 0 AND
[b](LD.JobNum LIKE 'R'+'{?PM-Command.JobNum}'+'*' OR
LD.JobNum = '{?PM-Command.JobNum}')[/b]

Select allOpen in new window

SELECT LD.JobNum,
LD.PayrollDate,
LD.EarnedHrs,
LD.LaborHrs,
LD.IndirectCode,
LD.ResourceGrpID,
LD.OpCode,
LD.ReWork,
LD.BurdenHrs,
LD.LaborType
FROM LaborDtl LD
WHERE LD.ResourceGrpID <> 'ACCT'
AND LD.ResourceGrpID <> 'Sales'
AND LD.ResourceGrpID <> 'Pur' AND
LD.LaborHedSeq > 0 AND
(LD.JobNum LIKE 'R'+'{?PM-Command.JobNum}'+'*' OR
LD.JobNum = '{?PM-Command.JobNum}')

Well the.. Didn't realize I couldnt highlight in the code box.. Sorry.. The last one is the one that represents how I currently have it filtered in the SQL.

Ah.. Just figured it out I think.. Needed to replace that * for a % in the SQL.. Always forget they are different :(

Wow.. Aside from learning how to join more than 2 tables in the SQL yesterday this is the next best thing! Talk about a time saver! mlmcc, your the best! Thanks!