Solved

SQL Server Connection String

Posted on 2014-02-08
4
390 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

The article will include the best Data Recovery Tools along with their Features, Capabilities, and their Download Links. Hope you’ll enjoy it and will choose the one as required by you.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This Micro Tutorial will teach you how to reformat your flash drive. Sometimes your flash drive may have issues carrying files so this will completely restore it to manufacturing settings. Make sure to backup all files before reformatting. This w…

778 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