Sql Query Is Taking More Than 4 Minutes

SQL query is taking more than 4 minutes

SELECT
    pvl.name_parametre_value_parametre_value_lang,
    pv.id_parametre_value,

    pipv.id_parametre_value as pipv_id_parametre_value
FROM
    ps_imp_combinaison_parametre_value_6 cpv
    LEFT JOIN ps_imp_combinaison_6 ic ON ic.id_combinaison = cpv.id_combinaison
    LEFT JOIN ps_imp_parametre_value pv ON pv.id_parametre_value = cpv.id_parametre_value
    LEFT JOIN ps_imp_parametre_value_lang pvl ON pvl.id_parametre_value = pv.id_parametre_value
    LEFT JOIN ps_imp_parametre p ON p.id_parametre = pv.id_parametre
    LEFT JOIN ps_imp_product_impression_parametre_value pipv ON pipv.id_parametre_value = pv.id_parametre_value and pipv.id_product_impression = 63
WHERE
    p.id_nom_domaine = 6
    AND
    pvl.id_lang_domaine = 18
    AND
    ic.id_product_impression = 63
GROUP BY
    ic.id_combinaison,
    cpv.id_parametre_value
ORDER BY
    ic.id_combinaison,
    p.id_parametre 
LIMIT
    0, 50

SQL query taking more than 3 minutes to get only 100 records

Try this version of your query:

SELECT  DocumentID 
INTO    #temp
FROM    (
        SELECT  ROW_NUMBER() OVER (Order By DocumentID desc) peta_rn
                , d2.DocumentID
        FROM    Documents d2 
        ) peta_paged
WHERE   peta_rn > 92000 AND peta_rn <= 92100

Select  d.DocumentID, d.IsReEfiled, d.IGroupID, d.ITypeID, d.RecordingDateTime, 
        d.IDate, d.InstrumentID, d.DocumentStatusID,ig.Abbreviation as IGroupAbbreviation, u.Username, j.JDAbbreviation, inf.DocumentName,
        it.Abbreviation as ITypeAbbreviation, d.DocumentDate, ds.Abbreviation as DocumentStatusAbbreviation 
            From Documents d 
                Inner Join IGroupes ig On d.IGroupID = ig.IGroupID 
                Left Join ITypes it On d.ITypeID = it.ITypeID 
                Left Join Users u On u.UserID = d.UserID 
                Left Join DocumentStatuses ds On d.DocumentStatusID = ds.DocumentStatusID 
                Left Join InstrumentFiles inf On d.DocumentID = inf.DocumentID                         
                Inner Join Jurisdictions j on j.JurisdictionID = d.JurisdictionID 
                INNER JOIN #temp ON d.DocumentID = #temp.DocumentID

Simple SQL query is taking 20 minutes to run?

You should be able to use explicit JOIN notation instead of the IN clause:

SELECT a.badge, COUNT(a.usid)*100 / (SELECT COUNT(DISTINCT usid) from Table1)
  FROM Table1 AS a
  JOIN (SELECT DISTINCT usid FROM Table2 WHERE msid = 1) AS b ON a.usid = b.usid
 GROUP BY a.badge

However, I'm not confident that will fix the performance problem. A half-way decent optimizer will realize that the sub-select in the select-list is constant, but you should verify that the optimizer is half-way decent (or better) by looking at the query plan.

I'm not convinced that the COUNT(a.usid) does anything different from COUNT(*) in this context. It would produce a different answer only if a.usid could contain nulls. See also COUNT(*) vs COUNT(1) vs COUNT(pk) — which is better?

SQL Query takes about 10 - 20 minutes

Have you tried using a bulk query? I had this same problem earlier in the week with C#; I had to insert about 25000 records and it took around 30 minutes. Changing to a bulk insert cut it down to about 5 seconds.

---

Related Topics