Solved

SQL Server Connection String

Posted on 2014-02-08
4
393 Views
Last Modified: 2014-02-18
Hi, Experts

I need Fastest sql server 2008 R2 connection string in c#
I am using following connection string which make our website very slow.
connectionString="Data Source=dbserver;Initial Catalog=gpdb;User ID=sa;Password=abcxyz;MultipleActiveResultSets=true;" providerName="System.Data.SqlClient"/>

Open in new window

0
Comment
Question by:Asrar Azmi
4 Comments
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 39843829
Can you explain why you think the connection string has anything to do with the speed?
0
 
LVL 10

Expert Comment

by:HuaMinChen
ID: 39843847
Try this
	<connectionStrings>
		<add name="conn2" connectionString="Data Source=192.?.?.?\your_db_instance;Initial Catalog=your_db_schema;Integrated Security=False;User ID=your_db_login;Password=??????" providerName="System.Data.SqlClient"/>
	</connectionStrings>

Open in new window


in addition to this, if the website is really slow, it can be because of other reasons, like networking or database. For database, you can try to tune the relevant Sql scripts.
0
 
LVL 40

Accepted Solution

by:
Jacques Bourgeois (James Burger) earned 500 total points
ID: 39844254
As hinted by Date Burrell, the connection string has nothing to do with the performance. It's used to connect, but that is all. Once connected, it's the way the database is designed and used that matters.

Do you use text fields instead of integer fields for primary keys and for fields that are used in links? Text fields are not efficient for these purposes.

Do you use indexes where appropriate? They make a big difference in performance.

Do you lock records or run transactions that for long periods of time. If there are too many useless locks, users could be waiting for records to unlock.

Do you store a lot of information in blob fields, such as images. Depending on the application, it is sometimes better to leave images and videos in files and simply store the path in the database.

Do you run the database on a dedicated server. If it runs on a computer that performs a lot of tasks, then the database will be slowed down. You should also be sure to have a decent computer to run the server.
0
 

Author Comment

by:Asrar Azmi
ID: 39850023
I think becoz of this curson it make perfomance slow
can any one can check and please let me know what wrong in the cursor

USE [gpssystem]
GO
/****** Object:  Trigger [dbo].[CheckGeofenceArea]    Script Date: 02/11/2014 16:17:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[CheckGeofenceArea] ON [dbo].[gpsobject] 
FOR UPDATE
AS

If UPDATE (latitude) AND UPDATE(longitude)

declare  @devicenum varchar(50),@vehiclename varchar(50),@dynlatitude decimal(18, 4),@dynlongitude decimal(18, 4),@systimetime datetime ,@speed as decimal
declare @id int,@radius decimal(18, 4),@fixlatitude decimal(18, 4),@fixlongitude decimal(18, 4),@rowno int
---------------------------------------------------------------
select @devicenum=devicenum,
@dynlatitude=latitude,
@dynlongitude=longitude,
@systimetime=systime,
@speed=speed
from inserted

--select @rowno =(select count(*) from alerts where devicenum =@devicenum )

--select @id=id, @radius=radius, @fixlatitude=latitude , @fixlongitude=longitude  from  alerts where devicenum=@devicenum and isactive=1 order by id
-------------------------------------------------------------
Declare MY_data CURSOR LOCAL FOR
 --359231039842834
--Select Name ,Mobile from  My_table (Nolock)
select id,radius,latitude,longitude from  alerts where devicenum=@devicenum and isactive=1 order by id 

OPEN MY_data
    FETCH NEXT FROM MY_data INTO @id ,@radius,@fixlatitude,@fixlongitude
        WHILE @@FETCH_STATUS = 0
        BEGIN
 
           ------------------------------------------------------------------------------- 
                      --Validate Function goes here.
                      declare @result varchar(3)
                     
                      exec @result = dbo.ValidateLocation @fixlatitude,@fixlongitude,@radius,@dynlatitude,@dynlongitude
					  --exec ValidateLocation1 'Out', 24.8034,46.7457,2,24.1569,47.3387
					  update Alerts set AreaStatus=@result,systime=@systimetime,speed=@speed where id=@id
           -------------------------------------------------------------------------------
            
            
	
		
			
		
        FETCH NEXT FROM MY_data INTO @id ,@radius,@fixlatitude,@fixlongitude
        END
    CLOSE MY_data
DEALLOCATE MY_data


----------------------------------------------------------------


--update Alerts set AreaStatus='Out',systime='" + sysdatetime + "',speed='" + speed + "' where id='" + strid + "' and devicenum=@devicenum;


	PRINT 'AFTER UPDATE Trigger fired. : CHECK_GEOFENCE_AREA'

Open in new window

0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Finding original email is quite difficult due to their duplicates. From this article, you will come to know why multiple duplicates of same emails appear and how to delete duplicate emails from Outlook securely and instantly while vital emails remai…
When we purchase storage, we typically are advertised storage of 500GB, 1TB, 2TB and so on. However, when you actually install it into your computer, your 500GB HDD will actually show up as 465GB. Why? It has to do with the way people and computers…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question