How Do You Improve SQL Performance Over Large Amount Of Data?
Jul 23, 2005
Hi,
I am using SQL 2000 and has a table that contains more than 2 million
rows of data (and growing). Right now, I have encountered 2 problems:
1) Sometimes, when I try to query against this table, I would get sql
command time out. Hence, I did more testing with Query Analyser and to
find out that the same queries would not always take about the same
time to be executed. Could anyone please tell me what would affect the
speed of the query and what is the most important thing among all the
factors? (I could think of the opened connections, server's
CPU/Memory...)
2) I am not sure if 2 million rows is considered a lot or not, however,
it start to take 5~10 seconds for me to finish some simple queries. I
am wondering what is the best practices to handle this amount of data
while having a decent performance?
Thank you,
Recently we added a new table into our SQL2000 database specifically to store scanned in images of documents. This new table contains a PK field, a couple of datetime fields, a couple of char(1) fields and one 'image' field.
Before adding this table, the database size was approx 6GB. Six months after adding this new table, the database has grown to 18GB - 11GB of this is due to the scanned in images.
Would this new table affect the SQL performance with regards to accessing other data in the database that has nothing related to the new table?
If so, would moving this new table into it's own database be recommended?
I have a large amout of dbs (150) on my SQL Server and when using the enterprise manager to do administrational tasks like Backup, Restore etc. it takes 1.5 hour to open the Database folder. Server is 2xP4, 3Gb RAM. Any ideas on how to manage this number of dbs on the same server and instance of SQL. Cheers!
i have just created a test database and now need to insert a large number of records into one of the tables, we were thinking of about 1 million records, has anyone got an sql script that i could use to create these records
This is a general question on the best way to import a large amount of datato a MS-SQL DB.I can have the data in just about any format I need to, I just don't knowhow to import the data. I some experience with SQL but not much.There is about 1500 to 2000 lines of data. I am looking for the best way toget this amount of data in on a monthly basis.Any help is greatly thanked!!Mike Charney
I need to delete data from a particular table which has more than half a million records. The data needs to be deleted is more than 200,000 records from the table. What is the best way to delete the data from the table other than importing into a temporary table and performing the same operation?
Let me know if the strategy to be followed is okay.
1. Drop all the triggers 2. Drop all the indexes 3. Write a procedure with a loop setting ROWCOUNT to 1000 and delete the records. ( since if I try to delete all the rows it will give timeout error ) The above procedure will delete 1000 records for each batch inside the loop till it wipes out all the data for the specified condition. 4. Recreate Indexes and Triggers.
Please let me know if there are any other optimal solution.
I have SQL 2000 and need to retrieve fairly large amout of data (~50.000 characters) in XML format and then insert it into the field ofthe text type.As 'FOR XML' can't be used with either local variables, INSERT INTO orSELECT INTO this makes "XML support" quite useless in many aspects.Can anyone please help me in solving this.Thanks a lot for your help and time.Pavel
i need to retrieve a large amount of data from the sql server database and make changes to one field and put the data back using a console application. how do i do it?
Hi, My application needs to retrieve data from a table which has more than 15 lakh records. The records keep increasing in thousands every 15 days. Is there anyway i can reduce the time to retrieve? basically i have a select statement with a few conditions and a clause for the id's of these records.
We have several database linked via merge replications. Due to business requirements, we need to delete 5M rows in one table, we did it on one subscriber. However, the publisher kept uploading the deletion operations from the subscriber and blocked any downloading operation from publisher to subscriber. How can we acceralte the replications now as this has already operated in 2 days, and will continue 1-2 days? Is it possible to set the publisher take the downloading before uploading? How to speed up large amount data deletion operations in replication environment?
I have an interesting challenge. we are not allowed to allow users direct access to data in our SQL Server. Audit requires us to take the data out of our production server and pass it to the user. my situation is i have a table in SQl with over 100,000 records and growing. i want to pass that to an access data base. i am utilizing DTS and a data transform.
i s there a better way? the package is running slow and even appears to freeze. i see this amount of data as growing as well.
I have a combo box where users select the customer name and can eithergo to the customer's info or open a list of the customer's orders.The RowSource for the combo box was a simple pass-through query:SELECT DISTINCT [Customer ID], [Company Name], [contact name],City,Region FROM Customers ORDER BY Customers.[Company Name];This was working fine until a couple of weeks ago. Now wheneversomeone has the form open, this statement locks the entire Customerstable.I thought a pass-through query was read-only, so how does this do atable lock?I changed the code to an unbound rowsource that asks for input of thefirst few characters first, then uses this SQL statement as therowsource:SELECT [Customer ID], [Company Name], [contact name],City, Region Fromdbo_Customers WHERE [Company Name] like '" & txtInput & "*' ORDER BY[Company Name];This helps, but if someone types only one letter, it could still bepulling a few thousand records and cause a table lock.What is the best way to populate a large combo box? I have too muchdata for the ADODB recordset to use the .AddItem methodI was trying to figure out how to use an ADODB connection, so that Ican make it read-only to eliminate the locking, but I'm striking outon my own.Any ideas would be appreciated.Roy(Using Access 2003 MDB with SQL Server 2000 back end)
guys,I have a project need to move more than 100,000 records from onedatabase table to another database table every week. Currently, usersinput date range from web UI, my store procedure will take those dateranges to INSERT records to a table in another database, then deletethe records, but it will take really long time to finish this action(up to 1 or 2 hours).My question is if there is some other way I should do to speed up theaction, I am thinking about use bcp to copy those records to datafileand then use bcp to insert it into SQL Server table. Is this the rightway to do it or should I consider other solution (then, what is thesolution.)Thanks a lot!
We have created a databse in SQL Server 7 for support issues. We are having trouble with only one aspect of the databse and that is the body of the supported problem.
The databse is basically a Question/Answer Support database whch house Frequently Asked Questions. When the user does a search on a specific problem they have a list of matching questions shows on the screen (links). When the question is clicked on it shows the Questions with the Answer of possible fixes.
Our problem lies in the fact that SQL server is truncating the Answer portion. I have tried different Data Types with maximum lengths with no success it keeps truncating it. Right now I am on Data Type nvarchar with a length of 4000.
If anyone has any pointers on how to solve this problem all input is appreciated. You can post here to the forum or e-mail me directly at jason@flnet.com
Hello,I have experienced that some of my tables occupies an extremely large amountof pages but with few rows. An example is a table with 37 rows over 22000pages !. The columns are simple integer and char. I fixed the problem byintroducing a clustered index. Now it only uses 1 page. But can anyoneexplain this behaviour in SQLServer 2000 ?regards Jakob Mathiasen
Is there any way how to create indexies after insertion of any number of records (I dont want to create index after insertion of every record, but for example after insertion of 1000 records) ?
I heard it should be possible with „bulk insert“ or with transactions. Is it right ? I need do this with MS SQL Server 2005 (Workgroup edition).
I have been in contact with my Hosting provider who have told me that my website seems to be making a large amount of SQL commands, and locking the tables.
Hi, We have a poorly performing SQL 2000 db. i have just defragged ( the HD, not indexes, these are done daily via SQL Agent) the data files of our server and have not found any improvement in response. I have now got into using SQL profiler to analyse the server performance. in the results that the trace is returning there are some huge (REALLY BIG) values for the duration and cpu values but these rows have no textdata value returned (ie it is null)
why is this? for these rows, the reads and writes columns are also high.
if these rows are what is taking the cpu's time then how can i identify what the server is doing to make any changes?
any thoughts on what other values i might trace or what action i can take to find the slow down cause?
in performance manager the processors (dual Xeons) are rarely dropping below 60%.
Dear Experts, I'm a DBA, Working for a Product based company. We are implementing our product for a certain client of huge OLTP. our reports team is facing problem (error: all the reports are timed out).though the queries are written properly, Each query is taking some minutes of time. I've given the command DBCC DROPCLEANBUFFERS. the time immediately dropped to 10 sec.
now my question is : please suggest me the DBCC commands or any DBA related commands to improve the performance of the application for my reports team.
from your experience in SQL 2005 - do i have any free software that can help in improve performance or can help in identifying performance bottleneck. two examples of performance and help that i use usually use are the maintenance plan that do (check DB > reorganized index > rebuild index > update statics) and the second software is the SQL 2005 DASHBOARD for the reporting help. do you have any other free tools and help that you can give me for performance or any thing that i must have in my SQL 2005 servers.
I have a table called work_order which has over 1 million records and acontractor table which has over 3000 records.When i run this query ,it takes long time since its grouping bycontractor and doing multiple sub SELECTs.is there any way to improve performance of this query ??-------------------------------------------------SELECT ckey,cnam,t1.contractor_id,count(*) as tcnt,(SELECT count(*) FROM work_order t2 WHEREt1.contractor_id=t2.contractor_id and rrstm=1 and rcdt is NULL) as r1,(SELECT count(*) FROM work_order t3 WHEREt1.contractor_id=t3.contractor_id and rrstm=2 and rcdt is NULL) as r2,(SELECT count(*) FROM work_order t4 WHEREt1.contractor_id=t4.contractor_id and rrstm=3 and rcdt is NULL) as r3,SELECT count(*) FROM work_order t5 WHEREt1.contractor_id=t5.contractor_id and rrstm=4 and rcdt is NULL) as r4,(SELECT count(*) FROM work_order t6 WHEREt1.contractor_id=t6.contractor_id and rrstm=5 and rcdt is NULL) as r5,(SELECT count(*) FROM work_order t7 WHEREt1.contractor_id=t7.contractor_id and rrstm=6 and rcdt is NULL) as r6,SELECT count(*) FROM work_order t8 WHEREt1.contractor_id=t8.contractor_id and rcdt is NULL) as open_count,(SELECT count(*) FROM work_order t9 WHEREt1.contractor_id=t9.contractor_id and vendor_rec is not NULL) asAck_count,(SELECT count(*) FROM work_order t10 WHEREt1.contractor_id=t10.contractor_id and (rtyp is NULL or rtyp<>'R') andrcdt is NULL) as open_norwoFROM work_order t1,contractor WHEREt1.contractor_id=contractor.contractor_id andcontractor.tms_user_id is not NULL GROUP BYckey,cnam,t1.contractor_id ORDER BY cnam*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
Hey guys,Here's my situation:I have a table called lets say 'Tree', as illustred bellow:Tree====TreeId (integer)(identity) not nullL1(integer)L2(integer)L3(integer)....L10(integer)The combination of the values of L1 thru L10 is called a "Path" , andL1 thru L10 values are stored in a second table lets say called'Leaf':Leaf====LeafId (integer)(identity) not nullLeatText varchar(2000)Here's my problem:I need to lookup for a given keyword in each path of the tree table,and return each individual column for the paths that match thecriteria. Here's the main idea of how I have this now.SELECT TreeId,L1,L2,...,L10, GetText(L1) + GetText(L2) as L2text + ...+ GetText(L10) AS PathTextINTO #tmp FROM Tree //GetText is a lookup function for the Leaf tableSELECT L1,GetText(L1),L2,GetText(L2),...,L10,GetText(L10) FROM #tmpWHERECharIndex(@keyword,a.pathtext) > 0Does anyone would know a better,smart, more efficient way toaccomplish this task? :)Thks,
I'm facing a performance issue with the following query... The Output of the following Query is 184 Records and it takes 2 to 3 secs to execute the query.
SELECT DISTINCT Column1 FROM Table1 (NOLOCK) WHERE Column1 NOT IN
(SELECT T1.Column1 FROM Table1 T1(NOLOCK) JOIN Table2 T2 (NOLOCK)
ON T2.Column2 = T1.Column2 WHERE T2.Column3= <Value>)
Data Info.
No of records in Table1 --> 1377366
No. of distinct records of Column1 in Table1 --> 33240
Is there any way the above query can be rewritten to improve the performance, which should take less than 1 sec... (I'm using DISTINCT because there are Duplicate records of Column1 inTable1 )
Any of your help in this regard will be greately appreciated.
I am not an expert in either SSIS or VFP technology but know enough to get my way round. One anomaly I did discover I thought was worth sharing for all those concerned with getting large amounts of data out of VFP in as short a time as possible. When you search for performance tips in relation to SSIS the advice is to never use select table or view from data access mode list in ole db source as this effectively translates to select * from table and I've never come across anything to contradict this €“ well I am and let me explain why:
When you use SQL command as data access mode in ole db source (where ole db source is foxpro dbc) and you write out select column1, column 2 etc etc from table a etc etc and then connect that to a destination (in my case ole db destination) the SSIS task spends ages stuck on Pre-execute before anything happens (the bigger the fox tbl the longer the wait). What is happening behind scenes is that the foxpro engine (assuming its foxpro engine and not sql engine €“ either way don€™t think it matters too much) is executing the sql command and then writing results to a tmp (temp) file on your local temp folder €“ (in my case : C:Documents and SettingsautologinLocal SettingsTemp1). These files take up gigs of space and it is only when this process is complete does the SSIS task actually finish the Pre-execute and start the data transfer process. I couldn€™t understand a) why my packages were stuck on pre-execute for such long times? and b) why were the tmp files being created and why they were soo big?
If you change from SQL command in source to Table or view and then select your table from list the SSIS task when executed kicks off immediately and doesn€™t get stuck on pre-execute nor create any tmp files €“ so you save time and disk space. The difference in time is immense and if like me you were really frustrated with poor performance when extracting from VFP now you know why.
Btw maybe this does not apply to all versions of VFP but it certainly does to v7.
Hi, I have database D1 which contains 5 million users and one more database D2 having 95k Users. i wanted to insert common users into new database D3 based on filter which is Phone number and is unique value. Below is the structure of my tables in D1 and D2:
Now userProfiles table contains data in string format as below: User.state AA User.City CC User.Pin 1234 User.phonenumber 987654
so iam parsing for each user using cursor and writing phone numbers into some temp table and wanted to query D2 database to verify whether this phone number exists in Alerts Table of D2 database.
can anyone please suggest on how i can go ahead with this and also help me on how to improve perfomance.
Hello all - I currently have a project that has a gridview on the front end. The user can select multiple items from this grid, hit a button and all of the selected records should be updated in the process. However, this resultset can have a large amount of data coming back, and I'm stumped on how to pass all of the ID's to the sp. I'd rather not call the SP for each record selected, as there could be 1,000 items selected, and well, I'd rather not call the SP 1000 times :p. I thought of generating a comma delimited list as I'm looping through the grid and using dynamic sql, but the IDs are about 6-7 numbers long, and including comma, would take up almost all of the max space in a varchar. Are there any good solutions to this problem? Passing the items as an array? Generating a data table in .NET and passing that? Any help would be appreciated. -Jaime
How can I make a recursive algorithm in TSQL without using a large amount of resources?
New hires are put on a 6 month probation period, Their probation ends at the 6 month mark + sick time isnull(floor(sick_hours/8),0) they accrued during that 6 months. Their accrued sick days are added to the 6 month probation period to establish a new probation end date.
I then have to do the following loop:
1)Determine if any of the days between the original probation end date and the new probation end date have any weekend days or holidays for a particular employee.
2)If it does, I add those days to the new probation end date to create a newer probation end date. a.I then go back up to step 1).
3)If it does not, I jump out of loop and look at the next employee on probation.
Note: I already have coding for determining number of sick days accumulated during an employee’s probation period. Recursive CTE? Recursive function? Other?
Hi everyone I need a solution for this query. It is working fine for 2 tables but when there are 1000's of records in each table and query has more than 2 tables. The process never ends. Here is the query (select siqPid= 1007, t1.Gmt909Time as GmtTime,(t1.engValue+t2.engValue+t3.engValue+t4.engValue) as EngValue, t1.Loc1Time as locTime,t1.msgId into #temp5 from #temp1 as t1,#temp2 as t2,#temp3 as t3,#temp4 as t4 where t1.Loc1Time = t2.Loc1Time and t2.Loc1Time = t3.Loc1Time and t3.Loc1Time = t4.Loc1Time) I was trying to do something with this query.
But the engValues cant be summed up. and if I add that in the query, the query isnt compiling. (select siqPid= 1007, t1.Gmt909Time as GmtTime, t1.Loc1Time as locTime,t1.msgId,(t1.engValue+t2.engValue+t3.engValue+t4.engValue) as engValue --into #temp5 from #temp1 as t1 where exists (Select 1 from #temp2 as t2 where t1.Loc1Time = t2.Loc1Time and exists (Select 1 from #temp3 as t3 where t2.Loc1Time = t3.Loc1Time and exists (Select 1 from #temp4 as t4 where t3.Loc1Time = t4.Loc1Time))))
I need immediate help on that, I would appreciate an input on it.
I should add an Identity field (Identity=True) and a row version field(timestamp) to my table, and avoid to arrange tables into different databases, is it true in general?
I have a view which uses UNION of two tables. First table has a 1.5 Million records and the second one has 40,000 records. When I query the view with a column (that is indexed in both tables) in the where clause, it's taking taking 3 Minutes to give the result. The column is of DateTime data Type. Any ideas as to how to improve the query performance ???
Hello, I have the following setup and I would appreciate any help in improving the performance of the query.
BigTable: Column1 (indexed) Column2 (indexed) Column3 (no index) Column4 (no index)
select [time] = CASE when BT.Column3 = 'value1' then DateAdd(...) when BT.Column3 in ('value2', 'value3') then DateAdd(...) END, Duration = CASE when BT.Column3 = 'value1' then DateDiff(...) when BT.Column3 in ('value2', 'value3') then DateDiff(ss, BT.OrigTime, (select TOP 1 X.OrigTime from BigTable X where X.Column1 > BT.Column1 and X.Column3 <> 'value4' order by X.Column1 )) END,
FROM BigTable BT where BT.Column3 = 'value1' OR (BT.Column3 in ('value2', 'value3') and BT.Column4 <> (select X.Column4 from BigTable X where X.Column1 = BT.Column1 and X.Column3 = 'Value1'))
Apart from the above mentioned, there are a few more columns which are just a part of select statement and are not in any condition statments.
The BigTable has around 1 Mil records and the response time is very poor, it takes around 3 mins to retrieve the records (which would be around 500K)
I have this queston that I cannot get a clear answer on. I have searched the internet to find out if using foreign keys have any performane benefits but some articles yes and some say no. So what should I believe here. Does foreign keys have any performance benefits.
