Query Help

[Tom]

Hi:

I’m having trouble with the following query…any suggestions?

* tblProposal is a linked table in SQL Server
* qryProposalFilter is a local query that limits the list of
ProposalID to the desired subset.

What I want to do is return the value of the field ContingencyGeneral
if the value is the same for all the proposals in the desired subset.
If there are different values, I want the word “Varies” to be
returned.

I’m opening a recordset in code and pulling out the returned value to
plug into a Word report.

Thru much trial and error, I came up with the following working
solution.

SELECT Count(*) AS N,
IIf([n]=1,(SELECT TOP 1 dbo_tblProposal.ContingencyGeneral FROM
dbo_tblProposal
INNER JOIN qryProposalFilter ON dbo_tblProposal.ProposalID =
qryProposalFilter.ProposalID,'Varies') AS MiscRate
FROM [SELECT DISTINCT dbo_tblProposal.ContingencyGeneral FROM
dbo_tblProposal
INNER JOIN qryProposalFilter ON dbo_tblProposal.ProposalID =
qryProposalFilter.ProposalID]. AS T;


However, it is slow when used over a WAN. Performance seems
acceptable when over a LAN.

Any idea’s on how I can speed up the overall process? Either changes
to the SQL or to the overall approach in code would be much
appreciated.

(oh - and I have to repeat this for about a dozen different fields -
so the performance hit is magnified)

Thanks

 

[Mark Andrews]

If possible don't use a linked table. Use a pass thru query that either
runs a stored procedure in sql server or runs a SQL server based SQL
statement. At runtime you construct the querydef of the pass thru query and
then run it. You can have a million records in your sql server table and if
it gets filtered down to 25 it will still be fast.

That way the sql runs on the sql server end and you only get the returned
records coming over the wire.

I'm not sure how many records are typical in qryProposalFilter, if it's just
a few you can loop thru it to construct the sql using some kind of IN
statement or ORs.

Try an example by hard coding a pass thru query to see the speed difference.
Save the credentials in the pass-thru query.

My two cents,
Mark



Hi:

I’m having trouble with the following query…any suggestions?

* tblProposal is a linked table in SQL Server
* qryProposalFilter is a local query that limits the list of
ProposalID to the desired subset.

What I want to do is return the value of the field ContingencyGeneral
if the value is the same for all the proposals in the desired subset.
If there are different values, I want the word “Varies” to be
returned.

I’m opening a recordset in code and pulling out the returned value to
plug into a Word report.

Thru much trial and error, I came up with the following working
solution.

SELECT Count(*) AS N,
IIf([n]=1,(SELECT TOP 1 dbo_tblProposal.ContingencyGeneral FROM
dbo_tblProposal
INNER JOIN qryProposalFilter ON dbo_tblProposal.ProposalID =
qryProposalFilter.ProposalID,'Varies') AS MiscRate
FROM [SELECT DISTINCT dbo_tblProposal.ContingencyGeneral FROM
dbo_tblProposal
INNER JOIN qryProposalFilter ON dbo_tblProposal.ProposalID =
qryProposalFilter.ProposalID]. AS T;


However, it is slow when used over a WAN. Performance seems
acceptable when over a LAN.

Any idea’s on how I can speed up the overall process? Either changes
to the SQL or to the overall approach in code would be much
appreciated.

(oh - and I have to repeat this for about a dozen different fields -
so the performance hit is magnified)

Thanks

 

[Tom]

I'm not sure how many records are typical in qryProposalFilter, if it's just
a few you can loop thru it to construct the sql using some kind of IN
statement or ORs.

Typically only a few, but the user picks from a list of all available
proposals, so it could be more.

I'll give it a shot.

Thanks

 

[Mark Andrews]

Feel free to shoot me an email if you get stuck.

See site for details,
Mark
RPT Software
http://www.rptsoftware.com

 

[Tom]

Mark:

Thanks again for your help.

After taking a break from the problem, I finally came up with the
following solution... I created a local query with qryProposalFilter
and tblProposal that had just the dozen or so fields I was interested
in. I then used the following function to return the values I wanted:

Public Function fncCombinedRate(RateFieldName As String) As String
If ECount(RateFieldName, "qryCombinedRate", , True) > 1 Then
sResult = "Varies"
Else
sResult = ELookup(RateFieldName, "qryCombinedRate")
End If
fncCombinedRate = sResult

Where ECount and ELookup are Allen Browne's replacement functions for
DCount and DLookup.

Performance is very satisfactory, and its a much more straightforward
solution that I was previously using.

Thanks again for taking the time to look at my question

Tom