none
Query running very slow RRS feed

  • Question

  • I created proper indexes on tables but Query running very slow

    Please help me on below query

    DECLARE @ProductNo INT=1
    DECLARE @efftgt VARCHAR(MAX) = (SELECT SUBSTRING(Prd_data,1,3) FROM txt WHERE txt_group = 'GTT' AND txt_index = @ProductNo)
    DECLARE @qtytgt VARCHAR(MAX) = (SELECT SUBSTRING(Prd_data,5,4) FROM txt WHERE txt_group = 'GTT' AND txt_index = @ProductNo)
    SELECT m.mach_nbr, 
           m.mach_serial_nbr, 
           m.mach_line, 
       (SELECT RTRIM(Prd_data) AS 'Prd_data'  FROM txt WHERE txt_group ='CLM'  AND txt_index = m.mach_class) AS mach_class ,
       (SELECT RTRIM(Prd_data) AS 'Prd_data'  FROM txt WHERE txt_group ='MTP'  AND txt_index = m.mach_type)  AS mach_type ,
           i.ipr_line, 
           i.ipr_station, 
       (SELECT Prd_data FROM txt WHERE txt_group='STA' AND txt_index=i.ipr_station) AS job_desc,
           m.mach_station,        
           RIGHT(m.mach_termid, 4) mach_termid, 
           i.units, 
           i.eff,
       CASE WHEN i.eff > @efftgt+5 THEN 'toomuch' 
    WHEN i.eff > @efftgt-5  AND i.eff < @efftgt+5 THEN 'normal'
    WHEN i.eff > @efftgt-10 AND i.eff < @efftgt-5 THEN 'delayedatt'
    WHEN i.eff < @efftgt-10 AND i.eff > 0 THEN 'delayedwar'
    WHEN i.eff = 0 THEN 'poweroff'
    END AS Prdchkr,
    i.pr_Prd_nbr,
    i.pr_name_format AS employee, 
    RTRIM(s.dt_style_nbr) AS dt_style_nbr, 
    s.sdt_seq_nbr, 
       CASE WHEN m.mach_prob_code > '' THEN  RTRIM(m.mach_prob_code)
       ELSE '' 
       END AS mach_prob_code,   
       ISNULL(@efftgt,0) AS EffTgt,
       ISNULL(@qtytgt,0) AS QtyTgt,
       ISNULL((SELECT color FROM sta WHERE job=i.ipr_station ),0) AS color
    FROM   pmachine m 
           INNER JOIN (SELECT ipr_Prd_nbr, ipr_line, ipr_station,ipr_terminal_id,ipr_name_format,
    SUM(ipr_prod_unts) AS units,SUM(ipr_prod_sah)/SUM(ipr_hrs_worked) *100 AS eff
       FROM thisipr   
    GROUP BY ipr_Prd_nbr, ipr_line, ipr_station,ipr_terminal_id,ipr_name_format
    HAVING SUM(ipr_hrs_worked)<>0
    ) i ON i.ipr_terminal_id = RIGHT(m.mach_termid,4)   
           INNER JOIN style s 
                   ON s.dt_style_nbr in (SELECT log_style_nbr FROM thislog WHERE log_line_nbr = @ProductNo AND log_style_nbr > '')   
                      AND s.sdt_station = i.ipr_station 
    WHERE i.ipr_line=@ProductNo 
    ORDER  BY sdt_seq_nbr 

    Thanks in advance


    Monday, July 22, 2019 2:05 AM

All replies

  • Good day ?!?

    In order to discuss performance we need to be able to reproduce the execution and get information regarding your execution. In general the same query can be executed differently in two different table/database/environment

    1. Please provide DDL+DML (queries to CREATE the relevant table(s) and to insert sample data). This will help to reproduce something that we can play with. 

    2. Please provide your real Execution Plan (EP) - We need the XML version and not only image. This will give us the information regarding what EP your server built in order to execute the query (there are tens of parameters which can lead the server to build a different EP so we need to see yours)

    I did not read the query fully without this information, since it is much simpler and faster for me to start by watching the actual case and we will need this information anyway as soon as we can in order to discuss performance and improving of the query.

    With that being said, in the mean time, here are some general points:

    I noticed that you use multiple sub-queries. This can be something that slow your execution. You should try to avoid executing duplicate queries were you can get the data in once, and test the option of using SET approach (meaning for example using JOIN) instead of using sub-queries.

    1. For example, why do you need to execute two queries that explore the same table with the same filter, instead of doing this in one time:

    -- Why execute two queries:
    DECLARE @ProductNo INT=1
    DECLARE @efftgt VARCHAR(MAX) = (
    	SELECT SUBSTRING(Prd_data,1,3) FROM txt WHERE txt_group = 'GTT' AND txt_index = @ProductNo
    )
    DECLARE @qtytgt VARCHAR(MAX) = (
    	SELECT SUBSTRING(Prd_data,5,4) FROM txt WHERE txt_group = 'GTT' AND txt_index = @ProductNo
    )
    -- when we can get both values in one execution:
    DECLARE @efftgt VARCHAR(MAX),@qtytgt VARCHAR(MAX)
    SELECT @efftgt = SUBSTRING(Prd_data,1,3), @qtytgt = SUBSTRING(Prd_data,5,4)
    FROM txt WHERE txt_group = 'GTT' AND txt_index = @ProductNo

    I noticed this part in your query:

       (SELECT RTRIM(Prd_data) AS 'Prd_data'  FROM txt WHERE txt_group ='CLM'  AND txt_index = m.mach_class) AS mach_class ,
       (SELECT RTRIM(Prd_data) AS 'Prd_data'  FROM txt WHERE txt_group ='MTP'  AND txt_index = m.mach_type)  AS mach_type ,
    ...
    (SELECT Prd_data FROM txt WHERE txt_group='STA' AND txt_index=i.ipr_station) AS job_desc,

    Do you really have the best indexes for these sub-queries (Index that fit the filter part and include the column which you want ton use for example), and did you confirmed that the server actually use these indexes?

    * If we will get the EP then we might know the answer.

    2. You execute the same table three times inside sub-query in order to get the same column. The only difference is that each time you want to use different rows (different filter). In some cases (depend on multiple parameter like number of rows which are filtered), instead you can test the option of getting the data from txt table once using CTE and filter the data so you will get both set of rows together, and next you can use JOIN instead of sub-queries. A JOIN can be faster than an equivalent sub-query because the server might be able to optimize it better. You should test it in your case.


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]



    Monday, July 22, 2019 3:47 AM
    Moderator
  • Can you show us an execution plan of the query?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, July 22, 2019 4:15 AM
    Answerer
  • Hi BHVS,

     

    Thank you for your issue .

     

    Could you please share us your execution plan? So that we’ll get a right direction .

     

    Also in your script , I find that you use IN  and order by . I think this might lead to performance problems.

     

    Please refer to following script .

     

     INNER JOIN style s 
                   ON s.sdt_station = i.ipr_station 
    WHERE i.ipr_line=@ProductNo 
    and exists (SELECT 1 FROM thislog WHERE log_line_nbr = @ProductNo AND log_style_nbr > '' and  s.dt_style_nbr=log_style_nbr) 

     

    Hope it will help you .

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, July 23, 2019 6:50 AM