Hi all
i'm using sqlserver 2005
this statment take 1:30 min to execute
****insert into temotable (select key from table1)
if i used a select statment alone it takes 4 sec
but with insert statment it take 1:30min
by the way i put indexes on the table1
plz how i can increase the speed of insert statment.
thanks in advance
I have table having around 100 million rows.Everyday we have an ETL process in which table will be trucnated and relaoded. Will creating a partition on the table increase the inserting speed?
View 4 Replies View RelatedWell good morning/afternoon to everyone. It's been a while sinse I've posted here and it seems that the site is a lot faster now. Good to see. :) Anyways, I'm working a current problem here at work with our database being quite slow. I've done some research already and will continue to do so but i wanted to get some of your opinions. Right now, I've run the 'DBCC SHOWCONTIG' command and it is telling the following in the first 3 system tables: DBCC SHOWCONTIG scanning 'sysobjects' table... Table: 'sysobjects' (1); index ID: 1, database ID: 6 TABLE level scan performed. - Pages Scanned................................: 34 - Extents Scanned..............................: 12 - Extent Switches..............................: 33 - Avg. Pages per Extent........................: 2.8 - Scan Density [Best Count:Actual Count].......: 14.71% [5:34] - Logical Scan Fragmentation ..................: 41.18% - Extent Scan Fragmentation ...................: 83.33% - Avg. Bytes Free per Page.....................: 2303.6 - Avg. Page Density (full).....................: 71.54% DBCC SHOWCONTIG scanning 'sysindexes' table... Table: 'sysindexes' (2); index ID: 1, database ID: 6 TABLE level scan performed. - Pages Scanned................................: 72 - Extents Scanned..............................: 16 - Extent Switches..............................: 59 - Avg. Pages per Extent........................: 4.5 - Scan Density [Best Count:Actual Count].......: 15.00% [9:60] - Logical Scan Fragmentation ..................: 50.00% - Extent Scan Fragmentation ...................: 81.25% - Avg. Bytes Free per Page.....................: 4184.9 - Avg. Page Density (full).....................: 48.30% DBCC SHOWCONTIG scanning 'syscolumns' table... Table: 'syscolumns' (3); index ID: 1, database ID: 6 TABLE level scan performed. - Pages Scanned................................: 323 - Extents Scanned..............................: 50 - Extent Switches..............................: 299 - Avg. Pages per Extent........................: 6.5 - Scan Density [Best Count:Actual Count].......: 13.67% [41:300] - Logical Scan Fragmentation ..................: 48.61% - Extent Scan Fragmentation ...................: 96.00% - Avg. Bytes Free per Page.....................: 4527.0 - Avg. Page Density (full).....................: 44.07% DBCC SHOWCONTIG scanning 'systypes' table... Table: 'systypes' (4); index ID: 1, database ID: 6 TABLE level scan performed. - Pages Scanned................................: 1 - Extents Scanned..............................: 1 - Extent Switches..............................: 0 - Avg. Pages per Extent........................: 1.0 - Scan Density [Best Count:Actual Count].......: 100.00% [1:1] - Logical Scan Fragmentation ..................: 100.00% - Extent Scan Fragmentation ...................: 0.00% - Avg. Bytes Free per Page.....................: 6712.0 - Avg. Page Density (full).....................: 17.07% According to the DBCC SHOWCONTIG command documentation, there should be no fragmentation at all. Some questions: 1. would system performance be severly negatively reduced with the above fragmentation (logical and extent)? 2. can the 'DBCC INDEXDEFRAG(dbname, tablename, indexname)' command be issued against those system tables without consequences? 3. is there some other command that can defrag the entire database without having to specify which tables? Also, I have also used the index tuning wizard after a profile trace but that failed with some unknown error. Thats it for now, please let me know if you have some info I could use to help speed up my database.
View 8 Replies View Related
Hi all
I have two tables I need to Select the record from the First table and insert them into the second table and delete the record from the first table how can i do that with the SQL Statment?
Thank you in advance .....
Regards,
sms
i have 3 tables in sql the relation between them is one to one
person ==> employee ==> sales_department_stuff&
another relation ( one to one )between
person(the same as above ) ==> customer
i want to put person id in employee not repeated in customer becase all (employee and customer is a person )
sooooooo
i put this sql statment to try to insert person id in employee then in sales_department_stuff table to complet his ordersinsert into Person (Person_Name_Ar,Person_Name_En) values ('aaaaa',' ssssssss')select @@identity from Personinsert into Employee values (@@identity,'1') select @@identity from Personinsert into sales_department_Staff values (@@identity,'1')select @@identity from Personinsert into Driver values (@@identity,'2','2222','1/1/2005','5') /* SET NOCOUNT ON */
RETURN
the first 3 statment is run and success
but underline stetment return error that
he can insert null value in id field ,,,,,,,,,,,,,,,,
he can compile @@identity in these statment ??????????????
plz help me
Hi All,
I have a table "Person" that has two columns "FirstName" and "LastName".
How can I insert multiple rows into this table using the INSERT stsmt.
I want to use just one insert statement.
Thanks in advance,
Vishal S
Hello, How can I do something like:
Insert Into Displayed(snpshot_id, user_id, user_domain, ddisplay, iWidth, iHeight, disp_size, disp_format, watermark, frc_update, creditcost)
Values ((Select snpsht_id from SiteIndex Where user_domain like '%domain'), 1000, (Select domain_id from UserDomains Where user_domain like 'domain'), GetDate(), 480, 360, 2, 1, 1, 0, 3)
Im getting an error:
Subqueries are not allowed in this context. Only scalar expressions are allowed.
Thanks!
I have two tables that have a one-to-one relationship. The reason they are two table is that one table has user data, whereas the other table has security information. The security information will be in a different security zone.
How do I do an insert into the two tables so that I do not get an integrity error?
I know this is a sin in dbforums to jump forums to ask other forum questions, but I just had to do it.....
it's actually related to foxpro dbf tables. Here is the case:
I am opening this existing DBF file in Microsoft Visual Fox Pro 6.0 .
In the command window, select, update and even including delete statements works .
What is getting on my nerves is the "insert" statement. It always prompts "syntax error". But the @#$@#$ error message just didn't help much.
The funny thing is, if I use the "Append Mode" and add data directly via the GUI, it works!.
Here is the insert statement, just a very simple one:
<code> INSERT INTO ASSET (ACCNO) values '2000/141'</code>
You can reply me here , or go to the real thread to reply me if you can help out..thanks
http://www.dbforums.com/t1058508.html
Hello,
I have a table (SQL Server 2000) which contains data and I want to create insert statements based on this data. The reason for this is to create a .sql file to run this against another database to insert the same data. I know I can do an import but was trying to do this via a .sql script. Is there any way to generate these insert statement automatically using some SQL tool?
Thanks
Hi, I'm having problems passing a querystring from the datanavigateurlformatstring to be used in a insert sp on a webform. Please can someone take a look at my code and point me in the right direction. At the moment I'm gettingthis error messageCompiler Error Message: BC30451: Name 'userid' is not declared. but in the url i can see the querystring I've passed from the previous page.
Thanks in advance Dave
1 Protected Sub Execute_Clicked(ByVal sender As Object, ByVal e As EventArgs)
2
3 Dim objConn As New System.Data.SqlClient.SqlConnection()
4 objConn.ConnectionString = "My connection string"
5 objConn.Open()
6 Dim objCmd As New System.Data.SqlClient.SqlCommand("test_insert", objConn)
7 objCmd.CommandType = System.Data.CommandType.StoredProcedure
8
9
10 Dim puserid As System.Data.SqlClient.SqlParameter = objCmd.Parameters.Add("@userid", System.Data.SqlDbType.Int)
11 Dim pdate As System.Data.SqlClient.SqlParameter = objCmd.Parameters.Add("@date", System.Data.SqlDbType.DateTime)
12 Dim papplicants As System.Data.SqlClient.SqlParameter = objCmd.Parameters.Add("@applicants", System.Data.SqlDbType.Int)
13 Dim pclients As System.Data.SqlClient.SqlParameter = objCmd.Parameters.Add("@clients", System.Data.SqlDbType.Int)
14 Dim pcontacts As System.Data.SqlClient.SqlParameter = objCmd.Parameters.Add("@contacts", System.Data.SqlDbType.Int)
15
16
17
18
19
20 puserid.Direction = System.Data.ParameterDirection.Input
21 puserid.Value = Convert.ToInt32(userid.QueryString)
22 pdate.Direction = System.Data.ParameterDirection.Input
23 pdate.Value = Convert.ToDateTime([date].Text)
24 papplicants.Direction = System.Data.ParameterDirection.Input
25 papplicants.Value = Convert.ToInt16(applicants.Text)
26 pclients.Direction = System.Data.ParameterDirection.Input
27 pclients.Value = Convert.ToInt16(clients.Text)
28 pcontacts.Direction = System.Data.ParameterDirection.Input
29 pcontacts.Value = Convert.ToInt16(contacts.Text)
30
31
32
33 command.ExecuteNonQuery()
34
35
36 objConn.Close()
37
38 End Sub
How to perform a table JOIN with the INSERT SQL statment, just show a join example between 2 tables will do.
View 2 Replies View RelatedHow to perform a table JOIN with the INSERT SQL statment, just show a join example between 2 tables will do.
View 3 Replies View RelatedHi
i m trying to call a function in insert statment
Insert Into (value, value1)
Value(@value, dbo.function(@value1)
dbo.function returns a value,
when i test the function in querry builder all goes fine.
In my program i become a error
"Parameterized Query '' ' expects parameter @value1 , which was not supplied."
I m using visual studio , tableadapter.update function to insert datarecords in db
thx for help
Hi
Simple statment is giving me a logout on the server
INSERT INTO Angebot (Nummer,Variante,Bearbeiter,Datum,Geld,Kurs,Language,Flag,AngebotStatus,TStatus)
VALUES (90360,3,'Admin',DEFAULT,'CPI_BE-D',1,'CPI_AG-1',4,NULL,59)
The statment works fine on production, however i have played a backup from production to my laptop to test something and as soon as I add a record to the database my connection to the database is droped (the data is not added to the table).
the error message I get is double, however I execute the statement only once.
Msg 0, Level 11, State 0, Line 0
Für den aktuellen Befehl ist ein schwerwiegender Fehler aufgetreten. Löschen Sie eventuelle Ergebnisse.
Msg 0, Level 20, State 0, Line 0
Für den aktuellen Befehl ist ein schwerwiegender Fehler aufgetreten. Löschen Sie eventuelle Ergebnisse.
Sugestions anyone?
PS: the message in English is:
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
I'm running this procedure which insert into table_name(id, name.....) select id, name.... from table_name. For some reason the tempdb data file grow up to 200GB. The tempdb is set to expand unrestricted by 10%. How can I prevent that from hapening? Thanks.
View 5 Replies View RelatedI want to know how I can speed up inserting rows. Will stored procedures help at all? Any ideas are wanted. Thanks.
View 1 Replies View RelatedI have a data gathering application written in MSVC++ 6 that uses ADO to insert large amounts of data into a table. Currently I have a stored procedure that inserts a single row at a time and I call it everytime I have more data to insert. However this can often fully load SQL Server - I often have 10's or 100's of inserts a second for short periods and load goes up to 100%...
Does anyone know a way of making the inserts more effiicient without resorting to dynamic SQL?
For example is there a way of batching up these inserts such as passing 10 at a time to the sp and inserting them all at once with an "insert into <table> select ..."?
Or would modifying my C++ and wrapping a block of inserts to the single insert sp in a transaction help?
A collegue suggested writing the data to a temporary text file then using bulk insert at regular intervals but that would then involve writing a file management system as well and seems to be a bit of a hack!
Any help much appreciated.
I can think of ways to resolve this issue, but I am wondering if there is a standard practice or some setting that is used to ensure that text containing a single quote, such as "Bob's house", is passed correctly to the database when using a SqlDataSource with all of the default behavior.
For example, I have a FormView setup with some text fields and a SqlDataSource that is used to do the insert. There is no code in the form currently. It works fine unless I put in text with a single quote, which of course messes up the insert or update. What is the best way to deal with this?
Thank you
Hi,
I am in the middle of writing a console application that acquires data from dynamic odbc connections and inserts the results into a MSSQL database. I'm currently using a datareader to generate multiple calls to a stored procedure and batch execute them by means of a simple counter; this works fine.
However, I'm a little concerned that it seems to take so long to execute the sql stored procs and was wondering if anyone may know of any methods to help speed it up either on the app side or sql, or both.
I had a quick word with our dba who spoke briefly about some kind of process where by the application fires the request across to the database and carries on leaving the db to queue the request or something to that effect. He ran away before I could get any sort of sense out of him.
Any help greatly appreciated
Thanks
Hello,
Server hardware: Intel core 2 duo, 2Gb ram, SATA 1500Gb, SQL Server 2005
I have big table (~ from 50000 to 500000 rows) :
Code Snippet
CREATE TABLE [dbo].[CommonPointValues](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[pointID] [bigint] NOT NULL,
[pointValue] [numeric](10, 2) NOT NULL,
[qualityID] [int] NOT NULL,
[dateFrom] [datetime] NULL,
[dateTo] [datetime] NULL,
CONSTRAINT [PK_PointValues] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY]
UPDATE statement:
Code SnippetUPDATE dbo.CommonPointValues SET dateTo = @last_update WHERE ID = @lastValID
2000 rows update takes about 1 sec.
INSERT statement:
Code Snippet
INSERT INTO dbo.CommonPointValues (pointID, pointValue, qualityID, dateFrom, dateTo )
VALUES (@point_id, @value_new, @quality_new, @last_update, @last_update )
Speed with INSERT is similar like with UPDATE.
I need about 10000 updates per 1 second and 10000 inserts per 1 second or together 5000 inserts and 5000 updates per 1 sec.
Should I update hardware or try some improvements to table structure.
Hi,
I have several data bases on a server (SQL Server 2000 only, no web server installed) and lately, as the company keeps gowing, my users complain saying the server gets slow, (this dbs are well designed and recieve optimizations and integrity checks, etc) because of this, Im thinking about getting a new server to repleace my old ProLiant ML 330 which was bought 4 years ago but Im concerned about what server arquitecture or characteristic can help me best to improve response performance, is it HD speed? Processor speed? or more Ram? I want to make a good decision, so I´d really appreciate your help...
Thanks, Luis Luevano
Choose an EEO-1 Classification: Increase all employees salaries that have: the selected EEO-1 classification by 10%.
Increase all employees salaries by 5%
Hi! I am new to SQL, but trying to teach myself. I have had lots of help from you guys and appreciate it very much. Today, I was trying to do the following:
In my table of "Employees", I have a column called classifications. I want to increase the salaries of the employees with a classification of EE0-1 by 10%, but I have not figured out to do so.
My second question is how would I increase all employees' salaries by 5%?
Anyone can help?
Thanks!
Scott
i have DB 2GB on disk.if i increase tha ram up to 4GB, the sql sever use with theram to his temporary table while process quiry?
View 1 Replies View RelatedHello all,
I have, what i think, is a unique problem that i'm hoping some of you can help me on.
I need to create a record number that is incremented by 1 whenever someone adds a new record to the database. For example, records numbering 1,2,3 are in the database. When the users adds a new record, SQL takes the last recordno, 3 in this case, and adds 1 to it thus producing 4.
Also, i need to have the ability to replace deleted record numbers with new ones. Using the example above, say a user deletes record number 2. Whenever someone adds a new record, sql would see the missing number and assign the new record that number.
I hope i'm making sense here. Does anyone have any ideas about this? Any articles on the web that someone could point me to?
Thanks.
Richard M.
Can someone tell me how I can write a stored procedure that will automatically increment the value of the index by 001? I am attempting to build a table for a menu system and I need to increment the index value of the document by 001 when submitted to the database. I also need to have the script obtain the last index value of the node before inserting new value.
All ideas appreciated.
Sincerely,
Tim
If interested, this requested is based upon an article written by Michael Feranda:
http://www.pscode.com/vb/scripts/ShowCode.asp?txtCodeId=7321&lngWId=4
Database's log file can not increase when nearly get 2 GB
Mine is sql server2000,What's wrong with this? Any idea about this?
Thanks
hi,
I have one DB in SQL Server 2005.When I was creating it
takes 7813 MB.Now the databse size almost full.Now I want to extend the
DB Size.At my HDD has more space(near by 160 GB).But I don't know how to
extend the Size.More over I had one doubt.Am executing the
exec sp_helpdb command.It shows the Datafile maxsize is Unlimited.I want to
know when the data is full,whether it automatically takes the size from the
HDD or not.
Please Help me out to this Problem.
We are getting the following error on an SSIS package:
"54 Diagnostic VirtualSQLName DomainUserName Load Daily 859CF005-CB7F-47D8-8432-AE7C074B343C 1A986F18-343F-4424-ABAB-AC6575187DF3 2007-02-14 10:05:42.000 2007-02-14 10:05:42.000 0 0x Based on the system configuration, the maximum concurrent executables are set to 4. "
Basically it is saying the we have reached the maximum amount of executables. How can we increase this value and where?
Thanks
I am really starting to like CTEs. My only qualm is that you can only use them within the first statement after you declare them. I wish they would remain active for the duration of the sql batch just like everything else (variables,local temp tables, ect). Is there any trick so that you can use them multiple times? Maybe define them as a string and do dynamic sql or something like that? Hopefully (i have not researched the new version of sql server) in SQL Server 2008 the scope has increase. Anyways any help would be appreciated.
thanks,
Ncage
I cant find the error in this sql statment. What I want it to do is return everything from book, locations.locationname, and author.fullname. I want to to only return the first 10 rows. This will be used in paging, so, eventually it will be first 10, than 11-20, etc. Heres what I have, without the row limitSELECT RowNum, book.*, locations.LocationName, author.fullname FROM (SELECT book.*, locations.LocationName, author.fullname ROW_NUMBER() OVER(ORDER BY book.id) as RowNum FROM book INNER JOIN Author ON book.AuthorID = Author.ID OR book.AuthorID2 = Author.ID OR book.AuthorID3 = Author.ID OR book.AuthorID4 = Author.ID OR book.AuthorID5 = Author.ID INNER JOIN Locations ON book.LocationID = Locations.ID WHERE (Author.FullName LIKE '%' + @Search + '%') OR (Author.FirstName LIKE '%' + @Search + '%') OR (Author.LastName LIKE '%' + @Search + '%')) as BookInfo
View 6 Replies View RelatedI have two tables. One for videos and one for a "block list"Videos : VideoID UserID VideoURL VideoTitle etc. VideoBlockList :VideoID UserIDI have a datalist to show the videos but i want for the datalist to miss out any videos that a user can not see.So if the block list has "VideoID" = 2 and UserID = 1 if user 1 does a search then the search will skip out the video 2.how is this done? i tryed to do it using a specific / custom SQL statment but it errored cos the NOT value conflicted with the search... any ideas? thanks in advance si!
View 19 Replies View RelatedSelect @ID = top 1 ID From Contents Where Contents.UserID=@UserID And Contents.Status=4 AND Contents.InEdit=1
why it dosn't get back the ID vlaue but when i remove the top1 one its work well why