Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Sun Foster
Aged Yak Warrior
515 Posts |
 Posted - 2010-04-08 : 14:14:58
|
| 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 and insert into some tables.Is store procedure always fast than Access linked table via ODBC? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-04-08 : 16:19:56
|
If you just do a simpleINSERT INTO MyTable (Col1, Col2, ...) VALUES ('XXX', 'YYY' ...)then Stored Procedure ("SProc") will be no faster, and may be slower.However, if you do two or more SQL statements in the SProc then the SProc will be faster. If you move some logic from the application into the SProc (so the SProc has IF / ELSE type logic, multiple SQL SELECT /INSERT / UPDATE statements, and so on) then it really starts to win.If you have large tables and are not using parametrised queries from Access, and you change to using parametrised queries calling SProcs the you will again see a performance increase. (Having said that Access is pretty smart under-the-hood, so it may very well be parametrising ordinary queries sent to SQL)So if you are not seeing a performance increase I would suspect that that is because you don't, yet, have the SQL Experience to convert you application to take advantage of what SProcs can offer  |
 |
|
|
Sun Foster
Aged Yak Warrior
515 Posts |
Posted - 2010-04-09 : 09:32:48
|
| 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? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-04-10 : 02:55:37
|
| "Is there another way instead of temp table?"Maybe!Post the query here and someone can comment on best way to do it in SQL. |
|
|
|
|
|
|
|
|
Is store procedure fast than ODBC linked table?