If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
SQL Server
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? |
#2
|
|||
|
|||
SQL Server
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. -- Duane Hookom Microsoft Access MVP "JimP" wrote: 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? |
#3
|
|||
|
|||
SQL Server
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? "Duane Hookom" wrote in message ... 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. -- Duane Hookom Microsoft Access MVP "JimP" wrote: 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? |
#4
|
|||
|
|||
SQL Server
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 "JimP" wrote in message ... 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? "Duane Hookom" wrote in message ... 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. -- Duane Hookom Microsoft Access MVP "JimP" wrote: 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? |
#5
|
|||
|
|||
SQL Server
JimP wrote:
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. -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com |
#6
|
|||
|
|||
SQL Server
...thank you, all.
"Rick Brandt" wrote in message et... JimP wrote: 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. -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com |
Thread Tools | |
Display Modes | |
|
|