[web] ASP: Are queries bad practise?
Author
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.
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).
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).
Author
Thanks for that [smile]
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?
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?
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.
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.
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.
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.
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.
set DBconn = server.createobject("ADODB.connection") DBconn.open "ODBC data source", "username", "password" set DBrs = server.createobject("ADODB.recordset") DBrs.open "SELECT * from table", DBconnthis 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.
Quote:Original post by evolutionalQuote: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 :(
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.
This topic is closed to new replies.
Advertisement
Popular Topics
Advertisement
