Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Access Pipeline to SQL Server and inefficiencies

Discussion in 'Information Technology' started by wnfisba, Jul 28, 2006.

  1. wnfisba

    wnfisba
    Expand Collapse
    Guest

    Can anyone provide me a link or an explanation as to why SQL Server does not
    run optimized SQL coming over from Access???

    I know there might be some politics involved...Microsoft does NOT want you
    to be able to access SQL Servers tables easily in Microsoft Access...

    Any information would be greatly apprecaited.

    Thanks in advance.

    wnfisba
     
  2. Loading...


  3. Danny J. Lesandrini

    Danny J. Lesandrini
    Expand Collapse
    Guest

    Can't you pass optimizer hints with a Pass Through query? Or does ODBC get in the way?

    --

    Danny J. Lesandrini
    dlesandrini@hotmail.com
    http://amazecreations.com/datafast


    "wnfisba" <wnfisba@discussions.microsoft.com> wrote ...
    > Can anyone provide me a link or an explanation as to why SQL Server does not
    > run optimized SQL coming over from Access???
    >
    > I know there might be some politics involved...Microsoft does NOT want you
    > to be able to access SQL Servers tables easily in Microsoft Access...
    >
    > Any information would be greatly apprecaited.
    >
    > Thanks in advance.
    >
    > wnfisba
     
  4. wnfisba

    wnfisba
    Expand Collapse
    Guest

    How can I pass Optimizer hints through a pass-through query??? We actually
    had our DBA monitor the Access query coming over to SQL Server and it always
    seems to performs table space scans and never utilizes indexes. The query and
    joins on the Access side have been torn apart and are efficient. It just
    seems to be what's coming over the pipeline.

    "Danny J. Lesandrini" wrote:

    > Can't you pass optimizer hints with a Pass Through query? Or does ODBC get in the way?
    >
    > --
    >
    > Danny J. Lesandrini
    > dlesandrini@hotmail.com
    > http://amazecreations.com/datafast
    >
    >
    > "wnfisba" <wnfisba@discussions.microsoft.com> wrote ...
    > > Can anyone provide me a link or an explanation as to why SQL Server does not
    > > run optimized SQL coming over from Access???
    > >
    > > I know there might be some politics involved...Microsoft does NOT want you
    > > to be able to access SQL Servers tables easily in Microsoft Access...
    > >
    > > Any information would be greatly apprecaited.
    > >
    > > Thanks in advance.
    > >
    > > wnfisba

    >
    >
    >
     
  5. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    When you say it always seems to perform table space scans, are you talking
    about pass-through queries, or are you using linked tables? Are the queries
    being used in reports?

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "wnfisba" <wnfisba@discussions.microsoft.com> wrote in message
    news:1A165DF4-13DB-4644-87CA-4344F24532F4@microsoft.com...
    > How can I pass Optimizer hints through a pass-through query??? We actually
    > had our DBA monitor the Access query coming over to SQL Server and it

    always
    > seems to performs table space scans and never utilizes indexes. The query

    and
    > joins on the Access side have been torn apart and are efficient. It just
    > seems to be what's coming over the pipeline.
    >
    > "Danny J. Lesandrini" wrote:
    >
    > > Can't you pass optimizer hints with a Pass Through query? Or does ODBC

    get in the way?
    > >
    > > --
    > >
    > > Danny J. Lesandrini
    > > dlesandrini@hotmail.com
    > > http://amazecreations.com/datafast
    > >
    > >
    > > "wnfisba" <wnfisba@discussions.microsoft.com> wrote ...
    > > > Can anyone provide me a link or an explanation as to why SQL Server

    does not
    > > > run optimized SQL coming over from Access???
    > > >
    > > > I know there might be some politics involved...Microsoft does NOT want

    you
    > > > to be able to access SQL Servers tables easily in Microsoft Access...
    > > >
    > > > Any information would be greatly apprecaited.
    > > >
    > > > Thanks in advance.
    > > >
    > > > wnfisba

    > >
    > >
    > >
     
  6. Danny J. Lesandrini

    Danny J. Lesandrini
    Expand Collapse
    Guest

    Create a new query but before adding any tables, select Pass Through
    from the Query | SQL Specific menu.

    Once opened, paste or type your query, with hints. Next, choose Properties
    from the View menu and set the ODBC property to point to your SQL Server.
    (Either type in the ODBC string or click the elipsis to navigate to a DSN.)

    Run the query.

    I have a pass-through query to return the Server and DBS name.

    select @@ServerName As ServerName, DB_NAME() AS DatabaseName

    As you know, this won't work from an Access query, but it works fine in a
    Pass Through query.
    --

    Danny J. Lesandrini
    dlesandrini@hotmail.com
    http://amazecreations.com/datafast


    "wnfisba" <wnfisba@discussions.microsoft.com> wrote ...
    > How can I pass Optimizer hints through a pass-through query??? We actually
    > had our DBA monitor the Access query coming over to SQL Server and it always
    > seems to performs table space scans and never utilizes indexes. The query and
    > joins on the Access side have been torn apart and are efficient. It just
    > seems to be what's coming over the pipeline.
    >
    > "Danny J. Lesandrini" wrote:
    >
    >> Can't you pass optimizer hints with a Pass Through query? Or does ODBC get in the way?
    >>
    >> --
    >>
    >> Danny J. Lesandrini
    >> dlesandrini@hotmail.com
    >> http://amazecreations.com/datafast
    >>
    >>
    >> "wnfisba" <wnfisba@discussions.microsoft.com> wrote ...
    >> > Can anyone provide me a link or an explanation as to why SQL Server does not
    >> > run optimized SQL coming over from Access???
    >> >
    >> > I know there might be some politics involved...Microsoft does NOT want you
    >> > to be able to access SQL Servers tables easily in Microsoft Access...
    >> >
    >> > Any information would be greatly apprecaited.
    >> >
    >> > Thanks in advance.
    >> >
    >> > wnfisba

    >>
    >>
    >>
     
  7. Albert D.Kallal

    Albert D.Kallal
    Expand Collapse
    Guest

    >
    > I know there might be some politics involved...Microsoft does NOT want you
    > to be able to access SQL Servers tables easily in Microsoft Access...


    not true at all. In fact, they seem to spend all kinds of time and ways to
    get people to upgrade to use sql server!!!

    They LOVE when this happens..and tend to do handstands to make this happen.

    Two things:
    1# if you create a ADP (ms-access project), then ALL OF YOUR queries and
    sql is executed 100% native server side. There is no JET involved, and you
    don't even have local tables. This is a 100% oleDB native sql server
    connection. This is not even odbc. So, ms-access has some serious
    development dollars spent on its ability to work with sql server in a native
    mode. All sql used in this fashion is compliry optimized by sql server
    before any results are retuned to the ms-access client. I only recommend
    using access "projects" for new applications, as exiting ones can't use dao
    code you write, and extensive modifications are required. however, you are
    JUST using ms-access to write sql queries, and build reports, and your data
    is in sql server, then a adp project is great choice...as it takes less work
    to get better performance then linked odbc tables.

    #2
    If you are using odbc linked tables, then the simple solution in most cases
    for a report is to create a view on the server side, and link to that.

    Furthermore, a few other posters here suggested that you can use a
    pass-through query..and again, it will be 100% optimized on the server
    side.....

    Between good designs, using linked views, and pass-through quires...you
    ms-access application will run as well as c++, or vb, or whatever favorite
    developer tool you use to build a user interface to sql server. Don't blame
    ms-access...as it is no worse then VB, or anything else if you do it
    right....


    --
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada
    pleaseNOOSpamKallal@msn.com
    http://www.members.shaw.ca/AlbertKallal
     
  8. wnfisba

    wnfisba
    Expand Collapse
    Guest

    I am indeed talking about linked SQL Server Tables. Part of the politics I'm
    dealing with on this end is that end users do NOT want to lose that control
    over accessing and massaging returned data from SQL Server. When you start
    talking to them about "Stored Procedures" or "Views", then they realize that
    that implies IT dependency which is a hard pill for them to swallow.

    We recently had a software application upgrade where the vendor of our app
    cahnged a lot of columns from char to varchar. That being done has created
    LOTS more rows on pages and thus a lot more contention for pages; which is
    why we believe their Access Database has become real slow.

    It certainly sounds as though Stored Procedures and/or Views is the simple
    solution here. So now I am wondering if anyone has ever encountered something
    like this and have counter attacked with a more viable, still independent
    solution.

    Thanks everybody!

    "Douglas J Steele" wrote:

    > When you say it always seems to perform table space scans, are you talking
    > about pass-through queries, or are you using linked tables? Are the queries
    > being used in reports?
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "wnfisba" <wnfisba@discussions.microsoft.com> wrote in message
    > news:1A165DF4-13DB-4644-87CA-4344F24532F4@microsoft.com...
    > > How can I pass Optimizer hints through a pass-through query??? We actually
    > > had our DBA monitor the Access query coming over to SQL Server and it

    > always
    > > seems to performs table space scans and never utilizes indexes. The query

    > and
    > > joins on the Access side have been torn apart and are efficient. It just
    > > seems to be what's coming over the pipeline.
    > >
    > > "Danny J. Lesandrini" wrote:
    > >
    > > > Can't you pass optimizer hints with a Pass Through query? Or does ODBC

    > get in the way?
    > > >
    > > > --
    > > >
    > > > Danny J. Lesandrini
    > > > dlesandrini@hotmail.com
    > > > http://amazecreations.com/datafast
    > > >
    > > >
    > > > "wnfisba" <wnfisba@discussions.microsoft.com> wrote ...
    > > > > Can anyone provide me a link or an explanation as to why SQL Server

    > does not
    > > > > run optimized SQL coming over from Access???
    > > > >
    > > > > I know there might be some politics involved...Microsoft does NOT want

    > you
    > > > > to be able to access SQL Servers tables easily in Microsoft Access...
    > > > >
    > > > > Any information would be greatly apprecaited.
    > > > >
    > > > > Thanks in advance.
    > > > >
    > > > > wnfisba
    > > >
    > > >
    > > >

    >
    >
    >
     
  9. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    A pass-through query doesn't have to be a stored procedure or a view. It's
    can be simply a query that's run on the server, rather than on the client.

    Create the query, then select SQL Specific from the Query menu. Define the
    connection string, and try it.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no private e-mails, please)


    "wnfisba" <wnfisba@discussions.microsoft.com> wrote in message
    news:B553D20B-67E9-413F-9129-655A8BFC00BE@microsoft.com...
    >I am indeed talking about linked SQL Server Tables. Part of the politics
    >I'm
    > dealing with on this end is that end users do NOT want to lose that
    > control
    > over accessing and massaging returned data from SQL Server. When you start
    > talking to them about "Stored Procedures" or "Views", then they realize
    > that
    > that implies IT dependency which is a hard pill for them to swallow.
    >
    > We recently had a software application upgrade where the vendor of our app
    > cahnged a lot of columns from char to varchar. That being done has created
    > LOTS more rows on pages and thus a lot more contention for pages; which is
    > why we believe their Access Database has become real slow.
    >
    > It certainly sounds as though Stored Procedures and/or Views is the simple
    > solution here. So now I am wondering if anyone has ever encountered
    > something
    > like this and have counter attacked with a more viable, still independent
    > solution.
    >
    > Thanks everybody!
    >
    > "Douglas J Steele" wrote:
    >
    >> When you say it always seems to perform table space scans, are you
    >> talking
    >> about pass-through queries, or are you using linked tables? Are the
    >> queries
    >> being used in reports?
    >>
    >> --
    >> Doug Steele, Microsoft Access MVP
    >> http://I.Am/DougSteele
    >> (no e-mails, please!)
    >>
    >>
    >> "wnfisba" <wnfisba@discussions.microsoft.com> wrote in message
    >> news:1A165DF4-13DB-4644-87CA-4344F24532F4@microsoft.com...
    >> > How can I pass Optimizer hints through a pass-through query??? We
    >> > actually
    >> > had our DBA monitor the Access query coming over to SQL Server and it

    >> always
    >> > seems to performs table space scans and never utilizes indexes. The
    >> > query

    >> and
    >> > joins on the Access side have been torn apart and are efficient. It
    >> > just
    >> > seems to be what's coming over the pipeline.
    >> >
    >> > "Danny J. Lesandrini" wrote:
    >> >
    >> > > Can't you pass optimizer hints with a Pass Through query? Or does
    >> > > ODBC

    >> get in the way?
    >> > >
    >> > > --
    >> > >
    >> > > Danny J. Lesandrini
    >> > > dlesandrini@hotmail.com
    >> > > http://amazecreations.com/datafast
    >> > >
    >> > >
    >> > > "wnfisba" <wnfisba@discussions.microsoft.com> wrote ...
    >> > > > Can anyone provide me a link or an explanation as to why SQL Server

    >> does not
    >> > > > run optimized SQL coming over from Access???
    >> > > >
    >> > > > I know there might be some politics involved...Microsoft does NOT
    >> > > > want

    >> you
    >> > > > to be able to access SQL Servers tables easily in Microsoft
    >> > > > Access...
    >> > > >
    >> > > > Any information would be greatly apprecaited.
    >> > > >
    >> > > > Thanks in advance.
    >> > > >
    >> > > > wnfisba
    >> > >
    >> > >
    >> > >

    >>
    >>
    >>
     

Share This Page