Is store procedure always fast than Access linked table via ODBC?

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

0
aspfun
4/8/2010 6:23:35 PM
sqlserver.server 1327 articles. 0 followers. Follow

3 Replies
805 Views

Similar Articles

[PageSpeed] 19

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@uwe> wrote in message 
news:a63c2ceb52b4d@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
> 
0
Andrew
4/8/2010 9:41:29 PM
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

0
aspfun
4/9/2010 1:30:27 PM
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@uwe> wrote in message 
news:a646305d0e012@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
> 
0
Andrew
4/9/2010 2:45:22 PM
Reply:

Similar Artilces:

Outlook 2003 access to Hamster IMAP fails
I was using Outlook 2003 with Hamster Classic 2.0.0.1 Everything was fine until the Microsoft update 2 weeks ago. When I closed Outlook the processor went to 100%. This seems to be a know bug of Hamster 2.0.0.1 based on pasts posts to the Hamster newsgroup. I therefore upgraded Hamster to 2.1.0.11. Now Outlook 2003 continually locks up. There are no problems with Outlook 2002 or any other email client including Thunderbird and Outlook Express. The issue is limited to Outlook 2003. The older version of Hamster did not support IDLE. I believe the problem is connected to IDLE. Is there a w...

tables in word
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: Power PC there are only two reasons i use office <br> 1. I like entourage as an e-mail client and general organizer. (Sorry I dislike Outlook intensely.) <br> 2. I have the misfortune that I have to use a god awful windoze computer at work with office as he main application. <br><br>At work in word i can insert an excel table, can I do this in office for mac 2008? Yes, you can, in pretty much the same way: Insert> Object - Microsoft Excel Sheet. Unfortunately it is limited to 5 Columns by 10 ...

Link Error: Unresolved external GetThisClass
I am writing an MDI application based generally on the CHKBOOK sample. I need to have several views of the same document. I started out creating a single view and got that working then made a copy of the first view and did a replace on the class name to create the second view. At this point the second view is still identical to the first until I get the mechanism working the way I want it. The problem is that I keep getting the following link error once the second view is added: PCR.obj : error LNK2019: unresolved external symbol "public: static struct CRuntimeClass * __stdcall CByDayVie...

Refreshing ODBC link in Template
I have a template and the link to the ODBC connection needs to be updated. There was a slight change to the table, so I need to refresh the link. This is something I can easily do in Access, but I don't see where to do this in my template. Can someone please point me in the right direction? Thanks in Advance, Trisha ...

HTML Code Fragment via Merge
Just learned how to create a catalog with Publisher and I was pretty pleased with this capability that I had never even noticed before. Now what I would like to do is create a web version of my catalog and include an item in each catalog entry that is an HTML code fragment. These would basically be paypal code items for a shopping cart entry relative to each catalog item. I would like to add the code for my fragment via another column in my XL spreadsheet that is the source for my merge. Does anyone know if this is doable? Get a program suitable to the task called WebPlus10 http://www.se...

need to find the file that Outlook emails are stored in
there is one file that contains all the outlook email and information. i need help to find it. also, how can i view Hidden Files? thanks arthur Open Windows Explorer and select Tools | Folder Options. Select Show Hidden Files and Folders and uncheck the option to hide protected operating system files. OK out and open the search panel (f3). If you are using Windows XP, you need to configure the search to look for hidden items. If all goes right, you should find your *.pst file(s) under \documents and settings\<userid>\local settings\application data\microsoft\outlook. -- Neo [...

Access text box
I was trying to add text boxes to my database in form design view, which worked fine up until the moment when i was back in form or table view. For some reason when I add detail in one of the cells of the new column it was automatically copied to every other cell in that new column. This has only been happening with the new columns and the ones I created using the wizard when starting the database seem to be working fine. What can I do about this? On 25 mrt, 12:19, FernP <Fe...@discussions.microsoft.com> wrote: > I was trying to add text boxes to my database in form desig...

Importing Data From Access Problem
I'm trying to import some query results from MS Access into Excel using the Import External Data function. Some of the queries are coming across OK, others are only bringing the column headers across and some are bringing partial data. Does anyone have any ideas why this may be happening? Thanks Helena ...

Access corrupt files DBF?
Access corrupt files DBF? "Frank Dulk" <fdulk@bol.com.br> wrote: >Access corrupt files DBF? What is your question? I see you're from Brazil. Have you asked your question in the Portuguese Access newsgroup? microsoft.public.pt.access. Tony -- Tony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ "Frank D...

Pivot table #63
I have a pivot table that is connected to an OLAP data source. It is on a network drive so users can access it. It works fine for me and other users, but one user gets a "database <DBName> does not exist" error when he tries to change a filter on the page dimension. I am very new to Pivot tables, so I'm not sure how it works. Does this user need some kind of software loaded in order to access OLAP data sources? ...

Can't Click on Links in E-mail Messages
Starting today, whenever I click on a link in an e-mail message, the following box pops up: This operation has been canceled due to restrictions in effect on the computer. Please contact your system administrator. Does anyone know why this is happening, and what I can do to fix it so that I can click on links? -- Brett Hi Brett, The following Knowledge Base (KB) article addresses the issue that you are having. Please follow the steps outlined in this article and this should resolve your issue. http://support.microsoft.com/default.aspx?scid=KB;EN-US;307818 Regards, Scott Atkins MCSE,...

Changed relationship; records no longer linked
I have a Corporates table holding details of company customers. I have a CorpContacts table holding details of our contact persons at those companies. Originally I had a relationship (one to many) between Corporates.CorpName and CorpContacts.CorpName. I had a Corporates form which contained a CorpContacts subform displaying the contact persons' details. All working fine. But if we updated the name of the company i.e. Corporates.CorpName, the link was lost and the contact persons were no longer displayed. So I introduced new fields in each table ("CorpID"), i.e. some...

Changing OLAP Pivot Table Connection Source
I have numerous spreadsheets that have pivot tables pointing to an OLAP Cube via a connection string. Is there an easy way (either automated or manual) to change the connection string to point to a new version of the OLAP Cube? I can edit the connection properties and navigate to the new .odc connection file, but get an error saying "The OLAP provider returned an unexpected number of dimensions." ...

Link to other slides from table using c#
Hi, I'm looking to create links from a certain slide to another, and I need those links to be from within a table. I need it to be something like this: var pptApp = new PowerPoint.Application(); var presentation = pptApp.Presentations.Add(Office.MsoTriState.msoFalse); var slides = presentation.Slides; slides.InsertFromFile(@"C:\Users\Tng1\Documents\PresentationTemplate.potx" , 0, 1); var titleSlide = slides[1]; titleSlide.Name="TitleSlide" ; var tableSlide = slides[2]; tableSlide.Name = "TableSlide" ; va...

A program is trying to access e-mail addresses you have stored in Outlook. #3
I disabled this prompt always when it first started coming up and now, I need it again. Anyone know where I can reset it? You can't disable the prompt as an end user, so I'm baffled as to what you did and what you're now trying to do. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx "connieh" <anonymous@discussions.microsoft.com> wrote in message news:3d0101c47f27$61d33420$a601280a@phx.gbl... > I disabled this prompt alway...

data links on subsequent worksheets
I have main data entered on worksheet 1 that I have linked to subsequent worksheets, however when there is no data on worksheet 1 I get 0's printed in the data space. How doI eliminate the 0? If you want to hide all zero values on the sheet, Tools --> Options --> [View] tab and clear the checkbox next to "Zero Values" In Excel 2007, that's "Office Button" --> [Excel Options] --> then [Advanced] in left pane and scroll down to the "Display Options for this Worksheet" and clear the box next to "Show a zero in cells that have a...

Transfer Errors
Hello, I am seeing an issue where a store generates a Transfer Out and when the receiving store receives the corresponding Transfer In, extra items show up (seemingly random items with random quantities) there were never issued in the Transfer Out. Has anyone seen this or know why it happens or how to deal with it. It does not seem to occur when HQ initiates the transfer via the 330 worksheet, only when a store originates the Transfer Out. Thanks, Dave It kind of sounds like you have some item data that's out of sync between your stores. You could try reindexing all store datab...

How to tell Excel to insert cells and shift down from Access
Hi all, I have Access 2003. I am trying to tell Excel to insert cells and shift cells down from Access, with this line of code: oWksh.Cells.Insert Shift:=xlDown But Access does not like that line. It gave me run-time error 1004. I know if has to do with the part Shift:=xlDown Can you suggestion what I can do? Thank you in advance, Ben "Ben" <Ben@NoSpam.com> wrote in message news:eSZuzcDpKHA.1892@TK2MSFTNGP02.phx.gbl... > Hi all, > > I have Access 2003. I am trying to tell Excel to insert cells and shift > cells down from Access,...

Import List of Email Addresses from Access
Is there a way to populate a distribution list in Outlook from a table field in Access? Thanks in advance, Claudette ...

How do i set Outlook to receive/send email via wanadoo
http://dts-l.org/goodpost.htm -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. "Wendy" <Wendy@discussions.microsoft.com> wrote in message news:1D969EE0-8D22-482B-901D-8F17FFBFA96E@microsoft.com... | ...

Linking 2 Subforms
After reading several posts on these forums, I am beginning to think that what I would like to do is impossible. I don't know if anyone can point me in the right direction or not, but here goes. I currently have a form with 2 subforms (both in datasheet view). The main form has combo boxes that filter the results in the first subform. I would like to be able to select a record in the first subform and have on the related records shown in the second subform. I believe this has something to do with Parent-Child links, but i know that you cannot have a parent-child link between 2 ...

Show linked cell alone somwhere on the desktop
I have DDE linked data in excell. After the all calculations I have result which is one cell. I need to see this "live" cell somwhere on the screen all the time (Excell can be open in background) and I want to see this number only or as less frame as possible - what is my options? Simply when I'm working with other software there is no way to squeeze in all Excell even if I have multiple monitors (: Thks. It's still me - Simplifying my question. Is there any way to bring Excel spreadsheet out of main Excel frame - in Office 2007? "GPS" wrote: > I have D...

Set table name dynamically for CR 11 in VB.Net 2003
I posted this question earlier today. It showed up but has disappeared now. I have the following code in VB.Net 2003 for CR XI; I would like to set the table name for the report in VB.Net code. All tables would have the exact same structure. With crConnectionInfo .ServerName = MyServer .DatabaseName = MyDatabase .UserID = MyID .Password = MyPWD End With Dim MyTable As String if s=1 then MyTable = TableA else MyTable = TableB end if CrTables = sReportName.Database.Tables For Each CrTable I...

Run query or report from multiple tables
I have three tables: Equipment Instruments System Numbers Each record in the tables 'equipment' and 'instruments' has three lookup fields that link to fields to the 'system numbers' table.' How can I create a query or report that lists all records contained within the equipment and instruments tables that are linked to the system numbers table? On Mon, 26 Nov 2007 21:20:01 -0800, Sim <Sim@discussions.microsoft.com> wrote: >I have three tables: > >Equipment >Instruments >System Numbers > >Each record in the tables 'equipment'...

Access exchange using IMAP
Hi There. I cannot access my email message using IMAP to exchange 2003. The statement as below: Connection refused Configuration: Account: Staffmail Server: 172.18.3.203 User name: Staff\Taufik Protocol: IMAP Port: 143 Secure(SSL): 0 Code: 800cccd9 any ide how so solve it? thanks Have you started the IMAP service and made sure that IMAP access is enabled for your user account? Exchange 2003 disables IMAP and POP3 services by default. -- Ben Winzenz Exchange MVP MessageOne "Muhamad Taufik Khasim" <Taufik@uniten.edu.my> wrote in message news...