i am dealing with a database, which is having billions of data. To improve the performance have introduced threads( thread pool c#). Also implemented locks to avoid conflict. But unfortunately that didnt improved the performance.
I did the same without lock now i got 25% improvement...
Tell me an idea to achieve performance without conflict
This blocks the caller thread till all threads you queued finished executing. If it blocks, I don't see the benefit of using threading here. Your design is confusing. Can you post sample of the "StartProcessing" method ? Are you on a stand alone application or a web application ?
All C# applications should call Application.Quit(); in the beginning to avoid any .NET problems.- Unclyclopedia
Are your clustered indexes being used?
Usually having the correct clustered indexes help a lot, but if they are build wrong for the tables, then your going to have overhead (long query times, more space being taken up).
If your using SQL Server, then the "Estimated Execution Plan" in the SQL Management Stuido can help a lot with seeing the steps of the queries and what operations are taking the longest to complete.
a couple more ideas
Partitioning your data
Striping your tables across multiple disks
And of course index tuning
Then check your queries and the join/where clauses
Hire a DBA to do some performance tuning (if tables have "billions" of rows you need one)
Never underestimate the power of human stupidity
Easiest way to do this is by creating an unique non-clustered index (it could be clustered, but since there is a PK on the table a clustered index should have been created by default as the PK columns):
Create unique nonclustered index IX_Id_eleID on <table_name>
When duplicate rows are inserted, it will give the following error:
Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.cTest' with unique index 'IX_Id_eleID'.
Just put the name of the table in place of <table_name>, and name the index how you want.
SELECT INTO's only work on tables that dont exist when the command is run, you can use a SELECT INTO #tmp4 once but it wont work a second time beacuse #tmp4 will exist. also the columns in your insert into select statement arent in the same order as the select into, since the select into will create the table with the column order specified you need to make sure your column order in the inserts match, or you'll get wierd data. the better solution is to list out the columns from the table you're insterting into i.e. INSERT INTO (col1, col2, col3..) SELECT col1, col2, col3...
that way the order in the table wont matter as long as each column list matches. I dont know what you are trying to accomplish but you may be able to use self joins, or the NULLIF command to figure it out without a temp table. Hope this helps.
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
I have a sudden problem. I have my MS SQL Server 2005 on my local development machine and about 7 DB's on it. Well, I have a .net application that accesses 2 of the DB's. The connection to the first one opens just fine, but I get an error on the second connection when I attempt to open it: "Cannot open database "xyz" requested by the login. The login failed". The really strange thing is that I can connect to the DB's from VS 2005 via the Server Explorer (and yes I made sure that the connection string in the Server Explorer and my code jives), but then it kicks back that error in my try block. The following is the error that is generated in the sql server log:
2008-07-01 06:42:55.12 Logon Error: 18456, Severity: 14, State: 16.
2008-07-01 06:42:55.12 Logon Login failed for user 'DENNIS-CB848706\Dennis'. [CLIENT: <local machine="">]
Any help or insight on this problem would be greatly appreciated,
I will need you all professional expertise here. I have a database (Mssql server 2000) on a box that has been running for a year now, but suddenly crashed and some data were lost... The backup (Mssql server 2000) has been re-instored on a new box A. so we want to replicate the same data on box A to a new sql server 2005 on box B real time.
I will appreciate it if you guys can assist me by letting me know how to do data replication from mssql server 2000 to mssql server 2005.