Find The Best Approch To Insert Data In Sql Server Database Based On Perfrmance Metrics
Nov 5, 2007
I have two VB.NET dataclient program A abd B.
What i want to do is .. to find the best way to insert the high speed data( that comes at every 10 ms) to the sql server express database table .
I have two options to store the data in the table . One is to insert all the data in one row , the other is to divide it into many rows in the same table . In approach A i will get like 10 records per second and in approach B i may get i may get 30 - 50 records per second based on the amount of data that is comming . i.e i am creating a new row in the table for every extra column added in Approach A and duplication the other columns.
I wanted to find the better way to insert the data based on the performace metrics like CPU usage and memory usage.
I'm in the middle of developing a Database for a hospital that measures its audits, inhouse operations, and finance. What we currently have and do everyday is collect data from a large database that is real time with patient data, progess, infomation, etc and dump it into a data warehouse that runs on TSI/Eclipsys. We run reports using a number of programs and dump it into Excel sheets that have charts, reports, etc. This Database for which I'm developing won't come soley from the TSI/Eclypsys source, but this is the only source thats updated regularly. I don't want to have in sync with TSI/Eclipysys in fear that every day when it updates data may be lost, not read, or worse won't be up date if there is a problem. My question is is it possible to run a query from Sequel Server 2005 that will take that data upon request using the reporing features on Sequel Server 2005. i.e. What if I need to run a report on measure B in department 12 from Jan 1-Feb 1, instead of being in sync, can I just write queries to take that information rather than double the data and take up twice the space and trouble. FYI, these datatypes rarely change in the TSI/Eclipsys data warehouse. This sure was long question and didn't intended it to be . Thanks for listening and hope to hear back.
hii am using vs2005 for development of web application for reporting with sqlexpress05 as back end .later when project is ready for deployment i have to deploy the project on remote hosting server where i have limited access and sqlserver2000 database to use.i want to ask is there are any limitation or problem of sqlexpress while deploying it on remote sqlservre 2000.and should i have to to continue with sqlexpress as back end.is there any problems for using dynamic database connections(by using smart tags) other than programaticaly connecting database to asp.net ie by writing code.i am new in developmentplease guide me, please guide
I've never worked with a column that's defined as a binary data type. (In the past if ever we had to include a photo we stored a link to the JPG/BMP/PNG/whatever into a column, but never actually inserted or updated a column with binary data.But now I've got to do that. So how do you put data into a column defined as BINARY(4096)?
I am writing a query to return some production data. Basically i need to insert either 1 or 2 rows into a Table variable based on a decision as to does the production part make 1 or 2 items ( The Raw data does not allow for this it comes from a look up in my database)
I can retrieve all the source data i need easily but when i come to insert it into the table variable i need to insert 1 record if its a single part or 2 records if its a twin part. I know could use a cursor but im sure there has to be an easier way !
Below is the code i have at the moment
declare @startdate as datetime declare @enddate as datetime declare @Line as Integer DECLARE @count INT
set @startdate = '2015-01-01' set @enddate = '2015-01-31'
Do you have any idea if there is / where can I find web based data entry into SQL 7.0 database. What I'm looking for is something like Oracle Forms but for SQL 7.0.
Hello. I have a little issue of trying to find the most efficient way do doing something. Consider a table mat age qty aaa 5 100 aaa 7 300 aaa 12 400 aaa 3 700
What I want to get out is Mat 0-4 5-9 10-14 15-19 aaa 700 400 400 0
I know I could do it with 4 views all joined but I hope I can use a CASE somehow ?
Can any one point out a whitepaper/article or something which describes in detail the metrics in SSIS related to CPU usuage, Memory usage, page faults etc depending on the volume of load. We are planning to use SSIS for ETL purposes and DB size is around 3 TB. Plan is to have ssis packages continously run E-T-L...so wanted to understand how SSIS would behave.
I have a table with PO#,Days_to_travel, and Days_warehouse fields. I take the distinct Days_in_warehouse values in the table and insert them into a temp table. I want a script that will insert all of the values in the Days_in_warehouse field from the temp table into the Days_in_warehouse_batch row in table 1 by PO# duplicating the PO records until all of the POs have a record per distinct value.
Example:
Temp table: (Contains only one field with all distinct values in table 1)
I need to create a function that replaces the data in a column with an 'X' based on the LEN of the data in the column. I created one that does a replacement, but it fills the column based on the max data length, and not the current length of the string or integer. An example of what I'm trying to accomplish.
Original data in a varchar(30) column: thisisavalue thisisanothervalue thisisanothervalueagain shortval
replaced with xxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxx xxxxxxx
My current function is replacing the data like this: xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I need to periodically import a (HUGE) table of data from an external data source (not SQL Server) into SQL Server, with the following scenarios: Some of the records in the external data source may not exist in SQL.Some of the records in the external data source may have a different value at different imports, but this records are identified univocally by the same primary key in the external datasource and in SQL Server.Some of the records in the external data source may be the same in SQL.
Due to the massive volume of the import, I would like to import only the records which are different from what I have in SQL Server (cases 1 and 2 above). In fact case 2 is the most critical.
I thought of making a query with a left outer join between the data in the external data source table (SOURCE) and the data in the SQL Server table (DESTIN). The join is done on the respective primary keys (composed keys of up to 10 columns) and one of the WHERE conditions will be that the value in SOURCE is different from the value in DESTIN.
The result of this query would be exactly what I need to import. How to do this in SSIS??? I couldn't figure out how to join tables in different data sources yet.
In fact I cannot write a stored procedure to do that, since one of the sources is in a datasources not SQL Server. I have seen the Lookup transformation in this article http://www.sqlis.com/default.aspx?311 but this is not exacltly what I want to do. Another possibility is to use the merge join, but due to the sorting I believe its performances would be terrible!
So I know that each employee should have 2 Type 1's and 4 Type 2's. I hope that makes sense, I'm trying to change my data because ours is very proprietary.
I need to identify employees who do not have all their stages and list the stages they are missing. The final report should only have employees and the associated missing types and stages.
I do a count by employee to see how many types they have to identify the ones that don't have all the types and stages.
My count would look something like this:
EmployeeNumber Type Total 100, 1, 2 100, 2, 2 200, 1, 1 200 1, 2
So I know that employee 100 should have 2 more Type 2's and employee 200 should have 1 more Type 1 and 2 more Type 2's based on the required list.
The problem I'm having is taking that required list and joining to my list of employees with missing data and pulling from it the types and stages that are missing by employee. I thought I could get a list of the employees that are missing information and right join it to the required list where the missing records would be nulls. But, that doesn't work because some employees do have the required information and so I'm not getting any nulls returned.
I've got an order generation query that will _sometimes_ run long. By that I mean every few days a query used to estimate the required stock quantities for an order will time-out because it has taken an extended time to run. This only occurs at one particular site. This site shares a common schema with others who do not experience this issue, although site is a particularly busy one.
This query (a stored proc call) needs to aggregate sales data from ~200k rows and usually takes 10-15 seconds. On the days when it 'runs long' it will hit the 5 minute time-out period and be failed by the application.
The problem I have with this process is that if I have the application dump out the query it's using to generate the order (this query will vary as the stored proc is passed the name of a temp table to use for this order) so I can run it in Query Analyzer to get an execution plan and maybe some clue as to why it takes so long; the issue will vanish. After I've tried to investigate in this way an order generated from either Query Analyzer or the application will work in the normal time. When the query is running long any processes which attempts to write to the sales data tables will get blocked until the order generation has finished.
The application is accessing the database via ADO 2.6. The database itself is running on SQL Server 2000 sp3 under Win2k.
I believe that the SQL Profiler is the tool best suited to investigate this but I'm hoping for some advice on which of the myriad of watchable metrics I should be checking here.
Any profiler advice or estimates as to what the issue may be here would be great!
I am trying to perform a performance audit on a SQL Server where I am positive my most likely bottle neck is the disks. I assume the primary metrics I should be interested in are %Disk Activity and Disk Queue Length, right?
What are the actually guidelines for these metrics? Also, Are there other perf mons I should be running against the disks?
It looks like these options are only available in the SQL Server Management Studio? I installed SQL Server Management Express Studio and I can't even find the DTSWizard.exe on my machine.
Can you please help how I can import data from excel or where can I download the SQL Server Management Studio?
I have two SQL Express database and I want to do two things. One is to transfer a table over to the other database. Two, move the files from one table in one database to another. Please let me know when you get a chance.
I have an excel sheet containing one column (ID_NO) with 400K rows. I have a database from where I have to fetch some other columns from a Netezza database. Initially I tried hardcoding all the 400K rows in the query that I wrote using filter WHERE ID IN ('1212','2334'). But after pasting all the 400K rows the query is running indefinitely.
I have imported all the ID in a SQL table (MY_LIST table). I used a DFT, and selected ODBC source, and selected my netezza server. Then in the 'Data access mode' I selected the SQL command from the dropdown.I pasted the same query that I wrote in Netezza. Is there any way to pull only for those records that I have pulled in my SQL table (MY_LIST) ?
Hi, I installed already vs 2005. The installation installed automaticaly SQL Server 2005 express. Now , I want to see which databases I have there and tables,views,store procedure etc. I tried the server explorer in the vs but I don't see any database on my pc. I tried also to open somthing like enterprise manager like I have in sql server 2000 but I don't found such a tool. I tried to find it in START=>Programs=>SQL Server 2005 but their I see only the Configuration tool. I want to know if the installation of the vs 2005 does not install Databases for the sql server Express ? Should I have to download this database samples? What about a tool to view those database like the enterprise manager in sql server 2000 ? Thanks, David
i have a table of phonecall records detailing amongst other things the time and duration of the call. there is a relationship linking this table to a table of handsets and from there to a table of users. this users table lists the start date the user had the phone. as ever sample data might make this easier to show:
PhoneUsers table
PhoneUserID Name StartDate PhoneID
1 john 1/08/2006 1
2 bob 20/08/2006 1
3 fred 2/08/2006 2
etc
Phones table:
PhoneID
1
2
etc
PhoneCalls table:
PhoneCallID PhoneID CallDate Duration
1 1 10/08/2006 25
2 1 23/082006 20
3 2 23/08/2006 20
i want the following result set:
user calldate duration
john 10/08/2006 25
bob 23/08/2006 20
fred 23/08/2006 20
essentially i am trying to link the phonecalls to the user of the phone at the time of the call. i make the following assumptions regarding the data:
the user is responsible for all calls for a given handset from the 'startdate' until the next 'startdate' for a different user, if such a record exists, if not then for all calls after that date
I would like to transfer selected data from an ODBC-based table to a OLEDB-based table. However, there isn't a data flow source on the Data Flow Design screen to accomodate such an action. Please help!
What is wrong with this code the dropdowlist mainlyusing System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using System.Data.SqlClient;public partial class NewAccount : System.Web.UI.Page{ protected void Page_Load(object sender, EventArgs e) { } protected void NaccountButton_Click(object sender, EventArgs e) { if (Page.IsValid) { //Define data objects SqlConnection conn; SqlCommand comm; //read from web config string connectionString = ConfigurationManager.ConnectionStrings["OneBank"].ConnectionString; //Initialize connection conn = new SqlConnection(connectionString); //create command comm =new SqlCommand( "INSERT INTO Customer (FirstName, LastName, Street, City, State," + "Zip, Phone, Payee,AccountType)" + "VALUES(FirstName, LastName, Street, City," + "State, Zip, Phone, Payee,AccountType)", conn); //add parameters comm.Parameters.Add("@FirstName", System.Data.SqlDbType.NVarChar, 50); comm.Parameters["@FirstName"].Value=Firstname.Text; comm.Parameters.Add("@LastName", System.Data.SqlDbType.NVarChar, 50); comm.Parameters["@LastName"].Value=lastname.Text; comm.Parameters.Add("@Street", System.Data.SqlDbType.NVarChar, 50); comm.Parameters["@Street"].Value=street.Text; comm.Parameters.Add("@City", System.Data.SqlDbType.NVarChar, 50); comm.Parameters["@City"].Value=city.Text; comm.Parameters.Add("@State", System.Data.SqlDbType.NVarChar, 50); comm.Parameters["@State"].Value=state.Text; comm.Parameters.Add("@Phone", System.Data.SqlDbType.Int); comm.Parameters["@Phone"].Value=phone.Text; comm.Parameters.Add("@AccountType", System.Data.SqlDbType.NVarChar, 50); comm.Parameters["AccountType"].Value = dropdownlist1.SelectedValue.ToString(); //Enclose database in try catc finally try { //open connection conn.Open(); //execute the command comm.ExecuteNonQuery(); //reload query Response.Redirect("NewAccount.aspx"); } catch { //Display error Errormessage.Text= "Error submitting request!"; } finally { conn.Close(); } } }}
This is the table where thisis going customer ------------- customerID pk generated automatically firtsname lastname street city statezipphoneaccountType
I have a table changereport and network info.i insert a record with date of insertion plus chagereport id.then I have a xml file <ChangeReport> <Network-Info> <Version> 2.0</Version> <Highest-Ver> 2.0</Highest-Ver> <Description> WinSock 2.0</Description> <System-Status> Running</System-Status> <Max> 2.0</Max> <IP-address> 192.168.142.1</IP-address> <Domain-Name> samin</Domain-Name> <UDP-Max> 2.0</UDP-Max> <Computer-Name> SAMIN</Computer-Name> <User-Name> samin</User-Name> </Network-Info></ChangeReport> I want's to insert a record in network info table where field name are nodes name data should be the one against those nodes in xml file .How can I do it I am using sql server
What have I done wrong with this script?I'm getting an error The ConnectionString property has not been initialized.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.InvalidOperationException: The ConnectionString property has not been initialized.
hi It seems that my code can insert data into memory, but not into the database. What I mean is that after "insert data", I can "read data", which I just insert. When I check the actual database table, it didn't get updated.
I am using VS 2005 and table designer. Regarding to this problem, is it related to any setting of setup of the database? I check the code, and I have no idea how it occurs.
