SQL Server :: How To Increase The Performance Of The Select Query Fetching Data From Huge Temp Table
Jan 25, 2011
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 have written one store procedure for fetching huge data like this. there are nearly 2000 records. It is taking nealy 50 sec to execute. Could you tell me best method to fetch large no of data ?
ALTER PROCEDURE bulls_orbit.BuySellUpdateFetchData AS Select Id,Message,EndDate,RefId from tblBuySellUpdate Order By Id Desc RETURN
I want to create a temp table to store data in a stored procedure and then then retrive it from the code. I need to save the data in the begining before it is deleted.And then after the inserts are done I want to update the tblcontactlist by referring to the temp table.
I have partitioned a table and it has been partitioned according to an account id. ie also rows that have accountid =1 will be in A partition and all rows that have accountid = 2 will be in B partition. so now if i execute a query with where condition accountid =1 sql server will search only in A partition. Suppose if i add another condition to it ie where accountid=1 and place="aaa" will this query search only A partition or the entire table?
I have inherited a very old SQL Server Database for a live site, but have been asked to add a new table for invoice recording to it. Fairly easy to do for any new record added from now on, however I run into some issues with selecting older records.I need to find a way to select from 3 tables, see if a record exists in the new table and if not insert a new record to it.My stored procedue so far is (The new table is [Invoicing]:
Does it have any major effect on performance/ memory if my web.config is really huge (say, 1000+ entries in <appSettings>)? Is it a good idea to maintain a different custom xml config file for all business specific settings for my app?
I have about 5,00,000 data. I want to display the records according to group by. It takes to me about 2 minutes. Is it good?
If not, then what is best process to display the record in a very fast way?
Moreover, I have encrypted one field in database and encryption is done from my code behind. So when I display the records, I have to decrypt those. That's why I make paging on 100 records per page.
I'm implementing a BLL layer and have a few questions. First, in my DAL, there is a contacts table, the select method is a sql query against a sql server 2000 database. The preview data returns everyting as expected. The query is
[Code]....
The BLL method is built like so
[Code]....
When using the BLL method to a gridview, the results of the subquery are not displayed. There are also other methods, each having a different where clause for ContactRole_ID and each is having the same issue of not displaying the subquery reult in the gridview. No error, but no data is displayed for the subquery either. Here is the templatefield of the gridview
[Code]....
Another item that is causing an error is in trying to update a record. Here is the DAL update method
[Code]....
And the BLL update method
[Code]....
The error says i have extra parameters, but have no clue where the extra parameters are being picked up. The error isObjectDataSource 'odsContacts' could not find a non-generic method 'UpdateContact' that has parameters: _ContactID, _Active, _ContactName, _Office, _VirtualOffice, _Pager, _Fax, _Home, _Cellular, _Email, _ContactRole_ID, Active, ContactRole_ID, ContactName, Office, Email, VirtualOffice, Pager, FAX, Home, Cellular.And the objectdatasource
As I tested SQL temp table drops every time when sql connection from ASP.NET is closed/open. Is this only way using it, or it can be used depends on ASP.NET session as well? I'd need to access temp table from more separate selects on more different pages on the web site, if it is possible.
Suppose I have a temporary table (Shown below). The table is defined and populated in my stored procedure. After it is populated, I need to go through every row in the table. Here is what I need to do :
Check if Amount2 > Amount1 If Amount2 > Amount1 then set Amount1 = Amount2
It feels a little akward for me to use the temp table.
We have a .aspx file which has about 400 lines of javascript code. Is it a good idea to have such huge code in its own file? What is the performance difference in having huge javascript code in aspx as against the .js file?
I have such sql query, in which there are 8 to 9 temp table generated and from each temp table, I am fetching data with use of read table.
I want to store temp table output into gridview, but after using sqlcommand,I am able to display first temp table output into gridview, but after executing query first temp table destroyed and second sql query which is using first temp table, is not getting reference. as first temp table is not available.
e.g. #temp1 -> I am able to display out put in grid view & destroyed
#temp2 -> which is using #temp1, but not getting reference of #temp1.
i would like to retrieve data from two table, how can i create a temp table and then insert data from two different table.and then use the temp table to join other table in stored procedure.if i want to select same data from two table, how can i select all distinct data from two table into temp table
I'm trying to call a linked server, SS2000 that has NText field and instert that data into an nvarchar(120) field on SS2008.
If I execute the SPROC from SS2008 Management Studio, it works fine. If I try to call the SPROC from my ASP.NET 3.5 web application, I don't get any errors, but the data doesn't get updated.
I'm using the following SPROC: CREATE PROCEDURE [dbo].[usp_UpdateMilestoneDescription] @UID int AS BEGIN
I am creating a temp table and populating it's two columns manually and the rest of the table from the data returned by the stored procedure. When I added the first column called custno as @CustNumber, it allowed me to add it fine and the rest of the columns were filled by the stored procedure as expected. I then needed to add another column called RepeatCustomer the same way but it keeps saying:
Msg 207, Level 16, State 1 Invalid column name 'Repeatcustomer' So eventhough I have added Repeatcustomer column it's not seeing it. Any one has seen this error before or know what's wrong with this code. Note when I parse the query it's fine, it only fails upon the execution.
I have a DataTable available with me which contains thousands of rows. There is a column called EmpID which is containing '0' for some of the rows. I want to remove them from my current DataTable and want to create a new correct DataTable. I cannot go row by row checking it since it contains huge amount of data.
Does Modelbinding increase performance that much? I have a large table which I have split into 4 details/edit screens, and want to load only information that is needed on the particular screen.
