MS SQL Server ODBC - Re: Problem using Access or Query Designer to run queries in SQL S

Author

Re: Problem using Access or Query Designer to run queries in SQL S

BI_Specialist

2005-05-16, 8:23 pm

Thank you for taking the time to respond and appreciate your honesty
regarding Access's limitations. Definitely didn't expect such unequivocal
talk from Microsoft about their own tools. Unfortunately, it appears that you
completely missed my question...either I was too long winded or you were too
busy to actually read my post.

I'm sure its my karma. As part of IT, for years I have chastised "power"
users in Finance or Sales for using Excel and Access against production
Databases as query/reporting tools when there was something like Business
Objects/Cognos etc available. Now, having landed in Finance and not having
access to any IT resources, I've actually come to realize that querying
functionality within Excel or Access isn't so bad, although they do have
their limitations.

Using Access as FE to SQL server is definitely not my first choice. At the
same time, scores of folks use Access to query databases all the time. What I
find ironic is that although I'm no big fan of Access as a FE for querying, I
seem to be in the unlikely situation of having to defend its capabilities in
this regard. As I mentioned before, I used Access to run the same query
against the same 5million records sitting in a MySQL table, with where
conditions etc and didn't get a odbc timeout. If I were to go along with what
you were saying, I'd have to believe that the open source community has
figured out a way around Access's limitations...something that Microsoft's
own engineers didn't or couldn't.

Anyways, peronal commentary aside, let me clarify the symptoms of my problem
again:
1. I AM trying to fetch less data via use of where clauses, when I query
against a SQL server based table (linked via ODBC). And that is exactly when
I get the timeout error. Didnt' matter if it was a Select Query or a
Pass-thru query, still got the ODBC timeout, even though I was pulling in 4-5
fields only and maybe 250 rows after grouping.
2. I can understand if my query times out when trying to retrieve ALL 5
million rows from SQL server..but noooo...that works fine!! Is this is a
"limitation" of Access? Don't understand how a Query-form interface would
help or be any different than a pass-through query, if the SQL being sent to
the server is the same.

Good news is that I searched the Access newsgroup and found out where I
could change the ODBC timeout value from the default 60seconds to 300 (buried
under Query Properties). Problem solved- No more ODBC call failed error
message!!

But if someone could please tell me where the timeout is set within
Enterprise Manager's Query Designer, I'd be very grateful. The symptoms are
identical to Access (can view all records, but can't apply any conditions),
but not sure what the connection type is and how to configure it. I guess
everyone uses Query Analyzer, so this has never been an issue.

Sorry about another long post.

Thanks,
John H.

"Mary Chipman [MSFT]" wrote:

> Bottom line: don't DO that. As you have seen, it won't work and will
> never work the way you are going about it.
>
> Access was originally designed (a dozen years ago) to be a desktop
> database connected to the low-end Jet engine, not a general purpose
> querying tool for fetching server data consisting of millions of rows.
> Its basic architecture is essentially unchanged since then. If you
> want to use it as a FE to SQLS, then you need to design a
> query-by-form interface that builds queries with WHERE clauses that
> restrict the number of rows fetched from the server. There are a ton
> of Access books that have been written over the years that tell you
> exactly how to construct such a UI.
>
> Part of developing a successful and robust application is picking the
> right tool for the job, or understanding the limitations of the tool
> you are stuck with, and working around those limitations. You can use
> Access as long as you honor its limitations and obey the golden rule
> of fetching less data.
>
> --Mary
>

Mary Chipman [MSFT]

2005-05-17, 9:23 am

On Mon, 16 May 2005 18:26:03 -0700, "BI_Specialist"
< BISpecialist@discuss
ions.microsoft.com> wrote:

>1. I AM trying to fetch less data via use of where clauses, when I query
>against a SQL server based table (linked via ODBC). And that is exactly when
>I get the timeout error. Didnt' matter if it was a Select Query or a
>Pass-thru query, still got the ODBC timeout, even though I was pulling in 4-5
>fields only and maybe 250 rows after grouping.

The idea is to perform all data processing on the server, not the
client. Sometimes a Profiler trace can help you troubleshoot. Another
option is to code complex queries as stored procedures, which
guarantees that all processing is done on the server. If you are using
Access queries, then you have to be aware that using functions or
expressions in queries can cause processing to bog down since the
expression service must then process each row individually.

Using Access queries against linked ODBC tables can work, but you have
to be careful in coding them.

>2. I can understand if my query times out when trying to retrieve ALL 5
>million rows from SQL server..but noooo...that works fine!! Is this is a
>"limitation" of Access? Don't understand how a Query-form interface would
>help or be any different than a pass-through query, if the SQL being sent to
>the server is the same.

As in my answer to 1), it depends on what is being processed where.
Without seeing actual query syntax, it's hard to tell. Take a look at
a Profiler trace to analyze the SQL. Even if the SQL sent to the
server is the same, it may not be processed the same way.

Let's clarify what is and what isn't a limitation of Access. When you
do a SELECT * FROM in an Access query, you're not really fetching all
million rows. You're only fetching the first few rows and the key
values to the remaining rows, in what is known in Access as a dynaset,
or keyset cursor. As you scroll through the UI, the data in the
remaining columns is fetched. This creates a situation where locks are
held on the data and the network is continuously in use fetching rows.
You don't get all million rows in a gulp, even though you may think
so. The real limitation here is that Access is tying up network and
server resources while holding on to that keyset cursor. This design
pattern works OK when it's an Access-Jet app, but is terrible for an
Access-SQL app.

>But if someone could please tell me where the timeout is set within
>Enterprise Manager's Query Designer, I'd be very grateful. The symptoms are
>identical to Access (can view all records, but can't apply any conditions),
>but not sure what the connection type is and how to configure it. I guess
>everyone uses Query Analyzer, so this has never been an issue.

Tools|Options|Connec
tions|Query Timeout. QA is not meant to be an
end-user tool for querying data, which is why it's not an issue.

Bottom line: appearances can be deceptive. If you want to build a
light-weight querying application in Access going against SQL Server
data that can handle more than a handful of users without bogging down
the network or the server, then you need to understand that Access
DOES NOT work out of the box, nor was it ever intended to. You'll
solve most of your problems if you learn how to code it the right way
using stored procedures, which you can call from pass-through queries.
Just stay away from cursors :-)

--Mary

BI_Specialist

2005-05-17, 8:23 pm

Thanks again for a detailed response. Lots of good information which I do
appreciate. I realize I'm functioning in a far-from-ideal kind of situation
and its not going to scale as more users use Access/Excel to run ad-hoc
queries against SQL server. Its hopefully a band-aid fix for the next 6
months that allows some work to be done, while more heavy-duty and IT
approved tools/projects come online.

The need for the group of financial analysts I support, is some ad-hoc query
capability via the existing MS Office tool set (can't install any new
software etc..). Therefore stored procedures called via pass-thru queries
are not applicable. They are used to waiting 30+ min for their queries to run
against the company's transactional system (Oracle), so waiting just 2-4 min
for their Access/Excel queries to fetch data from SQL server is still a huge
win. I'm sure once I build a true normalized database or dimensional datamart
out of my big flat file table, query times should be well under the 60 sec
default timeout. Till then, I can instruct them to set the ODBC timeout
within Access to a higher value.

"Mary Chipman [MSFT]" wrote:
>
> Tools|Options|Connec
tions|Query Timeout. QA is not meant to be an
> end-user tool for querying data, which is why it's not an issue.

> --Mary

I think you got confused between QA and QD (thats within EM). The qeury
timeout default within QA is 0, so its never an issue. However my question
was with regards to the hidden-Access-like-GUI based query interface within
Enterprise Manager ...called Query Designer in my SQL server Bible book.
This is the one that times out like Access and there seems to no place for
me to set the timeout value higher.

Also, your info about how Access quries place undue load on network/server
resources and your last statement about QA not being an end-user tool (I
agree with you) leads me to ask:
Whats the best client or most efficient way to query data in SQL server for
an end-user, that is user-friendly (no coding required) and does not bog down
the server?
Is Microsoft planning such a tool or 3rd party tools my only safe bet?

Thanks again,
John H.

Mary Chipman [MSFT]

2005-05-20, 11:23 am

On Tue, 17 May 2005 13:04:04 -0700, "BI_Specialist"
< BISpecialist@discuss
ions.microsoft.com> wrote:

>Whats the best client or most efficient way to query data in SQL server for
>an end-user, that is user-friendly (no coding required) and does not bog down
>the server?
>Is Microsoft planning such a tool or 3rd party tools my only safe bet?

The best way is to code as much as possible in stored procedures,
expecially if the result set is going to be read-only, and let the
users execute the stored procedures. If you want to provide the
illusion that users are querying against base tables, you can create
views that reflect a subset of the data and let the users select from
the views. Unfortunately there isn't anything out of the box that can
create efficient queries because so much of what constitutes
efficiency is the relational design of the tables
(overnormalized=bad)
, how aggregates are created (on server=good, on
client=bad), indexing, network speed, etc. -- all things that have
nothing to do with the tools you select for the client to use on the
FE. So the bottom line is that the developer has to do a lot of the
coding on the back end to make it (a) a seamless, code-free experience
for the user on the front end, and (b) an efficient application.

--Mary

Sponsored Links