migration from MS Acess

Discussion in 'microsoft.public.sqlserver.newusers' started by iccsi, Apr 20, 2011.

  1. iccsi

    iccsi Guest

    Do I need bring all queries to stored procedures after migrate from MS
    Access tables?
    Will it improve performance if I leave all queries in mdb front end?

    Thanks again for helping,
     
    iccsi, Apr 20, 2011
    #1
    1. Advertisements

  2. That's a little difficult to say on a general basis, as it depends a little
    on how the queries are written. If you do:

    sql = "SELECT ... FROM tbl WHERE col " & some_val

    That is not good for performance in SQL Server, since you will litter the
    cache. But if you are already using parameterised queries as in:

    sql = "SELECT ... FROM tbl WHERE col ?"

    You are almost there. You should change it to

    sql = "SELECT ... FROM dbo.tbl WHERE col ?"

    to follow best practice.

    It's never wrong to use stored procedures, but then again, it depends on
    what kind of queries you have. If you tend to build them dynamically and
    conditions depending on user choices, you will need to use dynamic SQL
    in the stored procedures, and in that case there is not really much
    difference.


    --
    Erland Sommarskog, SQL Server MVP,

    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
     
    Erland Sommarskog, Apr 21, 2011
    #2
    1. Advertisements

  3. iccsi

    iccsi Guest

    Thanks for the message,
    Should it improve performance since the data analyze is done in the
    back end is not like MS Access?

    Thanks again,
     
    iccsi, Apr 22, 2011
    #3
  4. This should of course be:

    sql = "SELECT ... FROM tbl WHERE col = ?"

    and likewise

    sql = "SELECT ... FROM dbo.tbl WHERE col = ?"
    It is not so much about the query speed as such - the query always run
    on the server. It is about utilizing the plan cache in SQL Server
    efficiently.

    If you submit

    SELECT col1 FROM tbl WHERE col2 = 9

    and

    SELECT col1 FROM tbl WHERE col2 = 19

    That will be two entries in the cache. If there are tons of users doing
    this, the plan cache can get out of hand.

    If you instead use parameters and explicitly specify the schema as in
    the example above, there will be a single cache entry.

    On a system with a small number of users, this may not matter. On a system
    with a lot of concurrent users, it matters a lot.


    --
    Erland Sommarskog, SQL Server MVP,

    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
     
    Erland Sommarskog, Apr 22, 2011
    #4
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.