Jump to content
  • Advertisement
Sign in to follow this  
boolean

[web] ASP: Are queries bad practise?

This topic is 4799 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

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
Advertisement
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
Sign in to follow this  

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

We are the game development community.

Whether you are an indie, hobbyist, AAA developer, or just trying to learn, GameDev.net is the place for you to learn, share, and connect with the games industry. Learn more About Us or sign up!

Sign me up!