Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Posts
    65

    Unhappy Unanswered: asp.net Stored Procedures vs not

    Hey,

    I'm developing an asp.net page that will be looking at having approx 500 000 members.

    I have be warned by my webhost admin not to use stored procedures. They say that they are much less efficient with large databases. Harder to manage, and will lock me down.

    This struck me as odd, as everything I have read and done so far points to SP's being the way to go.

    People that have experience with large databases, what advice / comments can you give me? Should I use stored procedures, or should I put all my sql in the asp.net pages.

    My situation, asp.net, ms sql. I will be storing the users in user groups, each group will have its own table containing member information, each group will have between 20 and 500 members. Each group will also have 2-3 tables associated with it. (not sure if this information is relevant, but if it is, there ya go).

    Thanks
    -Ashleigh

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    What?? The only way I could see it being harder to manage would be if they didn't know what the heck they were doing with stored procedures...

    I've been developing web sites with database interaction for the last 6 years or so and IMO stored procs are a must.

    Stored procedures are less efficient with large databases?? Um,... no,... especially not if you are comparing them to straight sql statements being executed.... yes, you can get problems with execution paths not using the right indexes but if you know what you are doing it's not a big issue....

  3. #3
    Join Date
    Sep 2003
    Posts
    65
    Phew, thought I was going mad. Because I couldn't understand why not to use SP's, lol.

    One question though. (here we see the n00b emerge) lol

    -Quote
    yes, you can get problems with execution paths not using the right indexes but if you know what you are doing it's not a big issue....

    I don't follow what people mean when they say indexing etc. I've sort of learn sql by playing and reading forums. I've never actually found a good book I thought worth buying (or resource really) other than these forums, lol.

    How can I avoid bad indexing? What is indexing? This may be big and hard to answer, so if you'd rather throw me at a resource (online or book), do it, lol. A little reading never hurt anybody. It's a lot of 'reading the wrong stuff' that hurts, lol.

    Thanks again rokslide,
    -Ashleigh
    -Ashleigh

  4. #4
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    In short an index is exactly that.....

    Think about a book,.. it has an index that tells you where to find what you are looking for....

    Tables are like books and they can have indexes,...

    The indexes help "organise" the data and mean that when you are in there looking for things you can find it alot faster.... Indexes will make the database take up more space but generally the performance increase and the reduced table locking will be well worth it...

    It's difficult to say exactly what is bad indexing. I guess it's indexes that are too large for the return they give....

    The problem I was referring to is.... stored procedures are compiled, when they compile the build and execution plan and decide what indexes they will use. Sometimes they will not pick the best index and sometimes the best index will change depending on the data in the table. When this helps you need to force the stored procedure to build a new execution plan. This is probably not technically right (eg I have the phrases wrong) but the gist of it is correct from my understanding...

    Deciding what indexes you want to build really depends on what you want to search the table for and how the data in the table relates to other things.... drop me a PM (do we have PM's here?) and I can help you if you want to provide specific details.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •