November 30, 2007 at 1:56 pm
Antares
Your query did get the correct values, however it took 55 seconds to run the query. I think that the previous solution of using the exists rather than the joins is more efficient for this problem.
November 30, 2007 at 2:25 pm
The fun part is there are dozens and dozens of ways this query could be built here is another
SELECT DISTINCT
Accounts.UID
, Accounts.Fname + ' ' + Accounts.MI AS Fname
, Accounts.Lname
, Accounts.Addr
, Accounts.Addr2
, Accounts.City
, Accounts.State
, Accounts.Zip
, Accounts.Phone
, Accounts.DayPhone
, Accounts.Email
, Accounts.Company
, Accounts.Fax
, Accounts.Account_ID AS TXCNum
, Accounts.Alt_ID AS TONum
FROM
dbo.Accounts Accounts
INNER JOIN
dbo.Invoice Invoice
INNER JOIN
dbo.Vendors Vendors
ON
Invoice.Vendor_ID = Vendors.Vendor_ID
AND Invoice.Vendor_ID = '{48BC97F6-A2AC-446D-9E0A-C7D04354B2A5}'
ON
Invoice.UID = Accounts.UID
WHERE
(
LEN(Accounts.Lname) > 1
OR LEN(Accounts.Fname) > 1
)
AND Accounts.Type <> 'P'
AND (
EXISTS (
SELECT
1
FROM
dbo.Tickets Tickets
INNER JOIN
dbo.Seats Seats
ON
Tickets.Seat_ID = SeatsT.Seat_ID
WHERE
Invoice.Invoice_Num = Tickets.Invoice_Num
AND Invoice.AUTHCODE IS NOT NULL
AND Tickets.Status = 'S'
AND Tickets.Venue_GUID = '{1A10E4AA-348B-4A89-9562-4CFD83ADFC1B}'
)
OR EXISTS (
SELECT
1
FROM
dbo.InvoiceItems InvoiceItems
INNER JOIN
dbo.Seats Seats
ON
InvoiceItems.SeatPackageClub_ID = Seats.Seat_ID
AND Seats.Venue_GUID = '{1A10E4AA-348B-4A89-9562-4CFD83ADFC1B}'
WHERE
Invoice.I_GUID = InvoiceItems.I_GUID
AND InvoiceItems.Type = 'TP'
AND (
Invoice.AUTHCODE IS NOT NULL
OR Invoice.Type = 'Receivable'
)
)
)
ORDER BY
Accounts.Lname
, Accounts.Fname
November 30, 2007 at 2:28 pm
3 seconds with correct results! 😀
November 30, 2007 at 2:40 pm
Yeah, at least I shaved time. But like I said there are all kinds of tweaks you can do to a query, some will have major impact, others may not and the situation will vary greatly. I could try futher with group by to replace the distinct (which might improve a bit based on how group by and distinct actually behave). Then once you get into group by you can explore moving some items to a HAVING clause. In Oracle I had a query running the magnitude of tens of minutes (may have been over an hour but forget now). I rewrote and rewrote it to no avail then decided to work in a group by that still met the goal, this didn't improve much on perfomance, but then I moved some where conditions to the HAVING cluse and boom sub-30 seconds everytime.
December 1, 2007 at 8:21 am
That is insane... I have much to learn and it looks like I'm at the right place! 😎
January 23, 2008 at 7:09 pm
Hey guys I am back. I have another query that I am optimizing that is very similar to the one in this post a couple of months back.
Here is the first query I am trying to optimize:
DELETE FROM @tblCustomers WHERE AccountID IN
(SELECT DISTINCT Accounts.Account_ID
FROM Accounts INNER JOIN
Invoice ON Accounts.UID = Invoice.UID INNER JOIN
InvoiceItems ON Invoice.I_GUID = InvoiceItems.I_GUID
INNER JOIN TicketPackages ON
IvoiceItems.SeatPackageClub_ID = TicketPackages.TP_ID
WHERE (Invoice.AUTHCODE IS NOT NULL) AND (TicketPackages.Category_Num = @vintCatPrevSeason))
Here is my first effort to change the joins with "where exists":
DELETE FROM @tblCustomers WHERE EXISTS
(Select *
FROM Invoice
WHERE Invoice.UID = UID
AND (Invoice.AUTHCODE IS NOT NULL)
AND EXISTS
(SELECT *
FROM InvoiceItems
WHERE InvoiceItems.I_GUID = Invoice.I_GUID
AND EXISTS
(SELECT *
FROM TicketPackages
WHERE TicketPackages.TP_ID = InvoiceItems.SeatPackageClub_ID
AND (TicketPackages.Category_Num = @vintCatPrevSeason))))
I suspect the issue is occurring where I "WHERE Invoice.UID = UID". Any thoughts?
January 24, 2008 at 8:02 am
I assume the UID is part of the Table variable. This might work
DELETE
xT
FROM
@tblCustomers xT
INNER JOIN
Invoice I
INNER JOIN
InvoiceItems II
INNER JOIN
TicketPackages TP
ON
TP.TP_ID = II.SeatPackageClub_ID
AND (TP.Category_Num = @vintCatPrevSeason)
ON
II.I_GUID = I.I_GUID
ON
I.UID = xT.UID AND
I.AUTHCODE IS NOT NULL
January 24, 2008 at 8:52 am
worked like magic! The query now only takes 6 seconds compared to 1:30.
Thanks a ton!!
Cheers,
Justin
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply