• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 438
  • Last Modified:

SQL Server Connection String

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
Asrar Azmi
Asked:
Asrar Azmi
1 Solution
 
Dale BurrellDirectorCommented:
Can you explain why you think the connection string has anything to do with the speed?
0
 
HuaMinChenSystem managerCommented:
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
 
Jacques Bourgeois (James Burger)PresidentCommented:
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
 
Asrar AzmiWeb DeveloperAuthor Commented:
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now