[dba-SQLServer] Why is it so?

Thanks for your reply Francisco, but the Union was created as a view in SQL.
So SQL would have already done the work before Access asked to see the
results. This is why it is so bizarre.  The 4 seconds to run in SQL wasn't
through Query Analyser, it was through Enterprise Manager and opening the
view to 'Return all Rows'.  I then used 'Link Tables' in Access to this View
via ODBC.  Then double clicked on the 'linked table' in the database window
(not via a query) and that is where it was taking 20-22 seconds to display
the records. The second view I made in SQL which made the difference was
'SELECT * FROM View_Union_Name' and when I linked this view the same way and
opened it the same way it takes 4 seconds to return exactly the same record
set.   Wierd hey.
Cheers
Selina


----------------------------------------------------------------------------
---- Selina Iddon selina at easydatabases.com.au Ph: 0414 225 265 Easy Access
Databases
----- Original Message -----
From: "Francisco H Tapia" <my.lists at verizon.net>
To: <dba-sqlserver at databaseadvisors.com>
Sent: Tuesday, April 29, 2003 1:34 AM
Subject: Re: [dba-SQLServer] Why is it so?


> Selina,
>    You mentioned that the query took all of 4 seconds to run inside SQL,
is
> that Query Analyzer that you're talking about?  Also you said that using
> linked tables in access it took 20-23 seconds to open, was the Union Query
> made in Access when you were seeing this 20-23 time?  Finally you
mentioned
> that you linked to the view from Access and that was only 4 seconds...
>
> If the above is true on your union query then that makes perfect sense
> because Access will request all the data from all the tables in the query
> and then join them and finally come up with the results...
>
> by linking the view the work is done on the Sql Server and only the
results
> are passed to Access...
>
>
> -Francisco
> http://rcm.netfirms.com
>
> On Friday, April 25, 2003 10:09 PM [GMT-8],
> Selina Iddon <selina at easydatabases.com.au> wrote:
>
> : Hi Everyone
> : Can someone please explain the following.
> : I had a SQL database with a Union View which took 4 seconds to open
> : inside SQL.  Using ODBC connection linked table with Access, it took
> : 20-23 seconds to open.
> : Trying all kinds of things I eventually made a new SQL View which did
> : nothing but show all fields in the Union View.  I then linked the new
> : view and hey presto, Access also only takes 4 seconds to open it.
> : Great that it worked, but I'd love to know why.
> : Thanks
> : Selina
> :
> :
> : _______________________________________________
> : dba-SQLServer mailing list
> : dba-SQLServer at databaseadvisors.com
> : http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> : http://www.databaseadvisors.com
>
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>
>