Which type of Query is best?

[MikeBronner]

I am currently developing a database dependant application. I have come to a fork in the road, one way says develop with queries coded in the application, the other says queries implemented in the Access database.

My question here is: which is faster for the user?

The database will reside on a network drive, while the application will be ran on each user's PC.

Do you have any suggestions, and if so, why do you suggest that, and not the other?

Thanks so much! Best Regards and many Thanks!
Michael G. Bronner X-)

"Who cares how time advances? I am drinking [beer] today." Edgar Allan Poe

 

[MikeBronner]

*bump*

Any ideas on how i could test each method for performance? Best Regards and many Thanks!
Michael G. Bronner X-)

"Who cares how time advances? I am drinking [beer] today." Edgar Allan Poe

 

[shepherd]

If you're using Access as the database, I doubt there will be any difference. Normally, you would write the query into a stored procedure (on SQL Server for example), which is faster because it is compiled and optimized (plus other reasons...). Access doesn't really do this. To answer your question, I would write the sql directly in the code, because it would probably require less processing server side (though I'm not sure how access instantiates the query).

 

[MikeBronner]

Exactly that was my question, too. Does Access optimize the query in some fashion as a stored procedure? I currently have some queries coded client-side, and noticable delays do occurr while the query is being executed.And this with a database of only a few hundred records.
The database will eventually become quite large, and I fear that handling the queries client-side might impose to much processing on the client.
Wouldn't a stored procedure in Access be more efficient in the long run, as the database becomes larger?

Thanks for your thoughts! Best Regards and many Thanks!
Michael G. Bronner X-)

"Who cares how time advances? I am drinking [beer] today." Edgar Allan Poe

 

[fluteplr]

Not to offend to many people.

But using Access and Optimize/Performance in the same sentence is an oxymoron.

If you are having trouble with a few hundred records you will have a nightmare with any reasonably large number of records.

If you are still fairly early in the development process, I would suggest seriously considering using MSDE (Microsoft Data Engine) as your back end. Another plus side of this is since MSDE is code compatible with SQL server the upgrade is painless when your needs require it.

 

[shepherd]

Access doesn't support stored procedures. You can call queries, but not stored procedures. MSDE will support stored procedures though (MSDE is kind of a midway between SQL Server and Access - and requires no license). That said, stored procedures will realize a performance gain only if it is being called *frequently* and in relative succession. Otherwise, it actually can run slower. From what you've said so far, I'm thinking that you'd be fine coding these within your VB component.

You're raising a bigger issue however. Just because you're coding the SQL statements from the client doesn't mean it's run on the client. However, if you're returning a recordset, you will want to check that the cursor that you're using is a server side cursor (as opposed to a client side cursor) if you want all processing on that recordset to occur on the server. This is necessary no matter how you create the recordset, whether with a stored procedure or with a dynamic SQL query.

Also, keep in mind that Access will not support databases over 2 GB and will probably require frequent compacting if you're doing any frequent table deletions, etc.

 

[strongm]

Access doesn't do stored procedures. Queries held in the database are just that: strings containing SQL that is excuted when the query is invoked. In many ways they are more like SQL Server's Views. There is negligible performance difference between prewritten queries held in the databse versus queries created in the application.

 

[MikeBronner]

I know that Access isn't the best database out there, but it wasn't my choice either. So I'm stuck with it. I would have jumped on SQL, would I be able to use it .

At any rate, my problem is solved: since Access can't do stored procedures (and hence you can't use variables in the queries if they are stored in the database) I will continue to do client side queries.

Which brings us back to the issue of the cursor: client-side or server-side.
Can anyone highlight the pros and cons of each?

Thanks! Best Regards and many Thanks!
Michael G. Bronner X-)

"Who cares how time advances? I am drinking [beer] today." Edgar Allan Poe

 

[heico]

If you use client side, you have to replace the applications for every user if you want to change a query. With serverside you can avoid that problem. Otherwise it doesn't make much of a difference, but I would go with server-side, because it makes your code easier to read, and it's a cleaner way of programming.

Hope this makes your decision easier,

Heico

 

[shepherd]

Cursors don't affect how you compile, release or update an application. That said, what cursors do affect is performance and scalability. I suggest you do some reading on the benefits and drawbacks on various cursor types.

One thing to consider is that if you use server side cursors, you will be requiring more work from your network and server. Depending on how big these resultsets may be, or how much processing you plan to do on them, this could have quite an affect on the performance of both your server, and your application. On the other hand, if you have a reasonable amount of bandwidth, and decent client machines, than client side cursors may be the way to go. This would allow any processing of the resultsets to be done on the client as opposed to the server. Keep in mind that you will be porting more data across your network, and at peak times if your bandwidth isn't sufficient, you may notice it.

 

[strongm]

Also bear in mind, since you are using Access, that the client and the server will be on the same machine...

 

[shepherd]

The client and server do not have to be on the machine. Access applications are capable of being true client server applications. Put the .MDB file on a server, and distribute the .MDEs to your clients. You'll use ODBC to connect to the database (just as you would if you were connecting to SQL server).

 

[strongm]

I completely disagree. Your statement is totally misleading. Sure, you can install the MDB wherever you like - but the important factor is that the Jet database engine (the 'server') actually runs on the local machine. A native Access database is what Microsoft calls a file-server solution, and is in no way similar to client/server solutions such as SQL Server, MSDE, Oracle, Ingres, etc.