Querydefs performance (1 Viewer)

[Kayleigh]

Hi
I have a form for searching many records using a search string in a textbox. The results appear in a listbox.
We used to use the textbox string as a parameter for the listbox rowsource but now we have changed to SQL server and using a pass-through query for speedier results. So we can't use a local parameter in the query. Instead we have a local query based on the PT query. However it is still quite slow.
I have thought of an alternative but would be interested in any other ideas here.
My idea - only use PT but each change on the form would re-define querydef of rowsource to include the new search string but this requires opening recordsets and requerying the form - both are slow approaches.
Is there any other approach here?

 

[CJ_London]

have you considered using a stored procedure - you can pass your local variable as a parameter. Might provide better performance

other thing which are are probably aware of is use of the initial * (or % in sql server). This negates the use of an index. Searches on names for example - how many users looking for 'smith' start with 'i'? I rarely include the initial */% and train users to include it with their search string if required - and set their expectations that it will be slower.

Other factors on performance may be the network performance, plus although sql server has potentially faster processors it is also generally doing a lot more with other applications so if IT have not prioritised your app to some extent it may be it is at the bottom of the resource chain.

If you are moving to sql server purely with the expectation of improved performance, you are likely to be disappointed. One of my clients (a large multinational) moved to sql server with that expectation and then moved back because performance was dreadful - mainly for the two reasons above.

 

[Kayleigh]

Thank you @CJ_London
Can you advise re stored procedures - i am assuming these are defined in SQL Server. So how are they accessed on the MS Access FE?
Can you point me to more info on this please

 

[CJ_London]

in a PT

execute storedprocedurename (param1, param2 etc)

much the same with ADO

 

[Kayleigh]

Thank you.
I am having trouble with writing the stored procedure to do the above. Can anyone help me?

CREATE PROCEDURE test1
(@SRCH VARCHAR(50) ='')
AS
BEGIN


SELECT qryTransSearchAll.fldOrderID AS ID, qryTransSearchAll.fldDblGlzSysRef AS Ref, qryTransSearchAll.fldOQBRef AS [QB Invoice], qryTransSearchAll.fldOQBDptRef AS [QB Deposit], qryTransSearchAll.cfClient AS Name, qryTransSearchAll.fldCBusiness AS Business, qryTransSearchAll.cfAddress AS Site, qryTransSearchAll.fldOJobDescription AS Job, qryTransSearchAll.cftotal AS Total, qryTransSearchAll.cfpayment AS Payment, qryTransSearchAll.fldPayType AS Type, qryTransSearchAll.fldOrStatus AS Status
FROM (
SELECT qryOrderExtended.fldOrderID, qryOrderExtended.fldDblGlzSysRef, qryOrderExtended.fldOQBRef, qryOrderExtended.fldOQBDptRef, dbo.tblClients.fldCBusiness,
qryOrderExtended.fldOJobDescription, Str(CAST([cfGrandTotal] AS MONEY)) AS cfTotal, Str(CAST([fldTCredit] AS MONEY)) AS cfPayment,
qryTransactionsExtended.fldPayType, dbo.lkptblOrderStatus.fldOrStatus, lkpqryClient2.cfClient, lkpqryAddress1.cfAddress
FROM (
SELECT dbo.tblAddress.fldAddressID, concat([fldAAddress1], '  ', [fldastreet], ', ', [fldacity], ', ', [fldapostcode]) AS cfAddress
FROM dbo.tblClients INNER JOIN dbo.tblAddress ON dbo.tblClients.fldClientID = dbo.tblAddress.fldAClientID
) lkpqryAddress1 INNER JOIN (((((
SELECT *, ISNULL([fldototalquote], 0)+[cfvat]+[cfAgent] AS cfGrandTotal
FROM (SELECT *, CAST((ISNULL([fldototalquote], 0)+ISNULL([cfvat], 0))*(ISNULL(([fldoagentcommision]/100),0))*((ISNULL([fldovatrate], 0)/100)+1) AS MONEY) AS cfAgent
FROM (SELECT dbo.tblOrders.*, dbo.tblAddress.fldAClientID,  CAST(ISNULL(([fldototalquote]*([fldOvatpercentage]/100))*([fldovatrate]/100), 0) AS MONEY) AS cfVat
FROM dbo.tblAddress RIGHT JOIN dbo.tblOrders ON dbo.tblAddress.fldAddressID = dbo.tblOrders.fldOAddressID
) qryAmountDue1) qryAmountDue2
) qryOrderExtended LEFT JOIN (
SELECT dbo.tblTransaction.*, dbo.lkptblPayType.fldPayType
FROM dbo.lkptblPayType INNER JOIN dbo.tblTransaction ON dbo.lkptblPayType.fldPayTypeID = dbo.tblTransaction.fldTPayTypeID
) qryTransactionsExtended ON qryOrderExtended.fldOrderID = qryTransactionsExtended.fldTOrderID) INNER JOIN
dbo.tblClients ON qryOrderExtended.fldAClientID = dbo.tblClients.fldClientID) INNER JOIN dbo.lkptblOrderStatus ON qryOrderExtended.fldOStatusID = dbo.lkptblOrderStatus.fldOrderStatusID)
INNER JOIN(
SELECT  dbo.tblClients.fldClientID, concat([fldCFirstname], ' ', [fldclastname]) AS cfClient
FROM  dbo.tblClients
) lkpqryClient2 ON dbo.tblClients.fldClientID = lkpqryClient2.fldClientID) ON lkpqryAddress1.fldAddressID = qryOrderExtended.fldOAddressID
) qryTransSearchAll
WHERE (((qryTransSearchAll.fldOrderID) Like '%@SRCH%')) OR (((qryTransSearchAll.fldDblGlzSysRef) Like '%@SRCH%')) OR (((qryTransSearchAll.cfClient) Like '%@SRCH%'))
OR (((qryTransSearchAll.fldCBusiness) Like '%@SRCH%')) OR (((qryTransSearchAll.cfAddress) Like '%@SRCH%')) OR (((qryTransSearchAll.fldOJobDescription) Like '%@SRCH%'))
OR (((qryTransSearchAll.cftotal) Like '%@SRCH%')) OR (((qryTransSearchAll.cfpayment) Like '%@SRCH%')) OR (((qryTransSearchAll.fldPayType) Like '%@SRCH%'))
OR (((qryTransSearchAll.fldOQBRef) Like '%@SRCH%')) OR (((qryTransSearchAll.fldOQBDptRef) Like '%@SRCH%'))
ORDER BY qryTransSearchAll.cfClient, qryTransSearchAll.fldCBusiness

END

Error when executing:
Msg 217, Level 16, State 1, Procedure test, Line 39 [Batch Start Line 0]
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

 

[CJ_London]

Suggest go for a simpler query to make sure it executes then expand it gradually to determine the issue

A while since I've used TSQL but is this correct?

Like '%@SRCH%'

shouldn't it be

Like '%' + @SRCH + '%'

As previously advised, the use of the initial % means that indexing cannot be used so records will be found by a sequential scan - which can take a long time

 

[Pat Hartman]

I'm guessing that you are using LIKE when you should be using =. You would only use LIKE if you were actually searching for partial STRINGS. If you have an OrderID, it is probably numeric and you almost certainly have the entire value so why would you ever use LIKE? If you are trying to make the parameters optional and that is the ONLY reason for using LIKE, here is a better way:

Where (flda = forms!yourform!flda OR forms!yourform!flda Is Null)
AND (fldb = forms!yourform!fldb OR forms!yourform!fldb Is Null)
AND (fldc = forms!yourform!fldc OR forms!yourform!fldc Is Null)
etc.

Go back and try your original querydef replacing the LIKEs with something like my sample to see if that changes anything.


And you can use arguments in a passthrough query, you just have to build the query in VBA. It should be easier than trying to build a stored procedure if you don't know T-SQL