ODBiC SELECT statement never completes
by Dave, November 12, 2003 14:03
I'm using ODBiC from a command prompt against a local access database. The EXACT same query takes 2 - 3 seconds in MS access to complete but in ODBiC it never finishes and hogs 99% of the CPU. I've tried removing a couple of the "fluff" tables but still the same thing. Here's the full statement:
<!--SQL SELECT Payments.SiteID, Payments.ServDate, Payments.LineNum, Payments.TransNbr, Payments.CheckNum, Payments.Tendered, Payments.PayType, Payments.Allocation, Payments.VoidPayType, Payments.bPaidCharges, Transactions.ID, Transactions.Reimbursable, Transactions.Cashier, PayTypes.Description, Users.Name
FROM ((Transactions INNER JOIN Payments ON (Transactions.SiteID = Payments.SiteID) AND (Transactions.ServDate = Payments.ServDate) AND (Transactions.LineNum = Payments.LineNum) AND (Transactions.Transnbr = Payments.TransNbr)) INNER JOIN PayTypes ON Payments.PayType = PayTypes.TypeID) INNER JOIN Users ON Transactions.Cashier = Users.UserID
WHERE (((Payments.SiteID) In (SELECT [SiteID] FROM [Payments] As Tmp GROUP BY [SiteID],[ServDate],[LineNum],[TransNbr] HAVING Count(*)>1 And [ServDate] = [Payments].[ServDate] And [LineNum] = [Payments].[LineNum] And [TransNbr] = [Payments].[TransNbr] And Transactions.Reimbursable = True)))
ORDER BY Payments.SiteID, Payments.ServDate, Payments.LineNum, Payments.TransNbr-->
output to html table goes here...
Any help would be appricated...
Name E-mail optional TopicMessage:
HTTP Link: Link text: