Is store procedure always fast than Access linked table via ODBC?
in [Server Configuration]
|
From: aspfun via SQLMonster.com on 8 Apr 2010 14:23 I was assigned to upgrade one program from Access(using ODBC to connect to SQL 2000) to ASP.NET(using store procedure in SQL 2000). Finally, I tested them and found that ASP.NET is slower than Access. The mojority job of program is select some data from SQL 2000 tables, modify and then insert into some tables. Is store procedure always fast than Access linked table via ODBC? -- Message posted via http://www.sqlmonster.com
From: Andrew J. Kelly on 8 Apr 2010 17:41 Stored procedures don't add any overhead and they can save compile time. It's the code in the stored procedure and the underlying tables / indexes that will dictate performance. It's impossible to say what might be the problem without at least seeing the code you have in the sp. -- Andrew J. Kelly SQL MVP Solid Quality Mentors "aspfun via SQLMonster.com" <u53138(a)uwe> wrote in message news:a63c2ceb52b4d(a)uwe... > I was assigned to upgrade one program from Access(using ODBC to connect to > SQL 2000) to ASP.NET(using store procedure in SQL 2000). > Finally, I tested them and found that ASP.NET is slower than Access. > The mojority job of program is select some data from SQL 2000 tables, > modify > and then insert into some tables. > Is store procedure always fast than Access linked table via ODBC? > > -- > Message posted via http://www.sqlmonster.com >
From: aspfun via SQLMonster.com on 9 Apr 2010 09:30 Andrew J. Kelly wrote: >Stored procedures don't add any overhead and they can save compile time. >It's the code in the stored procedure and the underlying tables / indexes >that will dictate performance. It's impossible to say what might be the >problem without at least seeing the code you have in the sp. > >> I was assigned to upgrade one program from Access(using ODBC to connect to >> SQL 2000) to ASP.NET(using store procedure in SQL 2000). >[quoted text clipped - 3 lines] >> and then insert into some tables. >> Is store procedure always fast than Access linked table via ODBC? I found out the difference in first step: In Access, there are three make table query which will link to SQL table and then make theree temp table in local within mdb file. It took only 1-2 seconds. But, in store procedure, I code to create three temp tables (with #) and then insert the same data as Access into these three tables. It took about 5-8 seconds. Am I doing wrong? Is there another way instead of temp table? -- Message posted via http://www.sqlmonster.com
From: Andrew J. Kelly on 9 Apr 2010 10:45 Again its impossible to tell without seeing the actual code. I have no idea what you are trying to do and why you need the temp tables at all. -- Andrew J. Kelly SQL MVP Solid Quality Mentors "aspfun via SQLMonster.com" <u53138(a)uwe> wrote in message news:a646305d0e012(a)uwe... > Andrew J. Kelly wrote: >>Stored procedures don't add any overhead and they can save compile time. >>It's the code in the stored procedure and the underlying tables / indexes >>that will dictate performance. It's impossible to say what might be the >>problem without at least seeing the code you have in the sp. >> >>> I was assigned to upgrade one program from Access(using ODBC to connect >>> to >>> SQL 2000) to ASP.NET(using store procedure in SQL 2000). >>[quoted text clipped - 3 lines] >>> and then insert into some tables. >>> Is store procedure always fast than Access linked table via ODBC? > > I found out the difference in first step: > In Access, there are three make table query which will link to SQL table > and > then make theree temp table in local within mdb file. It took only 1-2 > seconds. > But, in store procedure, I code to create three temp tables (with #) and > then > insert the same data as Access into these three tables. It took about 5-8 > seconds. > Am I doing wrong? Is there another way instead of temp table? > > -- > Message posted via http://www.sqlmonster.com >
|
Pages: 1 Prev: How to compress my log file Next: DB in perptual single_user mode |