High CPU Performance Issue With BIGINT Column Comparison SQL Code
Dec 11, 2007
I recently converted a column that was once an int to an bigint on one of my tables. The modified column provided a generic row id information and there are duplicates within this column. I am trying to perform a self join via the following:
SELECT a.row_id FROM test_db a INNER JOIN test_db b ON b.row_id < a.row_id.
This code use to work when the column was an int but now I am getting high CPU issues since I converted to bigint. I am unsure on why the change to bigint will cause such an issue. The OS/SQL is 64BIT.
There are so many ways to use database in asp.net/ado.net, I'm a bit confused about their difference from the performance point of view.So apparently SqlDataSource in DataReader mode is faster than DataSet mode, at a cost of losing some bolt-on builtin functions.What about SqlDataSource in DataReader mode vs manual binding in code? Say creating a SqlDataSource ds1 and set "DataSourceID" in Gridview, vs manually creating the SqlConnection, SqlCommand, SqlDataReader objects and mannually bind the myReader object to the gridview with the Bind() method.Also Gridview is a very convenient control for many basic tasks. But for more complex scenarios it requires lots of customization and modification. Now if I do not use gridview at all and build the entire thing from scratch with basic web controls such as table and label controls, and mannually read and display everything from a DataReader object, how's the performance would be like compared to the Gridview-databind route?
I realise this is a stupid quesiton but i cannot really find any confirmation of this in BOL.
If you are running High Safety with automatic failover, when failover occurs does this automatically change to High Performance mode. SInce for failover to occur something has happen with the primary , it will be impossible to commit transactions on the new primary and mirror asyncronously since 1 of them is no longer available.
So am i correct in assuming that automatic failover also automatically changes the mode to High Performacne for that session?
Any idea on performance comparison between VARCHAR and CLOB? For example, if I want to insert or update 4000 characters in a field of a row and I am confused whether that column's type would better be decalred as VARCHAR or CLOB, what do you suggest?
Ive got an ETL process I have written which takes about 10 million rows from a staging database and loads it into production database with an INSERT statement. The INSERT statement makes a function call to retrieve the surrogate key for each row. The function looks in a replicated copy of our production database so no load is on our production environment during this time.
So: INSERT INTO foo(...) SELECT name, address, zip, dbo.fnGetSurrKey( name, address)
It took about 12hrs to insert 6 million rows last night and Im wondering if there is a better way of doing this. Maybe a multithreaded way like SSIS might have.
Assuming my function is optimized as much as possible, does anyone have any tips for speeding this up?
Also, the machine this ran on has 16gb of RAM but was setup to use only 2GB during this process. I have already changed it to 12gb and restarted the process a week ago, but the change doesnt take affect until you reboot. Would I see a significant performance increase from that?
Hello ,i am a master student and i am making a seminar about high volume DB performance problems ,example : if i have a table with length of 1000000 record and this length is growing exponentially by the time,what the problems may i face in insertion ,deletion , search,in such table?? and what the problems in processing such DB in general
I posted a link to a prior article in here, that one about highperformance hierarchies, and have the first two parts of a new series.Hopefully this is of value to someone.http://www.yafla.com/papers/SQL_Ser..._sql_server.htmThanks.
I am looking to improve the performance of my sql server databases.
I currently have a dual location system, the database server setup is basically a quad xeon with 4gb at my office and a double xeon with 4gb at a remote webhosting location. There are separate application/web/intranet servers at each site. The two databases servers are replicated with the local server publishing to the remote server.
The relational database holds circa 26 million records, growing by a volume of 10,000 per day, there are approximately 50,000 queries performed per day.
My theory is that the replication of the two databases is causing a slowdown; despite fast network connections (averaging 200ms between servers) the replication seems to place a large load on the local server. Would it be sensible to replicate to a second local server and then replicate to the remote server, placing any burden on the second server?
I am planning to upgrade the local server to a high capacity 4+ cpu 64bit server, my problem is that although I have noticed a slow down in performance over time, I am unsure how to go about measuring and quantifying this in order to diagnose the bottlenecks and ensure that investing in a new server would be worthwhile. Where would one be best advised to start this project?
I have a database that has a tble with a field that autoincrements as a primary key. meanig that the field type is BigInteger and it is set up as my Identity Column. Now when I insert a new record that field gets updated automaticly. How can I get this value in the same operation as my insert? meaning, in 1 sub, I insert a new record but then need to retieve the Identity Value. All in the same procedure. Waht is the way to achive this please? Marc
SELECT uri, evFieldUri, evFieldVal , CAST(evFieldVal AS BIGINT) FROM TSEXFIELDV
[Code] ....
And it returns this error:
Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to bigint.
So, I tried again, and this worked…
SELECT uri, evFieldUri, evFieldVal,CAST(evFieldVal AS BIGINT), ISNUMERIC(evFieldVal) FROM TSEXFIELDV WHERE URI > 0 AND evFieldUri IN ( SELECT URI FROM TSEXFIELD WHERE exFieldFormat IN (1,11))
I logged out and came back and tried again, and it still worked. So then I tried…
SELECT uri, evFieldUri, evFieldVal,CAST(evFieldVal AS BIGINT) FROM TSEXFIELDV WHERE URI > 0
UPDATE P SET P.IsError=1 ,P.IsDrawingRevNo=1 ,ErrorMessage=ISNULL(ErrorMessage,'')+'| DrawingRevisionNumber DataType Is Not Valid, smallint expected(-32768 AND 32767)' FROM ZPTSMGR.ProjectDrawingRaw P WHERE P.LogId=@LogId AND P.ProjectId=@ProjectId AND P.Revision > 32767 (P.Revision NOT BETWEEN -32768 AND 32767) --SMALLINT RANGE -32768 to 32767.
--DataType Range --tinyint DataType (MinVal: 0, MaxVal: 255). Its storage size is 1 byte. --smallint DataType from -2^15 (-32,768) through 2^15 - 1 (32,767) and its storage size is 2 bytes. --int DataType -2^31(-2,147,483,648) to 2 ^31-1(2,147,483,647). Its storage size is 4 bytes. --Bigint DataType -- from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807). Its storage size is 8 bytes.
The SQl statement fails, and not able to update it. The IsError flag need to set since the value does not lies in given range of smallint.--------say 457896523 which is not a small int value
We recently implemented merge replication.We were expereincing. The replication is between 2 SQL Servers (2005) over same network box, and since we have introduced the replication, the performance has degraded considerably on subscriber end.
1) One thing that should be mention is that its a "unidirectional Direction" flow of changes is from publisher towards subscriber (only one publisher and distributor as well and one subscriber ).
2) Updates are high than inserts and only one article let say "Article1" ave update up to 2000 per day and i am experiecing that dbo.MSmerge_upd_sp_Article1_GUID taking more cpu time.what should be do..
on subscriber database response time is going to slow and i am experiencing a lot of number of LOCK time outs on application end.
can any one can also suggest me server level settings for aviding locking time out.
I'm in need of a sql query that I'm not sure is possible. Here is anexample of how it's laid out.employee ID Job class Last Change Date12345 x 2/1/200412345 y 1/15/200412345 z 1/1/2004We know that this person is in job class 'x' because it's the mostrecent change. Is there a way to write a query that will exclude thelines 'y' and 'z' because they are currently incorrect?I would appreciate any help I could get. Thanks
I am importing States into a table and need to change all NULL fields before I perform a lookup. I'm using a derived column to replace the state value. I'd like to find all States that are blank and set it to "--".
I am no expert in sql, but I keep stubbling on this problem:I have a table t1 with 2 columns (a,b)I have a table t2 with 2 columns (c,d)I need to delete all records from t1 which have the same value (a,b)than the value of (c,d) in all records in the t2 table.I oracle, this is simple:delete from t1where (a,b) in (select c,d from t2)because Oracle has support for this syntax. Dont remember how they callit. But this is not support in sql server. So I have to resort to:delete from t1where a + '+' + b in ( select c + '+' + d from t2)Of course, a,b,c,d must be varchar for this to work. Basically I fake aunique key for the records. Is there a better way to do this?Thanks
Is there a transform available which allows you to specify two different tables (same primary key) and compare columns (you identify which column(s) values need to be compared in the transform) between those two tables?
I upload data from a Txt File(Txt_Temp) where I have VinNumber with 6 digits. Another table name Resrve_Temp1 where I have Vinumber with 17 digit. Now I need to update the vinnumber 6 digit to 17 digit or to new column in Txt_temp.
Txt_Temp - Table
I tried this code with no succes and only one row is updating
update Txt_Temp Set Txt_Temp.Vinnumber=dbo.R_ResrvStock.Vin from dbo.R_ResrvStock inner join Txt_Temp on Right (dbo.R_ResrvStock.Vin,6)=Txt_Temp.VinNumber
OR Add this code in view
Select dbo.R_ResrvStock.Vin,R_Txt_Temp.Vinnumber,R_Txt_Te mp.Model_Code from dbo.R_ResrvStock inner join R_Txt_Temp on Right (dbo.R_ResrvStock.Vin,6)=R_Txt_Temp.VinNumber
Is there a way to configure mirroring to go from High Availability to High Protection without having to reconfigure Database Mirroring? Using the interface in Management Studio, I can change the configuration option to High Performance, but not High Protection despite both of them being Synchronous.
If not, what are the recommended steps to configure the mirror once it already has been configured? Is just like initially setting up the mirror or would there be any shortcuts I could take? If I stop the mirroring and remove the witness, will the High Protection option be available?
SQL Server 2000I will to compare a normal table with a replicated audit table havingidentical columns. I wish to report on the differences between thedata.How can I loop though a "column list" in TSQL rather than explicitlynaming each column to compare ?Thanks,Craig
I run into a wirt asp.net sittuation, when coding my ASP.NET page using the traditional old way like this, it runs very fast: Dim thisSQL =" SELECT strAuthorName, strTitle, strContent, lngChapter, tblKiemHiepChapters.strReference FROM tblKiemHiepAuthors A " & _ " INNER JOIN tblKiemHiepTitles B ON A.ID=lngAuthorID " & _ " INNER JOIN tblKiemHiepChapters ON B.ID=lngTruyenID " & _ " AND tblKiemHiepChapters.ID="& e.CommandArgument Dim thisConnection As SqlConnection = new SqlConnection(ConfigurationSettings.AppSettings("DNS2")) Dim thisCommand As SqlCommand = New SqlCommand(thisSQL, thisConnection) thisConnection.Open() dim objReader As SqlDataReader = thisCommand.ExecuteReader() objReader.Read() if objReader.HasRows then if (objReader.Item("strTitle") Is DBNull.Value ) Then thisHeading = Replace(thisHeading, "#HEADING#", "" ) else ''''''''''' end if................ but when I used VISUAL STUDIO.NET way of binding datasouce like below: it runs so damn slow. do you guys know what the problem is? if you need to see the complete code please let me know. this happen when my ntext field that contains lots of text (the code post heredoes not necessary have the same functionality) <asp:SqlDataSource ID="GetContentByChapterID" runat="server" ConnectionString="<%$ ConnectionStrings:nangmoi_cnn_string %>"SelectCommand="SELECT [lngChapter], [strReference], [strContent] FROM [tblKiemHiepChapters] WHERE (ID = @ChapterID)"><SelectParameters><asp:SessionParameter DefaultValue=0 Name="ChapterID" SessionField="ChapterID" Type="Int32" /></SelectParameters> </asp:SqlDataSource>
I found this code in a Microsoft document about index perfromance in SQL 2005. Can some one explain what iss returned by this statement, in relation to what I should do with this data to create/improve indexes? I am not sure based on the results what the columns mean as far as what needs to be done to indexes. What is the equality_columns, included_columns, statement, etc information? Do I make an index for the included_columns? Any help would be appreciated.
Here is the code: -- Potentially Useful Indexes select d.* , s.avg_total_user_cost , s.avg_user_impact , s.last_user_seek ,s.unique_compiles from sys.dm_db_missing_index_group_stats s ,sys.dm_db_missing_index_groups g ,sys.dm_db_missing_index_details d where s.group_handle = g.index_group_handle and d.index_handle = g.index_handle order by s.avg_user_impact desc go --- suggested index columns and usage declare @handle int
select @handle = d.index_handle from sys.dm_db_missing_index_group_stats s ,sys.dm_db_missing_index_groups g ,sys.dm_db_missing_index_details d where s.group_handle = g.index_group_handle and d.index_handle = g.index_handle
select * from sys.dm_db_missing_index_columns(@handle) order by column_id
I am trying to exclude records from a table where the ID column is the same but the Mail code Column is multi-valued.For Example: (the table looks like....)
hi all iam having a query , c guys iam having a table in sql with some coulmns in it , i have a column named as country in the table , now wat i want to be done is , i want to delete the column country based on some conditions , i ve to write a script code as : i ve to check if the column is there already or not if its there it shld delete the column or if its not there it shld not show any error and just return empty handed thats all , i dont know wat to be done , so if anyone knows abt it pls do send as soon as possible guys , hopefully waiting for a reply Note : any dbts pls do mail me again Venkatesan
Hey, quick question here. In my database for my webapp (The one I showed you guys in my previous post), in my stickies table, I am using a column called StickyId for the PK, and it is of type int. My concern is that eventually, I may get to a point with more then 2 million records in the stickies field. I am looking at using the bigint datatype for StickyId instead, but it takes up twice the memory. So I have two questions:1. Can I declare the StickyId column as type int for now, and if my DB ever starts approaching the 2 million mark, access my DB and change it over to a bigint type? Aka, will MS SQL convert the exisiting int values into a bigint and be transparent to the app and the users?2. Does the bigint always use 8 bytes to store its value? Aka does it pad, so that if I stored the value of '1' would that take the same space as if I stored the value '43563636'? Or is it more like varchar, only taking up the space that that particular value needs?Thanks in advance guys!
Hi there! My question is: I use a bigint data type as a primary key in my database, so 1. Can I define bigint autoincrement as unsigned? 2. In case of possible, what type should I use in C# code that equal to bigint unsigned - ulong? 3. In case of impossible, what type should I use in C# code - long? Thanks
i am creating an application which the number of records will be beyond 100000 so i need to set the primary keys to bigint instead of int i need to know about the performance and difference will it affect the performance much and second will i have to change any code like delete,update
SELECT CASE WHEN Member.CuApplicationDocFK = 'NULL' THEN 'Existing' ELSE 'New' END AS MemberType FROM Member
But the problem is Member.CuApplicationDocFK is bigint type. When I run the query it gives a error message saying "error converting data type varchar to bigint.
The documentation seems to suggest that I can store either a signed or unsigned value in bigint. If I want to store an unsigned value how do I go about it?
