How to optimize this Query to be faster and more efficient? (1 Viewer)

[spenzer]

An Access 2007 SQL recordsource for a query named qryTransactionList below is completely working but it is very slow. I am hopeful that there something that can be done here to make it more efficient and a little bit faster.. Any suggestions are welcome. Thanks in advance.

SELECT IIf([SelfLiquidatingID] Is Null,IIf([ReceivableID] Is Null,[UnpaidBalanceID],[ReceivableID]),IIf([ReceivableID] Is Null,[SelfLiquidatingID],[ReceivableID])) AS tDID

FROM ((tblTransactions LEFT JOIN tblSelfLiquidatings ON tblTransactions.TransactionID = tblSelfLiquidatings.TransactionID) LEFT JOIN tblUnpaidBalances ON tblTransactions.TransactionID = tblUnpaidBalances.TransactionID) LEFT JOIN tblReceivables ON tblTransactions.TransactionID = tblReceivables.TransactionID

WHERE (((IIf([tblSelfLiquidatings].[Active] Is Null,IIf([tblReceivables].[Active] Is Null,[tblUnpaidBalances].[Active],[tblReceivables].[Active]),IIf([tblReceivables].[Active] Is Null,[tblSelfLiquidatings].[Active],[tblReceivables].[Active])))=-1));

 

[MarkK]

- One thing is to make sure that fields that you search, sort, filter or link are indexed.
- In some cases subqueries can be much faster, but they're harder to write becuase you can't use the designer.
- And welcome to the forum!!!
Cheers
Mark

 

[spenzer]

Thanks for the warm welcome sir.

I've tried subqueries a while ago and it turned out to be slower in performance also i indexed all the fields i use to search often.

I was thinking my last resort is to denormalize to lessen table linking somehow.. But i am still on a hunt for may be a better alternative code than what I have currently implemented.

 

[boblarson]

Given you have quite an IIF statement there, and that IIF statements evaluate ALL of them, even if the first one is true, it will still evaluate the next and the next, I wouldn't normally suggest a function to be faster but it might (doesn't hurt to try).

 

[spenzer]

Given you have quite an IIF statement there, and that IIF statements evaluate ALL of them, even if the first one is true, it will still evaluate the next and the next, I wouldn't normally suggest a function to be faster but it might (doesn't hurt to try).

Thanks for your suggestion sir. you're right sir it seemed to evaluate itself redundantly and so i'm here because I am not convince myself if what i did is the best form of coding to implement nor the most efficient but as of the moment it works but its really slow..

I am in the process of trying and figuring out how to setup your suggestion. a function to be used as recordsource for this particular query.

 

[spikepl]

What datatype is .Active ? Is that a boolean? If so, is that a required thing that it can have a Null, instead of a default False?

Update: Aaaaargh ... left joins, of course.

 

[spenzer]

@spikepl

.Active is a boolean with only true or false as the choices no nulls is allowed. It may appear to have nulls because of the way the SQL statement has been constructed but it doesn't, its only purpose is to just show all records on the other three tables left joined on a single table named tblTransactions that are only active.

I needed to do left joins because if not it will cause error and the query won't open.

 

[spenzer]

it appears nothing is faster than this method already when using microsoft access. when i try to use function it actually just slowed some more.

in sql server 2008 when i try converting this statement through ssma. it suggested using select case statement and the query will then be converted as view and upon converting the query or view is now lightning fast and as a bonus the statement is super clear because select case is easier to look at.

but the problem is that in access, it seems it won't allow select case statement and be saved as a query.