Solved

SQL Server Connection String

Posted on 2014-02-08
4
405 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

The question appears often enough, how do I transfer my data from my old server to the new server while preserving file shares, share permissions, and NTFS permisions.  Here are my tips for handling such a transfer.
Many businesses neglect disaster recovery and treat it as an after-thought. I can tell you first hand that data will be lost, hard drives die, servers will be hacked, and careless (or malicious) employees can ruin your data.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

738 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