Results 1 to 4 of 4
  1. #1
    Join Date
    May 2015
    Posts
    2

    Answered: Challenging query take over 10 min

    Hi, I'm currently working on a query in SQL 2012 that takes WAY to long to gather the informations.

    Explanation : I got a table in a database that gives me information about a date a quantities or two of a certain recipe or two. I got in my SharePoint database the information about the recipe which i gather in a view to get all the information I need. Then I need to run my query to get a date when the certain amount of recipe was used, with that i get the amount and the number of the recipe and i can calculate each ingredient and how much of it we've used in the recipe.

    Code:
    Select * from
    (Select CAST(db.BE_DATE as smalldatetime) AS Date_Coule
    				, db.BE_CONT AS Contrat_Coule
    				, db.BE_MIX_F AS Recette_Coule
    				, db.BE_MET AS Metre_Coule
    				, R.Ciment1 AS Ciment1
    				, SUM(R.QteCiment1 / 1000 * db.BE_MET) AS Ciment1_Coule
    				, SUM(R.QteCiment1 / 1000 * db.BE_MET * R.Ciment1Prix) AS Ciment1_Cost
    				, R.Ciment1CodeGL AS CodeGL_Ciment1
    				, R.Ciment2 AS Ciment2
    				, SUM(R.QteCiment2 / 1000 * db.BE_MET) AS Ciment2_Coule
    				, SUM(R.QteCiment2 / 1000 * db.BE_MET * R.Ciment2Prix) AS Ciment2_Cost
    				, R.Ciment2CodeGL AS CodeGL_Ciment2
    				, R.Sable1 AS Sable1
    				, SUM(R.QteSable1 / 1000 * db.BE_MET) AS Sable1_Coule
    				, SUM(R.QteSable1 / 1000 * db.BE_MET * R.Sable1Prix) AS Sable1_Cost
    				, R.Sable1CodeGL AS CodeGL_Sable1
    				, R.Sable2 AS Sable2
    				, SUM(R.QteSable2 / 1000 * db.BE_MET) AS Sable2_Coule
    				, SUM(R.QteSable2 / 1000 * db.BE_MET * R.Sable2Prix) AS Sable2_Cost
    				, R.Sable2CodeGL AS CodeGL_Sable2
    				, R.Pierre1 AS Pierre1
    				, SUM(R.QtePierre1 / 1000 * db.BE_MET) AS Pierre1_Coule
    				, SUM(R.QtePierre1 / 1000 * db.BE_MET * R.Pierre1Prix) AS Pierre1_Cost
    				, R.Pierre1CodeGL AS CodeGL_Pierre1
    				, R.Pierre2 AS Pierre2
    				, SUM(R.QtePierre2 / 1000 * db.BE_MET) AS Pierre2_Coule
    				, SUM(R.QtePierre2 / 1000 * db.BE_MET * R.Pierre2Prix) AS Pierre2_Cost
    				, R.Pierre2CodeGL AS CodeGL_Pierre2
    				, R.Pierre3 AS Pierre3
    				, SUM(R.QtePierre3 / 1000 * db.BE_MET) AS Pierre3_Coule
    				, SUM(R.QtePierre3 / 1000 * db.BE_MET * R.Pierre3Prix) AS Pierre3_Cost
    				, R.Pierre3CodeGL AS CodeGL_Pierre3
    				, R.MicroAir AS MicroAir
    				, SUM(R.QteMicroAir / 100 * db.BE_MET) AS MicroAir_Coule
    				, SUM(R.QteMicroAir / 100 * db.BE_MET * R.MicroAirPrix) AS MicroAir_Cost
    				, R.MicroAirCodeGL AS CodeGL_MicroAir
    				, R.Adjuvant1 AS Adjuvant1
    				, SUM(R.QteAdjuvant1 / 100 * db.BE_MET) AS Adjuvant1_Coule
    				, SUM(R.QteAdjuvant1 / 100 * db.BE_MET * R.Adjuvant1Prix) AS Adjuvant1_Cost
    				, R.Adjuvant1CodeGL AS CodeGL_Adjuvant1
    				, R.Adjuvant2 AS Adjuvant2
    				, SUM(R.QteAdjuvant2 / 100 * db.BE_MET) AS Adjuvant2_Coule
    				, SUM(R.QteAdjuvant2 / 100 * db.BE_MET * R.Adjuvant2Prix) AS Adjuvant2_Cost
    				, R.Adjuvant2CodeGL AS CodeGL_Adjuvant2
    				, R.Adjuvant3 AS Adjuvant3
    				, SUM(R.QteAdjuvant3 / 100 * db.BE_MET) AS Adjuvant3_Coule
    				, SUM(R.QteAdjuvant3 / 100 * db.BE_MET * R.Adjuvant3Prix) AS Adjuvant3_Cost
    				, R.Adjuvant3CodeGL AS CodeGL_Adjuvant3
    				, R.Pigment1 AS Pigment1
    				, SUM(R.QtePigment1 * db.BE_MET) AS Pigment1_Coule
    				, SUM(R.QtePigment1 * db.BE_MET * R.Pigment1Prix) AS Pigment1_Cost
    				, R.Pigment1CodeGL AS CodeGL_Pigment1
    				, R.Pigment2 AS Pigment2
    				, SUM(R.QtePigment2 * db.BE_MET) AS Pigment2_Coule
    				, SUM(R.QtePigment2 * db.BE_MET * R.Pigment2Prix) AS Pigment2_Cost
    				, R.Pigment2CodeGL AS CodeGL_Pigment2
    				, R.Pigment3 AS Pigment3
    				, SUM(R.QtePigment3 * db.BE_MET) AS Pigment3_Coule
    				, SUM(R.QtePigment3 * db.BE_MET * R.Pigment3Prix) AS Pigment3_Cost
    				, R.Pigment3CodeGL AS CodeGL_Pigment3
    
    FROM            SRV2.dbo.TABLE1 db  INNER JOIN SRV1.REPORTS.dbo.VIEW1 R ON db.BE_MIX_F collate Latin1_General_CI_AS = R.NumRecette collate Latin1_General_CI_AS
    
    GROUP BY db.BE_DATE, db.BE_CONT, db.BE_MIX_F, db.BE_MET, db.BE_MIX_D, db.BE_MET_D, R.Ciment1, R.Ciment1CodeGl, R.Ciment2, R.Ciment2CodeGl, R.Sable1, R.Sable1CodeGl, R.Sable2, R.Sable2CodeGl, R.Pierre1, R.Pierre1CodeGl,
    R.Pierre2, R.Pierre2CodeGl, R.Pierre3, R.Pierre3CodeGl, R.MicroAir, R.MicroAirCodeGl, R.Adjuvant1, R.Adjuvant1CodeGl, R.Adjuvant2, R.Adjuvant2CodeGl, R.Adjuvant3, R.Adjuvant3CodeGl, R.Pigment1,
    R.Pigment1CodeGl, R.Pigment2, R.Pigment2CodeGl, R.Pigment3, R.Pigment3CodeGl ) as A
    
    left join
    
    (Select			CAST(db.BE_DATE as smalldatetime) AS Date_Coule_D
    				, db.BE_CONT AS Contrat_Coule_D
    				, db.BE_MIX_F AS Recette_Coule_F
    				, db.BE_MIX_D AS Recette_Coule_D
    				, db.BE_MET_D AS Metre_Coule_D
    				, R.Ciment1 AS Ciment1_D
    				, SUM(R.QteCiment1 / 1000 * db.BE_MET) AS Ciment1_Coule_D
    				, SUM(R.QteCiment1 / 1000 * db.BE_MET * R.Ciment1Prix) AS Ciment1_Cost_D
    				, R.Ciment1CodeGL AS CodeGL_Ciment1_D
    				, R.Sable1 AS Sable1_D
    				, SUM(R.QteSable1 / 1000 * db.BE_MET) AS Sable1_Coule_D
    				, SUM(R.QteSable1 / 1000 * db.BE_MET * R.Sable1Prix) AS Sable1_Cost_D
    				, R.Sable1CodeGL AS CodeGL_Sable1_D
    				, R.Pierre1 AS Pierre1_D
    				, SUM(R.QtePierre1 / 1000 * db.BE_MET) AS Pierre1_Coule_D
    				, SUM(R.QtePierre1 / 1000 * db.BE_MET * R.Pierre1Prix) AS Pierre1_Cost_D
    				, R.Pierre1CodeGL AS CodeGL_Pierre1_D
    				, R.MicroAir AS MicroAir_D
    				, SUM(R.QteMicroAir / 100 * db.BE_MET) AS MicroAir_Coule_D
    				, SUM(R.QteMicroAir / 100 * db.BE_MET * R.MicroAirPrix) AS MicroAir_Cost_D
    				, R.MicroAirCodeGL AS CodeGL_MicroAir_D
    				, R.Adjuvant1 AS Adjuvant1_D
    				, SUM(R.QteAdjuvant1 / 100 * db.BE_MET) AS Adjuvant1_Coule_D
    				, SUM(R.QteAdjuvant1 / 100 * db.BE_MET * R.Adjuvant1Prix) AS Adjuvant1_Cost_D
    				, R.Adjuvant1CodeGL AS CodeGL_Adjuvant1_D				
    
    FROM             SRV1.REPORT.dbo.VIEW1 R INNER JOIN SRV2.dbo.TABLE1 db ON db.BE_MIX_D collate Latin1_General_CI_AS = R.numRecette collate Latin1_General_CI_AS
    
    group by db.BE_DATE, db.BE_CONT, db.BE_MIX_F, db.BE_MIX_D, db.BE_MET_D, R.Ciment1, R.Ciment1CodeGL, R.Sable1, R.Sable1CodeGl, R.Pierre1, R.Pierre1CodeGl, R.MicroAir, R.MicroAirCodeGl, R.Adjuvant1, R.Adjuvant1CodeGl) as B 
     
    on 
    	A.Date_Coule = B.Date_Coule_D
    AND A.Recette_Coule = B.Recette_Coule_F
    AND A.Contrat_Coule = B.Contrat_Coule_D
    I get all the information needed from my view and compare it with my table values, if it matches I calculate my totals for every ingredient of the recipe and then I check if I have to do the same with the second recipe.

    So far I get about 75 rows from this in 15 minutes....

    Here is an exemple of what it creates :
    Code:
    date         contract  recipe   metre  ingredient1     quantityused1   price1   codeGL1
    2015-06-12   102       x-xxx    10     ciment dust     2               26       99
    And that repeats for every ingredients in the recipe.

    I tried indexing my view but I can't since my information comes from another view generated from SharePoint

    If you have any clue for me to make it quicker I would really be appreciated

    Thank you.

  2. Best Answer
    Posted by dav1mo

    "are we getting your entire SQL here? I fail to see a need for your left outer join as you aren't selecting anything from this B table. Also, wouldn't expect this to be quick as there is no WHERE clause. Have you run an explain against this SQL to see how/which tables you are accessing?"


  3. #2
    Join Date
    Jan 2009
    Location
    United Kingdom
    Posts
    77
    Doing SUM functions is going to use take time. It depends what response time you're expecting. Have you looked at some computed columns or materialized views? http://www.sqlserver-dba.com/2013/10...onse-time.html

    Also, have you checked to see indexes and statistics are maintained properly?

  4. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    are we getting your entire SQL here? I fail to see a need for your left outer join as you aren't selecting anything from this B table. Also, wouldn't expect this to be quick as there is no WHERE clause. Have you run an explain against this SQL to see how/which tables you are accessing?
    Dave

  5. #4
    Join Date
    May 2015
    Posts
    2
    I got it figured. I made 2 other views to get my recipe from the front on one and my recipe from the back on the other. Then i used a full outer join to pair up the back and the front side. The thing was just poorly designed, I get an average of 10 seconds by request now.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •