Does anyone know how to improve performance on insert statements. I have to run a query of several thousand insert statements, but it just takes too long. Does anyone know of any good tips to improve performance?
joe
Hi, I was aware of an idea that I want to share with you guys: Here's the thing, I have an Access DB that I will pass to SQL, optimizing its structure and data, to optimize then its performance. About this last issue, the present Access DB has some consultations very redundant. Take this example as "source code": Consultation A is made by selecting some fields with some conditions on consultations A and B. I want to end this. Consultation A is consultation A, B is B and C is C, they all are made by themselfs.However, a new idea came to light! This data is to be displaied on a Web application on VB.Net, and I want to show the results from consultation A, B and C, once per time, but all sequencially. What if I execute two commands, one reading consultation A (by a stored procedure), another to consultation B, and save that data in an arraylist of objects, and then generate consultation A based on those application objects that are alread instaned and ready to use, selecting the final data on those objects and not in the database? Am I making any mistake, or am I optimizing performance somehow? The final purpose is really getting the most performance as we can!Any tips on this?Thanks a lot!
View 3 Replies View RelatedHello all,I've following problem. Please forgive me not posting script, but Ithink it won't help anyway.I've a table, which is quite big (over 5 milions records). Now, thistable contains one field (varchar[100]), which contains some data inthe chain.Now, there is a view on this table, to present the data to user. Theproblem is, in this view need to be displayed some data from this onelarge field (using substring function or inline function returningvalue).User in the application, is able to filter and sort threw this fields.Now, the whole situation starts to be more complicated, if I would likecombine this table, with another one, where is one additional much morlarger field, from which I need to select data in the same way.Problem is: it takes TO LONG to select the data according to userrequest (user access view, not table direct)Now the question:- using this substring (as in example) is agood solution, or beter todo a inline function which will return me the part of this dataset(probably there is no difference)- will it be much faster, if i could add some fields in toSource_Table, containing also varchar data, but only this part whichI'm interested in and binde these fields in view instead off usingsubstring function?Small example:CREATE TABLE [dbo].[Source_Table] ([CID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,[MSrepl_tran_version] uniqueidentifier ROWGUIDCOL NULL ,[Date_Event] [datetime] NOT NULL ,[mama_id] [varchar] (6) COLLATE Latin1_General_CI_AS NOT NULL ,[mama_type] [varchar] (4) COLLATE Latin1_General_CI_AS NULL ,[tata_id] [varchar] (4) COLLATE Latin1_General_CI_AS NOT NULL ,[tata_type] [varchar] (2) COLLATE Latin1_General_CI_AS NULL ,[loc_id] [nvarchar] (64) COLLATE Latin1_General_CI_AS NOT NULL ,[sn_no] [smallint] NOT NULL ,[tel_type] [smallint] NULL ,[loc_status] [smallint] NULL ,[sq_break] [bit] NULL ,[cmpl_data] [varchar] (100) COLLATE Latin1_General_CI_AS NOT NULL ,[fk_cmpl_erp_data] [numeric](18, 0) NULL ,[erp_dynia] [bigint] NULL) ON [PRIMARY]GOcreate view VIEW_AllDataasselect top 100 percentisnull(substring(RODZ.cmpl_data,27,10),'-') as ASO_NO,(RODZ.mama_type + RODZ.mama_Id) as MAMA,isnull(substring(RODZ.cmpl_data,45,5),'-') as MI,isnull(substring(RODZ.cmpl_data,57,3),'-') as ctl_EC,isnull(substring(RODZ.cmpl_data,60,3),'-') as ctl_IC,RODZ.Date_Event as time_time,RODZ.sn_no as SNFROMSource_Table RODZ with (nolock)goThanks in advanceMateusz
View 6 Replies View RelatedI got a table, and inside column 1, i wish to insert number by increase into every rows untill column 2 has no record, how to do that?
EXPECT OUTPUT EXAMPLE
COLUMN 1 COLUMN 2
-------- --------
10000 ghjgh
10001 asdasd
10002 yhhh
10003 pkoprt
10004 45450
10005 tgtged
10006 retgret
. .
. .
and so on
STOP WHEN NO RECORD
I am updating a db with data from a file, in this data we have new info, info that has been updated and info that is to be removed from the db.
Now I was wondering which approach results in better performance/shorter executin time:
1. first update excisting values, then insert new ones, and last delete cancelled data
or
2. delete cancelled data and data that will be updated, then insert new and updated info
I get all this data from a file, in that file all rows are similar and there is one column that defines if the data is new, updated or to be deleted (thus all the updates also include the information for the enty that has not been altered).
// Pati
Hey there :)Sorry if I'm asking a dumb question here, but I'm still quite new to MS SQL,so this problem might appear larger to me than it really is.I'm trying to create a performance test environment for a Ruby on Rails andMongrel setup with an MS SQL Server 2000.The adapter, mssqlclient, uses some kind of "conversion" for unicode, here'sa quote from the homepage:"Automatically translate from proper UTF-16LE nvarchar fields in thedatabase to UTF-8 Ruby Strings you can display in your application"As far as the local DB designer knows, we're not using UTF16-LE nvarcharfields, unless it's something that happens implicitly.Either way, this is how a query from the mssqlclient adapter might look:SELECT TOP 1 * FROM Item WHERE (Item.Itemnumber = N'45783745')Response time the first couple of times was upwards of 20+ seconds, afterthe sql server has "awaken from its slumber", it's roughly 4 seconds.Omitting the "N" from the WHERE clause, response time is in milliseconds (asone would expect, regardless of the fact that there's currently >2.5million items in the table).Any tips on how to resolve this? Is the SQL statement bad, or is it aquestion of configuring SQL Server correctly?Thanks in advance for any help,Daniel Buus :)--http://www.rhesusb.dk
View 2 Replies View Related
Hi All
I struck up with Slow perfornace query,Please some body help me how to analyze Slow perforamnce queris.
I have a number of complex search stored procedures that use the following syntax to try to simplify the code.
WHERE @SearchParam IS NULL OR SearchCol = @SearchParam
unfortunately it appears that this is really inefficient as far as the database is concerned.
If I run the following query on the AdventureWorks database (SQL Server 2005 with SP2 and fixes up to v3054)
Code Block
SET STATISTICS IO ON
Declare @CustomerID int
SET @CustomerID = 1
SELECT SalesOrderID
FROM Sales.SalesOrderHeader
WHERE @CustomerID IS NULL OR CustomerID = @CustomerID
SELECT SalesOrderID
FROM Sales.SalesOrderHeader
WHERE CustomerID = @CustomerID
Is see that the first select results in 45 logical reads, whereas the second results in only 2 logical reads.
Does anyone have any idea how I can get the benefit of a search procedure that does not have loads of IF blocks, or dynamic SQL without this major performance issue?
Cheers,
Justin
We have an issue where a cube hasn't been designed properly - when someone queries it with Excel, it is doing a mega-crossjoin. When anyone else tries to do *anything* on the AS server (connect with management studio, etc.) it just hangs. We have to either track down the person running the query (via the flight recorder), or restart the service. Obviously the correct fix is to change the design of the cube - I plan on doing it asap. But it brings up this important question - is there a setting I can change to allow others to use the box while this is going on? Maybe some thread isolation, or parallelism? I'm just throwing out ideas, as I haven't experienced this part of AS administration yet.
Thanks in advance,
John
Hello everyone, I am hoping someone can help me with this problem. Iwill say up front that I am not a SQL Server DBA, I am a developer. Ihave an application that sends about 25 simultaneous queries to a SQLServer 2000 Standard Edition SP4 running on Windows 2000 Server with2.5 GB of memory. About 11 of these queries are over views (all overthe same table) and these queries are all done from JDBC but I am notsure that matters. Anyway, initially I had no problem with thesequeries on the tables and the views with about 4 years of information(I don't know how many rows off hand). Then we changed the tables toreplicated tables from another server and that increased the amount ofdata to 15 years worth and also required a simple inner join on 2columns to another table for those views.Now here is the issue. After times of inactivity or other times duringthe day with enough time between my test query run I get what lookslike blocking behavior on the queries to the views (remember these allgo to the same tables). I run my 25 queries and the 11 view queriesall take about 120 seconds each to return (they all are withinmilliseconds of each other like they all sat there and then werereleased for processing at the same time). The rest of the queries arefine. Now if I turn around and immediately run the 25 queries again,they all come back in a few seconds which is the normal amount of time.Also, if I run a query on one of views first (just one) and then runthe 25 queries they all come back in a few seconds as well.This tells me that some caching must be involved since the times are sodifferent between identical queries but I would expect that one of thequeries would cache and thus take longer but the other 10 would befast, not all block for 2 minutes. What is more puzzling is that thisbehavior didn't occur before where now the only differences are:1) 3 times more data (but that shouldn't cause a difference from 3seconds to 120 and all tables have been through the index wizard with aSQL trace file to recommend indexes)2) There is now a join between 2 tables where there wasn't before3) The tables are replicated throughout the day.I would appreciate any insight into this problem as 120 seconds is waytoo long to wait. Thanks in Advance.Chris
View 1 Replies View Related
Hi All
I decided to change over from Microsoft Access Database file to the New SQLServerCe Compact edition. Although the reading of data from the database is greatly improved, the inserting of the new rows is extremely slow.
I was getting between 60 to 70 rows per sec using OLEDB and an Access Database but now only getting 14 to 27 rows per sec using SQLServerCe.
I have tried the below code changes and nothing seams to increase the speed, any help as I would prefer to use SQLServerCe as the database is much smaller and I€™m use to SQL Commands.
Details:
VB2008 Pro
.NET Frameworks 2.0
SQL Compact Edition V3.5
Encryption = Engine Default
Database Size = 128Mb (But needs to be changes to 999Mbs)
Where Backup_On_Next_Run, OverWriteQuick, CompressAns are Booleans, all other column sizes are nvarchar and size 10 to 30 expect for Full Folder Address size 260
TRY1
Direct Insert Using Data Adapter.
Me.BackupDatabaseTableAdapter1.Insert(Group_Name1, Full_Folder_Address1, File1, File_Size_KB1, Schedule_To_Run1, Backup_Time1, Last_Run1, Result1, Last_Modfied1, Last_Modfied1, Backup_On_Next_Run1, Total_Backup_Times1, Server_File_Number1, Server_Number1, File_Break_Down1, No_Of_Servers1, Full_File_Address1, OverWriteQuick, CompressAns)
14 to 20 rows per second (Was 60 to 70 when using OLEDB Access)
TRY 2
Using Record Sets
Private Sub InsertRecordsIntoSQLServerce(ByVal Group_Name1 As String, ByVal Full_Folder_Address1 As String, ByVal File1 As String, ByVal File_Size_KB1 As String, ByVal Schedule_To_Run1 As String, ByVal Backup_Time1 As String, ByVal Last_Run1 As String, ByVal Result1 As String, ByVal Last_Modfied1 As String, ByVal Latest_Modfied1 As String, ByVal Backup_On_Next_Run1 As Boolean, ByVal Total_Backup_Times1 As String, ByVal Server_File_Number1 As String, ByVal Server_Number1 As String, ByVal File_Break_Down1 As String, ByVal No_Of_Servers1 As String, ByVal Full_File_Address1 As String, ByVal OverWriteQuick As Boolean, ByVal CompressAns As Boolean)
Dim conn As SqlCeConnection = Nothing
Dim CommandText1 As String = "INSERT INTO BackupDatabase (Group_Name, Full_Full_Folder_Adress, File1,File_Size_KB, Schedule_To_Run, Backup_Time, Last_Run, Result, Last_Modfied, Latest_Modfied, Backup_On_Next_Run, Total_Backup_Times, Server_File_Number, Server_Number, File_Break_Down, No_Of_Servers, Full_File_Address, OverWrite, Compressed) VALUES ('" & Group_Name1 & "', '" & Full_Folder_Address1 & "', '" & File1 & "', '" & File_Size_KB1 & "', '" & Schedule_To_Run1 & "', '" & Backup_Time1 & "', '" & Last_Run1 & "', '" & Result1 & "', '" & Last_Modfied1 & "', '" & Latest_Modfied1 & "', '" & CStr(Backup_On_Next_Run1) & "', '" & Total_Backup_Times1 & "', '" & Server_File_Number1 & "', '" & Server_Number1 & "', '" & File_Break_Down1 & "', '" & No_Of_Servers1 & "', '" & Full_File_Address1 & "', '" & CStr(OverWriteQuick) & "', '" & CStr(CompressAns) & "')"
Try
conn = New SqlCeConnection(strConn)
conn.Open()
Dim cmd As SqlCeCommand = conn.CreateCommand()
cmd.CommandText = "SELECT * FROM BackupDatabase"
cmd.ExecuteNonQuery()
Dim rs As SqlCeResultSet = cmd.ExecuteResultSet(ResultSetOptions.Updatable Or ResultSetOptions.Scrollable)
Dim rec As SqlCeUpdatableRecord = rs.CreateRecord()
rec.SetString(1, Group_Name1)
rec.SetString(2, Full_Folder_Address1)
rec.SetString(3, File1)
rec.SetSqlString(4, File_Size_KB1)
rec.SetSqlString(5, Schedule_To_Run1)
rec.SetSqlString(6, Backup_Time1)
rec.SetSqlString(7, Last_Run1)
rec.SetSqlString(8, Result1)
rec.SetSqlString(9, Last_Modfied1)
rec.SetSqlString(10, Latest_Modfied1)
rec.SetSqlBoolean(11, Backup_On_Next_Run1)
rec.SetSqlString(12, Total_Backup_Times1)
rec.SetSqlString(13, Server_File_Number1)
rec.SetSqlString(14, Server_Number1)
rec.SetSqlString(15, File_Break_Down1)
rec.SetSqlString(16, No_Of_Servers1)
rec.SetSqlString(17, Full_File_Address1)
rec.SetSqlBoolean(18, OverWriteQuick)
rec.SetSqlBoolean(19, CompressAns)
rs.Insert(rec)
Catch e As Exception
MessageBox.Show(e.Message)
Finally
conn.Close()
End Try
End Sub
€™20 to 24 rows per sec
TRY 3
Using SQL Commands Direct
Private Sub InsertRecordsIntoSQLServerce(ByVal Group_Name1 As String, ByVal Full_Folder_Address1 As String, ByVal File1 As String, ByVal File_Size_KB1 As String, ByVal Schedule_To_Run1 As String, ByVal Backup_Time1 As String, ByVal Last_Run1 As String, ByVal Result1 As String, ByVal Last_Modfied1 As String, ByVal Latest_Modfied1 As String, ByVal Backup_On_Next_Run1 As Boolean, ByVal Total_Backup_Times1 As String, ByVal Server_File_Number1 As String, ByVal Server_Number1 As String, ByVal File_Break_Down1 As String, ByVal No_Of_Servers1 As String, ByVal Full_File_Address1 As String, ByVal OverWriteQuick As Boolean, ByVal CompressAns As Boolean)
Dim conn As SqlCeConnection = Nothing
Dim CommandText1 As String = "INSERT INTO BackupDatabase (Group_Name, Full_Full_Folder_Adress, File1,File_Size_KB, Schedule_To_Run, Backup_Time, Last_Run, Result, Last_Modfied, Latest_Modfied, Backup_On_Next_Run, Total_Backup_Times, Server_File_Number, Server_Number, File_Break_Down, No_Of_Servers, Full_File_Address, OverWrite, Compressed) VALUES ('" & Group_Name1 & "', '" & Full_Folder_Address1 & "', '" & File1 & "', '" & File_Size_KB1 & "', '" & Schedule_To_Run1 & "', '" & Backup_Time1 & "', '" & Last_Run1 & "', '" & Result1 & "', '" & Last_Modfied1 & "', '" & Latest_Modfied1 & "', '" & CStr(Backup_On_Next_Run1) & "', '" & Total_Backup_Times1 & "', '" & Server_File_Number1 & "', '" & Server_Number1 & "', '" & File_Break_Down1 & "', '" & No_Of_Servers1 & "', '" & Full_File_Address1 & "', '" & CStr(OverWriteQuick) & "', '" & CStr(CompressAns) & "')"
Try
conn = New SqlCeConnection(strConn)
conn.Open()
Dim cmd As SqlCeCommand = conn.CreateCommand()
cmd.CommandText = CommandText1
'cmd.CommandText = "INSERT INTO BackupDatabase (€¦"
cmd.ExecuteNonQuery()
Catch e As Exception
MessageBox.Show(e.Message)
Finally
conn.Close()
End Try
End Sub
€˜ 25 to 30 but mostly holds around 27 rows per sec I
Is this the best you can get or is there a better way. Any help would be greatly appericated
Kind Regards
John Galvin
I changed from Access97 to AccessXP and I have immense performanceproblems.Details:- Access XP MDB with Jet 4.0 ( no ADP-Project )- Linked Tables to SQL-Server 2000 over ODBCI used the SQL Profile to watch the T-SQL-Command which Access ( whocreates the commands?) creates and noticed:1) some Jet-SQL commands with JOINS and Where-Statements aretranslated very well, using sp_prepexe and sp_execute, including thesimilar SQL-Statement as in JET.2) other Jet-SQL commands with JOINS and Where-Statements aretranslated very bad, because the Join wasn´t sent as a join, Accesscollects the data of the individual tables seperately.Access sends much to much data over the network, it is a disaster!3) in Access97 the same command was interpreted wellCould it be possible the Access uses a wrong protocol-stack, perhapsJet to OLEDB, OLEDB to ODBC, ODBC to SQL-Server orJet to ODBC, ODBC to OLEDB and OLEDB to SQL-Server instead ofJet to ODBC and ODBC direct to SQL-ServerDoes anyone knows anything about:- Command-Interpreter of JetODBC, Parameters, how to influence thecommand-interpreter- Protocol-Stack of a Jet4.0 / ODBC / SQL-Server applicationThanks , Andreas
View 6 Replies View RelatedIs there any way to use a graphical designer to build your insert & update SQL statements in Enterprise manager? I mean Access has an EASY way to build them, surely SQL does too?
I would just build them in Access and copy the SQL, but then I'm stuck replacing all the "dbo_" with "dbo." and other little nuances.
I have 4 tables which I am extracting 2 distinct values;
Select Distinct UniId, PID from dbo.Event1
Select Distinct UniId, PID from dbo.Event2
Select Distinct UniId, PID from dbo.Event3
Select Distinct UniId, PID from dbo.Event4
Then, I want to select Distinct between these 4 tables (Event1, Event2, Event3 and Event4)
Then insert the distinct records of the 4 tables to the final table - dbo.EventLookup .
In Sql 2005, I can't get this to work. I am getting an error that says SQL does not support subquery. SQL has a problem with my subquery. It works fine with one subquery, but adding multiple gives me an an error.
INSERT INTO STU_SUMMARY
(SUMMARY_PERIOD,
CHILD_COUNT, NO_DATA_ERRORS, PROG_CD_DESCREP, TOT_CURR_SPEDSTU)
VALUES (GETDATE(),
(select count(*) counter from DATA_ERROR_DETAIL),
(select count(*) counter from DATA_ERROR_DETAIL),
(select count(*) counter from DATA_ERROR_DETAIL),
(select count(*) counter from DATA_ERROR_DETAIL),
)
Hi,Ive got a class in my code which contains data from several tables. A user profile has a single record in a user_profile tables, for each of these records there is several records in a another table, which itself has several records in a tags table.So far the save method on the class saves the record to the user profile table, then loops through the next two tables info to insert each record. I want to decrease the number of trips to the database and am wondering if anyone can advise on the best way to do this.I am considering using a dataset to retrieve and store the records, instead of a datareader Im using currently. Then when Im done altering the data in the dataset (all three tables) I can commit all the changes in one database transaction?? I have been avoiding datasets due to the fact datareaders are apparently faster.My other option is to try make three calls to the database, one per table, updating my stored procedures to accept arrays of data using XML?? Can anyone tell me what is the best option for me?Thanks,C
View 5 Replies View RelatedCan someone please help me modify the following pivot query into an INSERT INTO query (i.e. results are exported into a new table)...
SELECT RespondantID, [1] As Q1, [2] As Q2, [3] As Q3, [4] As Q4, [5] As Q5, [6] As Q6, [7] As Q7, [8] As Q8, [9] As Q9, [10]
As Q10 FROM (SELECT RespondantID, QuestionID, Answer FROM [3_Temp]
WHERE SurveyID=1) AS preData PIVOT
( MAX(Answer) FOR QuestionID IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10]) )
AS data
ORDER BY RespondantID
Thanks,
Martin
Hello
I have a question about something odd happening with an insert query.
I am working on a password protected site and I want to moniter general student use. I have an on page load even which picks up their user id and the url and sends it to the database
Because my site is a beta site, I also want the users to be able to add their comments as and when they want, so I built a small feedback form on each page.
The quick feedback form has a panel to hide it until a user clicks on a radio button and this reveals a text box and a submit button.
However, since I have added this second sql connection with an insert, I now get an error message I was getting a connection string error when I tried to point at the connection string in the web.config file, so I put the full path in to the database and now I get an error message that says the conTrackUser has not been declared, but as you can see from the code it certainly has. So I'm beginning to thing that the error is something entierly different, but that's probably me being thick.
I've pasted the two insert blocks below, I can't see anything wrong with them, but then I'm mostly groping about in the dark with asp.net.
Is it not possible to have two insert commands on a page? That would be daft.
Here is the code.
Track and initilize the radio button and panel for the feedback:
Sub Page_Load(Sender As Object, E As EventArgs)
Dim UserID as String = session("UserID")
pnlQuickFeedback.Visible = False
If Not Page.IsPostback Then
Dim conTrackUser As SqlConnection
Dim strInsert As String
Dim cmdInsert As SqlCommand
conTrackUser = New SqlConnection("server='server'; user id='me'; password='nuts'; database='database'")
cmdInsert = New SqlCommand("TrackUser5530", conTrackUser)
cmdInsert.CommandType = CommandType.StoredProcedure
cmdInsert.Parameters.Add("@UserIdentity", session("UserID") )
cmdInsert.Parameters.Add("@URL", Request.Url.AbsoluteUri )
conTrackUser.Open()
cmdInsert.ExecuteNonQuery()
conTrackUser.Close()
End If
End Sub
The sub for the feedback:
Sub SubmitFbbtn_Click(Sender As Object, E As EventArgs)
Dim UserID as String = session("UserID")
'If Not Page.IsPostback Then
Dim conQuickFeedback As SqlConnection
Dim strInsert As String
Dim cmdInsert As SqlCommand
conQuickFeedback = New SqlConnection("server='server'; user id='me'; password='nuts'; database='database'")
cmdInsert = New SqlCommand("QuickComments5530", conQuickFeedback)
cmdInsert.CommandType = CommandType.StoredProcedure
cmdInsert.Parameters.Add("@UserIdentity", session("UserID") )
cmdInsert.Parameters.Add("@Comments", txtComments.text)
cmdInsert.Parameters.Add("@URL", Request.Url.AbsoluteUri )
conQuickFeedback.Open()
cmdInsert.ExecuteNonQuery()
conQuickFeedback.Close()
pnlQuickFeedback.Visible = False
feedbackFormbtn.Visible = True
feedbackFormbtn.Checked = False
End Sub
thanks
I am trying to insert the queries in a table such as the following, not able to insert it.
create table test (txt Varchar(200))
insert into test values('select *from master.dbo.sysprocesses WHERE status NOT IN('sleeping','background')')
I have a SQL stored procedure like so:
CREATE PROCEDURE sp_PRO
@cat_num nvarchar (10) ,
@descr nvarchar (200) ,
@price DECIMAL(12,2) ,
@products_ID bigint
AS
insert into product_items (cat_num, descr, price, products_ID) values (@cat_num, @descr, @price, @products_ID)
when I try and insert something like sp_PRO '123154', 'it's good', '23.23', 1
I can't insert "'" and "," because that is specific to how each item is delimited inorder to insert into the stored procedure. But if I hard code this into a aspx page and don't create a stored procedure I can insert "'" and ",". I have a scenario where I have to use a stored procedure...confused.
I am using a sql server 2000 database to log the results from a monitorthat I have running - essentially every minuite, the table describedbelow has a insert and delete statements similar to the ones below runagaint it.Everything is fine for a few weeks, and then without fail, all accessesto the table start slowing down, to the point where even trying toselect all rows starts timing out.At that point, the only way to make things right that I have found, isto delete the table and recreate it.Am I doing something specific that sql server really doesn't like? Isthere a better solution then deleting and recreating the table?CREATE TABLE [www2] ([ID] [int] IDENTITY (1, 1) NOT NULL ,[stamp] [datetime] NULL CONSTRAINT [DF_www2_stamp] DEFAULT (getdate()),[success] [bit] NULL ,[report] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[level] [int] NULL ,[iistrace] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOINSERT INTO [www2] ([Report],[Success],[Level],[iistrace],[Stamp])VALUES ('Error on: <ahref="http://www2.klickit.com/include/asp/system_test.asp">http://www2.klickit.com/include/asp/system_test.asp</a><br><br>The operation timedout<br><br>(Test Activated From: Lynx/2.8.2rel.1libwww-FM/2.14)',0,1,'',getDate())DELETE FROM [www2] WHERE (Stamp<getDate()-3) AND (Success=1) AND (ReportNot Like 'ResetThanks in advance,Simon Withers*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View RelatedI'm writing an application for Windows Mobile 5 / Pocket PC using VB.NET 2005. The database is connected using an instance of SqlCeConnection and updated by an SqlCeCommand.
The application can perform select queries on data originally entered into the database through Visual Studio, or perform update / insert queries at run time. Anything inserted or updated can be returned by a select query whilst the application is running, however, anything I have inserted or updated doesn't appear to be written to the SDF file and hence is not in the database after restarting the application.
Am I missing something that's different between performing queries on an SQL CE database on Pocket PC and an ODBC source in a normal Windows application?
Hey,
I'm writing an application where I have to perform a lot of inserts/updates on a SQL Server Compact Edt. 3.1 database (up to 50.000 or even 100.000 consecutive inserts).
I'm using native C++ and OleDB. I set the command text only once for each table and I increased maximum buffer size (DBPROP_SSCE_MAXBUFFERSIZE). There are also no table indices or foreign keys.
The performance is quite well, but not good enough. I took a closer look at the MS Merge Replication and I observed a really good insert performance.
Maybe somebody can tell me some ''secrets'' how I can speed up my query performance!?
best regards
Tom
Hi,
I have a database that holds an information about customers, each customer's data is holded in many referenced tables. In one stored procedure i should insert/update many customers. I have 2 options: bulk insert of all customers data in one transaction or insert each customer in separate transaction. My question is: what approach is considered to be correct, and gives better performance assuming that the number of inserted/updated customers is very large? Thank you
Hi,Any suggestions on the following as I've kind of run out of ideas.I have 2 servers which are the same spec ie box, processor etc. Theonly difference I can tell is that the production box has raid setupbut the test box hasn't (I think).I have created a stored procedure to insert 10k rows into a dummy tablewith two columns.I have logged onto the boxes directly so there are no networks issueshere. Also the boxes only have light traffic on them really, thereisn't much going on at the point of running.The production box inserts the rows two times faster than the test boxi.e 30 secs rather than 1 min. Does anyone have any idea why this couldbe, do you think it could be raid?The prod box has 8 disks I think in hardware Raid 5 but as the test boxhas 4 disks and it looks as if all the space is available i doubt raidis being employed.ThanksIan.ps. does anyone know if there is a way to check the raid configurationof a box from within windows? or do you have to re-boot and go throughthe setup?
View 4 Replies View RelatedHi,
I have a insert into statement in sp1 which executes sp2.sp2 retuns around 2000 rows after lot of processing.When I execute sp2 directly from query analyser it takes about 2-3 mins but if I do the
insert into tab1
exec sp2
It takes 20 mins.
what are my other options, how can i improve the performance?
Thanks,
ssm
I have a situation where I need to do multiple inserts into the sql mobile db at one time. I am wondering what would be the most efficient method to do this. Right now I am just doing many inserts, but the performance is lacking. I tried to wrap all the inserts into 1 sql command and process it like that, but it does not seem to want to execute. Any help would be appreciated.
View 13 Replies View RelatedHi,
I'm using version 3.5.5386.0 of SqlServerCompact Edition for windows mobile.
My first tests indicates that an insert statement with '?' ist 20 % faster than with '@p1' parameters:
"INSERT INTO Itest(PKey,value1,value2,value3,Date1) VALUES (?,?,?,?,?)"
"INSERT INTO Itest(PKey,value1,value2,value3,Date1) VALUES (@P1,@2,@3,@P4,@P5)"
I've no explanation for that. I reported some problems with the '?' parameters in my other task http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2715685&SiteID=1
So I'm a little bit afraid to use this version.
Is there a recommandation which version to use for Inserts?
Thanks,
Stefan
Hi There
I have a table lets call it TABLE_A that has +- 100 million rows , obviously inserts into this table take some time as it has 1 clustered and 3 non clustered indexes.
I have another table lets call it TABLE_B, it is identical to TABLE_A and it holds 100,000 rows that must be inserted into TABLE_A.
As you can imagine a : INSERT INTO TABLE_A select * from TABLE_B takes alot of time.
What is the best way to speed this up? (Dopping indexes in not an option).
I know bulk insert gives the best performance, but can you bulk insert between tables ? Bulk insert in from a flat file source.
It seems redundant to write an ssis package to extract the data out of TABLE_B to file simply to bulk insert in back into the database?
So in a nutshell what is the fastest way to get the rows from TABLE_B in TABLE_A?
Thanx
Currently we import some pretty large files (sometimes >1gb) using DTS into a staging table then into the active table after some processing every night. The DTS stuff (before my time) looks pretty messy when all is said and done compared to a BULK INSERT TSQL statement.
My thought is to switch to a stored procedure using BULK INSERT because it will be easier for others to maintain after me. My question is with performance. As I mentioned, our imported files can be extremely large at times and I don't want to sacrifice too much performance for readability sake. What can I do to optimize a BULK INSERT TSQL statement and how does it stand up to similar actions using DTS?
hi,
I came across the following topic which speaks about inserting multiple rows into a table.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=52980
The following is the code it concludes:
----------------------------------------------------------
INSERT INTO InstitutionManagement.dbo.b10_partC
(unitid, repyear, test, subject_area, scores,
credit_awarded, comments, recno)
select
unitid = 'data1a',
repyear = 'data1b',
test = 'data1c',
subject_area = 'data1d',
scores = 'data1e',
credit_awarded = 'data1f',
comments = 'data1g',
recno = 'data1h'
union all
select
unitid = 'data2a',
repyear = 'data2b',
test = 'data2c',
subject_area = 'data2d',
scores = 'data2e',
credit_awarded = 'data2f',
comments = 'data2g',
recno = 'data2h'
union all
select
unitid = 'data3a',
repyear = 'data3b',
test = 'data3c',
subject_area = 'data3d',
scores = 'data3e',
credit_awarded = 'data3f',
comments = 'data3g',
recno = 'data3h'
... And so on...
----------------------------------------------------------
My question is based on the performance of the above insert statement aganist Microsoft.Net SqlBulkCopy Class.
One more thing: Does the above statement gets executed as asingle statement or as multiple statements (One execution for each Select statement).
thanks
regards,
vinay
The following code should insert into 3 tables based on conditions. There's something screwy in my syntax and I'm pretty new at this can anyone help with transforming this in terms of performance and being syntactically correct? Thanks a million!
CREATE PROCEDURE [insert_vwMusic]
(@Artist [nvarchar](50),
@Genre [nvarchar](50),
@NLink [nvarchar](50),
@Album[nvarchar](50),
@Song[nvarchar](50),
@ArtistID[nvarchar](50),
@AlbumID[nvarchar](50),
@SLink[nvarchar](50))
AS
DECLARE @NewArtistID VarChar(50),
DECLARE @NewAlbumID VarChar(50)
IF Not Exists (SELECT [Artist] FROM [integration].[dbo].[tblMusic_Artist] WHERE [Artist] = @Artist)
BEGIN
INSERT INTO [integration].[dbo].[tblMusic_Artist]
( [Artist],
[Genre],
[NLink])
VALUES
( @Artist,
@Genre,
@NLink)
SET @NewArtistID = @@IDENTITY
INSERT INTO [integration].[dbo].[tblMusic_Albums]
( [Album]
VALUES
( @Album)
SET @NewAlbumID = @@IDENTITY
INSERT INTO [integration].[dbo].[tblMusic_Song]
( [Song],
[ArtistID],
[AlbumID],
[SLink])
VALUES
( @Song,
@NewArtistID,
@NewAlbumID,
@SLink)
END
ELSE
BEGIN
IF Not Exists (SELECT [Album] FROM [integration].[dbo].[tblMusic_Album] WHERE [Album] = @Album)
BEGIN
INSERT INTO [integration].[dbo].[tblMusic_Albums]
( [Album]
VALUES
( @Album)
SET @NewAlbumID = @@IDENTITY
SET @NewArtistID = (SELECT [ID] FROM [integration].[dbo].[tblMusic_Artist] WHERE [Artist] = @Artist)
INSERT INTO [integration].[dbo].[tblMusic_Song]
( [Song],
[ArtistID],
[AlbumID],
[SLink])
VALUES
( @Song,
@NewArtistID,
@NewAlbumID,
@SLink)
END
END
ELSE
BEGIN
SET @NewAlbumID = (SELECT [ID] FROM [integration].[dbo].[tblMusic_Album] WHERE [Album] = @Album)
SET @NewArtistID = (SELECT [ID] FROM [integration].[dbo].[tblMusic_Artist] WHERE [Artist] = @Artist)
INSERT INTO [integration].[dbo].[tblMusic_Song]
( [Song],
[ArtistID],
[AlbumID],
[SLink])
VALUES
( @Song,
@NewArtistID,
@NewAlbumID,
@SLink)
END
GO
Hi,
I'm testing Mirroing.
1) I have dedicated NIC for Mirroring - 100Mb
There is no issue with the network (file of 25MB goes in 2.5 seconds)
2) I'm issuing the next simple command
Select * into dbo.Table2 from dbo.Table1
3) the size of the table is 25MB
in async mode it takes 3-4 sec (as if it runs only local)
in sync mode it takes 25-29 sec !!!!
WHY ? IS THIS NORMAL ?
is there any configuration i can change ?