Sign in to follow this  
boolean

[web] ASP: Are queries bad practise?

Recommended Posts

I have an ASP page pointing at an Access database. I'm just wondering, is it bad practise to be pointing the ASP page to queries in the database, rather than pointing it to just a table and doing the sorting in the ASP code? Thanks.

Share this post


Link to post
Share on other sites
Yes, the queries should be avoided if at all possible. I wish I could recall why, but unfortunately I can't (my company does access development, and we stay away from the query objects if at all possible). It seems like you're asking if you should just open the table up and sort through ASP code, is this what you meant? If so, then I'd stay away from that as well. And just open a recordset object with a SQL query:
SELECT FieldNames FROM YourTable ORDER BY FieldYouWantToSortBy

If you already knew that last part, ignore it :)

BTW, why are you using Access for website? I ask because Access has a horrible record in multiuser environments such as a website (unless you're not planning on getting many hits or are prototyping).

Share this post


Link to post
Share on other sites
Thanks for that [smile]

Quote:
Original post by Tape_Worm
BTW, why are you using Access for website? I ask because Access has a horrible record in multiuser environments such as a website (unless you're not planning on getting many hits or are prototyping).


I thought if you are using ASP, it is just expected that you use Access. Should I be using something else for the ASP site?

Share this post


Link to post
Share on other sites
Well, it depends on what you have available to you. Typically a SQL server of some sort (MySQL, Postgresql, MS SQL, Oracle, etc...). Access is typically fine for a single user/prototyping thing, just to work out the schema of the database and such.

Also keep in mind that as you add/update/delete records/tables/etc... in an Access database it tends to grow in size, and could possibly corrupt (especially if you're doing things it doesn't particularly like, trust me, I've seen a 3 meg access database grow to 6mb after a few changes, it's really a horrible little database).

I'd suggest you check out the available SQL databases that are available from other sources, Access isn't really up to the challenge.

Share this post


Link to post
Share on other sites
Quote:
Original post by boolean
I thought if you are using ASP, it is just expected that you use Access. Should I be using something else for the ASP site?


Unlss your host supports MS-SQL, MySQL or some other database, you've got very little choice.

Share this post


Link to post
Share on other sites
You really should be learning how to query the database using ANSI SQL syntax. Access' SQL syntax is a little odd at times. But you can still query tables using ANSI SQL which is usually done in ASP using ADO (ActiveX Data Objects) heres an example of setting up a connection and issuing a simple SQL command.


set DBconn = server.createobject("ADODB.connection")
DBconn.open "ODBC data source", "username", "password"
set DBrs = server.createobject("ADODB.recordset")
DBrs.open "SELECT * from table", DBconn



this way the SQL is embedded in the ASP pages not in access itself.

As everyone else has stated Access isn't really up to the task. i personnaly would recommend MySQL as it has some nice table and query building tools that you can use and then suck the SQL out and throw it into your ASP pages. Give me a personnal yell if you need any more help.


Share this post


Link to post
Share on other sites
Quote:
Original post by evolutional
Quote:
Original post by boolean
I thought if you are using ASP, it is just expected that you use Access. Should I be using something else for the ASP site?


Unlss your host supports MS-SQL, MySQL or some other database, you've got very little choice.


that is true but most hosting services these days either use php and mysql or ASP and MS SQL Server. I don't know any that still allow you to access MS Access DB's (i'm sure their are some out there ) with all the security risks associated with Access :(

Share this post


Link to post
Share on other sites
Quote:
Original post by boolean
I have an ASP page pointing at an Access database. I'm just wondering, is it bad practise to be pointing the ASP page to queries in the database, rather than pointing it to just a table and doing the sorting in the ASP code?

Thanks.


I'm not sure if I'm understanding you correctly, but from a typical standpoint, letting the database perform the SQL work is superior, performance-wise, to letting ASP perform the SQL work.

I'd say it's more evident when using Stored Procedures with SQL Server than with Access, but there are still performance gains to be had. This has been tested and proven before. I used to use Stored Queries (Access uses Stored Queries which are sort of like Stored Procedures of SQL Server, without much of the use that comes with them) all the time - especially from a code management standpoint - and it was nice to get some performance gains as well.

I'm pretty sure there's an article about it on 15 Seconds. Ah, here it is.

Quote:
Conclusion

There are two main conclusions that can be drawn from these results.

First, if you are executing the same dynamic queries every time in your script (i.e. without any variables), significant performance gains (in this case, 36%) can be achieved simply by switching to Stored Queries. With this comes the aforementioned fringe benefit of increased ease of query administration. For queries which do contain variables, there are still performance gains to be had, but they are less significant (8% for the query tested).

Second (and contrary to my own expectations), using the Command object's Parameters collection in preference to simply appending the parameter values to the end of the stored query SQL statement yields only a minor performance increase. In practice it may be worth completely avoiding this collection, especially since it is since the extra complication that it adds to your scripts (i.e. creating parameter objects, defining the data type, setting the value, and then adding the parameter object to the collection) may not


Hope that's what you were asking and is of use to you.

Share this post


Link to post
Share on other sites
Quote:
Original post by Tape_Worm
Yes, the queries should be avoided if at all possible. I wish I could recall why, but unfortunately I can't


Because you want to avoid disk accesses...

Share this post


Link to post
Share on other sites
You should always totally ignore any performance arguments, and ask

"What would make application maintenance easiest?"

In this case, the answer is to keep all your code in the same place (i.e. the ASP file), not in an object in the database which can become "out of sync" with the ASP code calling it.

The reasons for this are:

- You can't put MSAccess database objects into your version management system.
- You can't easily see what the database objects are
- You can't know whether they're "in sync" in development, staging or production environments, unless you have the MSAccess tool installed in all those places to examine the database manually
- This creates a deployment nightmare

The problem basically with relational databases is, it splits your code up between client code and objects in the database. Objects in the database are not versioned in the same way, exist independently in your dev / staging / production environments, and are not automatically deployed when you deploy a new set of code up.

This can of course be mitigated by having strict controls on how you do everything, i.e. make sure that developers document and script each change to anything in the database, so you can deploy it into staging / production / other developers' development environments as necessary.

Another thing often done in industry is to insist that all database change scripts also have "undo" scripts, so that the deployment can be reversed easily (something, I'm afraid I don't usually do (naughty me))

Mark

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this