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

It actually used to run in a couple of seconds, but I just added SUM(LD.LaborHrs) and it REALLY slows it down! I was thinking it would go a little faster if I tried to add it to this original statement, where initially I planned on putting it in its own statement in a subreport.. Perhaps the subreport is the better idea after all? Having it run in a sub report would yield about a 10 second run time..

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(LD.LaborHrs) AS LaborHrs,
SUM(P.OnHandQty) AS OnHandQty
FROM JobAsmbl AS JA 
LEFT JOIN JobMtl AS J
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
LEFT JOIN LaborDtl AS LD
ON LD.JobNum = J.JobNum
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

Open in new window

LVL 2
Jarred MeyerProduction ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
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.
Jarred MeyerProduction ManagerAuthor Commented:
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

Open in new window

Jarred MeyerProduction ManagerAuthor Commented:
I originally was thinking I would be adding more tables to this which is why I set the labordtl to LD.. FYI
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Jarred MeyerProduction ManagerAuthor Commented:
Sorry.. I thought that is where it was supposed to be. Where should it go? I am creating a SQL Query in Crystal Reports.
Jarred MeyerProduction ManagerAuthor Commented:
OH! I'm not sure how it got in ASP.. Just looked up there and saw that.. It must have auto selected that somehow?
BartVxCommented:
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)
Jarred MeyerProduction ManagerAuthor Commented:
Thanks mlmcc..

Bart.. I have never done anything with indexes.. Do I create that in the crystal query with my other query?
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
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.
BartVxCommented:
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)
Jarred MeyerProduction ManagerAuthor Commented:
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!
mlmccCommented:
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
Jarred MeyerProduction ManagerAuthor Commented:
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:)
BartVxCommented:
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.
Jarred MeyerProduction ManagerAuthor Commented:
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.
mlmccCommented:
How are they selecting?

The Crystal filter can be passed to the database.

mlmcc
Jarred MeyerProduction ManagerAuthor Commented:
Through a Crystal Report Parameter
mlmccCommented:
Can you put the parameter in as a Command parameter?

mlmcc

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jarred MeyerProduction ManagerAuthor Commented:
How do you do that? I'm not familiar with this.. Do you just add it into the query somehow?
mlmccCommented:
In the command edit window there should be a button for parameters
Click it
Create a parameter
Add it to the SQL in the command

mlmcc
Jarred MeyerProduction ManagerAuthor Commented:
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 ?
mlmccCommented:
Did you put it in the SQL

Create the parameter
Add it to the SQL as {SomeField} = YourParameter

Be sure to select the parameter from the parameter list

mlmcc
mlmccCommented:
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
Jarred MeyerProduction ManagerAuthor Commented:
AH! ok.. Well It prompts me to enter the job number but when I hit ok I get some error messages..

 error msg
 error msg 2
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}

Open in new window

mlmccCommented:
Is JobNum a number or string?

If string change the last line to

J.JobNum = '{?JobNumber}'

mlmcc
Jarred MeyerProduction ManagerAuthor Commented:
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/]

Open in new window

Jarred MeyerProduction ManagerAuthor Commented:
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]

Open in new window

Jarred MeyerProduction ManagerAuthor Commented:
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}')
Jarred MeyerProduction ManagerAuthor Commented:
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.
Jarred MeyerProduction ManagerAuthor Commented:
Ah.. Just figured it out I think.. Needed to replace that * for a % in the SQL.. Always forget they are different :(
Jarred MeyerProduction ManagerAuthor Commented:
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!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.