SQL Server Performance

Query result problem

Discussion in 'General DBA Questions' started by majere, Jan 31, 2006.

  1. majere New Member

    Hi,

    I have a little problem with a query, sometimes the result is 0 and i try to find why, what is the best way to find my problem

    Sorry i'm noob with SQL [V]

    Thanks

    Jeff
  2. Madhivanan Moderator

    Post some sample data, expected result and the query you used

    Madhivanan

    Failing to plan is Planning to fail
  3. majere New Member

    the result have 413 row like this, but sometimes nothing, sorry for this but i'm not able to give you in only one line

    223 TRICORBRAUN:NJ,PARAMUS 0099-009 MICRON/TRICORBRAUN FAB STE-CLAIRE USD) MICRON/TRICORBRAUN FOB STE-CLAIRE (USD)PE/HD HD/PE ALCOOL OVAL-OZ ALCOHOL OVAL-OZ 4c 16 OZ ALCOOL OVALE 28-400 PE/HD 16 OZ ALCOHOL OVAL 28-400 HD/PE 0115-005-31-001-001-001-1 31.00 SCLR58A SCLR58A NATUREL NATURAL VR 150 BU 150 VRAC BULK MICRON 2006-01-01 00:00:00.000 201.66 189.61 181.95 174.67 0.00 100.000 250.000 500.000 1,000.000 0.000 USD USD
  4. Madhivanan Moderator

    What is the query you used?

    Madhivanan

    Failing to plan is Planning to fail
  5. majere New Member

    Hi, my query is very long, can i send you via email? with a ressult?

    Thanks
  6. Madhivanan Moderator

    Post it here itself

    Madhivanan

    Failing to plan is Planning to fail
  7. majere New Member

    Looks like that,


    SELECT DISTINCT Produit_Prix.codeClient, AccesPrixListe.client, AccesPrixListe.CodeTermeLivraison, Produit_Prix.termeLivraisonFR, Produit_Prix.termeLivraisonEN, Produit_Prix.technologieFR, Produit_Prix.technologieEN, Produit_Prix.familleFR, Produit_Prix.familleEN, Produit_Prix.cavite, Produit_Prix.descriptionFR, Produit_Prix.descriptionEN, Produit_Prix.code, Produit_Prix.poids, Produit_Prix.resineFR, Produit_Prix.resineEN, Produit_Prix.couleurFR, Produit_Prix.couleurEN, Produit_Prix.emballageCourtFR, Produit_Prix.emballageCourtEN, Produit_Prix.emballageLongFR, Produit_Prix.emballageLongEN, Produit_Prix.proprietaire, Produit_Prix.dateEffective, Produit_Prix.prixA, Produit_Prix.prixB, Produit_Prix.prixC, Produit_Prix.prixD, Produit_Prix.prixE, Produit_Prix.qteA, Produit_Prix.qteB, Produit_Prix.qteC, Produit_Prix.qteD, Produit_Prix.qteE, Produit_Prix.deviseFR, Produit_Prix.deviseEN

    FROM Produit_Prix INNER JOIN AccesPrixListe ON Produit_Prix.codeTermeLivraison = AccesPrixListe.codeTermeLivraison

    WHERE ((Produit_Prix.dateEffective<=GETDATE())

    AND (Produit_Prix.client='MICRON')

    AND (AccesPrixListe.client='TRICORBRAUN:NJ,PARAMUS')

    AND (AccesPrixListe.CodeTermeLivraison IN ('0099-009','0146-001'))

    AND (Produit_Prix.proprietaire IN ('MICRON','TRICOR','AMPAK','AXCAN','CBC','LAMBERT','DERMTEK','DIETER','EMPIRE','GARNEAU','MARCELLE','PRETIUM','MWA','RICHARDS','NOVARTIS'))

    AND (Produit_Prix.familleEN IN ('ALCOHOL OVAL-OZ','BELL-ML','BOSTON ROUND REMOVER-ML','BOSTON ROUND SQUAT-OZ','BOSTON ROUND TALL-ML','BOSTON ROUND WESTON-OZ','BOSTON ROUND-ML','BOSTON ROUND-OZ','CARAFE','CLASSIC OBLONG-ML','CLASSIC OBLONG-OZ','CYLINDER-ML','CYLINDER-OZ','JAR-ML','JAR-OZ','METRIC OVAL-ML','METRIC ROUND-ML','OBLONG POWDER','OVAL-ML','OVAL-OZ','PHARMA OBLONG-ML','PHARMA ROUND-CC','PROTECTO-ML','ROUND OVAL-ML','ROUND RECESSED PANEL-OZ','SERUM-ML','SYRUP JUG-ML','TAPERED OVAL-ML','TAPERED OVAL-OZ','WESTON BULLET-ML','WESTON BULLET-OZ','TRICOR','AMPAK','AXCAN','CBC','LAMBERT','DERMTEK','DIETER','EMPIRE','GARNEAU','MARCELLE','PRETIUM','MWA','RICHARDS','NOVARTIS'))

    AND (Produit_Prix.couleurEN IN ('WHITE WH 2%TIO2 MB20902 4%','CLEAR','BLUE WH 2.5%TIO2 MB209351 5%','NATURAL','PRE-COLORED (AMBER)','WHITE 1735 4%','WHITE WH 2.5%TIO2 MB20902 5%','WHITE WH15136 4%','PURPLE TRANS.04-EHD-1209 4%','WHITE 01-PPP-260 4%','TRAN.PEACH (PINK)09EHD508 4%','WHITE 2%TIO2 01EHD225 4%','WHITE 2%TIO2 MMB909-C 4%','WHITE 10031950 4%')))

    ORDER BY AccesPrixListe.client, Produit_Prix.technologieen, Produit_Prix.familleen, Produit_Prix.cavite, Produit_Prix.descriptionen, Produit_Prix.dateEffective DESC
  8. majere New Member

    Thats append when it's 0

    codeClient client CodeTermeLivraison termeLivraisonFR termeLivraisonEN technologieFR technologieEN familleFR familleEN cavite descriptionFR descriptionEN code poids resineFR resineEN couleurFR couleurEN emballageCourtFR emballageCourtEN emballageLongFR emballageLongEN proprietaire dateEffective prixA prixB prixC prixD prixE qteA qteB qteC qteD qteE deviseFR deviseEN
    ---------- -------------------------------------------------- ------------------ ----------------------------------------------------------------- ----------------------------------------------------------------- ------------- ------------- -------------------------------------------------- -------------------------------------------------- ---------- -------------------------------------------------- -------------------------------------------------- ------------------------------ ---------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ---------------- ---------------- ------------------------------ ------------------------------ -------------------------------------------------- ------------------------------------------------------ -------------------- -------------------- -------------------- -------------------- -------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ---------- ----------

    (0 row(s) affected)
  9. Adriaan New Member

    I wonder if this bit -

    WHERE ((Produit_Prix.dateEffective<=GETDATE())

    - is causing the problem.

    Either ...

    ... well, you need to understand that GETDATE doesn't give you today's date, but it gives you today's date plus the current time. The same for the DateEffective column: if it is a column of the DATETIME data type, then it also contains a specific time (could be 00:00:00.000, but that is still midnight).

    ... or your DateEffective is the start of the effective period, rather than the end, so you would be better off using

    WHERE ((Produit_Prix.dateEffective>=GETDATE())

    - and that's where the time part of GETDATE() will really bite you in the back.
  10. majere New Member

    Thanks,

    But when i use >=GETDATE())i have no result.
  11. Adriaan New Member

    Decide if you need <= or >=, then try eliminating the time part - well, at least if you're certain that the time of day is of no consequence here:

    WHERE CONVERT(VARCHAR(10),Produit_Prix.dateEffective, 120)
    <= CONVERT(VARCHAR(10),GETDATE(), 120)

    or

    WHERE CONVERT(VARCHAR(10),Produit_Prix.dateEffective, 120)
    >= CONVERT(VARCHAR(10),GETDATE(), 120)
  12. majere New Member

    Thanks, i will try this.
  13. majere New Member

    I got the problem again, it's possible that the problem it's not only the date? Like a problem with the DB or index, server etc....

    I got the same result 100 times and after i got my 0 result for 1 or 10 times and again the good result.

    Thanks for you help!
  14. Adriaan New Member

    Well, we've only looked at the date criteria, and there's such a lot of other criteria going on as well - it's not very difficult to imagine that the criteria just filter most of the rows, and sometimes it filters them all out.

    Try dropping one of the criteria at a time, see which one is the biggest bump.
  15. majere New Member

    Thanks, i look for that.
  16. Madhivanan Moderator

    WHERE ((Produit_Prix.dateEffective<=GETDATE())

    should also be

    WHERE
    DateAdd(day,Datediff(day,0,Produit_Prix.dateEffective),0)
    <=DateAdd(day,DateDiff(day,0,GETDATE()),0)

    or

    WHERE
    Datediff(day,Produit_Prix.dateEffective,GETDATE())>=1

    Madhivanan

    Failing to plan is Planning to fail
  17. majere New Member

    Thanks again!
  18. Madhivanan Moderator

    Did you solve the problem now?

    Madhivanan

    Failing to plan is Planning to fail
  19. majere New Member

    Hi sorry i was not in my office, yes and no.... i think i have found a problem with the server, i run script 3 days to get the data and no problem and today i got the problem again, i try the DB with a other server with less data to see if i get my problem again.

    Thanks again.

Share This Page