Hello,
I use Linked Tables to connect to my SQL Server database.
I have a number of reports which worked fine when I was
using test data (few thousand records).
I just loaded in a million records and ran a report, and
received the following error:
ODBC call failed
when running this query:
SELECT regionorder, Regions3, 1 As Quarter, January as
A, February as B, March as C FROM
RegionofResidenceCrosstab
UNION ALL
SELECT regionorder, Regions3, 2 As Quarter, April, May,
June FROM RegionofResidenceCrosstab
UNION ALL
SELECT regionorder, Regions3, 3 As Quarter, July, August,
September FROM RegionofResidenceCrosstab
UNION ALL SELECT regionorder, Regions3, 4 As Quarter,
October, November, December FROM
RegionofResidenceCrosstab;
RegionofResidenceCrosstab looks like this:
TRANSFORM "~" & Count(IIf(DatePart('yyyy',
& #91;F_ARRV_DAT],1,0)=forms!DateRangePrev
iousCurrentYear!
PrevYear,[Final_Clean Tourists Query].[TRAV-KEY])) & "~"
& Count(IIf(DatePart('yyyy',[F_ARRV_DAT],1,0)=forms!
DateRangePreviousCurrentYear!CurrYear,[Final_Clean
Tourists Query].[TRAV-KEY])) AS Expr1
SELECT [Final_Clean Tourists Query].REGIONS3,
[Final_Clean Tourists Query].regionorder
FROM [Final_Clean Tourists Query]
WHERE [Final_Clean Tourists Query].REGIONS3<>''
GROUP BY [Final_Clean Tourists Query].regionorder,
[Final_Clean Tourists Query].REGIONS3
PIVOT Format([F_ARRV_DAT],"mmmm") in
("January", "February", "March", "April","May","June","Jul
y","August","September","October","November","December");
1. I am wondering if the query is not simply timing out.
2. Given that these queries were written for MS Access
maybe I need to convert them into stored procedures to be
called by the report.
Any other suggestions?Your particular query is way too much for Access to handle. The UNION
clauses are causing all rows to be fetched locally into Access for
processing. Then Access tries to run the IIf and other functions on
each row, one row at a time. So the answer is, yes indeedy, you need
to convert this monster query to a stored procedure, which you can
then call from a pass-through query that you can base the report on.
One other point -- Keep all format functions and the like in the
report itself, don't put those in the stored procedure. You'll just
slow down the execution of the stored procedure, and the report has to
process each line in the result set one at a time anyway. What you
want to do is let the stored procedure do all the data processing,
hand the result set back to Access, and let Access do the
presentation.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Fri, 19 Mar 2004 09:20:34 -0800, "blinton25"
<blinton25@.hotmail.com> wrote:
>Hello,
>I use Linked Tables to connect to my SQL Server database.
>I have a number of reports which worked fine when I was
>using test data (few thousand records).
>I just loaded in a million records and ran a report, and
>received the following error:
>ODBC call failed
>when running this query:
>SELECT regionorder, Regions3, 1 As Quarter, January as
>A, February as B, March as C FROM
>RegionofResidenceCrosstab
>UNION ALL
>SELECT regionorder, Regions3, 2 As Quarter, April, May,
>June FROM RegionofResidenceCrosstab
>UNION ALL
>SELECT regionorder, Regions3, 3 As Quarter, July, August,
>September FROM RegionofResidenceCrosstab
>UNION ALL SELECT regionorder, Regions3, 4 As Quarter,
>October, November, December FROM
>RegionofResidenceCrosstab;
>RegionofResidenceCrosstab looks like this:
>TRANSFORM "~" & Count(IIf(DatePart('yyyy',
>& #91;F_ARRV_DAT],1,0)=forms!DateRangePrev
iousCurrentYear!
>PrevYear,[Final_Clean Tourists Query].[TRAV-KEY])) & "~"
>& Count(IIf(DatePart('yyyy',[F_ARRV_DAT],1,0)=forms!
>DateRangePreviousCurrentYear!CurrYear,[Final_Clean
>Tourists Query].[TRAV-KEY])) AS Expr1
>SELECT [Final_Clean Tourists Query].REGIONS3,
>[Final_Clean Tourists Query].regionorder
>FROM [Final_Clean Tourists Query]
>WHERE [Final_Clean Tourists Query].REGIONS3<>''
>GROUP BY [Final_Clean Tourists Query].regionorder,
>[Final_Clean Tourists Query].REGIONS3
>PIVOT Format([F_ARRV_DAT],"mmmm") in
>("January", "February", "March", "April","May","June","Jul
>y","August","September","October","November","December");
>
>1. I am wondering if the query is not simply timing out.
>2. Given that these queries were written for MS Access
>maybe I need to convert them into stored procedures to be
>called by the report.
>Any other suggestions?
>|||Hi,
Thanks for the pointers, guess I have my work cut out for
me. I may also try upsizing to an ADP to try to improve
performance.
>--Original Message--
>Your particular query is way too much for Access to
handle. The UNION
>clauses are causing all rows to be fetched locally into
Access for
>processing. Then Access tries to run the IIf and other
functions on
>each row, one row at a time. So the answer is, yes
indeedy, you need
>to convert this monster query to a stored procedure,
which you can
>then call from a pass-through query that you can base the
report on.
>One other point -- Keep all format functions and the like
in the
>report itself, don't put those in the stored procedure.
You'll just
>slow down the execution of the stored procedure, and the
report has to
>process each line in the result set one at a time anyway.
What you
>want to do is let the stored procedure do all the data
processing,
>hand the result set back to Access, and let Access do the
>presentation.
>-- Mary
>Microsoft Access Developer's Guide to SQL Server
>http://www.amazon.com/exec/obidos/ASIN/0672319446
>On Fri, 19 Mar 2004 09:20:34 -0800, "blinton25"
><blinton25@.hotmail.com> wrote:
>
database.
August,
("January", "February", "March", "April","May","June","Jul
be
>.
>|||You will gain nothing in performance simply from upsizing to an ADP.
There's nothing magical about an ADP vs. an mbd with linked tables --
you're going to need to rewrite your app no matter which way you slice
it. You'll still need to re-do that query as a proc (since there's no
such thing as a saved query in an ADP anyway). What you give up in an
ADP is a lot of flexibility (local storage, queries, etc.). Also,
forms work differently by fetching snapshots, not dynasets. There's
simply no easy way to upsize a large, complex Jet app that doesn't
involve a re-write.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Sat, 20 Mar 2004 10:24:00 -0800, "blinton25"
<blinton25@.hotmail.com> wrote:
>Hi,
>Thanks for the pointers, guess I have my work cut out for
>me. I may also try upsizing to an ADP to try to improve
>performance.
>
>handle. The UNION
>Access for
>functions on
>indeedy, you need
>which you can
>report on.
>in the
>You'll just
>report has to
>What you
>processing,
>database.
>August,
>("January", "February", "March", "April","May","June","Jul
>be|||Hello,
Sorry, I am looking at performance in terms of moving
processing to the server versus the client and reducing
network traffic. Understood that I will have to rewrite
the queries as stored procedures, and initially will do
this and then pass through to the stored Proc.
Doing some reading at:
http://www.upsizewizard.com/Reference_Page.htm
http://www.granite.ab.ca/access/sqlserverupsizing.htm
to help me understand some approaches to achieving my
goals.
Thanks again for the guidance.
>--Original Message--
>You will gain nothing in performance simply from
upsizing to an ADP.
>There's nothing magical about an ADP vs. an mbd with
linked tables --
>you're going to need to rewrite your app no matter which
way you slice
>it. You'll still need to re-do that query as a proc
(since there's no
>such thing as a saved query in an ADP anyway). What you
give up in an
>ADP is a lot of flexibility (local storage, queries,
etc.). Also,
>forms work differently by fetching snapshots, not
dynasets. There's
>simply no easy way to upsize a large, complex Jet app
that doesn't
>involve a re-write.
>-- Mary
>Microsoft Access Developer's Guide to SQL Server
>http://www.amazon.com/exec/obidos/ASIN/0672319446
>On Sat, 20 Mar 2004 10:24:00 -0800, "blinton25"
><blinton25@.hotmail.com> wrote:
>
for
into
the
like
procedure.
the
anyway.
the
was
and
as
May,
Quarter,
& "~"
("January", "February", "March", "April","May","June","Jul
");
out.
Access
to
>.
>|||I understood that -- I was just saying that you gain nothing in terms
of performance from an ADP that you can't also achieve with an mdb
front-end. The key is fetching only needed data on the client, and an
mdb gives you a lot more options for caching static data locally, etc.
The first link you reference in your post does not answer the question
it poses "why ADP" other than to spout generalities about
client-server being faster. What the Access team itself is saying (at
recent conferences) is that they are recommending mdb over adp because
you give up so much flexibility with an adp. Performance is very much
dependent on factors such as efficient database design, avoidance of
concurrency conflicts, fetching less data, network speed and other
factors that have nothing to do with the client software. It's good
that you're doing reading up on the subject before leaping in, just be
aware that not everything you read is necessarily true or accurate.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Sun, 21 Mar 2004 09:12:30 -0800, "blinton25"
<blinton25@.hotmail.com> wrote:
>Hello,
>Sorry, I am looking at performance in terms of moving
>processing to the server versus the client and reducing
>network traffic. Understood that I will have to rewrite
>the queries as stored procedures, and initially will do
>this and then pass through to the stored Proc.
>Doing some reading at:
>http://www.upsizewizard.com/Reference_Page.htm
>http://www.granite.ab.ca/access/sqlserverupsizing.htm
>to help me understand some approaches to achieving my
>goals.
>Thanks again for the guidance.
>
>upsizing to an ADP.
>linked tables --
>way you slice
>(since there's no
>give up in an
>etc.). Also,
>dynasets. There's
>that doesn't
>for
>into
>the
>like
>procedure.
>the
>anyway.
>the
>was
>and
>as
>May,
>Quarter,
>& "~"
>("January", "February", "March", "April","May","June","Jul
>");
>out.
>Access
>to
