SQL Server :: Vastly Different Performance Of Table Update On Similar Machines?
Oct 21, 2010
Two machines. One a little faster than the other. The slow one runs windows xp, visual webdeveloper express 2010, sql server 2008. The fast one runs windows server 2008, visual wev developer express 2010, sql server 2008.The code in queston involves an automatically generated update function in one of the table adapters, called 'adapter'. Our C# code generates a data table called 'table', with which we update our 'datatable' in the table adapter.
The table has about 100 entries.We then update our datatable in the .xsd file by calling.
int rowsaffected = adapater.Update(table);//this is the line that takes long on fast computer return rowsaffected > 0;//since function returns a bool On the slow machine this line of code takes a few milliseconds.One the fast machine it takes 5 seconds.These results are consistent. It has nothing to do with one machine being busy, the other idle.
What could be the reason and how could it be solved? How can the same code, on the same platform, on a faster machine take 500 times longer than on a slower machine?
I have a piece of sql code that selects the top 10% of a table. See below a sample of the database table
And this is my code
[Code]....
So what this code does is add up all the points in the points table, (also works out what percentage of the points each user has) and then displays the top 10%, which in this case will give me "John" and "Dave"
So what I need the sql to do is check if any other records in that table have the same number of points ast the last selected record, and to select it to.
I have one Store Procedure that generating report ..
For storing the data there , i used many temp. tables. There is many Select Query , little less insert and delete query.
Now if there is huge data around 1 lac in temp table my select query taking to much time and also may be insert and delete query
I added Primary key to all auto Increment Field in temp table. Also defined Clustered index on that primary key as unique Clustered index to improve the performance .
But there is not so much improvement in case of huge temp table.
Right now the whole Store Procedure is taking time to complete around 1.5 days or around 30 hours ..
So i want to increase the performance as much that it completes on nearly 3-4 hours.
I'm using the ASP.Net Membership provider, but I don't want to allow system administrators to DELETE any users. Instead, I simply want to mark them as locked out (aspnet_Membership.IsLockedOut) of the system so they can't log in when an administrator assigns them to a role titled "Inactive". Make sense? Right now my trigger isn't throwing any errors, but it's not working either. Here's my trigger (which is on the aspnet_UsersInRoles table):
UPDATE SPECT SET SRE = CLSS.NEWSID FROM SPECT INNER JOIN student (NOLOCK) ON student.ID = SPECT.ID INNER JOIN CLSS ON CLSS.GID = SPECT.GID ON CLSS.GID = student.GID WHERE student.PID = '20201' AND CLSS.PID = '20201' AND SRE IS NOT NULL
However, student.PID and ClSS.PID need dynamically reach through from table CLSS to get each row of PID.
I have table a and would like update table a whose values from table b, but not update all rows at table a. Only update record ID in table a match record ID in tableC.
Here is my query
update a set a.Desc = b.Desc, a.Value = b.Value from TableA a, TableB b where a.name = b.name and a.ID = b.ID and a.ID in ( select ID from TableC)
I only have 12 a.ID match TableC, but above query update whole TableA.
How to only update 12 record at TableA from TableB?
I have a .net web app hosting in IIS6 with Windows Authentication and anonymous access disabled. The web app is making connection to a SQL Server that is in the same domain, but on a different machine.
I want to have the IIS Worker process acount I_USR_... to be granted access to the SQL Server.
Will this work ?
(Is there a one to one trust ? Or should I make some extra effort to make the trust work ?)
(edit) On this page I read:
IUSR_ must be in the domain, and given proper access to the SQL Server; or, you must disable anonymous access on the site / application - which will allow IIS to pass the users' credentials to SQL Server. Not doing either of these things will result in an error.
My question is about accessing local machines' files from a webserver.. I have an intranet project that works on my network.. My project is about accessing the other local machines,then displaying images from that machines.. For example my project is working on 192.168.9.170...when enter the site..it can be displayed lots of images from local machines..for example an image of them src path is:192.168.9.180, one of them is 192.168.9.181.. So,i want to publish this project on web.Can i access local machines from webserver..how can i map that machines
ParentID points to the ID field so that ID 2 is the child of ID 1.
Unfortunately, the order of the db elements is not guaranteed but what I'd like to do is have the information displayed sort of like in windows explorer with the children directly underneath the parents.
I've done something similar in the past with nested gridviews (ugh!), but it didn't point to itself and I knew how many children (additional db tables) there would be in advance. I'm basically trying to re-create that setup but make it much more flexible by making it point to itself.
Pseudo Coding I'd do something like:
Setup the html table and table headers Go through the DB from the top and search for the row with ParentID = 0 Add that row to the table Go through the db and find the row where ParentID = ID of Step 2)Add that row to the table Recursive magic go back to 4 Keep going deeper inception style ^^ Recursive magic go back to 2
I just have no idea how to even begin doing something like this!
This has nothing to do with my problem, but so you know where I'm going with this: after I get the table/gridview setup in the correct order, I plan on using jquery to hide all the non 0 parentID rows and make the rows clickable so that the child rows expand out underneath the parent row.
I have a form that is being populated with data from a sql table. The user can update via textboxes information on the form and click update. The data should then be updated back to the table.
For some reason, When I add the parameters I need for the query, it is not grabbing the update from my textbox, it is showing the value that was populated upon load. I tried to change the customer's phone number. When I run my code it is pulling in the original phone number and not picking up my change.
i want to update table using web service file, i am retriving value like this :
[WebMethod] public DataSet getinfo() { SqlConnection con = new SqlConnection(@"Data Source=WEBTECHNO1WEBTECHNO;Initial Catalog=blogsite;Integrated Security=True");[code].....
how can i update table using this web service , what should return type in update and all these things...
Im trying to design a simple ASP Page where I can reset the IsLockedout to false. The page already identifiesand lists in an editable gridview which displays accounts which are locked out, using the following code:
SELECT [UserId], [IsApproved], [IsLockedOut], [LastLoginDate], [FailedPasswordAttemptCount], [UserName] FROM [vw_aspnet_MembershipUsers] WHERE ([IsLockedOut] = @IsLockedOut)
However when I run the edit and update it updates all the records and not just the one I am trying to edit. The code for the Update is: UPDATE aspnet_Membership SET IsLockedOut = 0I have tried various Where Clauses to restrict it to only the one account I am editing but I get lots of different syntax errors.
I have a table that is not getting updated. My insert command is working fine. Here is the update code from business logic area and stored prodecure. The SP works fine when I execute it from sql mgmt studio and updates the table:
I have the following code-behind that executes when a user clicks a verification link from their email. The code works up to the sql update part - the user role is updated, then I get the error below. Can someone look at this and tell me what is going wrong:
Code-Behind:
[Code]....
Error:
No mapping exists from object type System.Web.Security.MembershipUser to a known managed provider native type.
Description: An unhandled exception occurred during the execution of the current web request. review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.ArgumentException: No mapping exists from object type System.Web.Security.MembershipUser to a known managed provider native type.
I have created normalised data tables to hold look up information (such as venues, players, teams etc) and want to dynamically update the 'matches' data table (my transactions) using webpages instead of running SQL queries.
What I want to do is select a name from a drop down list and then post the relevant ID associated with that name to my 'matches' table. At the moment I can only link to the value in the drop down list, i.e. the name which is incompatible with the validation which expects a primary key integer.
I'm sure this problem is a fairly common one but I haven't found the right answer on any of the tutorials or blogs, all of them assume that the data is de-normalised or describe things in more technical ways than I can yet understand. If anyone can point me to a 'how to article' or tell me what I am doing wrong, I would be grateful.
So far I am quite comfortable working with C# windows application. I am about to shift to Asp.net to develop a website. The requirement has made me to put around 50 columns in a single table. I know this concept of breaking it into small tables using normal forms.
I tried googling, but dint get much results. I need to know if my table with 50 attributes would decrease performance of my web application?
I am looking for simple asp.net code example showing a simple webform task.
When user clicks a button, a stored procedure fires which runs an update-table query (not pulling any parameters from the user) and returns a count of the number of rows that were updated when the query was run.
Within my stored procedure I have the parameter @RowCount=@@ROWCOUNT.
All sample data I have found online has been C# or VB, but nothing that I can apply to my asp.net program.
Again, this is a simple task that for whatever reason I cannot find examples of online.
