SQL Server

[JimP]

I'm currently using MS-Access to write reports using a SQL Server db as the
data source in a client/server arrangement.

The database is sufficiently large (2gb) that data retrieval speed is an
issue.

The current setup is linked tables to the SQL Server db via ODBC, and then
the usual queries.

1. Can views and/or stored procedures on the SQL Server side along with
paramaterized queries provide a significant performance boost?

2. I've read that views and stored procedures can be created w/i Access. Is
this the same as creating them in SQL Server?

3. Are there other things I should try?

 

[Duane Hookom]

If you don't need to link to Access tables, I would consider using only
pass-through queries. If you need to change the criteria of your pass-through
query, you can use a couple lines of DAO code to change the SQL property of
the p-t.

 

[JimP]

2 Questions

1. Where is the processing done in a pass through query?

2. One of the SQL Server programmers is pushing stored procedures, I guess
because the processing is done on the server and you're not relying on ODBC
to carry large amounts of data. Do you think a stored procedure for a
complex query is likely to have a significant performance boost vs a
traditional ODBC connection, linked tables and query?

 

[MH]

1. The processing is done on the server if you use a pass-through query.

2. Listen to the guy, he obviously knows what he is talking about. When you
use stored procedures, the performance gains are huge as the query execution
plan is stored with the procedure on the server. This means that the query
optimiser has to do less work and your server is not bogged down with ad-hoc
queries.

Stored procedures are deffinitely the way to go.

MH

 

[Rick Brandt]

2 Questions

1. Where is the processing done in a pass through query?

By definition a passthrough query is one where a SQL statement is "passed
through" to the server so ALL processing of the query is done on the server.

2. One of the SQL Server programmers is pushing stored procedures, I
guess because the processing is done on the server and you're not
relying on ODBC to carry large amounts of data. Do you think a stored
procedure for a complex query is likely to have a significant
performance boost vs a traditional ODBC connection, linked tables and
query?

It might, and it might not. Each situation is different. However, the more
complex the query the more likely it will be that a passthrough or stored
procedure will be the better way to do it.

Join queries in particular are better done on the server whenever possible.

 

[JimP]

...thank you, all.

By definition a passthrough query is one where a SQL statement is "passed
through" to the server so ALL processing of the query is done on the
server.


It might, and it might not. Each situation is different. However, the
more complex the query the more likely it will be that a passthrough or
stored procedure will be the better way to do it.

Join queries in particular are better done on the server whenever
possible.