?
Solved

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

Posted on 2011-10-06
32
Medium Priority
?
288 Views
Last Modified: 2012-08-13
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

0
Comment
Question by:SeyerIT
  • 18
  • 7
  • 3
  • +1
30 Comments
 
LVL 71

Expert Comment

by:Qlemo
ID: 36924268
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.
0
 
LVL 2

Author Comment

by:SeyerIT
ID: 36924287
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

0
 
LVL 2

Author Comment

by:SeyerIT
ID: 36924292
I originally was thinking I would be adding more tables to this which is why I set the labordtl to LD.. FYI
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 2

Author Comment

by:SeyerIT
ID: 36924310
Sorry.. I thought that is where it was supposed to be. Where should it go? I am creating a SQL Query in Crystal Reports.
0
 
LVL 2

Author Comment

by:SeyerIT
ID: 36924318
OH! I'm not sure how it got in ASP.. Just looked up there and saw that.. It must have auto selected that somehow?
0
 
LVL 3

Expert Comment

by:BartVx
ID: 36924347
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)
0
 
LVL 2

Author Comment

by:SeyerIT
ID: 36924358
Thanks mlmcc..

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

Expert Comment

by:Qlemo
ID: 36924413
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.
0
 
LVL 3

Expert Comment

by:BartVx
ID: 36924428
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)
0
 
LVL 2

Author Comment

by:SeyerIT
ID: 36924444
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!
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 36924461
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
0
 
LVL 2

Author Comment

by:SeyerIT
ID: 36924535
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:)
0
 
LVL 3

Expert Comment

by:BartVx
ID: 36924596
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.
0
 
LVL 2

Author Comment

by:SeyerIT
ID: 36924606
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.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 36924798
How are they selecting?

The Crystal filter can be passed to the database.

mlmcc
0
 
LVL 2

Author Comment

by:SeyerIT
ID: 36924807
Through a Crystal Report Parameter
0
 
LVL 101

Accepted Solution

by:
mlmcc earned 2000 total points
ID: 36924949
Can you put the parameter in as a Command parameter?

mlmcc
0
 
LVL 2

Author Comment

by:SeyerIT
ID: 36924982
How do you do that? I'm not familiar with this.. Do you just add it into the query somehow?
0
 
LVL 101

Assisted Solution

by:mlmcc
mlmcc earned 2000 total points
ID: 36925518
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
0
 
LVL 2

Author Comment

by:SeyerIT
ID: 36925589
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 ?
0
 
LVL 101

Assisted Solution

by:mlmcc
mlmcc earned 2000 total points
ID: 36926886
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
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 36926891
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
0
 
LVL 2

Author Comment

by:SeyerIT
ID: 36926962
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

0
 
LVL 101

Assisted Solution

by:mlmcc
mlmcc earned 2000 total points
ID: 36927005
Is JobNum a number or string?

If string change the last line to

J.JobNum = '{?JobNumber}'

mlmcc
0
 
LVL 2

Author Comment

by:SeyerIT
ID: 36927105
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

0
 
LVL 2

Author Comment

by:SeyerIT
ID: 36927109
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

0
 
LVL 2

Author Comment

by:SeyerIT
ID: 36927112
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}')
0
 
LVL 2

Author Comment

by:SeyerIT
ID: 36927120
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.
0
 
LVL 2

Author Comment

by:SeyerIT
ID: 36927144
Ah.. Just figured it out I think.. Needed to replace that * for a % in the SQL.. Always forget they are different :(
0
 
LVL 2

Author Comment

by:SeyerIT
ID: 36927171
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!
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question