Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820

    Unanswered: Anyone help simplify a query

    A users Given me a database with a query in it in i think that the're must be a better way of doing it

    here's the query

    SELECT tblMachines.MachineID, tblMachines.BarcoMCID, DateSerial(Year([C_STARTTIME]),Month([C_STARTTIME]),Day([C_STARTTIME])) AS PostingDate, HIS_T_HISSHIFTS.*
    FROM tblMachines INNER JOIN HIS_T_HISSHIFTS ON tblMachines.BarcoMCID = HIS_T_HISSHIFTS.C_MACHINE
    WHERE (((tblMachines.MachineID)="D01") AND ((tblMachines.BarcoMCID)="D01") AND ((DateSerial(Year([C_STARTTIME]),Month([C_STARTTIME]),Day([C_STARTTIME])))>=#6/14/2002#)) OR (((tblMachines.MachineID)="D02") AND ((tblMachines.BarcoMCID)="D01") AND ((DateSerial(Year([C_STARTTIME]),Month([C_STARTTIME]),Day([C_STARTTIME]))) Between #3/25/2002# And #6/12/2002#)) OR (((tblMachines.MachineID)="D02") AND ((tblMachines.BarcoMCID)="D02") AND ((DateSerial(Year([C_STARTTIME]),Month([C_STARTTIME]),Day([C_STARTTIME])))>=#6/14/2002#)) OR (((tblMachines.MachineID)="D02") AND ((tblMachines.BarcoMCID)="D1") AND ((DateSerial(Year([C_STARTTIME]),Month([C_STARTTIME]),Day([C_STARTTIME])))<=#3/23/2002#)) OR (((tblMachines.MachineID)="D03") AND ((tblMachines.BarcoMCID)="D02") AND ((DateSerial(Year([C_STARTTIME]),Month([C_STARTTIME]),Day([C_STARTTIME]))) Between #3/25/2002# And #6/12/2002#)) OR (((tblMachines.MachineID)="D03") AND ((tblMachines.BarcoMCID)="D03") AND ((DateSerial(Year([C_STARTTIME]),Month([C_STARTTIME]),Day([C_STARTTIME])))>=#6/14/2002#)) OR (((tblMachines.MachineID)="D03") AND ((tblMachines.BarcoMCID)="D2") AND ((DateSerial(Year([C_STARTTIME]),Month([C_STARTTIME]),Day([C_STARTTIME])))<=#3/23/2002#)) OR (((tblMachines.MachineID)="D04") AND ((tblMachines.BarcoMCID)="D03") AND ((DateSerial(Year([C_STARTTIME]),Month([C_STARTTIME]),Day([C_STARTTIME]))) Between #3/25/2002# And #6/12/2002#)) OR (((tblMachines.MachineID)="D04") AND ((tblMachines.BarcoMCID)="D04") AND ((DateSerial(Year([C_STARTTIME]),Month([C_STARTTIME]),Day([C_STARTTIME])))>=#6/14/2002#)) OR (((tblMachines.MachineID)="D04") AND ((tblMachines.BarcoMCID)="D3") AND ((DateSerial(Year([C_STARTTIME]),Month([C_STARTTIME]),Day([C_STARTTIME])))<=#3/23/2002#)) OR (((tblMachines.MachineID)="D05") AND ((tblMachines.BarcoMCID)="D04") AND ((DateSerial(Year([C_STARTTIME]),Month([C_STARTTIME]),Day([C_STARTTIME]))) Between #3/25/2002# And #6/12/2002#)) OR (((tblMachines.MachineID)="D05") AND ((tblMachines.BarcoMCID)="D05") AND ((DateSerial(Year([C_STARTTIME]),Month([C_STARTTIME]),Day([C_STARTTIME])))>=#6/14/2002#)) OR (((tblMachines.MachineID)="D05") AND ((tblMachines.BarcoMCID)="D4") AND ((DateSerial(Year([C_STARTTIME]),Month([C_STARTTIME]),Day([C_STARTTIME])))<=#3/23/2002#)) OR (((tblMachines.MachineID)="E03") AND ((tblMachines.BarcoMCID)="E03") AND ((DateSerial(Year([C_STARTTIME]),Month([C_STARTTIME]),Day([C_STARTTIME])))>=#6/14/2002#)) OR (((tblMachines.MachineID)="E03") AND ((tblMachines.BarcoMCID)="D10") AND ((DateSerial(Year([C_STARTTIME]),Month([C_STARTTIME]),Day([C_STARTTIME])))<=#6/12/2002#)) OR (((tblMachines.MachineID)="C04") AND ((tblMachines.BarcoMCID)="C04") AND ((DateSerial(Year([C_STARTTIME]),Month([C_STARTTIME]),Day([C_STARTTIME])))>=#6/14/2002#)) OR (((tblMachines.MachineID) Like "A*")) OR (((tblMachines.MachineID) Like "B*")) OR (((tblMachines.MachineID)="C02") AND ((tblMachines.BarcoMCID)="C02") AND ((DateSerial(Year([C_STARTTIME]),Month([C_STARTTIME]),Day([C_STARTTIME])))>=#6/14/2002#)) OR (((tblMachines.MachineID)="C03") AND ((tblMachines.BarcoMCID)="C03") AND ((DateSerial(Year([C_STARTTIME]),Month([C_STARTTIME]),Day([C_STARTTIME])))>=#6/14/2002#)) OR (((tblMachines.MachineID)="C05") AND ((tblMachines.BarcoMCID)="C05") AND ((DateSerial(Year([C_STARTTIME]),Month([C_STARTTIME]),Day([C_STARTTIME])))>=#6/14/2002#)) OR (((tblMachines.MachineID)="E02") AND ((tblMachines.BarcoMCID)="E02") AND ((DateSerial(Year([C_STARTTIME]),Month([C_STARTTIME]),Day([C_STARTTIME])))>=#6/14/2002#)) OR (((tblMachines.MachineID)="C01") AND ((tblMachines.BarcoMCID)="C01"))
    ORDER BY tblMachines.MachineID, DateSerial(Year([C_STARTTIME]),Month([C_STARTTIME]),Day([C_STARTTIME]));
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    must be a better way of doing what?

    does the query run? if it runs, does it produce the correct output? does it run well, or is it a lot slower than similar queries that join those particular tables? are you worried about the size of the source code, or the complexity of the parenthesized ANDs and ORs?

    like your doctor would say, "where does it hurt?"



    rudy

  3. #3
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    it works but is slow as hell and look at the damn thing, there has to be a neater and fast way of doing it
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, one thing might be to stop using DateSerial() on the year, month, and day parts of C_STARTTIME -- my guess is, the thing wanted here is to strip the time off

    it would be simple, i think, to use Format() on C_STARTTIME to turn it into an 8-character yyyymmdd string, and then enclose the hardcoded date strings with quotes instead of pound signs

    next, i didn't try to analyze your nesting, but you can see a bunch of dates repeating, in combination with some machine codes, i'd look at perhaps re-nesting those

    WHERE ( x AND y ) OR ( x AND z )

    is equivalent to

    WHERE x AND ( y OR z )

    finally, when you've figured out what conditions are actually driving the query, and which ones are necessary for selection at run time, i would take the driving stuff and save it as a query, then code the selection stuff in a query of the query

    rudy

  5. #5
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    i'll give it a try
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

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