Query takes 30 sec

[lburch]

Hi i have an access DB with 2million records
I have a query that looks up 6 conditions with all needing to be true.

Is 30sec an average time to return results.
I was hoping for 5sec. I paid someone to create, and am a bit disappointed it takes 30 sec.

I have tried all quick fix options


compact
turning off autofill etc

[jojowhite]

care to share/post the SQL string.
maybe someone here with good eye sight can make the query better
and reduce the waiting time.

[lburch]

would be happy too, just not sure how sorry

[Welshgasman]

Copy and paste it between code tags using the # icon.
Are all the fields being searched indexed?

[lburch]

ok will post

[lburch]

TRANSFORMSum(Qry_Race_OddsCateg_Count.[Number of Runners]) AS [SumOfNumber of Runners]SELECT Qry_Race_OddsCateg_Count.Race_ID, Sum(Qry_Race_OddsCateg_Count.[Number of Runners]) AS [Total Of Number of Runners]
FROM Qry_Race_OddsCateg_Count
GROUP BY Qry_Race_OddsCateg_Count.Race_ID
PIVOT Qry_Race_OddsCateg_Count.OddsCateg;

[lburch]

sorry doubled up

[Welshgasman]

Need to show Qry_Race_OddsCateg_Count as that is where the data is organized?

How long does that take to run?

[lburch]

SELECT Qry_Runners_FullList.Race_ID, Qry_Runners_FullList.OddsCateg, Count(Qry_Runners_FullList.Runner_ID) AS [Number of Runners]FROM Qry_Runners_FullList LEFT JOIN Qry_Race_TotalRunners_Count ON Qry_Runners_FullList.Race_ID = Qry_Race_TotalRunners_Count.Race_ID
WHERE (((Qry_Race_TotalRunners_Count.[Total Runners]) Between 
Eval("[forms]![frm_Main]![NavigationSubform].form![vMinRunners]") And 
Eval("[forms]![frm_Main]![NavigationSubform].form![vMaxRunners]")))

GROUPBY Qry_Runners_FullList.Race_ID, Qry_Runners_FullList.OddsCateg;

[jojowhite]

i haven't tested this:

TRANSFORM Count(Qry_Runners_FullList.Runner_ID) AS [Number of Runners] 
SELECT Qry_Runners_FullList.Race_ID 
FROM Qry_Runners_FullList LEFT JOIN Qry_Race_TotalRunners_Count ON Qry_Runners_FullList.Race_ID = Qry_Race_TotalRunners_Count.Race_ID
WHERE (((Qry_Race_TotalRunners_Count.[Total Runners]) Between 
Eval("[forms]![frm_Main]![NavigationSubform].form![vMinRunners]") And 
Eval("[forms]![frm_Main]![NavigationSubform].form![vMaxRunners]"))) 
GROUP BY Qry_Runners_FullList.Race_ID 
PIVOT Qry_Runners_FullList.OddsCateg;

[Welshgasman]

Not sure you need the Eval?

[CJ_London]

you don't - but you would then need to declare the form references as parameters since this is used as a Xtab query

think we also need to see the sql to the Qry_Race_TotalRunners_Count query since you should be applying where parameters earlier in the chain i.e.

Between
Eval("[forms]![frm_Main]![NavigationSubform].form![vMinRunners]") And
Eval("[forms]![frm_Main]![NavigationSubform].form![vMaxRunners]")

should be in the earlier query

[madpiet]

But did you try indexing the join and filter columns? The rest won't do anything. If you want it to be fast, you have to fix that parts that are making it slow.
Oh, and without the SQL Statement, it's impossible to tell what to fix.