Access 2000 Query very slow compared to SQL Query Analyzer

W

WB

I have the following Access 2000 query that runs so slow I can't even wait
until finishes. (over 5 minutes so far). When I run the code in SQL Query
Analyzer it doesn't take but 1 second. I know there are differences between
what Access can handle and SQL Server, but there has to be something I can
do to make the Access query run faster. Any suggestions?

WB

Access query
SELECT a1.Id, a1.StoreId, a1.StylistId, a1.CustomerId, a1.AptDate,
a1.CreatedDate, a1.AptStatusId
FROM tblAppointment AS a1
WHERE (((a1.AptDate) Between CLng(CDate([Enter Start Date])) And
CLng(CDate([Enter End Date]))) AND ((a1.AptStatusId)=3) AND ((Exists (SELECT
*
FROM tblAppointment a2
WHERE a2.AptDate > a1.AptDate AND
a2.CustomerId = a1.CustomerId AND
a2.AptStatusId = 1 AND
a2.CreatedDate < cdate(a1.Aptdate +
1)))<>False));


SQL Query
DECLARE @startDate AS DATETIME
DECLARE @endDate AS DATETIME

SET @startDate = '01/01/2010'
SET @endDate = '01/15/2010'

SELECT a1.Id, a1.StoreId, a1.StylistId, a1.CustomerId, a1.AptDate,
cast(a1.AptDate - 2 AS DATETIME) AS 'ApptDATE', a1.CreatedDate,
a1.AptStatusId
FROM tblAppointment AS a1
WHERE a1.AptDate BETWEEN CAST(CAST(CONVERT(char(8), @startDate, 112) AS
DATETIME) AS NUMERIC) + 2 AND
CAST(CAST(CONVERT(char(8), @endDate, 112) AS
DATETIME) AS NUMERIC) + 2 AND
a1.AptStatusId = 3 AND
EXISTS (SELECT *
FROM tblAppointment a2
WHERE a2.AptDate > a1.AptDate AND
a2.CustomerId = a1.CustomerId AND
a2.AptStatusId = 1 AND
a2.CreatedDate < CAST((a1.AptDate - 1) AS DATETIME))
 
J

John Spencer

First, remove as many of the vba and conversion functions as you can.

Parameters [Enter Start Date] DateTime,
[Enter End Date] DateTime,
SELECT a1.Id, a1.StoreId, a1.StylistId, a1.CustomerId, a1.AptDate,
a1.CreatedDate, a1.AptStatusId
FROM tblAppointment AS a1
WHERE a1.AptDate Between [Enter Start Date] And [Enter End Date]
AND a1.AptStatusId=3
AND Not Exists
(SELECT * FROM tblAppointment a2
WHERE a2.AptDate > a1.AptDate AND
a2.CustomerId = a1.CustomerId AND
a2.AptStatusId = 1 AND
a2.CreatedDate < a1.Aptdate + 1)

It might be faster to use a join in this structure (results are cannot be updated)
Parameters [Enter Start Date] DateTime,
[Enter End Date] DateTime,
SELECT a1.Id, a1.StoreId, a1.StylistId, a1.CustomerId, a1.AptDate,
a1.CreatedDate, a1.AptStatusId
FROM tblAppointment AS a1 LEFT JOIN
(SELECT * FROM tblAppointment
WHERE AptStatusID = 1) AS A2
ON a2.AptDate > a1.AptDate AND
a2.CustomerId = a1.CustomerId AND
a2.CreatedDate < a1.Aptdate + 1)
WHERE a1.AptDate Between [Enter Start Date] And [Enter End Date]
AND a1.AptStatusId=3
AND A2.CustomerID is Null

Of course, you could always use a pass-through query to execute the query on
the server and return the results.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I have the following Access 2000 query that runs so slow I can't even wait
until finishes. (over 5 minutes so far). When I run the code in SQL Query
Analyzer it doesn't take but 1 second. I know there are differences between
what Access can handle and SQL Server, but there has to be something I can
do to make the Access query run faster. Any suggestions?

WB

Access query
SELECT a1.Id, a1.StoreId, a1.StylistId, a1.CustomerId, a1.AptDate,
a1.CreatedDate, a1.AptStatusId
FROM tblAppointment AS a1
WHERE (((a1.AptDate) Between CLng(CDate([Enter Start Date])) And
CLng(CDate([Enter End Date]))) AND ((a1.AptStatusId)=3) AND ((Exists (SELECT
*
FROM tblAppointment a2
WHERE a2.AptDate > a1.AptDate AND
a2.CustomerId = a1.CustomerId AND
a2.AptStatusId = 1 AND
a2.CreatedDate < cdate(a1.Aptdate +
1)))<>False));


SQL Query
DECLARE @startDate AS DATETIME
DECLARE @endDate AS DATETIME

SET @startDate = '01/01/2010'
SET @endDate = '01/15/2010'

SELECT a1.Id, a1.StoreId, a1.StylistId, a1.CustomerId, a1.AptDate,
cast(a1.AptDate - 2 AS DATETIME) AS 'ApptDATE', a1.CreatedDate,
a1.AptStatusId
FROM tblAppointment AS a1
WHERE a1.AptDate BETWEEN CAST(CAST(CONVERT(char(8), @startDate, 112) AS
DATETIME) AS NUMERIC) + 2 AND
CAST(CAST(CONVERT(char(8), @endDate, 112) AS
DATETIME) AS NUMERIC) + 2 AND
a1.AptStatusId = 3 AND
EXISTS (SELECT *
FROM tblAppointment a2
WHERE a2.AptDate > a1.AptDate AND
a2.CustomerId = a1.CustomerId AND
a2.AptStatusId = 1 AND
a2.CreatedDate < CAST((a1.AptDate - 1) AS DATETIME))
Click to expand...
 
J

John Spencer

WHOOPS! missed a delimiter there in the parameter clause.

Parameters [Enter Start Date] DateTime,
[Enter End Date] DateTime;
SELECT a1.Id, a1.StoreId, a1.StylistId, a1.CustomerId, a1.AptDate,
a1.CreatedDate, a1.AptStatusId
FROM tblAppointment AS a1
WHERE a1.AptDate Between [Enter Start Date] And [Enter End Date]
AND a1.AptStatusId=3
AND Not Exists
(SELECT * FROM tblAppointment a2
WHERE a2.AptDate > a1.AptDate AND
a2.CustomerId = a1.CustomerId AND
a2.AptStatusId = 1 AND
a2.CreatedDate < a1.Aptdate + 1)

It might be faster to use a join in this structure (results cannot be updated)
Parameters [Enter Start Date] DateTime,
[Enter End Date] DateTime;
SELECT a1.Id, a1.StoreId, a1.StylistId, a1.CustomerId, a1.AptDate,
a1.CreatedDate, a1.AptStatusId
FROM tblAppointment AS a1 LEFT JOIN
(SELECT * FROM tblAppointment
WHERE AptStatusID = 1) AS A2
ON a2.AptDate > a1.AptDate AND
a2.CustomerId = a1.CustomerId AND
a2.CreatedDate < a1.Aptdate + 1)
WHERE a1.AptDate Between [Enter Start Date] And [Enter End Date]
AND a1.AptStatusId=3
AND A2.CustomerID is Null

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
W

WB

Ok, that was much faster. Now if you care to explain, for my knowledge, why
this is faster I would appreciate it. I know I will come accross this again
in the future.

WB

John Spencer said:
WHOOPS! missed a delimiter there in the parameter clause.

Parameters [Enter Start Date] DateTime,
[Enter End Date] DateTime;
SELECT a1.Id, a1.StoreId, a1.StylistId, a1.CustomerId, a1.AptDate,
a1.CreatedDate, a1.AptStatusId
FROM tblAppointment AS a1
WHERE a1.AptDate Between [Enter Start Date] And [Enter End Date]
AND a1.AptStatusId=3
AND Not Exists
(SELECT * FROM tblAppointment a2
WHERE a2.AptDate > a1.AptDate AND
a2.CustomerId = a1.CustomerId AND
a2.AptStatusId = 1 AND
a2.CreatedDate < a1.Aptdate + 1)

It might be faster to use a join in this structure (results cannot be updated)
Parameters [Enter Start Date] DateTime,
[Enter End Date] DateTime;
SELECT a1.Id, a1.StoreId, a1.StylistId, a1.CustomerId, a1.AptDate,
a1.CreatedDate, a1.AptStatusId
FROM tblAppointment AS a1 LEFT JOIN
(SELECT * FROM tblAppointment
WHERE AptStatusID = 1) AS A2
ON a2.AptDate > a1.AptDate AND
a2.CustomerId = a1.CustomerId AND
a2.CreatedDate < a1.Aptdate + 1)
WHERE a1.AptDate Between [Enter Start Date] And [Enter End Date]
AND a1.AptStatusId=3
AND A2.CustomerID is Null

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

John said:
First, remove as many of the vba and conversion functions as you can.
SNIP

Of course, you could always use a pass-through query to execute the
query on the server and return the results.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Click to expand...
Click to expand...
 
J

John Spencer

Which version was much faster?

First removing all the functions tends to make things faster if the fields are
indexed. If the fields are indexed removing the functions allows Access to
use the indexes.

Second version - Access is much more efficient when you use joins where
possible versus using other ways to filter records.

I don't know all the reasons since I don't know how the query plan is
constructed and how the engine reacts to the query plan.

ALSO, avoid forcing an ODBC query to do some stuff on the Access side in a
where clause. For instance,
AND a2.CreatedDate < CDate(a1.Aptdate + 1)
would probably mean that every record has to be brought back so Access can
apply the CDate function before doing a comparison.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Ok, that was much faster. Now if you care to explain, for my knowledge, why
this is faster I would appreciate it. I know I will come across this again
in the future.

WB

John Spencer said:
WHOOPS! missed a delimiter there in the parameter clause.

Parameters [Enter Start Date] DateTime,
[Enter End Date] DateTime;
SELECT a1.Id, a1.StoreId, a1.StylistId, a1.CustomerId, a1.AptDate,
a1.CreatedDate, a1.AptStatusId
FROM tblAppointment AS a1
WHERE a1.AptDate Between [Enter Start Date] And [Enter End Date]
AND a1.AptStatusId=3
AND Not Exists
(SELECT * FROM tblAppointment a2
WHERE a2.AptDate > a1.AptDate AND
a2.CustomerId = a1.CustomerId AND
a2.AptStatusId = 1 AND
a2.CreatedDate < a1.Aptdate + 1)

It might be faster to use a join in this structure (results cannot be updated)
Parameters [Enter Start Date] DateTime,
[Enter End Date] DateTime;
SELECT a1.Id, a1.StoreId, a1.StylistId, a1.CustomerId, a1.AptDate,
a1.CreatedDate, a1.AptStatusId
FROM tblAppointment AS a1 LEFT JOIN
(SELECT * FROM tblAppointment
WHERE AptStatusID = 1) AS A2
ON a2.AptDate > a1.AptDate AND
a2.CustomerId = a1.CustomerId AND
a2.CreatedDate < a1.Aptdate + 1)
WHERE a1.AptDate Between [Enter Start Date] And [Enter End Date]
AND a1.AptStatusId=3
AND A2.CustomerID is Null

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

John said:
First, remove as many of the vba and conversion functions as you can.
SNIP
Of course, you could always use a pass-through query to execute the
query on the server and return the results.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

WB wrote:
I have the following Access 2000 query that runs so slow I can't even
wait
until finishes. (over 5 minutes so far). When I run the code in SQL
Query
Analyzer it doesn't take but 1 second. I know there are differences
between
what Access can handle and SQL Server, but there has to be something I
can
do to make the Access query run faster. Any suggestions?

WB

Access query
SNIP
Click to expand...
Click to expand...
Click to expand...
 
W

w

Ok, I follow you. The problem as I see it now, a2.Created Date <
Crate(a.Apt date + 1) is written this way because they are different data
types. a2.Created Date is stored as a Date Time value where a1.Aptdate is a
long value as the number of days since (starting date for Access vs Starting
Date for SQL Server) But you state the conversion is done on each record
inside of access.

I do many of these types of comparisons with the table a1. Maybe I should
use a query to create a recordset witht the AptDate converted and then run
the query based on the comparison CreatedDate < converted(aptdate).

I was able to the modify the second example you gave with the joins and I am
on my way to making it work. Thanks Again.

WB

John Spencer said:
Which version was much faster?

First removing all the functions tends to make things faster if the fields
are indexed. If the fields are indexed removing the functions allows
Access to use the indexes.

Second version - Access is much more efficient when you use joins where
possible versus using other ways to filter records.

I don't know all the reasons since I don't know how the query plan is
constructed and how the engine reacts to the query plan.

ALSO, avoid forcing an ODBC query to do some stuff on the Access side in a
where clause. For instance,
AND a2.CreatedDate < CDate(a1.Aptdate + 1)
would probably mean that every record has to be brought back so Access can
apply the CDate function before doing a comparison.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Ok, that was much faster. Now if you care to explain, for my knowledge,
why
this is faster I would appreciate it. I know I will come across this
again
in the future.

WB

John Spencer said:
WHOOPS! missed a delimiter there in the parameter clause.

Parameters [Enter Start Date] DateTime,
[Enter End Date] DateTime;
SELECT a1.Id, a1.StoreId, a1.StylistId, a1.CustomerId, a1.AptDate,
a1.CreatedDate, a1.AptStatusId
FROM tblAppointment AS a1
WHERE a1.AptDate Between [Enter Start Date] And [Enter End Date]
AND a1.AptStatusId=3
AND Not Exists
(SELECT * FROM tblAppointment a2
WHERE a2.AptDate > a1.AptDate AND
a2.CustomerId = a1.CustomerId AND
a2.AptStatusId = 1 AND
a2.CreatedDate < a1.Aptdate + 1)

It might be faster to use a join in this structure (results cannot be updated)
Parameters [Enter Start Date] DateTime,
[Enter End Date] DateTime;
SELECT a1.Id, a1.StoreId, a1.StylistId, a1.CustomerId, a1.AptDate,
a1.CreatedDate, a1.AptStatusId
FROM tblAppointment AS a1 LEFT JOIN
(SELECT * FROM tblAppointment
WHERE AptStatusID = 1) AS A2
ON a2.AptDate > a1.AptDate AND
a2.CustomerId = a1.CustomerId AND
a2.CreatedDate < a1.Aptdate + 1)
WHERE a1.AptDate Between [Enter Start Date] And [Enter End Date]
AND a1.AptStatusId=3
AND A2.CustomerID is Null

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

John Spencer wrote:
First, remove as many of the vba and conversion functions as you can.

SNIP
Of course, you could always use a pass-through query to execute the
query on the server and return the results.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

WB wrote:
I have the following Access 2000 query that runs so slow I can't even
wait
until finishes. (over 5 minutes so far). When I run the code in SQL
Query
Analyzer it doesn't take but 1 second. I know there are differences
between
what Access can handle and SQL Server, but there has to be something I
can
do to make the Access query run faster. Any suggestions?

WB

Access query
SNIP
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Syntax error (missing operator) in query expression 2
JOIN Query very slow 4
insert a crosstab in to temp table query 2
Like to see an example of a "runningsum" being used in a query 0
Query Help, Works in Excel But not Access 3
Access to Excel format 2
excel dates in sql procedures 2
Formatting date gives bad results in query 2

Top