SQL Query (Slow) Date format mask

[imfarhan]

Hi All,

I have slightly change my SQL query in where condition and its being very slow now. don't know why.

Before Changes (Running OK and fast)

Param1= mm/dd/yyyy (Friday = 06/25/2010)
Param2= mm/dd/yyyy (RunDay = 06/30/2010)

WHERE ((IP.Hospital In (""NHH"",""ERDS"")
AND (IP.CEEndDateTime Between [Param1] And Dateadd(""d"", 1, [Param2]))

After Changes (Running very slow but results are ok)

AND (IP.CEEndDateTime Between IIF(weekday(now())=6,
format(now()-7,'dd/mm/yyyy'),[Param1]) And Dateadd(""d"", 1,Param2]))

I have also changed the format mask above like mm/dd/yyy as I think Access keeps the date format mask as defaults like mm/dd/yyyy but still running very slow , before it was running in 2 minute and now its running in 25min don't konw why?

The above query runs ok every day but on Friday its bring nulls value because Param1(Friday) and Param2(Rundate) will be equal.
I appreciate your help.

Many thanks
Farhan

 

[imfarhan]

Checking its post or not

 

[dk]

Hi,

There are two reasons I can think of as to why your query is running more slowly.

1. If you have an index on the CEEndDateTime field, then the index will not be used in your second query (indexes do not work when the field is used within a function, as in your Format.

2. More likely is that the IIF is very inefficient when being used for large datasets. IIF will always evaluate both the results so your query is evaluating <i>format(now()-7,'dd/mm/yyyy')</i> for every single row in your query. It is likely that this is causing your slowdown.

I'm not sure why you're using the Format function. Access uses American style date formats in the SQL window so I would remove the Format function. I am assuming that the CEEndDateTime field is a date type and not text.

Can you provide a little more information about what this query is doing - we can't tell if it's an update query, insert, select or what. Also, how are you running this query - from a form, report or directly. Depending on the method and type of query you may be able to just evaluate your parameters within some VBA code and then run your SQL directly from within VBA.

DK

 

[imfarhan]

Hi DK
Thanks for the swift response

Sorry I should give a full picture in my question.

This is a simple SQL query kept in a table which created part of Automation through VBA.
When its created its also execute through VBA(Ms Access).
So you may say, simple its created the query on Ms-Query(Access) and run through Ms-Access.

The source table is linked table so I don't think I can Index on the field please find the full SQL as follows:

(1)

SELECT OPSP.ContractId, OPSP.Category, OPSP.NHSNo, OPSP.DistrictNo, OPSP.Forenames, OPSP.Surname, OPSP.DOB, "" AS Address, OPSP.PrimaryProc, OPSP.ClinicName, OPSP.Consultant, OPSP.ConsSpecialty, OPSP.ApptDateTime, OPSP.Outcome, OPSP.ClinicHosptial AS ClinicHospital
FROM nhhtlod_OPSP AS OPSP
WHERE (((OPSP.Category)="20") And ((OPSP.ApptDateTime) Between IIf(Weekday(Now())=6,Now()-7,#6/25/2010#) And DateAdd("d",1,#6/30/2010#)) And ((OPSP.ReferralSource)<>"BEN"));


​

I take off the format mask as you advise me as you can see above.
i have also created the Make table query to calcuate the result which I wanted to use in my WHERE BETWEEN condition and use the result of Mk table quey in the condition as you can see given below in (2) and (3).

(2) Make Table Query
SELECT
DateAdd("d",1,#6/30/2010#) AS [To], IIf(Weekday(Now())=6,Now()-7,#6/25/2010#) AS [from] INTO tbl_test;

(3) How I use Make Table query result in the Main query(1)

Between (select tbl_test.from from tbl_test)
And (select tbl_test.to from tbl_test))
AND ((OPSP.ReferralSource)<>"BEN"));

But still take too long dont know why?

Awaiting for your response.

Regards
Farhan

 

[dk]

Hi

I'm still not entirely sure how your query is running - you say that it's stored in a table in your database - so do you mean that you are actually storing the SQL text in a field within a table???

Does this query run faster than before?

SELECT OPSP.ContractId, OPSP.Category, OPSP.NHSNo, OPSP.DistrictNo, OPSP.Forenames, OPSP.Surname, OPSP.DOB, "" AS Address, OPSP.PrimaryProc, OPSP.ClinicName, OPSP.Consultant, OPSP.ConsSpecialty, OPSP.ApptDateTime, OPSP.Outcome, OPSP.ClinicHosptial AS ClinicHospital
FROM nhhtlod_OPSP AS OPSP
WHERE OPSP.Category="20" And
OPSP.ApptDateTime Between #6/25/2010# And #6/30/2010# And
(OPSP.ReferralSource<"BEN" AND OPSP.ReferralSource>"BEN")

I have hard coded the dates so that we can see how much the IIf statement is impacting your query. If we can prove that is the reason for the slow running query then you then need to work out how to build your query so that it doesn't use the IIF.

And I've also changed the ReferralSource so that if there does happen to be any indexing on that field then it will be used (some DBMS's won't use the index when you use a <> criteria)

You also mentioned that you're using linked tables - what type of database are you linking to?

DK